解读

1. 现象本质：慢日志里 rows_examined 代表引擎层“扫描并判断”过的行数，rows_sent 是最终返回给客户端的行数。两者差距越大，说明索引过滤效果越差，引擎做了大量无用功。
2. 国内面试场景：国内互联网并发高、数据量大，慢 SQL 直接决定 P99 延迟和扩容成本。面试官想确认候选人能否“一眼定位索引失效”并给出可落地的优化节奏，而不是背八股文。
3. 高频误区：只谈“加索引”却不讲选择度、最左前缀、回表成本；或只聊“覆盖索引”却不考虑写放大与磁盘抖动，都会被追问到哑口。

知识点

1. 索引失效典型场景
   • 隐式转换：字符串列用数字查，或 utf8mb4 与 utf8 混用。
   • 函数/表达式：DATE(create_time)、MD5(mobile) 导致列值不再有序。
   • 最左前缀断裂：联合索引 (a,b,c) 只查 b、c 或范围查 a 后未继续用 b。
   • OR 条件跨表/跨列：部分子句无索引，优化器退化为全表。
   • 回表成本高：SELECT * 且过滤列不在索引，主键随机读放大。
2. 诊断工具
   • 慢日志 + pt-query-digest：先看执行频率、扫描/返回比。
   • EXPLAIN FORMAT=JSON + optimizer trace：确认优化器是否选错索引。
   • SHOW INDEX / information_schema.statistics：检查基数、重复度。
   • performance_schema.table_io_waits_summary_by_index_usage：识别从未用过的冗余索引。
3. 优化原则
   • 高选择度列放最左；等值在前，范围在后；覆盖索引减少回表。
   • 控制索引宽度＜3072 字节，避免页分裂与自适应哈希瓶颈。
   • 写多读少场景，优先保证主键顺序插入，减少二级索引维护成本。
   • 线上变更必须 INPLACE ALGORITHM=INPLACE, LOCK=NONE，并做影子索引（gh-ost 或 pt-osc），避免 MDL 锁导致雪崩。
4. 国内配套规范
   • 阿里/腾讯/字节均要求“单表索引数 ≤5 个、单索引字段数 ≤5 个”，超出需 DBA 评审。
   • 金融合规要求：索引变更必须走“灰度—回归—压测”三板斧，压测报告需包含 95/99 线对比。
   • 云托管 RDS（阿里云 PolarDB、腾讯云 TDSQL-C）默认开启 SQL 洞察，可直接按“扫描行数”告警，方便性能测试同学闭环。

答案

步骤 1：复现与量化

• 用 pt-query-digest 聚合最近 24h 慢日志，按“rows_examined/rows_sent 降序”取 TOP10，拿到指纹 SQL 与平均扫描比（如 18 000:1）。

步骤 2：快速定位

• EXPLAIN 该 SQL，若 type=ALL 或 range+rows 很大，key 为 NULL 或选错索引，即可确认索引失效。
• 检查 WHERE 列是否存在隐式转换或函数，用 SHOW WARNINGS 看优化器改写后语句。

步骤 3：设计索引

• 将高选择度等值列放最左，范围列置后；若 SELECT 列仅 3~4 个，可直接建覆盖索引，避免回表。
• 计算选择度：SELECT COUNT(DISTINCT col)/COUNT(*) 应＞0.2，否则考虑与其他列组合。
• 预估宽度：utf8mb4 下 varchar(255) 占 1020 字节，若再加 2 列 int，总宽超 1500 字节需缩短或前缀索引。

步骤 4：线下验证

• 在同等数据量（建议 1:1 灌库）的测试环境，用 sysbench 或自研压测脚本，对比旧/新索引执行计划：rows 从 18 000 降到 30，type 从 ALL 变 range，回表次数 0。
• 记录 CPU、QPS、P95 延迟：优化后 P95 从 420 ms 降至 38 ms，QPS 提升 4.7 倍。

步骤 5：线上灰度

• 采用 gh-ost 创建影子索引，设置 —max-load=“Threads_running=50” 避免业务高峰抖动。
• 索引建成后，立即用 ANALYZE TABLE 更新统计信息，防止优化器因旧统计再次选错。
• 观察 24h 慢日志：rows_examined/rows_sent 降至 3:1 以内，告警解除。

步骤 6：回归与监控

• 性能测试团队把该 SQL 加入每日基准巡检，10 倍流量压测 30 min，P99 延迟波动＜5%。
• 在 Prometheus + Grafana 配置 “mysql_slow_query_scan_ratio” 指标，扫描比＞100 持续 5 min 自动开 Jira 工单，形成闭环。

拓展思考

1. 索引下推（ICP）与 MRR 对“扫描远大于返回”场景的补充价值：ICP 可把 WHERE 过滤下推到引擎层，减少回表；MRR 可把随机主键读转为顺序读，对范围查询效果明显。面试时可反问“贵司 MySQL 版本是否开启 MRR，是否做过对比”。
2. 当数据分布倾斜（如 99% 订单状态=已完成）时，普通二级索引选择度极低，需结合“函数索引”或“虚拟列+索引”才能精准过滤，同时避免优化器因“索引代价高于全表”而弃用。
3. 超大分页（limit 100000,20）也会导致扫描行数暴增，此时“延迟关联”或“游标分页（where id＞last_id）”比单纯加索引更有效，可展示你对业务场景与索引成本的综合权衡。
4. 云原生环境下，Serverless 实例的 IOPS 与 内存随负载弹性伸缩，索引优化带来的扫描行下降可直接减少费用（按 IO 计费），性能测试同学可把“成本节省”量化进汇报，体现业务价值。