深入解析mysql索引加速查询原理及优化策略

78 阅读 0 评论 52 点赞

我是靠谱客的博主欣慰蜜粉，最近开发中收集的这篇文章主要介绍深入解析mysql索引加速查询原理及优化策略，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

关于sql优化，我们可以发现很多资料以及大佬们的优化经验都会涉及到索引，那么mysql索引是什么？索引是如何优化查询的？这篇文章会详细的讲述

索引的分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

主键索引：根据主键建立索引，每个主键默认会建立主键索引，不允许重复，不允许空值；
唯一索引：用来建立索引的列的值必须是唯一的，允许空值
普通索引：用表中的普通列构建的索引，没有任何限制
全文索引：用大文本对象的列构建的索引（不常用，本文暂时不讲）
组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

B+tree结构

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，mysql索引采用的是B+tree结构，关于树形结构可以参考这里，本篇文章大致描述下B+tree结构以便于后面索引加速查询的理解
B+tree结构：
在这里插入图片描述

如图所示，在B+tree上每个指针两个节点，并且左节点指向小于节点值的范围，右节点指向大于等于节点值的范围，非叶子节点上只存储键值不存储节点对应的数据（其实是节点的数据在对应的叶子节点上，从图中可以看出每个非叶子节点都会保存该节点到其右子节点），叶子节点上保存节点对应的数据，并且形成一个双向链表，从图中可以看出，按照树的分布规则，叶子节点上的数据是排好序的，这也是使用索引列排序效率很高的原因；

B+tree的每一层都是存储在不同的磁盘块中，mysql的存储引擎一次只读取一页的数据量，即一次读取一个磁盘块，每一次读取都是一次IO操作，因此要尽可能地使树的高度最低，减少IO次数，那么要如何降低树的高度呢？在MySQL的InnoDB存储引擎种默认一页读取16KB的数据量，一般主键常用类型为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）
这样一层理论上可以存储16KB/16B = 1000 个节点，3层树理论上可以储存1000 * 1000 * 1000=10亿条记录，但是实际上每层树都不会填满，因此在数据库中，B+Tree的高度一般都在2-4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作

索引加速查询原理

索引可以加速查询，时如何加速查询呢？mysql在查询数据时，在没有索引的情况下，将所有的数据从磁盘中读取出来，这样数据量越大效率越低下；索引就像书的目录一样，通过索引可以很快查询到想要的数据。
举个例子假设一条sql根据主键查询数据：

select * from user where id = 25

根据主键索引查询过程如下：

第一次磁盘IO，将磁盘1加载到内存，从头开始遍历，遍历到25<38，走左路，到磁盘3
第二次磁盘IO，将磁盘3加载到内存，从头开始遍历，遍历到25<30，走左路，到磁盘9
第二次磁盘IO，将磁盘9加载到内存，从头开始遍历，遍历到25=25，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。（这里需要区分的是在InnoDB引擎中Data存储的为行数据，而MyIsam中存储的是磁盘地址）

图解过程：
在这里插入图片描述

在没有索引的情况下，mysql查询数据是进行磁盘IO扫描整张表的数据，把符合条件的数据返回，这样在读取大数据量的表效率会特别低下，而使用索引，mysql查询则是根据索引的B+tree从根节点开始IO B+tree每一层的数据到内存，因为是在内存里比较，所以比较的时间几乎可以忽略，直到IO到叶子节点找到对应的索引，取出数据返回，上面也说到使用索引的查询一般只需1-3次IO操作，每一次IO可以立马找到指定的索引，所以使用索引查询的效率比较快，并且数据量越大，效率提升越明显

B+tree有一点比其他树形结构明显的优点，就是因为它包括父节点在内的所有节点都会保存在叶子点一层，并且在叶子节点使用双向链表将每个磁盘块的索引连接，所以使用范围查询效率很高；
假设我们将上面一条sql改为范围查询：

select * from user where id between 25 to 60

图解查询过程：
在这里插入图片描述

聚簇索引和非聚簇索引

B+tree结构索引上面图上说到叶子节点存储索引值以及索引所在的行数据，但是实际上并不是所有的索引都会在叶子节点索引所在的行数据；mysql的MyISAM存储引擎是叶子节点储存的数据是索引所在行的磁盘地址；InnoDB引擎则是主键索引在叶子节点存储索引值以及索引所在行的数据，非主键索引则是存储索引值以及索引所在行的主键值。

像InnoDB引擎主键索引在叶子节点存储索引值以及索引所在行的数据这种索引是聚簇索引；InnoDB引擎非主键索引和MyISAM引擎的所有类型索引在叶子节点没有存储索引所在的行的数据则是非聚簇索引

聚簇索引可以直接根据索引拿到索引所在行的数据，非聚簇索引只能通过拿到索引的data，根据索引的data再进行额外的IO操作才可能拿到索引所在行的数据，所以聚簇索引的查询效率比非聚簇索引高
以MyISAM引擎的索引为例，MyISAM引擎B+tree的叶子节点是存储的索引值以及索引所在行数据的磁盘地址，需要在拿到索引所在行的磁盘地址，再根据磁盘地址去查询索引所在行的数据
查询过程如下图所示：
在这里插入图片描述

InnoDB引擎的非主键索引是只存储索引值和主键值，要查询到索引所在的行数据，则需要先通过索引拿到索引的主键值，再回表进行主键查询返回索引所在的行数据，所以InnoDB引擎非主键索引查询的IO次数等于主键查询的IO次数 + 非主键索引的IO次数

假设我们在use表的name字段建立了普通索引，有一条根据name查询的sql

select * from user where name = ‘liubei’

它的查询过程如下图所示：
在这里插入图片描述

覆盖索引

上面我们讲到非主键索引，我们只能拿到主键值，要获取数据需要再进行一次主键索引查询，但是有一些情况，比如下面这条sql，name上建立了普通索引：

select id,name from user where name = ‘liubei’

查询会使用到辅助索引name，name索引的B+tree上保存的是name的值和所在行的主键，由于该sql只需要返回id和name字段，而这两个字段在辅助索引上已经有了，则可以直接返回查询结果，不需要再进行回表查询，这就是覆盖索引，即查询的所有字段包含在非主键索引的所包含的信息以内，省去再根据主键回表查询

覆盖索引查询过程图解如下：

在这里插入图片描述
覆盖索引并不算索引，准确来说它是一种优化策略

索引优化策略

索引虽然可以提高查询效率，但是它的缺点也很明显，索引会占据磁盘空间，表新增更新会使索引结构重新排列，因此会降低表更新效率，另外不合理地使用索引并不能提高查询效率，所以需要合理地利用索引，下面总结了索引优化策略

索引会占据磁盘空间，所以一张表不需要创建太多的索引，并且应该在常用做查询条件的字段上建立索引，达到读写平衡的效果

讲到mysql优化时，我们可能会了解到索引失效，索引为什么会失效，其实索引失效的实质是mysql内部优化器计算出使用索引效率比全表扫描效率低而放弃使用索引，这是因为不合理的使用索引导致的，比如使用 <>,not in,is null或is not null做判断条件，从B+tree的结构看，因为使用的是不等值，导致不知道从何处开始，会把每个节点都比较一遍，这样效率明显会很慢；还有使用模糊查询，从结构上可以看出，索引列是排好序的，只要前面部分相同都会排列在相邻的位置，使用右模糊查询可以根据非模糊部分找到B+tree上相应的节点位置，再根据模糊匹配规则将从节点右边一直匹配，因此右模糊查询是合理地运用索引，而全模糊和左模糊因为前面部分不确定，无法确定开始查找的节点，会导致每个节点都判断一遍，所以无法合理运用索引

索引列上避免使用<>, not in, is null 或 is not null判断，模糊查询避免用全模糊或左模糊

并不是任何经常作为判断条件的字段都应该建立索引，有时候一张表可能存储不同类型的数据，用一个作为标识的字段区分，通常这种字段值在2-4个不同值作为标识，在查询时往往会带上这个字段的判断条件；这样的字段是不适合建立索引的，因为该字段不同的值少，索引B+tree可能就一两层，每个节点存储大量的主键，如下图：
在这里插入图片描述
这样如果使用该字段的索引，通常会拿到大量符合条件数据的主键值，并且往往拿到的一系列主键值是杂乱无章的，不能很好地去使用范围查询的方式进行主键查询，等于要分大量的主键查询操作，这样效率往往会不如全表扫描，使得mysql优化器放弃该索引使用全表扫描；另外，由于B+tree树的每层空间大小有限，大文本字段占据的空间比较大，并且做比较也相对耗时，因此，索引也不适合建立在大文本字段上

索引应建立在重复度低的列上以及不适合在大文本字段上建立索引

索引类型中有一种索引是组合索引，什么组合索引？组合索引就是由多个列组成的复合索引，他可以看成将多个列按一定顺序拼接成一个新列，并在这个列上建立一个索引，假设一张user表，建立了一个组合索引name，age，position，它的B+tree结构如下图：
在这里插入图片描述

单个索引的排列顺序是固定的是按照创建指定的字段组合顺序定的，比如假设的user表的name，age，position组合索引是按照name，age，position顺序，在索引的B+tree中排列时先按照随前面的字段name排列，遇到name相同数据则相同的数据再按照第二个字段age排列，以此类推；那么组合索引是如何运用的呢？组合索引它有一个最左前缀匹配原则，如上图所示，由于组合索引的字段值的排列是固定，使用索引时首先会从最前面一列name进行比较，相同则再从第二列age比较，以此类推，同时只能使用等值判断，如果使用范围或不等值，B+tree就不知道从哪个节点开始查询，
假设下面这三条sql

select * from user where name = ‘MFS’ and age = 27 and position = ‘modao’;
select * from user where name = ‘MFS’ and age < 30;
select * from user where age = 24 and position = ‘daqiang’;

第一条sql都是等值的判断，B+tree会先找到组合索引排在最左字段name的判断条件，因为是等值判断所以都能够匹配上，再根据右边的字段依次匹配上age，position；

第二条sql，B+tree会先找到组合索引排在最左字段name，name是等值判断，所以能匹配上所有 name = ‘MFS’的索引值，但是由于age是范围判断，无法确定age范围最大值30的位置，所以只能将name = ‘MFS’记录全部比较一遍

第三条sql，B+tree会找不到组合索引排在最左字段name，不知道从何开始匹配，因此无法使用组合索引进行查询