索引策略才是慢SQL优化的根本：从B+树结构到实战调优

做数据库开发和后端运维这些年我有个很深的体会线上大部分慢SQL根子其实都出在索引策略上而不是SQL语句本身写得有多烂。遇到过不少同事拿着一条跑了几十秒的查询来找我劈头第一句就是“这条SQL还能怎么优化”可我用explain一看连索引都没建对SQL本身其实没什么大毛病。今天这篇就把索引策略这件事从底层原理到实战调优完整拆一遍内容包括SQL优化里最常见的慢SQL优化手段、并行SQL的使用边界、以及我在真实业务场景里反复验证过的索引设计方法。适合正在做SQL优化开发的同学、想系统梳理索引知识的技术人也适合刚接手慢SQL治理的DBA参考。1. 先搞清楚索引到底在干什么1.1 为什么慢查询总是指向索引问题——从全表扫描说起很多人一上来就背“索引能加速查询”但说不清楚它到底加速了哪一步。我说一个最直接的数字一张1000万行的订单表假设每行1KB数据总量差不多10GB。如果你不加任何条件去扫全表InnoDB要遍历全部数据页哪怕走顺序IO在普通SSD上跑一次全表扫描也要花十几秒甚至更久。而如果走索引B树高度通常只有3到4层定位一条记录只需要读取3到4个索引页每页8KB到16KB实际IO量是几十KB的级别。数据库慢不慢本质上拼的就是每次查询消耗的IO次数索引能把你从十几万次随机IO里救出来这就是它最大的价值。所以慢SQL排查的第一件事不是改SQL而是确认这条查询有没有正确走索引、走了哪个索引、预估扫描多少行。很多你觉得写得没问题的SQL在优化器眼里可能因为没有合适索引被逼着选了最笨的全表扫描路径。索引策略不是建了就完事它是一条SQL能不能跑快的先决条件。1.2 B树为什么能打——聚簇索引与二级索引的数据结构基础索引的核心数据结构是B树这和普通的B树有本质差异。B树的非叶子节点只存键值和指针不存实际数据所有数据都落在叶子节点上并且叶子节点之间用双向链表串起来。这样的好处有两个第一任何一次查询都要从根节点走到叶子节点路径长度固定且可控不会有B树那种偶尔在中间层就命中数据导致路径不稳定的情况第二范围查询直接顺着叶子节点的链表往后扫就行不需要反复回溯上层节点。在InnoDB里索引分聚簇索引和二级索引。聚簇索引就是主键索引它的叶子节点直接存整行数据所以按主键查是最快的。二级索引的叶子节点只存索引列和主键值查询时先扫二级索引再用主键回表读完整行。这个过程叫回表回表次数多了性能照样拉胯。这和查字典很像新华字典正文按拼音排序这就是聚簇索引如果你想按部首查一个字得先翻部首检字表找到页码再翻到正文那一页看内容这就是回表。如果部首检字表里直接把这个字的读音和解释都列全了你就不用再翻正文了对应到数据库就是覆盖索引。理解这些术语之后很多优化思路就自然冒出来了尽量用主键查、尽量做成覆盖索引减少回表、范围查询要小心排序和扫描范围。这些都是后面所有索引策略的地基。2. 索引设计从需求反推索引策略2.1 最左前缀原则的底层逻辑与实战推演复合索引是最常用的索引形态比如创建一个索引(a, b, c)数据库会按a排序再按b排序再按c排序像玻璃弹珠按颜色、大小、花纹依次排队。你能直接利用这个索引的条件组合是a、ab、abc因为走到哪一列哪一列都是有序的但如果你只查b或者只查c或者查bc索引基本就废了因为跳过了排在前面的列之后后面的列在整体数据里并不是全局有序的你没法通过二分定位方式快速缩小范围优化器只能大范围扫描甚至回退全表。这里有个常见误解要拆清楚很多人都以为“范围查询后面的列一定失效”其实不完全对。MySQL对复合索引的处理原则是等值匹配可以一直向后兼容但遇到第一个范围查询比如、、between之后后面的列就无法继续用于索引定位最多只能用于回表后的过滤。举个例子索引(a, b, c)查询条件是a1 and b10 and c5a可以走等值定位b可以走范围定位但c就没有办法在索引内精确匹配了。所以设计复合索引时等值条件列排前面范围条件排中间SELECT里需要的额外列放到最后面做成覆盖效果这是最实用的顺序规则。2.2 区分度、回表与覆盖索引——三个决定索引质量的关键参数很多人建索引只看“这个字段在WHERE里出现过”忽略了区分度结果建了个寂寞。区分度就是某个列中不同值的占比可以这样算SELECT COUNT(DISTINCT col) / COUNT(*) AS selectivity FROM table_name;假设一张用户表有5000万行性别列只有男、女、未知三个值区分度是0.00000006用这种列走索引每次查都可能返回上千万行加上回表成本优化器大概率直接放弃索引选全表扫描因为全表顺序读还更快。反之主键区分度是1订单号、身份证号这类列区分度接近1建索引效果就很好。经验上我会把“区分度低于20%的列单独建索引”视为不是一个好主意除非它是联合索引里用于等值过滤的前缀列并且能配合后续高区分度列缩窄范围。回表次数也是同理二级索引回表一次等于一次随机IO如果一次查询要回几千次上万次表性能不会比全表扫描好到哪里去。这时最有效的解法就是把SELECT要的列都塞进索引里做覆盖。覆盖索引的Extra里会显示Using index表示查询所需列都能从索引直接拿到这一步往往能把接口从几百毫秒压到几十毫秒。2.3 复合索引与冗余索引的取舍模型我见过很多库里索引比表还大一个表上挂七八个索引每个查询都能命中一个但写入性能被拖垮。索引不是免费的每一次INSERT、UPDATE、DELETE都要同步维护索引树索引越多写放大越严重。而且很多索引根本是冗余的已经有了(a, b, c)这个索引再建一个单独的(a)就是多余因为前者天然覆盖了后者的前缀能力。排查冗余索引可以直接看information_schemaMySQL里可以通过STATISTICS表把每个索引的列组合拉出来对比。我的取舍模型比较简单先统计线上真实查询模式把WHERE、ORDER BY、GROUP BY里高频出现的列组合列出来然后合并成尽可能少的复合索引每个复合索引尽量服务多类查询宁可允许一两条低频慢查询做全表扫描也不要因为索引堆积拖垮所有写入。索引设计一定是服务于真实查询模式的不是服务于“看起来每个字段都有索引”的安心感。3. SQL写法优化不改索引也能提速的几个硬技巧3.1 隐式转换看着没问题的SQL实际在偷偷全表扫描SQL优化常用的方法里有一个特别容易被忽略的坑隐式类型转换。最典型的就是字符串列和数字比较比如手机号字段是varchar你写where phone 13800138000数字会被转成字符串再比较还是字符串被转成数字比较取决于数据库内部规则但结果往往都是索引列上发生了函数级转换索引直接失效。我踩过的真实场景是表里user_id是varchar类型业务代码里传了个整数进来SQL写where user_id 123456。从结果看没毛病数据也查得到但explain一跑type是ALL扫描行数上千万。排查了半天最后把SQL改成where user_id 123456同样是等值查询却从全表扫描变成了ref级别接口时间从800毫秒降到40毫秒。类似的坑还有字符集不一致导致的隐式转换两张表关联字段一个utf8mb4_general_ci一个utf8mb4_bin即使内容一样优化器无法直接用索引因为两边排序规则不同需要先转换。碰到关联查询变慢先看字段类型和排序规则是否完全一致这是一个成本极低的检查项。另一个高频问题是函数套在索引列上。比如统计某天注册用户where DATE(created_at) 2024-06-01这种写法在绝大多数数据库里都让created_at索引失效因为你把索引列整体包进了函数里B树按原始值排序函数结果没法直接二分定位。正确的改法是范围条件where created_at 2024-06-01 00:00:00 and created_at 2024-06-02 00:00:00索引立刻能用。3.2 LIKE模糊查询与OR条件的改造思路LIKE查询也是优化重灾区。B树只能按前缀有序所以只有abc%这种前缀匹配才能走索引%abc和%abc%在MySQL里基本只能全表扫。业务上确实需要后缀匹配或中间匹配的时候我会分情况处理如果数据规模不大直接接受全表扫描但要控制QPS如果数据量很大单表几千万行就要换思路了比如把要搜索的文本转存到全文检索引擎里或者用ES做分词检索数据库只负责按主键批量取数据这种大数据量搜索场景靠SQL硬扛不划算。OR条件同理where a 1 or a 2这种如果同一列其实可以改写成IN索引使用效果更好如果OR连接的是不同列比如where status 1 or type 2优化器想走索引就得分别扫两个索引再合并很多时候它会干脆选择全表扫描。这类SQL我会改成UNION ALL把两个单列等值查询分开走索引-- 原始写法可能全表扫描 SELECT * FROM t WHERE status 1 OR type 2; -- 改写后两个查询独立走索引再合并结果 SELECT * FROM t WHERE status 1 UNION ALL SELECT * FROM t WHERE type 2;改写前要确认两个分支的结果集没有重复或者有重复也没关系、业务上可接受重复否则要用UNION去重。这个技巧在慢SQL优化里很常用实质就是让优化器避开它不擅长的多列OR合并路径把选择权交还给索引。3.3 分页深翻页与排序优化的常见解法分页是业务开发里每天都要写的功能但深分页的性能问题很多人都没意识到。offset 1000000 limit 10这种写法数据库要扫描前1000010行再丢到前100万行扫描掉了大量行这在大表上就是灾难。我记得有一次同事反馈一个后台列表接口越来越慢一开始几百毫秒翻到第500页后变成10秒。explain看下来SQL就是典型的深翻页typeALLrows扫到几百万。深分页改造方案里最常用的是延迟关联也叫延迟连接-- 原始写法深翻页扫描大量行 SELECT * FROM order_records ORDER BY created_at DESC LIMIT 1000000, 20; -- 延迟关联先只查主键再回表取完整行 SELECT o.* FROM order_records o INNER JOIN ( SELECT id FROM order_records ORDER BY created_at DESC LIMIT 1000000, 20 ) tmp ON o.id tmp.id;先把偏移量的主键用小结果集查出来再通过主键join回来回表范围被压缩到了只取需要的行接口性能能提升一个数量级。如果翻页场景很固定比如用户确实可能翻到很后面那更推荐游标分页记住上一页最后一条的id或时间下一页直接where id last_id或where created_at last_created_at order by ... limit 20这种方式不走深度偏移每一页成本都很低。排序优化则是另一个容易被忽视的点。ORDER BY能不能走索引看的是排序字段和WHERE条件能否构成复合索引的最左前缀。如果排序列不在索引里数据库就得把所有结果放进sort_buffer做排序数据量超过内存阈值还会落磁盘临时文件体现在explain的Extra里就是Using filesort。只要你在执行计划里看到Using filesort就要警觉它对大结果集的拖累非常明显。优化方式要么改索引让排序字段成为索引的一部分要么缩小排序集合并用延迟关联。SELECT *也要尽量少用它天然阻断了覆盖索引优化空间还会多传很多用不上的列。4. 慢SQL排查与并行SQL优化工具、思路、边界4.1 慢查询日志与explain的配合打法线上做慢SQL优化第一步一定是把慢查询日志开起来。MySQL里可以这么配置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都会被记录下来这是最基础的圈定候选清单的方法。拿到慢SQL后不要急着改SQL先explain重点看四个要素type、key、rows、Extra。type从好到坏大致是system、const、eq_ref、ref、range、index、ALL其中ALL是全表扫描index是扫全索引两种都是性能杀手range以上才算基本合格。rows是预估扫描行数这个数字越接近最终返回行数越好。Extra里出现了Using filesort、Using temporary、Using index这几个词含义完全不同——Using index是好事代表覆盖索引生效Using filesort和Using temporary则代表排序和临时表开销基本等于慢SQL预警。MySQL 8.0之后可以用explain analyze看实际执行时间和实际行数比explain的估算更真实。我的习惯是组合两个工具先用慢日志圈SQL再用explain看执行计划最后用explain analyze确认瓶颈到底在扫描、排序还是回表三步走完再决定改SQL还是改索引。4.2 并行SQL的适用场景与参数配置经验“并行SQL优化”听起来很高级但很多人把它理解成“让一条SQL同时跑多个线程就快”只要是慢查询就加并行这是大错特错的。并行SQL的本质是把一个大数据量的任务拆成多个子任务并发执行再汇总结果它只对特定场景有效大表聚合、大表扫描、大表JOIN比如一个5亿行的流水表算SUM或COUNT单线程扫确实耗时几分钟并行扫描就可以把时间压到几十秒。以MySQL 8.0为例InnoDB有一个参数innodb_parallel_read_threads默认是4控制在并行扫描表数据时使用的线程数。实际调的时候要小心线程数不是越大越好并行线程多了CPU上下文切换开销会反噬性能而且InnoDB的并行读取目前主要针对范围扫描场景OLTP高并发环境下开大并行反而会拖垮整体TPS。PostgreSQL的并行查询机制更成熟优化器会自己判断什么时候值得起并行worker相关参数是max_parallel_workers_per_gather默认2我一般调到4但同样要观察不能无脑调大。Oracle里的parallel hint是另一个形态虽然看起来能强制并行但在OLTP类系统上我从不推荐常规使用因为并行会消耗大量的进程资源和IO带宽只适合数仓离线任务这类明确可以接受资源倾斜的场景。简单说并行SQL优化的适用判断有两条一是单条SQL本身就耗时长且以聚合或扫描为主二是当前系统资源有富余两者同时满足才值得开并行。4.3 从执行计划反推索引问题执行计划不只是用来确认“走没走索引”更重要的是反推索引设计缺陷。比如执行计划显示typeref但rows很高只能说明索引类型不错但预估扫描行数远超预期这可能是复合索引前缀列区分度不够需要增加一个高区分度列放在前面再比如执行计划显示keyidx_a但Extra里有Using filesort说明排序字段没有被当前索引覆盖要么把排序字段加进索引要么调整SQL让排序条件与复合索引前缀匹配。还有一类情况是同一个字段在不同SQL里有时走索引有时不走这往往不是索引坏了而是优化器基于统计信息做的成本估算变了。统计信息过期会让优化器误判比如它对表行数的估算偏差很大导致认为全表扫描比走索引还便宜。这时跑一下ANALYZE TABLE刷新统计信息执行计划通常会恢复正常。从执行计划反推索引问题时我不建议只看单个explain要把这一条表的慢SQL全部拉出来对比它们共同的WHERE条件和ORDER BY字段才能设计出一个真正解决问题的复合索引而不是头痛医头地堆索引。5. 实战复盘一个从300ms到8ms的索引改造案例5.1 现象定位与执行计划分析说一个我实际做过的改造订单流水表order_records数据量5000万行。业务需求是按seller_id筛选某个商家的订单按created_at倒序排列再按创建时间分页最后join商家表取merchant_name展示。线上反馈列表页从打开到渲染要卡七八秒慢查询日志里这条SQL稳稳排第一。我先copy了慢SQL出来看关键部分长这样SELECT o.id, o.order_no, o.amount, o.status, m.merchant_name FROM order_records o LEFT JOIN merchant_info m ON o.merchant_id m.merchant_id WHERE o.seller_id 1024 ORDER BY o.created_at DESC LIMIT 20;explain结果很快印证了问题o表type是ALLrows预估一千万行Extra里有Using filesort。虽然表上已经有单独的seller_id索引和created_at索引但对这条SQL来说单列索引各自都解决不了排序和过滤的组合需求——seller_id筛出来一个商家的订单仍然有几十万行排序又没法利用索引只能在扫描之后做文件排序慢是必然的。5.2 索引设计与SQL改写分析完之后我做了两件事。第一件事是建复合索引把WHERE等值列放最前、排序列放中间、SELECT需要的其他字段放最后ALTER TABLE order_records ADD INDEX idx_seller_created_cover (seller_id, created_at, order_status, amount, order_no);这个索引一建seller_id顾上了等值定位created_at顾上了排序order_status、amount、order_no又让部分查询能直接从索引里取数减少回表。第二件事是SQL层面微调因为merchant_name在另一个表里join本身可能造成额外开销我把SQL拆成了两步——先在order_records上按索引查出20条主键再拿主键批量反查关联商家字段。两步操作各有明确索引支撑比一条复杂的LEFT JOIN让优化器自己猜路径更可控。5.3 改造前后的对比验收改造完再explaintype从ALL变成了refkey用上了idx_seller_created_coverrows直接从千万级降到了万级Extra不再出现Using filesort。实际压测从300ms降到了8ms页面接口体感是秒开。这个案例很典型普通SQL优化涉及的方法都用上了复合索引前缀设计、排序字段入索引、覆盖索引、延迟关联。整个过程没有改任何业务逻辑纯粹是让数据结构和查询方式匹配起来。还要强调的是上线方式。5000万行的表直接ALTER TABLE建索引会锁表阻塞线上读写。我当时用的是pt-online-schema-change在线变更工具先创建新表结构同步数据最后切换对线上业务几乎无感知。现在MySQL 8.0部分操作支持INSTANT算法但范围有限大表搞索引变更还是老老实实走在线DDL工具这条经验同样重要。6. 常见问题与排查技巧实录6.1 索引明明建了为什么优化器不走这是我在博客评论区被问得最多的问题。加完索引之后explain还是显示全表扫描常见原因有四个。第一统计信息过期优化器不知道你新索引的存在或低估了它的价值跑一次ANALYZE TABLE就能解决。第二隐式转换索引列是varchar但传了数字或两表关联列字符集排序规则不一致前面已经详细讲过。第三查询范围太大命中行数超过全表的一定比例比如全表10%甚至更高优化器算下来觉得扫描全表更划算这种情况索引未必能赢不能强求。第四函数运算包裹了索引列比如对列做了DATE()、LEFT()、运算索引失效。遇到这种情况可以用FORCE INDEX先验证索引到底能快多少确认走索引确实更快之后再考虑SQL改写或者调参不要一上来就怪优化器。6.2 索引碎片与维护为什么重建完索引更快了索引用久了会产生碎片尤其是在频繁DELETE和UPDATE的表上。InnoDB的索引页在数据删除后会留下空洞页内填充率下降本来一个页能存100条索引项碎片化之后只能存60条同一次索引扫描要读更多页IO放大。表现就是明明索引没变执行计划也没变但查询越来越慢。处理办法是定期或按需做碎片整理MySQL里可以用OPTIMIZE TABLE重建表并整理索引InnoDB还会额外做页重组但要注意这个操作会锁表并且需要至少一倍的临时磁盘空间大表要在业务低峰执行。MySQL 8.0之后ALTER TABLE ... ENGINE InnoDB也会触发类似的重建效果操作前记得看下当前磁盘余量空间不足会导致重建失败。我记得有个业务表删了上千万条历史数据discard掉的空间留在页里查询反而没变快。optimize之后索引页重新排列同样的查询从2秒降到了300毫秒。所以索引维护不只是建了就不管它是慢SQL优化治理里很重要的日常环节。6.3 新手做SQL优化最容易踩的五个坑最后整理一下我做SQL优化这些年来反复看到新人踩的坑每一条都是真金白银换来的。第一个坑是无脑给低区分度字段建单列索引。性别、状态、是否删除这类字段单独建索引大部分情况下都是负担大于收益我见过一个表给is_deleted建了索引查询根本没变快写入却拖慢了。第二个坑是复合索引列顺序拍脑袋。正确顺序应该是等值条件列在前、范围条件列中间、辅助排序列和覆盖列在后这个顺序对索引利用效率的影响是数量级的。第三个坑是SELECT *。它挡住覆盖索引、放大回表、浪费网络IO我要求团队业务SQL一律显式写需要的列慢SQL数量肉眼可见地下降。第四个坑是深翻页不处理。offset一千万limit二十这种写法不管索引多好都救不回来延迟关联和游标分页才是正解。第五个坑是直接在生产库手工ALTER TABLE建索引。大表会锁写严重的直接拖垮业务一定用在线DDL工具并挑低峰执行。这些都是我实际经历过的教训。SQL优化这件事能在索引层面解决的问题绝不要拖到代码层面去瞎改逻辑先把数据结构摸透再谈优化策略。