带你学习MySQL索引

微信扫一扫,分享到朋友圈

带你学习MySQL索引

1.什么是索引
索引在生活中处处可见,比如我们平时生活过程中会去电影院看电影,到电影院后会先去打印机打印出我们的票据,然后根据票据上指定的影厅号,直接进入对应的影厅观赏我们喜爱的电影,该过程高效快速节省时间。
2. 无索引场景
还是拿去电影院看电影举例,在拿到电影票票据后,票据上没有显示该电影在几号厅放映(也就是没有索引),结果就是我们会从一号厅开始查看,先走入厅内,看播放的电影是否是我们要看的电影,如果不是接着寻找二号厅、三号厅、四号厅、五号厅……如果电影院的包房厅较少,还可以接受,如果比较多,简直就是一场噩梦,在观看电影前我们需要耗费大量的时间来寻找目的厅。
3.索引的作用
经过上面的描述,可以很容易总结出索引的作用就是帮我们快速得到想要的结果。
MySQL作为一个关系型数据库,会存放应用程序需要持久化的数据,然后为应用程序提供查询持久化数据的功能。为了能够快速定位应用程序需要的数据,MySQL也提供了索引功能。
4.索引数据结构
4.1 非平衡二叉树

非平衡二叉树缺点很明显,在一定条件下会退化为链表,从而降低查询效率
4.2 平衡二叉树

平衡二叉树解决了非平衡二叉树会退化成链表的问题,存在的另一个问题就是一个根节点最多有两个子节点,存储数据过多时会导致树的高度很大,树的高度越大,搜索的IO次数也就越多
4.3 B+树

B+树的好处就是多叉,非叶子节点不存放数据,数据记录主要存放在叶子节点(最下面的结点)上,你可能会想为什么要这样设计呢?
如你所知MySQL一次IO并不是只取一条或者多条记录,而且取记录所在的一页数据,也就是说MySQL进行IO的基本单位为页,一页的大小16KB,可以通过show variables like ‘innodb_page_size’;命令查看。由此可知,非叶子节点存放的数据越少,一页存放的数据量也就越多,同样分叉也就越多,相同高度下存储的数据也就越多,IO的次数也就越少,查询效率自然也就越高。
5.索引类型
MySQL为满足不同的需求提供了多种索引类型:

主键索引 唯一索引 普通索引 全文索引
由这些索引类型又引出了各种相关的术语,比如聚簇索引、非聚簇索引、组合索引、覆盖索引、扩展索引、前缀索引、最左匹配原则、回表,下面会一一解释这些术语的含义。
5.1 聚簇索引
聚簇索引不是一种新的索引类型,而是一种数据存储形式,将索引列和数据记录存放在一起。MySQL会根据表中的主键来创建聚簇索引,聚簇索引的数据结构是一个B+树,非叶子节点只存放索引列和指向下一节点的指针,叶子节点会存放表中的所有数据。
如果表没有主键,那么MySQL会寻找第一个唯一索引,根据该唯一索引来创建聚簇索引;如果表中也没有唯一索引,MySQL会隐式创建一列来作为聚簇索引。
5.2 非聚簇索引
和聚簇索引相反的就是非聚簇索引,MySQL普通索引就是非聚簇索引,非聚簇索引对应B+树的非叶子结点存放索引列和指向下一节点的指针,叶子节点只存放索引列和该列所在行记录的主键(请注意:此处的叶子节点不存放完整的数据,只存放主键)。
如果非聚簇索引的叶子节点只存放主键,怎么获取查询的结果?使用普通索引的列作为搜索条件,首先会在非聚簇索引对应的B+树上进行查找,找到对应的主键后,会拿着主键在聚簇索引对应的二叉树上继续搜索,从而返回最终想要的数据结果。
5.3 组合索引
组合索引就是将多个列合起来创建一个索引,因为索引不是创建越多越好,而且一个表的索引数量也是有限制的,一个表索引限制数量为16个,再者就是索引会对插入和修改操作产生性能影响。
如针对a、b、c三列创建一个索引,相当于创建了3个索引(a)、(a, b)、(a, b, c),只要查询条件中包含组合作引的最左列,查询计划就会使用到索引。
5.4 覆盖索引
覆盖索引就是可以从索引中获取到想要的结果数据,这种索引成为覆盖索引,这也就是为什么不推荐使用select * 进行查询的原因,如果使用,那么永远不可能用到覆盖索引的优势。
5.5 扩展索引
当我们创建一个二级索引的时候,MySQL会自动在二级索引的后面加上主键索引,扩展了我们的二级索引。
5.6 前缀索引
需要对一个很大的字符串进行检索时,可以在创建索引的时候指定索引列的长度。
5.7 最左匹配原则
最左匹配原则和组合索引紧密相连,比如我们创建了一个(a, b, c)这样的一个组合索引,只要我们的查询列中包含最左列,就可以使用到索引,如果不包含就使用不到索引。
5.8 回表
如果无法从索引中得到想要的查询结果,就需要到表中去取需要查询的列数据,这个过程称为回表,回表会增加磁盘IO。
6.索引优化实战
6.1 构建SQL数据
可以从test_db下载需要的数据,然后在命令行中执行mysql -t < employees.sql -u root -p命令导入数据,出现如下图所以说明数据导入成功

6.2 表分析
此处选择employees表作为演示,在first_name、last_name、birth_date三列上建立联合索引:

6.3 最左匹配原理分析
最左匹配原理:查询条件中包含组合索引的最左列
6.3.1 全列匹配

执行计划中可以看出当前查询命中了索引,至于是否命中组合索引的全部,key_len字段可以给出解释。通过show create table employees\G;命令可以得知first_name字段定义为varchar(14) not null,last_name字段定义为varchar(16) not null,birth_date字段定义为date not null,表的字符集编码为latin1(该字符集下一个字符占用一个字节),以上信息可以得出组合索引的总长度 = 14 + 2 + 16 + 2 + 3 = 37,至此我们可以确定当前查询使用到组合索引的全部。
不知道你有没有类似的经历,在刚接触组合索引”最左匹配”原则时,天真的认为查询条件列的顺序要和组合索引列的顺序一致?

实践证明查询条件列的顺序并不需要和组合索引列的顺序保持一致,只需要查询条件中包含组合索引的最左列就可以了
6.3.2 最左前缀列匹配

可以看到使用组合索引的最左列进行查询,可以使用索引,key_len长度为16表明查询只使用到索引的一部分。
6.3.3 使用组合索引缺少中间项

在使用组合索引进行查询时,查询条件中缺少组合索引从左往右的某一列,之后的列是无法利用到索引。若是想充分利用组合索引的优势,可以采用填充的方法,将缺失的那一列填充上,前提是缺失的那一列存在的值是可罗列的。

6.4.4 查询条件中没有指定最左列

创建组合索引却没有指定最左列应该算是最糟糕的查询了
6.5.5 某一列模糊匹配

可以看到模糊匹配,只有后缀模糊的情况下才会使用到组合索引全部,其它情况并不会使用组合索引全部。
6.5.6 范围查询
![](/Users/pengli/Library/Application Support/typora-user-images/image-20200925215206528.png)
联合索引情况下,查询条件中出现范围查询,后面的条件就不能使用到索引,>=、between and、in却可以使用,原因是MySQL认为>=、between and、in是多值精确匹配。
6.5.7 查询条件中含有函数和表达式

MySQL的优势是数据持久化和进行简单查询,切记不要在查询条件列上使用表达式和函数,并非进行进行运算
7.覆盖索引
覆盖索引就是查询结果可以直接从非聚簇索引上中获取,不需要回到聚簇索引查询,避免了IO操作,来看看下面的例子

上面的查询没有任何问题,充分利用了索引的优势,但是真的就完美了嘛?假如我们只需要emp_no、first_name、last_name、birth_date这四列中的几列数据呢?

可以看到此查询计划和上面的唯一不同的地方在最后一列,上面执行计划最后一列为null,此查询最后一列为Using index,Using index是查询使用到覆盖索引的意思,这也就是某些MySQL优化宝典中提到的一个要点:不要使用SELECT * 的原因,可以再来看看两种方式下的查询耗时情况

同样的查询语句,如果查询结果可以从索引中获取,此时指定查询的列要比*的查询效率高
8.总结
本文主要讲解了什么是索引、索引使用场景、索引作用、索引数据结构、索引类型、索引相关术语、索引常见优化方式,你可以以此作为一个总结,如果有更好的案例,也欢迎交流沟通,一起学习,一起进步。

微信扫一扫,分享到朋友圈

带你学习MySQL索引

美的E90 ▪飞阁烟机|双腔劲拢自然清新,享受厨房生活本质之美

上一篇

爱奇艺VIP会员5折大促 黄金会员99元限时24小时

下一篇

你也可能喜欢

带你学习MySQL索引

长按储存图像,分享给朋友