MySQL慢查询优化

最新推荐文章于 2025-11-10 08:41:31 发布

原创最新推荐文章于 2025-11-10 08:41:31 发布 · 952 阅读

22 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

数据库专栏收录该内容

5 篇文章

订阅专栏

一、什么是慢查询？

慢查询是指执行时间超过指定阈值（long_query_time，通常设置为1秒或0.1秒）的SQL语句。优化慢查询是数据库性能调优中最核心、最直接有效的工作。

优化慢查询的完整流程可以概括为以下步骤，我们将按这个框架展开：

flowchart TD
    A[💡 发现慢查询] --> B[🔍 分析执行计划]
    B --> C[⚡ 制定并执行优化策略]
    C --> D{✅ 验证优化效果}
    D -- 效果良好 --> E[🎉 优化成功]
    D -- 效果不佳 --> B

二、发现与定位慢查询

在优化之前，首先要找到哪些SQL是慢查询。

1. 开启慢查询日志

这是最直接有效的方法。在数据库配置文件中（如MySQL的my.cnf）开启：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1   # 定义慢查询的阈值，单位为秒
log_queries_not_using_indexes = 1 # 记录未使用索引的查询

分析慢查询日志可以使用工具：

mysqldumpslow：MySQL自带的简单分析工具。
pt-query-digest：Percona Toolkit中的强大工具，提供详细报告。

2. 使用性能模式

MySQL的performance_schema和INFORMATION_SCHEMA.PROCESSLIST可以实时查看当前正在执行的慢查询。

SHOW PROCESSLIST; -- 查看当前连接和执行的语句
SELECT * FROM performance_schema.events_statements_history_long WHERE SQL_TEXT IS NOT NULL; -- 详细历史语句

3. 监控与APM工具

数据库监控平台：Prometheus + Grafana + node_exporter
应用性能管理：New Relic, Dynatrace, SkyWalking
这些工具可以直观地展示数据库的响应时间、QPS、慢查询趋势等。

三、分析慢查询：理解执行计划

找到慢查询后，下一步是使用EXPLAIN（或EXPLAIN FORMAT=JSON）分析其执行计划。这是优化的核心环节。

EXPLAIN关键字段解读：

type（访问类型，非常重要）
- 从好到坏大致顺序：
  - system / const：通过主键或唯一索引定位到唯一一行，性能最佳。
  - eq_ref：联表查询时，使用主键或唯一索引进行关联。
  - ref：使用普通索引进行等值查询。
  - range：使用索引进行范围查询（BETWEEN, IN, >, <）。
  - index：全索引扫描（比全表扫描好，因为只读索引树）。
  - ALL：全表扫描，需要重点优化的信号！
key（实际使用的索引）
- 显示查询实际使用的索引。如果为NULL，说明没有使用索引。
rows（预估扫描行数）
- 表示MySQL认为它需要扫描的行数。这个值越小越好。
Extra（额外信息，包含重要优化线索）
- Using filesort：表示MySQL需要额外的一次传递来排序。这通常发生在ORDER BY子句的列没有索引的情况下。需要优化。
- Using temporary：表示MySQL需要创建临时表来存储结果，常见于GROUP BY和ORDER BY子句的列不同时。需要优化。
- Using index：好的信号！ 表示查询使用了覆盖索引，无需回表。
- Using where：表示在存储引擎检索行后，服务器层再进行过滤。

四、慢查询优化策略与实战

策略一：索引优化（最常用、最有效）

为WHERE条件、JOIN条件、ORDER BY/GROUP BY的列创建索引

-- 慢查询示例
SELECT * FROM users WHERE age > 20 ORDER BY create_time DESC;
-- 优化：为(age, create_time)创建复合索引
ALTER TABLE users ADD INDEX idx_age_createtime (age, create_time);

使用覆盖索引，避免回表

-- 慢查询：需要回表查询所有列
SELECT * FROM orders WHERE user_id = 100 AND status = 'SHIPPED';

-- 优化：只查询索引包含的列，利用覆盖索引
SELECT order_id, user_id, status FROM orders WHERE user_id = 100 AND status = 'SHIPPED';
-- 索引应为：(user_id, status, order_id)

遵循最左前缀原则
- 对于复合索引 (col1, col2, col3)，以下查询能使用索引：
  WHERE col1 = ?
  WHERE col1 = ? AND col2 = ?
  WHERE col1 = ? AND col2 = ? AND col3 = ?
- 以下查询不能或不能充分使用索引：
  WHERE col2 = ? （不满足最左前缀）
  WHERE col1 = ? AND col3 = ? （只能用到col1，不能用到col3）

避免索引失效

不要在索引列上使用函数或表达式

-- 坏：索引失效
SELECT * FROM users WHERE DATE(create_time) = '2023-10-01';
-- 好：使用范围查询
SELECT * FROM users WHERE create_time >= '2023-10-01 00:00:00' AND create_time < '2023-10-02 00:00:00';

避免隐式类型转换

-- 假设user_id是字符串类型，但传入的是数字
SELECT * FROM users WHERE user_id = 123; -- 坏：索引失效
SELECT * FROM users WHERE user_id = '123'; -- 好：使用索引

谨慎使用LIKE通配符开头

SELECT * FROM users WHERE name LIKE '%小明%'; -- 索引失效
SELECT * FROM users WHERE name LIKE '小明%'; -- 可以使用索引

策略二：SQL语句重写优化

避免使用SELECT *
- 只取需要的列，减少网络传输和数据加载开销，增加覆盖索引的可能性。

优化分页查询（深度分页）

-- 慢：OFFSET越大越慢，因为它需要扫描并跳过前100000行
SELECT * FROM articles ORDER BY id DESC LIMIT 10 OFFSET 100000;

-- 优化：使用"游标分页"或"seek method"
SELECT * FROM articles WHERE id < 上次查询的最小id ORDER BY id DESC LIMIT 10;

将复杂查询分解
- 有时，一个巨大的、多表连接的复杂查询，不如将其拆分成多个简单的查询，在应用层进行数据组装。这利用了应用服务器的扩展性，避免了数据库的复杂连接操作。

避免使用NOT IN和<>

这类操作通常无法使用索引。考虑改用NOT EXISTS或LEFT JOIN ... IS NULL。

-- 慢
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist);
-- 优化
SELECT u.* FROM users u LEFT JOIN blacklist b ON u.id = b.user_id WHERE b.user_id IS NULL;

策略三：表结构与设计优化

选择合适的数据类型
- 使用尽可能小的数据类型（如TINYINT代替INT）。
- 使用VARCHAR(n)而不是CHAR(n)，除非长度固定。
- 避免使用TEXT和BLOB，如果必须使用，考虑将其分离到单独的表中。
规范化与反规范化的权衡
- 规范化（减少冗余）有利于写操作。
- 反规范化（适当增加冗余，如增加冗余列）有利于读操作，可以减少JOIN。例如，在"订单表"中冗余"用户名"，避免每次查询都要关联"用户表"。
分区表
- 对于非常大的表（如亿级），可以按时间、范围等将数据分割到不同的物理分区中。查询时，优化器可以只扫描相关的分区。

策略四：数据库系统调优

调整服务器参数
- innodb_buffer_pool_size：InnoDB缓冲池大小，通常设置为可用物理内存的70%-80%。这是最重要的参数。
- query_cache_size：查询缓存（在MySQL 8.0中已移除）。
- tmp_table_size和max_heap_table_size：增大这些参数可以减少磁盘临时表的产生。
使用更强大的硬件
- SSD硬盘：随机I/O性能远超机械硬盘。
- 更多内存：增大缓冲池，提高缓存命中率。
- 更快的CPU：加快排序、连接等计算操作。

五、一个完整的优化案例

问题：用户报告"订单列表"页面加载非常慢。

定位慢查询：

# 从慢日志中找到
SELECT o.*, u.username, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.user_id = 123
AND o.create_time BETWEEN '2023-01-01' AND '2023-10-31'
ORDER BY o.create_time DESC
LIMIT 20;

分析执行计划：
- 对上述SQL执行EXPLAIN，发现：
  - orders表的type为ALL（全表扫描）。
  - Extra中出现Using filesort。

优化方案：

步骤1：为orders表创建复合索引 (user_id, create_time)。这个索引可以同时优化WHERE条件和ORDER BY。

ALTER TABLE orders ADD INDEX idx_userid_createtime (user_id, create_time);

步骤2：重写查询，只选择必要的列。

SELECT o.id, o.order_no, o.amount, o.create_time, u.username, p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.user_id = 123
AND o.create_time BETWEEN '2023-01-01' AND '2023-10-31'
ORDER BY o.create_time DESC
LIMIT 20;