搞了好几年数据库，我发现一个很有意思的现象：一说“SQL索引”，很多开发的第一反应是“建了索引查询就快”，等线上慢查询打过来，查执行计划才发现索引根本没被用上。索引这件事，难的不是那条CREATE INDEX语句，而是搞清楚它的使用规则。这篇就把我这些年踩坑攒下的索引经验完整理一遍，包括底层原理、最左前缀、失效场景、执行计划分析和不同数据库的差异，适合对索引有基础认识但总在优化时翻车的同学。
网上讲索引入门的文章特别多，真正能落地的少。多数文章只会告诉你“在最频繁查询的列上加索引”，可同样的查询换一个写法，索引就失效了；同样是B+树索引，在MySQL、SQL Server、PostgreSQL里的行为又有微妙差别。这篇文章会把规则背后的“为什么”讲透，再带你看一次慢查询优化的完整过程。看完之后，你至少能自己判断一条SQL该不该走索引、走了之后到底快不快。
1. 索引到底是什么：动手建之前先建立正确认知
1.1 B+树索引的核心机制，以及主键索引和二级索引
我见过不少开发把索引想得很玄，其实可以拿新华字典来类比。你要查“绩”这个字，如果整本字典按拼音排序，你会先翻到声母j的区域，再找ji，最后定位到“绩”——这就是主键索引的查找方式。如果你只想通过“纟”偏旁找字，那得翻到字典末尾的部首检字表，先找到“纟”部，再根据剩余笔画数找到目标字，然后它告诉你正文在第几页——这就是二级索引加回表的过程。
关系型数据库里最常用的索引结构是B+树，它的特点是所有数据都挂在叶子节点上，并且叶子节点之间用指针串联。MySQL的InnoDB存储引擎里，表数据本身就是按主键组织的B+树，这叫聚簇索引；你在其他列上建的索引叫二级索引，二级索引的叶子节点存的是主键值，而不是整行数据。所以当你用SELECT *通过二级索引查询时，引擎先到二级索引里找到主键，再拿着主键回聚簇索引取整行，这个动作叫回表。
搞清楚回表之后，很多调优思路就自然出来了。比如“覆盖索引”就是让查询需要的所有列都包含在二级索引里，这样引擎不需要回表，直接扫索引叶子节点就够了。一个很典型的优化是把SELECT id, name FROM user WHERE city = '杭州'中的name加进city索引，让索引覆盖查询列。很多DBA把覆盖索引当成“免费的性能提升”，因为它节省的是最贵的随机I/O操作。
B+树索引天生适合范围查询和排序，因为叶子节点有序且相连。相比哈希索引只能做等值匹配，B+树的优势非常明显。哈希索引虽然查找单值是O(1)，但对>、<、BETWEEN这类范围条件完全无能为力，所以InnoDB的默认索引结构是B+树而非哈希。这也解释了为什么WHERE name = '张三'用哈希索引快，但WHERE age > 25必须靠B+树。
1.2 索引不是越多越好：评估一张表该有哪些索引
很多新人刚学会建索引时容易走另一个极端，恨不得每列都建一个索引。我见过一张只有十几个字段的表，愣是建了二十多个索引。性能没提上去，写入先慢了，磁盘空间也涨了好几倍。这事得说清楚：索引会加速查询，但代价是每次INSERT、UPDATE、DELETE都要同步维护索引树，索引越多，写放大越严重。
有位前辈给我说过一句话，我一直记到现在：“索引不是免费的午餐，是你用写性能和存储空间换读性能。”所以建索引前先问自己几个问题：这个查询是不是高频？数据量是不是大到全表扫描已经不可接受？查询条件里涉及到的列，区分度高不高？
我一般建议把一张表的索引数量控制在5个以内，复合索引优先，能合并的单列索引就合并。真正需要建索引的场景无非三类：高频等值查询的列、高频排序或分组的列、外键关联列。反之，几乎不会出现在WHERE里的列、区分度极低的列（比如性别）、频繁更新的列，都不适合建索引。区分度低的列建了索引反而浪费，比如性别只有“男”“女”两个值，索引树分叉极少，扫描一半数据跟全表扫描差不多，优化器大多数时候会直接放弃这个索引。
2. 建索引的第一条铁律：复合索引的最左前缀规则
2.1 最左前缀规则在实际查询中的三种表现
复合索引是索引优化的核心，也是最容易踩坑的地方。很多人建了复合索引就以为万事大吉，实际查询一跑，索引压根没生效。原因基本都在最左前缀规则上：复合索引的生效顺序是从左往右，查询条件必须包含最左边的列，或者连续命中前缀列，索引才会被使用。
举个具体例子。假设有一张用户表，我建了复合索引idx_city_age，顺序是(city, age)。那么下面这些查询的索引使用情况分别是：
WHERE city = '杭州' AND age > 25：命中city，又命中age，索引充分利用。WHERE city = '杭州'：命中最左列city，索引可用，但只用了一半。WHERE age > 25：没带city，索引完全失效，走全表扫描。
这里的关键在于“最左前缀”不是指SQL里条件的书写顺序，而是指查询条件是否从头开始覆盖了索引列。MySQL的优化器会自动调整WHERE age > 25 AND city = '杭州'这种条件顺序，所以书写顺序不影响，真正影响的是你有没有从最左列开始匹配。
最左前缀在实际应用中有三种典型表现：第一，前缀列越多，索引能过滤掉的数据越多；第二，范围条件（>、<、BETWEEN）会让其后的索引列失效，比如WHERE city = '杭州' AND age > 25 AND name LIKE '张%'，name这一列索引就用不上了，因为age是范围条件，断了链；第三，如果你在复合索引里跳过中间列去查后面的列，中间列一旦缺失，后续列全部失效，这是最容易被忽视的坑。
我自己有个习惯：设计复合索引时，永远把等值查询的列放在前面，把范围查询的列放在后面。比如WHERE status = 1 AND create_time BETWEEN '2024-01-01' AND '2024-12-31'，就应该建(status, create_time)而不是反过来。原因就是等值列可以精准定位，范围列放最后能让索引利用率最大化。
2.2 为什么索引列顺序比“哪几列”更重要
很多开发建复合索引时只看“要包含哪些列”，却忽略了“列的顺序”。其实顺序决定了索引的过滤效率。这里有一个概念叫基数，可以简单理解为一列中不同值的数量。基数越大的列，区分度越高，放在复合索引越靠前的位置，越能快速缩小扫描范围。
假设user表有100万行数据，status只有3种取值，city有100种取值，last_login_time基本每个用户都不同。如果查询是WHERE status = 1 AND city = '杭州' AND last_login_time > '2024-01-01'，索引顺序应该怎么排？我建议是(city, status, last_login_time)。
为什么不把status放最前面？因为status区分度太低，先用它过滤，可能还要面对几十万行数据。先用city过滤，能直接砍掉99%的数据，再叠加status，最后用时间范围收窄，每一步都在快速缩小数据集。区分度高的列往前放，这是复合索引设计里性价比最高的原则。
还有一种常见误区是“把查询里经常出现的列都加上就行”，这会导致索引又宽又笨。索引列越多，每个索引条目占的空间越大，B+树的层高可能增加，查询反而变慢。我见过一个极端案例，一张表建了一个包含8列的复合索引，结果索引比表数据还大，优化器算了一下成本，直接选择全表扫描。记住，复合索引不是越多列越好，而是“恰好覆盖查询需求”最好。
3. 索引失效的现场还原：这些写法让DBA看了直摇头
3.1 函数运算、隐式类型转换和模糊查询的隐藏陷阱
索引失效是慢SQL的头号原因，而且往往藏在你觉得“很正常”的写法里。第一种高频坑是查询条件用了函数或表达式。比如WHERE DATE(create_time) = '2024-01-01'，这个写法在逻辑上没错，但create_time的索引会被函数破坏，因为函数改变了列值的原始顺序。正确的做法是把函数移到常量这边：WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。函数不一定要完全避免，但要确保它作用在查询参数上，而不是索引列上。
第二个高频坑是隐式类型转换。这个特别隐蔽，尤其是当索引列是字符串类型，而查询参数传了数字的时候。比如phone列是VARCHAR，查询写WHERE phone = 13800000000，MySQL会尝试把字符串列转成数字，索引就失效了。但如果反过来，列是数字类型，参数传字符串'13800000000'，MySQL会把字符串转成数字，索引反而可以正常使用。这个细节不同数据库处理方式还不一样，但最稳妥的做法就是查询参数类型和列类型保持一致，别依赖数据库的隐式转换。
第三个高频坑是前导模糊查询。WHERE name LIKE '%张%'会让索引失效，因为B+树是按索引列值的完整顺序排列的，你从前面的任意位置开始匹配，树根本没法定位起点。但WHERE name LIKE '张%'这种后缀通配是可以走索引的，它相当于把范围限定在“张”开头的区间内。
我还遇到过一种不算失效但性能很差的写法：在WHERE里做列运算，比如WHERE price * 1.1 > 1000。你以为只影响1000行，实际上引擎必须把每一行的price都算一遍才能判断，相当于隐式全表计算，任何索引都救不了。正确的做法是把运算改写为WHERE price > 1000 / 1.1，把运算移到常量那边。
3.2 OR连接、NOT IN和排序分页的索引陷阱
OR连接是另一个容易让人误判的地方。比如WHERE city = '杭州' OR city = '上海'，如果city有索引，优化器通常能把OR改成两个等值条件的合并，索引还勉强能用。但如果是WHERE status = 1 OR create_time > '2024-01-01'，一个走索引一个要全表扫，MySQL会选择把两个结果合并，代价是至少扫描一半数据，很多时候优化器干脆放弃索引。我的建议是：能用IN就别用OR，WHERE city IN ('杭州', '上海')不仅可读性好，优化器处理起来也更高效。
NOT IN和<>同样容易让索引失效，因为它们本质上是“排除某个值”，B+树很难利用“排除”语义快速定位。但这里有个例外：如果表中该值的占比极低，比如status字段99%都是1，只有1%是0，那么WHERE status <> 1理论上可以走索引，因为优化器会估算扫描行数，如果扫描代价低于全表扫描，它就会用索引。
排序和分页的坑更隐蔽。ORDER BY如果和索引顺序不一致，MySQL会先查出数据再文件排序（filesort），看起来查询走了索引，实际性能还是差。假设索引是(city, age)，查询WHERE city = '杭州' ORDER BY age能利用索引，因为city等值过滤后，age天然有序；但WHERE city = '杭州' ORDER BY name就没办法利用索引顺序，必须额外排序。还有一个经典的分页深坑是LIMIT 100000, 20，即使走索引，MySQL也要先扫描并丢弃前10万行，代价极高。优化的思路是先用子查询定位主键，再回表取数据：SELECT * FROM user WHERE id > (SELECT id FROM user WHERE city = '杭州' ORDER BY id LIMIT 100000, 1) LIMIT 20。
3.3 一张速查表记住失效场景
我把这些年遇到的索引失效场景整理成一张速查表，遇到慢SQL时可以对照检查。
| 场景 | 典型写法 | 为什么失效 | 推荐改写 |
|---|---|---|---|
| 函数包裹列 | WHERE DATE(create_time) = '2024-01-01' | 函数破坏列值顺序 | WHERE create_time >= ... AND < ... |
| 隐式类型转换 | WHERE phone = 13800000000 | 列被隐式转换 | 参数加引号，保持类型一致 |
| 前导模糊查询 | WHERE name LIKE '%张%' | 无法定位起始位置 | 改'张%'或考虑全文索引 |
| 列上做运算 | WHERE price * 1.1 > 1000 | 每行都要计算 | WHERE price > 1000 / 1.1 |
| 复合索引断列 | WHERE age > 25（索引city,age） | 缺少最左列 | 补上city条件 |
| 范围后接列 | WHERE city='杭州' AND age>25 AND name='张' | 范围条件断链 | 调整索引列顺序 |
| OR跨条件 | WHERE status=1 OR time > ... | 合并代价高 | 用UNION或拆查询 |
| 排序不一致 | ORDER BY name（索引city,age） | 额外文件排序 | 让排序列进索引 |
4. 慢SQL优化实战：从执行计划到索引设计
4.1 EXPLAIN怎么看，重点盯哪几列
遇到慢SQL，第一件事不是猜，而是看执行计划。MySQL里就是EXPLAIN SELECT ...，SQL Server里叫“显示估计的执行计划”，PostgreSQL用EXPLAIN ANALYZE。我重点看四个指标：type、key、rows、Extra。
type表示访问类型，从好到差大致是const、eq_ref、ref、range、index、ALL。ALL就是全表扫描，这是最需要警惕的；index虽然是全索引扫描，比全表好不了太多；range说明索引限定了范围，已经可以接受；ref和const是等值查询的理想状态。key表示实际用了哪个索引，如果为NULL说明没有命中索引。rows是优化器估算的扫描行数，这个数字越精确越小越好。Extra里出现Using filesort或Using temporary就要注意了，排序和临时表通常是性能瓶颈。
看执行计划时有个经验：不要只看key有没有值，还要结合rows判断。有时候索引命中了但rows还是很大，说明索引的区分度不匹配查询，这时候要考虑换索引或调整索引列顺序。我见过key显示用了idx_status，但扫描行数接近全表的案例，原因就是status区分度太低，索引形同虚设。
4.2 一个真实慢查询案例的优化全过程
去年接手过一个线上报表查询，每天晚上跑一次，耗时从最初的5秒恶化到40秒。表结构大概是这样的：订单表orders有600万行，查询条件是WHERE shop_id = 10086 AND status = 1 AND pay_time >= '2024-01-01' ORDER BY pay_time DESC LIMIT 50。
初始表上的索引是idx_status（只有status一列）和idx_pay_time（只有pay_time一列）。EXPLAIN的结果很尴尬：优化器选了idx_status，rows估算30万，Extra里还有Using filesort。原因也清楚：status过滤完还剩30万行，然后还要按pay_time排序，索引帮不上忙。我新建了复合索引idx_shop_status_pay，顺序为(shop_id, status, pay_time)，再跑EXPLAIN，type变成range，rows降到几百，Extra里的Using filesort消失了。
最终那条SQL从40秒降到0.08秒，差不多500倍提升。这个案例的典型之处在于：单列索引各自都有用，但组合起来就是无法满足查询需求。真正解决问题的不是“加索引”，而是“把过滤和排序的列组合成一条完整匹配查询路径的复合索引”。
这类优化过程中还有个小细节：ORDER BY pay_time DESC能不能利用索引，取决于索引定义里的排序方向。MySQL 8.0之前不支持降序索引，索引列默认都按升序存储，所以DESC排序经常需要反向扫描，虽然也能用，但性能略打折扣。MySQL 8.0引入了降序索引，可以定义(pay_time DESC)，SQL Server、PostgreSQL也都支持混排。如果你的查询大量使用DESC排序，值得关注这个特性。
4.3 索引维护：统计信息、碎片和冗余索引
索引不是建好就一劳永逸的。数据库优化器靠统计信息判断要不要走索引，如果统计信息过期，明明有索引，优化器也可能选择全表扫描。MySQL的InnoDB会在后台自动更新部分统计信息，但大量数据变动后，最好手动ANALYZE TABLE刷新一下。SQL Server有类似的统计信息更新机制，但夜间大批量导入数据后同样建议手动更新。
索引碎片是另一个容易被忽略的问题。频繁的增删改会让索引页分裂，逻辑顺序和物理顺序错乱，扫描效率下降。MySQL的OPTIMIZE TABLE可以重建表和索引，SQL Server里通过ALTER INDEX ... REORGANIZE或REBUILD处理。碎片率超过30%建议直接REBUILD，5%-30%之间REORGANIZE就够了。不过这个操作会锁表，生产环境要安排在低峰期执行。
还有一类问题叫冗余索引，两个索引有包含关系，比如idx_city和idx_city_age就是冗余的。idx_city_age本身可以覆盖city单独查询的场景，单独建idx_city纯粹浪费写入和存储成本。这类冗余索引在业务迭代中特别容易积累，建议定期用sys.schema_redundant_indexes这类视图排查清理。
5. 不同数据库的索引差异，以及容易被忽视的索引新特性
5.1 MySQL与SQL Server、PostgreSQL的索引细节差异
很多人会问：索引规则在MySQL里成立，换到SQL Server还成立吗？大体上成立，但细节有不少出入。MySQL的InnoDB是聚簇索引结构，表数据按主键组织，所以二级索引查询几乎都带一次回表。SQL Server默认也是B+树，但它是堆表加聚集索引的结构，可以建聚集索引（数据按索引排序），也可以建非聚集索引（类似MySQL的二级索引）。PostgreSQL更像SQL Server，支持非聚簇索引，没有InnoDB那种“表数据一定跟主键绑死”的约束。
NULL值的处理也值得注意。MySQL的索引默认允许NULL，但IS NULL能否走索引取决于版本和优化器。SQL Server对NULL的处理更谨慎，复合索引中只要有一列为NULL，索引利用可能变复杂。PostgreSQL的B+树索引默认可以处理NULL，但IS NULL的优化策略也和等值查询不同。我的经验是：业务上能避免NULL就尽量避免，给字段加NOT NULL DEFAULT默认值，能省去很多排查时间。
还有个易混淆概念：PostgreSQL里经常被提到的“双向索引”。其实它对应的就是复合索引中混合升序降序的定义，比如(column_a ASC, column_b DESC)。MySQL 8.0和SQL Server都支持这种混排索引，专门解决“A升序B降序”的排序需求。如果你看到有人搜“双向索引”，多半就是这个场景。
不同数据库的索引命名和创建语法略有差异，但不影响规则本身。SQL Server 2022、2019、2016这些版本我在实际项目里都用过，索引设计思路完全一致，新版本主要补充了在线索引操作等能力，并没有推翻旧的规则。
5.2 SQL Server在索引上的几个实用功能
聊到SQL Server，我顺便提几个比较实用的索引功能。第一个是包含列索引（Included Columns），创建非聚集索引时可以额外把一些列加到索引的叶子节点，但不参与排序。这和MySQL的覆盖索引思路类似，适合“查询列很多、但过滤和排序只需要少数列”的场景。比如CREATE INDEX idx_shop_status ON orders (shop_id, status) INCLUDE (pay_time, amount)，这样二级索引叶子节点直接带出pay_time和amount，查询时不用回表。
第二个是筛选索引（Filtered Index），也就是带WHERE条件的索引，比如CREATE INDEX idx_active_users ON users (city) WHERE status = 1。这个功能在“大部分数据是历史数据，只有小部分活跃数据被高频查询”的场景下特别好用。索引体积小，查询精准，维护成本也低。MySQL直到8.0都没有直接等价的功能，PostgreSQL里有部分索引（Partial Index）可以实现同样效果，SQL Server的筛选索引在实际优化中非常实用。
第三个是联机索引重建（Online Index Rebuild），就是ALTER INDEX ... REBUILD WITH (ONLINE = ON)，可以让索引重建过程中业务还能继续读写。SQL Server 2022对这个功能的优化更成熟，支持暂停和恢复，对超大表的维护非常友好。有些DBA谈索引碎片色变，有了在线重建，碎片整理就不必非等停机窗口了。
5.3 别把倒排索引和数据库索引搞混
热搜词里老出现“倒排序索引”“mapreduce倒排序索引”这类词，很多人以为跟数据库索引有关，其实这完全是两码事。倒排索引（Inverted Index）主要用在全文检索场景，比如Elasticsearch、Lucene，以及数据库的全文索引功能里。它的核心逻辑是记录“关键词出现在哪些文档”，而不是关系型数据库的B+树“按列值排好序”。搜索引擎搜“MySQL”会瞬间返回几百万个结果，靠的就是倒排索引。
Hadoop课程里经常出现的“倒排序索引”实验，本质上是MapReduce实现倒排索引的过程，重点在分布式计算，不在SQL。如果你在优化关系型数据库，看到“倒排索引”这四个字先别激动，想想你的场景是不是全文搜索。如果确实是全文搜索需求，MySQL可以用FULLTEXT索引，PostgreSQL直接用GIN加tsvector，SQL Server有全文索引组件，而不是硬往B+树方向套。
这个区分挺重要，因为很多开发在SQL里遇到LIKE '%关键词%'慢查询，第一反应是“要不要建倒排索引”，其实应该先分析数据量和场景。几百万行以内可以用LIKE加覆盖索引凑合，上千万行且有搜索需求，直接上独立的全文检索组件更合理。数据库不是万能的，别拿SQL索引去硬扛全文检索的活。
6. 常见问题与排查技巧实录
6.1 索引“超过许可范围”这类报错怎么处理
有些PostgreSQL用户会遇到一个报错，大致意思是索引列数或索引项超出限制。比如试图在一个表上创建超过32列的复合索引，PostgreSQL会直接拒绝；或者对超长的文本列建B树索引时，因为单行索引条目超过页面限制而报错。
我处理过类似案例：一张日志表有request_uri字段，长度几百个字符，开发想对它的完整值建普通B树索引，结果报错“index row size exceeds btree version 4 maximum 2704 bytes”。解决方案很简单：不要对整个长字符串建B树索引，而是提取前缀建索引，比如CREATE INDEX idx_request_uri_prefix ON logs (request_uri(100))，或者改用hash索引（只能等值查询），或者干脆上全文索引。超过“索引许可范围”不等于不能建索引，而是你选的索引类型不匹配字段特征。
还有一个不少人踩过的坑：在MySQL里对BLOB或TEXT列建索引时必须指定前缀长度，否则直接报错。这也是为什么我建议，表设计时能用VARCHAR就不要用TEXT，能用短字符串就不要用长字符串。索引字段长度越短，B+树层高越低，查询越快，这是物理定律，不是玄学。
6.2 开发环境正常、生产环境偏偏慢
“开发环境查得飞快，生产环境慢到超时”这个问题我在多个项目里遇见过。第一反应是看数据量。开发环境几千行数据，全表扫描也就几毫秒，生产环境几千万行，同样的SQL直接几十秒。这给我们的警醒是：测试阶段就必须把数据量放大到接近生产的量级，否则执行计划根本不可信。
第二个原因是统计信息差异。生产环境数据分布和开发环境差异大，优化器可能走不同的执行计划。我在SQL Server上遇过类似问题，开发环境走了索引，生产环境因为统计信息过期选了一个差计划。解决办法就是在业务低峰期更新统计信息，同时把查询参数化，避免因为不同的参数值导致计划抖动。
第三个原因，也是最容易被忽略的：生产环境的并发和锁竞争。开发环境一个人查询，SQL再快都没问题；生产环境同一时间几十个查询打过来，即使单条SQL走索引，也可能因为锁等待、I/O争抢而超时。所以优化SQL不能只看单条语句的执行时间，还要看它占用的资源，相同的执行计划在不同负载下的表现完全不同。
6.3 排查思路、实用工具和我要强调的经验
排查慢SQL，我会按固定顺序来：先抓慢查询日志，MySQL开slow_query_log，SQL Server用扩展事件或DMV，PostgreSQL用pg_stat_statements；然后用EXPLAIN看执行计划，重点确认索引是否生效；再看扫描行数和排序、临时表标记；最后结合业务场景判断该调整索引还是改写SQL。
工具有不少，MySQL官方自带的mysqldumpslow可以汇总慢SQL，pt-query-digest更好用。SQL Server里我习惯直接查sys.dm_exec_query_stats和sys.dm_db_index_usage_stats，这两个视图能告诉你哪些索引被用了、哪些索引从建好就没碰过，是清理冗余索引的直接依据。PostgreSQL的pg_stat_user_indexes专门查索引使用率。优化不是靠感觉，是靠数据。哪个索引该留、哪个该删，看使用统计比猜靠谱得多。
在这行干了这么多年，我经手过几十个慢查询优化，索引规则的核心其实就一句话：让查询条件和索引顺序对齐。可每次栽跟头，几乎都是败在“我以为索引会生效”上。所以如果你看完这篇文章只记住一件事，我希望是——写SQL之前先想想这条语句会怎么过滤数据，写完SQL之后顺手看一眼执行计划。单列索引各有各的道理，复合索引的顺序才是真正的讲究，这两步比任何索引技巧都管用。
最后再分享一个我个人的习惯：每当业务上线新功能、新增查询语句，我会顺手跑一遍EXPLAIN，把rows异常的大和type为ALL的语句捞出来。这个习惯帮我挡掉了至少十次线上慢查询事故。索引优化不是上线前的一次性工作，它是和业务代码同步演进的长期维护动作，养成习惯，比囤积再多技巧都有价值。
