21 mysql ref 查询

前言

这里主要是探究一下 explain $sql 中各个 type

诸如 const, ref, range, index, all 的查询的影响, 以及一个初步的效率的判断

这里会调试源码来看一下各个类型的查询需要 lookUp 的记录

以及相关的差异

此系列文章建议从 mysql const 查询开始看

测试表结构信息如下


 
CREATE TABLE `tz_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `field1` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3333343 DEFAULT CHARSET=utf8

测试数据为序列 1 – 99

ref 查询存在的记录

更新表结构, 增加 field1 的索引配置


CREATE TABLE `tz_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `field1` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `field1` (`field1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3333343 DEFAULT CHARSET=utf8

执行更新, 更新一部分记录的 field1 为 ”field33”

update tz_test set field1 = 'field33' where id in (33, 35, 60);

mysql 读取索引, 这个是在读取索引的数据, 然后和查询条件进行对比

索引记录存放了原字段的值 -> 记录的主键

这里获取到第一个匹配的索引记录

up_rec 索引记录信息如下, 为 ‘field33’ -> 33

然后这里将 pcur->btr_cur->page_cur.rec 更新为 ‘field33’ -> 33 对应的索引的位置

然后接下来就是读取索引的记录

然后是比较索引字段的信息和查询条件, 如果匹配上才获取对应的记录

是否需要查询真实记录?

这里会有两种情况, 一种情况是查询的索引字段以及主键, 不需要额外的查询真实记录, 术语称之为覆盖索引

假设是普通字段, 这里更新 need_to_access_clustered 为 TRUE

另外一种是需要根据主键关联查询到真实的记录, 术语称之为回表

修改数据表结构如下


CREATE TABLE `tz_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `field1` varchar(12) DEFAULT NULL,
  `field2` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `field1` (`field1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8
 
update tz_test set field2 = id;