一家餐厅后厨，客人点了一份糖醋排骨。粗心的服务员不知道食材放哪，只能把整个厨房翻个底朝天——冷藏库、调料架、水台，一件件拿起来比对，运气好三分钟上菜，运气不好把厨房搞得一团糟。数据库里的“全表扫描”就是这么回事：没有索引的查询会把整张表从头到尾扫一遍。而聪明的服务员会先查食材分类标签（索引），直接走向目标货架，两秒取到食材；熟练的点餐员还会把“先问后厨备货再回复客人”改成“直接和后厨同步出菜”（SQL改写）；遇到客人翻到第100页订单时，先翻目录定位再精准取货（延迟关联）。这篇文章把这些提速心法一一道来，让你写的每条 SQL 都像熟手服务员一样快、稳、准。

岗位关联

学会查询优化，是后端与数据工程师从“能跑”到“能扛”的分水岭：线上救火、面试加分、绩效亮眼，都离不开这一身基本功。

目录
  1. 可视化演示
  2. 查询执行流程：一张订单的后厨之旅
  3. 索引利用：给食材贴上分类标签
  4. 全表扫描避免：别把厨房翻个底朝天
  5. SQL改写技巧：换个点法，菜就快了
  6. 深分页优化：翻到100页后的加速秘籍
  7. 监控与调优：给厨房装上仪表盘
  8. AI 应用场景
  9. Python 演示
  10. 常见误区
  11. 面试题

可视化演示

展示SQL执行计划分析、索引利用与全表扫描对比、子查询改JOIN优化及覆盖索引延迟关联深分页优化，交互切换深入理解慢查询调优技巧。

本节配有交互动效（进入视口后自动加载，约 3KB）。

执行计划：SQL从文本到结果的全流程 解析器生成解析树，优化器选择执行计划，执行器调用存储引擎 EXPLAIN查看执行计划，关注type/key/rows/Extra字段 SQL → Parse → Optimize → Execute → Result 理解执行计划是优化的起点。

查询执行流程：一张订单的后厨之旅

你走进餐厅点了一份糖醋排骨。这个请求先被前台记下（解析 SQL），后厨总管看一眼菜单库，决定先腌肉还是先调汁（优化器生成执行计划），接着厨师们照单开工（执行），最后服务员把菜端到你面前（返回结果）。每一个环节都有成本，而最贵的往往是“开工”那一步。

数据库执行一条 SQL 也走这四步：解析（Parsing）、优化（Optimization）、执行（Execution）、返回（Fetch）。其中执行环节最耗时，但优化环节决定了执行效率的上限。优化器会像总管一样评估多种“做法”，挑一条代价最小的执行计划。

为了挑出最优计划，优化器用代价估算模型打分。查询的总成本主要由两个因素决定：读了多少页数据、处理了多少行数据。

所以想让“上菜快”，要么减少翻动的货架数量（读页少），要么减少拿起来比对的食材件数（处理行少）。索引就是同时削减两者的利器——这正是下一节要讲的主题。

Cost=Rpages×Cio+Rrows×CcpuCost = R_{pages} \times C_{io} + R_{rows} \times C_{cpu}
页是磁盘读写的最小单位，行是 CPU 计算的最小单位。想提速，要么减少读页，要么减少处理行数。
  1. R_{pages}（读取页数）查询需要从磁盘读取的数据页总数。表越大、索引越差，这个值越大。
    RpagesR_{pages}
  2. C_{io}（单页IO代价）每读一个页的耗时。机械硬盘约 10ms，SSD 约 0.1ms，是慢查询的大头成本。
    CioC_{io}
  3. R_{rows}（处理行数）执行过程中需要检查、过滤、排序的行数，行越多 CPU 干活越久。
    RrowsR_{rows}
  4. C_{cpu}（单行处理代价）对每行做 WHERE 判断、JOIN 匹配、GROUP BY 聚合的 CPU 开销。
    CcpuC_{cpu}

索引利用：给食材贴上分类标签

老厨师从不乱翻厨房。每样食材都有标签：猪肉在 2 号冷柜、糖在调料架 A3、醋在 A4。点菜时按标签直接去取，一步到位。数据库里的索引就是这样的“标签目录”。

索引常见实现是 B+ 树，它在索引列上排好序，让 WHERE 条件能像查字典一样跳过不相关的行。但索引不是随便建的，它有一个重要前提叫“最左前缀”：复合索引 (a, b, c) 必须从最左列开始连续匹配，跳过中间的列，后面的列就用不上。

建索引前还要看列的选择性——这个列的值区分度够不够高。选择性公式如下：

给“性别”贴标签没意义——每个货架都挂同样的标签，找了等于没找。要给“订单号”“手机号”这种一货一签的列建索引。复合索引 (a, b, c) 就像“章-节-小节”的目录，只报“小节”找不到位置，必须从“章”开始报。

S=∣distinct(K)∣NS = \frac{|distinct(K)|}{N}
选择性 S 越接近 1，说明这个列的值越“稀有”，索引越能快速缩小范围；S 很小说明区分度低，建了也白建。
  1. distinct(K)（去重取值数）索引列 K 去重后的取值个数。比如性别列只有“男/女”两个值，去重后是 2，区分度很低。
    distinct(K)distinct(K)
  2. N（总行数）表里一共有多少行数据。
    NN
  3. S（选择性）主键的选择性恒为 1，所以主键查询天然最快。给性别建索引时 S 只有 0.5 或更低，索引帮不上大忙。
    SS

全表扫描避免：别把厨房翻个底朝天

不带索引的查询，等于让服务员把整个厨房翻一遍。食材越多，翻得越久。数据库里的全表扫描（EXPLAIN 的 type=ALL）就是读取表的所有数据页，然后逐行过滤。一张 1GB 的表，至少要读 1GB 的磁盘数据。

全表扫描的代价可以精确算出来——它要读的页数等于表的物理大小除以每页大小：

避免全表扫描的手段有三个：为 WHERE 条件列建索引、避免在列上套函数（如 WHERE UPPER(name)='TOM' 会让索引失效）、把过滤条件尽量下推到子查询或 JOIN 的内层先执行。EXPLAIN 看到 type=ALL 时就要拉响警报。

翻厨房的次数越少，上菜越快。哪怕是缩小一半的搜索范围，都是数量级的提升。全表扫描就是那个“每次都从头翻到尾”的笨办法，能避则避。

Pfull=⌈BtablePsize⌉P_{full} = \left\lceil \frac{B_{table}}{P_{size}} \right\rceil
一张 100MB 的表，默认每页 16KB，全表扫描至少要读 6400 个页。页数越多，IO 时间越长。
  1. B_{table}（表大小）表占用的字节数，行数越多、字段越宽，这个值越大。
    BtableB_{table}
  2. P_{size}（页大小）数据库按页存储，MySQL InnoDB 默认每页 16KB。
    PsizeP_{size}
  3. ⌈⌉（向上取整）只要表里还有一行数据，不足一页也要读一整页，所以用向上取整。
    ⌈⋅⌉\lceil \cdot \rceil

SQL改写技巧：换个点法，菜就快了

客人问“有糖醋排骨吗？”服务员非要先回厨房翻一圈再出来答话，自然慢。改成领位时顺手看一眼菜单牌（EXISTS），或者直接让后厨报菜名（JOIN），都快得多。SQL 的写法直接影响执行计划的优劣。

最常见的两个改写是：子查询改 JOIN、避免 SELECT *。子查询（IN）的代价是外层每一行都要和子查询结果全量比对，而 JOIN 让优化器可以自由选择连接顺序和连接算法：

SELECT * 的坏处是输出无关列，增加网络开销，还阻碍覆盖索引发挥作用。EXISTS 与 IN 的取舍要看子查询大小：子查询结果集大时用 EXISTS 或 JOIN，结果集小时 IN 也能接受。OR 条件导致索引失效时，拆成 UNION 让每个分支各自走索引。

同样的食材，点法不同，后厨动线就天差地别。会优化 SQL 的人不只是“把菜点出来”，而是“把后厨的活儿提前安排好”。

CostIN=Nouter×NinnerCost_{IN} = N_{outer} \times N_{inner}
当子查询返回 N_inner 行、外层有 N_outer 行时，最坏要比较 N_outer × N_inner 次。数据量大时，这是乘法级别的爆炸。
  1. N_{outer}（外层行数）主查询需要处理的行数。
    NouterN_{outer}
  2. N_{inner}（子查询行数）IN 后面的子查询返回的行数。结果集越大，外层每行要做的比对越多。
    NinnerN_{inner}
  3. Cost_{IN}（比较总次数）把外层每行与内层每行逐一比较的次数。改写成 JOIN 后，优化器可用哈希连接等算法把复杂度大幅压低。
    CostINCost_{IN}

深分页优化：翻到100页后的加速秘籍

客人说“把第 10000 页的订单给我”。笨办法是从第 1 条订单开始数，数到第 10 万条再往后取 20 条；聪明办法是记住上一页最后一条的编号，直接让服务员从那个编号之后开始取。

LIMIT 100000, 20 的 OFFSET 实现确实是“先数 10 万行再丢掉”。分页越深，代价越大。深分页优化的两个主流方案是延迟关联和游标分页。深分页的代价公式如下：

延迟关联的做法是：先在索引上完成“找第 10 万条之后 20 条 id”的定位（极快），再用这 20 个 id 回表取详情——相当于先翻目录本，再精准取货，而不是把整本书从头翻到尾。

Costoffset=Oskip×Crow+K×CfetchCost_{offset} = O_{skip} \times C_{row} + K \times C_{fetch}
前 10 万行每行都要被检查一次，这一部分的成本与 OFFSET 成正比。页码越深，跳过成本越高。
  1. O_{skip}（跳过行数）OFFSET 的值，分页越深越大。
    OskipO_{skip}
  2. C_{row}（逐行检查代价）读页、比较、丢弃的累计成本，是深分页慢的根本原因。
    CrowC_{row}
  3. K（返回行数）LIMIT 指定要取的条数，比如 20。
    KK
  4. C_{fetch}（取回代价）真正满足条件的行回表读取的代价。K 固定时，这部分成本基本不变。
    CfetchC_{fetch}

监控与调优：给厨房装上仪表盘

后厨总管不能等客人投诉了才知道哪个环节慢。他要盯仪表盘：哪个菜出餐最久？哪个环节积压了？数据库的“仪表盘”就是慢查询日志和 EXPLAIN。一条查询的总耗时由多个环节组成：

EXPLAIN 重点看四列：type（访问类型，ALL 最差）、key（实际用到的索引）、rows（预估扫描行数）、Extra（出现 Using filesort 表示排序没走索引）。另外，索引不是越多越好——每个索引都要占空间、拖慢写入，读多写少的表才值得多建索引。

仪表盘装好了，优化精力才能花在刀刃上。总出餐慢，先看是配菜环节（网络）慢还是炒菜环节（执行）慢，别一上来就把厨房重新装修一遍。

---

Ttotal=Tnet+Tparse+Tplan+Texec+TfetchT_{total} = T_{net} + T_{parse} + T_{plan} + T_{exec} + T_{fetch}
优化要盯着最大的分量下手——通常是 T_exec（执行），但也可能是 T_net（网络往返）。别一上来就重写 SQL，先看时间花在哪。
  1. T_{net}（网络耗时）SQL 发送到数据库、结果传回客户端的时间。
    TnetT_{net}
  2. T_{parse}（解析耗时）把 SQL 文本变成语法树的时间。
    TparseT_{parse}
  3. T_{plan}（优化耗时）优化器生成执行计划的时间。
    TplanT_{plan}
  4. T_{exec}（执行耗时）真正扫描表、连接、排序的时间，通常是最大头。
    TexecT_{exec}
  5. T_{fetch}（取回耗时）客户端逐行取结果的时间。SELECT * 会把这一项拉高。
    TfetchT_{fetch}

AI 应用场景

电商订单列表深分页优化

阶段：数据存储与检索优化

解决的问题：用户翻到第 100 页时，传统 LIMIT 分页要扫描并丢弃数万行，接口超时。用覆盖索引 + 延迟关联，先在索引上定位 id 再回表取详情，响应时间从秒级降到毫秒级。

具体案例：某电商后台订单管理页，运营人员翻到第 500 页时页面卡死。把 SELECT * FROM orders ORDER BY id LIMIT 100000, 20 改写为延迟关联后，页面秒开，数据库负载下降 70%。

报表系统慢查询治理

阶段：数据查询与分析优化

解决的问题：BI 报表多表关联时，子查询嵌套导致优化器选不出好计划。用 EXPLAIN 定位全表扫描的驱动表，把子查询改 JOIN 并下推过滤条件，让优化器有更多选择空间。

具体案例：某 SaaS 公司月度经营报表涉及订单、用户、地区三表关联，原 SQL 执行 80 秒。改写成 JOIN + 覆盖索引后降到 3 秒，报表从“跑批”变成“秒出”。

日志检索系统索引设计

阶段：数据建模与索引设计

解决的问题：日志表写入频繁但查询慢，原因是没按查询模式设计索引。按最左前缀原则建复合索引，避免对时间列做函数处理，查询即可走索引直达。

具体案例：某运维监控平台按“应用名 + 时间范围”查日志，全表扫描耗时 10 秒以上。建立复合索引 (app_id, created_at) 后降到毫秒级，排障效率大幅提升。

Python 演示

python54 行
"""模拟深分页优化: 传统OFFSET线性扫描 vs 游标索引定位"""import bisectimport time  def fetch_page_linear(rows, offset, limit):    """传统OFFSET: 从第0行扫到第offset+limit行"""    scanned = 0    page = []    for row in rows:        if scanned < offset:            scanned += 1            continue        page.append(row)        if len(page) >= limit:            break    return page, scanned  def fetch_page_cursor(ids, last_id, limit):    """游标分页: 通过索引二分定位到last_id之后, 直接切片"""    pos = bisect.bisect_right(ids, last_id)    return ids[pos:pos + limit], pos  def main():    n = 200_000    page_size = 20    offset = 150_000     # 模拟无索引的有序表    rows = list(range(n))    ids = list(range(n))     # 传统OFFSET分页 (第7500页)    start = time.perf_counter()    page1, scanned = fetch_page_linear(rows, offset, page_size)    cost1 = time.perf_counter() - start     # 游标分页: 记录上一页最后一条id    last_id = offset    start = time.perf_counter()    page2, pos = fetch_page_cursor(ids, last_id, page_size)    cost2 = time.perf_counter() - start     print(f"深分页第 {offset // page_size} 页")    print(f"[OFFSET] 扫描 {scanned} 行 -> 耗时 {cost1 * 1000:.3f} ms")    print(f"[游标]   二分定位+取 {len(page2)} 行 -> 耗时 {cost2 * 1000:.3f} ms")    print(f"加速比: {cost1 / cost2:.1f} 倍")    print(f"两页内容一致: {page1 == page2}")  if __name__ == "__main__":    main()
目的：直观对比深分页两种策略的耗时差异——传统 OFFSET 需要线性扫描并丢弃前 15 万行，而游标分页通过索引二分定位直接取数。
  1. for row in rows:线性扫描每一行，这是全表扫描的本质
  2. if scanned < offset:跳过前 offset 行，OFFSET 越深浪费越严重
  3. bisect.bisect_right(ids, last_id)模拟索引的二分定位，复杂度 O(log n)
  4. return page2, pos返回 20 条数据和定位位置，两步完成取页
  5. print(f"加速比: {cost1 / cost2:.1f} 倍")输出两种策略的差距，深页时通常相差几十到上百倍

常见误区

误区很多人觉得 `SELECT *` 只是把不需要的列也查出来，数据库反正都读了一遍，无所谓。

正解SELECT * 会增加网络传输量，更关键的是阻碍覆盖索引——索引树里本来存了所需的列，但 SELECT * 要求取所有列，引擎只能回表读取完整行。就像目录卡上明明写了价格，你非要把商品从架子上搬下来才能看价格。

为何常见：初学者看不到网络 IO 和回表的发生，只看到“结果一样”，自然觉得无所谓。

误区以为给表建了索引，所有查询都会变快。

正解索引不是万能的。对列做函数运算、违反最左前缀、表数据量很小、写多读少的表，索引可能失效或成为负担。每个索引都占空间、拖慢写入，就像为每本书做卡片目录，书少时反而费事。

为何常见：索引能加速的场景太常见，让人忽略了它的存储成本和写入代价。

误区有人认为把子查询改成 JOIN 就一定会变快。

正解不一定。现代优化器会把部分子查询自动改写为 JOIN，但 IN + 去重语义改成 JOIN 后可能出现重复行，需要额外去重。正确做法是用 EXPLAIN 看实际执行计划，而不是拍脑袋改。就像同一个菜不同厨师做法不同，关键看最终出餐速度。

为何常见：网上教程常说“子查询改 JOIN 能提速”，但没说前提是优化器改写不了、且 JOIN 不会引入语义偏差。

误区认为 `LIMIT offset, count` 是从偏移量开始直接跳过去取，应该很快。

正解OFFSET 的实现是“扫描并丢弃”——数据库从头读完前 offset 行再丢掉，越深越慢。深分页应该用延迟关联或游标分页。就像翻书找第 10 万条笔记，你不可能直接翻到，必须一页页翻过去。

为何常见：LIMIT 的语义让人误以为“跳过”是免费操作，忽略了数据库必须逐行检查这一实现细节。

误区看到 EXPLAIN 结果里 type=ref 或 range 就认为查询已经优化好了。

正解还要看 rows（预估扫描行数）和 Extra 列有没有 Using filesort / Using temporary。索引用上了但排序列没索引，一样慢。像导航告诉你“走高速”，但没说高速正在堵车。

为何常见：大家对 type 列比较熟，容易忽略 rows 和 Extra 这两个同样决定性能的关键字段。

面试题

Q1. 一条 SQL 查询在数据库中的正确执行流程是？

单选题 · easy

  • A.解析→优化→执行→返回结果← 正确答案
  • B.执行→解析→优化→返回结果
  • C.优化→解析→执行→返回结果
  • D.解析→执行→优化→返回结果

答案：A。查询先被解析成语法树（理解需求），再由优化器生成执行计划（定方案），然后执行引擎跑计划（干活），最后把结果返回客户端（上菜）。就像点餐：看菜单→厨房定做法→做菜→上桌，顺序乱了就会出错。

Q2. 数据库中的索引，最像生活中的什么工具？

单选题 · easy

  • A.点菜时的食材清单
  • B.图书馆的目录检索卡← 正确答案
  • C.厨房里的储物冰箱
  • D.快递柜旁的取件号码

答案：B。索引就像图书馆的目录检索卡——不翻书架上的每本书，先查目录知道书在哪个架子哪一层，再直接去取。全表扫描则是把馆里所有书都翻一遍。食材清单、冰箱、取件码都只描述了“有什么”，只有目录卡体现了“先查位置再取物”的查找逻辑。

Q3. 以下哪些写法会导致索引失效？

多选题 · mid

  • A.对索引列使用函数包裹处理← 正确答案
  • B.对索引列进行算术四则运算← 正确答案
  • C.使用等值条件进行精确匹配
  • D.使用主键进行等值条件查询

答案：AB。在索引列上套函数或做运算（如 UPPER(name)、age+1），优化器无法用 B+ 树的有序性定位，只能退化为全表扫描。C 和 D 的等值条件能正常走索引。就像食材按编号归位，你非要按“编号+1”来找，目录就用不上了。

Q4. 以下哪些是避免全表扫描的有效手段？

多选题 · mid

  • A.为WHERE条件列建立索引← 正确答案
  • B.避免对列使用函数包裹← 正确答案
  • C.使用SELECT星号输出全列
  • D.利用覆盖索引加速查询← 正确答案

答案：ABD。建索引提供查找路径，避免函数包裹让索引保持可用，覆盖索引免去回表，三者都能避免全表扫描。SELECT * 输出所有列，反而增加 IO 和网络开销，拖慢查询。配合 EXPLAIN 验证，才能确认优化真正到位。

Q5. 子查询改写成 JOIN 连接的主要好处是？

单选题 · mid

  • A.减少SQL语句的篇幅
  • B.优化器有更多选择空间← 正确答案
  • C.让表名变得更简短
  • D.避免使用普通索引

答案：B。优化器对 JOIN 可以自由选择连接顺序和算法（嵌套循环、哈希连接等），而对 IN 子查询往往只能按“每行探测”执行，子查询结果集大时开销陡增。让优化器有更多选择空间是核心收益，篇幅变短只是附带效果。

Q6. 深分页查询 LIMIT 100000, 20 性能差的根本原因是？

单选题 · hard

  • A.要先扫描并丢弃前十万行← 正确答案
  • B.网络传输速度太慢导致超时
  • C.返回的结果集体积过于庞大
  • D.索引根本无法被正常创建

答案：A。LIMIT 的 OFFSET 不是“跳过”而是“扫描并丢弃”。数据库从头读完前 10 万行再丢掉，再取 20 行。页码越深，丢弃越多。网络和结果集大小不是主因——即使返回相同的 20 行，深页仍然慢，根子在于必须从头数。

Q7. 优化深分页的常用做法有哪些？

多选题 · hard

  • A.采用延迟关联定位技巧← 正确答案
  • B.游标分页记录上次位置← 正确答案
  • C.继续加大OFFSET偏移量
  • D.覆盖索引加延迟关联← 正确答案

答案：ABD。延迟关联先在索引上完成定位再回表取数；游标分页记住上一页最后 id，用 WHERE id > last_id 跳过已看数据；覆盖索引让分页查询无需回表。三者都避免了“从头数”。加大 OFFSET 只会让跳过行数更多，越调越慢。

Q8. 覆盖索引的含义是？

单选题 · mid

  • A.索引涵盖了所有数据表
  • B.查询所需列全在索引中← 正确答案
  • C.索引包含表中全部行
  • D.索引与表拥有相同大小

答案：B。覆盖索引指查询要取的列全部包含在索引里，引擎读完索引即可返回数据，无需回表。A 和 D 是字面误解，索引并不会复制整张表或整行数据，它只是按需保存少量列的有序结构。

Q9. 使用 EXPLAIN 分析时，哪个访问类型表示全表扫描？

单选题 · easy

  • A.ALL，全表逐行扫描← 正确答案
  • B.ref，索引引用访问
  • C.range，范围索引扫描
  • D.eq_ref，等值索引引用

答案：A。EXPLAIN 的 type 列中，ALL 表示扫描整张表，性能最差；ref、range、eq_ref 都是利用索引的访问方式，效率依次更高。看到 ALL 就要检查 WHERE 条件是否有索引可用、有没有函数包裹列。

Q10. 复合索引(a,b,c)遵循最左前缀，以下哪个查询无法利用该索引？

单选题 · extreme

  • A.只给a列加等值条件
  • B.给a和b列加等值条件
  • C.只给b和c列加等值条件← 正确答案
  • D.给a和c列加等值条件

答案：C。最左前缀要求复合索引从第一列开始连续匹配。C 只给 b 和 c 加条件，跳过了 a，索引完全用不上。A 和 B 从 a 开始能用到索引；D 从 a 开始也能用到 a 列的部分索引。就像目录按“章-节”两级组织，只报节名是定位不到的。