按 Enter 键跳转到正文

Mysql中的回表

发布于 2024年4月23日，作者 Leanku

MYSQL中的回（Back to Table）表说明

一、 基本概念

回表是指当使用非聚簇索引（二级索引）进行查询时，首先在索引中查找到所需数据的主键值，然后再根据这个主键值回到主键索引（聚簇索引）中查找完整数据行的过程。

二、核心原理

聚簇索引 vs 非聚簇索引

 1-- 创建测试表
 2CREATE TABLE user_info (
 3    id INT PRIMARY KEY,           -- 主键，聚簇索引
 4    name VARCHAR(50),             
 5    email VARCHAR(100),
 6    age INT,
 7    created_at DATETIME,
 8    INDEX idx_email (email),      -- 非聚簇索引（二级索引）
 9    INDEX idx_name_age (name, age) -- 复合非聚簇索引
10);

索引结构对比

聚簇索引（主键索引）结构：

1索引节点 -> 叶子节点包含完整数据行
2[id:1] -> [id:1, name:'张三', email:'zhang@xx.com', age:25, ...]
3[id:2] -> [id:2, name:'李四', email:'li@xx.com', age:30, ...]

非聚簇索引（二级索引）结构：

1索引节点 -> 叶子节点只包含索引列 + 主键值
2[email:'li@xx.com'] -> [主键id:2]
3[email:'zhang@xx.com'] -> [主键id:1]

三、 回表现象详解

示例1：基本的回表查询

1-- 这个查询会发生回表
2EXPLAIN SELECT * FROM user_info WHERE email = 'zhang@xx.com';

查询过程：

  1. 在 idx_email 索引中查找 email = ‘zhang@xx.com’
  2. 找到对应的主键值 id = 1
  3. 使用 id = 1 回到聚簇索引中查找完整数据行
  4. 返回所有列的数据

EXPLAIN 结果：

1type: ref
2key: idx_email
3Extra: Using index condition

示例2：覆盖索引避免回表

1-- 这个查询不会回表（覆盖索引）
2EXPLAIN SELECT id, email FROM user_info WHERE email = 'zhang@xx.com';

查询过程：

  1. 在 idx_email 索引中查找 email = ‘zhang@xx.com’
  2. 索引中已经包含所需的 id 和 email 列
  3. 直接返回结果，无需回表

EXPLAIN 结果：

1type: ref
2key: idx_email
3Extra: Using index  ← 这个表示使用了覆盖索引

四、 实际案例演示

准备测试数据

 1-- 创建详细的测试表
 2CREATE TABLE employee (
 3    emp_id INT PRIMARY KEY AUTO_INCREMENT,
 4    emp_name VARCHAR(50) NOT NULL,
 5    department VARCHAR(50),
 6    salary DECIMAL(10,2),
 7    hire_date DATE,
 8    email VARCHAR(100),
 9    phone VARCHAR(20),
10    INDEX idx_department (department),
11    INDEX idx_email (email),
12    INDEX idx_hire_date (hire_date),
13    INDEX idx_department_salary (department, salary)
14);
15
16-- 插入测试数据
17INSERT INTO employee (emp_name, department, salary, hire_date, email, phone) VALUES
18('张三', '技术部', 15000, '2020-01-15', 'zhang@company.com', '13800138001'),
19('李四', '技术部', 12000, '2021-03-20', 'li@company.com', '13800138002'),
20('王五', '销售部', 8000, '2022-06-10', 'wang@company.com', '13800138003'),
21('赵六', '销售部', 7500, '2022-08-05', 'zhao@company.com', '13800138004'),
22('钱七', '人事部', 9000, '2021-09-15', 'qian@company.com', '13800138005');

案例1：明显的回表查询

1-- 查询1：会发生回表
2EXPLAIN SELECT * FROM employee WHERE department = '技术部';

执行过程分析：

  1. 使用 idx_department 索引找到所有 department = ‘技术部’ 的记录
  2. 获取对应的主键值 emp_id
  3. 用这些 emp_id 回表到聚簇索引查询完整数据
  4. 返回所有字段

案例2：避免回表的覆盖索引

1-- 查询2：覆盖索引，避免回表
2EXPLAIN SELECT emp_id, department FROM employee WHERE department = '技术部';

执行过程分析：

  1. 使用 idx_department 索引找到所有 department = ‘技术部’ 的记录
  2. 索引中已经包含 emp_id 和 department 字段
  3. 直接返回，无需回表

案例3：复合索引的回表情况

1-- 查询3：复合索引，但需要回表
2EXPLAIN SELECT emp_id, emp_name, department, salary 
3FROM employee 
4WHERE department = '技术部' AND salary > 10000;

执行过程分析：

  1. 使用 idx_department_salary 索引找到符合条件的记录
  2. 索引中包含 department, salary, emp_id
  3. 但是 emp_name 字段不在索引中，需要回表查询

五、 如何识别回表

使用 EXPLAIN 分析

1-- 查看执行计划判断是否回表
2EXPLAIN SELECT * FROM employee WHERE email = 'zhang@company.com';
3
4-- 重点关注 Extra 字段：
5-- "Using index": 覆盖索引，无回表 ✓
6-- "Using index condition": 使用索引，但需要回表
7-- "Using where": 可能回表

实际测试对比

 1-- 测试1：需要回表的查询
 2EXPLAIN 
 3SELECT emp_name, department, salary, email, phone 
 4FROM employee 
 5WHERE department = '技术部';
 6
 7-- 测试2：覆盖索引查询（无回表）
 8EXPLAIN 
 9SELECT emp_id, department 
10FROM employee 
11WHERE department = '技术部';
12
13-- 测试3：部分回表
14EXPLAIN 
15SELECT emp_id, department, emp_name 
16FROM employee 
17WHERE department = '技术部';

六、 回表的性能影响

性能测试对比

 1-- 创建大数据量表进行测试
 2CREATE TABLE large_table (
 3    id INT PRIMARY KEY AUTO_INCREMENT,
 4    data1 VARCHAR(100),
 5    data2 VARCHAR(100),
 6    data3 VARCHAR(100),
 7    index_col INT,
 8    INDEX idx_index_col (index_col)
 9);
10
11-- 插入10万条测试数据
12DELIMITER //
13CREATE PROCEDURE InsertTestData()
14BEGIN
15    DECLARE i INT DEFAULT 0;
16    WHILE i < 100000 DO
17        INSERT INTO large_table (data1, data2, data3, index_col) 
18        VALUES (CONCAT('data1_', i), CONCAT('data2_', i), CONCAT('data3_', i), i);
19        SET i = i + 1;
20    END WHILE;
21END//
22DELIMITER ;
23
24CALL InsertTestData();

性能对比查询

1-- 需要回表的查询（慢）
2SELECT * FROM large_table WHERE index_col BETWEEN 1000 AND 2000;
3
4-- 覆盖索引查询（快）
5SELECT id, index_col FROM large_table WHERE index_col BETWEEN 1000 AND 2000;

七、 如何避免回表

方法1：使用覆盖索引

1-- 创建覆盖索引
2CREATE INDEX idx_covering ON employee(department, salary, emp_name);
3
4-- 现在这个查询不会回表
5EXPLAIN 
6SELECT emp_id, department, salary, emp_name 
7FROM employee 
8WHERE department = '技术部' AND salary > 10000;

方法2：只查询需要的列

1-- 不好的写法：查询所有列
2SELECT * FROM employee WHERE email = 'zhang@company.com';
3
4-- 好的写法：只查询需要的列
5SELECT emp_id, emp_name, email FROM employee WHERE email = 'zhang@company.com';
6
7-- 更好的写法：创建覆盖索引
8CREATE INDEX idx_covering_email ON employee(email, emp_name);
9SELECT emp_name, email FROM employee WHERE email = 'zhang@company.com';

方法3：优化复合索引设计

1-- 根据查询需求设计覆盖索引
2-- 常见查询：按部门查询员工信息和薪资
3CREATE INDEX idx_dept_covering ON employee(department, salary, emp_name, hire_date);
4
5-- 现在这些查询都可以使用覆盖索引：
6SELECT emp_name, salary FROM employee WHERE department = '技术部';
7SELECT department, AVG(salary) FROM employee GROUP BY department;
8SELECT emp_name, hire_date FROM employee WHERE department = '销售部' ORDER BY hire_date;

八、实际业务场景优化

场景1：用户查询优化

 1-- 原始表结构
 2CREATE TABLE users (
 3    user_id INT PRIMARY KEY,
 4    username VARCHAR(50),
 5    email VARCHAR(100),
 6    phone VARCHAR(20),
 7    real_name VARCHAR(50),
 8    avatar_url VARCHAR(200),
 9    created_time DATETIME,
10    INDEX idx_email (email)
11);
12
13-- 问题：根据email查询用户信息需要回表
14SELECT * FROM users WHERE email = 'user@example.com';
15
16-- 优化：创建覆盖索引
17CREATE INDEX idx_email_covering ON users(email, username, avatar_url);
18
19-- 优化后的查询（页面展示常用字段）
20SELECT user_id, username, email, avatar_url 
21FROM users 
22WHERE email = 'user@example.com';

场景2：订单查询优化

 1-- 订单表
 2CREATE TABLE orders (
 3    order_id BIGINT PRIMARY KEY,
 4    user_id INT,
 5    amount DECIMAL(10,2),
 6    status VARCHAR(20),
 7    create_time DATETIME,
 8    update_time DATETIME,
 9    INDEX idx_user_status (user_id, status)
10);
11
12-- 常见查询：用户订单列表
13-- 需要回表的查询
14SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';
15
16-- 优化为覆盖索引
17CREATE INDEX idx_user_status_covering ON orders(user_id, status, order_id, amount, create_time);
18
19-- 优化后的查询（列表页面需要的字段）
20SELECT order_id, amount, create_time 
21FROM orders 
22WHERE user_id = 123 AND status = 'completed';

总结

回表的关键点：

  1. 触发条件：使用二级索引且查询字段不全部在索引中
  2. 性能影响：额外的磁盘I/O，降低查询性能
  3. 识别方法：EXPLAIN查看执行计划，关注"Using index"
  4. 避免方法：

    • 使用覆盖索引

    • 只SELECT需要的列

    • 合理设计复合索引

    • 考虑使用聚簇索引

发布在 Database

发表评论