基于本文回答

播面

0
评论

MySQL中一条慢查询SQL的优化思路是怎样的？

知识点图片

MySQL慢查询优化遵循发现、分析、解决闭环，核心用Explain分析，通过索引及SQL重写提升性能。

优化 MySQL 慢查询是一个系统性的工程，通常遵循 “发现问题 -> 分析问题 -> 解决问题 -> 验证效果” 的闭环流程。

以下是详细的优化思路和步骤：

第一步：发现问题（定位慢 SQL）

在优化之前，首先要抓到“肇事”的 SQL 语句。

  1. 开启慢查询日志 (Slow Query Log)
    • 查看配置：SHOW VARIABLES LIKE '%slow_query_log%';
    • 设置阈值：设置 long_query_time（例如设置为 1 秒或 0.5 秒），超过该时间的 SQL 会被记录。
  2. 使用工具分析日志
    • mysqldumpslow：MySQL 自带工具，可以按照频次、时间排序，帮你找到最耗时的那几类 SQL。
    • pt-query-digest：Percona Toolkit 中的工具，分析报告更详细，能给出优化建议。
  3. 实时监控
    • 使用 SHOW PROCESSLIST 查看当前正在执行的线程，排查是否有锁等待或长时间运行的 SQL。

第二步：分析问题（使用 EXPLAIN）

找到慢 SQL 后，最核心的步骤是使用 EXPLAIN 命令查看执行计划。

关注 EXPLAIN 结果中的关键字段：

  1. type (访问类型) - 性能由好到差排序：
    • system > const > eq_ref > ref > range > index > ALL
    • 优化目标：至少达到 range 级别，最好是 ref。如果是 ALL（全表扫描），必须优化。
  2. key (实际使用的索引)
    • 如果是 NULL，说明没用到索引，这是慢查询的常见原因。
  3. rows (扫描行数)
    • 预估需要扫描的行数。数值越小越好。
  4. key_len (索引长度)
    • 用于判断联合索引中被使用到的列是哪些（是否符合最左前缀原则）。
  5. Extra (额外信息) - 重点关注坏味道：
    • Using filesort：说明 MySQL 无法利用索引进行排序，需要额外的排序操作（CPU 消耗大）。
    • Using temporary：说明使用了临时表（常见于 Group By 或 Distinct），性能损耗严重。
    • Using index：这是好事，说明使用了覆盖索引，不需要回表查询。

第三步：解决问题（具体优化手段）

根据 EXPLAIN 的分析结果，采取具体的优化措施。

1. 索引优化 (最常用、见效最快)

  • 全表扫描 (type=ALL)：
    • 给 WHERE、ORDER BY、GROUP BY 后面的字段添加索引。
  • 违背最左前缀原则：
    • 如果是联合索引 (a, b, c)，查询 WHERE b=1 AND c=1 无法使用索引。必须带上 a。
  • 索引失效陷阱：
    • 函数计算：WHERE YEAR(create_time) = 2023 会导致索引失效，改为 WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'。
    • 类型转换：字符串字段不加单引号（如 phone = 138000），发生隐式转换会导致索引失效。
    • 模糊查询：LIKE '%abc'（前导百分号）无法使用索引，LIKE 'abc%' 可以。
    • OR 条件：OR 两边的字段必须都有索引，否则索引失效。
  • 利用覆盖索引：
    • 尽量避免 SELECT *，只查询需要的列，让查询字段都在索引树中，避免回表（Back-table lookup）。

2. SQL 语句重写

  • 深分页优化 (Deep Pagination)：
    • LIMIT 1000000, 10 会很慢，因为要扫描前 100 万行并丢弃。
    • 优化：利用 ID 或者是上次查询的锚点。
      • SELECT * FROM table WHERE id > 1000000 LIMIT 10;
      • 或者延迟关联：SELECT a.* FROM table a, (SELECT id FROM table LIMIT 1000000, 10) b WHERE a.id = b.id;
  • 优化 JOIN：
    • 小表驱动大表：确保 Left Join 左边是小表（数据量小或过滤后数据量小）。
    • 被驱动表的关联字段必须有索引。
  • 优化 UNION：
    • 尽量使用 UNION ALL 代替 UNION，除非必须去重。UNION 会多一步临时表去重操作。
  • 优化 IN 和 EXISTS：
    • 主表大、子表小，用 IN。
    • 主表小、子表大，用 EXISTS。

3. 表结构与设计优化

  • 字段类型：
    • 能用 TINYINT 别用 INT。
    • 尽量把字段设置为 NOT NULL，NULL 值会增加索引维护成本且让统计变复杂。
  • 反范式设计：
    • 如果关联查询太多，可以考虑在表中增加冗余字段，以空间换时间，减少 JOIN。

4. 系统/架构层面 (当 SQL 优化到极致后)

  • 缓存：引入 Redis 缓存热点数据。
  • 读写分离：主库写，从库读，分担压力。
  • 分库分表：单表数据量超过千万级（经验值），考虑垂直拆分（按业务）或水平拆分（Sharding）。

第四步：验证效果

优化完成后，必须验证：

  1. 再次执行 EXPLAIN，确认 type 提升，rows 减少，Extra 中的坏味道消失。
  2. 执行 SQL，对比执行时间。
  3. 观察系统负载（CPU、IOPS）是否有明显下降。

总结速记 (优化口诀)

  1. 先跑 Explain，全表扫描要避免。
  2. 索引遵循最左侧，覆盖索引效率高。
  3. 模糊查询去前缀，函数运算不要搞。
  4. 大表分页用 ID，Select 星号要去掉。
  5. 类型转换是深坑，单引号里不能少。
右滑查看面试常问