Mysql深度讲解InnoDB引擎与Index索引（三）

前言

上一篇【Mysql深度讲解InnoDB引擎与Index索引（二）】经过一系列的推导，最终推导出来索引(Index)的底层原理其实就是一棵B+树，那么本篇就会详细说下索引应该怎么用，以及索引的优化方案又有哪些。更多Mysql调优内容请点击【Mysql优化-深度讲解系列目录】。

创建样例表

为了说明这个例子，还是创建一个Sample表，和上篇的一样t1表，按顺序插入数据。

create table t1(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=InnoDB;

insert into t1 values(1,6,7,4,'d');
insert into t1 values(3,3,3,3,'h');
insert into t1 values(4,2,1,7,'g');
insert into t1 values(6,4,5,4,'b');
insert into t1 values(7,9,3,6,'f');
insert into t1 values(9,1,1,1,'c');
insert into t1 values(12,1,2,4,'a');
insert into t1 values(15,2,2,5,'e');

主键索引（聚集索引）

我们已经推导出来InnoDB默认的索引(Index)是一个以主键排序的B+树，其实也可以称作主键索引。我们通过InnoDB创建表的时候，顺带创建的默认索引就是这样一种数据结构。根据上篇的分析，在索引(Index)中，数据和索引其实是在一起的，所以这种结构就被称为聚集索引。这里可以看出，InnoDB要排序必须有一个主键，那么建表的时候没有指定怎么办？其实如果没有指定，InnoDB会默认第一个Unique的列为主键，如果连Unique的列也没有指定，那么InnoDB会自动取找一个隐藏的列row_id作为主键。注：这个列将会在找不到指定主键和Unique列的情况下才会有。

辅助索引（二级索引、联合索引）

除了主键做索引以外，还会对个别的列创建索引。其实底层做的就是对这一列(多列) 进行一个排序，因为做索引是为了查询快，查询快就必须先排序。比如我们要根据b这一列创建索引，那么做的第一件事情就是对所有行的数据根据b这一列的字段内容进行排序。除了对单独的列建索引以外，还可以建多个列组合的索引，比如给b、c、d三个列一起创建一个索引，这样的索引就叫做联合索引。不管索引的形式是怎么样的，除了主键索引以外，所有其他的索引都被称为辅助索引，或者二级索引。注：这种二级索引，主键和数据将不会在一起，具体后面分析。

字符集编码与排序规则

一般来说主键应该尽量小，使用int之类，不建议使用类似uuid这种很长的字符串作为主键。因为在Mysql中有很多的字符集，他们的编码类型不同。比如utf8，mysql中的utf8是三个子节的0-3，而一般理解的utf8是四个子节的0-4，mysql里面还有一个类型是utf8mb4才是正常意义下的utf8。比如latin编码方式占1个子节。而不同的字符集有不同的排序规则，比如我们以为a<b，可能换一个排序规则就会变为a>b。
图例：众多的字符集编码方式

图例：每个字符集都有多个排序规则

如果使用很长的字符串作为主键就会放大一些缺点：第一、排序会非常的耗时；第二、主键就会过多占用行数据的大小，那么数据就很有可能被挤到下一页占用额外的空间，增加查找时间。所以设置一个小的主键也是可以帮助提高查询效率的。

联合索引底层解析

一般情况下，主键索引已经由InnoDB创建，而且上篇也已经对主键索引做了一个详细的分析，所以这里说的索引的自然就是辅助索引，就以样例表的b、c、d列为例，做一个联合索引的详细讲解，看一下联合索引底层是什么样的。

建立索引命令：create index idx_t1_bcd on t1(b,c,d)

既然要对b、c、d建立索引，第一步肯定还是要排序。三个字段排序，肯定要有一个比较顺序，这个顺序就是创建索引的时候写入的顺序。我们命令里的顺序是b列、c列、d列，那么排序也会先比较b列的大小，然后比较c列的大小，最后比较d列的大小，这样一个顺序去对整个表的数据行进行排序。如果b列的大小无法确定，再比较c列的大小，以此类推。最终建立联合索引的时候也会建立一个B+树，其实就是把表里的数据按照新的规则重新创建一个新的B+树。为了更好的说明联合索引的排序结构，首先我们先画出来t1主键索引的样子。

主键索引创建出来以后，按照主键索引的逻辑，进而创建出联合索引，由于主键索引已经把所有的数据存在了其叶子节点上，因此作为辅助索引的联合索引便不需要存储具体的数据到其叶子节点上，只需要存储主键索引的主键值就可以了，这也就是为什么只有主键索引被称为聚集索引的缘由。为什么不直接存数据呢？可以设想一下，如果每建一个索引就存一份数据，创建多个索引以后数据的冗余就太大了，所以不会存放数据。根据上述分析，就可以得到下面的联合索引结构。

无论如何都会取当前最小的索引值作为key，最下面就是辅助索引对应的叶子节点，最终叶子节点对应的就是相应行的主键索引key值。

利用索引查找数据

既然索引已经创建出来，查找数据的时候就可以利用索引提升查找的效率，比如下面的sql语句就一定可以利用到这个索引。

select * from t1 where b=1 and c=1 and d=1;

既然查询条件是b=1 and c=1 and d=1和select * 就代表要查询的是这一整行数据，那么根据我们建立的索引，先会去idx_t1_bcd索引里取找b=1、c=1、d=1，找到主键9，然后再去主键索引去把整行的数据拿出来。这就是一个大概的索引查找思路。

使用索引的条件

我们现在已经有了一个索引的查找基准，根据这个基准就可以推算出索引的利用条件。什么情况下会用到索引呢？

第一种情况：全值匹配原则

比如我们上面例子中b列、c列、d列都进入条件判断了，肯定可以利用到索引。接下来就一步一步的分析一下，这个例子是如何利用索引的。首先在入口页匹配到111，然后进入二级目录页匹配到111，再进入到叶节点匹配到111，根据主键1去主键索引拿出主键为1的行值。这一系列的步骤可以说：一个查询能不能利用到索引，其就是再说当前的查询条件，能不能基于索引去缩小查询结果集的范围。再比如：要查询112，那么333这边的右子树就不需要再去查找了，因为这个分支最小的就是333，一定不会有112存在，这样查询的结果集就被缩小了，说明索引idx_t1_bcd对这个查询是有效果的，能够减少这次查询范围，也可以说当前查询条件可以利用到索引。

第二种情况：最左匹配原则

select * from t1 where b=1;
select * from t1 where b=3 and c=3;

比如上述两个查询条件，也是可以利用到索引的。因为b=1这个条件也是可以通过索引过滤的。根据我们画出来的B+树，很明显无论c列或者d列数据为多少，只要b列为1，就一定会走到左子树里，也就减少了查询的结果集。第二个例子中条件b=3 and c=3，会首先走右子树的查询，然后再走二层左子树，同样减少了查询结果集，因此也是可以使用的。那么上面的例子说明除了全值匹配，最左值匹配原则同样可以利用到索引。

无法适应的匹配

什么情况下创建的索引会失效呢？

第一种情况：不适应最左原则

select * from t1 where  c=3;

比如上面的这条sql语句，这条语句就无法使用索引idx_t1_bcd，因为无法确定c=3到底在哪个子树里，相当于最左边的值是无法确定的，最终还是会导致Mysql进行全表匹配。

第二种情况：like字符双向匹配符

select * from t1 where b like '%1%';

这种情况下索引idx_t1_bcd也是无法使用的，因为此时也是无法确定索引子树的范围具体在哪里，如果要使用索引修改为b like '1%'即可，其实还是必须符合最左原则。

排序如何利用索引

select * from t1 order by b,c,d;

上面的排序语句理论上也是可以用到索引的，但在Mysql中order by命令会根据数据的容量进行优化，因此小批量数据可能无法用应用到索引。注意到order by后面的顺序一定要和创建出来的索引顺序一样，如果是order by c,b,d，或者order by d,c是无论如何都无法使用索引去提高查询效率的。还有一点要注意ASC和DECS不能够混用，比如order by b ASC, c DESC，也是不生效的。

索引的代价

首先没创建一个索引，都会构建一个B+树，这些内容会放在磁盘上，对空间进行一些消耗。其次对一个表建立很多索引，就会极大的影响修改和删除的时间，因为每次修改或者删除都会对索引进行一个重新构建，因此时间上就会有所消耗。过多的索引在空间上和时间上都会有代价，因此对表里每一个字段都做索引是非常错误的做法。

总结

本篇详细说了Mysql中索引的底层原理，以及根据原理延伸出来的索引适用情况，其实只要把握一个原则即可，那就是最左原则，因为只有最左边的值确定下来了，才能够定位并减少查询字数的范围，进而我们创建的索引才是有价值的。因而引出了一系列的sql语句匹配索引的条件，比如like的匹配方式，exist的匹配方式，包括大于小于的条件语句组合，甚至包括order等等。但是本篇博客基本上是从理论上说明了索引的使用方式，创建方式，以及索引是如何帮助我们优化查询的。有利就必然有弊，创建索引也是有代价，所以创建一个合理高效的索引就是提交性能的关键所在。那么下一篇【Mysql深度讲解InnoDB引擎与Index索引（四）】将会讲解什么是一个高效的索引，以及索引的选择性是如何计算的。