mysql慢查询

在 MySQL 中排查慢查询问题，可以按照以下步骤逐步分析并优化：

1. 确认问题 SQL

• 开启慢查询日志：

  -- 查看当前慢查询配置
  SHOW VARIABLES LIKE 'slow_query_log%';
  SHOW VARIABLES LIKE 'long_query_time';
  
  -- 开启慢查询日志（重启后失效）
  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL long_query_time = 2; -- 阈值设为2秒
  

  • 日志路径通常在 /var/lib/mysql/<hostname>-slow.log。
• 使用工具分析日志：
  推荐使用 mysqldumpslow 或 pt-query-digest（Percona Toolkit）分析慢查询：

  mysqldumpslow -s t /var/lib/mysql/slow.log
  pt-query-digest /var/lib/mysql/slow.log
  

2. 分析 SQL 执行计划（EXPLAIN）

对慢查询 SQL 使用 EXPLAIN 或 EXPLAIN FORMAT=JSON，重点关注以下字段：

EXPLAIN SELECT * FROM users WHERE age > 30 ORDER BY create_time DESC;

• 关键字段解读：
  • type：访问类型（性能从高到低）
    const > eq_ref > ref > range > index > ALL（全表扫描需优化）。
  • possible_keys：可能使用的索引。
  • key：实际使用的索引。
  • rows：预估扫描的行数。
  • Extra：额外信息（如 Using filesort、Using temporary 需优化）。

3. 检查索引有效性

• 确认索引是否存在：

  SHOW INDEX FROM users;
  

• 添加缺失索引：

  CREATE INDEX idx_age ON users(age);
  CREATE INDEX idx_age_create_time ON users(age, create_time); -- 复合索引
  

• 避免索引失效的场景：
  • 对索引列使用函数或运算：WHERE YEAR(create_time) = 2023。
  • 隐式类型转换：WHERE phone = 13800138000（phone 是字符串类型）。
  • 前导通配符模糊查询：WHERE name LIKE '%John%'。

4. 优化 SQL 语句

• 减少返回数据量：

  -- 避免 SELECT *
  SELECT id, name FROM users WHERE age > 30;
  

• 拆分复杂查询：

  -- 将子查询改为 JOIN
  SELECT u.* 
  FROM users u
  JOIN orders o ON u.id = o.user_id
  WHERE o.amount > 1000;
  

• 避免全表排序：

  -- 添加 ORDER BY 列的索引
  CREATE INDEX idx_create_time ON users(create_time);
  

5. 检查服务器状态

• 监控资源使用：

  # 查看 CPU、内存、磁盘 I/O
  top
  iostat -x 1
  

• 查看 MySQL 状态：

  SHOW GLOBAL STATUS LIKE 'Threads_running'; -- 当前并发线程数
  SHOW ENGINE INNODB STATUS; -- 查看 InnoDB 引擎状态（锁、事务等）
  

6. 检查锁竞争

• 查看当前锁信息：

  -- 查看未提交的事务和锁等待
  SELECT * FROM information_schema.INNODB_TRX;
  SELECT * FROM information_schema.INNODB_LOCKS;
  SELECT * FROM information_schema.INNODB_LOCK_WAITS;
  

• 优化事务：
  • 避免长事务，尽快提交或回滚。
  • 使用 SELECT ... FOR UPDATE 时尽量缩小锁定范围。

7. 调整配置参数

• 关键参数优化：

  # my.cnf 配置文件
  innodb_buffer_pool_size = 系统内存的 70%-80%  # 缓存池大小
  max_connections = 1000                      # 最大连接数
  query_cache_type = 0                        # 关闭查询缓存（MySQL 8.0 已移除）
  

• 动态调整：

  SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB
  

8. 表结构优化

• 垂直拆分：将大字段（如 TEXT/BLOB）分离到单独的表。
• 水平拆分：按时间或范围分区。

  -- 创建分区表
  CREATE TABLE logs (
    id INT,
    log_date DATE
  ) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
  );
  

9. 使用缓存或读写分离

• 缓存：使用 Redis 缓存热点数据。
• 读写分离：通过主从复制将读请求分发到从库。

总结流程图

发现慢查询 → 记录慢SQL → EXPLAIN分析 → 检查索引 → 优化SQL → 监控资源 → 调整配置 → 表结构优化 → 缓存/分库分表

通过逐步排查，可精准定位性能瓶颈并针对性优化。