MySQL 笔记整理(11)

Wesley13
• 阅读 480

笔记记录自林晓斌(丁奇)老师的《MySQL实战45讲》

(本篇内图片均来自丁奇老师的讲解,如有侵权,请联系我删除)

11) --怎么给字符串字段加索引?

  日常工作中的登录系统,你很可能会使用emai这个字段。因此也很容易遇到类似这样的语句:

mysql> select *from user where email = 'xxx';

  我们知道,如果没有索引那就只能使用全表扫描。并且MySQL是支持前缀索引的,也就是说,你可以顶一个字符串的一部分作为索引。默认地,如果你创建索引的语句不指定前缀长度,那么索引就会包含整个字符串。由于email字段通常较长,如果你直接使用email作为索引的话,索引占用的空间就会较大。所以可以采用前缀索引的优势,比如,建立索引email(6),每个邮箱字段只取前6个字节。但这也会带来损失,如你可能会增加额外的记录扫描行数。因为前6位相同的邮箱就没办法直接通过eamil的索引来判断了。因此我们建议使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。

前缀索引对覆盖索引的影响:

我们知道,普通索引存储的是主键索引的值,因此,如果你不是select* 而是select id,email就可以不进行回表,也就是覆盖索引。但此处有一个隐藏的问题,即,如果你使用了email字段来作索引,没问题可以利用覆盖索引的性质来进行快速的查询。但如果你使用的是前缀索引email(255),(我们db中的email数据的长度均不超过它),虽然索引覆盖了全部长度的email的值,还是不能使用覆盖索引的性质。因为系统并不确定前缀索引是否截断了完整的信息,因此必须进行回表。

其他方式:

当然,你也可能会遇到字段的前缀区分度不够高的情况。如我们国家的身份证号,对于同一个城市的人,身份证号前几位的区分度很低。此时,你可以有别的方式来进行处理如:

  • 使用倒序来存储数据。
  • 使用Hash字段存储。

  这两种方式的相同点是都不支持范围查询,并且都提供了足够多的区分度。另外,Hash计算的结果虽有冲突的可能,但概率很低,因此可以认为每次查询的扫描行数接近1.但使用Hash的方式会额外增加计算的消耗以及额外的存储空间的消耗。

上期问题:

如果没有session A的配合,只是单独执行 delete from t; call idata(); explain这三条语句,会看到explain结果中rows字段其实还是再10000左右,即使用了索引,这是为什么呢?

  答:delete语句删掉了所有的数据,然后通过call idata()插入了10万行数据,看上去是覆盖了原来的10万行。但是,session A开启了事务并没有提交,所以之前插入的10w行数据不能删除,这样,之前的数据每一行都会有两个版本,旧版本是delete之前的数据,新版本是标记为delete的语句。这样索引a上的数据其实是有两份的。由于Mysql是使用删除标记来删除记录的,并不从索引和数据文件中真正删除。如果delete和insert中间的间隔相对较小,purge线程还没来得及清理记录。如果主键相同的情况下,新insert会沿用delete之前的记录空间,因此统计信息不变。

问题:

如果你要维护学生信息的数据库,学生登录名统一是"学号@gamail.com",学号的规则是十五位的数字,前三位是城市编号,四到六位是学校编号,七到十位是入学年份,最后五位是顺序编号,只考虑登录验证的话,你会怎么设计这个登录名的索引呢?

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
5个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这