探秘MySQL——全面了解索引、索引优化规则

本博客实战部分仓库：点击跳转github

0.什么是索引

索引是帮助MySQL高效获取数据的数据结构。简单来讲，数据库索引就像是书前面的目录，能加快数据库的查询速度。
对于海量数据来说，它的目录也是很大的，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中

1.常用索引分类

逻辑维度

- 唯一索引、主键索引：值唯一，前者可以为null，后者不能为null
- 联合(组合)索引：为多个字段创建的索引，遵循左前缀原则，即从最左边的字段开始匹配
- 普通索引：没什么限制，可重复可为空，都行

底层数据结构维度

hash索引：适用于= <> != IN几种情况，精确查找。仅Memory搜索引擎支持。
B+树索引：适用于范围查找。InnoDB和MyISAM支持。

物理维度（InnoDB）

InnoDB提供的一种分类方式，InnoDB的每张表都会有一个聚集索引，有且仅有一个，这也是该表的物理存储方式；非聚集索引可以有多个。

- 聚簇索引（主键索引）：
根据主键构建的索引叫做聚簇索引。将数据存储与索引放到了一块，找到索引也就找到了数据。
优点：检索速度很快，排序查找、范围查找都很不赖；没有回表查询现象。
缺点：主键最好是自增的，因为连续的主键索引性能更好；主键最好设置为不可变，改变主键会导致聚集索引的维护代价很高。- 非聚簇索引（辅助索引）：
不是根据主键构建的索引叫做非聚集索引或者二级索引或者辅助索引。
将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。
缺点：存在回表查询现象

2.为什么底层是B+树

在探讨这个问题前我们先明确一件事： 对于索引的树结构，每个节点称为页，页就是我们上面说的磁盘块，是MySQL数据读取的基本单位。因此，一个节点的读取对应于一次IO操作，底层数据结构的设计应该往减少IO次数的方向进行优化。

如果不知道这几种数据结构的，建议先面向百度学习一下，这不是本文的重点。

平衡二叉查找树

- 缺点：
路数太少，树太深，检索性能有限，而且会导致查找过程中IO次数很多。

红黑树

- 缺点：
1 同样的，路数太少，树太深，检索性能有限；
2 另外，红黑树不是绝对平衡，IO次数会不稳定。
其实所有的二叉树都有类似的局限性。

B树（多叉）

- 优点：
1 路数比较多，成功地将二叉树的瘦长结构优化成了矮胖结构，极大地减少了IO查询次数。
2 另外，叶节点都在同一层上，IO次数是比较稳定的。
其实B树已经比较理想了，那为啥MySQL索引没用B树呢？
- 不足：
1 每个非叶节点存储的是索引+数据，其中数据是一条记录，
试想一下，如果一条记录属性非常多，那么B树的每个节点能存储的数据就会变少，
面对海量的数据，最后B树就会从矮胖型变成瘦长型，IO次数势必无法得到优化
2 注意看叶节点层，不同节点不是连续的，当我们需要进行快速范围检索的时候，B树恐怕无法满足我们的要求。

B+树（多叉）

- 优点：
1 非叶节点仅存储索引，不存储数据，每个节点可存储较多索引值，因此可保证树是矮胖型的，IO次数得到优化
2 叶节点全部在同一层，IO次数十分稳定
3 叶节点保存索引+数据，并且增加了双循环链表的支持，可支持快速范围检索

为什么底层是B+树，现在知道原因了吧，这种设计是不是很巧妙？

3.MySQL索引优化

光了解理论也没什么意思，直接实战。这部分请 下载我在文章一开始提供的github仓库源码，里面有必要的数据。

由于频繁插入数据会导致索引维护代价很大，因此，我没在创建表时添加索引，而是在插入大量数据结束后再手动添加索引。

SQL性能分析之explain

explain可以分析一条sql的优劣，通过mysql反馈我们，需要我们自己去读懂explain的sql执行结果来判断是否要进行优化。

• 使用：

explain sql语句;

建议先看看这个老哥的博客，了解下explain方便我们调优。

Q.MySQL如何查看查询是否用到了索引

执行explain语句，查看结果中的type字段（使用到的索引类型）、possible_keys字段（可用索引，未必是最终使用的）、key字段（实际使用的索引）。

优化一：为表添加自增主键

我的三张表都是使用的InnoDB存储引擎，上面提到了，InnoDB会默认为每张表建立一个聚集索引（主键索引） ，这个聚集索引会以主键为键。因此主键建议自增，且最好不要修改，否则索引维护代价很大。

优化必要性：设置自增主键更加符合主键索引的底层特性（有序、范围查找），使其发挥最高检索效率。并且自增主键后期不建议进行修改。

考虑到我的三张表都没有设置主键自增，因此这是我针对数据库需要优化的第一个地方。

• 表结构修改思路：原id重命名，重新添加自增主键

仅展示一张表

-- ----------------------------
-- 优化1：设置主键自增
-- ----------------------------
DROP TABLE IF EXISTS `claim`;
CREATE TABLE `claim`  (`id` int(0) NOT NULL AUTO_INCREMENT,`app_no` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`tree` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;

重构表结构之后，再执行data.sql插入数据。

优化二：添加唯一索引

注意：插入大量数据之后再设置索引，可以避免索引的频繁维护。

请看claim这张表，这个app_no字段是唯一的，考虑到我需要经常查询这个字段，我给它设置一个唯一索引。

ALTER TABLE claim
ADD UNIQUE INDEX(app_no);

text表中的index字段也是同理：

ALTER TABLE text
ADD UNIQUE INDEX(`index`);

优化三：添加联合索引

联合索引数据存储方式：先对索引中第一列的数据进行排序，而后在满足第一列数据排序的前提下，再对第二列数据进行排序，以此类推。

优化规则：

1 考虑选择性：选择性=count(distinct 字段名)/count(*)
将选择性最高的列放到索引最前列（但是不是绝对的）。
2 把经常同时出现在where and子句中的字段设置成联合索引

ALTER TABLE text ADD INDEX id_app_no(`id`,application_no,date);SELECT * FROM text 
WHERE `id`>1 AND application_no>'EP2567834' AND date>'20170614';

Q.索引失效场景：联合索引非连续

建立联合索引(a,b,c)，where c = 5是否会用到索引？为什么？
用不到，因为联合索引遵循左匹配原则，where c=5子句中联合索引直接从a断开了，所以用不到该联合索引。

优化四：针对order by的优化

• 可优化的情况：

# 查询语句
explain select * from text 
where application_no>'EP2567834' and date>'20170614' 
order by id;

针对这种情况，建立联合索引(application_no,date,id)能命中索引：

注意：where子句中出现的字段放前面，order by中的字段放后面，效率会更高。

alter table text add index app_no_id(application_no,date,`id`);

Q.索引失效场景：order by同时使用ASC 和 DESC

以下情况索引会无法命中：

SELECT FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

Q.性别字段适合添加索引吗

不适合

性别字段因为可重复肯定只能建立非聚集索引，然而因为非聚集索引叶子节点存储的是索引值和聚集索引值，需要回表。所以在性别这种辨别度较低的字段上建立索引，索引树可能只有两个节点，跟线性查找没有太大区别，并且因为回表的存在导致在聚集索引树和非聚集索引树来回切换反而导致查询时间更慢。并且维护该索引还要一定的开销。

参考

博客1
博客2
博客3