引言

内容为慕课网的"高并发 高性能 高可用 MySQL 实战"视频的学习笔记内容和个人整理扩展之后的笔记，本节内容讲述的索引优化的内容，另外本部分内容涉及很多优化的内容，所以学习的时候建议翻开《高性能Mysql》第六章进行回顾和了解，对于Mysql数据的开发同学来说大致了解内部工作机制是有必要的。

由于文章内容过长，所以这里拆分为两部分，上下部分的内容均使用sakila-db，也就是mysql的官方案例。第一部分讲述优化的理论和Mysql过去的优化器设计的缺陷，同时会介绍更高的版本中如何修复完善这些问题的（但是从个人看来新版本那些优化根本算不上优化，甚至有的优化还是照抄的Mysql原作者的实现的，发展了这么多年才这么一点成绩还是要归功于Oracle这种极致商业化公司的功劳）。

如果内容比较难，可以跟随《Mysql是怎么样运行》个人读书笔记专栏补补课，个人也在学习和同步更新中。

地址如下：juejin.cn/column/7024…

【知识点】

• Mysql索引内容的介绍
• 索引的使用策略和使用规则
• 查询优化排查，简单了解Mysql各个组件的职责

前置准备

sakila-db

sakila-db是什么？国外很火的一个概念，指的是国外的电影租赁市场使用租赁的方式进行电影的观看十分受外国的喜欢。这里介绍是因为后续的内容都用到了这个案例。所以我们需要提前把相关的环境准备好，从如下地址进行下载：

下载地址：dev.mysql.com/doc/index-o…

《高性能Mysql》的SQL 案例也是使用官方的example

work-bench

work-bench是官方开发的数据库关系图的可视化工具，使用官方案例的具体关系图展示效果如下，通过这些图可以看到Sakila-db之间的大致关系：

work-bench也是开源免费软件，下载地址如下：

dev.mysql.com/downloads/w…

安装workbench和下载sakila-db的方式这里不做记录，在运行的时候需要注意先建立一个数据库运行sheme文件，然后执行data的sql文件，最终在navicat中查看数据：

正文部分

索引类型

首先是索引的特点以及作用：

1. 索引的目的是为了提升数据的效率。
   
2. 对于ORM框架来说索引的使用至关重要，但是ORM的优化往往难以顾及所有业务情况，后续被逐渐废弃。
   
3. 不同的索引类型适用于不同的场景。
   
4. 索引关键在于减少数据需要扫描的量，同时避免服务器内部对内容排序和临时表（因为临时表会索引失效），随机IO转顺序IO等特点
   

下面介绍Mysql相关的索引类型：

• 哈希索引：哈希索引适合全值匹配和精确查找，查询的速度非常快 在MySQL中只有memory存储引擎显式支持此索引，memory还支持非唯一哈希索引的，是哈希索引设计里面比较特殊的。
• 空间索引：空间索引是myisam表支持，主要用作地理数据存储，这里包含一个叫做GIS的玩意，但是GIS在Postgre中使用比MySQL要出色很多，所以mysql中空间索引是无关紧要的东西。
• 全文索引：全文索引也是myisam独有支持的一种索引类型。适合使用的场景为全值匹配的场景和关键字查询，对于大文本的关键字匹配可以有效处理。
• 聚簇索引：聚簇索引是innodb存储引擎的默认存储引擎。
• 前缀压缩索引：注意这个索引针对的是myisam存储引擎，目的是为了让索引放入内存中排序，，前缀压缩的方法是首先保存索引块的第一个值，然后在保存第二个值，存储第二个值类似（长度,索引值）的形式存放前缀索引。

其他索引类型注意事项：

Archive 在5.1之后才支持单列自增索引。

MyISAM 支持压缩之后的前缀索引，使得数据结构占用更小。

哈希索引

在Mysql中唯一显式实现哈希索引的存储引擎为Memory，Memory是存在非唯一哈希索引，同时BTree也支持“自适应哈希索引的方式“兼容哈希索引。

下面是哈希索引特点：

• 键存储的是索引哈希值，注意不是索引值本身，而值存储的是指向行的指针
• 注意此哈希索引无法避免行扫描，但是在内存中指针非常快通常可以忽略不计
• 注意只有哈希值按照顺序排序，但是行指针不是按照顺序排序
• 哈希不支持：部分索引覆盖，只支持全索引覆盖，因为使用全部的索引列计算哈希值
• 哈希索引支持等值匹配操作不支持范围查询，比如等于，in子查询，不全等。
• 如果出现哈希冲突，哈希索引将退化为链表顺序查询，同时维护索引的开销也会变大

聚簇索引

聚簇表示数据行的值紧凑存储在一起。而innodb聚簇的值就是主键的值，所以通常使用都是主键上的索引，针对主键索引的选择十分重要。由于本部分着重索引优化，聚簇索引这里就不再讲述了。

MyISam和Innodb的主键索引区别是MyISam的索引很简单，因为数据行只包含行号，所以索引直接存储列值和行号，数据单独存放另一处，类似于一个唯一非空索引，索引和数据不在一处，MyISam的索引设计比InnoDB简单很多，这和MyIsam不需要支持事务也有直接关系，而innodb将索引和行数据放入一个数据结构，将列进行紧凑的存储。

聚簇索引有下面优点

• 紧凑存储数据行，所以可以直扫描少量磁盘就可以获取到数据
• 数据访问的速度非常快，索引和数据放在同一颗BTree中，比非聚簇索引查询快很多
• 覆盖索引可以直接减少回表

当然索引也有下面的缺点：

• 对于非IO密集型应用，聚簇索引的优化无意义。
• 插入速度依赖于插入顺序，但是如何不是自增插入则需要optimize table重新组织表。
• 更新代价非常高，因为BTree要保证顺序排序需要挪动数据页位置和指针。
• 主键数据插入过满数据页存在页分裂问题，行溢出会导致存储压力加大。
• 聚簇索引导致全表扫描变慢，页分裂导致数据问题等。
• 二级索引需要回表查询聚簇索引才能查询数据。
• 二级索引由于需要存储主键开销会更大，至少在InnoDb中维护一个二级索引的开销是挺大的。

压缩索引

压缩索引的特点是使用更少的空间存放尽可能多的内容，但是这样的处理方式仅仅适用于IO密集型的系统，压缩前缀存储形式最大的缺陷是无法使用二分法进行查找，同时如果使用的倒序索引的方式比如order by desc 的方式可能会因为压缩索引的问题存在卡顿的情况。

Bree索引的特点

• 叶子结点存在逻辑页和索引页两种，通常非最底层叶子结点都是索引页，最底层索引页由链表串联。
  
• Btree索引会根据建表顺序对于索引值进行排序，索引建表时候建议将经常查询的字段往前挪。
  
• Btree索引适合的查询类型：前缀查询，范围查询，键值查询（哈希索引）。
  

自适应哈希索引

当innodb发现某些索引列和值使用频繁的时候，BTree会在此基础上自动创建哈希索引辅助优化，但是这个行为是不受外部控制的，完全是内部的优化行为，如果不需要可以考虑关闭。

Btree查询类型

针对Innodb的Btree索引，有下面几种常见的查询方式：

• 全值匹配：等值匹配的方式，全值匹配适合哈希索引进行查询
• 最左匹配原则：二级索引的查询条件放在where最左边
• 前缀匹配：只使用索引的第一列，并且like ‘xxx%’
• 范围匹配：范围匹配索引列到另一列之间的值
• 范围查询和精确匹配结合，一个全值匹配，一个范围匹配
• 覆盖索引查询：覆盖索引也是一种查询方式，

索引策略

下面是关于建立索引的一些常见策略：

1. 第一件事情需要考虑的是预测那些数据为热点数据或者热点列，按照《高性能Mysql》介绍，对于热点列来说有时候要违背最大选择性的原则，通过建立时常搜索的索引作为最左前缀的默认的设置。同时优化查询需要考虑所有的列，如果一个查询的优化会破坏另一个查询，那么就需要优化索引的结构。
2. 第二件事情是考虑where的条件组合，通过组合多种where条件，需要考虑的是尽可能让查询重用索引而不是大规模的建立新索引。
3. 避免多个范围进行扫描，一方面是范围查询会导致，但是对于多个等值的条件查询，最好的办法是尽量控制搜索范围。

对于索引的策略我们还需要了解下面的细节

• 单行访问很慢，特别是随机访问要比顺序访问要慢更多，一次性加载很多数据页会造成性能的浪费。
• 顺序访问范围数据很快，顺序IO的速度不需要多磁道查找，比随机的访问IO块很多，顺序访问也可以使用group by进行聚合计算。
• 索引覆盖速度很快，如果查询字段包含了索引列，就不需要回表。

索引碎片优化

Innodb的数据结构和特性会导致索引存在数据碎片，对于任何存储结构来说顺序的存储结构是最合适的，并且索引顺序访问要比随机访问快更多，数据存储的碎片比索引本身复杂很多，索引碎片通常包含下面的情况：

• 行碎片：数据行的数据被存储在多个数据页当中，碎片可能会导致性能的下降。
• 行间碎片：逻辑顺序上的页，行在磁盘上不顺序存储，行间数据碎片会导致全表扫描。
• 剩余空间碎片：数据页的间隙有大量的垃圾数据导致的浪费。

对于上面几点，对于myisam 都有可能出现，但是innodb的行碎片不会出现，内部会移动碎片重写到一个片段。

索引碎片的处理方式：在Mysql中可以通过optimize table 导入和导出的方式重新整理数据，防止数据碎片问题。

索引规则

• 索引必须按照索引顺序从左到右匹配
• 如果在查询中间出现范围，则范围查询之后的索引失效
• 不能跳过索引列的方式查询（和B+tree索引数据结构设计有关系）

接着是索引顺序问题，由于BTree的结构特性，索引都是按照建立顺序进行查找的，通常不包含排序和分组的情况下，把选择性最高的索引放在最左列是一个普遍正确策略。

如何查看索引基数：show index from sakila.actor，还有一种方式是通过information_schema.statistics 表查询这些信息，可以编写为一个查询给出选择性较低的索引。

当innodb打开某些表的时候会出发索引信息的统计，比如打开information_schema表或者使用show table status和show index的时候，所以如果在系统要运行压力较大的业务时期尽量避开这些操作。

冗余重复索引

Mysql允许同一个列上创建多种类型的索引，有时候会因为建表的特性问题给字段重复建索引造成不必要的性能浪费。冗余索引和重复索引有什么区别？

冗余索引：是符合最左匹配法则的情况下重复对相同列建立索引。

重复索引：是对于不最做的方式创建的索引就有可能是重复创建索引。

比如联合索引：(A,B) 如果在创建 （A）或者（A，B）都是重复索引，但是创建（B）就不是重复索引而是冗余索引。另外某些十分特殊的情况下可能用到冗余索引，但是这会极大的增加索引维护的开销，最为直观的感受是插入、更新、删除的开销变得很大。

多列索引

首先多列索引不是意味着where字段出现的地方就需要加入，其次多列索引虽然在现在主流使用版本中（5.1版本之后）实现了索引内部合并，也就是使用and or或者and和or合并的方式相交使用索引，但是他存在下面几个缺点

• 内部优化器的合并和计算十分耗费CPU的性能，是的索引反而增加数据查询复杂度，效率也不好
• 往往会存在优化过度的情况，导致运行效果还不如全表扫描
• 出现多列索引合并通常意味着建立索引的方式不对，存在反向优化的嫌疑

文件排序

文件排序遵循Innodb的Btree索引的最基本原则：最左前缀原则，如果索引列的顺序和order by排序一致，并且查询列都和排序列都一样才会用索引替代排序，对于多表查询则排序字段全为第一个表才能进行索引排序。但是有一个特例那就是排序字段的前导列为常量的时候依然可以使用索引排序。

案例：rental 表的联合索引列进行排序

Backward index scan 是 MySQL-8.0.x 针对上面场景的一个专用优化项，它可以从索引的后面往前面读，性能上比加索引提示要好的多

EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id desc, customer_id asc;
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Using filesort
EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id desc;
-- Backward-index-scan
-- Backward index scan 是 MySQL-8.0.x 针对上面场景的一个专用优化项，它可以从索引的后面往前面读，性能上比加索引提示要好的多
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Backward index scan
EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id, staff_id;
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Using filesort
-- 无法使用索引
EXPLAIN select rental_id,staff_id from rental where rental_date > '2005-05-25' order by inventory_id, customer_id;
-- 1 SIMPLE rental ALL rental_date 16008 50.00 Using where; Using filesort
EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' and inventory_id in (1,2) order by customer_id;
-- 1 SIMPLE rental range rental_date,idx_fk_inventory_id rental_date 8 2 100.00 Using index condition; Using filesort
explain select actor_id, title from film_actor inner join film using(film_id) order by actor_id;
-- 1 SIMPLE film index PRIMARY idx_title 514 1000 100.00 Using index; Using temporary; Using filesort
-- 1 SIMPLE film_actor ref idx_fk_film_id idx_fk_film_id 2 sakila.film.film_id 5 100.00 Using index