MySQL回表

一、概述

回表是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語句、數據類型、緩存等多個方面入手。