慢查询又双叒叕出现了。这是我在数据库群里被问得最多的一类问题同样一条SQL数据量差不多为什么昨天执行5毫秒今天就变成5秒了大多数时候问题的根源都不在SQL本身写得多么离谱而是查询优化器选了一条糟糕的执行路径。这也正是关系查询处理和查询优化这一章的价值所在搞清楚数据库从收到一条SQL到返回结果中间到底经历了什么以及优化器凭什么、怎么样在众多执行方案里挑一个相对最快的。这一章的内容属于数据库系统原理里偏内功的部分不像建表、写SQL那样有立竿见影的反馈但恰恰是排查性能问题、设计表结构、评估索引方案时的理论根基。无论你是刚入门的学生、工作经验两三年的开发还是已经开始碰数据库内核的工程师把查询处理流程和优化策略吃透都能让你在面对慢SQL时不再靠瞎猜和乱加索引而是能真正看懂执行计划定位到问题所在。1. 从一条SQL说起查询处理的全流程到底做了什么1.1 查询分析数据库是怎么读懂你这条SQL的很多人以为数据库收到一条SQL就直接开始查数据了实际上第一步是词法分析和语法分析合起来叫查询分析。词法分析做的事情是把SQL语句拆成一个个有意义的单词也就是token。比如SELECT name FROM users WHERE age 18会被拆成SELECT、name、FROM、users、WHERE、age、、18这些独立的符号。这个阶段不关心你的表结构只关心这些词法单元是否合法比如有没有未闭合的字符串引号、有没有非法的数字写法。语法分析则是在词法分析的基础上按照SQL语言的语法规则构造一棵语法分析树。说白了就是判断你这句话通不通顺——SELECT后面跟的到底是列名还是乱七八糟的字符FROM后面是否跟了合法标识符WHERE条件的逻辑表达式结构是否正确。如果这里没过关数据库会直接报语法错误SQL根本进不到后面的优化阶段。注意语法错误和语义错误是完全不同的两回事。语法错误是句子不通比如SELEC name FROM users语义错误是句子通但对象不存在比如SELECT name FROM users WHERE nonexistent_col 1。语义检查发生在下一步。1.2 查询检查与视图展开语义层面的把关语法分析通过后查询检查阶段会做几件关键的事。第一检查查询中涉及的表和属性是否真实存在也就是做一次基于数据字典的元数据校验。第二检查用户的权限你有没有权限访问这张表、这个列。第三也是容易被忽略的一点视图的展开。视图展开的意思是如果SQL里访问的是视图而不是基表查询检查会把视图的定义取出来将视图引用替换成视图定义中的子查询。这个过程在关系代数层面相当于做了一次宏替换最终生成的查询语句里只有基表。对于带聚集函数的查询还需要检查聚集函数的参数类型是否正确对于GROUP BY的列要确认是否满足分组语义的要求。举个实际例子如果表里有一个视图active_users定义为SELECT * FROM users WHERE status 1你执行SELECT count(*) FROM active_users查询检查阶段会把它改写成SELECT count(*) FROM (SELECT * FROM users WHERE status 1) AS active_users然后再进入后续的优化流程。这也是为什么视图嵌套很深时会明显感觉查询变慢——嵌套层数越多展开后的中间结果可能越复杂。1.3 查询优化与查询执行真正决定快慢的分水岭语法和语义都正确之后数据库要做两件最核心的事查询优化和查询执行。查询优化器拿到的是查询检查阶段输出的初始代数表达式或逻辑计划它的任务是在等价的关系代数表达式集合里找出一个执行代价最小的。注意这里说的是代价最小未必是绝对最优。因为优化器通常不会穷举所有可能的执行方案——在表多、连接条件复杂的场景下这个搜索空间是指数级的穷举根本不现实。查询执行则是按照优化器选定的执行计划逐步调用存储引擎和访问方法真正去读表、读索引、做连接、做排序、做聚集运算最终把结果返回给用户。执行阶段是整个流程的最后一公里计划选得好不好最终都会在这个阶段用时间体现出来。所以整个查询处理流程用一句人话概括就是先听懂你说了什么再校验你说得有没有道理然后帮你找一条最划算的走法最后按这条走法把数据搬回来。这四步每一步都有各自的复杂性但找走法这一步也就是查询优化是拉开数据库性能差距的关键所在。2. 代数优化为什么逻辑计划也要重新排布2.1 关系代数表达式和它的树形表示查询优化在逻辑层面也叫代数优化它不涉及底层的存储和索引细节只针对关系代数表达式本身做等价变换。我们先看关系代数表达式是怎么表示一条SQL的。SELECT name FROM users WHERE age 18这条SQL翻译成关系代数就是 [ \pi_{name}(\sigma_{age18}(users)) ] 其中 (\sigma) 表示选择(\pi) 表示投影。这个表达式可以画成一棵关系代数语法树根节点是投影 (\pi_{name})子节点是选择 (\sigma_{age18})叶子节点是表users。如果一条SQL涉及连接比如查orders和users两表连接后满足某些条件的记录表达式树就会长成多分支结构。优化器在这棵树上做文章把选择、投影下推把连接顺序调整目的是让每步操作处理的数据量尽可能小。树形表示方式为什么重要因为优化的本质就是在保持了原查询语义的前提下重新安排树中节点的计算次序。2.2 启发式规则先做选择、再做投影、最后处理连接代数优化最常用的是一组启发式规则它们不是靠代价计算得出最优解而是凭借多年经验总结出的通常更快的调整原则。最核心的一条规则是尽可能早地执行选择操作。选择操作的作用是减少元组数量而减少元组数量这件事做得越早后续操作的处理量就越小。比如σ_age18(σ_status1(users))这个嵌套选择应该合并成σ_age18∧status1(users)并且尽量把选择下推到离表最近的位置。第二条常见规则是尽可能早地执行投影操作。投影可以减少列的数量减少每行数据的宽度配合选择一起下推能显著减少中间结果的存储开销和IO开销。但投影有一个细节容易被忽略如果上层操作需要某些列下推投影时就不能把这些需要的列删掉。例如查询最终需要name和age两列下推投影时只能在保证这两个输出列的前提下把其他列去掉而不能把age也一并投影掉。还有一条规则涉及连接顺序如果两个关系要做连接尽可能选择元组数小的那个作为左侧关系这样能为后续的嵌套循环连接减少外层扫描的代价。此外把笛卡尔积和随后的选择操作合并成连接操作也是启发式优化的常用手段——因为笛卡尔积会产生大量中间数据能合并就合并。2.3 一个具体的代数优化例子看一个稍综合的例子。假设有关系Student学号、姓名、系别、SC学号、课程号、成绩要查询计算机系学生选课并且成绩大于90的学生姓名。SQL写出来是SELECT Student.Sname FROM Student, SC WHERE Student.Sno SC.Sno AND Student.Sdept CS AND SC.Grade 90;初始的关系代数表达式长这样 [ \pi_{Sname}(\sigma_{Student.SnoSC.Sno \wedge Student.SdeptCS \wedge SC.Grade90}(Student \times SC)) ]这个表达式的问题是先把 Student 和 SC 做笛卡尔积形成一个极其庞大的中间结果然后才做选择和投影毫无性能可言。代数优化会怎么做第一步把连接条件从选择中分离将选择分解成σ_{Student.SdeptCS}和σ_{SC.Grade90}两个独立的选择并分别下推到笛卡尔积的两侧先在 Student 上筛出计算机系学生在 SC 上筛出成绩大于90的记录让两侧的数据量先大幅缩减。第二步此时笛卡尔积的两侧都变小了再把σ_{Student.SnoSC.Sno}和笛卡尔积合并成等值连接Student ⋈ SC避免生成无意义的全组合。第三步把投影π_{Sname}下推尽早去掉不需要的列同时注意连接条件需要Sno所以连接之前不能把Sno投影掉。经过这三步优化最终的表达式变成 [ \pi_{Sname}((\sigma_{SdeptCS}(Student)) \bowtie_{Sno} (\sigma_{Grade90}(SC))) ] 数据量从两个表全量做笛卡尔积缩小到两边各自筛选后的结果再做连接中间结果规模完全不在一个量级。这就是代数优化最直观的价值。2.4 代数优化的边界它管不到的事情需要说清楚的是代数优化不关心数据在磁盘上怎么存、索引走不走、连接算法选哪种这些是物理优化阶段的事。代数优化只保证一件事变换前后的关系代数表达式在逻辑上等价查询结果完全一致。即便如此代数优化也不是万能的。一些看似可行的等价变换在特定场景下反而会变慢。比如选择下推在某些分布式数据库里不一定划算——如果下推后单个分片上的数据过滤率不高反而无谓地增加了网络传输次数。还有对于包含子查询、窗口函数的复杂SQL代数优化的施展空间往往被语法限制并不能随心所欲地把操作下推到子查询内部。理解代数优化的边界比记住哪条规则更能帮你准确判断实际性能问题的位置。3. 物理优化数据怎么取、连接怎么做这一步才算数3.1 访问路径的选择全表扫描和索引扫描的争论逻辑优化确定了操作顺序之后物理优化要决定每一张表到底怎么读取。这里最关键的选择是全表扫描还是索引扫描。全表扫描就是从表的第一个数据页开始一页一页把所有数据块都读出来再逐行过滤条件。它的特点是IO次数和数据量线性相关但胜在连续扫描顺序读的效率高。当查询需要返回表中大部分数据时全表扫描反而是最优的方案。索引扫描则是先走B树找到符合条件的记录指针再回表取完整数据行。当过滤条件的选择性很高比如WHERE id 12345或者WHERE status 1 AND type 2能过滤掉99%的行时索引扫描能极大减少需要访问的数据页数量。但索引扫描有个隐患如果通过索引命中的记录散布在很多不同的数据页里每取一条记录都可能回表一次产生大量随机IO这比全表扫描的顺序IO还要慢。举个例子一张一亿行的订单表如果WHERE status PAID能匹配8000万行这时候走 status 字段上的索引做索引扫描意味着要做几千万次回表随机读很可能还不如全表扫描快。这种场景在真实业务里非常常见很多开发一看到查询慢就加索引但加了索引还是慢原因就是索引选择性太差优化器最终还是会选择全表扫描。提示判断一个过滤条件有没有资格走索引核心指标是选择率。一般经验是过滤后剩余数据量低于原表总行数的5%到10%索引扫描通常才有优势。但这不是绝对阈值实际还要结合行宽、数据分布、缓冲池命中率综合判断。3.2 连接操作的三种主要实现算法连接是关系数据库最昂贵的操作之一物理优化阶段需要为连接选择具体算法。三种最主要的选择是嵌套循环连接、排序合并连接和哈希连接。嵌套循环连接最朴素对外层表驱动表的每一行去内层表找匹配行。当然实际数据库不会傻到每次从磁盘重读内层表一般会利用内层表上的索引来加速匹配这叫索引嵌套循环连接。它的优势是内存占用小、可以流式输出适合内层表有合适索引、且驱动表数据量不大的场景。但如果驱动表很大或者内层表没有索引嵌套循环连接的代价就会指数级上涨。排序合并连接的做法是先把两个表都按连接键排序然后用类似归并排序双指针的方式线性扫描匹配。它的优点是不依赖连接键上有索引两个表都被排序之后匹配过程是顺序IO比较稳定。缺点是需要额外的排序开销如果数据量太大排序过程本身可能要落盘代价也不低。哈希连接是现代数据库的宠儿对较小的表或者称为构建侧扫描并构建一张哈希表然后扫描另一个表探测侧用连接键的哈希值去哈希表里找匹配记录。哈希连接特别适合等值连接尤其在两个表的数据量都比较大、且没有合适索引的情况下它往往是最快的连接策略。缺点是对内存有要求如果哈希表大到无法全部放进内存就得做分区哈希连接增加IO次数。三者的选择没有绝对的好坏完全取决于数据规模、内存大小、索引存在与否。这也是物理优化器要算代价的原因——同一组表连接在不同数据分布下最优算法可能完全不同。3.3 连接顺序的确定左深树的代价估算逻辑多表连接时物理优化还要决定连接的顺序和后连接顺序。经典的策略是把连接树构造成左深树左侧关系不断和新的表连接结果继续作为左侧输入依次向右扩展。为什么偏爱左深树而不是茂密的树枝树部分原因是左深树便于流水线化执行——执行器可以一边读取左侧的结果一边参与下一步连接不需要物化整个中间结果另一个原因是左深树状态空间相对小便于用动态规划计算代价。这里有个实践规律非常值得一提多表连接中执行计划倾向于先做能最大程度缩减中间结果量的连接。假设你有四张表 A、B、C、DA 和 B 连接后只剩100行C 和 D 连接后剩1万行那么先算 A⋈B 再和 C 或 D 连接明显比先算 C⋈D 更划算。这和代数优化里的先选择再连接一脉相承本质都是想让中间结果尽量小。不过真实优化器决定连接顺序时用的是基于代价的动态规划而不是这条粗糙的经验。它会考察不同连接顺序下每种连接的累计代价选出总代价最小的一条路径。4. 代价估算模型优化器凭什么敢替你做决定4.1 代价模型里到底放了哪些变量物理优化最终要落到一个数字每条执行计划的估算代价。大多数数据库的代价模型把代价分解成IO代价和CPU代价。IO代价按需要访问的磁盘数据块数来算CPU代价则是处理这些数据块中元组所消耗的计算资源最终两者按照一定权重合成一个总代价。以全表扫描一张包含 (b) 个数据块的表为例全表扫描的IO代价大致就是 (b)因为要把所有块都读一遍。如果使用索引扫描代价估算就更复杂了要加上索引树的访问代价从根节点到叶子节点的路径长度乘以相应层级的块数还要加上回表访问数据块的代价回表数据块数取决于索引过滤后的元组数量以及这些元组在数据页上的聚集程度。连接操作的代价估算是重头戏。嵌套循环连接的代价大致是外层元组数乘以内层单次查找的代价排序合并连接要估算两表排序的代价加上归并过程的IO代价哈希连接则要估算构建哈希表时读构建表的代价、探测阶段读探测表的代价以及如果哈希表放不进内存时额外写入临时文件的代价。4.2 统计信息不准代价估算必然失真所有代价估算都建立在统计信息之上。优化器需要知道一张表有多少行、每个字段的基数不同取值的数量、数据分布直方图、索引的选择率等。这些信息通常由数据库在表变更比较多的时候自动更新也可以手动触发。统计信息一旦失真整个代价模型就全盘皆输。最常见的情况就是字段数据分布极度倾斜比如订单表里有一个order_status字段99%的行是COMPLETED只有1%是PENDING。如果统计信息里记录的是均分分布优化器可能认为WHERE order_status PENDING会返回总行数的一半于是大幅高估这个过滤条件的选择率导致明明只需要查几百条记录却选了一条全表扫描或者错误的连接顺序。这就是为什么在互联网业务里频繁更新的热表往往要更积极地更新统计信息。很多DBA会在每日低峰期跑一次统计信息刷新任务就是为了避免统计信息过期导致优化器误判。作为开发遇到同一张表、同样数据量、SQL就是突然变慢的情况第一个要检查的不是索引而是统计信息和现有执行计划是不是对不上号了。4.3 基数估计代价估算中最容易崩的一环基数估计是整个代价估算里最微妙的部分。基数指的是某个操作输出的结果行数它对后续每一步操作的代价影响都极大——一个选择算错基数可能导致连接顺序、连接算法、嵌套层级全盘错乱。单表过滤的基数估计相对好做依赖直方图和唯一值数量就行。但多表连接后的基数估计就很头疼了连接结果的基数理论上等于两个表基数的乘积乘以连接键的选择率。如果连接键分布不均匀或者存在多个相关列上的过滤条件简单乘法估算会叠加误差导致偏差被放大到几个数量级。这也是实际优化中经常出现的优化器发疯场景。一个简单的例子WHERE category_id 1 AND brand_id 2如果category_id1的选择率和brand_id2的选择率分别准确但这两列其实有强相关性——category 1 下的品牌基本集中在 brand 5很少出现 brand 2——那么独立假设下的乘积估算就严重失实。优化器以为会返回大量数据结果实际只返回几十行计划自然选坏了。现在不少数据库引入多列统计信息和采样估算就是想缓解这个问题。5. 看执行计划把优化器的决策摊开检视5.1 执行计划的基本阅读方法讲了这么多理论实际工作中真正要面对的还是执行计划。不管是哪家数据库执行计划的核心要素是相通的每个操作节点、每个节点的访问方法、每步估算的行数和代价、以及节点之间的依赖关系。读执行计划时我先看三样东西。第一有没有意外的全表扫描 / 全索引扫描节点。第二每个节点的返回行数和估算行数是否悬殊——如果估算10万行实际返回100行说明基数估计出了问题。第三连接顺序和连接算法是否符合表的数据特点驱动表和被驱动表的选择是否合理。以某业务系统为例一条SQL在优化器选的计划里对 A 表做了Seq Scan估算137万行而这条SQL实际只要返回7行。只要看到这个问题的定位方向就非常明确不是SQL太慢是过滤条件没有被正确下推或者没能用上索引。与其反复盲试不如直接从执行计划里把优化器选坏的环节找出来。5.2 统计信息过期、参数化查询和优化器误判执行计划经常出现的一个问题是参数化查询的第一次执行决定了未来的计划。数据库为了避免每条SQL都重新做一遍代价很大的优化会对参数化SQL缓存执行计划后续执行直接复用。如果第一次传入的参数正好命中一个低选择率的值优化器生成了一套计划后续传入高选择率的参数还是执行同一套计划性能就会骤降。一个实际例子查询WHERE order_date $1第一次执行时传入昨天的日期只命中几百条新订单优化器选了索引扫描。几小时后业务侧传入半年前的日期理论上要命中几百万条但数据库还复用着原来的索引扫描计划每条记录都回表随机读慢到不可接受。解决这类问题有几条路径有些数据库支持自动重新优化有些场景下可以通过给SQL加上精确匹配的提示来强制走合适的访问路径还有的做法是把类似查询拆分成不同语义的模板让优化器分别生成计划。当然最实际的手段还是调整业务侧的参数使用方式避免用跨度过大的参数跑同一模板。5.3 手动干预优化器的手段和代价当优化器确实选错了工程上可以做的干预包括查询提示指定连接顺序或访问方法、改写SQL等效语义、增加冗余索引改变选择空间、人工拆分查询减少单条SQL复杂度。我一直强调查询提示算是无奈之举改SQL和调整索引是首选。因为提示往往和数据库版本、统计信息强绑定统计信息一变你强制指定的计划很可能就从次优变成远非最优。我在实际项目里就见过某个同事在SQL里强制指定了哈希连接当时数据量下确实快了几倍结果半年后数据量增长哈希连接的内存占用导致整个库的并发能力下降反而把系统拖垮了。所以使用提示前务必将它视为一个临时规避措施而不是长期方案。改写SQL是更稳妥的思路。比如把相关子查询改写成连接把OR条件改写成UNION ALL把函数包列的条件改写成范围条件这些改写往往能引导优化器走上正确的计划。当然每一条改写都要反复验证结果等价不能为了一时性能牺牲正确性。6. 实战中积累的查询优化经验6.1 优化器选错计划的典型信号总结几条我判断优化器大概率选错计划的信号遇到这些情况基本不用怀疑是硬件问题或并发问题先往执行计划方向查。第一SQL本身逻辑不复杂但执行时间随数据量增长呈非线性爆炸甚至出现几秒到几十秒的跳变。第二执行计划里出现了明显匪夷所思的节点比如小表驱动大表。第三同一个语义的SQL只要参数不同性能表现天差地别。第四加索引完全不起作用或者加索引后计划还是走了全表扫描。第五执行计划中某个节点的估算行数和实际返回行数差出两个数量级以上。这些信号的共性是数据库的决策依据出了问题而不是SQL写错了。定位方向应该集中在统计信息是否过期、过滤条件能不能正确利用索引、连接顺序是否合理这几个点上。6.2 几条实用的SQL改写建议在真实业务里踩过很多次坑之后我沉淀了几条经常见效的改写建议。第一避免在索引列上做函数运算比如WHERE substr(phone, 1, 3) 139基本让索引失效应该改成WHERE phone 139 AND phone 140之类的范围条件。第二能用连接就不要用相关子查询特别是在子查询内部还要访问外部表列的场景改为连接通常更容易让优化器找到高效计划。第三OR条件在多个字段上时优化器经常转成全表扫描如果OR的两侧分别有索引改写成UNION ALL往往能各自走索引。第四分页查询深翻页时LIMIT 100000, 20要扫描前面10万行再丢弃常见的优化是先把主键查出来再回表取详情把大偏移量转化为小结果集连接。这些改写不是银弹每一条都要结合实际数据和测试结果来验证但大多数场景下它们确实能让优化器的工作变得简单——优化器最怕的就是语义模糊、难以提取有效条件。6.3 统计信息和索引维护的节奏最后想聊一下容易被很多团队忽略的运维规范。统计信息的更新频率要和表的写入频率匹配。对于日增百万行以上的热表如果只依赖默认的自动采样阈值很可能在数据分布发生显著变化时仍然沿用老旧的统计信息。我建议每周至少做一次全量统计信息刷新大表可以按分区逐个刷新避免一次刷新锁表时间过长。索引不是越多越好但创建索引后要及时让统计信息感知到新索引。很多数据库在创建索引后不会自动更新统计信息优化器也就不知道新索引的存在导致你建了索引却感觉没效果。创建完索引后手动收集一次统计信息是让索引立刻生效的常规手段。还有一点是关于索引和查询模式的配套关系。实际业务里最有效的做法不是针对每一条SQL单独建索引而是梳理高频查询的模式把多个查询共享的过滤列做成联合索引并且注意列的顺序——等值条件的列放前面范围条件的列放后面。这套思路和优化器选择索引的左前缀规则是严格对应的。写到这里其实关系查询处理和查询优化这一章最核心的东西就这些了查询处理的四个阶段、代数优化怎么缩小中间结果、物理优化怎么选访问路径和连接算法、代价模型为什么依赖统计信息、以及如何通过执行计划反推优化器的决策。它不像写业务代码那样立竿见影但确实是所有数据库性能问题的地基。我自己在实际操作中的体会是每次排查一个慢SQL都不要急着用执行计划工具看一遍就下结论而是先在心里把查询处理流程默默走一遍这个条件能不能下推这个连接在数据分布下应该用哪种算法统计信息还准不准顺着这条链路去想定位问题的速度往往比自己瞎试快得多。你能提前预判优化器会怎么走也就真正掌握了和数据库对话的能力。
