mysql innodb索引原理

                                     mysql innodb索引原理

本文完全取自 张鹏程老师的博客，另注有个人的学习笔记    http://baijiahao.baidu.com/s?id=1598257553176708891&wfr=spider&for=pc

 

1、聚簇索引

每个InnoDB的表都拥有一个索引，称之为聚簇索引，此索引中存储着行记录，一般来说，聚簇索引是根据主键生成的。为了能够获得高性能的查询、插入和其他数据库操作，理解InnoDB聚簇索引是很有必要的。

聚簇索引整体是一个b+树，非叶子节点存放的是键值，叶子节点存放的是行数据，称之为数据页，这就决定了表中的数据也是聚簇索引中的一部分，数据页之间是通过一个双向链表来链接的，上文说到B+树是一棵平衡查找树，也就是聚簇索引的数据存储是有序的，但是这个是逻辑上的有序，但是在实际在数据的物理存储上是，因为数据页之间是通过双向链表来连接，假如物理存储是顺序的话，那维护聚簇索引的成本非常的高。（标注：innodb表空间分为段，区，页，行，其中段分为数据段，索引段，回滚段，b+树的叶子节点存在数据段的，非叶子节点存的索引标识（非空唯一索引），回滚段存的是undo日志，用于事务回滚的和获取之前版本，innodb1.1之前只支持1个回滚段，支持1023个并发同时进行，1.2之后，支持128个回滚段，也就是同时支持的并发量是128*1023，另外innodb中还有一种日志叫redo 日志在其他博客中再说道。）

聚簇索引按照如下规则创建：

1. 当定义了主键后，InnoDB会利用主键来生成其聚簇索引；
2. 如果没有主键，InnoDB会选择一个非空的唯一索引来创建聚簇索引；
3. 如果这也没有，InnoDB会隐式的创建一个自增的列来作为聚簇索引；

注意：对于选择唯一索引的顺序是按照定义唯一索引的顺序，而非表中列的顺序, 同时选中的唯一索引字段会充当为主键，或者InnoDB隐式创建的自增列也可以看做主键。

2、辅助索引

除了聚簇索引之外的索引都可以称之为辅助索引，与聚簇索引的区别在于辅助索引的叶子节点中存放的是主键的键值。一张表可以存在多个辅助索引，但是只能有一个聚簇索引，通过辅助索引来查找对应的行记录的话，需要进行两步，第一步通过辅助索引来确定对应的主键，第二步通过相应的主键值在聚簇索引中查询到对应的行记录，也就是进行两次B+树搜索。相反通过辅助索引来查询主键的话，遍历一次辅助索引就可以确定主键了，也就是所谓的索引覆盖，不用回表（查询聚簇索引）。

创建辅助索引，可以创建单列的索引，也就是用一个字段来创建索引，也可以用多个字段来创建副主索引称为联合索引，创建联合索引后，B+树的节点存储的键值数量不是1个，而是多个，如下图：

 

联合索引的B+树和单键辅助索引的B+树是一样的，键值都是排序的，通过叶子节点可以逻辑顺序的读出所有的数据，比如上图所存储的数据时，按照(a,b)这种形式(1,1),(1,2),(2,1),(2,4),(3,1),(3,2)进行存放，这样有个好处存放的数据时排了序的，当进行order by对某个字段进行排序时，可以减少复杂度，加速进行查询；

当用select * from table where a=? 可以使用索引(a,b)来加速查询，但是在查询时有一个原则，sql的where条件的顺序必须和二级索引一致（这句话有点没明白，如果说的是where条件的顺序应该和联合索引索引的字段顺序一致吗，不就是后文说的最左原则吗，另外如果用到多个索引查询时查询优化器自然会对where子句的顺序进行重新排序），而且还遵循索引最左原则，select * from table where b=?则无法利用(a,b)索引来加速查询。

辅助索引还有一个概念便是索引覆盖，索引覆盖的一个好处便是辅助索引不包含行记录，因此其大小远远小于聚簇索引，利用辅助索引进行查询可以减少大量的IO操作。

3、SQL执行顺序

以下图几乎完整的展现出一个sql的所以信息了。每一步操作都会生成一个虚拟表，作为下一个处理的输入，在这个过程中，这些虚拟表对于用户都是透明的，只用最后一步执行完的虚拟表返回给用户，在处理过程中，没有的步骤会直接跳过。

(1) from：对左表left-table和右表right-table执行笛卡尔积(a*b)，形成虚拟表VT1;

(2) on: 对虚拟表VT1进行on条件进行筛选，只有符合条件的记录才会插入到虚拟表VT2中;（这不确定是先生成笛卡尔积再进行筛选呢，还是在边生成边筛选，个人理解认为后者策略效率更好）

(3) join: 指定out join会将未匹配行添加到VT2产生VT3,若有多张表，则会重复(1)~(3);

(4) where: 对VT3进行条件过滤，形成VT4, where条件是从左向右执行的;

(5) group by: 对VT4进行分组操作得到VT5;

(6) cube | rollup: 对VT5进行cube | rollup操作得到VT6;（cube mysql5.6是不支持的）

(7) having: 对VT6进行过滤得到VT7;

(8) select: 执行选择操作得到VT8，本人看来VT7和VT8应该是一样的;

(9) distinct: 对VT8进行去重，得到VT9;

(10) order by: 对VT9进行排序，得到VT10;

(11) limit: 对记录进行截取，得到VT11返回给用户。

这里具体的 on 和 where 和 having 就不在这里区别了。

4、SQL优化建议

索引有如下优点：减少服务器扫描的数据量、避免排序和临时表、将随机I/O变为顺序I/O。

可使用B+树索引的查询方式：

• 全值匹配：与索引中的所有列进行匹配，也就是条件字段与联合索引的字段个数与顺序相同；
• 匹配最左前缀：只使用联合索引的前几个字段；
• 匹配列前缀：比如like 'xx%'可以走索引；
• 匹配范围值：范围查询，比如>,like等（这里也有like不明白）；
• 匹配某一列并范围匹配另外一列：精确查找+范围查找；
• 只访问索引查询：索引覆盖，select的字段为主键；
• 前缀索引

范围查询后的条件不会走索引，具体原因会在下一节进行介绍（这个还真不清楚为啥）。

列的选择性（区分度）

选择性（区分度）是指不重复的列值个数/列值的总个数，一般意义上建索引的字段要区分度高，而且在建联合索引的时候区分度高的列字段要放在前边，这样可以在第一个条件就过滤掉大量的数据，有利用性能的提升，对于如何计算列的区分度，有如下两种方法：

• 根据定义，手动计算列的区分度，不重复的列值个数/列值的总个数；
• 通过 MySQL的carlinality，通过命令show index from来查看(但是这个不一定准确)（https://blog.csdn.net/cdx_akang/article/details/83900427）；

但是选择区分度高的列作为索引也不是百试百灵的，某些情况还是不合适的，下节会进行介绍（这个还真不清楚为啥）。

MySQL查询过程

当希望 MySQL能够高性能运行的时候，最好的办法就是明白 MySQL是如何优化和执行的，一旦理解了这一点，很多查询优化工作实际上就是遵循了一些原则让优化器能够按照预想的合理的方式运行。

当想 MySQL实例发送一个请求时， MySQL按照如下图的方式进行查询：

• 客户端先发送一条查询给服务器；
• 服务器先检查查询缓存，如果命中了缓存，则立刻返回给存储在缓存中的结果，否则进入下一个阶段；
• 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
• MySQL 根据优化器生成的执行计划，调用存储引擎的API来执行查询；
• 将结果返回客户端。

注意&建议

• 主键推荐使用整型，避免索引分裂；
• 查询使用索引覆盖能够提升很大的性能，因为避免了回表查询；
• 选择合适的顺序建立索引，有的场景并非区分度越高的列字段放在前边越好，联合索引使用居多；
• 合理使用in操作将范围查询转换成多个等值查询；
• in操作相当于多个等值操作，但是要注意的是对于order by来说，这相当于范围查询，因此例如select * from t1 where c1 in (x,x) order by c2的sql是不走索引的；
• 将大批量数据查询任务分解为分批查询；
• 将复杂查询转换为简单查询；
• 合理使用inner join，比如说分页时候。

以上建议目前部分理解得还不是很透彻，需要进一步学习。

5、问题分析

索引分裂

此处提一下索引分裂，就我个人理解，在 MySQL插入记录的同时会更新配置的相应索引文件，根据以上的了解，在插入索引时，可能会存在索引的页的分裂，因此会导致磁盘数据的移动。当插入的主键是随机字符串时，每次插入不会是在B+树的最后插入，每次插入位置都是随机的，每次都可能导致数据页的移动，而且字符串的存储空间占用也很大，这样重建索引不仅仅效率低而且 MySQL的负载也会很高，同时还会导致大量的磁盘碎片，磁盘碎片多了也会对查询造成一定的性能开销，因为存储位置不连续导致更多的磁盘I/O,这就是为什么推荐定义主键为递增整型的一个原因， MySQL索引页默认大小是16KB，当有新纪录插入的时候， MySQL会留下每页空间的1/16用于未来索引记录增长，避免过多的磁盘数据移动。

自增主键的弊端

对于高并发的场景，在InnoDB中按照主键的顺序插入可能会造成明显的争用，主键的上界会成为“热点”，因为所有的插入都发生在此处，索引并发的插入可能会造成间隙锁竞争，何为间隙锁竞争。

另外一个原因可能是Auto_increment的锁机制，在 MySQL处理自增主键时，当

innodb_autoinc_lock_mode

为0或1时，在不知道插入有多少行时，比如

insert t1 xx select xx from t2

，对于这个statement的执行会进行锁表，只有这个statement执行完以后才会释放锁，然后别的插入才能够继续执行，但是在

innodb_autoinc_lock_mode=2

时，这种情况不会存在表锁，但是只能保证所有并发执行的statement插入的记录是唯一并且自增的，但是每个statement做的多行插入之间是不连接的。

innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为；（https://www.cnblogs.com/JiangLe/p/6362770.html）

优化器不使用索引选择全表扫描

比如一张order表中有联合索引(order_id, goods_id)，在此例子上来说明这个问题是从两个方面来说：

• 查询字段在索引中

select order_id from order where order_id > 1000   ,如果查看其执行计划的话，发现是用use index condition,走的是索引覆盖。

• 查询字段不在索引中

select * from order where order_id > 1000, 此条语句查询的是该表所有字段，有一部分字段并未在此联合索引中，因此走联合索引查询会走两步，首先通过联合索引确定符合条件的主键id,然后利用这些主键id再去聚簇索引中去查询，然后得到所有记录，利用主键id在聚簇索引中查询记录的过程是无序的，在磁盘上就变成了离散读取的操作，假如当读取的记录很多时（一般是整个表的20%左右），这个时候优化器会选择直接使用聚簇索引，也就是扫全表，因为顺序读取要快于离散读取，这也就是为何一般不用区分度不大的字段单独做索引，注意是单独因为利用此字段查出来的数据会很多，有很大概率走全表扫描。

范围查询之后的条件不走索引

根据 MySQL的查询原理的话，当处理到where的范围查询条件后，会将查询到的行全部返回到服务器端（查询执行引擎），接下来的条件操作在服务器端进行处理，这也就是为什么范围条件不走索引的原因了，因为之后的条件过滤已经不在存储引擎完成了。但是在 MySQL 5.6以后假如了一个新的功能index condition pushdown(ICP),这个功能允许范围查询条件之后的条件继续走索引，但是需要有几个前提条件：

• 查询条件的第一个条件需要有边界的，如select * from xx where c1=x and c2>x and c3,这样c3是可以走索引的；
• 支持InnoDB和MyISAM存储引擎；
• where条件的字段需要在索引中；
• 分表ICP功能5.7开始支持；
• 使用索引覆盖时，ICP不起作用。

可以控制index condition pushdown （ICP）开关

范围查询统计函数不遵循 MySQL索引最左原则

比如创建一个表：

当执行

时，time是可以用到idx_uid_date`的索引的,看如下的执行计划：

 

其中extra标识use index（这个图看不出来）说明是走索引覆盖的，一般意义来说是 MySQL是无法支持松散索引的，但是对于统计函数，是可以使用索引覆盖的，因此 MySQL的优化器选择利用该索引。

分页offset值很大性能问题

在 MySQL中，分页当offset值很大的时候，性能会非常的差，比如limit 100000, 20，需要查询100020条数据，然后取20条，抛弃前100000条，在这个过程中产生了大量的随机I/O,这是性能很差的原因，为了解决这个问题，切入点便是减少无用数据的查询，减少随机I/O。 解决的方法是利用索引覆盖，也就是扫描索引得到id然后再从聚簇索引中查询行记录，我知道有两种方式：

比如从表t1中分页查询limit 1000000,5

• 利用inner join

,子查询先走索引覆盖查得id,然后根据得到的id直接取5条得数据。

• 利用范围查询条件来限制取出的数据

，即利用条件

id > 1000000

在扫描索引是跳过1000000条记录，然后取5条即可,这种处理方式的offset值便成为0了，但此种方式通常分页不能用，但是可以用来分批取数据。

索引合并

对于如上的sql在 MySQL 5.0版本之前，假如没有建立相应的联合索引，是要走全表扫描的，但是在 MySQL 5.1后引入了一种优化策略为索引合并，可以在一定程度上利用表上的多个单列索引来定位指定行，其原理是将对每个索引的扫描结果做运算，总共有：交集、并集以及他们的组合，但是索引合并并非是一种合适的选择，因为在做索引合并时可能会消耗大量的CPU和内存资源，一般用到索引合并的情况也从侧面反映了该表的索引需要优化。