一、优化不是「改 SQL」，是「先量」

新手做优化的典型路子是：觉得哪条慢就改哪条，改完感觉快了就收工。 这条路的问题不在「改」，在「没量」——改之前不知道慢多少，改之后也不知道快多少。 正确的顺序是先有观测、再动手，动手之后再用同一套观测验证。 这一套观测在 MySQL 里叫「慢查询日志」，在 SQLite 里没有现成的，得自己写十行。 为什么强调「同一套观测」？因为「感觉快了」这件事极不可靠。 机器有缓存、有预热、有负载波动，同一条语句前后两次能差好几倍。 所以这本书里所有耗时都是「跑五遍取最快」——取最快不是为了让数字好看， 是为了把「偶发的干扰」剔掉。

二、慢查询日志：先找出最慢的那几条

教材给的三行是 MySQL 的开关：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SHOW VARIABLES LIKE 'slow_query_log_file';

long_query_time = 2 的意思是「超过 2 秒就记一笔」，默认是 10 秒。 这个数定得太大，日志里什么都不会有；定得太小，日志会淹掉。 所以第一个要问的问题不是「阈值多少」，是「这个业务的正常响应时间是多少」。 一个接口本来就要 800 毫秒，阈值定 200 毫秒毫无意义——它会把所有正常请求都记下来。 复刻版的思路完全一样：跑一批语句，每条记下耗时，最后按耗时排序。 排完序，最上面那几条就是「该改的」，下面那些可以先放着。

三、profile：时间花在哪一段

SHOW PROFILE 会把一条语句拆成若干阶段：解析、打开表、统计、发送数据…… 它的价值在于「分段」——让你知道是「算得慢」还是「传得慢」。

SET profiling = 1;
SELECT count(*) FROM tb_g WHERE cat = 42;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

这四个阶段的区分很实用：如果是「统计」久，那是扫描的锅； 如果是「发送数据」久，那是结果集太大的锅。 两种病的药方完全不一样——前者加索引，后者改分页。 沙箱里没有 SHOW PROFILE，但能分出另一件事：解析和执行的比重。 办法是跑两组：一组重复同一条语句（命中语句缓存），一组每条都不一样（强制重新解析）。 两组之差，就是「解析加计划」的成本。 这次实测下来，这个差只有一微秒级，而每条语句的执行是几十微秒到几毫秒。 所以「解析开销」在这套环境里几乎可以忽略。 这个结论有实际指导意义：不要去抠 SQL 的写法「让解析快一点」，那个方向没有肉。

四、EXPLAIN：三个字段够用大半

教材用 EXPLAIN 看 type、rows、key。 SQLite 的 EXPLAIN QUERY PLAN 给得更少，但关键的都在：

SCAN 是「从头扫到尾」，SEARCH 是「按索引定位」。 这两个词是整章的分水岭：看到 SCAN 就要问一句「能不能变成 SEARCH」。 SQLite 不直接给「扫多少行」，但可以自己数。 最简单的办法是先 count(*) 看一下结果集有多大，再想「为了这点结果扫了全表值不值」。 MySQL 的 rows 是个估算值，也不精确——但「同一张表， 两个计划的 rows 差十倍」这件事本身就够说明问题了。 估算值的用法是「比大小」，不是「当准数」。 最后提醒一个容易踩的坑：改完索引之后看计划，要换一个连接。 同一个连接会复用语句缓存里的旧计划，你看到的是「改之前的样子」。 这一点在第 11 章删索引、第 15 章删触发器时都踩过，是同一个机制。

五、动手 Demo：自己记一份慢查询日志，再量解析和执行的比重

这一段先把六条语句各跑五遍取最快，按耗时排成一张表——这就是慢查询日志的复刻。 然后跑两组对照，量出「解析加计划」的成本；最后列出 EXPLAIN QUERY PLAN 能给的三类信息。

import sqlite3, time

c = sqlite3.connect(u"shop.db", isolation_level=None)
c.executescript(u"""
CREATE TABLE tb_g (id INTEGER PRIMARY KEY, name TEXT, cat INTEGER,
                    price REAL, stock INTEGER);
CREATE INDEX i_cat ON tb_g(cat);
CREATE INDEX i_name ON tb_g(name);
""")
c.execute(u"BEGIN")
c.executemany(u"INSERT INTO tb_g VALUES (?,?,?,?,?)",
              [(i, u"商品%05d" % i, i % 100, 1.0 * i, i % 50)
               for i in range(1, 50001)])
c.execute(u"COMMIT")

def best(fn, n=5):
    t = None
    for _ in range(n):
        t0 = time.perf_counter()
        fn()
        dt = time.perf_counter() - t0
        t = dt if t is None else min(t, dt)
    return t

QUERIES = [
    (u"按主键取一行", u"SELECT name FROM tb_g WHERE id = 321"),
    (u"按索引列取一列", u"SELECT name FROM tb_g WHERE cat = 42"),
    (u"取全列", u"SELECT * FROM tb_g WHERE cat = 42"),
    (u"函数包裹列", u"SELECT count(*) FROM tb_g WHERE substr(name,1,2)='商品'"),
    (u"LIKE 中缀", u"SELECT count(*) FROM tb_g WHERE name LIKE '%123%'"),
    (u"全表聚合", u"SELECT avg(price) FROM tb_g"),
]

rows = []
for label, sql in QUERIES:
    ms = best(lambda sql=sql: c.execute(sql).fetchall()) * 1000
    pl = u" / ".join(r[-1] for r in c.execute(u"EXPLAIN QUERY PLAN " + sql))
    rows.append((ms, label, pl))
for ms, label, pl in sorted(rows, reverse=True):
    print(u"%8.3f ms  %-12s %s" % (ms, label, pl))

实跑输出：

--------------------------------------------------------------------
优化概述：先量，再改
--------------------------------------------------------------------


  优化的第一步不是改 SQL，是找出「哪条慢」
  --------------------------------------------------------------------
**教材 19.1 讲两件事：分析数据库性能、用 profile 工具看语句消耗。**
**两件事的前提是同一个：你得先有数据，才知道该改哪儿。**

  [教材原码 · 本沙箱不执行]
  | SET GLOBAL slow_query_log = ON;
  | SET GLOBAL long_query_time = 2;
  | SHOW VARIABLES LIKE 'slow_query_log_file';
  沙箱没有慢查询日志，这三行是教材原码。

**`long_query_time = 2` 的意思是「超过 2 秒就记一笔」，默认是 10 秒。**
**这个数定得太大，日志里什么都不会有；定得太小，日志会淹掉。**
**所以第一个要问的问题不是「阈值多少」，是「这个业务的正常响应时间是多少」。**

  没有慢查询日志，就自己记一条
  --------------------------------------------------------------------
**复刻版的思路一样：跑一批语句，每条记下耗时，最后按耗时排序。**
**写成一个函数，就是十行。**
毫秒   语句在干什么    执行计划
--------------------------------------------------------------------
8.256  函数包裹列      SCAN tb_g USING COVERING INDEX i_name
6.602  LIKE 中缀       SCAN tb_g USING COVERING INDEX i_name
5.365  全表聚合        SCAN tb_g
1.039  取全列（回表）  SEARCH tb_g USING INDEX i_cat (cat=?)
0.635  按索引列取一列  SEARCH tb_g USING INDEX i_cat (cat=?)
0.065  按主键取一行    SEARCH tb_g USING INTEGER PRIMARY KEY (rowid…
**六条语句，最慢的和最快的差了 127 倍。**
**注意「按主键取一行」是零点零几毫秒级，**
**而「函数包裹列」是好几毫秒——扫描五万行的代价就是这么直观。**
**这张表就是慢查询日志的复刻：它不告诉你「怎么改」，但它告诉你「先改哪条」。**
**没有这一步，优化就会变成「凭感觉改 SQL」，改完也不知道有没有变好。**

  profile：时间到底花在哪一段
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | SET profiling = 1;
  | SELECT count(*) FROM tb_g WHERE cat = 42;
  | SHOW PROFILES;
  | SHOW PROFILE FOR QUERY 1;
  沙箱不认 SET profiling，报 near "SET"。

**`SHOW PROFILE` 会把一条语句拆成若干阶段：解析、打开表、统计、发送数据……**
**它的价值在于「分段」——让你知道是「算得慢」还是「传得慢」。**
**复刻版没有这些阶段，但能分出另一件事：解析和执行的比重。**
**办法是跑两组：一组重复同一条语句（命中语句缓存），一组每条都不一样（强制重新解析）。**
跑法                  2000 次要多久
-----------------------------------
同一条语句，重复执行  0.1419 秒
每条语句都不一样      0.1476 秒
**两者只差 2.9 微秒／条——解析和计划的成本，比执行小两个数量级。**
**这个数很有说服力：它说明「优化 SQL 的写法」能省的是解析开销，而真正的大头在「扫多少行」。**
**所以看到一条慢查询，第一反应应该是「它扫了多少行」，不是「它的语法够不够漂亮」。**

  EXPLAIN：三个字段就够用大半
  --------------------------------------------------------------------
**教材用 `EXPLAIN` 看 `type`、`rows`、`key`。**
**SQLite 的 `EXPLAIN QUERY PLAN` 给得更少，但关键的都在：**
信息          MySQL                            SQLite
-----------------------------------------------------------------
怎么访问的    `type`（`ALL`／`ref`／`const`）  `SCAN` 或 `SEARCH`
用了哪个索引  `key`                            `USING ... INDEX`
扫多少行      `rows`                           计划里不直接给
**`SCAN` 是「从头扫到尾」，`SEARCH` 是「按索引定位」。**
**这两个词是整章的分水岭：看到 `SCAN` 就要问一句「能不能变成 `SEARCH`」。**
**SQLite 不直接给「扫多少行」，但可以自己数：在语句前面加 `EXPLAIN`，看它走了多少步；**
**或者干脆用 `count(*)` 估一下结果集有多大。**
**MySQL 的 `rows` 是个估算值，也不精确——但「同一张表，**
**两个计划的 `rows` 差十倍」这件事本身就够说明问题了。**

排序之后第一件事就清楚了：SCAN 的三条都在上面，SEARCH 的三条都在下面。 这张表没有告诉你「怎么改」，但它告诉你「先改哪条」——这就够开始了。 注意「取全列」比「取一列」慢一倍，而两者的计划一模一样。 计划相同、耗时不同，说明差别不在「怎么找」，在「找完搬多少」。 这一条会在 19.2 的覆盖索引里展开。 最后那组「同一条语句 vs 每条都不一样」的对照，量的是解析成本。 它只在微秒级，比执行小两个数量级——所以「优化 SQL 写法」这条路，天花板很低。

30 秒实验

1. 把 best 的 n 从 5 改成 1，再跑一遍，看数字抖动有多大，体会「为什么要取最快」。
2. 给 cat 建一个复合索引 (cat, stock)，把「取全列」那条改成 WHERE cat=42 AND stock=3， 看计划变不变。
3. 把五万行降到五千行，看那六条语句的排序会不会变——哪些还是慢，哪些变快了。

常见误区

1. 以为「改了就是优化了」。没有改前改后的同一套测量，等于什么都没做——只是换了个写法。
2. 以为慢查询日志的阈值越小越好。阈值要对着「业务的正常响应时间」定。 定小了日志会淹掉，等于没开。
3. 用一次耗时下结论。缓存、预热、后台任务都会干扰。至少跑几遍取最快，或者取中位数。

概述讲完了。下一篇动手：怎么把一条 SCAN 改成 SEARCH，以及哪些写法会让索引失效。

• ☐ （本篇完，下一篇 19.2 优化查询）

相关笔记

• 索引概述
• 删除索引
• 🛢️ MySQL从入门到精通 · 索引