MySQL回表

发布时间：2023-05-19

一、概述

回表是MySQL中的一个概念，指的是在使用索引进行查询后，需要回到原表中查找记录的情况。回表操作带来了额外的IO操作，影响查询性能，因此需要尽可能减少回表的次数。

二、回表的原因

回表的原因可以分为两种：

1. 需要查询的字段不在索引中

在MySQL中，如果使用索引查询一个表，那么MySQL会通过索引找到符合条件的记录，然后将这些记录的主键或者索引的值保存在一个临时表里面，再根据临时表里的主键或者索引的值，去原表中查询需要的字段。如果需要查询的字段不在索引中，就需要回到原表中查询。

-- 创建测试表
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据
INSERT INTO `test_table` VALUES (1,'Tom',18),(2,'Jerry',20),(3,'Lucy',19),(4,'Lily',21);
-- 使用索引查询
EXPLAIN SELECT id,name FROM test_table WHERE name='Tom';

上面的查询会使用name索引查询，但是需要查询的字段不在索引中，因此需要回到原表中查询，产生回表操作。

2. 需要查询的字段在索引中但是长度太大

在MySQL中，每种存储引擎对于索引字段的长度都有限制。如果需要查询的字段在索引中但是长度太大，就可能会导致索引不能完全覆盖查询的所有字段，从而需要回到原表中查询。

-- 创建测试表
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `desc` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `desc` (`desc`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据
INSERT INTO `test_table` VALUES (1,'Tom','This is Tom.'),(2,'Jerry','This is Jerry.'),(3,'Lucy','This is Lucy.'),(4,'Lily','This is Lily.');
-- 使用索引查询
EXPLAIN SELECT id,`desc` FROM test_table WHERE `desc` LIKE '%Tom%';

上面的查询会使用desc索引查询，但是desc字段的长度超过了100个字符的限制，因此需要回到原表中查询，产生回表操作。

三、减少回表的方法

为了减少回表操作，可以从以下几个方面入手：

1. 使用索引覆盖所有需要查询的字段

如果需要查询的字段在索引中，并且索引可以完全覆盖查询的所有字段，就不需要回到原表中查询了。

-- 创建测试表
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据
INSERT INTO `test_table` VALUES (1,'Tom',18),(2,'Jerry',20),(3,'Lucy',19),(4,'Lily',21);
-- 使用索引查询
EXPLAIN SELECT name,age FROM test_table WHERE name='Tom';

上面的查询会使用name_age索引查询，由于查询的所有字段都在索引中，因此不需要回到原表中查询，不会产生回表操作。

2. 避免使用SELECT *语句

在使用SELECT语句时，避免使用SELECT *语句，只查询需要的字段。这样可以减少回表的数据量。

-- 不使用SELECT *语句
EXPLAIN SELECT id,name FROM test_table WHERE name='Tom';

3. 优化数据类型

使用长度更小的数据类型可以减少索引和数据的存储空间，从而可以减少回表操作。比如可以使用TINYINT替代INT或者使用VARCHAR替代TEXT。

4. 合理使用索引

合理的索引设计可以减少回表操作，尽可能利用覆盖索引和前缀索引等技术。同时，需要注意不能为了减少回表操作而过度使用索引，导致索引失效。

5. 合理的使用缓存

在MySQL中，使用缓存可以减少回表操作。比如使用Memcached来缓存查询结果，可以避免相同的查询重复回表。同时需要注意缓存的更新策略，避免缓存和实际数据的不一致。

四、总结

回表是MySQL中的一个概念，指的是在使用索引进行查询后，需要回到原表中查找记录的情况。回表操作带来了额外的IO操作，影响查询性能，因此需要尽可能减少回表的次数。为了减少回表操作，可以从优化索引、SELECT语句、数据类型、缓存等多个方面入手。

推荐文章