如何巧用索引优化SQL语句性能？

每周聚焦

发布于 2024-05-30 12:00:39

1550

发布于 2024-05-30 12:00:39

为什么在 MySQL数据库中，一条慢查询只要添加上合适的索引，查询速度就能提升一个档次？对于 MySQL，如何巧用索引优化SQL语句性能？需要注意什么问题？

解决问题之前最重要且最难的事情是定位问题，因此，我们需要先定位出慢 SQL，这样才能对症下药进行优化，那么，如何定位慢 SQL呢？

如何判断慢 SQL？

判断慢 SQL的方法有很多种，这里介绍最常用的两种方式：查看执行时间和查看执行计划。

查看执行计划

日常开发中，我们一般会使用“EXPLAIN”命令来查看 SQL语句的执行计划，从而判断 SQL是否存在慢SQL的风向，能否投入生产。

为了更好的解释“EXPLAIN”命令，我们通过一个真实示例来演示，场景：根据 name字段从拥有百万条数据的 user表中来查询记录，EXPLAIN执行计划如下图：

EXPLAIN输出的每个字段解释：

id：标识查询中每个SELECT子句的顺序。通常，id值越大表示优先级越高，越先被执行。
select_type：描述查询的类型。常见值包括：
- SIMPLE：简单SELECT查询，不包含子查询或UNION。
- PRIMARY：最外层的SELECT。
- UNION：UNION中的第二个或后续的SELECT语句
- DEPENDENT UNION：UNION中的第二个或后续的SELECT语句，取决于外部查询
- SUBQUERY：子查询中的第一个SELECT
- DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外部查询
table：查询涉及的表名
partitions：显示查询访问的分区（如果表是分区表）
type：连接类型，表示查询使用的访问方法。常见类型从好到差依次为：
- system：表仅有一行（系统表）
- const：表最多有一个匹配行（常量表）
- eq_ref：对于每个来自前一个表的行，最多有一个匹配行
- ref：对于每个来自前一个表的行，有多个匹配行
- range：使用索引范围扫描
- index：全索引扫描
- ALL：全表扫描
possible_keys：查询中可能使用的索引列表
key：实际使用的索引。如果没有使用索引，则显示 NULL
key_len：使用的索引的长度（字节数）
ref：显示索引的哪一列被使用了，如果可能的话，是一个常量
rows：估计需要读取的行数。这是一个估算值，越小越好
filtered：表示返回的行的百分比。该值是一个估算值，表示在应用表条件后，返回的行数占读取行数的百分比 Extra：其他的额外信息。常见的值包括：
- Using index：只使用索引覆盖扫描（覆盖索引），不需要访问表数据
- Using where：使用了 WHERE子句进行过滤
- Using temporary：使用临时表保存中间结果
- Using filesort：使用文件排序，通常意味着需要优化

上述示例截图中执行计划的结果分析如下：

id：1，表示这是最外层的查询
select_type：SIMPLE，表示这是一个简单查询
table：user，表示查询的表是 user表
partitions：NULL，表示没有使用分区
type：ALL，表示进行了全表扫描
possible_keys：NULL，表示没有使用索引
key：NULL，表示没有使用索引
key_len：NULL，表示没有使用索引，所以索引长度为NULL
ref：NULL，表示索引列与常量进行比较。
rows：1，表示预计读取 936000行数据
filtered：10.00，表示在扫描了user表的所有行之后，只有大约 10%的行满足查询条件并被返回
Extra：Using where，表示使用了WHERE子句进行过滤

通过示例分析可以知道：该查询进行了全表扫描且未使用任何索引，实际耗时是 240毫秒。因此，我们可以判断这条 SQL为慢 SQL（耗时大于 100ms），可以考虑给name创建一个索引来优化：

给 name字段增加一个“index-name”索引，信息如下：

从执行计划可以看出：查询使用了“index_name”索引，实际查询的行数是 1，执行时间从 240ms 降低到 10ms，速度提升了 24倍。

查看执行时间

对于已经投入生产使用的 SQL查询语句，我们一般会通过查看 SQL执行日志，通过 SQL执行时间来判断是否存在慢 SQL，在 MySQL中，可以使用下面的指令来开启慢查询日志和设置慢SQL时间阈值：

sql复制代码SET GLOBAL slow_query_log = 'ON'; -- 开启慢 SQL日志
SET GLOBAL long_query_time = 0.1; -- 设置慢查询阈值为 100毫秒

然后查看日志目录，指令如下：

sql复制代码SHOW VARIABLES LIKE 'slow_query_log_file';

索引优化

在使用索引的时候，需要注意的一些事项和使用技巧：

聚簇索引

首先需要判断 MySQL的引擎是不是 Innodb，它采用的聚簇索引(主键索引)，B+树的非叶子节点(内部节点)存放的是索引值和指向子节点的指针，叶子节点上存放的是索引值和数据。

非聚簇索引，B+树的非叶子节点存储索引值和指向子节点的指针，叶子节点存放的是索引值和聚簇索引值。因此非聚簇索引需要先遍历非聚簇索引B+树定位到聚簇索引的值，再到聚簇索引上回表获取数据。聚簇索引的优点：可以避免每棵索引树上都存放数据，使得在相同的内存空间下存放的更多的索引节点，减少磁盘IO。

聚簇索引示意图如下：

非聚簇索引示意图如下：

聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据。

索引覆盖

在当前索引树上能直接查找所需结果，不需要回表，这就是索引覆盖。

比如上面的案例： select id from user where age = 30 and sex = '男'; 因为id已经在当前索引的叶子节点，所以不需要到聚簇索引上回表，因此这就是一个索引覆盖的场景。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

联合索引

联合索引是指将表中多个字段联合组合成一个索引，比如：index(age, sex)

那么联合索引是如何用B+树实现的呢？

场景：查询用户表中年龄为30岁的男性表结构：

sql复制代码mysql> create table user(
id int primary key,
name varchar(16),
age int not null,
sex varchar(4) not null,
index(age, sex)) engine=InnoDB;

联合索引在 B+树索引模型示意图如下：

查询分析：

ini复制代码首先，从根节点根据组合索引里面的所有字段进行精确匹配查到到age=30 and sex='男'的记录有两条；

然后，获取id2和id3两个节点中指向子节点的指针，定位到子节点，再定位到叶子节点，从叶子节点中拿到聚簇索引的值 id2和id3；

最后，到聚簇索引上遍历id2和id3，直到叶子节点上获取目标数据；

最左前缀原则

在日常的工作中，我们发现查询条件比较多，比如上面的用户表，有根据age和sex查询，有根据name和age查询，也有根据name和sex查询，各种查询组合，那是不是都要为它们创建一个索引呢？答案是不一定。B+树可以利用索引的“最左前缀”来定位记录。最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符

css复制代码比如：联合索引index(a, b, c)
查询条件 where a = ?
where a = ? and b = ?
where a = ? and b = ? and c = ?
where 条件中的字段都可以匹配索引，但是 where a = ？and c = ?   where条件中的a,c只有a 可以匹配 联合索引的a字段。

示例：场景：查询用户表中姓刘的男性联合索引：index(name, sex) B+树索引模型示意图如下：

查询分析：

sql复制代码首先，从根节点查到第一个'刘'开头的记录是id2，然后向后遍历，直到不满足条件为止，最后结果id2，id3两条；

然后，获取指向子节点的指针，定位到子节点，一直到叶子节点，接着比较第2个字段 sex='男'，定位到 id2；

最后，根据id2到聚簇索引上遍历，直到叶子节点上获取目标数据；
从上面的查询分析可以看到：索引前缀原则，查询条件 name like '刘%' and sex = '男'，只用到了联合索引中的name字段，那么set条件没有用到索引会怎么处理呢？  这个就是MySQL5.6引入的索引下推机制，name字段定位了一批数据减少了全表扫描，在符合name like '刘%'的数据集中再筛选sex='男'，这样减少了回表的次数，降低了磁盘IO。

问题3：一个三层的B+树可以存放多少行数据呢？

ini复制代码在Innodb存储引擎里面，最小的存储单元是页(page)，一个页的大小是16KB，
也就是一个节点的大小。根据上文，非叶子节点保存的是索引值和指针，
假设索引id是long类型，占8个byte，指针占6 byte, 所以，
根节点可以存放 16KB / (8 + 6) = 1170 个索引值，因此就有1170个指针，
假设一条数据的大小是1K，因此叶子节点可以存放 16Kb/1K = 16条数据，
所以3层的B+树可以存放 1170 * 1170 * 16 = 21902400行记录