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

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

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

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

SQL如下：

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索引原理

本文由 董航 创作，采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名

写得好 长知识了