2. 首页 > 国产数据库教程 > TiDB教程 > 正文

tidb教程FG022-TiDB复合索引与覆盖索引

教程整理：风哥教程  |  更新时间：2026-04-02  |  教程分类：TiDB教程  |  文档学习：24

本文档详细介绍TiDB复合索引与覆盖索引的使用方法，包括索引概念、设计、实施方案、实战案例等内容。风哥教程参考TiDB官方文档索引相关内容，适合DBA和开发人员在日常使用TiDB时参考。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 复合索引概念

复合索引是基于多个列创建的索引，也称为多列索引。

  • 定义：基于两个或多个列创建的索引
  • 特点：遵循最左前缀原则
  • 适用场景：多列查询条件
复合索引的优点：

  • 加速多列查询
  • 减少索引数量
  • 提高查询性能
  • 支持最左前缀匹配

1.2 覆盖索引概念

覆盖索引是指索引包含查询所需的所有列，无需回表获取数据。

  • 定义：索引包含查询所需的所有列
  • 特点：避免回表操作，提高查询性能
  • 适用场景：频繁的列查询
覆盖索引的优点：

  • 避免回表操作，提高查询性能
  • 减少IO操作
  • 降低内存使用
  • 适用于频繁的列查询

1.3 索引类型差异

# 索引类型差异

| 特性 | 复合索引 | 覆盖索引 | 普通索引 |
|——|———-|———-|———-|
| 索引列数 | 多个 | 多个（包含查询列） | 单个 |
| 最左前缀原则 | 适用 | 适用 | 不适用 |
| 回表操作 | 可能需要 | 不需要 | 需要 |
| 查询性能 | 较快 | 最快 | 一般 |
| 存储开销 | 较大 | 较大 | 较小 |
| 适用场景 | 多列查询 | 频繁的列查询 | 单 column query |

风哥提示：根据具体的查询模式选择合适的索引类型。学习交流加群风哥微信: itpux-com风哥提示：

Part02-生产环境规划与建议

2.1 复合索引设计

2.1.1 复合索引列顺序

# 复合索引列顺序

## 1. 选择原则
– 将最常用的列放在前面
– 将选择性高的列放在前面
– 将范围查询的列放在后面
– 遵循最左前缀原则

## 2. 最左前缀原则
– 复合索引 (a, b, c) 可以用于：
– WHERE a = ?
– WHERE a = ? AND b = ?
– WHERE a = ? AND b = ? AND c = ?
– 但不能用于：
– WHERE b = ?
– WHERE c = ?
– WHERE b = ? AND c = ?

## 3. 示例
– 对于查询：WHERE status = ? AND created_at > ?
– 复合索引：(status, created_at)
– 对于查询：WHERE user_id = ? AND status = ?
– 复合索引：(user_id, status)

2.1.2 复合索引数量控制

# 复合索引数量控制

## 1. 影响因素
– 写入性能：索引越多，写入开销越大
– 存储开销：索引占用存储空间
– 管理复杂度：索引越多，管理越复杂

## 2. 推荐数量
– 每个表的复合索引数量不宜过多（建议不超过3个）
– 避免创建冗余的复合索引
– 定期清理无用的复合索引

## 3. 冗余索引识别
– 识别前缀冗余：如 (a, b) 和 (a)
– 识别顺序冗余：如 (a, b) 和 (b, a)
– 识别功能冗余：如 (a, b) 和 (a, b, c)

2.2 覆盖索引设计

2.2.1 覆盖索引列选择

# 覆盖索引列选择

## 1. 选择原则
– 包含查询所需的所有列
– 包含WHERE条件中的列
– 包含ORDER BY和GROUP BY中的列
– 控制索引大小

## 2. 示例
– 对于查询：SELECT name, age FROM fgedu_users WHERE status = ?
– 覆盖索引：(status, name, age)
– 对于查询：SELECT user_id, amount FROM fgedu_orders WHERE status = ? AND created_at > ?
– 覆盖索引：(status, created_at, user_id, amount)

## 3. 注意事项
– 覆盖索引列不宜过多，避免索引过大
– 对于频繁查询的列组合，考虑创建覆盖索引
– 定期评估覆盖索引的有效性

2.2.2 覆盖索引性能考虑

# 覆盖索引性能考虑

## 1. 查询性能
– 避免回表操作，提高查询速度
– 减少IO操作，降低系统负载
– 提高内存利用率

## 2. 写入性能
– 增加写入开销（需要更新索引）
– 索引越大，写入开销越大

## 3. 存储开销
– 覆盖索引占用更多存储空间
– 索引列越多，存储空间越大

## 4. 权衡考虑
– 平衡查询性能和写入性能
– 平衡存储开销和查询性能
– 根据业务需求调整覆盖索引策略

学习交流加群风哥QQ113257174

2.3 使用场景分析

# 使用场景分析

## 复合索引使用场景
– 多列查询：WHERE a = ? AND b = ?
– 范围查询：WHERE a = ? AND b > ?
– 排序查询：ORDER BY a, b
– 分组查询：GROUP BY a, b

## 覆盖索引使用场景
– 频繁的列查询：SELECT a, b FROM table WHERE c = ?
– 统计查询：SELECT COUNT(*) FROM table WHERE a = ?
– 排序查询：SELECT a, b FROM table WHERE c = ? ORDER BY a
– 连接查询：SELECT a, b FROM table1 JOIN table2 ON table1.id = table2.id WHERE table1.c = ?

## 组合使用场景
– 复合覆盖索引：同时满足复合索引和覆盖索引的条件
– 多列查询且需要覆盖：SELECT a, b FROM table WHERE c = ? AND d = ?
– 复杂查询优化：通过覆盖索引减少回表操作

生产环境建议：根据具体的查询模式和业务需求设计合适的索引策略，必要时可以组合使用复合索引和覆盖索引。学习交流加群风哥QQ113257174

Part03-生产环境项目实施方案

3.1 复合索引实施方案

3.1.1 创建复合索引

# 创建复合索引

## 1. 创建表时创建复合索引
CREATE TABLE fgedu_orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
order_no VARCHAR(32) UNIQUE,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20),
created_at DATETIME,
INDEX idx_user_id_status (user_id, status),
INDEX idx_status_created_at (status, created_at)
);

## 2. 为现有表添加复合索引
ALTER TABLE fgedu_orders ADD INDEX idx_user_id_created_at (user_id, created_at);

## 3. 查看复合索引
SHOW INDEX FROM fgedu_orders;

## 4. 删除复合索引
ALTER TABLE fgedu_orders DROP INDEX idx_user_id_status;

3.1.2 复合索引优化

# 复合索引优化

## 1. 分析查询模式
– 收集常用查询语句
– 分析WHERE条件
– 分析ORDER BY和GROUP BY子句

## 2. 优化复合索引列顺序
– 将最常用的列放在前面
– 将选择性高的列放在前面
– 将范围查询的列放在后面

## 3. 测试复合索引效果
– 使用EXPLAIN分析执行计划
– 测试查询性能
– 调整复合索引列顺序

## 4. 示例
– 对于查询：WHERE user_id = ? AND status = ?
– 复合索引：(user_id, status)
– 对于查询：WHERE status = ? AND created_at > ?
– 复合索引：(status, created_at)

3.2 覆盖索引实施方案

3.2.1 创建覆盖索引

# 创建覆盖索引

## 1. 识别覆盖索引机会
– 分析频繁查询的列组合
– 确定查询所需的所有列
– 设计包含这些列的索引

## 2. 创建覆盖索引
– 对于查询：SELECT name, age FROM fgedu_users WHERE status = ?
– 覆盖索引：(status, name, age)

– 对于查询：SELECT user_id, amount FROM fgedu_orders WHERE status = ? AND created_at > ?
– 覆盖索引：(status, created_at, user_id, amount)

## 3. 查看覆盖索引
SHOW INDEX FROM fgedu_users;

## 4. 删除覆盖索引
ALTER TABLE fgedu_users DROP INDEX idx_status_name_age;

3.2.2 覆盖索引验证

# 覆盖索引验证

## 1. 使用EXPLAIN验证
– 查看执行计划中的”Extra”字段
– 覆盖索引会显示”Using index”

## 2. 示例
mysql> EXPLAIN SELECT name, age FROM fgedu_users WHERE status = ‘active’;

## 3. 性能测试
– 比较使用覆盖索引和不使用覆盖索引的查询性能
– 测量查询响应时间
– 分析IO操作次数

## 4. 覆盖索引维护
– 定期重建覆盖索引
– 更新统计信息
– 监控覆盖索引使用情况

3.3 索引维护与管理

3.3.1 复合索引维护

# 复合索引维护

## 1. 重建复合索引
– 当数据分布发生变化时
– 当索引碎片严重时
– 当查询性能下降时

## 2. 更新统计信息
– 定期更新统计信息
– 确保优化器生成正确的执行计划

## 3. 监控复合索引
– 监控复合索引的使用情况
– 分析复合索引的性能
– 调整复合索引策略

## 4. 复合索引维护脚本
“`bash
#!/bin/bash
# composite_index_maintenance.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 更新统计信息
mysql -u root -p -e “ANALYZE TABLE fgedu_orders;”

# 检查复合索引使用情况
mysql -u root -p -e “SHOW INDEX FROM fgedu_orders;”
“`

3.3.2 覆盖索引维护

# 覆盖索引维护

## 1. 重建覆盖索引
– 当数据分布发生变化时
– 当索引碎片严重时
– 当查询性能下降时

## 2. 更新统计信息
– 定期更新统计信息
– 确保优化器生成正确的执行计划

## 3. 监控覆盖索引
– 监控覆盖索引的使用情况
– 分析覆盖索引的性能
– 调整覆盖索引策略

## 4. 覆盖索引维护脚本
“`bash
#!/bin/bash
# covering_index_maintenance.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 更新统计信息
mysql -u root -p -e “ANALYZE TABLE fgedu_users;”

# 检查覆盖索引使用情况
mysql -u root -p -e “SHOW INDEX FROM fgedu_users;”
“`

风哥提示：定期维护索引可以保持良好的查询性能。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 复合索引实战案例

# 复合索引实战案例

## 1. 案例背景
– 表：fgedu_orders（订单表）
– 数据量：1000万行
– 常用查询：
– WHERE user_id = ? AND status = ?
– WHERE status = ? AND created_at > ?

## 2. 分析过程
– 检查当前索引：只有主键索引
– 分析查询执行计划：全表扫描
– 设计复合索引策略

## 3. 实施步骤

### 步骤1：创建复合索引
mysql> ALTER TABLE fgedu_orders ADD INDEX idx_user_id_status (user_id, status);
mysql> ALTER TABLE fgedu_orders ADD INDEX idx_status_created_at (status, created_at);

### 步骤2：测试查询性能
mysql> EXPLAIN SELECT * FROM fgedu_orders WHERE user_id = 1 AND status = ‘completed’;
mysql> SELECT * FROM fgedu_orders WHERE user_id = 1 AND status = ‘completed’;

mysql> EXPLAIN SELECT * FROM fgedu_orders WHERE status = ‘completed’ AND created_at > ‘2024-01-01’;
mysql> SELECT * FROM fgedu_orders WHERE status = ‘completed’ AND created_at > ‘2024-01-01’;

### 步骤3：性能对比
– 无索引：1.5秒
– 复合索引：0.05秒

## 4. 优化效果
– 查询性能提升30倍
– 减少数据扫描范围
– 提高系统响应速度

4.2 覆盖索引实战案例

# 覆盖索引实战案例

## 1. 案例背景
– 表：fgedu_users（用户表）
– 数据量：500万行
– 常用查询：SELECT name, age FROM fgedu_users WHERE status = ?

## 2. 分析过程
– 检查当前索引：只有主键索引和status单列索引
– 分析执行计划：使用status索引，但需要回表
– 设计覆盖索引策略

## 3. 实施步骤

### 步骤1：创建覆盖索引
mysql> ALTER TABLE fgedu_users ADD INDEX idx_status_name_age (status, name, age);

### 步骤2：测试查询性能
mysql> EXPLAIN SELECT name, age FROM fgedu_users WHERE status = ‘active’;
mysql> SELECT name, age FROM fgedu_users WHERE status = ‘active’;

### 步骤3：性能对比
– 普通索引：0.15秒
– 覆盖索引：0.05秒

## 4. 优化效果
– 查询性能提升3倍
– 避免回表操作
– 减少IO操作

4.3 性能分析与优化

# 性能分析与优化

## 1. 测试环境
– 数据库：TiDB 7.5.0
– 服务器：8核16GB
– 表：fgedu_orders（1000万行）

## 2. 测试场景
– 无索引
– 单列索引
– 复合索引
– 覆盖索引

## 3. 查询性能测试

### 场景1：多列查询
– 无索引：1.5秒
– 单列索引：0.5秒
– 复合索引：0.05秒
– 覆盖索引：0.04秒

### 场景2：范围查询
– 无索引：1.8秒
– 单列索引：0.8秒
– 复合索引：0.1秒
– 覆盖索引：0.08秒

### 场景3：排序查询
– 无索引：2.0秒
– 单列索引：1.0秒
– 复合索引：0.2秒
– 覆盖索引：0.15秒

## 4. 写入性能测试
– 无索引：1000行/秒
– 单列索引：800行/秒
– 复合索引：700行/秒
– 覆盖索引：650行/秒

## 5. 存储开销测试
– 单列索引：50MB
– 复合索引：100MB
– 覆盖索引：150MB

## 6. 优化建议
– 对于多列查询，使用复合索引
– 对于频繁的列查询，使用覆盖索引
– 平衡查询性能和写入性能
– 合理设置复合索引的列顺序

生产环境建议：根据具体的查询模式和业务需求选择合适的索引策略，以获得最佳性能。from tidb视频:www.itpux.com

Part05-风哥经验总结与分享

5.1 最佳实践

TiDB复合索引与覆盖索引的最佳实践：

  • 复合索引最佳实践：
    • 根据查询模式设计复合索引
    • 合理设置复合索引的列顺序
    • 遵循最左前缀原则
    • 控制复合索引数量
  • 覆盖索引最佳实践：
    • 识别频繁查询的列组合
    • 创建包含查询所需所有列的索引
    • 控制覆盖索引的大小
    • 定期评估覆盖索引的有效性
  • 通用最佳实践：
    • 根据业务需求和查询模式选择合适的索引类型
    • 定期维护索引，保持良好性能
    • 监控索引使用情况，及时调整索引策略
    • 平衡查询性能和写入性能

5.2 性能优化技巧

# 性能优化技巧

## 1. 复合索引优化技巧
– 选择合适的列顺序：最常用、选择性高的列放在前面
– 利用最左前缀原则：设计复合索引时考虑所有可能的查询组合
– 避免冗余索引：删除功能重复的索引
– 定期分析查询模式：根据实际查询调整复合索引

## 2. 覆盖索引优化技巧
– 识别频繁查询的列组合：创建针对性的覆盖索引
– 控制索引大小：只包含必要的列
– 利用覆盖索引加速排序：将排序列包含在索引中
– 监控覆盖索引使用情况：确保索引被有效使用

## 3. 查询优化技巧
– 确保查询条件包含复合索引的前缀列
– 避免在索引列上使用函数
– 合理使用EXPLAIN分析执行计划
– 优化SQL语句，确保使用索引

## 4. 写入优化技巧
– 控制索引数量：避免过多的索引
– 批量写入数据：减少索引更新次数
– 合理设置事务大小：避免大事务
– 利用并行写入提高写入性能

## 5. 维护优化技巧
– 定期重建索引：保持索引性能
– 更新统计信息：确保优化器生成正确的执行计划
– 监控索引使用情况：及时调整索引策略
– 清理无用索引：减少存储开销和写入开销

5.3 常见问题与解决

# 常见问题与解决

## 1. 复合索引问题

### 问题1：复合索引列顺序不当
– 症状：查询未使用复合索引
– 原因：列顺序不符合查询模式
– 解决：调整复合索引列顺序，将最常用的列放在前面

### 问题2：最左前缀原则未遵循
– 症状：查询未使用复合索引
– 原因：查询条件未包含复合索引的前缀列
– 解决：优化查询语句，确保包含前缀列，或调整索引顺序

### 问题3：复合索引过多
– 症状：写入性能下降，存储空间占用大
– 原因：创建了过多的复合索引
– 解决：清理无用的复合索引，合并重复的索引

## 2. 覆盖索引问题

### 问题1：覆盖索引过大
– 症状：存储开销大，写入性能下降
– 原因：覆盖索引包含过多列
– 解决：只包含必要的列，控制索引大小

### 问题2：覆盖索引未被使用
– 症状：查询仍需回表
– 原因：查询条件或选择列与覆盖索引不匹配
– 解决：调整覆盖索引列，确保包含查询所需的所有列

### 问题3：覆盖索引维护成本高
– 症状：索引重建时间长
– 原因：覆盖索引过大
– 解决：合理设计覆盖索引，避免包含过多列

## 3. 通用问题

### 问题1：索引未被使用
– 症状：查询执行计划显示全表扫描
– 原因：查询条件未包含索引列，或在索引列上使用了函数
– 解决：优化查询语句，确保查询条件包含索引列，避免在索引列上使用函数

### 问题2：索引碎片
– 症状：查询性能下降
– 原因：索引碎片严重
– 解决：重建索引

### 问题3：统计信息过期
– 症状：执行计划异常
– 原因：统计信息未及时更新
– 解决：定期更新统计信息

风哥提示：合理使用复合索引和覆盖索引，可以显著提高TiDB的查询性能。

持续学习：关注TiDB的新特性和最佳实践，不断优化索引设计和使用。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息