InnoDB存储引擎支持B+树索引、哈希索引、全文索引等常用索引。哈希索引是自适应的，InnoDB会根据表的使用情况自动为表生成哈希索引。B+树索引是目前关系数据库中最常用、最有效的索引之一。它的索引结构是多路平衡树结构（类似于二叉树，B代表Balance而不是Binary）。通过B+树索引可以快速定位到要查找的数据所在的数据页，然后将页读入内存，通过页字典槽快速找到数据行。InnoDB引擎实现了B+树结构的索引，其高度一般为2~3层。也就是说，查询记录的IO操作次数最多为3次。InnoDB索引可以分为聚簇索引和非聚簇索引，两者都是B+树结构。聚集索引（ClusteredIndex）聚集索引，也称聚簇索引，是一种数据存储方式。在InnoDB存储引擎中，B+树索引和数据存储在一起。也就是说，InnoDB存储引擎的数据是通过B+树索引来组织的。建表在进行探究引解之前，我们首先建立如下表：--------------------------------index_test的表结构------------------------------如果存在`index_test`则删除表；创建表`index_test`(`id`intNOTNULLAUTO_INCREMENT,`name`varchar(50)NOTNULL,`age`intNOTNULL,`profession`varchar(100)NOTNULL,`sex`char(1)NOTNULL,PRIMARYKEY(`id`),KEY`index_name_profession`(`name`,`profession`))ENGINE=InnoDBAUTO_INCREMENT=8DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;------------------------------index_test的记录----------------------------BEGIN;INSERTINTO`index_test`(`id`,`name`,`age`,`profession`,`sex`)VALUES(1,'Tom',33,'teacher','m');INSERTINTO`index_test`(`id`,`name`,`age`,`profession`,`sex`)VALUES(2,'Ryan',25,'programmer','m');INSERTINTO`index_test`(`id`,`name`,`age`,`profession`,`sex`)VALUES(3,'Li',18,'student','w');INSERTINTO`index_test`(`id`,`name`,`age`,`profession`,`性别`)VALUES(4,'Bob',40,'医生','m');INSERTINTO`index_test`(`id`,`name`,`age`,`profession`,`sex`)VALUES(5,'Jim',60,'doctor','m');INSERTINTO`index_test`(`id`,`name`,`age`,`profession`,`sex`)VALUES(6,'Ben',34,'teacher','m');INSERTINTO`index_test`(`id`,`name`,`age`,`profession`,`sex`)VALUES(7,'Joy',28,'programmer','w');犯罪;聚簇索引结构数据行实际存储在数据页中，数据页通过B+树索引结构的叶子节点组织如下图（如果不知道B+树结构可以阅读我的另一篇文章《数据结构-B树族》)：聚簇索引结构B+树的每个叶子节点都是一个数据页。B+树的内部节点都是索引节点，key的左右指针指向数据页。叶子节点（数据页）通过指针（页指针）连接起来，是一个双向链表结构。叶子节点（数据页）内部是数据行，数据行之间也是通过指针连接起来的。叶子节点（数据页）和叶子节点中的数据行按照主键的顺序排列（注意：叶子节点和行在物理上不是连续的，都是链表结构）。主键选择原则使用B+树作为数据存储结构，我们需要让主键（键值）满足以下特点：键值的长度尽可能小：键值会占用空间，我们希望越小越好。键值尽量单调递增：B+树的插入可能会导致节点分裂。如果不是单调递增，我们可能会把它插入到页面中间，这可能会造成数据分裂和数据移动，严重影响插入性能。非聚集索引（SecondaryIndex）非聚集索引，又称非聚集索引、二级索引、辅助索引等。在InnoDB中，非聚集索引的页节点除了包含键外，还包含一个书签，即key对应的数据行所在的位置。结合我们上面说的，这里的书签值对应的是聚簇索引的key。辅助索引与聚簇索引的关系单列索引单列索引，即一个索引树只包含一列的值，一个表可以创建多个单列索引。如果查询语句中包含单列索引列，优化器可能只会选择一个最优的单列索引，遵循以下原则：索引，优化器将遵循最优策略并可能命中一个或多个索引。如果查询条件是OR连接，并且所有使用的列都被索引，那么所有的索引都会被命中。如果查询条件是OR连接，并且只对使用的部分列进行了索引，则进行全表扫描。1和2这两个原则涉及到一个index_merge策略，它是一种多索引合并优化策略。我们将在下面讨论这个概念。单列索引合并合并索引是MySQL5.7的InnoDB引擎引入的一种策略，我们称之为index_merge。如果使用该策略，执行计划将返回类型：index_merge，具有以下特点：它将多个两个索引的范围扫描结果合并为一个（AND为交集，OR为并集）。该策略只适用于单表操作，多表查询无效。如果存在不创建单列索引的OR条件，则会失败。如果所有条件对应的列都是索引，那么AND和OR的组合也会命中这种类型的索引。执行如下语句，可以看到type为index_merge，Extra为sort_union。EXPLAINSELECT*FROMindex_testWHEREname='Tom'ORprofessinotallow='teacher';执行结果组合索引在没有建立组合索引的情况下，可以通过多次单列索引UNION操作快速得到结果。接下来介绍一下复合指数。见下图：复合索引对于复合索引，所有参与索引的列都会出现在索引树上。如上图所示，是一个index_profession_name复合索引。存储引擎会先按照专业列值的顺序创建第一个索引列，然后根据第一个列创建第二个索引列。复合索引具有以下特点：查询遵循最左原则，查询条件必须包含第一个索引列，即profession、profession&name、profession&sex等组合；如果查询条件包含第一个索引列，则不要求查询条件的书写顺序，可以写name&profession，age&profession等，优化器会按顺序进行处理；OR查询会使复合索引失效；查询复合索引回表可能会涉及查询回表操作，什么是查询回表？当SELECT列包含非索引列时，我们需要通过聚集索引来补全数据，回调到表查询。举个例子：SELECTprofession,name,age,sexFROMindex_testWHEREprofession='xxx';此时age和sex列不在index_profession_name索引中，需要查询index_test的聚簇索引来补全age和sex列信息。如果我们SELECT的列都是索引列怎么办？是否不需要回表查询，这涉及到另一个概念，即索引覆盖率。索引覆盖从上面的例子我们很容易得出索引覆盖是：当SELECT列包含所有索引列时，我们可以通过非聚集索引获取所有数据，这称为索引覆盖。下图是覆盖索引时执行计划的内容。我们可以看到Extra是Usering索引。IndexcoverageIndexpushdown关于indexpushdown从字面上看不太好理解（这个词很唬人，但是理解了它的逻辑之后，你会发现它极其简单，就命名的重要性而言），我们看下图:SELECT*FROMindex_testWHEREnamelike'J%'ANDprofessional='programmer'ANDsex='m';indexpushdown在MySQL5.6之前，只要第一个索引列满足查询条件，就会回表查询，如上图，有3个回表查询。MySQL5.6以后，会通过索引下推的方式依次匹配多个索引列，不符合的会被过滤掉，从而减少回表的次数。如上图所示，并不是说程序员直接跳过，减少了一个回表的操作。索引下推可以有效减少返回表的次数，从而提高查询效率（也就是多次if判断，做个名词来唬人）。索引的选择性索引的选择性是指是否有必要建立索引，因为并非所有出现在查询条件中的列都需要建立索引。比如性别（男，女），整张表是男是女，浪费索引存储空间，对提高查询速度没有任何作用。索引的选择性有一个很重要的指标，就是基数（cardinality），它是索引统计的唯一记录数。如果越接近聚簇索引，它的利用率和效率就会越高，如下图所示：索引的选择性索引的选择性公式为：索引的选择性=唯一索引值个数/数据表中的记录总数。聚簇索引的选择性为1，也就是说，如果一个索引的选择性接近于1，则查询效率更高，但索引占用的空间更大。索引失败OR前后的查询条件不都是索引字段。不遵循最左边的N个字段。模糊查询LIKE以%开头。需要类型转换。WHERE中的索引列有计算。WHERE中的索引列使用该函数。在索引字段上使用NOT、<>、!=。当全表扫描比索引快时。前缀索引我们先看下面两个索引：ALTERTABLEindex_testADDINDEXindex_name(name);更改表index_test添加索引index_name_pre(name(1));上面两个索引唯一的区别是index_name_pre索引是一个namePrefix索引，前缀的长度为1，也就是说index_name_pre只包含name字段的第一个字符。下面分别执行以下语句，看看两个索引的用法：EXPLAINSELECT*FROMindex_testWHEREnamelike'Ben';index_name_preindexindex_name_preindexindex_nameindexindex_nameindex从两个执行计划可以看出，如果查询会扫描2行记录，但是在index_name索引下只需要扫描1行记录。这是否意味着前缀索引没有意义？但事实并非如此，让我们继续。前缀索引的选择原则列值很长，需要建立索引：如果我们为表index_test表新建一列：addressvarchar(500)，该列是存储用户的地址列，其实际长度可能有数百个字符。如果我们为它建立一个全量索引，它占用的索引空间会很大，那么我们可以为它建立一个前缀索引。前缀索引需要列的一部分前缀作为索引，这“部分”的计算依据是根据索引的选择性来确定的。我们希望的是：前缀n的选择性无限接近于整列的选择性，但是n的值需要尽可能的小（节省空间）。计算步骤如下：column_name全列选择性的计算方法：SELECTCOUNT(DISTINCTcolumn_name)/COUNT(*)FROMtable_name;column_name的前缀n的选择性计算方法：SELECTCOUNT(DISTINCTLEFT(column_name,n))/COUNT(*)FROMtable_name;通过调整n的大小，得到一个接近全列n值的选择，同时保证前缀足够小。Hash索引MySQL的Memory引擎是支持Hash索引的，不过我们今天说的不是引擎，而是InnoDB的存储引擎的hash索引。我这里说的哈希索引严格来说应该叫做自适应哈希索引（AdaptiveHashIndex，简称AHI）。自适应哈希索引不能由用户手动创建。它是由引擎根据当前视图的数据访问频率在缓冲池中创建的。按访问频率建立索引，即针对高频热点数据建立索引。结构哈希索引是通过哈希表实现的。key是通过hash函数（CRC32计算）使用查询条件中的key计算出来的，value直接指向数据页中的value。上图所示的自适应哈希索引结构可以通过哈希索引实现O(1)时间复杂度的查询，而使用辅助索引则需要N次（与树的高度有关）。自适应触发条件使用同一个条件访问同一个索引17次；例如，index_test表有一个index_profession_name复合索引。如果我们使用以下任何语句访问（不是交替访问），就可以创建一个自适应索引：SELECT*FROMindex_testWHEREprofession='programmer';SELECT*FROMindex_testWHEREprofession='programmer'ANDname='Tom';如果同一个查询条件访问超过100次；数据页被同一条查询语句访问N次（N=页记录数*1/16）；缺点自适应哈希索引的维护必然会使用锁来控制并发，因此锁可能会造成性能损失。自适应哈希索引在DML操作引起数据变化时，处理效率高，成本低。自适应哈希索引的条件非常严格，要求连续访问相同的查询条件，并且只适用于等价的搜索条件，orderby，模糊查询等是不行的。本身可能会占用大量的内存池空间，从而增加引擎的负担，需要调整参数。总结一下，InnoDB存储引擎中有以下几种索引：B+树索引、哈希索引、全文索引。本文主要介绍前两者。InnoDB存储引擎的数据是通过B+树索引来组织的。换句话说：聚集索引存储了完整的记录数据，即使索引被索引。可以使用多个单列索引的索引合并来达到复合索引的效果，但不推荐这样做。在设计复合索引时，需要注意索引的选择性。接近1的索引效率会更高，但索引存储空间也会增加。可以使用覆盖索引来快速查询。覆盖索引不需要回表查询，效率很高。当遇到非常大的列需要建立索引时，可以考虑使用前缀索引，但是要注意前缀的长度，可以通过索引的选择性公式来计算。索引下推可以有效减少复合索引的回表数，提高查询效率。自适应哈希索引条件非常苛刻，尽量使用它来提高查询效率。
