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

tidb教程FG118-索引设计与优化生产实战

教程整理：风哥教程  |  更新时间：2026-04-04  |  教程分类：TiDB教程  |  文档学习：28
fgedu.net.cn

目录

一、基础概念

1.1 索引定义

索引是一种数据结构，用于快速查询数据库表中的特定记录。TiDB支持多种类型的索引，包括主键索引、唯一索引、普通索引和覆盖索引等。

1.2 索引类型

索引类型 描述 适用场景
主键索引 唯一标识表中的记录，不能为空 所有表必须有主键
唯一索引 确保列值唯一 需要唯一性约束的列
普通索引 加速查询 高频查询的列
覆盖索引 包含查询所需的所有列 频繁查询的固定列组合
前缀索引 对字符串列的前缀部分建立索引 长字符串列

1.3 索引工作原理

TiDB使用B+树作为索引结构，具有以下特点：

  • 所有叶子节点位于同一层，保证查询效率稳定
  • 叶子节点包含完整的索引键和对应的数据指针
  • 支持范围查询和排序操作
  • 通过索引可以快速定位数据，减少全表扫描

二、规划建议

2.1 索引设计原则

  • 选择高频查询列：对经常出现在WHERE、JOIN、ORDER BY和GROUP BY子句中的列建立索引
  • 避免过度索引：每个索引都会增加写操作的开销，合理控制索引数量
  • 考虑索引选择性：选择区分度高的列作为索引
  • 合理使用复合索引：将最常用的列放在前面
  • 注意索引维护成本：索引会占用存储空间并影响写入性能

2.2 索引选择策略

适合建立索引的场景：

  • 频繁查询的列
  • 用于连接的列（JOIN条件）
  • 排序和分组的列
  • 唯一性约束的列

不适合建立索引的场景：

  • 很少查询的列
  • 数据重复度高的列（如性别、状态）
  • 经常更新的列
  • 小表（数据量小于1000行）

2.3 复合索引设计

  • 遵循最左前缀原则
  • 将选择性高的列放在前面
  • 考虑查询条件的组合方式

    • 风哥提示：

  • 避免创建冗余的复合索引

三、实施方案

3.1 索引创建

创建主键索引

CREATE TABLE fgedudb.fgedu_users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

创建唯一索引

CREATE UNIQUE INDEX idx_email ON fgedudb.fgedu_users (email);

创建普通索引

CREATE INDEX idx_username ON fgedudb.fgedu_users (username);

创建复合索引

CREATE INDEX idx_name_age ON fgedudb.fgedu_users (username, age);

创建前缀索引

CREATE INDEX idx_email_prefix ON fgedudb.fgedu_users (email(20));

3.2 索引管理

查看索引

SHOW INDEX FROM fgedudb.fgedu_users;
+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| fgedu_users |          0 | PRIMARY          |            1 | id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| fgedu_users |          0 | idx_email        |            1 | email       | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| fgedu_users |          1 | idx_username     |            1 | username    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| fgedu_users |          1 | idx_name_age     |            1 | username    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| fgedu_users |          1 | idx_name_age     |            2 | age         | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| fgedu_users |          1 | idx_email_prefix |            1 | email       | A         |        NULL |       20 | NULL   |      | BTREE      |         |               |
+------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

删除索引

DROP INDEX idx_email_prefix ON fgedudb.fgedu_users;

修改索引

-- 先删除旧索引，再创建新索引
DROP INDEX idx_username ON fgedudb.fgedu_users;
CREATE INDEX idx_username ON fgedudb.fgedu_users (username);

3.3 索引使用分析

使用EXPLAIN分析索引使用情况

EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE username = 'admin';
+-------------------------+----------+-----------+------------------------+---------------------------------------+
| id                      | estRows  | task      | access object          | operator info                         |
+-------------------------+----------+-----------+------------------------+---------------------------------------+
| TableReader_7           | 1.00     | root      |                        | data:Selection_6                      |
| └─Selection_6           | 1.00     | cop[tikv] |                        | where:username = 'admin'              |
|   └─IndexScan_5         | 1.00     | cop[tikv] | table:fgedu_users, index:idx_username(username) | range:['admin','admin'], keep order:false |
+-------------------------+----------+-----------+------------------------+---------------------------------------+

查看索引统计信息

ANALYZE TABLE fgedudb.fgedu_users;
Query OK, 0 rows affected (0.12 sec)

四、实战案例

4.1 电商平台用户表索引优化

场景：电商平台用户表，需要优化登录、注册和个人信息查询的性能。

步骤1：创建用户表

CREATE TABLE fgedudb.fgedu_users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    mobile VARCHAR(20) NOT NULL,
    password VARCHAR(100) NOT NULL,
    nickname VARCHAR(50) NOT NULL,学习交流加群风哥QQ113257174
    avatar VARCHAR(255),
    gender TINYINT,
    birthday DATE,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

步骤2：添加索引

-- 添加唯一索引
CREATE UNIQUE INDEX idx_email ON fgedudb.fgedu_users (email);
CREATE UNIQUE INDEX idx_mobile ON fgedudb.fgedu_users (mobile);
CREATE UNIQUE INDEX idx_username ON fgedudb.fgedu_users (username);

-- 添加普通索引
CREATE INDEX idx_created_at ON fgedudb.fgedu_users (created_at);

-- 添加复合索引
CREATE INDEX idx_nickname_gender ON fgedudb.fgedu_users (nickname, gender);

步骤3：测试索引效果

-- 测试登录查询（使用唯一索引）
EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE email = 'user@example.com';
+-------------------------+----------+-----------+------------------------+---------------------------------------+
| id                      | estRows  | task      | access object          | operator info                         |
+-------------------------+----------+-----------+------------------------+---------------------------------------+
| TableReader_7           | 1.00     | root      |                        | data:Selection_6                      |
| └─Selection_6           | 1.00     | cop[tikv] |                        | where:email = 'user@example.com'      |
|   └─IndexScan_5         | 1.00     | cop[tikv] | table:fgedu_users, index:idx_email(email) | range:['user@example.com','user@example.com'], keep order:false |
+-------------------------+----------+-----------+------------------------+---------------------------------------+

-- 测试注册验证（使用唯一索引）
EXPLAIN SELECT id FROM fgedudb.fgedu_users WHERE username = 'testuser' OR email = 'test@example.com' OR mobile = '13800138000';
+-------------------------------+----------+-----------+------------------------+---------------------------------------+
| id                            | estRows  | task      | access object          | operator info                         |
+-------------------------------+----------+-----------+------------------------+---------------------------------------+
| HashAgg_11                    | 1.00     | root      |                        | group by:id, funcs:firstrow(id)      |
| └─Union_12                    | 3.00     | root      |                        |                                       |
|   ├─TableReader_18            | 1.00     | root      |                        | data:Selection_17                     |
|   │ └─Selection_17            | 1.00     | cop[tikv] |                        | where:username = 'testuser'           |
|   │   └─IndexScan_16          | 1.00     | cop[tikv] | table:fgedu_users, index:idx_username(username) | range:['testuser','testuser'], keep order:false |
|   ├─TableReader_24            | 1.00     | root      |                        | data:Selection_23                     |
|   │ └─Selection_23            | 1.00     | cop[tikv] |                        | where:email = 'test@example.com'      |
|   │   └─IndexScan_22          | 1.00     | cop[tikv] | table:fgedu_users, index:idx_email(email) | range:['test@example.com','test@example.com'], keep order:false |
|   └─TableReader_30            | 1.00     | root      |                        | data:Selection_29                     |
|     └─Selection_29            | 1.00     | cop[tikv] |                        | where:mobile = '13800138000'          |
|       └─IndexScan_28          | 1.00     | cop[tikv] | table:fgedu_users, index:idx_mobile(mobile) | range:['13800138000','13800138000'], keep order:false |
+-------------------------------+----------+-----------+------------------------+---------------------------------------+

4.2 订单表索引优化

场景：电商平台订单表，需要优化订单查询、统计和分析的性能。

步骤1：创建订单表

CREATE TABLE fgedudb.fgedu_orders (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    status TINYINT NOT NULL,
    payment_method TINYINT NOT NULL,
    shipping_address VARCHAR(200) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

步骤2：添加索引

-- 添加唯一索引
CREATE UNIQUE INDEX idx_order_no ON fgedudb.fgedu_orders (order_no);

-- 添加普通索引
CREATE INDEX idx_user_id ON fgedudb.fgedu_orders (user_id);
CREATE INDEX idx_created_at ON fgedudb.fgedu_orders (created_at);
CREATE INDEX idx_status ON fgedudb.fgedu_orders (status);

-- 添加复合索引
CREATE INDEX idx_user_status ON fgedudb.fgedu_orders (user_id, status);
CREATE INDEX idx_status_created ON fgedudb.fgedu_orders (status, created_at);

步骤3：测试索引效果

-- 测试用户订单查询
EXPLAIN SELECT * FROM fgedudb.fgedu_orders WHERE user_id = 1001 ORDER BY created_at DESC;
+-------------------------+----------+-----------+------------------------+---------------------------------------+
| id                      | estRows  | task      | access object          | operator info                         |
+-------------------------+----------+-----------+------------------------+---------------------------------------+
| IndexReader_6           | 10.00    | root      |                        | index:IndexScan_5                     |
| └─IndexScan_5           | 10.00    | cop[tikv] | table:fgedu_orders, index:idx_user_id(user_id) | range:[1001,1001], keep order:false   |
+-------------------------+----------+-----------+------------------------+---------------------------------------+

-- 测试订单状态统计
EXPLAIN SELECT status, COUNT(*) FROM fgedudb.fgedu_orders WHERE created_at >= '2024-01-01' GROUP BY status;
+-------------------------------+----------+-----------+------------------------+---------------------------------------+
| id                            | estRows  | task      | access object          | operator info                         |
+-------------------------------+----------+-----------+------------------------+---------------------------------------+
| HashAgg_11                    | 5.00     | root      |                        | group by:status, funcs:count(1)       |
| └─TableReader_12              | 10000.00 | root      |                        | data:Selection_10                     |
|   └─Selection_10              | 10000.00 | cop[tikv] |                        | where:created_at >= '2024-01-01'      |
|     └─IndexScan_9             | 10000.00 | cop[tikv] | table:fgedu_orders, index:idx_created_at(created_at) | range:['2024-01-01 00:00:00',+inf), keep order:false |
+-------------------------------+----------+-----------+------------------------+---------------------------------------+

五、经验总结

5.1 索引设计最佳实践

  • 为每个表设置主键：主键是表的唯一标识，对查询性能至关重要
  • 选择合适的索引类型：根据查询场景选择合适的索引类型
  • 合理设计复合索引：遵循最左前缀原则，将常用列放在前面
  • 定期维护索引：定期分析表统计信息，确保索引使用效率
  • 监控索引使用情况：通过慢查询日志和执行计划分析索引使用情况

5.2 性能优化建议

  • 避免全表扫描：确保查询条件能够使用索引
  • 优化查询语句：避免使用SELECT *，只查询需要的列
  • 合理使用覆盖索引：减少回表操作
  • 控制索引数量：每个表的索引数量建议不超过5个
  • 定期重建索引：对于频繁更新的表，定期重建索引

5.3 常见问题与解决方案

问题 原因 解决方案
索引未被使用 查询条件不匹配索引、索引选择性低、统计信息过时 优化查询语句、重新设计索引、更新统计信息
索引过多 创建了不必要的索引 删除冗余索引，只保留必要的索引
索引碎片 频繁的插入、更新和删除操作 定期重建索引
复合索引失效 违反最左前缀原则 调整查询条件顺序或重新设计索引

5.4 索引使用注意事项

  • 索引列不要使用函数或表达式
  • 避免在索引列上进行类型转换
  • 对于长字符串，考虑使用前缀索引
  • 索引列的值不要为NULL
  • 定期监控索引大小和使用情况

更多视频教程www.fgedu.net.cn

© 2024 TiDB数据库培训文档

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

相关推荐

联系我们

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

微信号：itpux-com

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