上一篇分析了InnoDB存储引擎的B+树索引，现在来进行一个简单的回顾。

  1. 每个索引都对应一颗B+树，B+树分为好多层，最下边一层是叶子结点，其余的是内结点。所有的用户记录都存储在B+树的叶子结点，所有目录项记录都存储在内节点。
  2. InnoDB存储引擎会自动为主键建立聚簇索引，聚簇索引的叶子结点包含完整的用户记录。
  3. 我们可以为指定的列建立二级索引，二级索引的叶子结点包含的用户记录由索引列+主键组成，所以如果项通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
  4. B+树中每层结点都是按照索引列值从大到小的顺序排序而组成了双向链表，而且每个页内的记录(不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，在按照联合索引后边的列排序。
  5. 通过索引查找记录是从B+树的根节点开始，一层一层乡下搜索。由于每个页面都按照索引列的值建立了页目录，所以在这些页面中查找非常快。

一，做一些前置的准备

为了这篇文章的演示，需要先建立一张简单的表，用来演示索引执行过程中出现的一些情况。

  1. create table single_table
  2. (
  3.     # 主键索引
  4.     id           int primary key auto_increment,
  5.     key1         varchar(100),
  6.     key2         int,
  7.     key3         varchar(100),
  8.     key_part1    varchar(100),
  9.     key_part2    varchar(100),
  10.     key_part3    varchar(100),
  11.     common_field varchar(100),
  12.     # 索引列key1
  13.     key idx_key1 (key1),
  14.     # 唯一索引：索引列key2
  15.     unique key uk_key2 (key2),
  16.     # 索引列key3
  17.     key idx_key3 (key3),
  18.     # 联合索引：索引列：key_part1, key_part2, key_part3
  19.     key idx_key_part (key_part1, key_part2, key_part3)
  20. ) engine = innodb
  21.   charset = utf8;

再往表中插入100w条数据，具体的插入过程不在演示。

二，索引的代价

凡事都是有利有弊的，索引可以加快查询的速度，但是同样的，他也有相应的缺点。

  1. 空间上

一个索引对应一个B+树，每一个B+树的每一个节点都是一个数据页。一个数据页大小默认是16kb，所以一张表的索引越多，占用的空间其实越大，特别是在数据量大的时候，所以一般我们建立索引，默认每张表不要超过5个。

  1. 时间上

在对表进行增删改操作的时候，要对所有索引对应的B+树进行修改。而且上一篇分析过，B+树的每一层节点都按照索引列的值从小到大的顺序排序组成了双向链表。页中的记录都按照索引列的值从小到大的顺序形成了一个单向链表。而增删改操作可能会对结点和记录的排序造成破坏，所以存储引擎需要额外的时间进行页面分裂，页回收等操作，好维护结点和记录的排序。索引越多，维护的时间成本越高。

还有一点就是执行查询语句之前，会先生成执行计划。一般情况下一条语句再一次执行过程中只会使用一个二级索引(有特殊的，后面会分析)，在生成执行计划的时候需要计算使用不同索引执行查询时所需成本，最后选取成本最低的索引执行查询。如果索引太多，分析成本就会很高，耗时严重，从而影响查询语句的执行性能。

为了合理的建立索引，一方面加快我们的查询速度，一方面又不会过分的占用我们的时间和空间，我们需要了解索引在查询执行期间到底是如何发挥作用的。

三，使用B+树索引

1.扫描区间和边界条件

先说什么是全表扫描？就是从头到尾依次遍历所有结点，再依次遍历结点中的所有记录。全表扫描虽然效率很低，但是却是一种万能的解决方案，所有查询都可以使用这种方案兜底。

我们可以利用B+树查找索引列值等于某个值的记录，这样可以明显减少需要扫描的记录数量。由于B+树叶子节点中的记录是按照索引列值从小到大的顺序排序的，所以只扫描某个区间或某些区间中的记录也是很快的，比如下面这个查询语句：

  1. explain select * from single_table where id>=2 and id<=100;

1.png

这个时候其实是走了主键索引，这个语句其实是想查找id值在区间【2，100】内的所有聚簇索引记录，我们可以通过主键索引先定位到id=2的记录，然后顺着这条记录的单向链表往后找就行了。

与全表扫描的100w数据相比，扫描这个区间的成本简直太小了，所以提升了查询效率。我们把这个案例中待扫描的id值所在区间称为扫描区间，把形成这个扫描区间的搜索条件称为形成这个扫描区间的边界条件。

其实对于全表扫描来讲，就是在【-∞，+∞】的区间进行扫描而已。

再来看一条查询语句：

  1. explain
  2. select *
  3. from single_table
  4. where key2 in (1438, 6328)
  5.    or (key2 >= 38 and key2 <= 79);

2.png

这个查询的搜索条件涉及到key2列，我们正好在key2列上建立了唯一索引。如果使用唯一索引执行这个查询，实际上相当于从三个区间获取二级索引的记录。

  1. 【1438，1438】
  2. 【6328，6328】
  3. 【38，79】

类似前面两个区间这种，只有一个值的区间，我们称为单点扫描区间，把类似第三个区间这样存在多个值的叫做范围扫描区间，另外，由于我们的查询列是*，导致从上述的区间每次获取到一条二级索引记录，就需要根据二级索引记录的id列的值取回表一次。

当然，并不是所有的条件都可以称为边界条件，比如下面的查询语句：

  1. explain
  2. select *
  3. from single_table
  4. where key1 > 'aaa'
  5.   and key3 < 'zzz'
  6.   and common_field = 'aaa';

3.png

  1. 如果使用idx_key1执行查询，那么相应的扫描区间就变成了【’aaa’,+∞】，后面的条件就是普通搜索条件，这些普通的搜索条件需要在获取到idx_key1的二级索引记录后，在执行回表操作，在获取到完整的用户记录后才能去判断他们是否成立。
  2. 如果使用idx_key3执行查询，那么扫描区间就是【-∞,’zzz’】,其余的条件就是普通搜索条件，这些普通的搜索条件需要在获取到idx_key3的二级索引记录后，在执行回表操作，在获取到完整的用户记录后才能去判断他们是否成立。

在使用某个索引执行查询的时候，关键的问题就是通过搜索条件找出合适的区间，然后再去对应的B+树中扫描索引列值在这些扫描区间的记录，对于每一个区间来说，只需要定位到第一条，就可以沿着单链表一直往后扫符合条件的记录。

其实对于B+树索引来说： = <=> in not in is null is not null > < >= <= between != like 都会进行区间扫描，只不过区间扫描大小不同导致效率不同。

不过也有一些需要注意的点：

  1. in和多个 = 用or连接起来的效果其实是一样的，都会产生多个单点扫描区间
  2. 不等于 产生的区间比较操蛋：
  1. select * from single_table where key1 != 'aaa'

这个时候idx_key1 执行查询的时候对应的扫描区间就是【-∞,’aaa’】和【’aaa’,+∞】。

  1. like操作比较特殊，只有在匹配完整的字符串或者匹配字符串前缀的时候才会产生合适的扫描区间

比较字符串的大小其实就是逐个比较每个字符的大小。字符串的比较过程如下：

  1. 先比较字符串的第一个字符，第一个字符串小的字符就比较小
  2. 如果第一个字符一样的话就按照上面的规则比较第二个，以此类推。

对于某个索引列来说，字符串前缀相同的记录在由记录组成的单向链表中肯定是相邻的。比如我们有一个搜索条件是key1 like 'a%'，对于二级索引idx_key1来说，所有字符串前缀为a的二级索引记录肯定是相邻的。这也就意味着我们只要定位到key1值得字符串前缀为a的第一条记录，就可以依次往后扫描，直到某条二级索引记录的字符串不是a为止。

很显然，key1 like 'a%'形成的扫描区间相当于【’a’,’b’】。

在执行一个查询语句的时候，首先需要找出所有可用的索引以及使用他们时对应的扫描区间。接下来我们分析下怎么从包含若干个and或者or的复杂搜索条件中提取出正确的扫描区间。

1.1 所有搜索条件都可以生成合适的扫描区间的情况

在使用某个索引执行查询的时候，有时每个小的搜索条件都可以生成一个合适的扫描区间来减少需要扫描的记录数量。

  1. explain
  2. select *
  3. from single_table
  4. where key2 > 100
  5.   and key2 > 200;

4.png

在使用唯一索引进行查询的时候，这两个条件都可以形成一个扫描区间【100，+∞】，【200，+∞】。因为这两个条件是用and连接的，所以最终就是两个区间取交集【200，+∞】。

我们把sql改一改：

  1. explain
  2. select *
  3. from single_table
  4. where key2 > 100
  5.   or key2 > 200;

5.png

这个时候因为是使用or进行两个条件的连接，所以两个条件的区间应该取并集：【100，+∞】。

1.2 有的搜索条件不能生成合适的扫描区间的情况

在使用某个索引进行查询的时候，有些小的搜索条件并不能生成合适的扫描区间来减少需要扫描的行数。

  1. explain
  2. select *
  3. from single_table
  4. where key2 > 100
  5.   and common_field ='abc';

在使用唯一索引进行查询的时候，第一个条件会定位出区间【100，+∞】，但是第二个条件是一个普通条件，相当于【-∞，+∞】，因为两个条件使用and连接的，所以最终取交集之后的区间就是【100，+∞】。

其实在使用唯一索引进行查询的时候，在寻找对应的扫描区间的过程中，搜索条件common_field ='abc'没有起到任何作用，我们可以直接把这个条件进行一个等价替换【TRUE】(true对应的扫描区间也是【-∞，+∞】)。

  1. explain
  2. select *
  3. from single_table
  4. where key2 > 100
  5.   and true;

在进行化简之后就变成：

  1. explain
  2. select *
  3. from single_table
  4. where key2 > 100

也就是说上面的查询语句在使用唯一索引进行查询的时候对应的扫描区间就是【100，+∞】。

再来看一下使用OR的情况：

  1. explain
  2. select *
  3. from single_table
  4. where key2 > 100
  5.   or common_field ='abc';

同样进行化简

  1. explain
  2. select *
  3. from single_table
  4. where key2 > 100
  5.   or true;

继续化简

  1. explain
  2. select *
  3. from single_table
  4. where true

可见如果此时强制使用唯一索引进行查询，对应的扫描区间就是【-∞，+∞】，再加上这是二级索引，每次匹配到一条都要进行回表，所以这个查询的代价甚至比全表扫描还大，这个时候再使用唯一索引就没意义了。

1.3从复杂的搜索条件中找出扫描区间

来一个复杂点的条件：

  1. select *
  2. from single_table
  3. where (key1 > 'aaa' and key2 > 748)
  4.    or (key1 < 'eee' and key1 > 'ccc')
  5.    or (key1 like '%f' and key1 > 'aaa' and (key2 < 8000 or common_field = 'aaa'));

这无语的SQL怎么搞？

  1. 先看where子句里面都涉及到了哪些列，以及我们为哪些列建立了索引
  2. 对于可以用到的索引，我们来分析索引的扫描区间

1.3.1 使用idx_key1查询

先把不能形成合适扫描区间的搜索条件干掉，怎么干掉？直接把他们替换成TRUE。

替换之后的效果：

  1. select *
  2. from single_table
  3. where (key1 > 'aaa' and TRUE)
  4.    or (key1 < 'eee' and key1 > 'ccc')
  5.    or (TRUE and key1 > 'aaa' and (TRUE or TRUE));

化简之后的结果：

  1. select *
  2. from single_table
  3. where (key1 > 'aaa' ) -- 【aaa,+∞】
  4.    or (key1 < 'eee' and key1 > 'ccc') -- 【'ccc','eee'】

因为这两个条件之间是用OR连接起来的，所以我们应该取并集，最终：【aaa,+∞】。

也就是需要把所有key1在这个区间内的所有二级索引记录都取出来，针对获取到的每一条二级索引记录进行一次回表，在得到完整的用户记录之后在使用其他的搜索条件进行过滤。

1.3.2 使用唯一二级索引查询

我们还是进行化简

  1. select *
  2. from single_table
  3. where (TRUE and key2 = 748)
  4.    or (TRUE and TRUE)
  5.    or (TRUE and TRUE and (key2 < 8000 or common_field = 'aaa'));

再继续化简

  1. select *
  2. from single_table
  3. where  key2 = 748
  4.    or TRUE

因为两个条件使用OR连接的，所以最终的结果就是【-∞，+∞】。

也就是需要把所有key2所有二级索引记录都取出来，针对获取到的每一条二级索引记录进行一次回表，在得到完整的用户记录之后在使用其他的搜索条件进行过滤，比全表扫描还耗时，所以这个时候我们是不会走唯一二级索引的。

在使用idx_key1执行上述查询的时候，搜索条件 key1 like '%f' 比较特殊。虽然他不能作为形成扫描区间的边界条件，但是idx_key1的二级索引记录是包含key1列的。因此我们可以先判断获取到的二级索引记录是否符合这个条件。如果符合在执行回表操作，如果不符合就不用回表了。这样就可以较少因为回表带来的性能损耗，这就是索引下推。

1.4使用联合索引执行查询时对应的扫描区间

联合索引的索引列包含多个列，B+树中的每一层页面以及每一个页中的记录采用的排序规则比较复杂。以上面的表为例，idx_key_part (key_part1, key_part2, key_part3) 采用的排序规则如下：

  1. 先按照key_part1进行排序
  2. key_part1相同按照key_part2进行排序，以此类推

1.4.1全值匹配原理

对于下面这条查询语句来讲：

  1. select *
  2. from single_table
  3. where key_part1 ='a';

因为二级索引记录先按照key_part1进行值排序的，所以符合条件的所有记录肯定是相邻的。我们可以先定位到符合条件的第一条记录，沿着链表顺序往下扫描知道不符合条件为止（当然，对于获取到的每一条二级索引记录都需要进行回表）。此时的扫描区间【’a’,’a’】。

在看一条查询语句：

  1. select *
  2. from single_table
  3. where key_part1 = 'a'
  4.   and key_part2 = 'b';

按照联合索引的排序规则，最终的扫描区间其实就是【(‘a’,’b’),(‘a’,’b’)】。

在看一条SQL：

  1. select *
  2. from single_table
  3. where key_part1 <'a';

因为二级索引记录先按照key_part1进行值排序的，所以符合条件的所有记录肯定是相邻的。我们可以先定位到符合条件的第一条记录，然后顺着单向链表继续往后扫描，直到遇到不符合规则的记录就停止。【-∞,’a’】

1.4.2最佳左前缀匹配原理

在看一条SQL：

  1. select *
  2. from single_table
  3. where key_part2 = 'a';

由于二级索引记录不是直接按照key_part2列的值进行排序的，所以符合条件的二级索引记录可能并不相邻，也就意味着我们不能通过搜索条件来减少需要扫描的行数，这种情况下，我们是不会使用这个索引的。

在看一条SQL：

  1. select *
  2. from single_table
  3. where key_part1 = 'a'
  4.   and key_part3 = 'c';

这个时候，其实是可以按照key_part1进行过滤的，但是因为接下来是按照key_part2进行排序的，所以满足搜索条件 key_part3 = 'c'的二级索引值记录可能并不相邻，这个时候扫描区间其实就是【’a’,’a’】。因为第二个条件走不了索引。

针对获取到的每一条二级索引记录，如果没有开启索引条件下推的特性，则必须先回表获取完整的记录在来判断 key_part3 = 'c'条件是否成立，如果开启了索引下推特性，可以判断完 key_part3 = 'c'是否成立后在进行回表操作，索引下推是在MySQL5.6引入的，默认开启。

在看一条SQL：

  1. select *
  2. from single_table
  3. where key_part1 < 'a'
  4.   and key_part2 = 'c';

因为二级索引记录先按照key_part1进行值排序的，所以符合条件的所有记录肯定是相邻的。但是对于key_part1 < 'a'条件的二级索引记录来说，并不是直接按照key_part2进行排序的，也就是说我们不能根据key_part2 = 'c'来进一步减少扫描的行数。那么，如果使用当前索引执行查询，可以定位到符合key_part1 < 'a'的第一条记录，然后沿着单链表往后扫描，一直到不符合key_part1 < 'a'为止。

所以在使用当前索引执行SQL的时候，对应的扫描区间其实就是【-∞,’a’】。

在看一条SQL：

  1. select *
  2. from single_table
  3. where key_part1 <= 'a'
  4.   and key_part2 = 'c';

这条SQL和上一条SQL很像，唯一的区别就是从小于变成了小于等于。很显然符合key_part1 <= 'a'的索引值记录是连续的，但是对于符合key_part1 <= 'a'条件的二级索引记录来说，并不是直接按照key_part2列排序的。但是，对于符合key_part1 = 'a'的二级索引记录来说，是按照key_part2的值进行排序的。那么再确定需要扫描的二级索引记录的范围时，当二级索引记录的key_part1 = 'a'时，也可以通过key_part2 = 'c'来减少扫描行数，也就是说，当扫描到不符合key_part1 <= 'a' and key_part2 = 'c'的第一条记录的时候，就可以结束扫描，而不需要将所有的key_part1 = 'a'的记录全部扫描完。

2. 索引用于排序

我们在写查询语句的时候经常需要对查询出来的记录通过ORDER BY子句按照某种规则进行排序。一般情况下，我们只能把记录都加载到内存中，再用一些排序算法，比如快速排序、归并排序等等在内存中对这些记录进行排序，有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。在MySQL中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort），跟文件这个词儿一沾边儿，就显得这些排序操作非常慢了（磁盘和内存的速度比起来，就像是飞机和蜗牛的对比）。但是如果ORDER BY子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤，比如下边这个简单的查询语句：

  1. SELECT * FROM single_table ORDER BY key_part1, key_part2, key_part3 LIMIT 10;

这个查询的结果集需要先按照key_part1值排序，如果记录的key_part1值相同，则需要按照key_part2来排序，如果key_part2的值相同，则需要按照key_part3排序。因为这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。

2.1使用联合索引进行排序注意事项

对于联合索引有个问题需要注意，ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出ORDER BY key_part1, key_part3, key_part2的顺序，那也是用不了B+树索引，这种颠倒顺序就不能使用索引的原因我们上边详细说过了，这就不赘述了。

同理，ORDER BY key_part1、ORDER BY key_part1, key_part2这种匹配索引左边的列的形式可以使用部分的B+树索引。当联合索引左边列的值为常量，也可以使用后边的列进行排序，比如这样：

  1. SELECT * FROM single_table WHERE key_part1 = 'A' ORDER BY key_part2, key_part3 LIMIT 10;

这个查询能使用联合索引进行排序是因为key_part1列的值相同的记录是按照key_part2, key_part3排序的。

2.2不可以使用索引进行排序的几种情况

2.2.1ASC、DESC混用

对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。

ORDER BY子句后的列如果不加ASC或者DESC默认是按照ASC排序规则排序的，也就是升序排序的。

为啥会有这种规定呢？这个还得回头想想这个idx_key_part联合索引中记录的结构：

  • 先按照记录的key_part1列的值进行升序排列。
  • 如果记录的key_part1列的值相同，再按照key_part2列的值进行升序排列。
  • 如果记录的key_part2列的值相同，再按照key_part3列的值进行升序排列。

如果查询中的各个排序列的排序顺序是一致的，比方说下边这两种情况：

  • ORDER BY key_part1, key_part2 LIMIT 10
    这种情况直接从索引的最左边开始往右读10行记录就可以了。
  • ORDER BY key_part1 DESC, key_part2 DESC LIMIT 10，
    这种情况直接从索引的最右边开始往左读10行记录就可以了。

但是如果我们查询的需求是先按照key_part1列进行升序排列，再按照key_part2列进行降序排列的话，比如说这样的查询语句：

  1. SELECT * FROM single_table ORDER BY key_part1, key_part2 DESC LIMIT 10;

这样如果使用索引排序的话过程就是这样的：

  • 先从索引的最左边确定key_part1列最小的值，然后找到key_part1列等于该值的所有记录，然后从name列等于该值的最右边的那条记录开始往左找10条记录。
  • 如果key_part1列等于最小的值的记录不足10条，再继续往右找key_part1值第二小的记录，重复上边那个过程，直到找到10条记录为止。

这样不能高效使用索引，而要采取更复杂的算法去从索引中取数据，所以就规定使用联合索引的各个排序列的排序顺序必须是一致的。

2.2.2排序列包含非同一个索引的列

有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序，比方说：

  1. SELECT * FROM single_table ORDER BY key_part1, common_field LIMIT 10;

key_part1和common_field并不属于一个联合索引中的列，所以无法使用索引进行排序。

2.2.3排序列使用了复杂的表达式

要想使用索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式，比方说这样：

  1. SELECT * FROM single_table ORDER BY UPPER(key_part1) LIMIT 10;

使用了UPPER函数修饰过的列就不是单独的列了，这样就无法使用索引进行排序了。

3. 索引用于分组

有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组。比如下边这个分组查询：

  1. SELECT key_part1, key_part2, key_part3, COUNT(*) FROM single_table GROUP BY key_part1, key_part2, key_part3

这个查询语句相当于做了3次分组操作：

  1. 先把记录按照key_part1值进行分组，所有key_part1值相同的记录划分为一组。
  2. 将每个key_part1值相同的分组里的记录再按照key_part2的值进行分组，将key_part3值相同的记录放到一个小分组里，所以看起来就像在一个大分组里又化分了好多小分组。
  3. 再将上一步中产生的小分组按照key_part3的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把大分组分成若干个小分组，然后把若干个小分组再细分成更多的小小分组。

然后针对那些小小分组进行统计，比如在我们这个查询语句中就是统计每个小小分组包含的记录条数。如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又和我们的B+树中的索引列的顺序是一致的，而我们的B+树索引又是按照索引列排好序的，这不正好么，所以可以直接使用B+树索引进行分组。

和使用B+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组。

四，回表的代价

看下边这个查询：

  1. SELECT * FROM single_table WHERE key_part1 > 'aaa' AND key_part1 < 'zzz';

在使用idx_key_part索引进行查询时大致可以分为这两个步骤：

  1. 从索引idx_key_part对应的B+树中取出key_part1值在aaa～zzz之间的用户记录。
  2. 由于索引idx_key_part对应的B+树用户记录中只包含key_part1、key_part2、key_part3、id这4个字段，而查询列表是*，意味着要查询表中所有字段，也就是还要包括其他字段。这时需要把从上一步中获取到的每一条记录的id字段都到聚簇索引对应的B+树中找到完整的用户记录，也就是我们通常所说的回表，然后把完整的用户记录返回给查询用户。

由于索引idx_key_part对应的B+树中的记录首先会按照key_part1列的值进行排序，所以值在aaa～zzz之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这些连着的记录从磁盘中读出来，这种读取方式我们也可以称为顺序I/O。根据第1步中获取到的记录的id字段的值可能并不相连，而在聚簇索引中记录是根据id（也就是主键）的顺序排列的，所以根据这些并不连续的id值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式我们也可以称为随机I/O。一般情况下，顺序I/O比随机I/O的性能高很多，所以步骤1的执行可能很快，而步骤2就慢一些。所以这个使用索引idx_key_part的查询有这么两个特点：

  1. 会使用到两个B+树索引，一个二级索引，一个聚簇索引。
  2. 访问二级索引使用顺序I/O，访问聚簇索引使用随机I/O。

需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。比方说key_part1值在aaa～zzz之间的用户记录数量占全部记录数量90%以上，那么如果使用idx_key_part索引的话，有90%多的id值需要回表，还不如直接去扫描聚簇索引（也就是全表扫描）。

那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执行查询呢？这个就是查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。当然优化器做的分析工作不仅仅是这么简单，但是大致上是这个过程。一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择使用二级索引 + 回表的方式进行查询，因为回表的记录越少，性能提升就越高，比方说上边的查询可以改写成这样：

  1. SELECT * FROM single_table WHERE key_part1 > 'aaa' AND key_part1 < 'zzz' LIMIT 10;

添加了LIMIT 10的查询更容易让优化器采用二级索引 + 回表的方式进行查询。

对于有排序需求的查询，上边讨论的采用全表扫描还是二级索引 + 回表的方式进行查询的条件也是成立的，比方说下边这个查询：

  1. SELECT * FROM single_table ORDER BY key_part1, key_part2, key_part3;

由于查询列表是*，所以如果使用二级索引进行排序的话，需要把排序完的二级索引记录全部进行回表操作，这样操作的成本还不如直接遍历聚簇索引然后再进行文件排序（filesort）低，所以优化器会倾向于使用全表扫描的方式执行查询。如果我们加了LIMIT子句，比如这样：

  1. SELECT * FROM single_table ORDER BY key_part1, key_part2, key_part3 LIMIT 10;

这样需要回表的记录特别少，优化器就会倾向于使用二级索引 + 回表的方式执行查询。

五，更好的创建和使用索引

1. 只为了用于搜索，排序&分组的列创建索引

也就是说，只为出现在WHERE子句中的列、连接子句中的连接列，或者出现在ORDER BY或GROUP BY子句中的列创建索引。而出现在查询列表中的列就没必要建立索引了：

  1. SELECT key_part1, key_part2 FROM single_table WHERE key_part3 = 'Ashburn';

像查询列表中的key_part1、key_part2这两个列就不需要建立索引，我们只需要为出现在WHERE子句中的key_part3列创建索引就可以了。

2. 考虑列的基数

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2, 5, 8, 2, 5, 8, 2, 5, 8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。假设某个列的基数为1，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排序，无法进行快速查找了～ 而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了。所以结论就是：最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

3. 索引列的类型尽量小

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT这么几种，它们占用的存储空间依次递增，我们这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT～ 这是因为：

  1. 数据类型越小，在查询时进行的比较操作越快（这是CPU层次的东西）
  2. 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

4. 为列前缀建立索引

一个字符串其实是由若干个字符组成，如果我们在MySQL中使用utf8字符集去存储字符串的话，编码一个字符需要占用1~3个字节。假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题：

  1. B+树索引中的记录需要把该列的完整字符串存储起来，而且字符串越长，在索引中占用的存储空间越大。
  2. 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

索引列的字符串前缀其实也是排好序的，所以索引的设计者提出了个方案 —- 只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再对比就好了。这样只在B+树中存储字符串的前几个字符的编码，既节约空间，又减少了字符串的比较时间，还大概能解决排序的问题。

5. 覆盖索引

为了彻底告别回表操作带来的性能损耗，建议：最好在查询列表里只包含索引列，比如这样：

  1. SELECT key_part1, key_part2 FROM single_table WHERE key_part3 = 'Ashburn';

因为我们只查询key_part1, key_part2, 这2个索引列的值，所以在通过idx_key_part索引得到结果后就不必到聚簇索引中再查找记录的剩余列，这样就省去了回表操作带来的性能损耗。我们把这种只需要用到索引的查询方式称为索引覆盖。排序操作也优先使用覆盖索引的方式进行查询，比方说这个查询：

  1. SELECT  key_part1, key_part2, key_part3  FROM person_info ORDER BYkey_part1, key_part2, key_part3;

虽然这个查询中没有LIMIT子句，但是采用了覆盖索引，所以查询优化器就会直接使用idx_key_part索引进行排序而不需要回表操作了。

当然，如果业务需要查询出索引以外的列，那还是以保证业务需求为重。但是尽量不要用用*号作为查询列表，最好把需要查询的列依次标明。

6.不要乱动列名

假设表中有一个整数列my_col，我们为这个列建立了索引。下边的两个WHERE子句虽然语义是一致的，但是在效率上却有差别：

  1. WHERE my_col * 2 < 4
  2. WHERE my_col < 4/2

第1个WHERE子句中my_col列并不是以单独列的形式出现的，而是以my_col * 2这样的表达式的形式出现的，存储引擎会依次遍历所有的记录，计算这个表达式的值是不是小于4，所以这种情况下是使用不到为my_col列建立的B+树索引的。而第2个WHERE子句中my_col列并是以单独列的形式出现的，这样的情况可以直接使用B+树索引。

所以结论就是：如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

7. 尽量维持有序插入

对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，这就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间：

6.png

如果此时再插入一条主键值为9的记录，那它插入的位置就如下图：

7.png

可这个数据页已经满了，再插进来咋办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有**AUTO_INCREMENT**，让存储引擎自己为表生成主键，而不是我们手动插入 。

8.冗余和重复索引

我们知道，通过idx_key_part索引就可以对key_part1列进行快速搜索，再创建一个专门针对key_part1列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

至此，索引命中的原理和我们在建立索引的时候应该注意什么就分析完了，好家伙，又是一个通宵。