同事在群里扔过来一句这个查询我明明加了索引怎么还是跑了三秒然后甩了一张 EXPLAIN 的截图。这种场景我见得太多绝大多数时候不是索引没建而是建了一个数据库根本用不上的索引。SQL 建立索引这件事说简单也简单一句 CREATE INDEX 就完事说难也难难在你得先想明白数据库拿到这条 SQL 之后会怎么选路、怎么走这条索引、走完之后还要不要回表。我准备把自己这些年建索引、拆索引、灭掉冗余索引的过程整理一遍覆盖从原理到 EXPLAIN 逐字段读法再到那些让索引白建的写法。内容偏实战涉及 MySQL、SQL Server、Oracle、PostgreSQL 的通用思路示例以 MySQL 为主。刚接触数据库的同学可以照着跑一遍写过几年 SQL 的人也可以拿它当一份索引排查清单。1. 索引到底替数据库省掉了哪一步1.1 一次没有索引的查询磁盘上发生了什么先把最朴素的情况摆出来。假设有一张用户表十万行你执行一句SELECT * FROM users WHERE phone 13800000000;如果 phone 上没有任何索引InnoDB 会从这张表的第一个数据页开始把整张聚簇索引的叶子节点顺序读一遍。数据页默认 16KB十万行记录按每行两百字节算大约是 2GB 的数据要读上千个页。即便有 Buffer Pool 缓存第一次执行也基本是实打实的磁盘 I/O。这就是所谓的全表扫描EXPLAIN 里体现为 typeALL。有人会说十万行不算大扫过去也就几百毫秒。问题在于真实业务里的表很少只有十万行而且这条 SQL 可能每秒被打几千次。单次几百毫秒乘以 QPSCPU 和磁盘马上就顶不住了。所以索引要解决的核心问题只有一个把逐行比对变成按位置直接取。1.2 B 树为什么能撑起这个直接取InnoDB 的索引底层是B 树和教科书里讲的略有差别但关键特征一致非叶子节点只存键值和子节点指针所有真实数据或主键都在叶子层叶子之间用双向链表串起来。这个结构带来两个直接好处。第一树的层数很少。InnoDB 的一个数据页 16KB非叶子节点里一个键值加指针大概十几字节一页能放上千个条目。三层 B 树就能撑住千万级甚至上亿行的记录。也就是说从根到叶最多三次页读取第一次读根页后面两层大概率已经缓存在内存里真正落到磁盘的往往只有一次。第二叶子节点的链表让范围查询变得顺滑。WHERE created_at BETWEEN 2024-01-01 AND 2024-01-31这种条件定位到起始键之后顺着链表往后读就行不需要回到上层再重新找。这里有个容易忽略的点B 树的高度决定了索引的访问代价。当表从百万级涨到亿级时树的层数可能从三层变成四层多出来的那一次 I/O 在压测里是能看出来的。这不是危言耸听而是你在设计大表索引时应该心里有数的东西。提示不要指望靠多建几个索引解决所有性能问题。索引能加速的是定位行这件事如果查询本身就要求扫描大量数据比如统计全表求和索引帮不上忙这时候该考虑的是汇总表或缓存而不是继续加索引。1.3 聚簇索引与二级索引回表这件事是怎么发生的这是整个索引体系里最容易被略过、又最影响性能的一层。InnoDB 是索引组织表主键索引的叶子节点直接存放整行数据这张索引叫聚簇索引。你建的其他任何索引包括唯一索引和普通索引都是二级索引叶子节点里存的是索引列的值 主键值。这就解释了回表。执行SELECT id, name, phone FROM users WHERE phone 13800000000;如果只在 phone 上建了二级索引数据库会先在这棵树上找到13800000000对应的主键值比如 id9527然后再拿着 9527 回到聚簇索引里把那行完整记录捞出来从中取出 name 和 phone。这一次回到主键索引再查一次的动作就是回表。回表的代价是什么如果命中的行很多而且这些行在主键索引上分布得很散那么每回表一次都可能是随机 I/O。一百次回表就是一百次随机读这个成本远高于走一次索引。所以很多优化到最后都会落到同一个方向想办法别回表也就是后面要说的覆盖索引。理解了这一层再看 SQL Server 里的聚集索引Clustered Index和非聚集索引、Oracle 里的堆表 ROWID逻辑其实是相通的——非主结构索引最终都要靠某种行定位符回到真实数据上。区别只在于这个定位符是主键、是 ROWID还是一个物理位置指针。2. 从慢查询日志出发而不是从直觉出发2.1 先让数据库把慢 SQL 自己交出来建索引最怕的事情是凭直觉猜。我看到过太多人上来就说给 created_at 加个索引吧加完之后查询没快多少写还慢了一点。正确的入口应该是让数据库告诉你哪些 SQL 真的慢。MySQL 的慢查询日志是标准做法-- 查看当前配置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 动态开启重启失效持久化要写进配置文件 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time设成 1 秒是个常见的起点但别一直留着。业务高峰期这条线上可能一堆正常的批量任务把日志刷得没法看。我的习惯是先设 1 秒跑一天看看整体的慢 SQL 分布然后针对性地收紧到 0.2 秒专盯高频的短慢查询——那种单次 200ms、每秒几百次的 SQL比偶尔一次的 3 秒查询更值得优化。SQL Server 对应的手段是查询存储Query Store加动态管理视图SELECT TOP 20 qs.total_elapsed_time / qs.execution_count AS avg_elapsed, qs.execution_count, SUBSTRING(st.text, 1, 200) AS sql_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY avg_elapsed DESC;PostgreSQL 则是pg_stat_statements扩展按总耗时排序看。这一步的意义在于先把目标锁定。你手里有一份按总耗时排名的 SQL 清单优化就不会跑偏。2.2 EXPLAIN 里那几个必须盯死的字段拿到慢 SQL 之后第一件事是 EXPLAIN。很多人只看有没有用到索引这远远不够。下面这几个字段我每次都会逐个看字段该看什么危险信号type访问类型ALL、index 需要警惕理想是 const、eq_ref、ref、rangekey实际使用的索引为 NULL 说明没走索引key_len用到的索引前缀长度比预期短说明复合索引只用了前面几列rows预估扫描行数远大于实际返回行数说明索引选择性差filtered过滤后剩余比例很低说明大量行被读了又丢掉Extra附加动作Using filesort、Using temporary、Using where 配合 typeALLkey_len这个字段特别值得展开说因为它是判断复合索引是否被完整利用的最直接证据。以 utf8mb4 的varchar(50)为例一个可空列在索引里的长度是50 × 4 1NULL 标志 2变长长度 203字节。如果你建了(a, b, c)三列复合索引而 key_len 只显示 203那就意味着只用了 a 这一列后面的 b 和 c 白搭。这个数字能帮你快速确认最左前缀到底用到了第几列。2.3 选择性算一算别给低区分度列建索引索引是不是有用本质取决于选择性。计算方式很简单选择性 该列不重复值的数量 / 表的总行数这个值越接近 1 越好。查一下SELECT COUNT(DISTINCT status) / COUNT(*) AS sel_status, COUNT(DISTINCT user_id) / COUNT(*) AS sel_user_id FROM orders;如果 status 只有待付款、已付款、已发货、已完成、已取消五个值选择性就是 0.0000x 级别。给这种列单独建索引优化器很可能压根不用它——因为走索引定位到 20 万行再回表 20 万次还不如直接全表扫一遍顺序读。经验上的分界线大概是选择性低于 0.01 的单列索引基本没有独立存在的价值。但注意这不等于它不能出现在复合索引里。如果WHERE status pending AND user_id 123这种查询频繁出现把 status 放在复合索引的第二位、user_id 放第一位反而是合理的。3. 复合索引的列序决定它能不能被用上3.1 最左前缀原则用一句话讲清楚复合索引(a, b, c)的排序规则是先按 a 排a 相同的按 b 排b 相同的按 c 排。这就像电话号码簿先按姓排、同姓按名排一样。你可以用 a 单独查可以用 ab可以用 abc但不能跳着用。直接用 b 查或者用 bc 查这棵树帮不上忙。具体到 SQL 上以下几种写法假设索引是(a, b, c)WHERE 条件是否用到索引用到哪几列a 1是aa 1 AND b 2是a, ba 1 AND b 2 AND c 3是a, b, cb 2否无a 1 AND c 3部分ac 用不上但 8.0 有索引下推会做过滤a 1 AND b 2部分a范围之后的 b 无法用于定位最后一行是最容易被忽视的陷阱。范围查询之后的列会失去定位能力。所以复合索引的列序有一条几乎通用的原则等值条件在前范围条件在后排序列紧跟等值列。3.2 一个真实例子列序错一位性能差三十倍我做过一个订单查询原始 SQL 是SELECT order_no, amount, created_at FROM orders WHERE status 1 AND created_at 2024-06-01 AND created_at 2024-07-01 ORDER BY created_at DESC LIMIT 20;最初的索引是(created_at, status)。按理说 created_at 选择性极高放第一位没问题。但实测这条 SQL 每秒执行两百多次时平均耗时 180msP99 超过 800ms。原因在于索引先按 created_at 排好定位到 6 月 1 日之后然后逐行判断 status 是不是 1。6 月一个月可能有上百万行其中 status1 的只占一小部分剩下的大量行被读了扔掉还要回表判断。EXPLAIN 的 rows 显示预估扫描 40 万行。改成(status, created_at)之后ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);数据库先在 status1 这个等值区间里定位然后在区间内顺着 created_at 的顺序读读到 20 行就停。而且因为 ORDER BY 的列正好是索引的第二列排序这一步完全省掉Extra 里没有 Using filesort。平均耗时掉到 6ms。列序调换一位三十倍的差距。这个案例想说明的是等值列放前面是为了把范围收窄范围列放后面是为了让排序顺势而为。如果你的查询里没有等值条件那范围列当然还是放第一位。3.3 覆盖索引把回表这一步直接砍掉前面说过回表是二级索引的主要成本来源。如果查询需要的所有列都能从索引里拿到数据库就不需要回表了这就是覆盖索引。还是那条 SQL如果改成ALTER TABLE orders ADD INDEX idx_cover (status, created_at, order_no, amount);那么SELECT order_no, amount, created_at FROM orders WHERE status1 AND created_at ... ORDER BY created_at DESC LIMIT 20就可以完全在这棵二级索引上完成。EXPLAIN 的 Extra 会显示Using index这就是覆盖索引的标志。代价是索引变宽了。order_no 和 amount 两列被复制到索引里索引占用的磁盘空间增大写入时也要多维护两个字段。所以覆盖索引不是无脑加列得看这条 SQL 的调用频次。我的判断标准是单条 SQL 占总查询量的 5% 以上或者本身就是核心链路才值得为它单独设计一个覆盖索引。3.4 长字段和表达式查询的处理方式手机号、身份证号、URL 这类长字符串直接建索引索引体积会很夸张。这时候可以用前缀索引ALTER TABLE users ADD INDEX idx_phone_prefix (phone(8));它只索引前 8 个字符。前缀长度的选取有个简单的验证方法逐步增加前缀长度直到选择性接近完整列的选择性为止。SELECT COUNT(DISTINCT LEFT(phone, 6)) / COUNT(*) AS p6, COUNT(DISTINCT LEFT(phone, 8)) / COUNT(*) AS p8, COUNT(DISTINCT phone) / COUNT(*) AS full FROM users;如果 p8 已经和 full 很接近那 8 就够用。前缀索引有个明确的限制它无法用于覆盖索引因为索引里存的不是完整值数据库没法确定完整内容。另外它对 ORDER BY 和 GROUP BY 的支持也很有限排序结果可能是前缀序而不是完整序。表达式查询是另一个场景。WHERE DATE(created_at) 2024-06-01这种写法会让 created_at 上的索引用不上因为索引存的是完整时间戳不是日期。两种处理方式改成范围写法created_at 2024-06-01 AND created_at 2024-06-02或者在 MySQL 8.0 及以上建函数索引ALTER TABLE orders ADD INDEX idx_date ((DATE(created_at)));我更推荐前者因为它对数据库版本没有要求改写成本也低而且范围写法能同时利用索引的排序能力。4. 索引建了却不生效问题通常在这几处4.1 隐式类型转换最常见的隐形杀手这是我在生产环境里遇到次数最多的索引失效原因。典型场景phone列定义是varchar(20)但应用传参时传进来一个数字-- 索引失效 SELECT * FROM users WHERE phone 13800000000; -- 索引正常 SELECT * FROM users WHERE phone 13800000000;MySQL 在做比较时会遵循一套隐式转换规则字符串列和数字比较时会把字符串转成数字。这个转换发生在列上索引自然就用不了了。EXPLAIN 里 key 是 NULLtype 是 ALL。排查方法很直接看一眼表结构里这列是什么类型再看一眼 SQL 里传的是什么类型。日志里如果打印的是不带引号的参数基本就能确定问题。注意这个坑在 ORM 框架里特别隐蔽。有些框架会根据字段类型自动决定是否加引号如果你的实体类里把 phone 定义成了 Long框架就会按数字传参SQL 层面看毫无问题索引却默默失效了。4.2 在索引列上做运算和函数这条规则可以概括成一句话索引列必须干净地出现在比较符的一侧。-- 索引失效 SELECT * FROM orders WHERE YEAR(created_at) 2024; SELECT * FROM orders WHERE amount * 2 1000; SELECT * FROM orders WHERE SUBSTRING(order_no, 1, 4) 2024; -- 索引可用 SELECT * FROM orders WHERE created_at 2024-01-01 AND created_at 2025-01-01; SELECT * FROM orders WHERE amount 500; SELECT * FROM orders WHERE order_no LIKE 2024%;原因还是那棵树索引是按 created_at 的原始值排序的不是按年份排序的。你在列上套了函数数据库就无法用树的排序结构去定位只能把每一行算一遍。前置模糊匹配LIKE %abc属于同一类问题。它不知道要从前缀的哪个位置开始匹配只能全扫。如果实在需要这种查询得考虑全文索引或者额外的倒排结构单靠普通 B 树索引解决不了。4.3 OR、NOT IN 与不等条件OR的两侧如果有一侧没索引整个查询就会退化成全表扫描。-- 如果 b 没有索引a 上的索引用不上 SELECT * FROM t WHERE a 1 OR b 2;MySQL 5.7 之后有 index merge 优化两侧都有索引时可能会合并两个索引的结果但这种方式并不总是比全表扫快尤其是两侧命中的行数都很多的时候。更稳的做法是改写成 UNIONSELECT * FROM t WHERE a 1 UNION ALL SELECT * FROM t WHERE b 2 AND a 1;!、NOT IN、NOT EXISTS这类否定条件也有类似的麻烦。优化器很难判断不等于覆盖了多少行如果比例很高比如不等于某个罕见值走索引反而更慢它会直接选全表扫描。这类查询如果确实高频通常得从数据模型上想办法而不是硬在索引上找解法。4.4 统计信息过期导致执行计划跑偏有时候索引完全没问题SQL 也写得很规范但执行计划就是不选它。这时候要怀疑统计信息。优化器是靠统计信息估算行数的。如果表在短时间内插入或删除了大量数据统计信息还停留在旧的状态优化器就会基于错误的行数估算选错路径。MySQL 里可以手动刷新ANALYZE TABLE orders;SQL Server 里是UPDATE STATISTICSOracle 里可以按表或按 schema 收集。排查这类问题有个好用的手段看看 EXPLAIN 里的 rows 预估和实际返回行数差多少。差一个数量级以上基本可以怀疑统计信息。另外可以对比一下EXPLAIN和EXPLAIN ANALYZEPostgreSQL或EXPLAIN FORMATJSON加EXPLAIN ANALYZEMySQL 8.0.18的结果前者是预估后者会真实执行并给出实际行数。有个坑要提前说EXPLAIN ANALYZE会真的执行这条 SQL包括 INSERT 和 UPDATE。别在生产环境随手对一个写语句跑它。5. 索引的代价写入变慢、空间占用和冗余清理5.1 每个索引都在给写入加税索引不是免费的。每次 INSERT数据库要往聚簇索引写一份还要往每一个二级索引各写一份。UPDATE 更麻烦如果更新的是索引列还要先删后插。一张表上挂五六个索引单条 INSERT 的耗时可能是只有一个索引时的三四倍。这在批量导入场景下非常明显一个十万行的批量插入索引多的时候可能从 20 秒变成一分多钟。所以有一个判断原则索引数量应该由最核心的读查询决定而不是把所有被 WHERE 过的列都加上索引。我一般会先看这张表的读写比读远大于写比如 100:1的表可以适当多建写密集的表比如日志表、埋点表则要克制通常只保留两三个必要的索引。5.2 冗余索引和重复索引怎么找出来项目跑一段时间之后索引往往会失控A 建了一个(user_id)B 建了一个(user_id, created_at)两个索引的第一个列一样前者其实完全可以删掉因为它能被后者覆盖。还有更极端的同一个人在不同时间建了两个完全一样的索引名字不同而已。MySQL 里可以用系统视图扫一遍SELECT * FROM sys.schema_redundant_indexes; SELECT * FROM sys.schema_unused_indexes;schema_redundant_indexes会指出哪些索引是另一个索引的前缀schema_unused_indexes基于performance_schema统计哪些索引从来没被用过。这里要提醒一句schema_unused_indexes不能盲信。它只统计了服务器启动以来的使用情况如果某个索引只在月底结算任务里用一次而你的实例上周刚重启过它就会显示未使用。所以删除索引之前至少要确认两件事统计周期覆盖了完整的业务周期以及这个索引不是为某个低频但重要的报表任务准备的。删除索引的语句要写得能回滚-- 先记录原定义 SHOW CREATE TABLE orders; -- 再删除 ALTER TABLE orders DROP INDEX idx_status_created;5.3 碎片与重建B 树在频繁的插入删除之后会产生页分裂导致索引碎片。表现是索引占用的空间比理论值大很多扫描时的 I/O 也变多。MySQL 里查看碎片SELECT table_name, index_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb FROM information_schema.tables WHERE table_schema your_db AND table_name orders;重建方式在 InnoDB 里其实只有一种推荐做法ALTER TABLE orders ENGINE InnoDB;这个语句会重建表并重建所有索引。它对大表来说是重量级操作需要额外磁盘空间还会持有表锁MySQL 5.6 之后支持 Online DDL但仍要评估。执行前一定要在从库或者测试环境先跑一遍估算耗时。SQL Server 里对应的操作是ALTER INDEX ... REBUILD或REORGANIZE选择逻辑上更清晰碎片率 5% 到 30% 之间做 REORGANIZE30% 以上做 REBUILD。这个分界值可以直接照用。6. 三个改索引的实际场景从现象到结论6.1 深分页为什么会越翻越慢分页查询的经典问题SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;这条 SQL 会先取出前 1000020 行然后丢掉前 1000000 行。即使走了主键索引这个读取量也相当可观。优化方向有两个。第一个是延迟关联先在索引上把主键捞出来再回表取数据SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 1000000, 20 ) t ON o.id t.id;子查询里的SELECT id是覆盖索引扫描不需要回表代价小很多只有最后 20 行才真正回表。第二个是游标分页把上一页最后一条记录的 id 传进来SELECT * FROM orders WHERE id 1000000 ORDER BY id LIMIT 20;这种方式在接口设计上需要改成分页参数是last_id而不是page_no。它每页耗时恒定是三种方案里最快的。代价是不能跳页适合下拉加载更多这种交互。6.2 联合查询里的驱动表选择两张表关联时优化器会选一张作为驱动表另一张作为被驱动表。被驱动表上的关联列必须有索引否则每一行驱动数据都要去被驱动表做一次全表扫描。SELECT u.name, o.order_no FROM users u INNER JOIN orders o ON u.id o.user_id WHERE u.city hangzhou;如果 users 上 city 有索引、orders 上 user_id 有索引优化器可能选 users 做驱动表用 city 索引筛出一批用户然后逐个用 orders 的 user_id 索引去查订单。这种执行方式在 EXPLAIN 里表现为驱动表 typeref、被驱动表 typeref。如果发现被驱动表的 type 是 ALL说明关联列缺索引这是要立刻补的。判断哪张表在被驱动看 EXPLAIN 输出的顺序和 key 字段通常第一行是驱动表。需要注意的是MySQL 8.0 之前会为每个关联查询在内存里建临时表Using join buffer小于某个阈值就纯内存操作。所以小表驱动大表是优化器的一般倾向但这不是铁律实际以 EXPLAIN 为准。6.3 一个线上问题的完整排查链路最后分享一次完整的排查过程我觉得比结论更有价值。现象是订单列表接口在下午三点到五点之间变慢P99 从 200ms 涨到 1.2s。数据库监控显示 CPU 使用率从 30% 涨到 65%慢查询日志里出现了那条订单列表 SQL。第一步拿到 SQL跑 EXPLAIN。typerangekeyidx_status_createdrows 预估 3000。看起来正常但实际返回只有 20 行。这个差距说明索引选择性在某个时间段内急剧下降。第二步查数据分布。发现下午这个时段 status1 的订单占总量的比例从平常的 5% 涨到了 40%。原因是订单审核任务集中在下午跑大量订单堆积在 status1 这个状态。选择性下降走索引的成本就上来了。第三步验证假设。把 status 换成另一个值同一时间段执行很快。确认问题就出在数据分布倾斜上。第四步解决方案。最简单的做法是加一个覆盖索引让这条 SQL 不需要回表。但更根本的问题是 status1 这个状态下的数据太集中。最终做法是调整业务逻辑让审核任务分批执行同时给索引补上需要的列扩大覆盖范围。这个案例想说的是执行计划正常不等于没问题要看预估行数和实际行数的差距。数据分布是会变的索引的效果也会跟着变。定期回看核心 SQL 的 EXPLAIN 结果比出问题后再救火要划算得多。6.4 上线新索引前该做的两件事最后说两个工程习惯。第一新索引一定要在接近生产数据量的环境里验证。测试库几千行的表加什么索引都快看不出问题。用真实数据的子集比如抽取一千万行跑一遍观察执行计划是不是真的走新索引以及写入性能下降了多少。第二上线要能快速回滚。新增索引本身在 MySQL 5.6 之后支持 Online DDL对读的影响很小但依然会占用额外的磁盘和 I/O。我的做法是先记下表的SHOW CREATE TABLE输出索引命名统一加日期前缀或业务前缀比如idx_order_status_created_202406出问题时能一眼认出来是最近加的。索引这东西加的时候很随意删的时候才需要勇气命名规范能省掉很多犹豫。按我的经验一套索引体系其实是要跟着数据一起成长的。上线时觉得完美的索引跑了半年可能就变成了负担因为查询模式变了数据分布也变了。与其一次设计到位不如养成定期看慢查询、定期扫冗余索引的习惯让索引跟着业务一起调整。