MySQL InnoDB索引随手记

在面试中,我们经常被问到,你对于MYSQL的性能优化有什么实践。很多兄弟只是懵懂的回答:索引,加索引,用索引。
但是,为什么使用索引会加速查询呢?面试中常见的陷阱有哪些呢?
大多数情况下,我们现在使用MYSQL,都用的InnoDB引擎,我们建立索引时,最常见的索引算法是b+tree,这次的记录仅针对该场景下做一些简单的分析。
1.索引的作用

我们拿出任意一本书,字典,教材,打开前面几页,能看到目录。目录中,根据页码顺序,标注了不同的文章、课程所在的页码。我们如果想查看(或者查找)某一章节内容,需要现在在目录中找到章节对应的页码,再翻开书籍到对应的页,就可以快速打开我们想要的内容。假设,你买的这本书吗,没有目录,会怎么样?你如果想查看章节内容,必须一页页的翻找,才有可能找到你想要的内容。相比之下,有目录的书,肯定会比没有目录的书查找章节内容要快。那目录(索引)没有任何缺点吗?我们假设一个场景,一本700多页的新华字典(表),假设新收录了几百个a开头的汉字(新插入了几条数据),按照字典的目录规则,肯定是放在字典相当前面页面了。此时很有可能,b开头及以后的汉字所在的页码多发生了变化,他们在字典中的位置被新收录的汉子顶到后面去了,那么,之前的目录(索引)里面存的页码,就失效了,需要重新编写目录(重建索引)。毫无疑问,这是一个相当耗时的事情(操作)。

2.MYSQL中主键索引的工作流程
InnoDB的主键索引是聚集索引,有的地方称呼为聚簇索引,那么,如何理解这个聚集的意思呢。

我们有一张表,他有id,name,company三列。我们对id建立主键索引,name简历普通的索引,有些地方叫做二级索引/辅助索引。

SQL如下:

1
2
3
4
5
6
7
CREATE TABLE `NewTable` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID,主键索引' ,
`name` varchar(255) NOT NULL ,
`company` varchar(255) NOT NULL ,
PRIMARY KEY (`id`),
INDEX `name_index` (`name`) USING BTREE
);

主键的聚集索引我们可以理解为,索引存储的是行记录的指针(实际上不是指针,但是概念很类似),我们只要找到了主键,就能根据主键存储的行记录的位置,拿到行数据。
而普通/辅助/二级索引,我们可以简单的认为,他存储的主键的值。那么,如果我们根据二级索引查询到了主键的指,实际上,我们还要在走一次主键索引,才能拿到具体的行记录。
那么,索引到底是怎么帮助查找的呢?我们看下图。


假设,我们有1~99条记录。实际上,ID可能不连续,所以这里只有22条数据。根据b+tree的算法,他的数据分部可能是上面的结构。
那么根节点中,我们认为它对1~99做了分段记录,分为1~27(其实只有3,5,8,9,10,12,13,15,17,26总计10条数据),28~99两段(12条数据)。对于第二层,我们认为,他把1~27分为了三段,1~9,10~16,17~27。第三层,就是具体的主键的值了。
如果我们要找id为60的记录,在根节点中,计算出,60在28为起点的的块中,向下一级取到第二个块。在第二个块中,再做一次查找,我们计算出,60在36~78的块中,再向下一级到第三层,拿到指定的数据块36~60~65~75。再做一次查找,最终拿到行数据(或者拿不到,返回空行)。它的查找顺序如下图:

b+tree中,上级节点不仅存了下级节点的指针,最下级叶字节点中,还额外维护了横向的指针(假想为一个类似双向联表的结构)。如果要查找到id为13~60的记录,那么,先走一次上面的查找过程,拿到13,然后做横向(叶子节点间的指针移动)移动,逐个遍历数据,一直获取到60。它的查找顺序如下图:

使用非聚集索引/二级索引/辅助索引查找时,它的查找过程可以参考下图:

从这个图我们不难看出,辅助索引拿到数据后会再走一次聚集/主键索引拿到数据行。所以,常规情况下,我们在写获取单行记录时,还是应该尽量使用主键索引(当然,大部分情况下我们会建立一个自增的ID列作为主键索引)。

3.一些众所周知的索引建立原则

1).数据量少的表不用建立索引,或者说索引提升不大。

why?就上图的例子来说,22条数据我们认为他总共分了6个磁盘块存储。如果没有索引,遍历到60这条数据也只需要5次磁盘IO(一般认为文件在磁盘上不是连续存储)就可以拿到数据。而使用索引,第一级节点一次IO,第二级节点一次IO,就可以拿到数据所在的磁盘块。差距其实不是特别大,再加上索引重建的损耗,实际效果可能没有什么差别。但假如行记录数量在百万级别,数据分块特别多,顺序遍历需要的IO操作数会直线上升,而b+tree的索引则始终维持在2次。

2).数据重复率高的列不适合建立索引。

why?我们假设用户表性别字段,我们建立索引,他可能的数据分布图如下:

此时的情况下,b+tree的层级过少,假设我们要查找id为32的数据,第一次IO后,我们定位到第一段,然后去下级节点拿到1~4,然后要做多次横向遍历,才有可能拿到32的数据。和全表扫描的区别不是很大,而且额外增加了索引重建的成本。

4.面试中常见的索引问题陷阱。

1).现在有个表,有四列a,b,c,d,我建立联合索引a_b_c_d,SQL条件为 where b = 1 and c = 1 and a = 1 and d =1,会不会使用索引?

答:会,所谓的最左前缀匹配原则,是指查询条件要和联合索引建立的字段顺序一致,才会使用索引,但是,MYSQL的查询优化器,会自动调整 = 和 in的顺序,以便使用索引。

2).如果查询条件为 where a =1 and b = 1 and c = 1,会不会使用索引?

答:会,最左前缀匹配的应用。拿生活中的字典举例,字典是根据拼音顺序建立的目录结构。拿我的姓氏举例,dong,他其实类似于索引为 where a = d and b = o and c= n and d =g,我如果只想查d起头的汉子,你说全拼的目录对于你翻找字典有没有帮助?

3).如果查询条件为 where b > 2 and a = 1,会不会使用索引?

答:会,但是只有a = 1部分会用到索引。

参考&引用:

voidccc@剖析Mysql的InnoDB索引

brotherbin@MySQL索引原理