mysql慢查询优化


一 首先找到慢sql

开启慢查询日志

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- 临时开启（推荐设置为1秒）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;


mysqldumpslow ­s c ­t 10 /var/run/mysqld/mysqld­slow.log # 取出使用最多的10条慢查询
mysqldumpslow ­s t ­t 3 /var/run/mysqld/mysqld­slow.log # 取出查询时间最慢的3条慢查询
mysqldumpslow ­s t ­t 10 ­g “left join” /database/mysql/slow­log #得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow ­s r ­t 10 ­g 'left join' /var/run/mysqld/mysqldslow.log # 按照扫描行数最多的

二 分析慢sql

explain select * ......

比如：

mysql> explain select * from example_table where c=1 and a=1 and b=2;
+----+-------------+---------------+------------+------+-------------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys     | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+-------------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | example_table | NULL       | ref  | idx_a,idx_b,idx_c | idx_a | 4       | const |    1 |   100.00 | Using where |
+----+-------------+---------------+------------+------+-------------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

重点关注 ref key type  Extra

type：ALL（全表扫描）最差，index/range/ref 较好，const 最优。

key：实际使用的索引。

rows：预估扫描行数，越大越慢。

Extra：

Using filesort：需要额外排序，性能差。
Using temporary：使用临时表，常见于GROUP BY或ORDER BY。
Using where：在存储引擎层后过滤。
Using index：覆盖索引，无需回表，理想状态。


三 优化策略（对症下药）

1. 索引优化（最常见、最有效）

  • ✅ 为 WHERE、JOIN、ORDER BY、GROUP BY 字段建索引
  • ✅ 复合索引遵循“最左前缀”原则
    如索引 (a,b,c)，查询 WHERE a=1 AND b=2 可命中，WHERE b=2 不会命中。
  • ✅ 避免索引失效场景：
  • ✅ 使用覆盖索引：查询字段全部在索引中，避免回表。

2. SQL语句改写

  • ❌ 避免 SELECT * → 明确列出字段
  • ❌ 避免深度分页：LIMIT 10000, 20
  • ❌ 避免大事务和长查询
  • ✅ 用 JOIN 替代子查询（有时）
  • ✅ 拆分复杂查询，避免一次性处理大量数据

3. 表结构优化

  • ✅ 选择合适的数据类型（如用 INT 而不是 VARCHAR 存ID）
  • ✅ 适当冗余字段减少JOIN（反范式化）
  • ✅ 大表分区（Partitioning）：按时间或范围分区，减少扫描量
  • ✅ 冷热数据分离：历史数据归档

4. 配置与架构优化

  • ✅ 增大 innodb_buffer_pool_size（建议设为物理内存的 70%~80%）
  • ✅ 调整 innodb_io_capacity 提升IO性能
  • ✅ 主从复制 + 读写分离，减轻主库压力
  • ✅ 引入缓存层（Redis/Memcached）缓存热点数据