① 数据访问方式 —— SQL 到底是怎么查数据的

对应第 86 ~ 90 讲 · 执行计划 type 字段的各种取值与底层执行路径

总览 ① 数据访问方式 ② 多表关联原理 ③ 成本优化 ④ 规则优化 ⑤ EXPLAIN 字段 ⑥ Extra 与调优 架构与实践篇 01~20 实战与高可用篇 109~130
基于索引树的查找（根节点二分查找 · 逐层跳转） 多索引合并 index_merge（特殊场景 · 一次 SQL 查多棵索引树） 访问方式性能阶梯 —— 看到哪一档，就知道快不快 映射为一种访问方式 可能合并多棵索引树 WHERE 里用到了索引列 等值 = · 范围 >= <= · IS NULL · 最左前缀 AND / OR 多条件且各有索引 where x1=xx and x2=xx · or 连接两个索引列 const 常量级 主键 / 唯一索引 等值查询 几次磁盘 IO 定位 · 性能极高 ref 普通二级索引等值 · 联合索引最左连续等值 主键/唯一索引 IS NULL 也是 ref ref_or_null 等值匹配 + IS NULL 一起查 在二级索引里搜值 + 搜 NULL · 再回表 range 范围查询 age >= x and age <= y · between 利用索引做范围筛选 怎么快速判别一种访问方式的快慢？ • 从索引树根节点二分查找、逐层跳转 → const / ref / range，性能极高 • 逐页遍历索引的叶子节点、没有二分查找 → index（二级索引）/ all（聚簇索引） • 共同忌讳：哪怕走索引，若一次性查出 10 万条数据，照样把 MySQL 搞死 • 回表：二级索引查到主键后回聚簇索引查完整数据，是多数访问方式的必经步骤 intersection 交集 and 条件：两棵索引树各查一波 按主键取交集 → 再统一回表 union 并集 or 条件：x1=xx or x2=xx 两棵索引树的结果合并 什么情况下才会一次查多棵索引树？ • 联合索引：索引里每个字段都必须出现在 SQL 中，且都是等值匹配 • 或者：主键查询 + 其他二级索引等值匹配 • 动机：查索引树快、回表慢 → 交集把回表量从上万条缩到几十条 查询优化器怎么在多个可用索引里取舍？ • 多条件都能走索引 → 选在索引里扫描行数少的那个条件对应的索引 • 整个 WHERE 里只有一个字段有索引 → 该字段走 ref，其余条件回表后在内存里过滤 • 索引设计目标：让等值条件在索引树里筛出来的数据量尽量少 第一梯队 · const / ref / range —— 基于索引树查找，性能极高 const 主键/唯一索引等值 · ref 二级索引等值 · range 索引范围筛选 第二梯队 · index —— 遍历二级索引的叶子节点（别误以为是二分查找！） 例：KEY(x1,x2,x3) + select x1,x2,x3 where x2=xxx → 查的列都在索引里，免回表（覆盖索引） 第三梯队 · all —— 全表扫描聚簇索引的叶子节点，一行一行扫 几百条数据无所谓 · 几万到几百万数据基本就得跪 · SQL 调优首先要消灭的对象 index 扫的是二级索引叶子（体积小，尚可接受）· all 扫的是聚簇索引叶子（完整数据行，最慢）—— index 再慢也比 all 快 图例 索引树查找（快） 多索引合并 / 第一梯队 索引扫描（中） 全表扫描（慢 · 要消灭） 判别要点 / 注解

const · ref · ref_or_null 精确边界

  • • const：主键或唯一二级索引的等值查询，常量级速度
  • • ref：普通二级索引等值；联合索引要求最左侧连续多列等值
  • • 主键 / 唯一索引写 IS NULL：只能算 ref（例外规则）
  • • 二级索引同时等值 + IS NULL：ref_or_null（搜值也搜 NULL）
  • • 看到 const / ref：底层一定是在某棵索引树上快速查找

index 访问方式的最大误区

  • • 以为 index = 走索引树二分查找？大错特错
  • • 真相：从叶子节点开始一个页一个页遍历二级索引
  • • 触发场景：where 用了非最左列，但 select 的列全在联合索引里
  • • 因为不用回聚簇索引，二级索引叶子又小 → 比全表扫描快
  • • 但本质是遍历，速度远不如 const / ref / range

index_merge 多索引合并速记

  • • and 条件 → 各查一棵索引树 → 按主键取交集再回表
  • • or 条件 → 各查一棵索引树 → 取并集
  • • 硬性条件：联合索引全字段等值，或主键 + 二级索引等值
  • • 交机能把上万条回表缩成几十条，此时优化器才划算
  • • 不是必然发生：优化器按成本决定用不用这招

① 数据访问方式 · MySQL 执行计划知识图谱 · 依据第 86 ~ 90 讲整理