zxcfdcmv's Notes

MySQL慢查询

Properties1
tags运维/mysql

8分钟阅读

监控发现

  • long_query_time：建议生产环境逐步下调至 0.1s - 0.5s（默认 10s 毫无意义）。
  • log_queries_not_using_indexes = 1：必须开启。没有使用索引的 SQL 即使在测试环境因数据量小执行很快，上线后随数据增长也会暴雷。

分析定位

利用 EXPLAIN 或 EXPLAIN ANALYZE（MySQL 8.0 推荐）分析执行计划，深度诊断时需特别关注以下核心指标：

核心指标危险信号治理方向
typeALL (全表), index (全索引扫描)优化为 ref、eq_ref 或 range
rows预估扫描行数巨大，接近全表行数精准匹配索引，减少扫描边界
key_len比预期联合索引的总长度短说明联合索引发生了索引截断（如范围查询导致后方失效）
ExtraUsing filesort, Using temporary严重消耗 CPU/内存。需利用索引的有序性消除排序和临时表

优化策略

  1. 索引层深度调优（低成本、高收益）
  • 提升索引区分度：优先选择区分度 >0.1 的列作为索引。利用公式 COUNT(DISTINCT col) / COUNT(*) 评估。
  • 最左前缀与范围阻断：联合索引中，一旦遇到范围查询（>、<、BETWEEN、LIKE），其右侧的列将无法走索引。应将等值条件列放在联合索引左侧，范围条件列放右侧（如：将 (a,b,c) 改为 (a,c,b)）。
  • 覆盖索引，减少回表：尽量让查询的字段全部包含在辅助索引中，避免 Using index condition 演变为高昂的二次回表 IO。
  1. SQL 语句重构（规避优化器陷阱）

  • 防止索引失效：杜绝在 WHERE 子句中对索引列进行函数运算或隐式类型转换（如字符串不加单引号）。

  • 延迟关联 (Deferred Join)：针对大深度的 LIMIT m, n 分页查询（如 LIMIT 1000000, 10），先通过覆盖索引找出主键 ID，再用主键 JOIN 原表获取全量数据，避免百万级数据的无用回表。

  • Or 改写：大数量级下，将 WHERE a=1 OR b=2 改写为 UNION ALL，往往能让两个独立的索引同时生效。

  1. 架构与业务层治理（从源头消灭慢 SQL）

  • 读写分离与从库分流：将报表分析、大批量导出等无法优化的高能耗 SQL，强制分流到只读从库，避免阻塞线上核心业务。

  • 引入缓存/搜索组件：

    • 频繁访问的高频等值查询（如商品详情）移至 Redis。
    • 复杂的模糊搜索（如 LIKE '%关键词%'）直接交由 Elasticsearch 承接。

  • 历史数据归档：表体积过大是慢查询的催化剂。定期通过冷热分离机制，将 1 年前的数据迁移至归档表或 Hive 中。

  1. 数据库参数与硬件调优
  • 加大内存分配：确保 innodb_buffer_pool_size 达到物理内存的 60% - 80%，让绝大多数热数据和索引驻留在内存中。
  • 连接池与资源限制：设置 max_execution_time（MySQL 8.0+），对于执行超过 5s 的前端查询强制断开连接，防止其长时间占满线程池导致服务雪崩。

案例

超大表深分页（LIMIT M, N）导致系统雪崩

  • 业务背景：某电商平台的订单历史列表页，单表数据量 5000万行。
  • 故障现象：用户翻页到后几百页时，页面加载长达 8~10秒，甚至直接报 504 Gateway Timeout。高流量时导致数据库 CPU 瞬间冲到 100%，引发线上雪崩。

  1. 问题分析

    SELECT id, order_no, user_id, status, price, create_time 
 FROM t_order 
 WHERE user_id = 10086 
 ORDER BY create_time DESC 
 LIMIT 200000, 20;
    • 执行计划 (EXPLAIN)：虽然命中了 idx_user_id_create_time 联合索引，但由于 LIMIT 200000，MySQL 优化器需要先扫描前 200,020 条数据，回表 200,020 次去获取整行数据，最后抛弃前 20 万条，只返回 20 条。巨大的 回表 IO 导致了查询极慢。

  2. 治理方案

    利用 覆盖索引（Covering Index）机制，先在索引树上完成深分页计算，只拿到核心的主键 id（不需要回表），最后再关联原表获取其他字段。

    SELECT o.id, o.order_no, o.user_id, o.status, o.price, o.create_time
 FROM t_order o
 JOIN (
     SELECT id 
     FROM t_order 
     WHERE user_id = 10086 
     ORDER BY create_time DESC 
     LIMIT 200000, 20
 ) tmp ON o.id = tmp.id;

  3. 治理效果

    • 耗时对比：从 8.5秒 降至 0.04秒。
    • 原理解析：子查询内部的 SELECT id 完美走覆盖索引，无需任何回表 IO；外层 JOIN 最终只根据 20 个精确的 id 回表了 20 次。

隐式类型转换，导致千万级表全表扫描

  • 业务背景：某社交 APP 的用户信息查询接口，单表数据量 1200万行。
  • 故障现象：根据手机号（phone）查询用户详情，TPS 稍微上升，数据库 CPU 就大幅波动，耗时在 1.5秒 ~ 3秒 之间。
  1. 问题分析 
    -- 注：phone 字段在数据库中的定义是 VARCHAR(20)
 SELECT id, nickname, age FROM t_user WHERE phone = 13800138000;
    • 执行计划 (EXPLAIN)：type = ALL（全表扫描），key = NULL。
    • 致命原因：研发人员在传入参数时，误将手机号作为整型（Int）传入，而数据库中是字符串（VARCHAR）。MySQL 为了执行比对，会在底层对每一行的 phone 字段调用函数进行类型转换（隐式转换），导致 phone 字段上的索引完全失效。
  2. 治理方案
    • 短期修复：在代码层强制将入参转为字符串，确保 SQL 传入单引号。
    SELECT id, nickname, age FROM t_user WHERE phone = '13800138000';
    • 长期治理（长效管控）：在流水线（CI/CD）中加入静态 SQL 审核工具（如 SQLE），凡是发现 SQL 中条件类型与表结构定义不符的，直接拒绝代码合并
  3. 治理效果
    • 耗时对比：从 2.4秒 降至 0.001秒（毫秒级返回）。
    • 原理解析：加上单引号后，SQL 完美命中 uk_phone 唯一索引，执行计划从全表扫描变为 const（常量级别查找）。

关系图谱