MySQL的ref有什么用

共计 3628 个字符，预计需要花费 10 分钟才能阅读完成。

这篇文章主要介绍“MySQL 的 ref 有什么用”，在日常操作中，相信很多人在 MySQL 的 ref 有什么用问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL 的 ref 有什么用”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

  回忆一下查询成本

对于一个查询来说，有时候可以通过不同的索引或者全表扫描来执行它，MySQL 优化器会通过事先生成的统计数据，或者少量访问 B + 树索引的方式来分析使用各个索引时都需要扫描多少条记录，然后计算使用不同索引的查询成本，最后选择成本最低的那个来执行查询。

创建场景

假如我们现在有一个表 t，它的表结构如下所示：

CREATE TABLE t ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, key1 VARCHAR(100), common_field VARCHAR(100), INDEX idx_key1 (key1) ) ENGINE=InnoDB CHARSET=utf8;

这个表包含 3 个列：

id 列是自增主键

key1 列用于存储字符串，我们为 key1 列建立了一个普通的二级索引

common_field 列用于存储字符串

现在该表中共有 10000 条记录：

mysql  SELECT COUNT(*) FROM t; +----------+ | COUNT(*) | +----------+ | 10000 | +----------+ 1 row in set (2.65 sec)

其中 key1 列为 a 的记录有 2310 条：

mysql  SELECT COUNT(*) FROM t WHERE key1 =  a  +----------+ | COUNT(*) | +----------+ | 2310 | +----------+ 1 row in set (0.83 sec)

key1 列在 a 到 i 之间的记录也有 2310 条：

mysql  SELECT COUNT(*) FROM t WHERE key1    a  AND key1    i  +----------+ | COUNT(*) | +----------+ | 2310 | +----------+ 1 row in set (1.31 sec)

现在我们有如下两个查询：

查询 1：SELECT * FROM t WHERE key1 =  a   查询 2：SELECT * FROM t WHERE key1    a  AND key1    i

按理说上边两个查询需要扫描的记录数量是一样的，MySQL 查询优化器对待它们的态度也应该是一样的，也就是要么都使用二级索引 idx_key1 执行它们，要么都使用全表扫描的方式来执行它们。不过现实是貌似查询优化器更喜欢查询 1，而比较讨厌查询 2。查询 1 的执行计划如下所示：

#  查询 1 的执行计划  mysql  EXPLAIN SELECT * FROM t WHERE key1 =  a \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref possible_keys: idx_key1 key: idx_key1 key_len: 303 ref: const rows: 2310 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.04 sec)

查询 2 的执行计划如下所示：

#  查询 2 的执行计划  mysql  EXPLAIN SELECT * FROM t WHERE key1    a  AND key1    i \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: ALL possible_keys: idx_key1 key: NULL key_len: NULL ref: NULL rows: 9912 filtered: 23.31 Extra: Using where 1 row in set, 1 warning (0.03 sec)

很显然，查询优化器决定使用 idx_key1 二级索引执行查询 1，而使用全表扫描来执行查询 2。

为什么? 凭什么? 同样是扫描相同数量的记录，凭什么我 range 访问方法就要比你 ref 低一头? 设计 MySQL 的大叔，你为何这么偏心 …

解密偏心原因

世界上没有无缘无故的爱，也没有无缘无故的恨。这事儿还得从索引结构说起。比方说 idx_key1 二级索引结构长这样：

原谅我们把索引对应的 B + 树结构弄了一个极度精简版，我们忽略掉了页的结构，只保留了叶子节点的记录。虽然极度精简，但是我们还是保留了一个极其重要的特性：B+ 树叶子节点中的记录是按照索引列的值从小到大排序的。对于二级索引 idx_key1 来说：

二级索引叶子节点的记录只保留 key1 列和 id 列

二级索引记录是先按照 key1 列的值从小到大的顺序进行排序的。

如果 key1 列的值相同，则按照主键值，也就是 id 列的值从小到大的顺序进行排序。

也就是说，对于所有 key1 值为 a 的二级索引记录来说，它们都是按照 id 列的值进行排序的。对于查询 1：

查询 1: SELECT * FROM t WHERE key1 =  a

由于查询列表是 *  ，也就是说我们需要通过读取到的二级索引记录的 id 值执行回表操作，到聚簇索引中找到完整的用户记录 (为了去获取 common_field 列的值) 后才可以将记录发送到客户端。对于所有 key1 列值等于 a 的二级索引记录，由于它们是按照 id 列的值排序的，所以：

前一次回表的 id 值所属的聚簇索引记录和下一次回表的 id 值所属的聚簇索引记录很大可能在同一个数据页中

即使前一次回表的 id 值所属的聚簇索引记录和下一次回表的 id 值所属的聚簇索引记录不在同一个数据页中，由于回表的 id 值是递增的，所以我们很大可能通过顺序 I / O 的方式找到下一个数据页，也就是说这个过程中很大可能不需要很大幅度的移动磁头就可以找到下一个数据页。这可以减少很多随机 I / O 带来的性能开销。

综上所述，执行语句 1 时，回表操作带来的性能开销较小。

而对于查询 2 来说：

查询 2: SELECT * FROM t WHERE key1    a  AND key1    i

由于需要扫描的二级索引记录对应的 id 值是无序的，所以执行回表操作时，需要访问的聚簇索引记录所在的数据页很大可能就是无序的，这样会造成很多随机 I /O。所以如果使用 idx_key1 来执行查询 1 和查询 2，执行查询 1 的成本很显然会比查询 2 低，这也是设计 MySQL 的大叔更钟情于 ref 而不是 range 的原因。

MySQL 的内部实现

MySQL 优化器在计算回表的成本时，在使用二级索引执行查询并且需要回表的情境下，对于 ref 和 range 是很明显的区别对待的：

对于 range 来说，需要扫描多少条二级索引记录，就相当于需要访问多少个页面。每访问一个页面，回表的 I / O 成本就加 1。

比方对于查询 2 来说，需要回表的记录数是 2310，因为回表操作而计算的 I / O 成本就是 2310。

对于 ref 来说，回表开销带来的 I / O 成本存在天花板，也就是定义了一个上限值：

double worst_seeks;

这个上限值的取值是从下边两个值中取较小的那个：

比方对于查询 1 来说，回表的记录数是 2310，按理说计算因回表操作带来的 I / O 成本也应该是 2310。但是由于对于 ref 访问方法，计算回表操作时带来的 I / O 成本时存在天花板，会从全表记录的十分之一 (也就是 9912/10=991，9912 为估计值) 以及聚簇索引所占页面的 3 倍 (本例中聚簇索引占用的页面数就是 97，乘以 3 就是 291) 选择更小的那个，本例中也就是 291。

全表记录数的十分之一(此处的全表记录数属于统计数据，是一个估计值)

聚簇索引所占页面的 3 倍

小贴士：在成本分析的代码中，range 和 index、all 是被分到一类里的，ref 是亲儿子，单独分析了一波。不过我们也可以看到，设计 MySQL 的大叔在计算 range 访问方法的代价时，直接认为每次回表都需要进行一次页面 I /O，这是十分粗暴的，何况我们的实际聚簇索引总共才 97 个页面，它却将回表成本计算为 2310，这也是很不精确的。

到此，关于“MySQL 的 ref 有什么用”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！