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

tidb教程FG020-TiDB索引原理与设计

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

本文档详细介绍TiDB索引的原理与设计方法，包括索引概念、类型、原理、设计、创建、管理等内容。风哥教程参考TiDB官方文档索引相关内容，适合DBA和开发人员在日常使用TiDB时参考。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 索引概念

索引是数据库中用于提高查询性能的数据结构，通过创建索引可以加速数据的查找和排序。

  • 定义：索引是一种数据结构，用于快速定位和访问数据
  • 作用：提高查询性能，加速数据检索
  • 代价：占用存储空间，增加写入开销
索引的优点：

  • 加速查询：减少数据扫描范围
  • 加速排序：利用索引的有序性
  • 加速连接：提高表连接性能
  • 保证唯一性：通过唯一索引确保数据唯一性

1.2 索引类型

TiDB支持多种类型的索引：

1.2.1 主键索引

# 主键索引
– 唯一标识表中的每一行数据
– 自动创建，无需手动创建
– 不能为空
– 一个表只能有一个主键
– 示例：
CREATE TABLE fgedu_users (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);

1.2.2 唯一索引

# 唯一索引
– 确保索引列的值唯一
– 允许空值
– 一个表可以有多个唯一索引
– 示例：
CREATE TABLE fgedu_users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
name VARCHAR(50) NOT NULL
);

1.2.3 普通索引

# 普通索引风哥提示：
– 加速查询，不保证唯一性
– 允许空值
– 一个表可以有多个普通索引
– 示例：
CREATE TABLE fgedu_users (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
INDEX idx_name (name),
INDEX idx_age (age)
);

1.2.4 复合索引

# 复合索引
– 基于多个列创建的索引
– 遵循最左前缀原则
– 示例：
CREATE TABLE fgedu_users (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
INDEX idx_name_age (name, age)
);

1.2.5 前缀索引

# 前缀索引
– 基于列的前缀创建的索引
– 减少索引大小，提高查询性能
– 适用于长字符串列
– 示例：
CREATE TABLE fgedu_users (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
INDEX idx_name_prefix (name(20))
);

1.3 索引原理

TiDB的索引实现基于B+树数据结构：

  • B+树结构：平衡树，每个节点包含多个键值对
  • 叶子节点：存储数据或指向数据的指针
  • 非叶子节点：存储索引键和指向子节点的指针
  • 有序性：索引键按顺序存储，便于范围查询
  • 聚簇索引：主键索引直接存储数据，其他索引存储主键值
风哥提示：了解索引原理有助于设计高效的索引策略。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 索引设计

2.1.1 索引设计原则

# 索引设计原则

## 1. 选择合适的索引列
– 频繁用于WHERE条件的列
– 频繁用于ORDER BY和GROUP BY的列
– 频繁用于JOIN条件的列
– 基数高的列（唯一值多的列）

## 2. 复合索引设计
– 遵循最左前缀原则
– 将最常用的列放在前面
– 将选择性高的列放在前面
– 避免创建过多的复合索引

## 3. 索引数量控制
– 每个表的索引数量不宜过多（建议不超过5个）
– 避免创建不必要的索引
– 定期清理无用的索引

## 4. 索引大小控制
– 对于长字符串，使用前缀索引
– 避免在大文本列上创建索引
– 合理设置索引列的长度

2.1.2 索引设计步骤

# 索引设计步骤

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

## 2. 选择索引列
– 基于查询模式选择索引列
– 考虑复合索引的组合
– 评估索引的选择性学习交流加群风哥QQ113257174

## 3. 创建索引
– 按照设计创建索引
– 测试索引效果
– 调整索引设计

## 4. 监控与优化
– 监控索引使用情况
– 分析慢查询
– 调整索引策略

2.2 索引选择

2.2.1 索引类型选择

# 索引类型选择

## 1. 主键索引
– 用于唯一标识行
– 自动创建，无需手动创建
– 建议使用自增整数类型

## 2. 唯一索引
– 用于确保列值唯一
– 适用于邮箱、手机号等唯一字段

## 3. 普通索引
– 用于加速查询
– 适用于频繁查询的字段

## 4. 复合索引
– 用于多列查询
– 适用于复杂的查询条件

## 5. 前缀索引
– 用于长字符串列
– 减少索引大小

2.2.2 索引列选择

# 索引列选择

## 1. 适合创建索引的列
– 频繁用于WHERE条件的列
– 频繁用于ORDER BY和GROUP BY的列
– 频繁用于JOIN条件的列
– 基数高的列
– 字符串列的前缀（使用前缀索引）

## 2. 不适合创建索引的列
– 频繁更新的列
– 基数低的列（如性别）
– 大文本列
– 很少使用的列
– 计算列（建议使用虚拟列）

2.3 性能考虑

# 性能考虑

## 1. 查询性能
– 索引加速查询：减少数据扫描范围
– 索引加速排序：利用索引的有序性
– 索引加速连接：提高表连接性能

## 2. 写入性能
– 索引增加写入开销：每次写入需要更新索引
– 索引数量：索引越多，写入开销越大
– 索引大小：索引越大，写入开销越大

## 3. 存储开销
– 索引占用存储空间
– 复合索引占用更多空间
– 前缀索引减少空间占用

## 4. 维护开销
– 索引需要定期维护
– 索引碎片需要清理
– 统计信息需要更新

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

生产环境建议：根据业务需求和查询模式设计合适的索引策略，平衡查询性能和写入性能。学习交流加群风哥QQ113257174

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

3.1 索引创建

3.1.1 基本索引创建

# 基本索引创建

## 1. 创建表时创建索引
CREATE TABLE fgedu_users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT,
city VARCHAR(50),
INDEX idx_name (name),
INDEX idx_age (age),
INDEX idx_city (city)
);

## 2. 为现有表添加索引
ALTER TABLE fgedu_users ADD INDEX idx_name_age (name, age);

## 3. 创建前缀索引
ALTER TABLE fgedu_users ADD INDEX idx_city_prefix (city(20));

## 4. 创建唯一索引
ALTER TABLE fgedu_users ADD UNIQUE INDEX idx_email (email);

3.1.2 复合索引创建

# 复合索引创建

## 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_created_at (user_id, created_at)
);

## 2. 多列复合索引
ALTER TABLE fgedu_orders ADD INDEX idx_status_created_at_amount (status, created_at, amount);

## 3. 复合唯一索引
ALTER TABLE fgedu_orders ADD UNIQUE INDEX idx_user_id_order_no (user_id, order_no);

3.2 索引管理

3.2.1 查看索引

# 查看索引

## 1. 查看表的索引
SHOW INDEX FROM fgedu_users;

## 2. 查看索引详情
SHOW CREATE TABLE fgedu_users;

## 3. 查看索引使用情况
EXPLAIN SELECT * FROM fgedu_users WHERE name = ‘张三’;

## 4. 查看索引统计信息
SELECT
table_schema,
table_name,
index_name,
cardinality
FROM
information_schema.statistics
WHERE
table_schema = ‘fgedudb’
AND table_name = ‘fgedu_users’;

3.2.2 删除索引

# 删除索引

## 1. 删除普通索引
ALTER TABLE fgedu_users DROP INDEX idx_age;

## 2. 删除唯一索引
ALTER TABLE fgedu_users DROP INDEX idx_email;

## 3. 删除复合索引
ALTER TABLE fgedu_users DROP INDEX idx_name_age;

## 4. 注意事项
– 不能删除主键索引（需要先修改表结构）
– 删除索引会影响查询性能
– 删除索引后需要更新应用程序

3.3 索引维护

3.3.1 索引重建

# 索引重建

## 1. 重建单个索引
ALTER TABLE fgedu_users DROP INDEX idx_name, ADD INDEX idx_name (name);

## 2. 重建所有索引
ALTER TABLE fgedu_users ENGINE = InnoDB;

## 3. 重建表
ALTER TABLE fgedu_users REBUILD;

## 4. 重建的时机
– 索引碎片严重时
– 数据量变化较大时
– 查询性能下降时

3.3.2 统计信息更新

# 统计信息更新

## 1. 手动更新统计信息
ANALYZE TABLE fgedu_users;

## 2. 更新单个索引的统计信息
ANALYZE TABLE fgedu_users UPDATE HISTOGRAM ON name;

## 3. 统计信息的作用
– 帮助优化器生成正确的执行计划
– 影响索引选择
– 影响连接顺序

## 4. 更新时机
– 数据量变化较大时
– 索引重建后
– 查询计划异常时

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

Part04-生产案例与实战讲解

4.1 索引设计实战案例

# 索引设计实战案例

## 1. 案例背景
– 表：fgedu_orders（订单表）
– 数据量：1000万行
– 常用查询：
– 根据用户ID查询订单
– 根据订单状态查询订单
– 根据创建时间范围查询订单
– 根据用户ID和创建时间查询订单

## 2. 索引设计
– 主键索引：id
– 唯一索引：order_no
– 复合索引：idx_user_id_created_at (user_id, created_at)
– 普通索引：idx_status (status)

## 3. 实施步骤

### 步骤1：创建表和索引
mysql> CREATE TABLE fgedu_orders (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> order_no VARCHAR(32) UNIQUE,
-> user_id INT,
-> amount DECIMAL(10,2) NOT NULL,
-> status VARCHAR(20),
-> created_at DATETIME,
-> INDEX idx_user_id_created_at (user_id, created_at),
-> INDEX idx_status (status)
-> );

### 步骤2：插入测试数据
mysql> INSERT INTO fgedu_orders (order_no, user_id, amount, status, created_at) VALUES
-> (‘FG202401010001’, 1, 100.00, ‘completed’, ‘2024-01-01 10:00:00’),
-> (‘FG202401010002’, 1, 200.00, ‘completed’, ‘2024-01-02 10:00:00’),
-> (‘FG202401010003’, 2, 300.00, ‘pending’, ‘2024-01-01 11:00:00’),
-> (‘FG202401010004’, 2, 400.00, ‘processing’, ‘2024-01-02 11:00:00’),
-> (‘FG202401010005’, 3, 500.00, ‘completed’, ‘2024-01-01 12:00:00’);

### 步骤3：测试查询性能
mysql> EXPLAIN SELECT * FROM fgedu_orders WHERE user_id = 1;
mysql> EXPLAIN SELECT * FROM fgedu_orders WHERE user_id = 1 AND created_at BETWEEN ‘2024-01-01’ AND ‘2024-01-31’;
mysql> EXPLAIN SELECT * FROM fgedu_orders WHERE status = ‘completed’;

## 4. 索引效果分析
– 根据用户ID查询：使用idx_user_id_created_at索引
– 根据用户ID和创建时间查询：使用idx_user_id_created_at索引
– 根据状态查询：使用idx_status索引
– 索引覆盖：减少回表操作

4.2 索引优化实战案例

# 索引优化实战案例

## 1. 案例背景
– 表：fgedu_users（用户表）
– 数据量：500万行
– 问题：查询性能差
– 常用查询：SELECT * FROM fgedu_users WHERE name = ‘张三’ AND age = 25;

## 2. 分析过程
– 查看当前索引：只有主键索引
– 分析执行计划：全表扫描
– 查看数据分布：name和age的选择性较高

## 3. 优化方案
– 创建复合索引：idx_name_age (name, age)
– 测试查询性能
– 验证索引效果

## 4. 实施步骤

### 步骤1：查看当前索引
mysql> SHOW INDEX FROM fgedu_users;

### 步骤2：创建复合索引
mysql> ALTER TABLE fgedu_users ADD INDEX idx_name_age (name, age);

### 步骤3：测试查询性能
mysql> EXPLAIN SELECT * FROM fgedu_users WHERE name = ‘张三’ AND age = 25;
mysql> SELECT * FROM fgedu_users WHERE name = ‘张三’ AND age = 25;

### 步骤4：验证索引效果
– 执行计划：使用idx_name_age索引
– 查询响应时间：从0.5秒降低到0.01秒
– 扫描行数：从500万行减少到10行

4.3 性能分析与调优

# 性能分析与调优

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

## 2. 测试场景
– 无索引
– 单个索引
– 复合索引
– 前缀索引

## 3. 查询性能测试

### 场景1：根据用户ID查询
– 无索引：1.2秒
– 单个索引：0.05秒
– 复合索引：0.05秒
– 前缀索引：N/A

### 场景2：根据用户ID和创建时间查询
– 无索引：1.5秒
– 单个索引：0.3秒
– 复合索引：0.03秒
– 前缀索引：N/A

### 场景3：根据状态查询
– 无索引：0.8秒
– 单个索引：0.1秒
– 复合索引：0.1秒
– 前缀索引：N/A

### 场景4：根据城市查询（长字符串）
– 无索引：1.0秒
– 单个索引：0.15秒
– 复合索引：N/A
– 前缀索引：0.1秒

## 4. 写入性能测试
– 无索引：1000行/秒
– 1个索引：800行/秒
– 3个索引：600行/秒
– 5个索引：400行/秒

## 5. 调优建议
– 根据查询模式创建合适的索引
– 优先使用复合索引
– 对于长字符串使用前缀索引
– 控制索引数量，避免过多索引
– 定期维护索引，更新统计信息

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

Part05-风哥经验总结与分享

5.1 最佳实践

TiDB索引设计的最佳实践：

  • 索引设计最佳实践：
    • 根据查询模式设计索引
    • 优先使用复合索引
    • 遵循最左前缀原则
    • 控制索引数量
  • 索引使用最佳实践：
    • 确保查询条件包含索引列
    • 避免在索引列上使用函数
    • 合理使用覆盖索引
    • 定期分析执行计划
  • 索引维护最佳实践：
    • 定期重建索引
    • 更新统计信息
    • 监控索引使用情况
    • 清理无用索引
  • 性能优化最佳实践：
    • 平衡查询性能和写入性能
    • 根据业务需求调整索引策略
    • 使用前缀索引减少空间占用
    • 合理设置索引列的顺序

5.2 性能优化技巧

# 性能优化技巧

## 1. 索引设计技巧
– 选择高选择性的列作为索引列
– 将最常用的列放在复合索引的前面
– 避免创建冗余索引
– 对于长字符串使用前缀索引

## 2. 查询优化技巧
– 使用索引覆盖查询
– 避免全表扫描
– 合理使用EXPLAIN分析执行计划
– 优化SQL语句，确保使用索引

## 3. 写入优化技巧
– 控制索引数量
– 批量写入数据
– 避免频繁更新索引列
– 合理设置事务大小

## 4. 维护优化技巧
– 定期重建索引
– 更新统计信息
– 监控索引使用情况
– 清理无用索引

## 5. 监控与调优技巧
– 监控慢查询
– 分析执行计划
– 调整索引策略
– 优化系统参数

5.3 常见问题与解决

# 常见问题与解决

## 1. 索引问题

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

### 问题2：索引失效
– 症状：索引存在但未被使用
– 原因：统计信息过期，或查询条件不符合索引使用条件
– 解决：更新统计信息，优化查询语句

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

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

## 2. 查询问题

### 问题1：慢查询
– 症状：查询响应时间长
– 原因：未使用索引，或索引设计不合理
– 解决：优化查询语句，设计合适的索引

### 问题2：全表扫描
– 症状：查询扫描全表数据
– 原因：未创建合适的索引，或查询条件不符合索引使用条件
– 解决：创建合适的索引，优化查询语句

### 问题3：索引回表
– 症状：查询需要回表获取数据
– 原因：索引未覆盖查询列
– 解决：使用覆盖索引，或调整索引设计

## 3. 系统问题

### 问题1：存储空间不足
– 症状：索引占用过多空间
– 原因：索引数量过多，或索引过大
– 解决：清理无用索引，使用前缀索引

### 问题2：写入性能下降
– 症状：写入速度慢
– 原因：索引数量过多，或索引设计不合理
– 解决：减少索引数量，优化索引设计

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

风哥提示：合理设计和使用索引是提高TiDB性能的关键，需要根据具体的业务需求和查询模式进行调整。

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

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

相关推荐

联系我们

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

微信号：itpux-com

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