MySQL优化查询之深入浅出索引

建索引是我们在提高数据库查询性能、减低SQL执行时间的主要手段，但是怎么建立一个最适合的索引呢？我们来深入浅出索引分析一下

我们都知道索引的作用是提高数据查询效率。

 

常见索引模型：哈希表、有序数组、搜索树

哈希表原理：采用的是键值对的存储方式，哈希冲突的处理办法是采用链表。

哈希表使用的场景：在只有等值查询的场景

有序数组原理：按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))。

有序数组的适用场景：静态存储引擎。

搜索树原理：每个节点的左儿子小于父节点，父节点又小于右儿子。查询时间复杂度O(log(N))，更新时间复杂度O(log(N))

搜索树的适用场景：数据库存储大多不适用二叉树，因为树高过高，会适用N叉树。

我们常用的InnoDB引擎中的索引模型：B+Tree（N 叉树）由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

 

索引类型：主键索引、非主键索引。

主键索引的叶子节点存的是整行的数据(聚簇索引)，非主键索引的叶子节点内容是主键的值(二级索引)。

主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)。

回表：回到主键索引树搜索的过程，称为回表。

覆盖索引：某索引已经覆盖了查询需求，称为覆盖索引。例如：select ID from T where k between 3 and 5 在引擎内部使用覆盖索引在索引K上直接读取到主键ID，减少了回表操作从而提升了性能。

最左前缀原则：B+Tree这种索引结构，可以利用索引的"最左前缀"来定位记录。只要满足最左前缀，就可以利用索引来加速检索。最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

索引下推：在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

 

选择普通索引还是唯一索引？

普通索引：查到满足条件的第一个记录后，继续查找下一个记录，知道第一个不满足条件的记录。

唯一索引：由于索引唯一性，查到第一个满足条件的记录后，停止检索。

change buffer：当需要更新一个数据页，如果数据页在内存中就直接更新，如果不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中。下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中的与这个页有关的操作。

merge：将change buffer中的操作应用到原数据页上，得到最新结果的过程。

访问数据页会触发merge，系统有后台线程定期merge，在数据库正常关闭(showdown)的过程中，也会执行merge。

两类索引在查询效率上是没有差别的，主要是更新效率上的差异。change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。所以尽量使用普通索引， 普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。当然主要还是看业务需求，如果业务上需要用到唯一索引的特性还是要选择唯一索引的。

 

给字符串加索引

使用前缀索引定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。如：

alter table user add index index1(email(6));

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

 

总结：博主大概深入浅出的讲述了一下索引的常见选择及一个执行过程，在日常业务中具体索引的选择要以业务需求为主。可以通过explain查看SQL的索引选择，不会使用explain可以参考：https://blog.csdn.net/baidu_38083619/article/details/82790712。