MySQL优化的底层逻辑

时间：09-05来源：作者：点击数：26

前言

前面一篇《MySQL性能调优参考》，文章中提到优化的几个技巧，比如数据类型的使用、范式和反范式的合理使用、索引的使用及其使用的注意事项等等。其中我们接触最多的就是索引，你可能知道索引的底层结构是B+Tree、使用索引要遵守最左匹配原则，那你知道为什么要用B+Tree、为什么使用索引有那么多注意事项吗？所以还是要知其然知其所以然，看完这篇文章你就懂了。

索引的底层结构

首先，MySQL索引的存储不仅仅只有B+Tree的结构，还有Hash和全文，这个在创建索引时可以指定。

MySQL中常用的InnoDB存储引擎默认使用B+Tree结构，毕竟使用MySQL时范围查找的场景是最多的，当然如果等值查询比如热点数据这种场景可以使用Hash索引，如果有大量的文本数据需要搜索和处理，使用全文索引是一个常见的选择。这里只对B+Tree索引展开介绍，如果不了解B+Tree的可以先了解下前置知识《常见的数据结构及应用》。

数据与索引的关系

要想知道在使用索引为什么要有那么多的注意事项和原则，我们需要先了解一下数据和索引的关系。接下来我通过一个简单例子，说明一下B+Tree索引在存储数据中的具体实现。

先创建一张商品表，设置id为主键：

CREATE TABLE `goods`  (
  `id` int PRIMARY KEY NOT NULL,
  `goods_no` varchar(20)  DEFAULT NULL,
  `goods_name` varchar(255) DEFAULT NULL,
  `goods_price` decimal(10, 2) DEFAULT NULL
)；

表中的数据如下：

聚簇索引的数据存储

在向MySQL插入一行数据时，默认情况下，会根据主键字段的数据作为索引键值构建B+Tree索引，这个过程会遵循B+Tree的规则。goods表中的这些数据在B+Tree中的逻辑结构如下图

可以看到在非叶子节点上只存放了主键列的值，而叶子节点存放了主键对应的整行数据，这种索引又叫「聚簇索引」也叫「主键索引」。当sql句为 select id from goods 或者 select * from goods where id = 1 时都会通过这个索引进行查询到数据，这个可以通过执行计划看到

普通索引的数据存储

而我们自行设置的其他索引都称之为「普通索引」或「二级索引」或者是「非聚簇索引」，在向MySQL插入一行数据时除了会根据主键构建一个聚簇索引，还会根据其他索引列构建对应的普通索引。这里为goods_no、goods_name列创建一个普通索引后，表中的数据在这个索引中逻辑结构如下图

可以看到在普通索引中每个非叶子节点的键值存放的是索引列的数据，而叶子节点不仅存放了索引列的数据，还存放了对应的主键值。

索引的命中逻辑

那么，问：那么请问，当执行以下sql时会使用哪个索引？为什么？

sql1:select id,goods_name from goods where goods_no='00001'
sql2:select id,goods_name,goods_price from goods where goods_no='00001'
sql3:select id,goods_name,goods_price from goods where goods_name='goods1'

sql1中使用了goods_no、goods_name创建的普通索引。因为查询的字段id和goods_name都在这个索引的叶子节点中，可以直接返回这些数据，所以不用再去其他地方查询，这个过程也叫做「覆盖索引」,执行计划中的Using index 就可以说明。

sql2中使用了普通索引和主键索引。因为要查询的goods_price在普通索引没有找到，所以在拿到主键后会去主键索引中再查找一次，这个过程叫做「回表」，也就是说要查两个 B+Tree 才能查到数据（如下图）。通常情况下要尽量避免回表操作，因为多一次扫描查询效率就会下降一些。

sql3没有使用索引，走的是全表扫描。首先条件字段并未使用到普通索引，因为不符合「最左匹配原则」。其次查询字段id,goods_name,goods_price在两个索引中都无法匹配，因此走了全表扫描，这种现象也叫「索引失效」。

怎么理解索引失效

不论是WHERE条件也好，查询字段也罢，是否使用索引或者使用哪个索引都是「优化器」来决定的，以下几个是优化器工作时索引失效的例子及说明：

当索引列的唯一值与总行数的区分度很小，比如索引列的值就是男和女，那么优化器就会忽略索引直接全表扫描，因为这种情况下即使通过索引定位数据，也会因为索引列的重复去访问大部分的数据页，加上维护索引以及访问索引的开销，这样下来还不如直接去访问数据页。
当优化器看到WHERE条件中有OR关键字时，会看前后两个字段是否都是索引列，因为OR的含义就是两个只要满足一个即可，所以只要有一个不是索引列就会进行全表扫描。
说一下sql3的「最左匹配原则」，当构建联合索引（goods_no，goods_name）时，会先对goods_no进行排序插入，如果遇到goods_no相同时再对goods_name进行排序插入，所以优化器在看到查询字段goods_name与索引字段顺序不一样时就会忽视掉这个索引，从而导致索引失效。如果符合最左匹配原则，在根据索引查找数据时会先根据goods_no进行比较，在goods_no相同时再根据goods_name进行比较。
还有大家都知道的索引列使用函数、表达式会失效，为什么？因为B+Tree中的节点存放的是列的原始数据，你拿一个经过函数或者表达式计算的值来查找当然不认识了。 5 还有等等索引失效场景。