cyxCoder

写在文章之前：其实在架构技术选型时就应该去考虑数据是应该放到MongoDB、ES、MQ、Redis还是放到数据库中，确定数据的存放的地方才好进行相关优化。

MySQL调优最重要的一个就是查询优化，查询优化主要就是对慢查询的优化，而慢查询的原因一般都是由于查询数据量过大引起的，因此对慢查询的优化更多的就是去减少数据的访问量。

一、慢查询排查

如何确认慢查询的原因，减少数据的访问量？

我们首先需要对慢查询进行分析。

mysql有一个参数：记录所有执行超过long_query_time（默认10s）设定的SQL语句日志。相关参数如下,仅供参考：

# 慢查询日志
show variables like 'slow_query_log';
# 开启慢查询
set GLOBAL slow_query_log = 1;
# 关闭慢查询
set GLOBAL slow_query_log = 0;
# 慢查询默认阈值
show variables like 'long_query_time';
# 设置慢查询阈值为0，方便测试
set GLOBAL long_query_time = 0;
# 慢查询日志记录的路径（Windows默认是在C:\ProgramData\MySQL\MySQL Server XXXX\Data下）
show variables like 'slow_query_log_file';
# 查询没有应用索引的SQL是否记录到慢查询日志中
show variables like 'log_queries_not_using_indexes';
# 开启没有应用索引的SQL是否记录到慢查询日志中
set GLOBAL log_queries_not_using_indexes = 1;

注意‌：上面的参数修改不是永久生效，重启MySQL后失效。要永久生效需要在MySQL的配置文件中设置参数值。

慢查询日志内容介绍：

如下图所示，一段慢查询内容一般是以# Time开头，查询语句SELECT XXXX结尾。

Time表示执行的时间；Query_time表示语句执行时间，Lock_time表示获取锁的时间，一段SQL在数据库中执行的时间可以理解为上面2个时间相加。

Rows_sent表示查询的结果数据，如下图所示为18条，Rows_examined表示扫描了多少条数据，如下图是256条。

然后可以通过mysqldumpslow命令，对慢查询的日志结果进行指定的排序以进行分析。很简单这里就不进行细讲了，可以查看网上其他文章。

二、执行计划

在SQL查询的前面加上EXPLAIN关键字，该命令会返回一个结果集，包含查询执行的详细信息。

返回列说明

• id：查询序列号，表示查询中各个子句的执行顺序。
• select_type：查询类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
• table：当前操作的表名。
• partitions：和分区表有关，一般情况下我们查询语句的执行计划的partitions列的值都是NULL。
• type：连接类型，反映了查询使用索引的效率，常见值有system > const > eq_ref > ref > range > index > ALL。一般来说，得保证查询至少达到range级别，最好能达到ref，ALL即表示全表扫描。
• possible_keys：可能使用的索引。
• key：实际使用的索引。
• key_len：使用的索引长度，越短越好。
• ref：哪些列或常量被用于查找索引列上的值。
• rows：MySQL 估计要读取的行数，值越小越好（注意：这是预测的结果，而不是真实值）。
• filtered：存储引擎返回的数据在经过WHERE条件过滤后，剩下的记录所占的百分比估计值。取值范围：0.00（无数据满足条件）到 100.00（所有数据满足条件）。
• Extra：额外信息，包含查询执行的细节，如Using filesort（文件排序）、Using temporary（使用临时表）等。

注意：EXPLAIN展示的是MySQL查询优化器优化后的执行计划，而非原始的SQL结构。

三、查询优化器

MySQL 查询优化器是数据库的核心组件之一，负责将 SQL 语句转换为最高效的执行计划。

以下列举一些优化器可能执行的关键优化策略：

• 查询重写优化
• 索引优化
• 排序与分组优化
• 聚合优化
• 临时表优化
• 其它优化

3.1、查询重写优化

1、子查询优化：MySQL会将 IN/NOT IN 子查询转换为 JOIN，减少嵌套查询。还会将多个子查询合并为单个查询。

-- 原始SQL
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'China');

-- 优化后
SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.country = 'China';

2、表达式简化：如：WHERE a = 5 + 3 会简化为 WHERE a =8。WHERE 1 = 1 AND a > 5 会简化为 WHERE a > 5

3、连接顺序优化：调整多表JOIN的顺序，优先处理数据量小的表，减少中间结果集。

3.2、索引优化

1、选择最有效的索引

SELECT * FROM users WHERE age > 30 AND gender = 'F';
-- 若存在复合索引 (age, gender) 和 (gender, age)，优化器会选择更高效的索引。

2、索引条件下推

EXPLAIN SELECT * FROM products WHERE category = 'book' AND price > 100;
-- 若有复合索引 (category, price)，会先在索引中过滤price条件。

3、覆盖索引

SELECT id, name FROM users WHERE age = 25;
-- 若索引包含 (age, id, name)，则无需回表。

3.3、排序与分组优化

1、利用索引顺序避免额外排序

SELECT * FROM orders ORDER BY create_time;
-- 若有索引 (create_time)，则直接按索引顺序读取。

2、利用索引实现分组

SELECT category, COUNT(*) FROM products GROUP BY category;
-- 若有索引 (category)，则按索引分组更高效。

3.4、聚合优化

1、在子查询或派生表中提前计算聚合结果

SELECT (SELECT COUNT(*) FROM users) AS total_users, ...;
-- 优化器可能会缓存子查询结果。

2、优化MIN/MAX：若字段有索引，直接取索引首尾值。

SELECT MIN(price) FROM products;
-- 若有索引 (price)，直接取索引第一个值。

3.5、临时表优化

1、优化器会尽量避免创建临时表

SELECT DISTINCT category FROM products;
-- 若category有索引，直接利用索引去重，无需临时表。

2、将临时表存储在内存中（使用tmp_table_size参数控制）

SELECT * FROM users WHERE age IN (20, 25, 30);
-- 小结果集可能使用内存临时表。

3.6、其它优化

1、将 WHERE 条件尽早应用于数据源，减少中间数据量。

2、对大表查询启用多线程并行执行（MySQL 8.0+，parallel_execution_enabled参数要启动）

3、优化 INSERT/UPDATE 语句，把多次单挑插入改为批量插入。

附录

索引调优可以查看我的另一篇文章《Mysql导致B+Tree索引失效的场景汇总》