搞了好几年数据库我发现一个很有意思的现象一说“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 13800000000MySQL会尝试把字符串列转成数字索引就失效了。但如果反过来列是数字类型参数传字符串13800000000MySQL会把字符串转成数字索引反而可以正常使用。这个细节不同数据库处理方式还不一样但最稳妥的做法就是查询参数类型和列类型保持一致别依赖数据库的隐式转换。第三个高频坑是前导模糊查询。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就别用ORWHERE 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 age25 AND name张范围条件断链调整索引列顺序OR跨条件WHERE status1 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_statusrows估算30万Extra里还有Using filesort。原因也清楚status过滤完还剩30万行然后还要按pay_time排序索引帮不上忙。我新建了复合索引idx_shop_status_pay顺序为(shop_id, status, pay_time)再跑EXPLAINtype变成rangerows降到几百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%建议直接REBUILD5%-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加tsvectorSQL 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_logSQL Server用扩展事件或DMVPostgreSQL用pg_stat_statements然后用EXPLAIN看执行计划重点确认索引是否生效再看扫描行数和排序、临时表标记最后结合业务场景判断该调整索引还是改写SQL。工具有不少MySQL官方自带的mysqldumpslow可以汇总慢SQLpt-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的语句捞出来。这个习惯帮我挡掉了至少十次线上慢查询事故。索引优化不是上线前的一次性工作它是和业务代码同步演进的长期维护动作养成习惯比囤积再多技巧都有价值。
SQL索引优化实战：从B+树原理到慢查询排查与失效场景解析
搞了好几年数据库我发现一个很有意思的现象一说“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 13800000000MySQL会尝试把字符串列转成数字索引就失效了。但如果反过来列是数字类型参数传字符串13800000000MySQL会把字符串转成数字索引反而可以正常使用。这个细节不同数据库处理方式还不一样但最稳妥的做法就是查询参数类型和列类型保持一致别依赖数据库的隐式转换。第三个高频坑是前导模糊查询。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就别用ORWHERE 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 age25 AND name张范围条件断链调整索引列顺序OR跨条件WHERE status1 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_statusrows估算30万Extra里还有Using filesort。原因也清楚status过滤完还剩30万行然后还要按pay_time排序索引帮不上忙。我新建了复合索引idx_shop_status_pay顺序为(shop_id, status, pay_time)再跑EXPLAINtype变成rangerows降到几百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%建议直接REBUILD5%-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加tsvectorSQL 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_logSQL Server用扩展事件或DMVPostgreSQL用pg_stat_statements然后用EXPLAIN看执行计划重点确认索引是否生效再看扫描行数和排序、临时表标记最后结合业务场景判断该调整索引还是改写SQL。工具有不少MySQL官方自带的mysqldumpslow可以汇总慢SQLpt-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的语句捞出来。这个习惯帮我挡掉了至少十次线上慢查询事故。索引优化不是上线前的一次性工作它是和业务代码同步演进的长期维护动作养成习惯比囤积再多技巧都有价值。
