深入理解MySQL之SQL调优

最新推荐文章于 2024-07-24 16:18:52 发布

从入门到脱发

最新推荐文章于 2024-07-24 16:18:52 发布

阅读量340

点赞数

分类专栏： MySQL 文章标签： mysql 索引

本文链接：https://blog.csdn.net/CX610602108/article/details/106386217

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章目录

前言
一般查询优化
分页查询优化
排序分组优化
关联查询优化
总结

前言

结合MySQL中B+树的索引结构和Explain关键子给出的查询计划，我们就可以有针对性的对一些慢SQL进行优化，这篇博客就来总结一下优化思路。
先声明一下表结构：

CREATE TABLE `s1` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`key1` VARCHAR(100),
	`key2` VARCHAR(100),
	`key3` VARCHAR(100),
	`key_part1` VARCHAR(100),
	`key_part2` VARCHAR(100),
	`key_part3` VARCHAR(100),
	`common_field` VARCHAR(100),
	PRIMARY KEY (`id`),
	KEY idx_key1 (`key1`),
	UNIQUE KEY idx_key2 (`key2`),
	KEY idx_key3 (`key3`),
	KEY idx_key_part(`key_part1`, `key_part2`, `key_part3`)
) Engine=InnoDB CHARSET=utf8;

CREATE TABLE `s2` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`key1` VARCHAR(100),
	`key2` VARCHAR(100),
	`key3` VARCHAR(100),
	`key_part1` VARCHAR(100),
	`key_part2` VARCHAR(100),
	`key_part3` VARCHAR(100),
	`common_field` VARCHAR(100),
	PRIMARY KEY (`id`),
	KEY idx_key1 (`key1`),
	UNIQUE KEY idx_key2 (`key2`),
	KEY idx_key3 (`key3`),
	KEY idx_key_part(`key_part1`, `key_part2`, `key_part3`)
) Engine=InnoDB CHARSET=utf8;

两张表除了表名外结构一样，除了id主键索引外，还为key1和idx_key3建了普通索引，为key2建了唯一索引，为key_part1和key_part2和key_part3建了联合索引。s1表中有1000条数据，s2表中有10000条数据。

一般查询优化

常见的 type（访问类型）从最优到最差分别为：system > const > eq_ref > ref > ref_or_null > range > index > ALL，一般来说，得保证查询达到 range 级别，最好达到 ref，如果没有达到就需要考虑优化了。

避免全字段查询

尽量在查询的时候，指定查询需要的字段，减少SELECT *的使用，在可能使用索引覆盖的场景下，不仅可以节省MySQL查询优化器对于是否回表的判断开销，还可以减少MySQL回表的次数，扫描索引树就能获得结果。比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb";

在这里插入图片描述

等值匹配

这种情况的优化方式一般是尽量让搜索条件能用上索引，注意联合索引尽量不要出现中间断开的场景，比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb" AND key_part3 = "aa";

在这里插入图片描述
从 key 长度为 303 可以得知只用到了联合索引的第一个列。

模糊匹配

模糊查询可以使用右模糊查询，尽量不要使用左模糊查询，比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 LIKE "bb%"

在这里插入图片描述
右模糊的查询类型是 range 级别，再看一下左模糊：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 LIKE "%bb"

在这里插入图片描述
此时查询类型就变成 index 级别了。

分页查询优化

一般的分页语句：

EXPLAIN SELECT * FROM s2 LIMIT 9000, 5;

在这里插入图片描述
在表记录连续不会物理删除的情况下，即主键连续自增的情况下，可以这样优化：

EXPLAIN SELECT * FROM s2 WHERE id > 9000 LIMIT 5;

在这里插入图片描述
显然优化后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。但是，优化后的 SQL 在很多场景并不适用，因为表中可能某些记录被删后，主键空缺，导致结果不一致。
所以这种优化得满足以下两个条件：

主键自增且连续
结果是按照主键排序的

一般的分页排序语句：

EXPLAIN SELECT * FROM s2 ORDER BY key_part1 LIMIT 9000, 5;

在这里插入图片描述
可以看到并没有使用到联合索引，这是因为扫描整个索引树并查找到没索引的行（可能要遍历多个索引树）的成本比扫描全表的成本更高，所以优化器放弃使用索引。

优化思路是让排序时返回的字段尽可能少，可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下：

EXPLAIN SELECT * FROM s2 s INNER JOIN (SELECT id FROM s2 ORDER BY key_part1 LIMIT 9000, 5) t ON t.id = s.id;

在这里插入图片描述
原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序，且分页和排序使用到了联合索引。

排序分组优化

MySQL支持两种方式的排序 filesort 和 index，Using index 是指 MySQL 扫描索引本身完成排序，filesort 是使用文件排序。index 效率高，filesort 效率低。
排序分组优化方式一般是尽量让排序和分组字段用上索引，比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb" ORDER BY key_part1;

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb" GROUP BY key_part1 ORDER BY NULL;

order by 满足两种情况会使用 Using index：

order by 语句使用索引最左前列
使用 where 子句与 order by 子句条件列组合满足索引最左前列

比如：

EXPLAIN SELECT * FROM s1 WHERE key_part1 = "bb" ORDER BY key_part1, key_part3;

在这里插入图片描述
此时排序条件跳过了联合索引的中间列，导致需要使用文件排序。

需要注意的点：

如果是联合索引，不要出现中间断开的场景
不要出现倒序正序交替的场景，要么都是倒序，要么都是正序
where 高于 having，能写在 where 中的限定条件就不要在 having 中限定

用于排序的联合索引中间断开的场景，比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb" ORDER BY key_part1, key_part3;

在这里插入图片描述
可以看到只用到了联合索引的第一个列。

出现倒序正序交替的场景，比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb" AND key_part2 = "aa" ORDER BY key_part1, key_part2;

在这里插入图片描述
此时都是正序排序，Extra 提示 Using index 扫描索引就可以完成排序，如果正序倒序交替，比如：

EXPLAIN SELECT * FROM s1 WHERE key_part1 = "bb" ORDER BY key_part1 ASC, key_part3 DESC;

在这里插入图片描述
此时 Extra 提示 Using filesort 需要用到文件排序。

限定条件用在 having 和 where 的比较：

EXPLAIN SELECT key_part1, COUNT(id) num FROM s1 GROUP BY key_part2 HAVING key_part1 = "aa";

在这里插入图片描述

EXPLAIN SELECT key_part1, COUNT(id) num FROM s1 WHERE key_part1 = "aa" GROUP BY key_part2;

在这里插入图片描述

关联查询优化

MySQL的表关联常见有两种算法：

Nested-Loop Join（NLJ）算法
Block Nested-Loop Join（BNL）算法

1、嵌套循环连接 Nested-Loop Join（NLJ）算法
循环从驱动表中读取行数据，在这行数据中获取到关联字段，根据关联字段在被驱动表里取出满足条件的行，然后取出两张表的结果合集。

EXPLAIN SELECT * FROM s2 INNER JOIN s1 ON s1.key3 = s2.key3;

在这里插入图片描述
从执行计划中可以看到这些信息：

驱动表是 s1，被驱动表是 s2。可以看到优化器一般会优先选择小表作为驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
使用了 NLJ 算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的算法是 NLJ。

SQL的执行过程大致如下：

从表 s1 中读取一行数据
从取出的一行数据中取出关联字段 key3，到表 s2 中查找
取出表 s2 中满足条件的行，跟 s1 中获取到的结果合并，作为结果返回给客户端
重复上面步骤，直到连接条件不满足

整个过程会读取 s1 表的所有数据（扫描1000行），然后遍历每行数据中字段 key3 的值，根据 s1 表中 key3 的值索引扫描 s2 表中的对应行（扫描1000次 s2 表的索引，1次扫描可以认为最终只扫描 s2 表一行完整数据，也就是总共 s2 表也扫描了1000 行）。因此整个过程扫描了 2000 行。

2、基于块的嵌套循环连接 Block Nested-Loop Join（BNL）算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行读取出来跟 join_buffer 中的数据做对比。如果被驱动表的关联字段没索引，MySQL会选择 Block Nested-Loop Join 算法。

EXPLAIN SELECT * FROM s2 INNER JOIN s1 ON s1.common_field = s2.common_field;

在这里插入图片描述
Extra 中的 Using join buffer （Block Nested Loop）说明该关联查询使用的是 BNL 算法。上面SQL的执行过程大致如下：

把 s1 所有的数据放入到 join_buffer 中
把表 s2 中每一行数据取出来，跟 join_buffer 中的数据做对比
返回满足连接条件的数据

整个过程对表 s1 和 s2 都做了一次全表扫描，因此扫描的总行数为11000行（表 s1 的数据总量 1000 + 表 s2 的数据总量10000）。并且 join_buffer 里的数据是无序的，因此对表 s2 中的每一行，都要做 1000 次判断，所以内存中的判断次数是 1000 * 10000= 1000 万次。
可以看到这种算法的扫描行数为两张表的记录总和，并且还有两张表数据量乘积的内存比较操作，但是相比于磁盘扫描，内存比较操作要快得多。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 NLJ 呢？
如果第二条SQL使用 NLJ 算法，由于没有索引，所以只能遍历扫描全表，那么磁盘扫描表数据就达到了1000 * 10000= 1000 万次，而用 BNL 算法的磁盘扫描行数会少很多，相比于磁盘扫描，BNL 算法的内存计算会快得多。

因此MySQL对于被驱动表的关联字段没有索引的关联查询，一般为了减少磁盘扫描的 IO 开销，会倾向于使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL 算法性能更高。

对于关联查询的优化：

关联字段加索引，让MySQL做关联操作时尽量选择 NLJ 算法
小表驱动大表，写多表连接SQL时如果明确知道哪张表是小表可以用 straight_join 写法固定连接驱动方式，省去MySQL优化器优化的性能开销。

straight_join 功能同 join 类似，但能指定让左边的表来驱动右边的表，能修改表优化器对于联表查询的执行顺序。
比如可以用 NLJ 算法查询的SQL语句，使用了 straight_join 后换成了 BNL 算法：

EXPLAIN SELECT * FROM s2 STRAIGHT_JOIN s1 ON s1.common_field = s2.key1;

在这里插入图片描述
强制用 s2 表作为驱动表后，原来能用 NLJ 算法现在MySQL使用了 BNL 算法来执行查询。使用的时候注意两点：

只适用于 inner join，并不适用于 left join，right join。（因为left join，right join已经指定了表的执行顺序）
尽可能让优化器去判断，因为大部分情况下MySQL优化器是比人要聪明的。使用 straight_join 一定要慎重，部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

总结

为表建立合适的索引，但是也不要建立太多索引，索引有开销和维护的成本，不合适的索引反而适得其反
模糊查询可以使用右模糊查询，尽量不要使用左模糊查询
联合索引要遵守最左前缀法则，查询从索引的最左列开始并且不跳过索引中的列
尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则
不在索引列上做任何操作（计算、函数、类型转换等），会导致索引失效而转向全表扫描
尽量使用覆盖索引（只访问索引包含的列的查询），减少SELECT * 语句
MySQL在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描
IS NULL，IS NOT NULL 无法使用索引，有的版本可以
字符串不加单引号会导致索引失效
少用 OR 或 IN，MySQL内部优化器会根据检索比例、表大小等多种因素整体评估是否使用索引
范围查询优化，将大的查询范围拆成多个小的查询范围
关联字段加索引，让MySQL做关联操作时尽量选择 NLJ 算法
小表驱动大表，写多表连接SQL时如果明确知道哪张表是小表可以用 straight_join 写法固定连接驱动方式，省去MySQL优化器优化的性能开销。

从入门到脱发

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
深入理解MySQL之SQL调优

文章目录前言一般查询优化避免全字段查询等值匹配模糊匹配分页查询优化排序分组优化关联查询优化总结前言结合MySQL中B+树的索引结构和Explain关键子给出的查询计划，我们就可以有针对性的对一些慢SQL进行优化，这篇博客就来总结一下优化思路。先声明一下表结构：CREATE TABLE `s1` ( `id` INT NOT NULL AUTO_INCREMENT, `key1` VARCHAR(100), `key2` VARCHAR(100), `key3` VARCHAR(100),
复制链接

扫一扫