mysql的三种索引（简单理解MySQL索引）

我想大家应该多多少少做过一些慢SQL的优化吧，通常会是公司的leader或者是运维人员扔给你一些执行速度慢的sql语句，然后让你去优化。当遇到慢sql的时候，大家的第一直觉是不是查看索引有没有创建，索引是不是有命中等等。那这个索引到底是什么东西，为什么能提高数据的查询速度呢？那么下面就简单的介绍一下索引以及索引底层的数据结构等。

索引是帮助MySQL高效获取数据的一种排好序的数据结构。通常由数据表中的一列或多列组成，可以用来快速查询数据表中的数据，从而避免扫描全表。索引好比书的目录，通过目录可以快速定位到我们需要的章节，从而加快查找效率。对于系统能有良好的运行，索引可以说非常关键，尤其是在表中的数据量越来越大时，索引的影响愈发重要。当然，索引也并非全是优点，也有缺点的存在：

优点：

• 减少服务器扫描的数据量，提高数据查询速度。
• 唯一索引可以保证数据的唯一性
• 加快表与表间的连接速度
• 提高分组或排序效率

缺点：

• 数据量越大，创建或维护索引就越耗时
• 索引也是存储在磁盘上的，所以会占用一定的物理磁盘空间。
• 当数据表执行删除，修改，新增操作时，也需要同态维护索引。
• 写多读少的情况下，不适合建索引

索引按底层数据结构类型分为Hash索引和BTree索引。Hash索引底层基于Hash表实现，BTree索引底层是基于多路平衡搜索树实现的。那为什么不使用二叉树或者红黑树这些呢？下面就来简单的分析一下为什么要使用BTree和为什么不使用二叉树。先给大家安利一个网站叫Data Structure VisualizatIOns，这个网站可以动态演示各种数据结构的动画，比较方便理解。

二叉树这里就不多介绍了，就是左节点的值小于右节点。那索引为什么不使用二叉树呢。假设有一张文章表document。其中id设为主键自增。那么这个时候像数据表document中添加数据，索引的底层数据结构使用的是二叉树。那么就会出现下图这种情况。

这是因为id设置了自增，二叉树竟然退化成了链表。要知道数据和索引都是存储在磁盘上呢，一旦退化成链表，那和全盘扫表没什么区别。如果由几十万的数据，我要查找id靠后的数据，那就意味着要进行几十万次的IO操作。这个效率不能在低下了。所以，这就是为什么不使用二叉树的原因。

红黑树在二叉树的基础上，当插入节点时会做一次平衡。这样虽然可以避免二叉树退化成链表的情况，但是当数据量增大时，数的高度也随之变大。最然说红黑树的查找效率比较高，但是随着数据量增大，树的高度是不可控的。如果存在百万的数据，要查找叶子节点数据，这个效率同样低下。所以，索引没有使用红黑树作为数据存储结构。

Hash索引底层的实现是通过Hash表来实现的，mysql会计算索引的hash值，根据hash对数组进行填充。mysql对于Hash冲突采用链地址发，和java中的hashmap一样，只不过不会转红黑树。hash索引的等值查找效率比较高，只要进行一次hash运算就可以定位到目标索引的位置，但是由于数据结构的限制，无法进行范围查询和大小比较等。如果数据量越来越大，那么hash冲突的几率也会越来越大。

上面有提到红黑树的查找效率比较好，但是树的高度不可控。那么有什么办法让树的高度尽量可控呢，此时就有的红黑树的一个变种B-Tree。B-Tree的数据结构与红黑树类似，B-Tree的每个节点都存储着多个有序的索引数据，这种也叫做一个数据页。在InnoDB存储引擎中，每个数据页大概有16kb,这个数据页大小是可以调整的，但是在非必要的情况下不建议修改这个大小。

show variables like 'innodb_page_size'; -- 查看数据页大小 复制代码

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据，有可能是索引行所在的磁盘地址，也有可能是索引所在行的其他数据列。在整个B-Tree上面，不会存在相同的key。如果基于B-Tree查找某个数据，IO的次数相对于其他其他数据结构要少很多。

B Tree是在B-Tree的基础上做了一些变动。B Tree的每个非叶子节点都去掉了索引的data,data数据统一放到了叶子节点中。这样做的好处是为了让非叶子节点的数据页存储更多的索引数据，上面提到了每个数据页的大小在16kb左右，如果在非叶子节点带上索引的data数据，那么同一大小的数据页B-Tree存储数据能力要小于B Tree。同样的数据量，B-Tree树的高度可能要高于B Tree。初次之外，B Tree在叶子节点使用了指针，相邻的节点用指针相连，提高了数据的区间连续性。所以B Tree是索引底层实现的最优选择。

数据页的大小为16KB，一般表的主键类型为int（占4字节）或bigint（占8字节） 如果用bigint作为主键，指针类型(磁盘地址)一般为6字节，也就是说非叶子节点所在一个页中大概存储16KB/(8B 6B)=1170，也就是说，一个数据页可以存储1170个索引和指针信息。而叶子节点的一个页大概存储16KB/1KB（索引 数据大概为1kb）= 16。也就是说一个深度为3的B Tree索引可以维护1170 * 1170 * 16 = 2000多万条记录。所以当Mysql数据大于2000多万查询性能会下降。

B-Tree和B Tree的区别对比

在结构方面：

• B-Tree不存在重复索引，每个索引key都带有索引data。而B Tree存在重复索引，但查询会一致查找到叶子节点，B Tree只有叶子节点才会带有索引data。
• B Tree叶子节点会通过指针相连，增加了数据区间的连续性。

在系统方面：

• B Tree的磁盘读写代价更低
• B Tree的IO操作更少，查询效率更高

Hash和B Tree的区别对比

• Hash索引查询效率高，但无法满足范围查询。B Tree叶子节点有维护指针，范围查询更便捷。
• Hash索引不能用来做复合索引
• B Tree的数据是有序的，而Hash是无需的
• Hash存在冲突问题

索引如何定位数据

在B-Tree的每个节点和B Tree叶子节点都带有索引的data,这个data在不同的存储引擎里面存放的数据是不一样的。比如主键索引在常见的InnoDB存储引擎和MyISAM存储引擎中，data存储的分别是该索引行的数据和索引行的磁盘地址。MyISAM存储引擎下，一张表在文件内可以分为.frm , .MYD , . MYI三种文件格式，frm文件存储数据表的结构，MYD文件存储表的数据，MYI文件存储表的索引。在MYI索引文件中存储了索引行的磁盘文件地址，当通过索引定位数据时，先查找到数据的地址，根据地址在定位到数据。

在InnoDB存储引擎下，一张表在文件内可分为.frm，.idb两种文件格式，同样frm文件存储表的结构。但是与MyISAM不同的是，InnoDB将索引和数据绑定到了一起并存储到了idb文件中。当要通过索引定位数据时，找到索引也就表示找到了数据行，这个效率要比MyISAM高很多，减少了通过磁盘地址在定位数据的操作。这种将索引和数据绑定在一起的索引也就是常说的聚簇索引。反之，为非聚簇索引

上面讲述的都是主键索引，B Tree索引字段都是单值主键。那么二级索引(除主键索引外的索引)是如何组织索引数据的呢？比如将document表中的title和content列设置为复合索引，那么索引在排序时，先按照title进行排序，再次按照content进行排序。如果title相同，那么就会按照content进行排序。但是和主键索引有区别的时，二级索引的叶子节点并不是存储索引行的数据，而是存储索引行的主键数据。当通过二级索引查找数据时，先要定位到叶子节点并根据叶子节点的主键，再次到主键索引上进行数据查找，这个过程也叫做回表。

explain执行计划

当我们拿到一条满sql后，如何确定sql满在哪里呢？那么就可以使用Explain来分析慢sql语句。比如像索引是否命中，表的读取顺序等。使用Explain可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈。在 select 语句之前增加 explain 关键字，MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，并不会真正的执行这条SQL。比如有三张表：文章表document_info,标签表label_info和关联表document_label。分析一个简单的查询语句

explain select * from document_info d where d.id=1 复制代码

可以看到打印的结构有id,select_type,table,partitions,type等一些字段。那么这些字段都有什么意义呢？这些在mysql的官网上面有所介绍EXPLAIN Output Columns。

id

id的值表示sql语句中select语句的执行序号，这个值可以为NULL,但是只有在使用union联合查询时，id会出现NULL。id的值越大表示select语句执行的优先级就越高。

EXPLAIN SELECT * FROM `document_info` d1 where d1.id =1 union SELECT * FROM `document_info` d2 where d2.id =2 复制代码

select_type

select_type的值有很多个，比如SIMPLE，PRIMARY，UNION等。表示select语句的查询复杂程度。

类型	描述
SIMPLE	单纯的一个select查询，没用使用子查询或者union
PRIMARY	复杂查询中，最外层的查询
UNION	union 查询中的第二个以及以后的select语句
DEPENDENT UNION	union 查询中的第二个以及以后的select语句，取决于外部查询
UNION RESULT	将union查询结果作为临时表，并通过临时表查询结果的select语句
SUBQUERY	SQL中的子查询(不包含from中的子查询)
DEPENDENT SUBQUERY	在WHERE列表中包含了子查询，子查询基于外层
DERIVED	派生表。一般在from后，比如select * from (select * from xxx) xxx
UNCACHEABLE SUBQUERY	无法使用缓存的子查询语句
UNCACHEABLE UNION	无法使用缓存的union查询语句

type

type列表示sql的连接类型，即mysql如何查询数据表的行，查询行的大概范围。常见的连接类型查询效率由最佳到最差可以分为：system>const>eq_ref>ref>range>index>ALL。一般情况下的查询sql要保证在range级别，最好达到ref。

• system：表只有一行数据记录。这是 const 连接类型的一个特例。
• const：表最多有一个匹配行，比如在查询document_info时使用主键id或唯一索引进行条件过滤。因为只有一行，所以这一行中列的值可以被优化器的其余部分视为常量。const非常快，因为它们只被读取一次。

• eq_ref：当在连表查询时，关联的字段值主键或唯一索引时，其type值为eq_ref。eq_ref可用于使用=运算符比较的索引列

• ref：相比较eq_ref，在查询时满足索引的最左前缀规则，使用非主键索引或非唯一索引时，type的值会为ref。比如将document_info表中的title设置为普通索引。在执行查询时

• range：使用索引进行范围查询，比如>,<,in,between等

• index：全索引扫描，一般扫描的是非主键索引(非聚簇索引),也就是二级索引。这种扫描不会从B Tree根节点开始查询，而是通过对B Tree的叶子节点进行遍历扫描，这种查询一般为了使用覆盖索引，查询速度比较慢。比如将document_info表中的title和content设为复合索引，执行explain:

• ALL：全表扫描，扫描主键索引(聚簇索引)的所有叶子节点。效率最低。

possible_keys

在分析执行sql时，可能会用到的索引。如果经过分析使用索引的查询效率可能没有不使用索引查询的效率低时，那么在最终执行sql时就不会使用索引。

key

执行sql时，最终实际用到的索引。如果没有用到索引，则显示为NULL。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，这个长度是数据库字段类型的长度，并非实际内容长度。比如document_info的id主键设置为int类型，int类型占4字节，所以key_len为4。

ref

显示哪些列或常量与键列中指定的索引进行比较，从而从表中选择结果行。

rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

extra

该列表示sql分析时的额外字段信息，该字段值有很多，但常见的有Using index，Using where，Using index condition等。

• Using index： 使用了覆盖索引，不需要回表查询。比如将document_info表中的title，content字段设置为复合索引。查询时，只查询两个字段或者其中一个，那么extra就为Using index。

• Using where：查询sql使用了where过滤，并且查询字段未被索引覆盖。比如查询label_info中的name字段(name列未设置任何索引)，那么extra就为Using where。
• Using index condition：查询列未被索引全覆盖，并且使用where进行条件过滤且是范围查询。

• Using filesort：使用非索引字段进行排序。

• Select tables optimized away：使用聚合函数访问索引字段。

索引失效场景

• 对索引列进行运算导致索引失效

在sql查询时，如果使用 ,-,*,/,!=时，会造成索引失效。

• 索引字段类型错误可能导致索引失效
• 使用 is null，is not null会导致索引失效
• not in , or可能会导致索引失效

竟然有人说使用not in()，or会导致索引失效，其实没有那么绝对，mysql优化器会根据当前表的情况和回表次数判断需不需要走索引。

• 使用一些函数导致失效

在sql查询时，如果使用像left，length，date等一下函数时，会导致索引失效。这是因为B Tree索引数据与使用函数后的数据可能不一致。

• 使用like '%xxx' 或 like '%xx%'模式查询导致失效

比如将document_info中title设为索引字段，执行like模糊查询语句时，导致了索引失效并进行全表扫描。那为什么使用like 'xx%'就可以使用索引呢？这个还是因为和B Tree有关，like 'xx%'前部分字段在索引树上仍然可以保证有序。

如果在日常开发中经常会用到like '%xx%'，但是还是想让使用索引改怎么优化呢？这个时候可以使用覆盖索引，让查询的字段，能在一个索引树上获取就可以。比如将document_info中的title和content设置为复合索引，在执行sql时就可以命中索引。

• 不满足最左原则导致索引失效

顾名思义，在复合索引中，最左侧的字段优先匹配。因此，在创建复合索引时，where子句中使用最频繁的字段放在组合索引的最左侧。而在查询时，要想让查询条件走索引，则需满足：最左边的字段要出现在查询条件中，如果遇到范围查询(>、<、between、like等)，就停止后边的匹配。。比如将document_info中的title,content,is_valid设置为复合索引。

-- 走索引的情况-- explain select * from document_info where title='a' and content='b' and is_valid=0; explain select * from document_info where title='a' and content='b'; explain select * from document_info where title='a' and is_valid=0; explain select * from document_info where is_valid=0 and title='a' and content='b'; explain select title from document_info where is_valid=0 and content='b';-- 查询的字段被索引覆盖，走索引-- -- 索引失效的情况 -- explain select * from document_info where is_valid=0 and content='b'; 复制代码

索引失效的场景还有很多，上面仅是列举在开发中经常遇到的情况。

索引下推

索引下推是mysql5.6版本做的一个优化项，其目的是为了减少回表次数，提高查询效率。在理解索引下推工作方式前，先要直到mysql的简单的架构，主要分为四层：连接层，服务层，存储引擎层，文件系统层。

以document_info为例，将title和is_valid设置为复合索引并分析下面的sql语句,发现索引是命中的，但是命中的只是title，is_valid并没有奏效(最左匹配原则到like就终止了，后面就断掉了)。

explain select * from document_info where title like '哈%' and is_valid=1; 复制代码

那既然索引生效了，mysql为什么还要优化呢？可以看到通过explain执行计划看到Extra的值为Using index condition，那就是说查询的列并没有被索引覆盖，需要做回表查询。如果不使用索引下推，那么会把二级索引树以‘哈’开头的title列筛选出来，进行回表查询(在存储引擎层)。引擎层将结果返回给server层，Server层在根据is_valid字段再次进行筛选。

在5.6之后版本，默认开启了索引下推。与未开启相比较，在存储引擎层，如果title匹配的上，那么会再次校验is_valid是否满足条件，如果不满足则直接过滤掉，避免回表查询。这样的话，server层拿到数据后，不需要再次进行二次校验。

总结

上述简单对索引做了简单的介绍，包括底层数据结构的选型，选择B Tree的好处，索引的数据定位以及日常开发中经常遇到索引失效的场景等。但是，需要注意的是索引并非一定能提高查询效率，当数据量较小时，还是不建议使用索引。一张表的索引也并非越多越好，适当且合理的索引可以让SQL查询性能提升数倍。

来源：https://juejin.cn/post/7137312940465586213

,