我印象最深的一次线上事故是一条查订单列表的 SQL在单表数据量刚过百万的时候响应时间从 300ms 一路涨到了 8 秒。现象很典型主库 CPU 飙升慢查询日志刷屏。当时团队第一反应是“加个索引”。但我被 DBA 同事反问了一句“你确定加了索引MySQL 就一定会走吗”这句话点醒了我。很多人对索引的理解停留在“建了索引查询就快”但到底走没走索引、为什么没走、加了之后是不是真有收益全靠感觉没有验证手段。从那以后我养成了一个习惯凡是涉及索引的调整一定先用一条简单的 SQL 去验证。不是靠猜而是看 MySQL 优化器给出的执行计划再配合真实耗时把“索引有没有用”这件事用数据钉死。这个过程其实不复杂今天我就用一条最基础的验证 SQL完整走一遍怎么验证 MySQL 索引以及怎么顺着同一个思路把索引失效的常见原因也排查清楚。这篇文章适合所有写 SQL 的人不管是刚入行的后端开发还是对执行计划一知半解的运维同学。你不需要懂很深的源码只需要一台 MySQL 实例按下面的步骤操作一遍以后再看索引问题心里会有底很多。1. 为什么我要用一条 SQL 去证明索引有用1.1 一次因为“没走索引”的线上事故给我的教训那次事故的 SQL 其实特别简单大概长这样SELECT * FROM orders WHERE user_id ? AND status ? ORDER BY create_time DESC LIMIT 20;逻辑上没有任何问题。可问题是数据量上来之后这张表的 user_id 这列没有索引MySQL 在处理 WHERE user_id ? 的时候只能从头到尾扫把整张表的聚簇索引叶子节点全过一遍然后再做排序、取 20 条。百万行数据不算多但每次请求都扫一遍并发一上来 CPU 直接打满。后来加了索引效果立竿见影。但也正是这次经历让我发现一个尴尬的事实很多人包括当时的我压根没法回答“索引为什么让这条 SQL 变快”这个问题更别说在加索引之前先去验证预判了。加索引本身不复杂复杂的是判断“加了之后是否能被优化器选中”以及“选中之后是否真的把成本降下来了”。1.2 验证索引的裁判只有一个优化器很多初学者有个误区以为索引建好了查询就会自动用。实际上走不走索引的决定权在 MySQL 优化器手里。优化器会根据表的统计信息、索引区分度、扫描行数、回表成本等因素选一个它认为最低成本的执行路径。它不关心你建了多少索引只关心哪个路径最便宜。所以验证索引有没有生效本质上是验证优化器是否把某个索引选进了执行计划。而看执行计划这件事在 MySQL 里就一行命令EXPLAIN SELECT ...;这一条 SQL就是我们讨论的“最简单的验证索引的 SQL”。1.3 适合谁来读这篇这篇文章不是纯理论科普每一步都会给出可以复现的建表语句、造数语句、验证语句。你如果正在做后端开发写 SQL 是日常学会了 EXPLAIN你在 code review 时一眼就能看出同事的 SQL 有没有踩索引失效的坑。你如果是 DBA 或者运维这篇文章可以帮你把常见的索引失效场景串成一套检查思路以后接到慢 SQL 工单不用瞎猜。2. 先造一张能让结果说话的表验证索引这种事最怕的是数据量太小。十几行数据MySQL 优化器怎么都不肯走索引因为扫全表也就是多读几个页的事走索引反而要回表绕一圈。为了让结果有说服力我建议你直接造一张百万行级别的表。2.1 测试表的结构怎么设计我用的测试表模拟了一个员工信息场景字段尽量覆盖日常开发的常见类型字符串、数字、日期、枚举状态。建表语句如下CREATE TABLE t_emp ( id INT NOT NULL AUTO_INCREMENT, emp_no VARCHAR(32) NOT NULL COMMENT 员工编号, name VARCHAR(50) NOT NULL COMMENT 姓名, department VARCHAR(50) DEFAULT NULL COMMENT 部门, salary DECIMAL(10,2) DEFAULT NULL COMMENT 薪资, hire_date DATE DEFAULT NULL COMMENT 入职日期, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1在职 0离职, last_login DATETIME DEFAULT NULL COMMENT 最近登录时间, PRIMARY KEY (id), UNIQUE KEY uk_emp_no (emp_no), KEY idx_dept_salary (department, salary), KEY idx_hire_date (hire_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;注意我一开始故意没给 name 字段建索引。这样一会儿就能先用 name 做一次全表扫描再给它建索引跑一遍 EXPLAIN前后对比会非常直观。2.2 一百万行数据怎么快速造出来MySQL 8.0 可以用递归 CTE 快速造数。我写了一个百万行级别的插入脚本SET SESSION cte_max_recursion_depth 1000000; INSERT INTO t_emp (emp_no, name, department, salary, hire_date, status, last_login) WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n 1 FROM seq WHERE n 1000000 ) SELECT CONCAT(EMP, LPAD(n, 8, 0)), CONCAT(user_, n), ELT(1 (n % 20), 技术部,产品部,运营部,市场部,财务部,人力部,法务部,客服部,数据部,架构部,测试部,运维部,安全部,内容部,销售部,商务拓展部,供应链部,风控部,设计部,战略部), ROUND(RAND() * 50000 5000, 2), DATE_SUB(2024-01-01, INTERVAL (n % 3650) DAY), IF(n % 100 0, 0, 1), DATE_SUB(NOW(), INTERVAL (n % 365) DAY) FROM seq;如果你用的 MySQL 版本较旧递归 CTE 不可用也可以用存储过程循环插入原理一样。关键是让数据量、区分度接近真实业务否则验证结果参考价值不大。插入完成后确认一下数据量SELECT COUNT(*) FROM t_emp;正常情况下应该返回 1000000。2.3 动手之前先确认两件事索引和基数开始验证之前先看看这张表有哪些索引并且了解一下每个索引列的数据分布情况。前者用 SHOW INDEX后者要看区分度。SHOW INDEX FROM t_emp;结果里重点看 Cardinality 这一列它表示索引列的去重估算值。比如 name 字段这一百万行基本都是唯一的Cardinality 接近 1000000这说明索引区分度很好优化器更愿意用它。区分度也可以直接跑一条 SQL 算出来SELECT COUNT(DISTINCT name) / COUNT(*) AS name_cardinality, COUNT(DISTINCT department) / COUNT(*) AS dept_cardinality FROM t_emp;department 字段只有 20 种值区分度 0.00002这种字段即使单独建索引优化器也不会优先选它。这也是为什么我的联合索引设计成 idx_dept_salary(department, salary)因为 department 作为前缀虽然区分度低但能配合 salary 过滤出一批精确行。3. 用 EXPLAIN 看执行计划最基本的索引验证 SQL3.1 两条 SQL 的对比同样的 WHERE截然不同的 type现在开始真正的验证。先用没有索引的 name 字段查一个人EXPLAIN SELECT id, emp_no, name, department, salary FROM t_emp WHERE name user_500000\G执行计划的关键部分是这样type: ALL possible_keys: NULL key: NULL rows: 1000000 Extra: Using wheretype 是 ALL含义是全表扫描key 是 NULL说明没有任何一个索引参与这次查询rows 估算扫描 100 万行。这种 SQL 在上线环境就是典型的慢查询全表扫一遍只是时间问题。接着给 name 字段加上索引ALTER TABLE t_emp ADD INDEX idx_name (name);再跑一次同样的 EXPLAINtype: ref possible_keys: idx_name key: idx_name key_len: 202 ref: const rows: 1 Extra: NULL差异一目了然。type 从 ALL 变成 refkey 从 NULL 变成 idx_namerows 从 1000000 变成 1。这一条 SQL 就把“索引有没有生效”证明得清清楚楚。WHERE name user_500000 这个条件在 B 树里是按照等值路径找到那一条记录的。3.2 执行计划里这几个字段才是关键EXPLAIN 会输出很多字段但验证索引是否生效优先看这五个字段含义怎么判断好坏type访问类型ALL 最差index 其次range/ref/eq_ref/const 都好possible_keys优化器认为可能用到的索引不是最终结果只是候选池key优化器实际选择的索引为 NULL 表示没走索引key_len使用到的索引字节长度用于判断联合索引用了几列rows优化器估算的扫描行数越小越好但只是估算值type 这个字段尤其重要。我一般会直接看它的值如果是 ALL基本可以判定这条 SQL 没走索引如果是 index也要警惕它表示扫描了整棵二级索引树虽然比聚簇索引小但依然不是等值定位。真正好的等值查询至少要做到 ref 级别。extra 里还有一些关键信号比如 Using where、Using index、Using filesort后面细说。3.3 用 key_len 判断联合索引有没有“用满”如果只验证单列索引key_len 可能没人在意。但一旦涉及联合索引key_len 就是验证索引使用情况的神器。我们这张表上有 idx_dept_salary(department, salary) 联合索引。先看只用 department 条件的情况EXPLAIN SELECT * FROM t_emp WHERE department 技术部\G执行计划里 key 是 idx_dept_salary但 key_len 是 203。203 怎么算出来的department 是 VARCHAR(50)字符集 utf8mb4最多占用 50×4200 字节加上变长字段长度前缀 2 字节再加上字段允许 NULL 时的 1 字节标记就是 203。再看同时用 department 和 salary 的情况EXPLAIN SELECT * FROM t_emp WHERE department 技术部 AND salary 20000\G这次 key_len 是 207比刚才多了 4 字节。这 4 字节就是 salary 这个 DECIMAL(10,2) 字段在索引里占用的空间。这两个 key_len 的差值说明联合索引在后一种查询里把两列都用上了前一种只用了第一列前缀。这个细节在优化联合索引时非常有用——一旦发现 key_len 没有包含预期字段的字节数就说明 SQL 的写法没有把索引用满。3.4 Extra 会告诉你索引在背后多做了一些事EXPLAIN 的 Extra 字段经常被忽略但它能解释很多性能细节。举几个和索引验证直接相关的Using whereWHERE 条件在存储引擎层返回后Server 层又做了一次过滤。这个不能直接判定为“没走索引”要结合 key 字段判断。Using index覆盖索引查询所需的列都能从索引树里取到不需要回表。这种是一件好事。Using index condition触发了 ICP索引条件下推MySQL 把部分 WHERE 条件下推到存储引擎层回表前先过滤一遍能明显减少回表次数。Using filesort排序没有用到索引MySQL 额外做了一次文件排序。如果 ORDER BY 后面的字段没有对应的索引顺序就会出现这个。比如查一下用户的姓名只返回 name 和 emp_no因为 emp_no 本身就是唯一索引且查询列都在索引里就会出现 Using indexEXPLAIN SELECT emp_no, name FROM t_emp WHERE name user_500000\G这说明这条 SQL 不仅走了索引而且回表都省了。Extra 和 key_len 配合起来看你对一条 SQL 的“索引使用程度”就能精准掌握。4. 用真实耗时和计数器做双重确认EXPLAIN 告诉我们的是优化器的执行计划属于“预判”。但实际跑起来到底快不快还需要看真实耗时和存储引擎层的读取行为。这也是验证索引的第二个层次。4.1 为什么执行计划说走索引线上还是慢有一种常见情况EXPLAIN 显示这条 SQL 确实走了索引但线上依然慢。原因通常不在“走没走索引”而在于“扫描行数虽然少了但回表次数多”或者“数据页缓存命中率低”。比如查出来一万条满足条件的数据每条都要回表读一次聚簇索引的随机页一万次随机 IO 不是闹着玩的。所以执行计划只是第一步真实执行耗时、真实扫描行数才能反映问题。4.2 用 EXPLAIN ANALYZE 拿到真实执行时间和行数MySQL 8.0.18 之后提供了 EXPLAIN ANALYZE它真正把 SQL 跑一遍然后返回每一步的实际执行时间、实际扫描行数和循环次数。我验证索引时最常用的就是它EXPLAIN ANALYZE SELECT id, emp_no, name, department, salary FROM t_emp WHERE name user_500000\G输出大致如下个人环境数据- Filter: (t_emp.name user_500000) (cost38949.9 rows1) - Table scan on t_emp (cost38949.9 rows378383) (actual time0.025..54.123 rows1 loops1)注意看 actual rows1但第一次是全表扫描时第二个节点显示扫描了整个表实际耗时 54ms 左右。这个 54ms 是在本地单机缓存场景下跑出来的如果没加索引线上真实环境可能就是几百毫秒甚至秒级。加了 idx_name 之后再看EXPLAIN ANALYZE SELECT id, emp_no, name, department, salary FROM t_emp WHERE name user_500000\G输出会变成类似- Index lookup on t_emp using idx_name (nameuser_500000) (actual time0.032..0.107 rows1 loops1)actual time 从几十毫秒级别降到零点几毫秒索引有没有用这一行数据就是铁证。如果环境不支持 EXPLAIN ANALYZE也可以手动开启 profiling。虽然 MySQL 8.0 之后 SHOW PROFILE 已经标记为 deprecated但在不少老版本上仍然可用SET profiling 1; SELECT * FROM t_emp WHERE name user_500000; SHOW PROFILES; SHOW PROFILE FOR QUERY 1;它会把一条 SQL 的各个阶段耗时拆开方便定位瓶颈是 CPU、IO 还是网络。4.3 Handler_read 计数器怎么看存储引擎层的读取计数器也能辅助验证。做法很简单FLUSH STATUS; SELECT * FROM t_emp WHERE name user_500000; SHOW SESSION STATUS LIKE Handler_read%;走全表扫描时Handler_read_next 会非常接近扫描行数走索引等值查询时Handler_read_key 会比较突出表示通过索引键值去读取记录。这个计数器是会话级的FLUSH STATUS 之后只统计当前会话的执行结果所以看到的数值可解释性很强。要注意的是Handler_read 系列不能单独作为诊断依据因为不同存储引擎、不同查询场景下计数含义有差异。它更适合配合 EXPLAIN 来佐证多个证据指向同一个结论才可靠。5. 顺着这套方法把索引失效场景也查清楚掌握了“用一条 SQL 验证索引”的方法最大的价值是把同样的套路迁移到“索引失效排查”上。下面几个场景都是我在实际工作中踩过、或者帮别人排查过的典型坑。每个场景都可以用 EXPLAIN 复现出来。5.1 隐式类型转换最常见也最隐蔽先跑一个正常走索引的查询EXPLAIN SELECT * FROM t_emp WHERE emp_no EMP000500000\Gemp_no 是 varchar 类型条件里也是字符串执行计划会走 uk_emp_notype 是 const。但如果把条件值写成数字类型EXPLAIN SELECT * FROM t_emp WHERE emp_no 500000\G执行计划立刻变成 typeALLkeyNULL。原因是 MySQL 在比较时会把 emp_no 隐式转换成数字相当于对索引列做了一次转换索引的排序结构就派不上用场了。这种问题在真实业务里极容易出现因为很多接口参数是前端传进来的后端没做类型校验数据库字段是 varchar传进来的却是 JSON number。排查方法很直接EXPLAIN 一看 key 没了再确认字段类型和条件类型基本就能定位。5.2 函数运算让索引瞬间“隐形”对索引字段做函数运算是另一个高频失效原因。比如 hire_date 上有 idx_hire_date但如果你写成EXPLAIN SELECT * FROM t_emp WHERE DATE(hire_date) 2023-05-20\G执行计划会显示 keyNULL。因为 DATE() 函数包裹了 hire_dateB 树里存的是原始日期值没法直接按 DATE(hire_date) 的运算结果去二分查找。正确写法是改成范围查询EXPLAIN SELECT * FROM t_emp WHERE hire_date 2023-05-20 AND hire_date 2023-05-21\G这样就走上了 idx_hire_datetype 是 rangekey_len 是 4date 类型固定 3 字节可空再加 1。同样的道理适用于 LEFT(name, 3)、YEAR(hire_date) 这类函数。如果你确实需要这种模糊搜索优先考虑改成 LIKE 前缀匹配或者 MySQL 8.0 的函数索引。5.3 联合索引的最左匹配不是建了索引就能用idx_dept_salary(department, salary) 是联合索引底层 B 树先按 department 排序department 相同再按 salary 排序。所以查询条件是 department 在前时索引能正常使用EXPLAIN SELECT * FROM t_emp WHERE department 技术部\G但如果跳过 department直接查 salaryEXPLAIN SELECT * FROM t_emp WHERE salary 20000\G执行计划里 key 通常要么是 NULL要么是 typeALL。原因很简单salary 的排序依赖前面的 department直接拿 salary 去 B 树里找相当于在一本先按姓氏再按名字排序的电话簿里只告诉对方“我想找名字叫 kaiwen 的人”没法直接定位。这个规则对索引验证的启示是联合索引在设计时要把最常等值查询、区分度相对好的列放在最前面验证时则要看 key_len 是否覆盖了用到的索引列不要看到 key 里有索引名就以为万事大吉。5.4 把失效场景做成一份对照清单用 EXPLAIN 验证了大量 SQL 之后我整理出了一份高频失效场景对照表平时遇到慢 SQL 可以直接对照排查场景问题写法有效写法EXPLAIN 表现隐式类型转换emp_no 500000emp_no 500000key 变 NULL函数运算DATE(hire_date) 2023-05-20hire_date 起始 AND hire_date 次日key 变 NULL联合索引不是最左列WHERE salary 20000WHERE department 技术部 AND salary 20000key 可能为空或扫描行数很大LIKE 前缀模糊name LIKE %user_5%name LIKE user_5%前者 keyNULL后者可用索引负向条件status 1改成正向等值/范围负向条件通常不走索引OR 连接非索引列name ? OR emp_no ?用 UNION 拆分或让每个分支都走上索引可能走全表扫描这份清单不需要死记只要每次遇到慢 SQL 都跑一遍 EXPLAIN看 key 和 type再看 SQL 写法很快就能形成条件反射。6. 验证过程中容易踩的四个坑这部分是我自己在大量验证测试里总结出来的全是实测经验。6.1 数据量太小优化器可能故意不走索引前面提过优化器会估算全表扫描成本和索引查找成本。如果表只有几千行全表扫描可能只需要读几个页走索引反而需要先查 B 树、再回表成本更高。所以小表上 EXPLAIN 经常出现 typeALL、key 为 NULL这不是索引失效而是优化器觉得没必要。这就提醒我们要验证索引的真正效果测试数据量必须接近真实生产规模而且数据要有区分度。否则会得出“加了索引也没用”的错误结论。6.2 EXPLAIN 的 rows 只是估算不是事实optimizer 的 rows 是基于统计信息估出来的不是精确扫描行数。统计信息完全可能因为长时间没更新而失真尤其在频繁增删改的表上。所以我在关键 SQL 上会再用 EXPLAIN ANALYZE 跑一次看看 actual rows 和执行计划估算的差异大不大。如果发现估算和实际差得远建议先执行ANALYZE TABLE t_emp;让优化器重新统计再跑 EXPLAIN。这个操作不会锁表太久在低峰期执行比较稳妥。6.3 缓存造成的“性能变好”假象刚验证完索引时很多人习惯只跑一次 SQL 就开始对比耗时。但其实第二次执行可能因为数据页已经在 buffer pool 里响应时间大幅下降这不一定是索引的功劳。习惯做法是每条 SQL 连续执行三到五次去掉最快和最慢取中间值对比。如果还想看冷缓存效果可以重启实例或者清 buffer pool生产环境慎做。多数情况下多次执行取稳定值比单次执行更有说服力。6.4 同一套 SQL 在不同环境可能给出不同执行计划开发环境、测试环境、生产环境的 MySQL 版本可能不一样optimizer_switch 参数也可能被调过表中数据的分布和统计信息更是千差万别。因此本地 EXPLAIN 显示走索引不代表生产环境就一定会走。我在正式上线之前会要求至少在生产低峰期用只读 SQL 跑一次 EXPLAIN ANALYZE 验证确认执行计划和性能都没有问题。7. 把“验证索引”变成写 SQL 的肌肉记忆验证索引这件事本质上是一个闭环写 SQL、看执行计划、确认访问路径、观察真实耗时。把它固化到日常开发流程里比临时遇到性能问题再排查有效得多。7.1 我日常的一个简单验证流程我现在拿到任何一条将要上线的 SQL不管简单还是复杂都会按下面五步走先跑 EXPLAIN看 type 是不是 ALL、key 是不是 NULL、rows 是不是过大。对该 SQL 涉及的条件列确认是否有可用的索引以及索引顺序是否符合 SQL 写法。用 EXPLAIN ANALYZE 看真实扫描行数和耗时验证优化器的估算是否靠谱。如果发现没走索引先检查是不是有隐式类型转换、函数运算、最左匹配等失效问题。最终确定索引方案后在生产环境的低峰期做一次对照测试观察慢日志的变化。这套流程看起来简单但能拦住绝大多数索引相关的低级事故。7.2 一份可以直接抄的 SQL 变更检查单下面这份检查单我每次写 SQL 优化建议时都会过一遍你完全可以抄走用检查项验证方式通过标准WHERE 条件列是否有索引SHOW INDEX FROM table条件列在索引列表中有没有类型不匹配对比字段类型和参数类型类型一致无隐式转换联合索引是否满足最左匹配看 WHERE 条件顺序和 key_lenkey_len 包含实际使用列有没有函数包裹索引列检查 WHERE 条件写法无函数运算或已用函数索引LIKE 模糊查询是否正确看 LIKE 字符串开头尽量前缀匹配排序字段是否可走索引看 Extra 有无 Using filesort无 Using filesort 最好查询列能否覆盖索引看 Extra 有无 Using index有更好没有至少走 ref真实耗时是否稳定多次执行取中位数目标耗时范围内还有一个个人偏好涉及多条条件时我会尽量把查询改写成既能走覆盖索引、又能避免排序和临时表的形态。比如 SELECT 只返回必要字段不随便 SELECT *这会让覆盖索引的命中概率高很多。验证索引这件事说到底就是用标准方法回答一个简单问题MySQL 到底有没有用我建的索引。现在让我做任何关于索引的决策都会先跑一条 EXPLAIN再对照真实耗时过程已经完全形成了肌肉记忆。你可以直接拿去用也建议你亲手在本地复现一遍数据会告诉你真相。