一条慢SQL打爆数据库这种事情我在过去几年里见过太多次了。业务方凌晨打电话说系统卡死抓出慢查询日志一看一条语句全表扫描了几百万行跑了三秒多。而表上明明有索引为什么没用因为索引建得不对或者压根没遵守使用规则。索引是SQL优化的第一切入点但大多数人建索引是拍脑袋建——看哪个字段用得频繁就建哪个完全不看区分度、不关心联合索引的顺序、不验证执行计划。今天这篇就把索引的使用规则一次讲透怎么建、怎么用、怎么判断失效、怎么避开线上坑。不管你是被慢SQL折磨的开发还是刚入门想进阶的DBA看完至少能解决工作中八成以上的索引问题。1. 索引到底在解决什么问题先想清楚为什么要有索引1.1 没有索引的时候数据库在干嘛很多人对索引的理解停留在“能让查询变快”这个层面但到底快在哪说不清楚。先看没有索引时的状态全表扫描。假设有一张五百万行的订单表你要查某个用户的订单SQL写出来没问题但数据库没有索引可用时只能从第一行开始一行一行往下扫把每一行都读出来判断满不满足条件。这个过程在数据库里叫full table scan。全表扫描慢的根本原因是磁盘IO。数据库的最小IO单位是页也叫块InnoDB里默认一页16KB一页能放几十上百行数据。五百万行的表可能要读几万甚至十几万个页全扫一遍至少是几万次磁盘IO。而磁盘随机IO的一次耗时大概在几毫秒到十几毫秒哪怕顺序读快一些几万个页加起来的耗时也轻松超过一秒。说白了全表扫描就像你在一个有几千页的通讯录里找一个名字每一页都翻一遍。而索引干的事是给你一个按姓氏排序的目录直接翻到那一页。1.2 索引的本质B树和“书的目录”索引为什么能少读那么多页因为底层结构是B树。B树的叶子节点保存着有序的索引值和对应的主键值或行指针非叶子节点只存索引值和下一层指针一个非叶子节点能存放大量索引条目整棵树的高度通常只有三四层。查一个等值条件时从根节点出发每层做一次二分查找三层四次就能定位到目标叶子节点。也就是说一次索引等值查询只需要读三四个页跟全表扫描几万个页完全不是一个量级。B树相比其他树结构比如二叉搜索树的优势也很直接树矮、层数少、范围扫描方便。叶子节点之间用双向链表串起来所以查WHERE id BETWEEN 100 AND 200这类范围条件只需要定位到起点然后沿着链表顺序往后读非常高效。顺便说一句搜索引擎里讲的“倒排索引”和数据库的B树索引是两码事一个是按关键词找文档一个是按字段值找行别被概念搞混。1.3 索引的类型和各自适用场景不同数据库支持的索引类型不完全一样但关系型数据库里常见的就这几种索引类型底层结构适合场景不适合场景B树索引B树等值、范围、排序、最左前缀前置模糊匹配哈希索引哈希表等值查询范围查询、排序全文索引倒排表like %关键词%、全文搜索普通等值查询位图索引位图低基数列、复杂组合查询Oracle高并发写入函数索引上述结构的函数结果对列做了函数运算的查询无生产环境里绝大多数场景用的都是B树索引。哈希索引虽然等值查询极快但解决不了ORDER BY和范围查询所以MySQL的InnoDB引擎默认就不支持用户主动建哈希索引只在内存层面用自适应哈希索引做内部优化。Oracle的位图索引在数据仓库里处理低基数列组合查询很有效但OLTP环境千万别用并发更新会互相锁块容易直接卡死。全文索引和LIKE %xx%的场景最匹配可惜很多人用错了方向后面会详细讲。2. 索引设计的通用规则哪些字段值得建索引2.1 选择性别给“男/女”这种字段建索引索引能不能帮查询快速缩小范围取决于一个关键指标——区分度也叫选择性。选择性 某个字段去重后的值个数 / 表总行数。举个例子一张一百万的用户表gender字段只有两个值那选择性是2/1000000约等于0.000002极低。你在这个字段上建B树索引查询WHERE genderM时如果男女各占一半数据库用索引扫描也要扫五十万行跟全表扫描没啥区别优化器评估下来可能直接就放弃索引了。反过来user_id、order_id、phone这种字段每个值都是唯一的选择性等于1索引就能非常精准地定位。所以建索引前先想一个问题这个字段到底能不能帮我把扫描范围缩到很小性别、状态、是否删除这类低基数字段单独建索引基本是浪费空间。除非是那种“大量记录都满足条件、只查极小部分”的场景或者联合索引里放在靠前位置的等值过滤条件才要考虑。这里有一个先算再建的技巧-- 先看看区分度 SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;选择性超过0.1也就是达到10%的区分度的字段才值得优先考虑建索引。低于0.01的基本可以放弃除非配合查询场景有特殊理由。2.2 单列索引还是联合索引字段使用频率说了算一个常见的错误做法是查询里有三个字段就分别给三个字段各建一个单列索引。看起来每个字段都有索引但优化器一次查询只能用一个索引索引合并是特例后面说三个单列索引往往一个都用不好。比如订单表经常按buyer_id status两个条件一起查SELECT * FROM order_info WHERE buyer_id 10086 AND status PAID;如果分别建了(buyer_id)和(status)两个单列索引数据库要选择其中一个去查另一个条件只能等所有候选行取出来后再用回表去判断。假设buyer_id的索引能筛出100行status的索引能筛出50万行那用前者明显更好但另一个索引就白建了。更优的做法是建一个联合索引(buyer_id, status)B树先按buyer_id排序再按status排序一次索引查找就能同时过滤两个条件。MySQL 5.0之后确实有索引合并index merge机制可能同时用两个单列索引再用交集/并集听着很聪明但实际效果不稳定合并需要额外的排序和临时结构两个单列索引的维护成本也高两倍。作为一个经验丰富的实践者我更建议如果多个字段总是同时出现在查询条件里优先考虑联合索引而不是拆成多个单列索引。2.3 最左前缀原则的底层逻辑联合索引是很多人的知识盲区特别是“最左前缀原则”为什么是这个规则很多人只背结论不理解原理。联合索引(a, b, c)在B树里的排序方式是先按a排序a相同再按b排序b相同再按c排序。整个索引就像一个字典先按姓、再按名、再按中间字排序。因为这种排序规则只有从最左列开始连续使用索引列才能命中索引过滤条件命中情况为什么WHERE a 1命中用到了索引第一层排序WHERE a 1 AND b 2命中依次用到了a、bWHERE a 1 AND b 2 AND c 3命中全部用上WHERE b 2不命中跳过a无法利用排序WHERE a 1 AND c 3只命中aa筛完后c无法利用b的排序注意最后一行a命中了b没有出现在条件里所以c无法用索引继续过滤只能用索引回表后再逐行判断c。MySQL叫这种为“索引里只用到一部分”。实际上优化器还会做索引条件下推这里先不展开后面有专门章节。所以建联合索引的时候字段顺序绝不是随机的。等值条件尽量放在前面紧接着放范围或者排序字段所有的查询都要能套在最左前缀的规则里否则索引就在那里优化器就是不用。2.4 主键、唯一、普通索引如何取舍主键索引是普通二级索引之外的一个重要角色。在InnoDB里主键索引就是聚簇索引数据行本身存在主键B树的叶子节点上所以主键直接决定了表的物理存储顺序。主键的选型有一个比较核心的点尽量选择单调递增的值。自增主键、雪花算法、自增的业务单号都符合这个规律。每次插入新行数据页往末尾追加不会触发频繁的页分裂。如果你用随机UUID做主键每次插入都随机分布在索引中间位置B树要不断做节点分裂、数据重排写放大很严重对高并发插入是灾难。日常场景里还有两类特殊索引。唯一索引除了加速查询还承担约束职责业务上需要字段唯一比如phone、order_no就直接建唯一索引不加一句话做两次查询。但注意一个表不建议建太多唯一索引因为每次写入都要对每个唯一索引做唯一检查额外开销不小。普通索引就灵活多了什么时候建、建几个完全跟着查询走。还有一个细节二级索引的叶子节点在InnoDB里会带上主键值所以SELECT id FROM t WHERE namexx这种查询哪怕只建了(name)一个索引也能直接拿到id不需要回表。这就是后面要讲的覆盖索引的一种隐藏红利。3. 最容易出事的索引失效场景与原因排查3.1 索引失效场景速查表索引建了却不是所有情况都能用上。下面是线上最常遇到的失效场景失效场景典型写法规避方式对索引列使用函数DATE(created_at)2025-01-01应改为created_at 2025-01-01 AND created_at 2025-01-02隐式类型转换字符串列放一个数字条件如WHERE phone 13812345678LIKE前置通配符LIKE %关键词%用不了索引应写成LIKE 关键词%OR连接非索引列WHERE a1 OR b2如果b无索引整个条件放弃索引NOT IN / NOT BETWEEN不等于逻辑通常很难使用索引索引列参与运算WHERE price * 2 100会让索引失效排序方向不一致索引构建是正序存储某些数据库倒序扫描会放弃索引这里最阴险的是隐式类型转换很多慢SQL就死在这上面。表里phone字段是varchar直接写WHERE phone 13812345678MySQL会把字符串列隐式转成数字来比较等于对索引列做了CAST运算优化器只能放弃索引。这种问题完全不会有任何报错只有慢查询日志会把你打醒。关于SQL注入的安全问题顺带提醒一句所有SQL里的外部输入参数都必须走参数化查询或预处理语句绝不能用字符串拼SQL这不只是防注入也是在保护索引拼接不当特别容易产生隐式类型转换导致索引失效。3.2 现场复盘一个典型慢查询的索引反转讲一个实际案例。运营后台有个查询需求是看某天下单的用户数最初写的是SELECT COUNT(*) FROM order_info WHERE DATE(created_at) 2025-03-10;这张表有800万行created_at上建了索引。看起来万事大吉但query跑出来要两三秒。问题出在DATE(created_at)上。对索引列套函数后索引里的排序值已经不再是原值优化器没法用B树去定位只能全表扫一遍把每行的created_at都算一次DATE再比较。改成范围写法之后一切都不一样了SELECT COUNT(*) FROM order_info WHERE created_at 2025-03-10 00:00:00 AND created_at 2025-03-11 00:00:00;这条能命中created_at索引因为B树做的是区间定位效率呈指数级上升。改完执行时间从2000ms降到30ms这就是对索引列做函数处理的代价。3.3 用EXPLAIN验证别靠猜索引失效不失效嘴上说不算执行计划才是一锤定音的东西。MySQL里直接在SQL前面加EXPLAIN就能看EXPLAIN SELECT * FROM order_info WHERE buyer_id 10086;看几个关键列就够type从好到坏依次是system const eq_ref ref range index ALL。出现ALL就是全表扫描索引没起作用必须警惕。key实际用到的索引名为NULL就是没用索引。key_len用了索引多少字节越长说明索引覆盖的字段越多可以用来判断是否只用了联合索引的一部分。rows优化器预估要扫描的行数。一个大表里预估几十万行八成有问题。Extra出现Using filesort代表排序没走索引出现Using temporary代表用了临时表都是要优化的信号。执行计划看起来复杂但核心逻辑就一句话看预估扫描行数是不是远小于表总行数再看有没有额外的排序和临时表操作。把这条准则记住你就不容易在索引上翻车。4. 联合索引、覆盖索引与索引下推把索引用到极致4.1 联合索引字段顺序的优先级很多人以为联合索引字段顺序就是把查询条件里的字段从左到右排一遍这么做其实只对了一半。真正合理的顺序要考虑三个维度等值条件优先WHERE a 1 AND b 2里等值过滤条件放最左边是最高效的它能一步锁定范围。区分度大优先如果两个都是等值条件区分度大的字段放前面能更快缩小范围。排序字段要能和索引顺序吻合如果SQL里有ORDER BY把排序字段也放进联合索引的靠后位置就可能免掉filesort。举一个最典型的例子一个搜索订单的SQLSELECT * FROM order_info WHERE buyer_id 10086 ORDER BY created_at DESC LIMIT 20;此时建联合索引(buyer_id, created_at)就比(created_at, buyer_id)好为什么因为前者先定位到buyer_id对应的所有行数量不会太多created_at已经按时间排序好了直接反向扫叶子节点就能一路取前20条不再需要额外排序。后者先按created_at排完全没法利用buyer_id过滤还得全表扫描后再filter损失巨大。一个小技巧是如果多个等值条件优先把区分度更高的字段放前面会让每个entry点多对应更少的行。4.2 覆盖索引索引里已经把所有要查的列备齐了覆盖索引是个被低估的优化点。它指的是查询需要的所有列都包含在同一个二级索引里查询只用读索引页不再需要回表查数据行。InnoDB的二级索引叶子节点默认会存主键值所以只要SELECT的字段恰好是“索引列 主键”不用额外建索引也能覆盖。但如果查询还带了其他字段就必须回表一次。拿一个具体的例子说SELECT order_id, status, amount FROM order_info WHERE buyer_id 10086 AND status PAID;假设有索引(buyer_id, status, order_id, amount)那这个查询可以直接从索引里拿出所有字段Extra显示Using index一次回表都没有。省掉的IO没准能让查询快5到10倍。但覆盖索引不是胡乱把字段往里塞的。索引列的存储也要占空间SELECT里真实高频查询的字段加进去才有收益。如果查询动不动就SELECT *那覆盖索引基本白搭不如别扩那么多字段。4.3 索引下推MySQL 5.6之后的优化器红利索引下推Index Condition PushdownICP是MySQL 5.6引入的优化让联合索引里“没被最左前缀用到的列”也能在索引层帮忙过滤减少回表次数。举例说明表有联合索引(a, b)查询是SELECT * FROM t WHERE a 1 AND b LIKE x%;最左前缀只用到a列b列看起来用不上。5.6之前数据库会把所有a1的行回表取出后再逐行判断b是否匹配。5.6以后只要b的条件是可以在索引层判断的表达式比如LIKE右前缀引擎会在读索引时就过滤掉不满足b的行只对极少部分继续回表。这个优化不需要DBA做任何配置自动生效但它提醒我们一件事联合索引就算不能完全命中最左前缀也是有用的。所以设计索引时把模糊匹配、范围条件尽量放在联合索引的后半段配合ICP收益更大。5. 主流数据库索引实操差异MySQL、Oracle、SQL Server、PostgreSQL5.1 MySQL InnoDB聚簇索引与二级索引回表MySQL最常用的InnoDB引擎里聚簇索引就是主键索引或者第一个非空唯一索引再不行就用隐藏主键数据行存在主键B树的叶子节点上。所以InnoDB表一定要明确指定主键且最好是单调递增的。如果你偷懒不指定主键InnoDB会自己生成一个6字节的隐藏主键你做不了任何控制后续所有二级索引都回表指向这个隐藏列运维时很容易一头雾水。另外MySQL 8.0增加了两个很实用的功能降序索引和不可见索引。降序索引可以让ORDER BY col DESC不再额外触发filesort不可见索引让你先给优化器“藏起来”测试效果ALTER TABLE t ALTER INDEX idx INVISIBLE确认没影响再真正下线用来测试删除索引是个很稳的流程。8.0.13还支持函数索引等于变相允许你对列做函数运算还不用担心索引失效。其实Oracle的B-tree索引结构大体一样不同之处在于Oracle支持反向键索引主要用在RAC并发场景减少数据块竞争也支持位图索引和基于函数的索引。Oracle还有一个老坑全NULL值的行不会进入普通B-tree索引所以WHERE col IS NULL永远走不了索引这是结构决定的。5.2 SQL Server与PostgreSQL的细节差异SQL Server里聚集索引和非聚集索引是分开定义的如果不指定聚集索引表就是堆表数据行没有固定顺序。它还有INCLUDE语法可以把不需要参与排序、只想拿来避免回表的列附加到非聚集索引页里跟MySQL的覆盖索引思路类似但语法更清晰CREATE INDEX idx_buyer_time ON order_info (buyer_id) INCLUDE (order_id, status);SQL Server 2022进一步增强了在线索引重建能力很多场景下重建索引不用长时间锁表。如果你的线上库是SQL Server碰到索引碎片过多优先考虑ONLINE重建而不是传统REBUILD。PostgreSQL的B-tree索引和MySQL类似但它有一个bitmap scan机制当单个索引扫描到的行数较多比如扫出百分之几到百分之十几的行时会先根据索引收集一批数据页面地址再按页号顺序批量回表把随机IO转成相对顺序的IO。所以PostgreSQL里偶尔“扫了半张表但用了索引”并不一定慢执行计划的表现和MySQL不完全一致分析时要注意。5.3 一张表到底建多少个索引合适索引不是越多越好这是新手最容易忽略的成本意识。每个索引都占磁盘空间每次INSERT、UPDATE、DELETE都要同步维护索引树。一个表五六个索引每次写入就要多写五六棵B树写放大特别严重。高并发的OLTP系统里索引多了甚至会让整体吞吐量掉一半。经验值一张OLTP表索引数量控制在5个以内最多别超过8个。数据仓库或者明细表可以适当放宽。更重要的是定期看慢查询日志把从来不用的索引干掉。MySQL 8.0有performance_schema.table_io_waits_summary_by_index_usage可以查索引使用次数长期零使用的索引该删就删。Oracle和SQL Server也都有等价的索引使用统计视图。6. 实操案例把一条3秒的慢SQL优化到毫秒级6.1 现场问题与原始SQL有一张订单表order_info500万行。业务方反馈一个订单列表页加载特别慢接口超时。抓出慢SQL是这条SELECT order_id, status, amount, created_at FROM order_info WHERE buyer_id 128391 AND status IN (PAID, SHIPPED) ORDER BY created_at DESC LIMIT 20;这条语句在测试环境跑2.8秒线上更糟。表上当时只有主键索引没有其他二级索引。6.2 用EXPLAIN定位问题加EXPLAIN看执行计划type是ALL全表扫描rows显示500万就是要把整张表全扫一遍Extra有Using filesort说明排序也没走索引两个信号一出来问题已经很明确查询条件里的buyer_id、status、created_at三个字段一个索引都没有数据库只能把全表翻出来再临时排序取前20条。性能差完全在意料之中。6.3 索引设计与验证按查询里出现的位置联合索引首选的字段组合是(buyer_id, status, created_at)。这个设计理由如下buyer_id是等值条件放最左直接锁定买家的订单范围status是IN条件等于也是一个可缩小的集合放第二列created_at不参与过滤但查询要求按它倒序放第三列正好利用B树的有序性避免filesort另外一个考虑是SELECT字段里有order_id、amount二级索引叶子节点自带主键order_id假设主键是order_idamount则不在索引里所以最多只要回表一次。为了省这次回表也可以把amount加进索引做覆盖索引CREATE INDEX idx_buyer_status_time ON order_info (buyer_id, status, created_at, amount);我先用基础版验证效果再考虑覆盖版毕竟amount是可变长度字段加进索引会让索引变大写入成本也会增加。实测发现基础版已经够用因为LIMIT 20最多回表20行根本不算瓶颈。索引不是越宽越好够用就行。执行计划再跑一次type变成了rangeIN条件走范围扫描key是idx_buyer_status_timerows从500万降到了个位数Extra里没有filesort生产环境上这条SQL从2.8秒降到了约20毫秒。慢SQL日志里这条记录彻底消失。6.4 案例复盘这个案例真正的结论是优化SQL的第一步永远是减少扫描行数而不是想着加缓存、上复杂架构。索引把500万行的全表扫描变成了几行定位比什么并行SQL优化、加内存缓存都来得直接且便宜。“并行SQL优化”也是热搜词之一但这里有个先后顺序问题——索引优化还没做就别指望并行救你。索引到位之后如果数据量巨大再考虑并行处理那是另一层功夫。7. 我踩过的坑和最后的几句实在话7.1 几个真实踩坑记录第一坑生产环境直接CREATE INDEX。在业务高峰期语句一执行表被锁住或长时间阻塞线上功能直接卡死几分钟。后来学乖了大表的索引变更全部走在线DDL工具MySQL用pt-online-schema-change或者GH-OST先把加索引操作限速执行减少对主库的影响再手动切换。第二坑听“大神”说覆盖索引好用把所有查询里出现过的字段全塞进一个索引结果索引膨胀到原来的两三倍大写入性能暴跌最后不得不拆掉一半。索引宽度有限制一个索引总长度也别超过一页能容纳的范围否则B树出现大量溢出页性能反而下降。第三坑低估了字段顺序对排序的影响。曾经有个查询WHERE a 1 ORDER BY b DESC我建了(b, a)索引执行计划照样Using filesort。很多人会在这个地方反复踩坑。正确做法是先等值字段a再排序字段b方向才能匹配。7.2 实用的索引设计检查清单检查项具体做法选择性判断先COUNT(DISTINCT col)/COUNT(*)选择性太低不建联合索引顺序等值字段优先区分度大优先排序字段放后面杜绝函数/隐式转换不对索引列用函数字符串列就用引号验证执行计划每条慢SQL都过EXPLAIN盯type、rows、Extra控制索引数量单表少于6个定期清理零使用索引大表在线操作索引变更用在线工具避开业务高峰注意版本差异MySQL 8.0的降序/不可见索引SQL Server 2022的ONLINE重建PG的bitmap scan索引这东西说到底是给数据库的查路图。建的时候多问自己一句这个查询真的需要这条索引吗它的字段顺序合理吗验证过执行计划吗多用执行计划说话少拍脑袋。把我上面这套规则用熟慢SQL能少一大半DBA的夺命电话也能少很多。