前两天帮一个朋友模拟面试他简历上写着“熟悉 MySQL 调优”。我问了一句EXPLAIN 出来的 typeref 是什么意思他答得很快非唯一索引等值查询。然后我接着问那 eq_ref 呢和 ref 差在哪复合唯一索引的最左前缀查询type 会是 const 还是 ref他沉默了几秒我就知道这个知识点要补课了。ref 这个词很有意思它在 type 列里排在中间位置——比 range、index、ALL 好比 const、eq_ref 略差。很多面试者能背出这个顺序但说不清为什么。这篇文章把 ref 从头到尾拆干净从执行计划的含义到 BTree 的扫描原理再到真实优化案例和面试话术适合正在准备 MySQL 面试的人也适合平时用 EXPLAIN 优化慢查询但只停留在表面的开发者。1. ref 到底是个啥从执行计划的第一列说起1.1 type 列就是 MySQL 访问表的方式MySQL 执行一条 SELECT 之前优化器会生成一份执行计划EXPLAIN 就是把这计划摊开给你看的工具。很多初学的人盯着 select_type、table 这些列看半天但我一直认为 type 这一列才是执行计划的灵魂因为它直接告诉你MySQL 到底是用什么姿势从表里取数据的。type 的全称叫访问类型access type本质上描述的是存储引擎层访问数据的策略。你见过的大多数值可以按效率从高到低粗略排序system const eq_ref ref ref_or_null range index ALL。注意这个排序不是官方文档里的绝对顺序实操中不同场景会有细微差异但作为面试回答的大框架是没问题的。继续说 ref 的位置它卡在中间偏上的区域代表的是“用上了二级索引但是索引列不唯一等值匹配可能命中多行”的访问方式。换句话说ref 意味着 MySQL 确实没有闷头做全表扫描而是顺着索引去定位了一批行只是这批行可能不止一条。这个“可能不止一条”特别关键。很多人把 ref 简单理解成“走索引了”这不够。走索引有很多种走法point 查询、范围查询、索引全扫都是走索引但各自的开销和返回行数完全不是一个量级。ref 具体属于哪一种我在后面的章节里展开。1.2 ref 的官方定义与直观例子看 MySQL 官方手册对 ref 的注释原文大意是如果 join 操作只用到了索引的最左前缀或者用的是非唯一索引那么对于前一张表的每一行组合都会从这张表读取所有匹配索引值的行这种访问方式就叫 ref。拆开来看能把 type 判为 ref 的条件有三个用的是二级索引也就是非聚簇索引包括普通索引和唯一索引。查询条件是等值比较最常见的就是WHERE 索引列 某个值。如果用联合索引条件必须命中最左前缀如果索引本身有唯一性约束也必须只用到了左前缀而不是完整联合唯一键。举两个具体例子。假设有一张用户表user(id, name, age)其中name上有普通索引idx_name。执行EXPLAIN SELECT * FROM user WHERE name 张三大概率你会看到 typerefref 列对应 const意思是用一个常量去索引列上做等值匹配。再假设有一个联合唯一索引uk(a, b)执行WHERE a 1这时候 type 依然会是 ref而不是 const。为什么因为a1在索引里可能对应多条记录比如(1, 100)、(1, 200)唯一性保证不了行数是 1。这个细节如果面试能主动讲出来比背十句八股都有用。2. ref 和它的邻居们const、eq_ref、ref_or_null、range 的区别2.1 一张表对比四种访问方式面试里最常出现的连环追问就是让你把 ref 和前排的几位“邻居”做区分。我把它们放在同一张表里对比这样信息密度最高看起来一目了然。访问类型典型场景索引要求可能返回行数常见 SQL 形态const主键或完整唯一索引等值匹配唯一索引全部列最多 1 行WHERE id 1eq_ref联表查询时被驱动表走主键/唯一索引唯一索引完整匹配每次最多 1 行JOIN ... ON t2.id t1.uidref普通二级索引等值匹配或唯一索引左前缀二级索引/最左前缀可能多行WHERE name 张三ref_or_nullref 基础上额外查 NULL二级索引可能多行WHERE name 张三 OR name IS NULLrange索引列范围比较二级索引范围区间行WHERE age BETWEEN 20 AND 30这张表可以直接背但背完还得理解背后的逻辑。const 之所以叫“常量”是因为优化器在做执行计划之前就能确定最多只返回一行这行数据甚至可以被当成常量直接嵌入计划里代价低到可以忽略。eq_ref 是 join 语境里的 const它要求被驱动表的连接字段是唯一索引这样驱动表每给一行被驱动表最多回一行不会产生行数放大。而 ref 不保证行数唯一所以优化器对它的代价估算要比 const 和 eq_ref 高一截。这也解释了为什么 type 排序里 ref 排在它们后面它需要沿着索引扫描到一个“连续区间”区间里有几条命中的索引条目就得处理几条。2.2 面试官最爱挖的两个坑唯一索引左前缀和 eq_ref 的归属第一个坑就是我前面提到的复合唯一索引。很多候选人背了“const 是唯一索引等值查询”一到实际场景就翻车UNIQUE INDEX uk(a, b)然后 SQL 写WHERE a 1他们脱口而出 type 是 const。错就错在没用“完整唯一索引”这五个字。官方对 const 的定义明确写的是“最多返回一行”而a1在组合唯一索引里可能有 (1, x)、(1, y) 多行虽然索引本身唯一但查询条件没有锁死所有组成列行数唯一性就没了。第二个坑是 eq_ref 和 ref 的归属。有一个我常听到的错误答案是“eq_ref 是等值引用ref 也是等值引用两者区别不大”。实际上 eq_ref 几乎只在多表 join 中被驱动表的位置出现它强制要求连接列是主键或完整唯一索引并且连接条件走的是索引列的全部列。举个例子SELECT * FROM orders o JOIN users u ON o.user_id u.id如果orders是驱动表users通过主键id被查找那么对 users 的访问类型就是 eq_ref。你几乎不会在单表单条 SQL 里看到 eq_ref这一点能帮你在面试时快速判断访问类型的实际含义。ref 和 eq_ref 的底层差异也决定了优化器行为eq_ref 每次从驱动表拿到一行去被驱动表做一次点查最多返回一行代价近似 O(N)ref 从驱动表每拿到一行去被驱动表的二级索引上可能取回多条如果被驱动表索引选择性不好代价可能退化成接近 O(N*M)。这也是为什么有时候 MySQL 会宁愿改走全表扫描而不选 ref后面实战部分我会专门演示。3. 为什么二级索引等值匹配就是 refBTree 扫描区间分析3.1 从聚簇索引和二级索引说起要理解 ref 为什么是现在这个样子得先回到 InnoDB 的索引结构。InnoDB 表默认按主键聚簇主键索引的叶子节点直接存整行数据这叫聚簇索引。你手动在其它列上建的索引叫二级索引二级索引的叶子节点不存整行只存“索引列的值 主键值”。当你通过二级索引找数据流程是先在二级索引的 BTree 里定位到目标索引条目拿到主键然后再回聚簇索引查完整行这一步就是常说的回表。这个设计能带来很多好处但也有代价二级索引本身是一棵独立的 BTree索引列有序排列而回表是额外的一次随机 IO。ref 这个访问类型就是在这种结构下产生的标准动作——通过二级索引先定位再看是否需要回表。这里有个很容易被忽略的点二级索引的叶子节点之间有链表连接并且按索引列值顺序排列。所有索引查找的本质都可以抽象成“在有序数组里划一个或多个区间然后顺序读取区间内的叶子节点”。ref 对应的区间正好是“索引列值等于某个常量的所有叶子节点”也就是一个准确定值区间。区间内有多少条目不取决于 SQL 怎么写而取决于这个索引列在表里有多少重复值。3.2 等值匹配如何在 BTree 上形成连续扫描区间很多文章讲 BTree讲得最多的是“矮胖树、减少磁盘 IO”却很少讲清楚“扫描区间”这个概念。我第一次彻底理解 ref是因为看了一个调优案例一张表 1000 万行name索引区分度很低等值查一个名字能命中 20 万行。执行计划里 typerefrows 显示 20 万。当时我就意识到ref 并不是某些人想象中的“点查”它的效率完全取决于区间长度。过程是这样的优化器把WHERE name张三转换成对二级索引的一个区间扫描起始位置是索引树中第一个等于‘张三’的叶子节点终止位置是最后一个等于‘张三’的叶子节点。BTree 的等值查找从根节点开始逐层下探每一层通过二分比较最终定位到起始叶子然后顺着叶子节点的链表向后遍历直到遇到的索引值不再是‘张三’为止。命中的每一条叶子记录里存的是主键值再用这个主键值回表查整行。这个机制的言外之意是只要叶子节点上命中的条数变多扫描时间就线性增长回表次数也线性增长。所以同样显示 typerefrows1的 SQL 和rows200000的 SQL实际性能天差地别。面试时如果能主动说出“ref 的执行时间主要取决于匹配行数和回表成本”面试官就会觉得你是真用 EXPLAIN 排过障的人而不是只会背概念。3.3 ref 和覆盖索引、ICP 的组合含义typeref 只告诉你 MySQL 用二级索引做了定位但没告诉你回没回表。回没回表要看 Extra 这一列。这是我观察到的另一个高频误区很多人以为 typeref 就代表“性能很好不用回表”。实际上 ref 和回表是两个维度的事情。如果 SQL 只 SELECT 索引列本身比如SELECT name FROM user WHERE name张三二级索引叶子节点上的数据已经足够返回结果不需要回表Extra 会显示Using index。这是最理想的 ref。如果 SELECT 了索引以外的列比如SELECT *那必须回表取整行Extra 不显示Using index。如果 WHERE 里除了等值匹配条件还有额外的索引列过滤条件比如联合索引 (name, age) 上执行WHERE name张三 AND age20MySQL 5.6 以后可以把 age 的条件判断下推到存储引擎层先索引扫出来再过滤减少回表次数Extra 会显示Using index condition也就是 ICP索引条件下推。这三个组合能回答一个很常见的面试连环问“同样是 ref为什么有的 SQL 快有的 SQL 慢”答案就在 Extra 列里。Using index完全不回表Using index condition部分过滤后再回表什么都没有就要老老实实逐条回表。这几个状态想清楚了你对 ref 的理解深度已经超过 80% 的候选人。4. 实战演示把一条 typeALL 的慢 SQL 优化到 typeref4.1 建表、造数、一条慢查询讲了这么多理论现在落地跑一遍。我用一个典型场景用户表收集 user_id、name、age、email 四个字段模拟一百多万行数据。没有任何索引的原始状态执行计划通常会让人绝望。CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT NOT NULL, email VARCHAR(100) NOT NULL ) ENGINEInnoDB; -- 批量插入若干条数据这里用存储过程造一百万行 -- 简单示意实际生产用数据生成工具或脚本执行下面这条查询EXPLAIN SELECT * FROM user WHERE name 赵四;在没有索引时type 列会是 ALLkey 列是 NULLrows 接近全表行数Extra 显示Using where。ALL 表示这条 SQL 要把聚簇索引的叶子节点从头到尾扫一遍这是最坏的情况。Using where也说明过滤是在存储引擎把所有数据吐出来之后、在 server 层做的意味着大量无用数据被白白读了一遍。这个场景在真实生产环境里太常见了用户表越来越大查询条件写得没问题就是没建索引于是一条按 name 查用户信息的简单语句硬生生把数据库 CPU 打到 100%。很多初级 DBA 第一反应是“加内存、加缓存”实际上你 EXPLAIN 一下加一个索引就能解决。4.2 加索引前后 EXPLAIN 对比给 name 字段建一个普通二级索引注意不是唯一索引因为我们允许同名用户存在ALTER TABLE user ADD INDEX idx_name(name);再次执行 EXPLAINEXPLAIN SELECT * FROM user WHERE name 赵四;这时候 type 变成 refkey 显示 idx_namekey_len 是 varchar(50) 在 utf8mb4 字符集下对应的字节长度ref 列显示 constrows 从一百多万掉到个位数。整条 SQL 的扫描范围从全表变成“索引等值区间”性能提升是数量级的。再看一个覆盖索引的例子EXPLAIN SELECT name, age FROM user WHERE name 赵四;如果我把索引改成联合索引 (name, age)那么这条查询的 type 依然是 ref但 Extra 会多一个Using index表示所有需要的列都在索引里不需要回表。这个优化在实际项目里特别实用因为它省掉的不是一两次 IO而是每一行命中的随机回表 IO。4.3 为什么有时候加了索引 type 还是 ALL选择性陷阱这里有个特别值得拿出来讲的实战经验加了索引type 也不一定变 ref。如果一个索引列的重复值太多MySQL 优化器会自己算一笔账通过二级索引定位到海量主键再逐条回表成本和直接全表扫描差不多甚至更高于是它宁可走 ALL 也不走 ref。拿我刚才那张表举例如果里面只有 10 个不同的 name每个 name 对应十万行那么WHERE name赵四虽然能用 idx_name 定位但要回表十万次InnoDB 大概会认为全表扫描更快。这时候你在 EXPLAIN 里看到的 type 可能还是 ref也可能变成 ALL具体取决于优化器版本和统计信息但 rows 会告诉你真实情况。这种场景怎么救核心思路不是强扭优化器而是把索引做成覆盖索引。如果你把 SQL 改成SELECT name, age FROM user WHERE name赵四并且索引是 (name, age)所有数据都在索引里不需要回表那么即使某个 name 有十万行也是顺序扫索引叶子节点成本可控。这就是为什么我一直建议想要 ref 效果稳定优先考虑把高频查询里的字段塞进联合索引做成覆盖索引而不是指望一个单列索引包打天下。5. 面试追问轰炸区ref 相关的五个深坑5.1 最左前缀原则下 ref 的边界联合索引 (a, b, c)查询条件WHERE b1 AND c2能不能用上 ref答案是大概率不能。二级索引先按 a 排序a 相同再按 bb 相同再按 c。跳过了 a 直接等值匹配 b索引本身的有序性发挥不出来优化器只能走 index 全索引扫描或者 ALL。这是最左前缀原则的基本盘。再往深问一层WHERE a1 AND c2会是什么 typea 能命中索引左前缀typerefc 无法直接参与索引定位但它属于索引列可以在索引内部做过滤MySQL 会视情况启用 ICP在扫描 (a1) 区间的过程中过滤 c2。所以这条 SQL 的 type 可能依然是 ref但 Extra 里会出现Using index condition字样。能把这个细节讲清楚才是真的理解最左前缀和 ref 的关系。5.2 索引列上动手脚ref 秒变 ALL这是实战里最常见的“意外情况”。索引列被函数包裹或者发生隐式类型转换都会导致索引失效。比如 name 上有索引但写的是WHERE UPPER(name)ZHANGSANMySQL 无法直接使用 name 的 BTree 有序结构去定位因为索引里存的是原始值而不是函数结果type 直接退化。再比如 phone 字段是 varchar但查询写WHERE phone 13800138000这里的 13800138000 会被当成数字类型。MySQL 为了比较会把 phone 字段转型成数字一旦对列本身做隐式转换索引定位就用不了了。刷面试题的时候很多候选人能答出这条规律但问“为什么”就卡住。其实原因很简单BTree 的有序性依赖原始列值的排列任何对列的加工都会破坏这个排列索引自然就废了。5.3 ref_or_null、ORDER BY、NULL 对 ref 的影响有一种特殊的 ref 变体叫 ref_or_null出现条件是WHERE key abc OR key IS NULL。普通等值匹配只需要扫索引里等于‘abc’的区间但加上 IS NULL 之后MySQL 还得另外把值为 NULL 的索引条目也扫一遍。Extra 里通常能看到Using wheretype 显示 ref_or_null。面试时能补一句“ref_or_null 比 ref 多一次 NULL 扫描”说明你读过官方文档。ORDER BY 对 ref 也有影响。比如SELECT * FROM user WHERE name张三 ORDER BY age如果 name 上有索引但 age 没有MySQL 拿到所有匹配行后要额外做一次 filesort但如果联合索引是 (name, age)排序就可以直接利用索引顺序避免额外排序。常见面试追问是“type 显示 ref 时ORDER BY 能一定避免 filesort 吗”答案是不能必须看排序字段是否包含在同一个索引中。NULL 本身比较特殊。如果索引列允许 NULL那么等值条件WHERE name张三不会匹配到 NULL 行。NULL 行只能靠 IS NULL 查出来。这也解释了为什么很多时候 DBA 建议把索引列设置为 NOT NULL一方面是避免语义混淆另一方面是减少索引扫描的额外分支。6. 答题话术把 ref 答出层次感6.1 三句话及格版如果面试官只给了你三十秒你可以这样答ref 是 MySQL 执行计划里 type 列的一种访问类型代表查询用到了非唯一二级索引或者唯一索引的最左前缀做等值匹配匹配结果可能返回多行。它比 range、index、ALL 好因为至少走索引定位但比 const、eq_ref 差因为不保证只返回一行可能需要回表。这个回答能拿到及格分因为概念准确还点出了它在访问类型谱系里的位置。但如果面试官想深挖这个答案撑不了太久因为没解释为什么也没展示你对执行计划细节的熟悉程度。6.2 带原理的加分版如果能多给一分钟我会推荐这样答访问类型里的 ref 本质上是二级索引等值匹配。InnoDB 的二级索引是一棵独立的 BTree叶子节点按索引列有序排列并且存了主键值等值条件会被优化器转换成一个扫描区间MySQL 从索引根节点定位到区间的起始叶子再沿叶子链表顺序扫描到值变化为止。这个区间里有多少条记录取决于索引列的选择性。如果再往深处看ref 和回表是两个维度的问题SELECT 语句只取索引列时Extra 会出现 Using index不用回表如果取索引列以外的字段就要回表聚簇索引。联合索引情况下还可以配合 ICP把部分过滤条件下推到存储引擎层。最左前缀原则也在这里生效跳过联合索引最左侧列通常就没法形成 ref。这段话含金量很高因为它把访问类型的含义、数据结构的支撑、实际执行的IO路径、相关优化特性全部串起来了。面试官想继续问也只能往更深的方向问但你已经证明了自己不是背题人。6.3 被追问时的应对逻辑面试中问完 ref大概率会继续追 const、eq_ref、range。我的建议是不要背定义而是记一条主线type 的排序本质上是“定位一条数据的成本从低到高”。const 是唯一匹配一行eq_ref 是 join 里唯一匹配一行ref 是二级索引匹配多行range 是一个区间index 是扫整个索引ALL 是扫全表。所有追问都是围绕这条主线展开的。如果被问到“为什么 typeref 了还是很慢”不要慌从三个角度排查回表次数是不是太多也就是索引选择性好不好Extra 列是不是没有 Using index说明每条命中的记录都额外回表了ORDER BY 或 GROUP BY 有没有造成 filesort。这三个点说完基本就把一个实际调优问题答全了。我在实际面试里见过不少人能准确说出 ref 的定义但一谈到真实 SQL 调优就露馅。原因在于只背了概念没有把执行计划、索引结构、回表成本这条链路打通。希望这篇解析能帮你把 ref 这个点真正钉在脑子里下次无论是面试还是排查慢查询都能自信地跟人聊出深度来。