1. 索引介绍

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件（一般作为 WHERE 子句的条件）。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。

1.1. MySQL 中的索引

索引在 MySQL 数据库中主要由如下几种：

B+ 树索引；
Hash 索引；
全文索引；

全文索引在 MySQL 中不常用，这里就不做介绍。

Hash 索引很容易理解，就是利用 Hash 函数根据查询关键字计算出存储槽的位置，其检索效率非常高，索引的检索可以一次定位，不像 B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的 IO 访问，所以 Hash 索引的查询效率要远高于B-Tree索引。

可能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢?任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些：

Hash 索引只支持等值比较查询，包括 ＝、IN、<=> (注意 <> 和 <=> 是不同的操作），也不支持任何范围查询，例如 WHERE price > 100；　
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且 Hash 值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算;
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用，所以在使用组合索引的情况下，Hash 索引在任何时候都不能避免表扫描；
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash 运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果；
Hash 索引遇到大量 Hash 值相等的情况后性能并不一定就会比 BTree 索引高；

所以在实际场景中更多的还是使用 B+ 树索引，B+ 树索引的原理一两句话说不清楚，详细可参考「MySQL索引之B+树」，本文后续的索引内容也主要就是针对 B+ 树索引。

1.2. 索引的类型

索引按其组织特点可分为如下类型：

聚集索引和非聚集索引：数据是否与索引存储在一起，是则是聚集索引，否则是非聚集索引；
主键索引和辅助索引：表中的主键列默认就是索引列，即主键索引，其它有索引的列则是辅助索引；
稠密索引和稀疏索引：索引是否索引了每一个数据项，是则是稠密索引，否则是稀疏索引，如主键索引就是稠密索引；
简单索引和组合索引：索引单列则为简单索引，索引多列则为组合索引；
左前缀索引：仅索引行数据的前面指定长度的字符；
覆盖索引：在索引中直接能查找到目标数据，即覆盖索引；

1.3. 聚集与辅助索引

1.3.1. 聚集索引

聚集索引列的选择规则如下：

如果表中设置了主键列，自动根据主键列生成索引树；
如果没有设置主键，自动选择第一个唯一键的列作为聚集索引；
自动生成隐藏的聚集索引；

建议：在建表时，显式的创建主键，最好是数字自增列。

功能：录入数据时，按照聚簇索引组织存储数据，在磁盘上有序存储数据行，从而加速基于聚集索引列的查询；

1.3.2. 辅助索引

辅助索引需要人为地创建，将经常作为查询条件的列创建辅助索引，起到加速查询的效果。
其构建过程如下：

构建叶子节点：提取主键 + 辅助索引列，按照辅助索引列进行从小到大排序后，生成叶子节点，并且相邻的叶子结点间有双向指针；
构建枝节点：提取叶子节点辅助索引列的范围 + 指针，构建枝节点，并且的相邻枝节点间有双向指针;
构建根节点：提取枝节点的辅助索引列的范围+指针，构建根节点；

按照辅助索引列，作为查询条件时，其执行流程如下：

在辅助索引树中查找最终得到目标辅助索引行对应的主键值；
拿着主键值回表（回到聚集索引中）查询；

即除了覆盖索引这种情况，仅通过辅助索引是得不到目标结果的，要想获取到目标结果，则需要根据索引得到目标行对应的主键值，然后重新根据主键值从聚集索引中查找最终的目标行。

1.4. 使用索引考虑的事项

1、回表是什么？回表会带来什么问题？怎么减少回表？
回表： 按照辅助索引列作为查询条件时，先查找辅助索引树，再到聚簇索引树查找数据行的过程。
回表带来的问题： IO 量多、IO 次数多、随机 IO 会增多；
减少回表的方法：

使用联合索引尽量达到覆盖索引的效果；
让查询条件精细化，即使用唯一值多的列作为查询条件；
使用 MRR（Multi-Range-Read）优化器；

启用 MRR 优化器：
mysql> select @@optimizer_switch;
mysql> set global optimizer_switch='mrr=on';
MRR 优化器的功能：

让辅助索引批量查找到主键值后进行自动排序；

拿着一批主键值一次性回表，由于主键已排序，所以很有可能受到 B+ 树中的双向指针的优化；

2、索引树高度的影响因素？如何解决？
首先要明确的是索引树高度肯定是越低越好。
数据行越多，高度就越高，解决方案有如下：

做分区表，比如有一 1000 万行数据的表，可将其拆分为 5 个 200 万行数据的表，由于在单表角度来说数据量减少了，所以索引树的高度也就降低了，但是由于多个分区表实际上还是在单实例中，所以其性能有待商榷；
按照数据特点，进行归档表，例：如果某业务表频繁被访问的仅是近期数据，此时可以让该业务表中仅保留近期的数据，比如一年内的数据，把一年前的数据持续迁移到其它表；
分布式架构，将海量数据存储到不同的实例中，高并发业务的主流方案；
在设计方面，尽量满足三大范式，单表尽量减少冗余。

主键列的长度不能过长：

主键的长度要短，长度长则其需要的存储空间随之增多，会导致在单页中可保存的聚集索引随之减少，进而其索引树高度就会增高；
主键尽量使用自增数字列；

辅助索引列的长度不能过长：

尽量保证索引列的长度短，列值长度越长，其构建出的索引数据量就越大，所以会影响到索引树高度；
对于长度过长的 varchar 列，可使用前缀索引，如在一个长度为 100 字符的列中，只取前10个字符，构建索引树；

选择合适的、简短的数据类型，例如：

存储人的年龄，使用 tinyint 和 char(3) 显然都可以满足，当年龄字段被索引且字段都添加了非空约束的情况下，此时 tinyint 只占 1 个字节，而 char(3) 在 utf8 编码下要占 3*3 个字节；
存储人名，char(20) 和 varchar(20) 的选择？
- a. 站在数据插入性能角度思考，应该选：char，因其分配长度固定；
- b. 从节省空间角度思考,应该选：varchar，因其存储空间接近实际存储值大小；
- c. 从索引树高度的角度思考，应该选：varchar，因其节省空间，所以单页下能存储的节点就多，索引树高度就矮；

所以建议使用 varchar 类型存储变长列值。

3、有一条 select 语句平常查询时很快，突然有一天很慢，会是什么原因？
有可能是因为统计信息不真实（过旧）导致索引本身失效了。
索引有自我维护的能力，对于表内容变化比较频繁的情况下，有可能会出现索引失效。
其解决办法有如下两种：

删除原来索引重建索引；
执行此语句手动触发指定表索引重新统计：ANALYZE TABLE <tb>；

对应的统计表为 innodb_index_stats 和 innodb_table_stats。

2. 索引管理

2.1. 查看索引

-- 详细查看 tb 表中的索引
show index from <tb>;
-- 简单查看表中索引列
desc <tb>;

2.2. 创建索引

-- 在 tb 表的 col 列上创建索引
create index student_sname on <tb>(<col>);
-- 在 tb 表的 col 列上创建索引
alter table <tb> add index [ix_name](<col>);
-- 创建联合索引
alter table <tb> add index [ix_name](<col1>[,col2, ...])
-- 创建前缀索引
alter table <tb> add index [ix_name](<col>(<length>));
-- 判断前缀长度多少合适：
select count(distinct(left(name,5)))  from city ;
select count(distinct name)  from city ;

2.3. 删除索引

-- 删除 tb 表中名为 index_name 的索引
drop index <index_name> on <tb>;

3. 执行计划与分析

MySQL 为我们提供了一个专门用来查看查询的执行计划的命令，那就是 explain，它的使用也很简单，只需要在查询语句的 select 前加上 explain 即可，如下：

mysql> explain select sname from student where sage=18\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

其中各列的含义如下：

id：当前查询语句中，每个 select 语句的编号，简单 select 查询时仅有一条，复杂类型查询时可有多个 id；
    查询可分为简单查询和复杂查询：
        简单查询就是单个 select 语句完成查询，如 select * from tb;
    复杂类型的查询有三种：
        简单子查询；
        用于 from 中的子查询；
        联合查询：union；
    注意：union 查询的分析结果会出现一额外临时表。
select_type：其取值可根据简单查询和复杂查询分为如下几类：
    简单查询为 SIMPLE；
    复杂查询：
        SUBQUERY：简单子查询（如 where 之后的子查询）；
        DERIVED：用于 from 中的子查询（如对一个 select 语句的结果集再次进行查询）；
        UNION：union 语句的第一个之后的 select 语句；
        UNION RESULT：匿名临时表；
table：查询所访问到的表；
type：索引查询类型，即 MySQL 决定的如何去查询表中行的方式，去取值有如下几种：
    ALL：全表扫描，没有使用到索引；
    index：根据索引的次序进行全表扫描，如果在 Extra 列出现了 “Using index” 表示使用了覆盖索引，而非全表扫描；
    range：有范围限制的根据索引实现范围扫描，扫描位置始于索引中的某一点，结束于另一点；
    ref：辅助索引等值查询；
    eq_ref：多表连接查询中，非驱动表的连接条件是主键或唯一键；
    const 或 system：直接返回单个行，如根据主键等值查找；
possible_keys：查询可能会用到的索引；
key：查询最终使用的索引；
key_len：在索引查询时覆盖索引列的长度，单位为字节；
ref：在利用 key 字段所标识的索引完成查询时所用的列或某常量值；
rows：估计为找到所有的目标行而需要读取的行数；
extra：额外信息，取值有如下：
    Using index：覆盖索引，以避免访问表；
    Using where：在存储引擎检索后，再进行一次过滤；
    Using temporary：对结果排序时会使用临时表；
    Using filesort：对结果的排序没有使用到索引；

上述字段有一些我们还需要仔细的了解一下，为方便测试，先执行下面语句创建测试用的表和数据：

drop table if exists user;
create table user
(
    id     int auto_increment
        primary key,
    name   varchar(24)      not null,
    age    tinyint unsigned not null,
    gender char(1)          null check ( 'F' 'M' )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

drop procedure if exists insert_user;
create procedure insert_user(count int)
begin
    declare i int default 1;
    while i <= count
        do
            -- 循环开始
            insert into user(name, age, gender)
            values (substring(MD5(RAND()), 1, 10), round(rand() * 100),
                    case round(rand() * 1000) % 2 when 0 then 'F' else 'M' end);
            set i = i + 1;
        end while; -- 循环结束
end;
-- 执行存储过程
call insert_user(50000);
-- 删除存储过程
drop procedure if exists insert_user；

3.1. type 详解

3.1.1. ALL

all 表示使用的查询条件没有使用到索引，例：

-- name 列没有建立索引，所以此时执行计划的 type 为 all
explain select * from user where name='cd94056fc0';
-- 给 name 列加上索引：
alter table user add index idx_name(name);
-- 此时如果使用不等值查询，也不会走索引
explain select * from user where name!='cd94056fc0';
-- 使用 not in 时不走索引
explain select * from user where name not in ('354f3caf60');
-- 使用完全模糊查询时不走索引，仅模糊查询右侧部分会走索引
explain select * from user where name like '%z%';

3.1.2. index

index 表示使用的查询条件会对索引进行完全扫描，例：

-- 查询表中所有的 name，此时 name 是辅助索引列，所以会完全扫描辅助索引
explain select name from user;

3.1.3. range

index 表示使用的查询条件会对索引进行范围扫描，例：

-- 对主键索引进行范围扫描
explain select * from user where id between 100 and 200;
-- 对辅助索引右侧部分模糊匹配也是范围扫描
explain select * from user where name like 'z%'
-- 使用 in 时也是范围扫描
explain select * from user where name in('354f3caf60','cd94056fc0');
-- or 的效果和 in 一样
explain select * from user where name='354f3caf60' or name='cd94056fc0';

3.1.4. ref

ref 表示使用的查询条件使用了辅助索引等值查询，例：

explain select * from user where name='354f3caf60';

3.1.5. eq_ref

eq_ref 表示在多表连接查询中，非驱动表的连接字段是主键或唯一键；

-- 懒得创建第二张表了，直接用 user 表做自连接效果也一样，其中 t2 就是非驱动表
explain select * from user t1 join user t2 on t1.id=t2.id

3.1.6. const 或 system

const 和 system 都表示使用的查询条件是主键或唯一键的等值查询。

explain select * from user where id=20000;

3.2. key_len 详解

在上面已经简单说了 key_len 列表示的是索引查询时覆盖索引列的长度，那么它这个长度到底是怎么计算来的呢？下面就来说一下这个问题。

key_len 是查询时应用到的每个索引列的最大储值空间长度，其单位为字节，所以它的长度和每个列所使用的数据类型有密切关系。
并且对于字符类型列，其 key_len 长度还与所在表使用的字符集相关联。
如对于 utf8 来说，单个中文占 3 个字节，单个英文占 2 个字节，而计算 key_len 时需按索引列最大储值空间计算，所以在字符集为 utf8 时，计算字符类型索引列的 key_len 则直接取 3 * 字符长度。

对于 varchar 类型字段来说，MySQL 还会额外分配 1-2 个字节存储该字段实际的储值长度：

当存储字符长度在 255 以内时，仅分配 1 个字节即可存储其储值长度；
当存储字符长度大于 255 时，则需要分配 2 个字节存储其储值长度了；

同样，在计算 varchar 类型的索引列的 key_len 时，我们依旧还是要取其最大储值空间计算，所以每个 varchar 字段在原有长度的基础上还需要 +2 才是它最终的 key_len。

还有一个要注意的点是，key_len 的值还受索引列是否有非空约束影响，如果索引列没有非空约束，那么 MySQL 会单独分配 1 个字节来存储该字段是否为空。

综上所述，小结如下表：

分类	数据类型	占用字节长度	非空时 key_len	不是非空 key_len
数值类型	tinyint	1	1	1+1
	int	4	4	4+1
	bigint	8	8	8+1
utf8 字符	char(10)	10*3	30	30+1
utf8 字符	varchar(10)	10*3+2	32	32+1
utf8mb4 字符	char(10)	10*4	40	40+1
utf8mb4 字符	varchar(10)	10*4+2	42	42+1

上表中各数字的单位为字节，上面仅列出了常用数据类型的长度，其它数据类型的长度可参考「数据类型介绍」。

这里我们要使用联合索引举例说明，所以需要先将之前在 name 字段中的索引删掉再重新创建索引：

-- 删除索引
drop index idx_name on user;
-- 新建联合索引
alter table user add index idx_name_age_gender(name,age,gender);

有了上面的内容的铺垫，我们此时就可以来计算一下当 user 表的联合索引列都被覆盖使用时的 key_len，该联合索引由如下三列组成：

列名	数据类型	计算出的 key_len
`name`	`varchar(24) not null`	`24*4+2=98`
`age`	`tinyint unsigned not null`	`1`
`gender`	`char(1) null check ( 'F' 'M' )`	`1*4+1=5`

所以，当这三个列都被覆盖时的 key_len 值为 98+1+5=104。

user 表使用的字符编码为 utf8，所以在计算 key_len 是其单个字符的长度为 4；

验证一下吧：

mysql> explain select * from user where name='0006233cf8' and age=43 and gender='M';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name_age_gender | idx_name_age_gender | 104     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

没毛病~~~所以在分析查询语句的执行计划时，我们就可以通过 key_len 来判断此次查询覆盖到了哪些索引列，然后就可以针对性的做出优化。

4. 联合索引应用

4.1. 最左原则

联合索引应用要满足最左原则：

建立联合索引时，选择重复值最少的列作为最左列；
使用联合索引时，查询条件中，必须包含最左列，才有可能应用到联合索引；

为啥要把重复值少的列作为最左列呢？其实很容易理解，使用联合索引过滤查询条件时，是按联合索引的列顺序依次过滤，以下面索引为例：

alter table user add index idx_name_age_gender(name,age,gender);

就是会先通过 name 列索引过滤条件，然后在过滤完 name 条件的基础上继续对后面的 age、gender 依次做过滤。
所以如果第一个索引列的重复值少，那么经过该索引过滤出来的行数就少，后续索引列过滤时的行基数就少了，也就加快了查询效率。

再说一下为啥查询条件中必须包含最左列，这个是和联合索引树的组织结构有关的。
关于联合索引树的组织结构，，整得我很迷茫，网上大致分为两派：

非叶子节点中仅保存着联合索引最左列范围和下层节点的指针，叶子节点中保存了联合索引所有列及主键，可参考「文章一」；
非叶子节点中逐级保存了联合索引中所有列的范围和下层节点的指针，叶子节点中保存了联合索引的所有列及主键，可参考「文章二」；

不过这两派都不影响对“查询条件必须包含最左列”的论证：

对于派 1，如果非叶子节点中仅保存最左列，那么索引查询的起始位置肯定也必须是最左列，不然根本找不到叶子节点。
如果仅通过最左列找到叶子节点，大概率会过滤出大量的叶子节点，剩下的索引列只能在这些叶子节点中轮询过滤，效率就较低；
对于派 2，如果每个非叶子节点中逐级保存了联合索引的所有列，则必须依次按照索引列定义的顺序进行过滤，不能跳过，所以查询条件最起码要包含最左列才能应用到一列以上索引。
此种方式在经过层层过滤到达叶子节点时就直接找到了最终的结果，相对来说效率就较高。但是由于非叶子节点中保存了所有索引列，所以相对来说此种方案联合索引占用的存储空间更多。

这里我个人比较倾向派 2，就是感觉轮询太 low 了，而且现在存储空间已经很廉价了，存储换效率还是很划算的。

4.2. 全部覆盖

以 user 表为例，下述几种情况会覆盖全部的联合索引列：

-- 前提，三个列都必须出现在判断条件中
-- 1. 按联合索引列顺序判断条件
explain select * from user where name='0006233cf8' and age=43 and gender='M';
-- 2. 不按联合索引列顺序判断条件，查询优化器会自动调整判断顺序应用索引
explain select * from user where age=43 and gender='M' and name='0006233cf8';
-- 3. 使用 in 判断
explain select * from user where name='0006233cf8' and age=43 and gender in ('M','F');
-- 4. 使用左前缀判断
explain select * from user where name like '0006%' and age in (43,50) and gender in ('M','F');
-- 5. 使用了最左列，然后按顺序排序
explain select * from user where name like '0006%' order by age,gender;

4.3. 部分覆盖

以 user 表为例，下述几种情况会覆盖部分的联合索引列：

-- 1. 覆盖前两列
explain select * from user where name='0006233cf8' and age=43;
-- 2. 覆盖最左列，中间跳过了 age，所以用不到最后的 gender 索引
explain select * from user where name='0006233cf8' and gender='M';
-- 3. 覆盖前两列，由于在中间列即 age 出现了非等判断，导致用不到后续的 gender 列索引
explain select * from user where name='0006233cf8' and age>18 and gender='M';
-- 4. 覆盖最左列，仅使用最左列判断
explain select * from user where name='0006233cf8';

4.4. 完全不覆盖

以 user 表为例，下述几种情况会出现完全不覆盖联合索引列：

-- 不使用最左列判断，就会完全扫描索引
explain select * from user where age=18;
explain select * from user where gender='F';
explain select * from user where age=20 and gender='F';

5. 索引小结

使用索引的优点：

索引可以降低服务需要扫描的数据量，减少了 IO 次数；
索引可以帮助服务器避免排序和使用临时表；
索引可以帮助将随机 IO 转为顺序 IO;

高性能索引建议：

全值匹配：精确某个值，如查询某个名字叫 bob 的用户；
独立使用列避免其参与运算，如不要这样：where age+20<60；
左前缀索引，索引构建于字段的左侧的多少个字符，要通过索引选择性来评估（索引选择性：不重复的索引值和数据表的记录总数的比值）；
多列索引，当查询的过滤条件多为 and 连接时，更适合使用联合索引；
选择合适的索引列次序，将选择性最高（过滤出的结果最少）的列放左侧；
不要添加重复索引，如 (name)、(name,age)；
匹配左前缀：只精确匹配起头部分，如 bo%；
必须要有主键，如果没有可以作为主键条件的列，可创建无关列作为主键列；
尽量让查询只访问索引（覆盖索引）；
对于辅助索引尽量不使用 <> 和 not in，因为它们不走索引；
索引维护要避开业务繁忙期；

当查询的结果集是原表中大部分数据（15% 到 25% 以上），此时可能出现不走索引的情况，因为优化器就觉得没必要走索引了，与 MySQL 的预读功能有关。

6. 扩展

6.1. 自适应哈希索引 - AHI

AHI 全称 "Adaptive hash index, AHI" 即自适应哈希索引，它是一种可以从查询信息直接定位到叶子结点的方法，从而省略根结点到叶子结点的路径上所消耗的时间。
查询语句使用 AHI 的时候有以下优点：

自动评估“热”的内存索引页并生成 Hash 索引表，然后可直接通过从查询条件直接定位到叶子结点，减少一次定位所需要的时间；
在 buffer pool 不足的情况下，可以只针对热点数据页建立缓存，从而避免数据页频繁的 LRU；
帮助 InnoDB 快速读取索引页，加快索引读取的效果，相当与索引的索引。

但是 AHI 并不总能提升性能，在多表 Join、模糊查询、查询条件经常变化的情况下，此时系统监控 AHI 使用的资源大于上述的好处时，不仅不能发挥 AHI 的优点，还会为系统带来额外的 CPU 消耗，此时需要将 AHI 关闭来避免不必要的系统资源浪费。

AHI 默认就是开启的，可通过如下语句查看：

mysql> show variables like '%hash_index%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index       | ON    |
| innodb_adaptive_hash_index_parts | 8     |
+----------------------------------+-------+
2 rows in set (0.00 sec)

6.2. 改变缓冲 - change buffer

在进行 insert、update、delete 数据操作时，对于聚簇索引会立即更新，而对于辅助索引，不是实时更新的（因可能导致索引树内存结构大规模变动）。
在 InnoDB 内存结构中，为保证查询时使用的索引能利用到最新数据加入了 insert buffer（会话），现在版本叫 change buffer。
change buffer 功能是临时缓冲辅助索引需要的数据更新，当我们需要查询新 insert 的数据，会在内存将原始索引数据和 change buffer 中的数据进行 merge（合并）操作，此时辅助索引就是最新的。

6.3. 8.0 版本索引新特性

1. 不可见索引 - invisable index
针对优化器不可见，但是索引还在磁盘存在，依旧会被自动维护，该操作对于客户端是透明的，在客户端“眼中”该索引已经被删除了。
对于索引维护时，如果不确定索引是否还有用，这时可以临时设定为 invisable，后续确定后可以再移除。

2. 倒序索引
以之前使用的 user 表为例，如果有一个这样的查询：

-- 有一个 idx(name,age,gender) 的联合索引
explain select * from user order by name,age,gender desc limit 10;

此时对 gender 的排序是不会用到索引的，因为默认情况下 B+ 树索引都是以 asc 的方式排序构建的。

在 8.0 后我们可以在创建索引时指定某列为倒序索引，即让其默认以 desc 的方式排序构建。
以适应上例需求为例，在 8.0 版本中可修改索引定义语句如下：

alter table user add index idx(name,age,gender desc);

6.4. 设定优化器算法

MySQL 内部提供了很多优化器算法，我们可以通过如下语句来进行查询：

mysql> select @@optimizer_switch;
-- 默认的输出在单行，下面对其进行了换行整理
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on

可通过如下方式修改启用或关闭指定的优化器算法。
1、修改 my.cnf 配置文件

[mysqld]
optimizer_switch='batched_key_access=on'

2、设置全局变量：

myssql> set global optimizer_switch='batched_key_access=on';

3、hints，在查询时指定使用或禁用优化器算法（了解即可）。

SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;

SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;

SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;

SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;

EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;

hints 可参考 https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html。

6.5. 索引下推 - ICP

ICP 是 "index_condition_pushdown" 的索引，可译为索引下推，是 MySQL 5.6+ 加入的特性。
还是以 user 表为例，此时在该表上存在如下联合索引：

idx(name, age, gender)

如果此时我们执行一个这样的查询：

select * from user where name like '000%' and age>18 and gender='F'

通过前面「联合索引应用 - 部分覆盖」的学习我们已经知道，在此种情况下只能应用到 name 列索引。
ICP 就是为了解决这个问题，在上述场景中，SQL 层做完过滤后，本来只能覆盖到 name 列的索引，而 ICP 能够在 engine 层真正去磁盘读取数据之前将 age 和 gender 列索引的过滤下推到 engine 层，进行再次过滤。
因为此时联合索引中的 age、gender 列索引本来已经随 name 列索引的应用加载到了内存，所以此时就可以在真正 IO 之前直接从内存中通过 age、gender 列索引来过滤无用的数据页。
所以最终去磁盘上拿的数据页就是我们目标数据，大大减少无用 IO 的访问。

具体参考：

https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

https://mariadb.com/kb/en/index-condition-pushdown/

6.6. 联接查询算法

参考：https://www.zze.xyz/archives/mysql-multi-table-arithmetic.html。

目录CONTENT

MySQL索引详解