索引失效与查询优化
同一个索引，在一条 SQL 里被用上，在另一条写法几乎相同的 SQL 里被放弃，这是查询优化里最常见的困惑。答案落在 InnoDB 索引的物理形态上：B+ 树的键按顺序排列，能被索引加速的查询，都是能在这份有序序列上圈出一个起点和一个终点的查询。本篇以这条判据为主线，先把「索引失效」拆成七类可判定的情形，再讲 count 与分页这两类高频语句的执行差异，最后给出定位手段。
默认存储引擎为 InnoDB，版本行为以 MySQL 8.0 为准；5.7 与 8.0 不同的地方单独标出。索引自身的存储结构、页内组织与选型推导在 03-索引与 B+ 树，数据页与行格式在 02-InnoDB 存储结构：表空间、页与行格式，本篇只引用结论。
1. 判断「用得上索引」的底层依据
1.1 索引是一份有序的键序列
InnoDB 的索引底层是 B+ 树。非叶子节点只存键值和指向子节点的指针，叶子节点存放实际的索引项——聚簇索引放完整行，二级索引放主键值；同一层的叶子之间按键值排列并用双向链表连接。这份结构决定了索引能做什么：它把「键」排成一条全局有序的序列，查询能利用索引，等价于能在这条序列上圈出一段起止明确的区间。
按区间定位分两种形态：
| 定位方式 | 条件形态 | 树上动作 |
|---|---|---|
| 等值定位 | col = 常量、col IN (...)、col = 常量 OR col = 常量 | 沿键下降找到等于该值的叶子，读一条或同值的一组 |
| 范围定位 | col > 常量、col BETWEEN a AND b、col LIKE '前缀%' | 找到起点叶子，沿叶子链向右读到终点 |
两者依赖同一个前提：参与比较的「量」在树上是有序的。等值定位是区间的退化（起点与终点重合），范围定位是区间本身。
InnoDB 二级索引的叶子层（按键值升序，叶子间双向链表相连）
┌───────┬───────┬───────┬───────┬───────┬───────┐
│ 陈 │ 杜 │ 林 │ 王 │ 张 │ 周 │
└───┬───┴───┬───┴───┬───┴───┬───┴───┬───┴───┬───┘
▶ ▶ ▶ ▶ ▶ ▶ ← 按序相连
能圈出区间的条件 圈不出区间的条件
┌──────────────────────────┐ ┌──────────────────────────┐
│ name = '林' → 一条 │ │ name LIKE '%林' → ? │
│ name LIKE '林%' → 一段 │ │ name LIKE '%林%' → ? │
│ 10 <= id < 20 → 一段 │ │ WHERE LENGTH(name) = 1 → ?│
└──────────────────────────┘ └──────────────────────────┘
有起点、有终点 ⟹ type = range/ref 无起点、无终点 ⟹ type = ALL反过来，「这一列的值经过某个运算之后的结果」如果不在树上有序，就无法定位起点，只能逐行取值再计算——这是后面各类失效的共同形态。每一种失效，都可以回到这张图上来判断：查询条件在键序列上有没有一个明确的起止点。
1.2 二级索引、回表与覆盖索引
二级索引的叶子存的是主键值，要拿到整行需要再回聚簇索引取一次，这一步叫回表。是否需要回表，决定了同一份索引扫描的代价量级：
| 查询形态 | 是否回表 | EXPLAIN 的 Extra |
|---|---|---|
select * from t where name = '林' | 需要，每条命中的二级索引记录回表一次 | 空或 Using where |
select id from t where name = '林' | 不需要，主键值就在二级索引叶子里 | Using index |
select id, name from t where name like '林%'（联合索引含 id、name） | 不需要 | Using index |
第二种与第三种称为覆盖索引：查询需要的列全部在索引里，扫描不必回到聚簇索引。
另一种减少回表的手段是 ICP（Index Condition Pushdown，索引条件下推）。没有 ICP 时，存储引擎按索引找到行就回表，WHERE 里剩下的条件交给 Server 层判断；开启 ICP 后，能只用索引列求值的条件被下推到存储引擎层，先判断再决定要不要回表（来源：MySQL 8.0 Reference Manual, 10.2.1.6）。EXPLAIN 的 Extra 显示 Using index condition。ICP 用在 range、ref、eq_ref、ref_or_null 四种访问方式上，且对 InnoDB 只在二级索引上生效——聚簇索引已经读出整行，下推没有 I/O 可省。它减少的是回表次数，不改变索引扫描的区间。
2. 索引失效的七类情形
每一类都按同一套判据走：SQL 想比较的量，在 B+ 树的键序列上还有没有序。有，则能定位；没有，则退化。七类里前六类由写法引起，最后一类由优化器的代价估算引起——索引本身可用，但它被主动放弃了。
2.1 左模糊与全模糊匹配
LIKE '林%' 走索引，LIKE '%林' 和 LIKE '%林%' 不走，差别就在前缀能否作为定位起点。
LIKE '林%' 与 LIKE '%林' 在键序列上的差别
升序序列: 安 白 林一 林二 林三 王 张
│ └──────┬──────┘ │
│ ▼ │
│ 前缀为「林」的值连续排在一起
│ → 起点=第一个「林…」，终点=最后一个「林…」
└────────────────────────────────────────
LIKE '%林'：后缀为「林」的值可能散布在整条序列上
陈林、张林、周林 都满足，彼此不相邻
└─ 找不到起点 → 无法定位区间 → type=ALLLIKE '林%' 的匹配条件本身是「name 以林开头」，它对应 name 字典序上的一段连续区间，所以 EXPLAIN 里 type=range、key=idx_name。LIKE '%林' 的匹配条件不限开头，满足条件的值可能出现在序列的任意位置，没有起点可找。LIKE '%林%' 同理，而且更宽松。
工程上的两条边界：LIKE '林%' 虽然有起点和终点，终点由「第一个不满足前缀的值」确定，仍需按序扫过这段区间里的全部记录，命中行数多时并不比 range 更省；把前缀搜索换成全文检索（FULLTEXT）或外部搜索引擎，是这类需求在数据量大时的常规去向。
2.2 对索引列使用函数
WHERE LENGTH(name) = 6、WHERE DATE(create_time) = '2026-01-01' 这类写法会失效，原因同样落在键序列上。
索引按 name 的原始值有序，函数值不是键的一部分
键序列（按 name 升序）: 安 白 林 王 周
函数值 CHAR_LENGTH: 1 1 1 1 1
└─ 多字姓名、多字词混入后，函数值与 name 的字典序不再对应：
name 更小的值，算出来的函数值不一定更小
WHERE CHAR_LENGTH(name) = 1
└─ 树上没有「按函数值排序」的序列，找不到起点 → type=ALL
WHERE create_time >= '2026-01-01' AND create_time < '2026-01-02'
└─ 直接落在 create_time 的字典序上，有起点和终点 → 走索引B+ 树排序用的是索引列的原始值，不是「原始值经过函数计算后的结果」。DATE(create_time) 把时间戳截断到日期，得到的新值与原值的顺序不再一致；LENGTH(name) 也一样。要判断条件能否用索引，看等号左边是不是裸的索引列即可。
改写方式是把「对列做函数」换成「对常量做函数」，让条件重新落回原始列的有序区间上：WHERE DATE(create_time) = '2026-01-01' 改写成 WHERE create_time >= '2026-01-01' AND create_time < '2026-01-02'。同一条 SQL 还可以用区间表达时，区间写法优先。
MySQL 8.0.13 起支持函数索引（functional key part），对表达式的计算结果单独建一份索引：ALTER TABLE t_user ADD KEY idx_name_length ((LENGTH(name)))，这样 WHERE LENGTH(name) = 6 就能用上（来源：MySQL 8.0 Reference Manual, 15.1.15）。它本质上是在索引里新增了一份「按函数值排序」的有序序列，代价是每次写入要多维护一个索引、多一份存储。函数索引底层用隐藏的虚拟生成列实现，所以会占用列数配额。
2.3 对索引列做表达式计算
WHERE id + 1 = 10 失效，WHERE id = 10 - 1 不失效。
原因与上一节相同：id + 1 是一个作用在索引列上的表达式，等号左边不再是裸的 id，树里没有「id + 1 的结果」这份有序序列，只能把每一行取出来算一遍再判断。右边的 id = 10 - 1 里，10 - 1 是两个常量之间的运算，优化器在解析阶段就会做常量折叠（constant folding），查询条件等价于 id = 9，落回 id 的有序区间。
优化器确实有能力把 id + 1 = 10 推成 id = 9——这类移项在形式上是可逆的——但它没有实现通用的表达式反解。表达式形态多样（id * 2、id % 7、ABS(id)），逐一反解的代码复杂度与收益不成比例，于是把「不要在索引列上写表达式」这条约束留给使用者。
2.4 隐式类型转换
这一类比前几类更容易踩，因为它不写在 SQL 里，写在列的类型定义与传入参数的类型之间。
转换方向的规则。MySQL 比较两个类型不同的值时，按下面的规则转换（来源：MySQL 8.0 Reference Manual, 14.3）：两个参数都是字符串时按字符串比较；两个参数都是整数时按整数比较；一个参数是十进制数时，视另一参数而定；其余情况一律按双精度浮点数（double）比较，字符串与数字比较就落在这条。手册的原话是 In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.
由此得到两条方向相反的结果：
比较方向决定索引能否使用
① 列是字符串，参数是数字：phone VARCHAR，WHERE phone = 1300000001
按 double 比较 → 需要转换的是 phone 一侧
double(phone) = 1300000001
转换作用在列上 → 列被「函数化」→ 索引失效（type=ALL）
② 列是数字，参数是字符串：id INT，WHERE id = '1'
按 double 比较 → 需要转换的是常量一侧
id = double('1')
转换作用在常量上 → 列保持原样 → 索引可用（type=const/ref）
判据：看哪个操作数需要转换
列一侧被转 ⟹ 失效 常量一侧被转 ⟹ 不影响索引手册对第一种情形给出了直接结论：For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly.，理由是 there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'（来源：MySQL 8.0 Reference Manual, 14.3）。这个理由比「等价于对列用 CAST」的说法更准确：失效的根源在于，字符串到数字的转换是多对一的，同一个数字可能对应多个不同的字符串，树无法按数字去反查字符串的区间。把它理解成「列被 CAST(phone AS signed int) 了」是常见但不够准确的简化——实际比较按双精度浮点进行，且转换未必是整数截断。
三条可复现的判据：
- 判断当前 MySQL 采用哪种比较方式，执行
SELECT "10" > 9;。返回1说明走了数字比较（若按字符串比较，"1" < "9"，结果会是0）。 - 字符串列配数字常量，
EXPLAIN里type=ALL，且key为空。 - 数字列配字符串常量，
EXPLAIN里仍能命中索引。
一个必须知道的危险情形：字符串列与整数 0 比较时，所有非数字开头的字符串都会转换成 0，于是条件恒真。手册的例子是 SELECT * FROM t1 WHERE c3 = 0;，c3 是 VARCHAR，结果返回了全部 5 行（grape、apple 等都在内），而且 strict SQL mode 也挡不住（来源：MySQL 8.0 Reference Manual, 14.3）。要避免，把 0 加引号写成 '0'。这类查询在生产里会静默返回远超预期的行数，命中大量无关数据。
字符集与排序规则不一致时。字符串比较还要先确定用哪一侧的排序规则。MySQL 用可强制性（coercibility）来裁决：显式 COLLATE 为 0、两个不同排序规则字符串的拼接结果为 1、列的排序规则为 2、系统常量为 3、字面量为 4、数值或时间值为 5、NULL 为 6，取值越低越优先（来源：MySQL 8.0 Reference Manual, 12.8.4）。两边可强制性相同时，若都是 Unicode 或都不是 Unicode 则报错；一边 Unicode 一边非 Unicode 时，Unicode 一侧获胜，另一侧自动做字符集转换；同一字符集里 _bin 与 _ci / _cs 混合时用 _bin。
排序规则不一致时，比较用的排序规则与索引的排序规则可能不同
列的排序规则 utf8mb4_0900_ai_ci
│
├─ WHERE name = 'A' 列与字面量同排序规则
│ └─ 用列自身的排序规则比较 → 索引的序与比较的序一致 → 可用
│
└─ WHERE name = 'A' COLLATE utf8mb4_bin 显式指定了别的排序规则
└─ 比较的大小写敏感规则变了，与索引里既有的序不再一致
└─ 优化器不能按索引区间定位 → 退化（待验证：具体到版本
与字符集组合，手册未逐条列出，用 EXPLAIN 实测为准）可用 SELECT COERCIBILITY(...) 查看某个表达式的可强制性：SELECT COERCIBILITY('A'); 返回 4，SELECT COERCIBILITY(VERSION()); 返回 3（来源：MySQL 8.0 Reference Manual, 12.8.4）。工程上的做法是让参与比较的两侧字符集与排序规则一致——连接层用 SET NAMES 对齐 collation_connection，建表时把列与字面量统一到同一套排序规则；只有在确需区分大小写时才用 COLLATE，并确认它没有把索引挡在门外。
2.5 联合索引与非最左匹配
联合索引 (a, b, c) 在树上的排序规则是逐列比较：先按 a 排，a 相同再按 b 排，a、b 都相同再按 c 排。所谓最左匹配，是这条排序规则的自然结果。
联合索引 (a, b, c) 的排序键
先按 a；a 相同再按 b；a、b 都相同再按 c
a=1 ─┬─ b=1 ─┬─ c=1
│ ├─ c=2
│ └─ c=3
├─ b=2 ─┬─ c=1
│ └─ c=2
└─ b=3 ─……
a=2 ─┬─ b=1 ─……
└─ b=2 ─……
WHERE a=1 AND b=2 AND c=3 → 三级过滤，定位到单点
WHERE a=1 AND b=2 → 定位到 (a=1,b=2) 的一段，需扫这段内全部 c
WHERE a=1 → 定位到 a=1 的一段
WHERE b=2 → 整棵树上 b 的值不连续，找不到起点
WHERE a=1 AND c=3 → a 定位到一段；c 不是这段内的连续键
WHERE a=1 AND b>2 AND c=3 → b 是范围，圈出一段；c 在其内仍不连续三组需要展开的边界：
其一，条件书写顺序不影响结果。 WHERE a = 1 AND b = 2 与 WHERE b = 2 AND a = 1 效率相同，优化器会按索引顺序重排等值条件。决定能否命中的是条件的集合，与 WHERE 里的书写顺序无关。
其二，a=1 AND c=3 属于索引截断。 a 用来定位，c 不是这段区间的连续键，无法参与缩小扫描范围。没有 ICP 时，c 的判断发生在 Server 层：先按 a=1 回表取出整行，再在 Server 层比对 c，回表次数等于 a=1 的行数。有 ICP 时，c 这类只依赖索引列的条件下推到存储引擎层——因为 c 就在 (a, b, c) 索引里——在回表之前先过滤，从而减少回表次数。EXPLAIN 的 Extra 会显示 Using index condition。ICP 减少的是回表，a 到 c 之间的那段扫描并没有被缩短。
其三，范围查询之后的列用不上。 在 (a, b, c) 上写 WHERE a = 1 AND b > 2 AND c = 3：a 是等值、b 是范围，定位到的区间是「a = 1 且 b > 2」的那一段；c 在这段里不再按顺序排列（c 只在 b 取定值时才有序），无法继续缩小范围。这条规则决定联合索引的列序设计：等值条件的列排在前面，范围条件的列排在最后，这样范围列之前的等值列都能参与定位。按这个原则，(a, b, c) 面向 a = 1 AND b > 2 是合理的；但若查询常写 a = 1 AND c = 3 AND b > 2，把 c 放到 b 前面更合适。
版本补充：索引跳跃扫描。 MySQL 8.0.13 起，当查询没有第一个索引列的条件、但后续列上有范围条件时，优化器可以跳过扫描（Skip Scan）：对第一个索引列的每个不同取值分别构造一段范围，逐段扫描。手册的例子是 (f1, f2) 上的 WHERE f2 > 40，优化器对 f1 的每个不同值各做一次 f1 = N AND f2 > 40 的范围扫描（来源：MySQL 8.0 Reference Manual, 10.2.1.2）。它受 optimizer_switch 的 skip_scan 开关控制，默认 on，EXPLAIN 的 Extra 显示 Using index for skip scan。适用条件包括：单表、查询只引用索引里的列、不在第一个索引列上的谓词必须是常量等值谓词、在后续列上必须有范围条件，且不能有 GROUP BY 或 DISTINCT。跳跃扫描把「非最左」从「完全用不上」放宽为「可能用得上」，但首个索引列的不同取值越多，需要构造的范围段越多，收益也随之下降。
列序设计的取舍。联合索引的列序不存在对所有查询都最优的排法，只能按实际查询模式取舍。设索引要在 (a, b, c) 与 (a, c, b) 之间二选一：若查询集合以 a = ? AND b > ? 与 a = ? AND b = ? 为主，b 在两种写法里都能参与定位，(a, b, c) 合适；若以 a = ? AND c = ? AND b > ? 为主，只有把等值的 c 放到范围列 b 之前，(a, c, b) 才能让三列都参与定位，代价是另一种排法下 b 的查询用不上 c。多组高频查询互相冲突时，把常用返回列一并放进索引组成覆盖索引，常常比再加一个联合索引更划算：它在减少回表的同时不引入新的索引维护面（待验证；具体收益取决于返回列的宽度与写入频率）。
2.6 WHERE 中的 OR 与索引合并
OR 的语义是「满足任意一侧即可」。如果一侧的条件列没有可用索引，这一侧只能逐行判断，整体就无法定位区间，退化为全表扫描。
WHERE id = 1 OR age = 18
┌─ 两个条件列都有索引 ──────────────────────────────┐
│ 优化器可做索引合并（index_merge） │
│ ├─ id = 1 走主键索引 → 结果集 A │
│ ├─ age = 18 走 age 二级索引 → 结果集 B │
│ └─ A ∪ B（Extra: Using union(id, age)） │
└───────────────────────────┬───────────────────────┘
│
┌─ 任一条件列无索引 ─────────▼───────────────────────┐
│ OR 要求两侧都能定位；一侧无法用索引， │
│ 该侧只能逐行判断 → 整体退化为全表扫描（type=ALL） │
└────────────────────────────────────────────────────┘当两侧都有索引时，优化器可能选择索引合并（Index Merge）：用多次 range 扫描分别取到各条件的行，再把结果集合到一起。它只作用于单张表，不跨表；合并可以是并集、交集或「并集的交集」（来源：MySQL 8.0 Reference Manual, 10.2.1.3）。三种算法在 EXPLAIN 的 Extra 里分别显示为：
Extra | 算法 | 适用条件 | 额外代价 |
|---|---|---|---|
Using intersect(...) | 交集 | 多个不同键上的 range 条件用 AND 连接 | 同时对多个索引做扫描再取交 |
Using union(...) | 并集 | 多个不同键上的 range 条件用 OR 连接，各条件本身是等值 | 合并各索引已按序的结果 |
Using sort_union(...) | 排序并集 | 同上，但条件本身是范围 | 必须先取出全部行 ID 排序，再返回 |
union 与 sort_union 的差别在返回前是否需要排序：union 适用的条件各段结果已按主键有序，能直接归并；sort_union 适用的条件（如 key_col1 < 10 OR key_col2 < 20）各段结果不能保证同序，必须先把两边的主键都取出来排序（来源：MySQL 8.0 Reference Manual, 10.2.1.3）。这解释了为什么范围条件的 OR 通常比等值条件的 OR 贵。
索引合并由 optimizer_switch 的 index_merge、index_merge_union、index_merge_sort_union、index_merge_intersection 四个开关控制，默认都为 on。它也有代价：读多份索引再做集合运算，当各条件命中行数很多时，成本可能超过一次顺序全表扫描，此时优化器仍会选 type=ALL——这属于下一节的代价判断，不是规则问题。
2.7 区分度太低：优化器主动放弃索引
前六类里，索引要么能用、要么不能用；这一类不同：索引可以用，但优化器估算后主动选了全表扫描。它是「明明建了索引，EXPLAIN 却是 type=ALL」里最常见的一种，特征是 possible_keys 非空、而 key 为空。
判断依据是代价。优化器为每个候选计划估一个代价，再选最低的（来源：MySQL 8.0 Reference Manual, 10.9.5）。走二级索引的代价由两部分拼成：按索引定位与扫描的代价，加上每条命中记录回表的代价；回表在主键上是随机 I/O。全表扫描则是顺序读整张表。当某条件命中的行占全表比例很高时，「扫索引 + 大范围回表」的随机 I/O 总量可能超过「顺序扫全表」——优化器据此放弃索引。
代价模型由两组常量构成，都存在可查询、可修改的系统表里（来源：MySQL 8.0 Reference Manual, 10.9.5）：
| 表 | 常量 | 影响 |
|---|---|---|
mysql.server_cost | row_evaluate_cost | 逐行求值的代价；调高它会让「扫很多行」的计划相对更贵，偏向少读行的计划 |
mysql.server_cost | key_compare_cost | 比较键的代价；调高它会让 filesort 相对更贵，偏向用索引避免排序 |
mysql.server_cost | disk_temptable_create_cost / disk_temptable_row_cost、memory_temptable_create_cost / memory_temptable_row_cost | 内部临时表的代价 |
mysql.engine_cost | io_block_read_cost | 从磁盘读一个块的代价；调高它会让读大量块的计划（如全表扫描）更贵 |
mysql.engine_cost | memory_block_read_cost | 从缓冲池读一个块的代价；与上一项的大小关系会影响同一查询在不同缓存状态下的计划 |
这些常量的数值存在 mysql.server_cost 与 mysql.engine_cost 表的 default_value 列，可以直接查（本页未给出具体数值，取用时以 SELECT * FROM mysql.server_cost; 的结果为准）。改完 cost_value 要执行 FLUSH OPTIMIZER_COSTS 才生效，而且新代价只对之后新建的连接生效（来源：MySQL 8.0 Reference Manual, 10.9.5）。
优化器估算命中行数的方式，是索引下潜（index dive）：在 range 的两端各下潜一次索引，用两端之间的记录数作为该范围的估计。下潜得到的估计准确，但对大量比较值的 IN 或 OR 列表，逐值下潜的耗时会上升，于是优化器在比较值过多时改用一个索引统计近似值——切换阈值由 eq_range_index_dive_limit 控制，见 §5.2。
「区分度低到什么程度会转向全表扫描」没有官方给定阈值。一个流传较广的经验值是命中比例超过约 20%–30%（待验证；该比例随硬件、缓冲池命中率与行宽变化，不同版本、不同统计信息下并不一致，只能作为排查时的方向，不能当判据）。可靠的判据是 EXPLAIN 的输出：type=ALL、possible_keys 列出了你预期的索引、rows 估计值接近全表行数。
先分清两件事。「区分度低」既可能指列本身的基数低（如性别、状态这类取值很少的列），也可能指当前条件命中的行占比高（同一列在高选择性时，也可能因为条件太松而命中很多行）。两者都让索引筛掉的记录有限，但原因与对策不同：前者是列不适合单独建索引，可以与高区分度的列组成联合索引、把低区分度列放在后面；后者是条件太宽，考虑收窄条件或改用覆盖索引。优化器看的是「这个条件预计命中多少行」，而不是列的基数本身——同一份索引，status = 'paid' 命中九成行时会被放弃，status = 'failed' 命中百分之一时会被采用。若怀疑是统计信息陈旧导致估算失真，先执行 ANALYZE TABLE 更新索引统计，再复查 EXPLAIN（来源：MySQL 8.0 Reference Manual, 10.2.1.2）。
区分度低时，只加一个普通索引无法改变结果。可行的方向有几条：把筛选条件与常用返回列组成覆盖索引，让扫描不必回表，代价结构随之改变；用联合索引把高区分度的列放在前面，先缩小范围；对确实需要顺序扫描的批量任务，接受全表扫描，用其它手段（分批、限流）控制影响。需要验证某个索引是否真的会被选中时，可以用 FORCE INDEX 临时强制一次，对比 EXPLAIN 与耗时——若强制后反而更慢，说明优化器放弃索引是对的，问题在该查询的写法或数据分布。
3. count 的四种写法与 InnoDB 的计数困境
3.1 count 的语义
COUNT() 是聚合函数，参数可以是列名，也可以是任意表达式，作用是把结果集里参数不为 NULL 的记录数出来（来源：MySQL 8.0 Reference Manual, 14.19.1）。COUNT(*) 是特例：它统计检索到的行数，不管行里是否含 NULL（来源：MySQL 8.0 Reference Manual, 14.19.1）。COUNT(NULL) 返回 0；没有匹配行时 COUNT() 返回 0。
这带来两个容易混淆的结果：COUNT(name) 会把 name 为 NULL 的行排除掉，与 COUNT(*) 在存在 NULL 时数值不同；COUNT(1) 的参数是常量表达式 1，永远不为 NULL，所以等价于数行数。
3.2 四种写法的扫描差异
四种写法最终都要把符合条件的记录数一遍，差别在「扫哪份数据结构」与「每条记录要不要读列值、判 NULL」。
四种 count 写法各自扫什么（表上有二级索引时）
写法 扫描对象 读取列值 判定 NULL
─────────────────────────────────────────────────────────────
count(*) 最小二级索引 / 聚簇索引 否 否（计行数）
count(1) 最小二级索引 / 聚簇索引 否 否
count(主键) 最小二级索引 / 聚簇索引 是 是（主键非 NULL）
count(col) col 的二级索引或全表 是 是
└─ col 无索引 ⟹ 全表扫描（type=ALL）
索引记录比聚簇索引记录小 ⟹ 扫二级索引的 I/O 更省
└─ count(*) / count(1) / count(主键) 都优先挑最小的二级索引COUNT(*) 与 COUNT(1) 的执行方式官方明确为相同：InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.（来源：MySQL 8.0 Reference Manual, 14.19.1）。这条结论在 5.7 手册里也有，并不是 5.7 的旧说法。「count(1) 比 count(*) 快」的说法不成立。
COUNT(*) 扫哪份索引，官方描述是：InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.（来源：MySQL 8.0 Reference Manual, 14.19.1）。「最小的二级索引」指占用存储空间最小的那份——同样条数的索引记录，二级索引比聚簇索引小，遍历的页更少，I/O 成本更低，所以优化器优先挑它。若查询里 COUNT(expr) 的 expr 需要读取具体列值，优化器必须保证该列在所选索引里可取（必要时回表），选择的自由度随之下降。
COUNT(主键字段) 与 COUNT(1) 的差异，官方手册没有单独给出结论。可推断的机制是：COUNT(1) 的参数不含列，不需要读出列值；COUNT(主键) 要取出主键值并判断是否为 NULL（实际主键非 NULL，判定恒为真），多了一步取值动作（待验证：手册只保证了 COUNT(*) 与 COUNT(1) 等价，COUNT(主键) 的具体计划随表结构与索引组合变化，以 EXPLAIN 实测为准）。因此把 COUNT(主键) 当作与 COUNT(1) 完全等价、或当作明确更慢，都缺乏依据。
COUNT(col) 是四种里最贵的一种：若 col 上没有索引，只能全表扫描，边扫边读 col、判 NULL；即便 col 上有索引，也要读出列值并跳过 NULL。它的结果与 COUNT(*) 在列允许 NULL 时并不相同。若确实需要「该列非 NULL 的记录数」，正确做法是给该列建索引，让它能走索引扫描。
3.3 InnoDB 为什么不能维护行数
MyISAM 能 O(1) 返回无条件的 COUNT(*)：每张 MyISAM 表维护了一个 row_count 计数，由表级锁保证一致性，直接读出来即可（来源：MySQL 8.0 Reference Manual, 14.19.1）。这套做法依赖「全表只有一个权威行数」，而 InnoDB 的事务模型不满足这条前提。
InnoDB 用 MVCC 支持并发事务，同一时刻不同事务「能看到」的行集合可能不同——一个事务插入但尚未提交的行，对另一个事务不可见；不同隔离级别下可见性规则也不一样。既然「表里有多少行」本身取决于「哪个事务、什么时刻在问」，就不存在一个对所有事务都正确的 row_count 可维护。手册的表述是：InnoDB does not keep an internal count of rows in a table because concurrent transactions might "see" different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction.（来源：MySQL 8.0 Reference Manual, 14.19.1）。因此无条件的 COUNT(*) 也要沿某份索引实际数一遍。
带上 WHERE 之后，MyISAM 与 InnoDB 没有区别：两者都要扫描来统计满足条件的行数（来源：MySQL 8.0 Reference Manual, 14.19.1）。MyISAM 的 row_count 只对「无 WHERE、单表、不取其它列」的 COUNT(*) 生效。
3.4 8.0 的优化与大表计数的替代方案
MySQL 8.0.13 起，对没有 WHERE、GROUP BY 等额外子句的 SELECT COUNT(*) FROM tbl_name，InnoDB 做了针对单线程工作负载的优化（来源：MySQL 8.0 Reference Manual, 14.19.1）。它改变的仍是常数项——扫描是并行还是串行、读多少页，量级仍是全索引扫描。
真正让大表 COUNT(*) 变慢的是记录数与缓冲池：索引记录不必全在缓冲池里，扫不到的部分要落盘读，成本随表增大线性上升（来源：MySQL 8.0 Reference Manual, 14.19.1）。如果业务不需要精确值，或不能接受每次扫全索引，有三类替代方案，各自在精确度、性能与一致性之间取舍：
| 方案 | 精确度 | 性能 | 一致性代价 |
|---|---|---|---|
SHOW TABLE STATUS / information_schema.TABLES.TABLE_ROWS | 估算值 | 极快，读元数据 | InnoDB 的行数是估算，可能明显偏离真实值；ANALYZE TABLE 后更新 |
EXPLAIN 的 rows | 估算值 | 极快，不真正执行 | 同上，是优化器的估算 |
| 单独计数表，事务内 +1 / -1 | 精确 | 读快，写慢 | 计数表成为热点行；要与业务写同一事务才能准确，增加锁与写入放大 |
Redis 等外部计数器 INCR / DECR | 近似精确 | 读写都快 | 与 MySQL 不是同一事务，崩溃或重启会丢计数；MySQL 直连写入会绕过计数 |
在最小的二级索引上 COUNT(*) | 精确 | 随表线性变慢 | 无额外一致性代价，是默认方案 |
选择取决于对精确度的要求：搜索页展示的「约 N 条」用估算值足够；交易对账一类必须精确，就只能接受计数表的热点或全索引扫描的成本。计数表方案的一个常见坑是「读到的计数与实际行数不符」——若维护计数的写没有和业务写放在同一事务里，中途失败会留下偏差，而偏差本身很难被察觉。
4. 分页与深度分页
4.1 LIMIT offset, n 的两种执行路径
LIMIT offset, n 表示跳过前 offset 条、返回接下来的 n 条；LIMIT n 等价于 LIMIT 0, n。关键点在于「跳过」是怎样实现的：执行器从存储引擎逐条取记录，取满 offset + n 条后，把前 offset 条丢掉，只把最后 n 条返回给客户端。丢弃发生在取到之后，所以那 offset 条记录的读取代价已经付出。
LIMIT offset, n 的两条执行路径
路径 A：ORDER BY 主键（走聚簇索引）
沿主键有序向前读：
读第 1 行 ─▶ 读第 2 行 ─▶ … ─▶ 读第 offset+n 行
└─ 前 offset 行整行读出后被丢弃；最后 n 行返回客户端
代价 ∝ offset + n（每行都是完整行）
路径 B：ORDER BY 非主键（走二级索引）
沿二级索引有序向前读，每读一条回表取整行：
二级索引第 i 条 ─▶ 回表（聚簇索引）─▶ 整行
└─ offset 大时回表次数 = offset + n；
优化器估算后可能放弃二级索引，改全表扫描 + 排序
（EXPLAIN 中 type 由 range/index 变为 ALL）
两条路径的共同点：都要先产生并丢弃 offset 行
└─ 差别只在「丢弃时要不要付回表的代价」主键有序时（ORDER BY id），执行器沿聚簇索引正向扫，读到的行已经是目标顺序，offset 条整行读出后丢弃。二级索引有序时（ORDER BY user_name），执行器沿二级索引扫，每读一条要回表取整行，offset 大时回表次数同样达到 offset + n，其中绝大多数回表结果是浪费的。
LIMIT 还有一个方向相反的优化：当查询同时有 ORDER BY 与 LIMIT 时，优化器默认倾向于选一个能提供所需顺序的索引，避免 filesort（来源：MySQL 8.0 Reference Manual, 10.2.1.19）。这个行为由 optimizer_switch 的 prefer_ordering_index 控制（8.0.21 起可关闭），见 §5.1。
4.2 为什么 LIMIT 1000000, 10 慢
LIMIT 1000000, 10 与 LIMIT 10 都要返回 10 条，代价却差了几个数量级，原因就是上节的「先取后弃」：前者要从存储引擎取出 1000010 条，丢掉前 1000000 条。丢掉的这部分不做任何有用功，但每条记录的索引定位、回表、行拷贝都已经发生。
二级索引路径下还有一层雪上加霜：当 offset 很大、回表次数达到百万级时，优化器估算「逐条回表」的代价可能超过「顺序扫全表 + 排序」，于是放弃二级索引直接全表扫描。EXPLAIN 上表现为 type 从 range/index 变成 ALL。这类查询进入慢查询日志后会长期占据最慢的位置，调整方向只有减少 offset 的有效规模。
4.3 三种优化方案与各自边界
方案一：游标分页（keyset pagination）。 不传页码，改传「上一页最后一条记录的键」，把 OFFSET 换成 WHERE key > ?：
-- 第一页
select * from page order by id limit 10;
-- 之后每一页：把上一页最后一条的 id 作为起点
select * from page where id > 1000 order by id limit 10;它把「取 offset + n 条再丢弃」换成「从键处定位后读 n 条」，无论翻到第几页，扫描的记录数都只有 n，代价稳定。边界有两条：排序键必须单调、唯一，否则「上一页最后一条」不唯一会漏行或重复——排序键不唯一时要用 (order_key, id) 复合游标；不支持跳页，用户无法从第 1 页直接跳到第 106 页。它适合的是「只支持上一页 / 下一页」或无限滚动的场景，limit 1000000, 10 这种深偏移在它的模型里不会出现。
方案二：延迟关联 / 覆盖索引 + 回表。 先只用覆盖索引取出目标行的主键，再用主键关联回整行：
select * from page t1
join (select id from page order by user_name limit 6000000, 100) t2
on t1.id = t2.id;子查询只取主键，走在 user_name 的二级索引上，返回 Server 层时只拷贝 id 一列，offset 行的丢弃代价从「整行 + 回表」降到「只读索引项」。拿到 100 个 id 后，再用主键索引取整行（lg(n) 定位后顺序读 100 条）。它把回表从 offset 次降到 n 次，显著降低常数项，但没有消除 O(offset) 的索引项扫描——子查询仍要把前 6000000 条索引项读出来再丢弃。offset 很大时，这一项仍是主要开销。
方案三：按业务约束缩短分页深度。 搜索或筛选类页面本身不该提供无限深的翻页。可用的约束有几条：限制可返回的结果总数（如最多前 1000 条），超过后不再允许继续翻；用外部搜索引擎承接深层检索，并配合其自身的分页限制；把交互形态改成只支持上一页 / 下一页（瀑布流），从产品层面消除随机跳页，也就消除了深偏移。这几条都需要产品侧确认可接受，属于非技术手段。
三种方案对照：
| 方案 | 代价量级 | 支持跳页 | 适用条件 |
|---|---|---|---|
| 游标分页 | O(n)，与页深无关 | 否 | 排序键唯一且单调；能接受只能顺序翻页 |
| 延迟关联 / 覆盖索引 | 仍 O(offset)，常数项明显降低 | 是 | 存在可用的覆盖索引；能把 offset 控制在百万以内 |
| 按业务改需求 | 与页深无关 | 视产品而定 | 产品侧同意限制深度或改交互 |
4.4 这些优化的不适用边界
游标分页在需要「跳页」或「按页码定位」的后台列表里不能用；排序键可变（如按热度排序，热度会随时间变化）时，游标会错位，出现重复或漏读。延迟关联依赖「存在一份能覆盖子查询的索引」——子查询里取 id、ORDER BY user_name，就需要 (user_name, id) 这样的联合索引，只有 user_name 单列索引而需要回表取 id 时，优化效果打折。它也没有改变 O(offset) 的索引项扫描，offset 到了千万级仍不可接受。
把深层检索交给外部搜索引擎时，深分页问题会以另一种形式出现：搜索引擎同样有返回结果数的上限，超出上限后要么拒绝，要么用游标式接口继续取，一致性模型与 MySQL 不同。换句话说，换系统换来的是不同的边界，深分页背后「用户不可能翻到第 10 万页」这个业务判断，才是真正的解法。
回到第一页那段代码：select * from page order by id limit 0, 10 之所以快，是因为 offset = 0，没有需要丢弃的部分。这也解释了为什么同一张表上「首页秒开、末页超时」并不是索引坏了。
5. 参数与开关
下面三个参数都直接参与「索引选不选、区间怎么定位」的决策。每个按默认值、语义、什么时候改、改大或改小的后果、联动、失败模式六项写齐。
5.1 optimizer_switch
默认值：一组开关，与本篇相关的默认值都是 on——index_merge=on、index_merge_union=on、index_merge_sort_union=on、index_merge_intersection=on、index_condition_pushdown=on、skip_scan=on、prefer_ordering_index=on、use_index_extensions=on、mrr=on、mrr_cost_based=on（来源：MySQL 8.0 Reference Manual, 10.9.2、7.1.8）。作用域为全局与会话，动态可改。
语义：它是优化器各类可选优化的总开关集合，不用重启就能逐项控制。赋值用逗号分隔的命令列表，未提及的项保持原值：SET [GLOBAL|SESSION] optimizer_switch='index_merge=off,skip_scan=off'（来源：MySQL 8.0 Reference Manual, 10.9.2）。与本篇相关的几项分工如下：
| 开关 | 控制的行为 | 关掉后的现象 |
|---|---|---|
index_merge | 所有索引合并的总开关 | OR 两侧都有索引时也可能退化为全表扫描 |
index_merge_union / index_merge_sort_union / index_merge_intersection | 三种索引合并算法分别 | 关闭某一种后，OR/AND 只能走其它合并方式或全表扫描 |
index_condition_pushdown | ICP 条件下推 | Extra 里 Using index condition 消失，联合索引截断后的回表次数回升 |
skip_scan | 跳跃扫描 | 缺少首个索引列条件时不再做多段范围扫描，退回全表或全覆盖索引扫描 |
prefer_ordering_index | ORDER BY ... LIMIT 优先选有序索引 | 改为按区分度选索引，可能引入 filesort（8.0.21 起可关） |
use_index_extensions | 用主键列扩展二级索引 | 某些覆盖判断失效 |
什么时候改：排查某个查询计划时做对照实验，或作为临时规避手段。比如怀疑 index_merge 反而拖慢某个 OR 查询，会话内 SET optimizer_switch='index_merge=off' 再 EXPLAIN 对比；怀疑 skip_scan 选错了索引，关掉再看。
改大或改小的后果：这里的「改」都是开与关。关掉某项优化，优化器可选的计划集合变小，可能规避坏计划，也可能失去好计划。例如关掉 index_condition_pushdown，a=1 AND c=3 这类查询会重新把 c 的判断抬回 Server 层，回表次数增加；关掉 prefer_ordering_index，ORDER BY ... LIMIT 可能从「用有序索引」改成「用更省的索引 + filesort」，在小 LIMIT 时后者可能更慢。
联动：index_merge 是总开关，index_merge_* 是分项，只开分项而总开关为 off 时都不生效；手册给的做法是「若只想启用某一种，先把 index_merge 设为 off，再单独开对应的分项」（来源：MySQL 8.0 Reference Manual, 10.2.1.3）。skip_scan 与 range 优化器共享区间构造逻辑，关掉它不影响普通 range 扫描。prefer_ordering_index 只在有 ORDER BY 或 GROUP BY 且带 LIMIT 时才参与决策。
与优化器提示的关系。optimizer_switch 的粒度要么是整个会话、要么是整个实例，都偏粗——为一条查询关掉 index_merge，会顺带影响同一会话里其它所有查询。8.0 提供逐语句的优化器提示，作用范围只到该条语句：SELECT /*+ NO_INDEX_MERGE(t) */ ...、/*+ INDEX(t idx_name) */、/*+ SET_VAR(optimizer_switch='index_merge=off') */。手册对两者的取舍有明确倾向：优先用提示而非改 optimizer_switch，因为提示可以不与 SQL 文本混在一起、可以按语句注入、语义上是「提示」而非「指令」（来源：What's New in MySQL 8.0, MySQL Server Blog）。SET_VAR 提示只影响紧随其后的那一条语句，这一点在排查时比会话级 SET 更安全。
失败模式：把 optimizer_switch 的会话值误当全局值改动，会影响后续所有查询；赋值里出现未识别的项名会导致整条 SET 失败、值不变（来源：MySQL 8.0 Reference Manual, 10.9.2）。生产上建议用 SET SESSION 做实验、以优化器提示（/*+ INDEX_MERGE(...) */ 这类）做单条语句的定向控制，避免用全局改开关的方式做长期规避。
5.2 eq_range_index_dive_limit
默认值：200；类型整数，作用域全局与会话，动态可改，范围 0–4294967295（来源：MySQL 8.0 Reference Manual, 7.1.8）。
语义：它决定 col IN (v1, ..., vN) 或 col = v1 OR ... OR col = vN 这类多值等值比较，优化器用索引下潜还是用索引统计估算命中行数。表达式里有 N 个等值范围；当 eq_range_index_dive_limit 大于 0 时，只要等值范围数达到或超过 eq_range_index_dive_limit，优化器就改用已有的索引统计而不是逐个下潜（来源：MySQL 8.0 Reference Manual, 7.1.8、10.2.1.2）。下潜得到的行数估计准确但耗时，统计估计快但误差大。
什么时候改：默认 200 已经覆盖绝大多数 IN 列表。需要精确估计、且比较值数量可控时，可以把上限提高到 N + 1，让最多 N 个等值范围仍走下潜；把值设为 0 则禁用统计、任何数量的比较值都走下潜。
改大或改小的后果：调大（或设为 0）会让行数估计更准，代价是比较值多时优化器构造计划耗时增加；调小则更早切到统计，计划生成快，但可能因估算偏差选错访问方式（例如把实际高区分度的条件估成低区分度，退化为全表扫描）。
联动：与 ANALYZE TABLE 相关——改用统计估计后，估计质量取决于 ANALYZE TABLE 更新的统计信息，表数据变化大而长期未 ANALYZE 时统计偏差更明显。MySQL 8.0 还允许在满足一组条件时跳过下潜：单表查询、带单个 FORCE INDEX 提示、索引非唯一且非 FULLTEXT、无子查询、无 DISTINCT/GROUP BY/ORDER BY（来源：MySQL 8.0 Reference Manual, 10.2.1.2）。此时 EXPLAIN FOR CONNECTION 的 rows 与 filtered 会显示为 NULL。
失败模式：调到 0 后，IN 列表极大的查询（成千上万个值）会让解析与估算阶段明显变慢，最坏情况把优化时间推高到可与执行时间相比；设得过小则预估失真，表现为同一查询在数据量变化后计划突然改变。
5.3 range_optimizer_max_mem_size
默认值：8388608 字节，即 8 MB；类型整数，作用域全局与会话，动态可改，0 表示不限制（来源：MySQL 8.0 Reference Manual, 7.1.8）。
语义：它是 range 优化器可用的内存上限。优化器在考虑用 range 访问方式时会跟踪消耗的内存；若预计要超出这个上限，就放弃 range 方案，改而考虑其它方案，包括全表扫描（来源：MySQL 8.0 Reference Manual, 10.2.1.2）。放弃时会写入告警 3170：Memory capacity of N bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.。
什么时候改：出现告警 3170、且已确认该查询本应走 range（比如一个包含大量 OR 或超长 IN 列表的查询突然变成全表扫描）时，可以调大。手册给出的内存估算口径：OR 连接的谓词每个约 230 字节，AND 连接的谓词每个约 125 字节；IN 列表里的每个字面量按一个 OR 谓词计，两个 IN 列表则按两者字面量数之积计（来源：MySQL 8.0 Reference Manual, 10.2.1.2）。按此可以估算一个查询需要多少内存。
改大或改小的后果：调大让更多 range 方案能被保留，代价是优化阶段在极端查询上占用更多内存，可能影响并发优化时的整体内存；调小会让更多查询过早放弃 range，退化为全表扫描。
联动：与 sql_safe_updates 联动——对 UPDATE、DELETE，若优化器降级为全表扫描且开启了 sql_safe_updates，会报错而不是只告警，因为此时实际上没有键可用于确定要修改哪些行（来源：MySQL 8.0 Reference Manual, 10.2.1.2）。
失败模式：把上限设得过大，在 IN 列表极长、OR 嵌套极深的查询上，优化阶段的内存占用可能失控；设得过小的症状则是「索引存在、条件也能用索引，计划却是全表扫描」，此时先查告警日志里有没有 3170，再决定是否调大，而不是急着改 SQL。
5.4 版本演进（与本篇相关）
素材按 5.7 叙述，本篇涉及的机制有多个在 5.7→8.0 之间发生了变化，逐条列出：
| 机制 | 5.7 | 8.0 |
|---|---|---|
| 函数索引 | 无。对列用函数必然失效，只能改写 SQL | 8.0.13 起支持 functional key part，可对表达式结果建索引（来源：MySQL 8.0 Reference Manual, 15.1.15） |
| 索引跳跃扫描 | 无。缺少首个索引列条件时用不上联合索引 | 8.0.13 起支持 Skip Scan，受 skip_scan 开关控制（来源：MySQL 8.0 Reference Manual, 10.2.1.2） |
| 降序索引 | DESC 被解析但忽略，实际仍建升序索引，逆序靠反向扫描 | 8.0 起 DESC 生效，按降序物理存储，可正向扫描，也支持 ASC/DESC 混合的多列索引（来源：MySQL 8.0 Reference Manual, 10.3.13；What's New in MySQL 8.0, MySQL Server Blog） |
COUNT(*) | 沿最小可用二级索引遍历 | 8.0.13 起，无 WHERE/GROUP BY 的 SELECT COUNT(*) 针对单线程工作负载优化（来源：MySQL 8.0 Reference Manual, 14.19.1） |
EXPLAIN ANALYZE | 无 | 8.0.18 引入，运行语句并输出实际执行信息，固定 TREE 格式（来源：MySQL 8.0 Reference Manual, 15.8.2） |
prefer_ordering_index | 无对应开关，无法关闭该行为 | 8.0.21 起可通过 optimizer_switch 关闭（来源：MySQL 8.0 Reference Manual, 10.2.1.19） |
| 慢查询日志的额外字段 | log_slow_extra 不存在 | 8.0.14 起 log_slow_extra 可让日志写出 Read_*、Sort_* 等字段（来源：MySQL 8.0 Reference Manual, 7.4.5） |
ICP 引入更早（5.6，该版本号未从现行手册核对；5.7 与 8.0 都支持）。查询缓存是另一个旧版本特征，8.0 已移除——它属于执行流程，不在本篇范围。
6. 常见故障与排查
6.1 EXPLAIN 与 EXPLAIN ANALYZE 的读法对照
EXPLAIN 给出的是优化器的估算计划，不执行语句；EXPLAIN ANALYZE（8.0.18 起）会真正执行语句，输出 TREE 格式，并给出每个迭代器的实际耗时、实际返回行数与循环次数（来源：MySQL 8.0 Reference Manual, 15.8.2）。两者配合使用：先用 EXPLAIN 看计划骨架，拿不准估算是否偏离时再用 EXPLAIN ANALYZE 看实际。
EXPLAIN 里与本篇相关的列：
| 列 | 关注点 |
|---|---|
type | 访问方式，由好到差的常见序列：const / eq_ref / ref > range > index > ALL；index_merge 表示索引合并 |
possible_keys | 该表上与该查询相关的候选索引；为空说明条件列上没有可用索引 |
key | 实际选中的索引；possible_keys 非空而 key 为空，通常是优化器主动放弃（§2.7） |
rows | 优化器估算要扫描的行数，是估算值，不是实际值 |
filtered | 按条件过滤后预计剩下的行百分比 |
Extra | 见下表 |
Extra 里值得对照的几项：
Extra | 含义 | 对应本篇哪类 |
|---|---|---|
Using index | 覆盖索引，无需回表 | §1.2 |
Using index condition | ICP 生效，条件下推到引擎层过滤 | §2.5 |
Using where | Server 层还要再过滤 | 常见于未能完全下推的条件 |
Using index for skip scan | 走了跳跃扫描 | §2.5 |
Using filesort | 无法用索引满足 ORDER BY，另做排序 | §4.1 |
Using temporary | 用了内部临时表，常见于 GROUP BY 无法用索引 | — |
Backward index scan | 反向扫描索引（如建升序索引却要降序读） | §5.4 |
EXPLAIN ANALYZE 的输出把「估算」与「实际」并排：每个节点的 (cost=... rows=...) 是估算，(actual time=... rows=... loops=...) 是实际，时间单位为毫秒（来源：MySQL 8.0 Reference Manual, 15.8.2）。判读时把两处 rows 对比：若某个节点的实际行数远大于估算行数，说明统计信息或下潜估计偏离，优化器可能因此选错访问方式——这时先 ANALYZE TABLE 更新统计，再复查计划。
看到一条慢查询的排查顺序
EXPLAIN <sql>
├─ type=ALL 且 possible_keys 有候选
│ └─ 写法问题（函数 / 隐式转换 / 非最左）还是区分度低？
├─ type=ALL 且 possible_keys 为空
│ └─ 条件列上没有可用索引 → 考虑加索引或改条件
├─ type=range/ref 但 rows 与实际差很多
│ └─ 统计信息过期 → ANALYZE TABLE，或看 EXPLAIN ANALYZE 的实际 rows
└─ Extra 含 Using filesort / Using temporary
└─ 排序或分组没走索引 → 调整 ORDER BY / GROUP BY 与索引列序
└─ 仍不确定优化器为何不选索引 → 开 optimizer_trace 看候选与代价（§6.3）6.2 状态计数器与慢查询日志
SHOW STATUS LIKE 'Handler_read%' 给出的是一组按访问方式分类的读计数器，用来看「实际发生了多少次哪种读」（来源：MySQL 8.0 Reference Manual, 7.1.10）：
| 变量 | 含义 | 高值说明 |
|---|---|---|
Handler_read_key | 按键读一行的请求数 | 索引被有效利用，是正向信号 |
Handler_read_next | 按键顺序读下一行的请求数 | 范围查询或索引扫描在发生 |
Handler_read_prev | 按键顺序读上一行的请求数 | 用于优化 ORDER BY ... DESC |
Handler_read_first | 读取索引第一条目的次数 | 高值提示大量全索引扫描 |
Handler_read_rnd | 按固定位置读一行的请求数 | 排序多、或 JOIN 没用上键 |
Handler_read_rnd_next | 在数据文件中读下一行的请求数 | 高值提示大量全表扫描，索引不当或查询没利用索引 |
Handler_read_next 对应 range 或索引扫描，Handler_read_rnd_next 对应全表扫描，两者的比例是判断「到底有没有在用索引」的快速口径。计数器是累计值，要看增量——在一次查询前后各取一次，比较差值。
慢查询日志记录执行超过 long_query_time（默认 10 秒）的语句。log_queries_not_using_indexes 默认 OFF，开启后会把「预期要取回全部行」的查询也写进慢查询日志（来源：MySQL 8.0 Reference Manual, 7.1.8、7.4.5）。它的副作用有两层：一是「没用到索引」的判断口径比想象中宽——使用全索引扫描的查询也会被记录，因为它虽然用了索引，但索引没有减少扫描行数（来源：MySQL 8.0 Reference Manual, 7.1.8）；二是慢查询日志会因此快速增长，因为这类查询通常数量多、单条执行未必慢。手册明确指出 When queries that do not use an index are logged, the slow query log may grow quickly.，并给出限流手段：log_throttle_queries_not_using_indexes 默认 0（不限制），设为正值后按每分钟的条数上限抑制后续记录（来源：MySQL 8.0 Reference Manual, 7.4.5）。
排查大表计数与深度分页时，慢查询日志里还常能看到 Rows_examined 远大于 Rows_sent——前者是 Server 层检查过的行数，后者是返回客户端的行数，两者差距大意味着大量扫描被丢弃，正是 §4.1 那种「先取后弃」的形态。若开启 log_slow_extra（8.0.14 起），日志会额外写出 Read_rnd_next、Read_next 等按语句统计的值，可以直接看出这条语句发生了多少全表扫描式读取（来源：MySQL 8.0 Reference Manual, 7.4.5）。
6.3 用 optimizer_trace 确认优化器为什么不选索引
EXPLAIN 给出的是结论，看不到过程。要确认优化器为什么没选某个索引——它考虑了哪些候选、每个候选的代价是多少、为什么排序规则或转换导致某个条件被丢弃——用 optimizer_trace。
使用分三步（来源：MySQL 8.0 Reference Manual, 10.15.1）：
-- 1. 会话内开启跟踪
SET optimizer_trace = 'enabled=ON';
-- 2. 执行要调查的语句
SELECT * FROM t_user WHERE phone = 1300000001;
-- 3. 读取 trace
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
-- 用完关闭
SET optimizer_trace = 'enabled=OFF';OPTIMIZER_TRACE 表有四个列（来源：MySQL 8.0 Reference Manual, 28.3.19）：QUERY 是被跟踪语句的文本；TRACE 是 JSON 格式的跟踪内容；MISSING_BYTES_BEYOND_MAX_MEM_SIZE 在 trace 达到 optimizer_trace_max_mem_size（默认 1048576 字节，即 1 MB）上限而未能完整记录时，显示缺失的字节数，trace 不完整时这个值非 0；INSUFFICIENT_PRIVILEGES 在跟踪的查询涉及 SQL SECURITY DEFINER 的视图或存储程序、当前用户无权查看时取 1，否则为 0。
跟踪只能看到当前会话内执行的语句，不能查看其它会话的 trace；optimizer_trace_max_mem_size 默认 1 MB，复杂查询可能超出而拿到不完整的 trace；optimizer_trace_limit 默认 1，即只保留最近一条（来源：MySQL 8.0 Reference Manual, 7.1.8）。读 trace 时重点看两块：候选索引列表与各自的代价估算，以及条件被下推或丢弃的记录。隐式类型转换导致的失效，在 trace 里能看到相关条件因为类型转换无法作为索引区间使用；这也给出了一条比反复试 EXPLAIN 更直接的定位路径。
排查顺序可以固定成：先 EXPLAIN 定位到具体的表与访问方式 → 若 possible_keys 有候选却未被选中，用 EXPLAIN ANALYZE 核对估算与实际行数 → 仍不清楚原因时开 optimizer_trace 看候选与代价 → 对照 §2 的七类逐一排除写法问题。多数「索引失效」在这条链路的第二或第三步就能定位。
相关
- 00-MySQL 专栏导览 —— 本专栏的入口、边界与阅读顺序
- 03-索引与 B+ 树 —— 每一类失效都要回到它的有序性才有解释
- 01-MySQL 架构与执行流程 —— 优化器与执行计划在这条链路里的位置
参考
- MySQL 8.0 Reference Manual, 7.1.8 Server System Variables. https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
- MySQL 8.0 Reference Manual, 7.1.10 Server Status Variables. https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html
- MySQL 8.0 Reference Manual, 7.4.5 The Slow Query Log. https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html
- MySQL 8.0 Reference Manual, 10.2.1.2 Range Optimization. https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html
- MySQL 8.0 Reference Manual, 10.2.1.3 Index Merge Optimization. https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html
- MySQL 8.0 Reference Manual, 10.2.1.6 Index Condition Pushdown Optimization. https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html
- MySQL 8.0 Reference Manual, 10.2.1.19 LIMIT Query Optimization. https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html
- MySQL 8.0 Reference Manual, 10.3.13 Descending Indexes. https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html
- MySQL 8.0 Reference Manual, 10.9.2 Switchable Optimizations. https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html
- MySQL 8.0 Reference Manual, 10.9.5 The Optimizer Cost Model. https://dev.mysql.com/doc/refman/8.0/en/cost-model.html
- MySQL 8.0 Reference Manual, 10.15 Tracing the Optimizer. https://dev.mysql.com/doc/refman/8.0/en/optimizer-tracing.html
- MySQL 8.0 Reference Manual, 12.8.4 Collation Coercibility in Expressions. https://dev.mysql.com/doc/refman/8.0/en/charset-collation-coercibility.html
- MySQL 8.0 Reference Manual, 14.3 Type Conversion in Expression Evaluation. https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
- MySQL 8.0 Reference Manual, 14.19.1 Aggregate Function Descriptions. https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html
- MySQL 8.0 Reference Manual, 15.1.15 CREATE INDEX Statement. https://dev.mysql.com/doc/refman/8.0/en/create-index.html
- MySQL 8.0 Reference Manual, 15.8.2 EXPLAIN Statement. https://dev.mysql.com/doc/refman/8.0/en/explain.html
- MySQL 8.0 Reference Manual, 28.3.19 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table. https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimizer-trace-table.html
- What's New in MySQL 8.0. MySQL Server Blog. https://dev.mysql.com/blog-archive/whats-new-in-mysql-8-0-generally-available
YJ