MySQL 索引优化

索引定义

索引是辅助存储引擎高效获取数据的一种数据结构，索引是数据的目录，便于存储引擎快速地定位数据

索引类型

数据结构

B+tree
Hash
Full-text
MySQL 常见的存储引擎，InnoDB、MyISAM 和 Memory 分别支持的索引类型

在 MySQL5.5 及以上版本中，InnoDB 存储引擎是 MySQL 关系型数据库默认使用的存储引擎，B+tree索引是 InnoDB存储引擎的默认索引类型。

B+tree 和 B-tree 的区别：
B+tree 只在叶子节点存储数据，而 B-tree 的非叶子节点也存储数据，所以 B+tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下能查询更多的节点，存储更高效，另外 B+tree 叶子节点采用单链表连接，更适合 MySQL 中常见的基于范围的顺序检索场景，而 B-tree 无法做到这一点。

B+tree 和红黑树的区别：
对于有 N 个节点的 B+tree，其查询复杂度为 O(logdN)，d 为 B+tree 的度，表示节点允许的最大子节点个数为 d 个，在实际的运用中 d 值是大于 100 的，即使数据达到千万级别时 B+tree 的高度依然维持在 3-4 左右，保证了 3-4 次磁盘 I/O 操作就能查询到目标数据；而红黑树是二叉树，节点的子节点数为两个，意味着其查询复杂度为 O(logN)，比 B+tree 高出不少，因此红黑树检索到目标数据所需经历的磁盘 I/O 次数更多。

B+tree 和 Hash 的区别：
范围查询时 MySQL 常见的查询场景，Hash 表不适合做范围查询，更适合做等值查询，另外 Hash 表还存在 Hash 函数选择和 Hash 值冲突等问题。因为这些原因，B+tree 要比 Hash 有更广的适用场景。

物理存储

聚簇索引
二级索引（辅助索引）

InnoDB

InnoDB 表的索引按叶子节点存储的是否为完整表数据分为聚簇索引和二级索引，全表数据就是存储在聚簇索引中的，聚簇索引以外的其他索引叫做二级索引。InnDB 默认创建的主键索引就是聚簇索引，其他索引都是二级索引，也叫辅助索引或非聚簇索引。
InnoDB 表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段情况下，表的第一个 NOT NULL 的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列，并在此列上创建聚簇索引。
二级索引的叶子节点并不存储一行完整的表数据，而是存储了聚簇索引所在列的值，由于二级索引的叶子节点不存储完整的表数据，所以当通过二级索引查询到聚簇索引所在的列值后，还需要回到聚簇索引，也就是表数据本身进一步获取数据，也就是回表查询，回表查询需要额外的 B+tree 查询过程，必然增大查询耗时。
需要注意的是，通过二级索引查询时，回表查询也不是必须的过程，当查询的所有字段在二级索引中就能找到时，就不需要回表，不需要回表查询的二级索引被称为覆盖索引，也称为触发了索引覆盖。通常可以使用 Explain 查看索引计划的 Extra 列的值是否为 Using index，如果是Using index则触发了索引覆盖，不需要回表查询，如果是 NUll，则未触发索引覆盖，需要回表查询。

MyISAM

MyISAM 存储引擎的表不存在聚簇索引，而使用的是二级索引，MyISAM 表中的主键索引和非主键索引的结构是一样的，它们的叶子节点不存放表数据，存放的是表数据的地址，即行指针，所以 MyISAM 表可以没有主键，MyISAM 表的数据和索引是分开的，是单独存放的，MyISAM 表中的主键索引和非主键索引的区别仅在与主键索引 B+tree 上的 key 必须符合主键的限制，非主键索引 B+tree 上的 key 只要符合相应字段的特性就可以了。

字段特性

主键索引
唯一索引
普通索引
前缀索引
主键索引是建立在主键字段上的索引，一张表最多只有一个主键索引，索引列值不允许有空值，通常在创建表的时候一起创建。
唯一索引是建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列值允许为空。
普通索引是建立在普通字段上的索引，即不要求字段是主键，也不就要求字段是 UNIQUE。
前缀索引是指对字符类型的前几个字符或对二进制类型字段的前几个 bytes 建立的索引，而不是在整个字段上建立索引，前缀索引可以建立在类型为 char、varchar、binary、varbinary 的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。但是，前缀索引也具有局限性，比如 order by 就无法使用前缀索引，无法把前缀索引用作覆盖索引。

字段个数

单列索引
联合索引（复合索引）
建立在单个列上的索引称为单列索引，建立在多个列上的索引称为联合索引，也叫复合索引。联合索引的非叶子节点保存了两个字段的值作为 B+tree 的 key 值，当 B+tree 上插入数据时，先按字段 id 比较，在 id 相同的情况下按 name 字段比较。

创建索引

建立前缀索引

通常字符类型的字段只需要对它前面几位字符建立索引就能满足性能要求了，过长的索引会使用更多的空间，也影响查找效率。

建立覆盖索引

覆盖索引是指sql 中查询的所有字段，在索引 B+tree 的叶子节点上都能找得到的那些索引，使用覆盖索引查询时无需回表查询。
如果 sql 中查询的字段无法触发已有索引的索引覆盖，就可以考虑去掉查询中不必要的字段，或者查询的字段数不多的话，可以再对这些字段建立新的联合索引，以便使用新的联合索引的索引覆盖。
联合索引具有最左匹配原则，这个原则指明联合索引中的某个字段，如果想要被用到联合索引的过滤当中，除了这个字段不会使联合索引失效以外，另外一个必要的前提是这个字段的前一个字段也被用到了联合索引的过滤中，所以建立联合索引时的字段顺序对索引效率是有很大影响的，越靠前的字段被用于索引过滤的概率越高。
通常会把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 sql 使用到，所谓区分度就是某个字段中不同值得个数与表的总行数的比值，比如性别字段，区分度就很小，不适合建立索引或不适合排在联合索引列的靠前的位置，相反像 uuid 这类字段就比较适合做索引或排在联合索引列的靠前的位置。
依据联合索引的最左匹配原则，当联合索引中的某个字段使得索引失效时，这个字段就不能被联合索引用于索引过滤了。MySQL 从 5.6 版本开始对这种情况引入了索引下推机制，索引下推机制能减少二级索引的回表次数，也能减少查询引擎和 MySQL Server 层之间的数据传递量。

json、group by、order by 语句的查询条件，要使用索引。
参与 join 的字段上一般需要加上索引，尽量结合现有的索引字段进行 order by 操作，尽量避免低效的文件排序，group by 操作中，分组的字段一般需要加上索引，尽量避免创建临时表。

索引的缺点

索引会带来数据写入延迟
索引会引入额外的空间损耗
索引能提升查询效率的场景是有限的，大数据量时，需要使用其他的，如读写分离、分库分表等方案
sql 中使用了索引，但是查询仍然很慢的原因：

• sql 查询是事务中依赖多版本并发控制（MVCC）的快照读，需要多次版本回退
• 索引失效
• 锁等待，触发表锁，或行锁升级为表锁，或死锁
• 不恰当的 sql 语句，比如， SELECT *
  、 SELECT COUNT(*)
  、 在大数据表中使用 LIMIT M,N
  分页查询，以及对非索引字段进行排序等。

索引优化

使用 EXPLAIN 查看 sql 执行计划

我们通过 EXPLAIN 查看 sql 语句的执行计划，explain + sql 语句。

EXPLAIN SELECT id,name FROM blog_tag WHERE id > 2;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | blog_tag | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   16 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• id：每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id。
• select_type：表示 SELECT 查询类型，常见的有 SIMPLE（普通查询、即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERT（子查询）等。
• table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。
• partitions：访问的分区表信息。
• type：数据扫描类型，常见扫描类型的效率从低到高依次为 ALL（全表扫描）、index（全索引扫描）、range（索引范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）、const（结果只有一条的主键或唯一索引扫描）。其中，ALL（全表扫描）和 index（全索引扫描）是要尽量避免的。
• possible_keys：可能使用的字段
• key：实际使用的字段
• key_len：索引的长度
• ref：关联 id 等信息。
• rows：扫描数据的行数
• filtered：查找到所需记录占总扫描记录数的比例
• Extra：额外的信息

其中重点介绍一下 type 列的值：

• system/const：表示只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。
• eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。
• ref：非唯一索引扫描，还可见于唯一索引最左前缀原则匹配扫描。
• range：索引范围扫描，比如 <
  ， >
  ， between
  等操作。
• index：索引全表扫描，此时遍历整个索引树。
• ALL：表示全表扫描，需要遍历全表来找到对应的行。

使用 Show Profile 分析 sql 执行性能

MySQL 在 v5.0.37版本之后支持 SHow Profile 功能，可以分析执行线程的状态和时间，还支持进一步选择 ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES 等类型来查询 sql 语句在不同系统资源上所消耗的时间。

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]

type参数：
| ALL：显示所有开销信息
| BLOCK IO：阻塞的输入输出次数
| CONTEXT SWITCHES：上下文切换相关开销信息
| CPU：显示CPU的相关开销信息 
| IPC：接收和发送消息的相关开销信息
| MEMORY ：显示内存相关的开销，目前无用
| PAGE FAULTS ：显示页面错误相关开销信息
| SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置(行数) 
| SWAPS：显示swap交换次数的相关开销信息

MySQL 最新版本是默认开启 Show Profile 功能的，旧版本默认是关闭该功能的。

索引失效

必定失效

• 被使用的索引列上有表达式计算
• 被使用的索引列上有函数操作
• 被使用的索引列上有隐式类型转换操作

失效原因：
索引的使用时依赖于整个 B-tree 索引树的遍历，而索引树的遍历依赖于索引树底层叶子节点的有序性，当被索引字段做了表达式计算，函数，隐式类型转换时，有可能这个字段新的排列顺序和原来在索引树的叶子节点层的排列顺序不一样了，这就破坏了索引树叶子节点层的有序性，当 sql 语句被执行时，MySQL 数据库的 sql 语句执行器无法判断原来的索引树还能否还能被检索使用，所以最后的结果就是 sql 语句执行器不使用该索引了。

• like 匹配使用了左模糊匹配符’%abc’ 和使用了左右模糊匹配符’%abc%’，都会造成索引失效，只有 like 关键字的右模糊匹配’abc%’，能够依旧使用到索引
• 被使用的索引字段，不是联合索引的最左字段

失效原因：
因为 MySQL 中的索引树检索遵循最左匹配原则，B-tree 索引树的叶子节点的有序性也是建立在最左匹配的基础上的，如果直接使用索引键的中部或者后部进行 sql 查询，由于违背了最左匹配原则。MySQL 的 sql 语句执行器无法利用这个索引树进行检索。
如果查询条件中使用 or，并且 or 的前后条件中有一个列没有索引，那么涉及的索引都不会被使用到。
需要注意的是，如果查询过程中发生了索引覆盖，也就是不需要回表时，索引树还是可以被使用的。

可能失效

索引列上用了!=、>、>=、<、<=、or、in 等
索引列上用了 is null，is not null
之所以说可能失效，是因为以上这些条件有比较大的概率会造成要扫描更多的数据，数据库一旦要扫描的数据量超过 20% 到 30% 范围的时候，会直接把执行计划变成一个全表扫描，不管有没有索引，它都会直接默认变成全表扫描。因为 MySQL 认为随机扫描的数据量太大了，还不如直接来一次全表扫描。
需要注意的是，并不是所有情况下，都会使索引失效，都一定会变成全表扫描，应该是要根据实际扫描的数据库来决定是否会导致索引失效。也就是说如果 MySQL 查询优化器预估走索引的代价比全表扫描的代价还要大，则不走响应的索引，直接全表扫描，如果走索引比全表扫描的代价小，则使用索引。
MySQL 查询优化器的预估并不是精确的，当优化器判定索引失效的情况下，我们可以通过 force index 的方式强制查询引擎走特定的索引。