mysql 慢SQL的问题如何排查？

**慢查询（Slow Query）** 是指执行时间超过指定阈值的 SQL 查询。在 MySQL 中，可以通过 **慢查询日志** 或 **监控系统** 发现慢 SQL，然后分析其执行计划，找出性能瓶颈并进行优化。 ## **1. 发现问题** ### **1.1 通过慢查询日志** MySQL 允许开启慢查询日志，当 SQL 执行时间超过 `long_query_time`（默认 10 秒），就会记录日志。 #### **启用慢查询日志** 在 `my.cnf` 或 `my.ini` 中添加： ```ini slow_query_log = 1 slow_query_log_file = /var/log/mysql-slow.log long_query_time = 1 # 记录执行时间超过 1 秒的 SQL log_queries_not_using_indexes = 1 # 记录未使用索引的 SQL ``` 然后 **重启 MySQL** 使配置生效： ```sh service mysql restart ``` #### **查询慢查询日志** ```sh tail -f /var/log/mysql-slow.log # 实时查看慢查询日志 ``` 示例日志： ``` # Time: 2024-03-11T10:15:30 # User@Host: root[root] @ localhost [] # Query_time: 3.21 Lock_time: 0.02 Rows_sent: 1000 Rows_examined: 500000 SELECT * FROM orders WHERE status = 'pending'; ``` - `Query_time: 3.21` → SQL 执行耗时 **3.21 秒**。 - `Rows_examined: 500000` → **扫描了 50 万行**，可能存在**全表扫描**问题。 ### **1.2 通过 EXPLAIN 分析查询** `EXPLAIN` 可以分析 SQL 的执行计划，判断是否使用索引，是否存在全表扫描等问题。 ```sql EXPLAIN SELECT * FROM orders WHERE status = 'pending'; ``` 示例结果： | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |----|------------|--------|-------|--------------|------|---------|------|--------|-------------| | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 500000 | Using where | - `type = ALL` → **全表扫描**，查询效率低。 - `possible_keys = NULL` → 没有可用索引，**索引缺失**。 - `rows = 500000` → 需要扫描 50 万行，查询效率低。 ✅ **优化方案**： ```sql CREATE INDEX idx_status ON orders(status); ``` 再次执行 `EXPLAIN`，查看 `type` 是否变为 `ref` 或 `range`，减少 `rows` 扫描行数。 ## **2. 定位问题** 常见导致慢 SQL 的问题： | **问题类型** | **表现** | **EXPLAIN 可能显示** | **解决方案** | |-------------|---------|----------------|-------------| | **无索引** | 扫描大量数据 | `ALL` | 添加索引 | | **索引未命中** | 可能走了错误索引 | `INDEX` / `ALL` | 调整索引结构 | | **回表次数过多** | 使用主键回表查询 | `Extra: Using index` | 尽量使用**覆盖索引** | | **JOIN 过多** | 关联表查询慢 | `Using temporary` / `Using filesort` | 适当**分表** | | **深度分页** | `LIMIT 10000, 10` 很慢 | `Extra: Using filesort` | 使用**延迟关联**优化 | ## **3. 解决问题** ### **3.1 索引优化** ✅ **创建合适的索引** ```sql CREATE INDEX idx_status ON orders(status); ``` ✅ **覆盖索引**（避免回表） ```sql SELECT id, status FROM orders WHERE status = 'pending'; ``` 如果 `status` 是索引的一部分，`SELECT` 可以直接从索引返回数据，避免 `Extra: Using index` 造成的回表操作。 ### **3.2 SQL 语句优化** ✅ **避免 `SELECT *`，查询必要字段** ```sql SELECT id, order_date FROM orders WHERE status = 'pending'; ``` ✅ **避免 `OR`，使用 `UNION ALL`** ```sql SELECT * FROM orders WHERE status = 'pending' UNION ALL SELECT * FROM orders WHERE user_id = 123; ``` ✅ **优化 `JOIN` 查询** - 确保 `JOIN` 字段有索引： ```sql CREATE INDEX idx_user_id ON users(id); ``` ### **3.3 分页优化** 深度分页 (`LIMIT 10000, 10`) 可能导致查询变慢： ```sql SELECT * FROM orders ORDER BY id LIMIT 10000, 10; -- 慢 ``` ✅ **优化方式（延迟关联）** ```sql SELECT o.* FROM orders o JOIN (SELECT id FROM orders ORDER BY id LIMIT 10000, 10) t ON o.id = t.id; ``` 这样先获取 `id`，再回表查询，避免扫描大量数据。 ### **3.4 负载优化** ✅ **开启查询缓存** 如果数据更新不频繁，可以使用 **Redis** 缓存查询结果： ```sh SETEX order:pending 60 "查询结果"; ``` ✅ **使用读写分离** - 主库（Master）处理写操作，从库（Slave）处理读操作： ```sql SELECT * FROM orders WHERE status = 'pending'; -- 走从库 ``` ## **4. 总结** | **优化方向** | **关键优化措施** | |-------------|----------------| | **开启慢查询日志** | 记录 `Query_time > 1s` 的 SQL | | **EXPLAIN 分析** | `type` 是否 `ALL`，`rows` 是否过大 | | **创建索引** | 避免 `ALL`，使用 `ref`、`range` | | **覆盖索引** | `Extra: Using index` 避免回表 | | **优化 JOIN** | 确保 `JOIN` 字段有索引 | | **优化分页** | `LIMIT 10000, 10` 改为**延迟关联** | | **缓存查询结果** | 使用 **Redis** | | **读写分离** | 主库写，从库读 |