【高并发高性能高可用之海量数据MySQL实战-15】-MySQL索引实现

1、MyISAM索引

我们以t_user_myisam为例，来说明。t_user_myisam的id列为主键，age列为普通索引。 

 CREATE TABLE `t_user_myisam` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(20) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE,
 KEY `idx_age` (`age`) USING BTREE
 ) ENGINE=MyISAM;  

MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的      键值为索引列的值，数据为索引所在行的磁盘地址。

1：主键索引

 表t_user_myisam的索引存储在索引文件t_user_myisam.MYI中，数据文件存储在数据文件t_user_myisam.MYD中。

等值查询数据 

select * from t_user_myisam where id=30; 

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）

2. 将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）

3. 检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项。（1次磁盘IO）

4. 从索引项中获取磁盘地址，然后到数据文件t_user_myisam.MYD中获取对应整行记录。（1次磁盘IO）

5. 将记录返给客户端。

磁盘IO次数：3+1次。 

范围查询数据 

select * from t_user_myisam where id between 30 and 49; 

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）

2. 将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）

3. 检索到叶节点，将节点加载到内存中遍历比较20<30，30<=30<49。查找到值等于30的索引项。根     据磁盘地址从数据文件中获取行记录缓存到结果集中。（2次磁盘IO）

我们的查询语句时范围查找，需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。

4. 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，30<49<=49，根据磁盘地址从数据文件中获取行记录缓存到结果集中。（2次磁盘IO）

5. 最后得到两条符合筛选条件，将查询结果集返给客户端。

磁盘IO次数：2+检索叶子节点数量+记录数。 

MyISAM在查询时，会将索引节点缓存在MySQL缓存中，而数据缓存依赖于操作系统自身的缓存。 

2： 辅助索引

在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也  需要按照范围查询的方式在辅助索引树中检索数据。

2、InnoDB索引

1： InnoDB索引简介

InnoDB索引-官方文档：MySQL :: MySQL 5.7 Reference Manual :: 14.6.2.1 Clustered and Secondary Indexes

每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：

1. 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。

2. 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。

3. 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

下面我们一起来看一看这两种索引的实现。

我们以t_user_innodb为例，来说明。t_user_innodb的id列为主键，age列为普通索引。

t_user_innodb的表结构和数据与MyISAM引擎表t_user_myisam完全一致。 

 CREATE TABLE `t_user_innodb` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(20) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE,
 KEY `idx_age` (`age`) USING BTREE
 ) ENGINE=InnoDB; 

InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织方式，是聚簇索引。

2：主键索引

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

InnoDB要求表必须有一个主键索引(MyISAM 可以没有)。

等值查询数据 

select * from t_user_innodb where id=30; 

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）

2. 将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）

3. 检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）  

磁盘IO次数：3次。

范围查询数据 

select * from t_user_innodb where id between 30 and 49; 

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）

2. 将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）

3. 检索到叶节点，将节点加载到内存中遍历比较20<30，30<=30<49。查找到值等于30的索引项。获     取行数据缓存到结果集中。（1次磁盘IO）

4. 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，30<49<=49，获取行数据缓存到结果集中。（1次磁盘IO）

5. 最后得到2条符合筛选条件，将查询结果集返给客户端。 

可以看到，因为在主键索引中直接存储了行数据，所以InnoDB在使用主键查询时可以快速获取行数 据。当表很大时，与在索引树中存储磁盘地址的方式相比，因为不用再去磁盘中获取数据，所以聚簇索  引通常可以节省磁盘IO操作。

磁盘IO次数：2次+检索叶子节点数量。 

3：辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。以表t_user_innodb的age列为例，age索引的索引结果如下图。

底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。

使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。 

等值查询数据 

select * from t_user_innodb where age=22; 

1. 先在索引树中从根节点开始检索，将根节点加载到内存，比较22<77，走左路。（1次磁盘IO）

2. 将左子树节点加载到内存中，比较22<34，向下检索。（1次磁盘IO）

3. 检索到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘IO） 第一项5：5<22不符合要求，丢弃。

第二项22：等于22，符合要求，获取主键id=18，去主键索引树中检索id=18的数据放入结果集中。（回表：3次磁盘IO）。

第三项22：等于22，符合要求，获取主键id=49，去主键索引树中检索id=49的数据放入结果集中。（回表：3次磁盘IO）

22=22,22=22。查找到值等于30的索引项，直接可以获取整行数据。将改记录返回给客户端。

4. 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较。（1次磁盘IO） 第一项34：34>22不符合要求，丢弃。查询结束。

5. 最后得到2条符合筛选条件，将查询结果集返给客户端。 

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。 

磁盘IO次数：2次+检索叶子节点数量+记录数*3。

范围查询数据

select * from t_user_innodb where age between 30 and 49; 

辅助索引的范围查询流程和等值查询基本一致，先使用辅助索引到叶子节点检索到第一个符合条件的  索引项，然后向后遍历，直到遇到第一个不符合条件的索引项，终止。

检索过程中需要将符合筛选条件的id值，依次到主键索引检索将检索的数据放入结果集中。 最后将查询结果返回客户端。 

4：组合索引

1. 组合索引存储结构

我们在使用索引时，组合索引是我们常用的索引类型。那组合索引是如何构建的，查找的时候又是如何  进行查找的呢？

表t_multiple_index，id为主键列，创建了一个联合索引idx_abc(a,b,c)，构建的B+树索引结构如图所示。索引树中节点中的索引项按照（a，b，c）的顺序从大到小排列，先按照a列排序，a列相同时按照b 列排序，b列相同按照c列排序。在最地城的叶子节点中，如果两个索引项的a，b，c三列都相同，索引 项按照主键id排序。

所以组合索引的最底层叶子节点中不存在完全相同的索引项。

 CREATE TABLE `t_multiple_index` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 `c` varchar(10) DEFAULT NULL,
 `d` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE, 8 KEY `idx_abc` (`a`,`b`,`c`)
) ENGINE=InnoDB;

2. 组合索引的查找方式 

1. 先在索引树中从根节点开始检索，将根节点加载到内存，先比较a列，a=14，14>13,走左路。（1 次磁盘IO）

2. 将左子树节点加载到内存中，先比较a列，a=13，比较b列b=14，14<16，走右路，向下检索。（1    次磁盘IO）

3. 达到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘IO）

第一项（13,14,3,id=4）：先比较a列，a=13，比较b列b=14，b!=16不符合要求，丢弃。

第二项（13,14,4,id=1）：一样的比较方式，a=13，b=16，c=4 满足筛选条件。取出索引data值即主键id=1，再去主键索引树中检索id=1的数据放入结果集中。（回表：3次磁盘IO）

第三项（13,14,5,id=3）：a=13，b=16，c!=4 不符合要求，丢弃。查询结束。

4. 最后得到1条符合筛选条件，将查询结果集返给客户端。 

3. 最左前缀匹配原则

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。

所以当我们使用 where a=13 and b=16 and c=4去查询数据的时候，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道 第一步应该从哪个节点查起。 

所以联合索引只能从第一列开始查找，比如以下三个查询都可以使用idx_abc索引树，检索数据。 

select * from t_multiple_index where a=13;
select * from t_multiple_index where a=13 and b=16;
select * from t_multiple_index where a=13 and b=16 and c=4;
select * from t_multiple_index where a=13 and b>13;
select * from t_multiple_index where a>11 and b=14;
select * from t_multiple_index where a=13 and c=4; 

而如果查询条件不包含a列，比如筛选条件只有(b，c)或者c列是无法使用组合索引的。下面的查询没有用到索引。

select * from t_multiple_index where b=16 and c=4;
select * from t_multiple_index where c=4;

所以创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。

组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

另外，我们还需要注意的是，书写SQL条件的顺序，不一定是执行时候的where条件顺序。优化器会帮助我们优化成索引可以识别的形式。比如:

select * from t_multiple_index where b=16 and c=4 and a=13;
#等价于下面的sql，优化器会按照索引的顺序优化
select * from t_multiple_index where a=13 and b=16 and c=4;

一颗索引树等价与三颗索引树，从另一方面了说，组合索引也为我们节省了磁盘空间。所以在业务中  尽量选用组合索引，能使用组合索引就不要使用单列索引。

索引使用口诀

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上不计算，范围之后全失效；

Like百分写最右，覆盖索引不写星；

不等空值还有OR，索引失效要少用。

4. 组合索引创建原则

1. 频繁出现在where条件中的列，建议创建组合索引。

2. 频繁出现在order by和group by语句中的列，建议按照顺序去创建组合索引。

order by a,b 需要组合索引列顺序（a,b）。如果索引的顺序是（b,a），是用不到索引的。

3. 常出现在select语句中的列，也建议创建组合索引。

对于第1种情况和第3种情况，组合索引创建的顺序对其来说是等价的，这种情况下组合索引中的  顺序，是很重要的。由于组合索引会使用到最左前缀原则，使用频繁的列在创建索引时排在前面。

下面的SQL语句除了建ab联合索引，还有更好的方案吗？

select * from t where a=1 and b>2 order by c

可以考虑建立 （a,c）联合索引：select * from xxx where a=1 and b>2 order by c 这样 a等值查询c就是已经排好序的了。这种情况实际上比较的是b的区分度和c的区分度，如果b的区分度比较差，建议使用ac。如果c的区分度比较差，建议使用a,b。

5：覆盖索引

前面我们提到，根据在辅助索引树查询数据时，首先通过辅助索引找到主键值，然后需要再根据主键  值到主键索引中找到主键对应的数据。这个过程称为回表。

使用辅助索引查询比基于主键索引的查询多检索了一棵索引树，那是不是所有使用辅助索引的查询都  需要回表查询呢？

表t_multiple_index，组合索引idx_abc(a,b,c)的叶子节点中包含(a,b,c,id)四列的值，对于以下查询语句 

select a from t_multiple_index where a=13 and b=16;
select a,b from t_multiple_index where a=13 and b=16;
select a,b,c from t_multiple_index where a=13 and b=16;
select a,b,c,id from t_multiple_index where a=13 and b=16; 

select中列数据，如果可以直接在辅助索引树上全部获取，也就是说索引树已经“覆盖”了我们的查询需求，这时MySQL就不会白费力气的回表查询，这中现象就是覆盖索引。使用explain工具查看执行计划，可以看到extra中“Using index”，代表使用了覆盖索引。 

大家试试将上面的语句，改为如下语句。大家猜猜这时会不会用到组合索引？

select a,b from t_multiple_index where b=16; 

上面的查询语句用到了覆盖索引进行全表扫描。MySQL基于成本考虑，会使用了覆盖索引进行全表扫描，使用覆盖索引可以减少了磁盘IO次数，显著提升查询性能。

覆盖索引相比与主键索引一个索引项占用的空间少，覆盖索引一个叶子节点中的就可以比主键索引存  放更多的数据量，相应的存放数据用到的总叶子树很少一些。

覆盖索引是一种很常用的优化手段。

6：索引条件下推ICP

官方索引条件下推： Index Condition Pushdown,简称ICP。是MySQL5.6对使用索引从表中检索行的一种优化。可以通过参数optimizer_switch控制ICP的开始和关闭。

#optimizer_switch优化相关参数开关
mysql> show VARIABLES like 'optimizer_switch'\G;
#关闭ICP
SET optimizer_switch = 'index_condition_pushdown=off';
#开启ICP
SET optimizer_switch = 'index_condition_pushdown=on';

ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。可用于和MyISAM 表，对于InnoDB表ICP仅用于辅助索引。

我们以InnoDB的辅助索引为例，来讲解ICP的作用。

大家有没有一个疑问，MySQl在使用组合索引在检索数据时是使用最左前缀原则来定位记录，那不满足最左前缀的索引列，MySQL会怎么处理？

select * from t_multiple_index where a=13 and b>15 and c='5' and d='pdf';

 

根据最左前缀匹配原则，这个SQL语句会使用组合索引idx_abc(a,b,c)的（a,b）两列来检索记录。

MySQL首先会在组合索引中定位到第一个满足a=13 and b>=15的索引项，MySQL之后会怎么处理呢？使用explain工具，查看执行计划，extra列中的“Using index condition”执行器表示使用了索引条件下推ICP。

ICP是MySQL 5.6引入的新特性，在使用ICP和不使用ICP时MySQL的执行情况会有所不同。

在MySQL 5.6之前， 不使用ICP时，MySQL只能从索引项（13,16,4,1）开始，一个个回表查询找到行数据，然后再在服务层过滤后，返回给客户端。

关闭ICP，使用explain工具，查看执行计划，extra列中的“Using where”执行器表示没有使用了索引条件下推ICP。

 

具体步骤如下：

1. 执行器使用索引(a,b,c)，筛选条件a=13 and  b>=15，调用存储引擎"下一行"接口。根据最左前缀原则联合索引检索定位到索引项（13,16,4,id=1），然后使用id=1回表查询，获得id=1的行记录。返    回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf'过滤，不符合要求，直接丢弃。

2. 执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=3）,使用id=3回表获得id=3  的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf'过滤，符合要求， 缓存到结果集。

3. 执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=6）,使用id=6回表获得id=6  的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf'过滤，不符合要求，直接丢弃。

4. 执行器调用"下一行"接口，存储引擎遍历向后找到索引项（14,14,14,id=8）不满足筛选条件，执行器终止查询。

5. 最终获取一条记录，返回给客户端。

可以看到，在不使用ICP时，回表查询了3次，然后在服务层筛选后（筛选3次），最后返回客户端。

在MySQL 5.6 引入了ICP，可以在索引遍历过程中，对where中包含的索引条件先做判断，只有满足条件的才会回表查询读取行数据。这么做可以减少回表查询，从而减少磁盘IO次数。 

使用ICP时，具体步骤如下：

1. 执行器使用索引(a,b,c)，筛选条件a=13 and b>=15 and c=5，调用存储引擎"下一行"接口。根据最左前缀原则联合索引检索定位到索引项（13,16,4,id=1），然后使用ICP下推条件c=5判断，不满足条件，直接丢弃。

向后遍历判断索引项（13,16,5,id=3），满足筛选条件a=13 and b>=15 and c=5，使用id=3回表获得id=3的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件d='pdf'过滤，符合要求，缓存到结果集。

2. 执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=6）,满足筛选条件a=13 and b>=15 and c=5，使用id=6回表获得id=6的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件d='pdf'过滤，不符合要求，直接丢弃。

3. 执行器调用"下一行"接口，存储引擎遍历向后找到索引项（14,14,14,id=8）不满足筛选条件，执行      器终止查询。

4. 最终获取一条记录，返回给客户端。

可以看到，在使用ICP时，回表查询了2次，然后在服务层筛选后（筛选2次），最后返回客户端。

不使用ICP，不满足最左前缀的索引条件的比较是在Server层进行的，非索引条件的比较是在Server层  进行的。

使用ICP，所有的索引条件的比较是在存储引擎层进行的，非索引条件的比较是在Server层进行的。

对比使用ICP和不使用ICP，可以看到使用ICP可以有效减少回表查询次数和返回给服务层的记录数，从而减少了磁盘IO次数和服务层与存储引擎的交互次数。

如果您觉得文章好看，欢迎点赞收藏加关注，一连三击呀，您的肯定是我持续输出的动力，感谢！！☺☻