MySQL慢查询优化

核心优化思想

优化慢查询不是一蹴而就的，应该遵循一个清晰的流程：
“发现问题 -> 分析问题 -> 解决问题 -> 持续监控”

第一步：定位慢查询 (发现问题)

在优化之前，你首先得知道哪些SQL是慢的。

开启慢查询日志 (Slow Query Log)
这是最核心、最直接的工具。它会记录所有执行时间超过指定阈值的SQL语句。

-- 查看慢查询相关配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 在MySQL配置文件（my.cnf或my.ini）中永久开启，修改后需重启
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 2               -- 单位：秒，定义“慢”的阈值，通常设为1s或0.5s
log_queries_not_using_indexes = ON -- 记录未使用索引的查询（谨慎开启，可能日志量巨大）
log_output = 'FILE'               -- 输出到文件，也可以是TABLE

-- 也可以在运行时临时设置（重启后失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

使用性能分析工具
- mysqldumpslow: MySQL自带的工具，用于解析慢查询日志文件。
  1
  2
  3
  # 分析慢查询日志
  mysqldumpslow -s t /var/lib/mysql/mysql-slow.log # 按耗时排序
  mysqldumpslow -s c /var/lib/mysql/mysql-slow.log # 按出现次数排序
- pt-query-digest (Percona Toolkit): 更强大、更专业的第三方工具。提供非常详细的分析报告，是业界的标准。
  1
  pt-query-digest /var/lib/mysql/mysql-slow.log

通过这些工具，你可以快速找到“最耗时”、“执行次数最多”的SQL，它们就是优化的首要目标。

第二步：分析慢查询 (分析问题)

找到慢SQL后，不要盲目猜测，要用工具分析它为什么慢。

使用 EXPLAIN 分析执行计划 (必会！)
这是SQL优化的重中之重。在SQL语句前加上 EXPLAIN 或 EXPLAIN FORMAT=JSON。
1
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
重点关注以下几个字段：
- type: 访问类型。从好到坏：system > const > eq_ref > ref > range > index > ALL。要尽量避免 ALL（全表扫描）和 index（全索引扫描）。
- key: 实际使用的索引。如果为NULL，说明没有使用索引。
- rows: 预估需要扫描的行数。值越小越好。
- Extra: 额外信息。常见重要值：
  - Using filesort: 表示MySQL无法利用索引完成排序，需要额外的排序操作，开销大。
  - Using temporary: 使用了临时表，常见于GROUP BY和ORDER BY，开销极大。
  - Using index: 使用了覆盖索引，性能非常好。
使用 SHOW PROFILE (已弃用，但仍有版本支持)
可以查看SQL语句执行过程中每个步骤的耗时。
1
2
3
4
SET profiling = 1;
SELECT * FROM your_slow_query;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
使用 Performance Schema (更现代的替代方案)
这是MySQL 5.5+引入的更强大的性能监控工具，可以收集更细粒度的执行信息。

第三步：具体的优化手段 (解决问题)

根据 EXPLAIN 的结果，对症下药。

1. 索引优化 (最有效的手段)

确保有合适的索引：在 WHERE, ORDER BY, GROUP BY, JOIN ... ON 子句中出现的列上考虑建立索引。
前缀索引：对于很长的字符列（如TEXT），可以只对列的前N个字符创建索引，节省空间。
1
CREATE INDEX idx_name ON users(name(10)); -- 只对name的前10个字符索引
覆盖索引：如果索引包含了查询所需的所有字段，MySQL就不需要回表查询数据行，速度极快。
- 坏：SELECT * FROM users WHERE age > 20; (即使有idx_age，也需要回表查所有字段)
- 好：SELECT id, age FROM users WHERE age > 20; (如果索引是 (age, id)，则成为覆盖索引)
联合索引 (最左前缀原则)：联合索引 (col1, col2, col3) 相当于同时创建了 (col1), (col1, col2), (col1, col2, col3) 三个索引。查询条件一定要包含最左列，否则索引失效。
避免索引失效：
- 不要在索引列上使用函数或表达式：WHERE YEAR(create_time) = 2023 （坏）-> WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' （好）
- 避免隐式类型转换：WHERE user_id = '123' （如果user_id是整数，字符串会导致索引失效）
- 使用 LIKE 时前缀不要用通配符：WHERE name LIKE '%abc' （坏，全表扫描）-> WHERE name LIKE 'abc%' （好，可以用索引）
- 谨慎使用 OR，可能使索引失效，考虑用 UNION 代替。

定期分析索引使用情况：删除 unused 的索引。

1	SELECT * FROM sys.schema_unused_indexes; -- 需要先开启performance_schema

2. SQL语句优化

只返回必要的列：坚决不用 SELECT *，需要什么字段就取什么字段。减少网络传输和数据量。

分页优化：传统的 LIMIT 100000, 20 偏移量越大越慢。可以改为：

-- 慢
SELECT * FROM table ORDER BY id LIMIT 100000, 20;
-- 优化后：使用子查询或记住上一页的最大ID
SELECT * FROM table WHERE id > 100000 ORDER BY id LIMIT 20;

优化JOIN：
- 确保 ON 和 WHERE 子句中的列上有索引。
- 小表驱动大表（MySQL优化器通常会自动处理）。
优化ORDER BY和GROUP BY：为排序和分组字段建立索引。GROUP BY 会隐式排序，如果不需要排序，可以用 ORDER BY NULL 取消。
避免使用 SELECT FOR UPDATE：除非必要，它会加锁，影响并发。

3. 数据库设计优化

选择合适的数据类型：越小越好。例如，用TINYINT而不是INT存储状态，用CHAR定长存储很短的字符串（如手机号）。
范式与反范式：适当的反范式设计（如冗余字段）可以减少JOIN，用空间换时间。
垂直拆分/水平拆分(分库分表)：当单表数据量过大时（如千万级），考虑分表。这是最后的大招。

4. 系统配置优化 (调整my.cnf)

innodb_buffer_pool_size：这是最重要的参数！ InnoDB缓冲池，用于缓存数据和索引。通常设置为可用物理内存的 50%-70%。
innodb_log_file_size：redo日志大小，更大的日志可以提高写性能。
max_connections：最大连接数。防止过多连接压垮数据库。

注意：不要盲目修改配置参数，最好在有监控和基准测试的前提下进行调整。

5. 硬件优化

如果上述手段都已用尽，性能仍不达标，可以考虑：

更快的磁盘（SSD）
更多的内存
更强的CPU

总结：优化流程 checklist

开启慢查询日志，设置 long_query_time = 1。
使用 pt-query-digest 找出最需要优化的前3条SQL。
对每条慢SQL使用 EXPLAIN 分析其执行计划。
针对性优化：
- 如果 type 是 ALL 或 index -> 考虑添加索引。
- 如果 Extra 出现 Using filesort 或 Using temporary -> 优化排序和分组，添加索引。
- 如果 rows 值非常大 -> 添加索引以减少扫描行数。
- 检查SQL语句，避免 SELECT *，优化分页等。
使用基准测试验证优化效果（如 sysbench）。
持续监控，优化是一个迭代的过程。

记住，索引不是万能的。每添加一个索引都会影响写操作（INSERT/UPDATE/DELETE）的性能，因为它需要维护索引树。因此，索引的创建需要权衡读写比例。