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

本文档详细介绍TiDB数据库索引设计与优化方法，包括索引类型、设计原则、创建维护、监控诊断等内容。风哥教程参考TiDB官方文档索引设计指南、性能调优手册等内容，适合DBA和开发人员进行TiDB索引优化。

Part01-基础概念与理论知识

1.1 TiDB索引概述

TiDB索引是提高查询性能的重要手段。TiDB支持主键索引、唯一索引、普通索引、组合索引等多种索引类型。索引可以显著减少数据扫描量，提高查询效率。

TiDB索引特点：

主键索引：聚簇索引，数据按主键顺序存储
二级索引：非聚簇索引，存储主键引用
唯一索引：保证字段值唯一性
组合索引：多字段联合索引
表达式索引：基于表达式的索引（TiDB 4.0+）

1.2 索引类型详解

# TiDB支持的索引类型

# 1. 主键索引（Primary Key）
# – 聚簇索引，数据按主键顺序存储
# – 每个表只能有一个主键
# – 推荐使用自增ID或雪花算法ID
CREATE TABLE fgedu_users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL
);

# 2. 唯一索引（Unique Index）
# – 保证字段值唯一
# – 允许NULL值（多个NULL不冲突）
CREATE UNIQUE INDEX uk_username ON fgedu_users(username);

# 3. 普通索引（Normal Index）
# – 加速查询，不保证唯一性
CREATE INDEX idx_email ON fgedu_users(email);

# 4. 组合索引（Composite Index）
# – 多字段联合索引
# – 遵循最左前缀原则
CREATE INDEX idx_status_created ON fgedu_orders(status, created_at);

# 5. 表达式索引（Expression Index）
# – TiDB 4.0+支持
# – 基于表达式的索引
CREATE INDEX idx_func ON fgedu_users((LOWER(username)));

# 6. 不可见索引（Invisible Index）
# – TiDB 8.0+支持
# – 索引存在但优化器不可见
CREATE INDEX idx_test ON fgedu_users(phone) INVISIBLE;

1.3 索引存储原理

TiDB使用TiKV作为存储引擎，索引数据以键值对形式存储。主键索引是聚簇索引，二级索引存储主键值用于回表查询。

风哥提示：理解索引存储原理有助于设计高效索引。二级索引查询需要回表，因此覆盖索引可以显著提升性能。更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 索引设计原则

# 索引设计核心原则

# 原则1：选择性原则
# – 高选择性字段（唯一值多）适合建索引
# – 低选择性字段（如性别、状态）不适合单独建索引

# 计算选择性
SELECT
COUNT(DISTINCT username) / COUNT(*) AS username_selectivity,
COUNT(DISTINCT status) / COUNT(*) AS status_selectivity
FROM fgedu_users;
# 结果：username_selectivity = 1.0（高选择性）
# status_selectivity = 0.001（低选择性）

# 原则2：最左前缀原则
# – 组合索引查询条件必须从最左列开始
# – 索引(a,b,c)可以支持：a、ab、abc查询

# 原则3：覆盖索引原则
# – 查询字段都在索引中，避免回表
# – 覆盖索引可以显著提升性能

# 原则4：控制索引数量
# – 单表索引不超过5个
# – 过多索引影响写入性能

# 原则5：考虑维护成本
# – 索引需要维护，占用存储空间
# – 频繁更新的字段建索引成本高

2.2 索引选择策略

# 索引选择决策树

# 1. 是否需要索引？
# – 数据量 < 1000条：不需要索引 # - 查询频率低：不需要索引 # - 写入频率远高于查询：谨慎建索引 # 2. 选择什么类型的索引？ # - 主键查询：主键索引 # - 唯一约束：唯一索引 # - 范围查询：普通索引 # - 多条件查询：组合索引 # 3. 字段选择优先级 # 高优先级： # - WHERE条件中的等值查询字段 # - JOIN条件字段 # - ORDER BY字段 # - GROUP BY字段 # 中优先级： # - WHERE条件中的范围查询字段 # - 覆盖索引需要的字段 # 低优先级： # - 低选择性字段 # - 频繁更新的字段 # - 大字段（TEXT、BLOB）

2.3 组合索引设计

# 组合索引设计方法

# 1. 确定索引字段顺序
# 原则：等值查询字段在前，范围查询字段在后

# 示例表
CREATE TABLE fgedu_orders (
order_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(10,2),
created_at TIMESTAMP,
INDEX idx_user_status_created (user_id, status, created_at)
);
风哥提示：
# 支持的查询
SELECT * FROM fgedu_orders WHERE user_id = 10001;
SELECT * FROM fgedu_orders WHERE user_id = 10001 AND status = ‘completed’;
SELECT * FROM fgedu_orders WHERE user_id = 10001 AND status = ‘completed’
AND created_at > ‘2024-01-01’;

# 不支持的查询（无法使用索引）
SELECT * FROM fgedu_orders WHERE status = ‘completed’; # 缺少user_id
SELECT * FROM fgedu_orders WHERE created_at > ‘2024-01-01’; # 缺少user_id和status

# 2. 覆盖索引设计
# 查询只访问索引中的字段，避免回表

# 优化前：需要回表
SELECT order_id, amount FROM fgedu_orders WHERE user_id = 10001;

# 优化后：覆盖索引
CREATE INDEX idx_user_amount ON fgedu_orders(user_id, order_id, amount);
# 查询可以直接从索引获取数据，无需回表

生产环境建议：组合索引字段顺序至关重要，错误的顺序会导致索引失效。建议使用EXPLAIN验证索引使用情况。学习交流加群风哥微信: itpux-com

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

3.1 索引创建实战

3.1.1 在线创建索引

# TiDB支持在线DDL，创建索引不阻塞读写

# 1. 创建普通索引
mysql> CREATE INDEX idx_user_id ON fgedu_orders(user_id);
Query OK, 0 rows affected (2.34 sec)

# 2. 创建唯一索引
mysql> CREATE UNIQUE INDEX uk_order_no ON fgedu_orders(order_no);
Query OK, 0 rows affected (1.89 sec)

# 3. 创建组合索引
mysql> CREATE INDEX idx_status_created ON fgedu_orders(status, created_at);
Query OK, 0 rows affected (3.12 sec)

# 5. 创建索引时指定选项
mysql> CREATE INDEX idx_user_id ON fgedu_orders(user_id)
-> COMMENT ‘用户ID索引’
-> INVISIBLE; # 创建不可见索引
Query OK, 0 rows affected (2.34 sec)

# 6. 使用ALTER TABLE创建索引
mysql> ALTER TABLE fgedu_orders ADD INDEX idx_product_id(product_id);
Query OK, 0 rows affected (2.56 sec)

3.1.2 索引创建最佳实践

#!/bin/bash
# tidb-create-index.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 索引创建最佳实践脚本

# 1. 选择低峰期创建索引
echo “建议在业务低峰期创建索引”
echo “当前时间: $(date)”

# 2. 评估索引影响
# – 大表创建索引可能耗时较长
# – 创建过程中会增加系统负载

# 3. 创建索引前检查
# 检查表数据量
mysql -h192.168.1.10 -P4000 -ufgedu -p’fgedu123′ -e ”
SELECT
TABLE_NAME,
TABLE_ROWS,
ROUND(DATA_LENGTH/1024/1024, 2) AS data_size_mb
FROM information_schema.tables
WHERE TABLE_SCHEMA = ‘fgedudb’
AND TABLE_NAME = ‘fgedu_orders’;
”
# 输出：
# +————–+————+—————+
# | TABLE_NAME | TABLE_ROWS | data_size_mb |
# +————–+————+—————+
# | fgedu_orders | 10000000 | 2048.50 |
# +————–+————+—————+

# 4. 创建索引
# 使用pt-online-schema-change或TiDB原生在线DDL

# 5. 验证索引
mysql -h192.168.1.10 -P4000 -ufgedu -p’fgedu123′ -e ”
SHOW INDEX FROM fgedu_orders;
”

# 6. 测试索引效果
mysql -h192.168.1.10 -P4000 -ufgedu -p’fgedu123′ -e ”
EXPLAIN SELECT * FROM fgedu_orders WHERE user_id = 10001;学习交流加群风哥QQ113257174
“

3.2 索引维护实战

3.2.1 索引分析和优化

# 2. 分析索引使用情况
mysql> SELECT
-> INDEX_NAME,
-> ROWS_SELECTED,
-> ROWS_INSERTED,
-> ROWS_DELETED
-> FROM mysql.schema_index_statistics
-> WHERE TABLE_SCHEMA = ‘fgedudb’ AND TABLE_NAME = ‘fgedu_orders’;
+——————-+—————+—————+————–+
| INDEX_NAME | ROWS_SELECTED | ROWS_INSERTED | ROWS_DELETED |
+——————-+—————+—————+————–+
| PRIMARY | 5000000 | 100000 | 5000 |
| idx_user_id | 2000000 | 100000 | 5000 |
| idx_status_created| 50000 | 100000 | 5000 |
+——————-+—————+—————+————–+
3 rows in set (0.01 sec)

# 3. 删除无用索引
# 如果索引很少被使用，可以考虑删除
mysql> DROP INDEX idx_unused ON fgedu_orders;
Query OK, 0 rows affected (0.45 sec)

# 4. 修改索引可见性
mysql> ALTER INDEX idx_test ON fgedu_orders INVISIBLE;
Query OK, 0 rows affected (0.02 sec)

3.3 索引监控方法

# 1. 监控索引使用情况
mysql> SELECT
-> t.TABLE_SCHEMA,
-> t.TABLE_NAME,
-> s.INDEX_NAME,
-> s.ROWS_SELECTED,
-> s.ROWS_INSERTED,
-> s.ROWS_DELETED,
-> ROUND(s.ROWS_SELECTED / (s.ROWS_INSERTED + 1), 2) AS read_write_ratio
-> FROM mysql.schema_index_statistics s
-> JOIN information_schema.tables t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME
-> WHERE t.TABLE_SCHEMA = ‘fgedudb’
-> ORDER BY s.ROWS_SELECTED DESC;
+————–+————–+——————-+—————+—————+————–+——————+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_SELECTED | ROWS_INSERTED | ROWS_DELETED | read_write_ratio |
+————–+————–+——————-+—————+—————+————–+——————+
| fgedudb | fgedu_orders | PRIMARY | 5000000 | 100000 | 5000 | 49.50 |
| fgedudb | fgedu_orders | idx_user_id | 2000000 | 100000 | 5000 | 19.80 |
| fgedudb | fgedu_users | PRIMARY | 1500000 | 50000 | 2000 | 29.41 |
+————–+————–+——————-+—————+—————+————–+——————+
3 rows in set (0.02 sec)

# 2. 查找未使用的索引
mysql> SELECT
-> TABLE_SCHEMA,
-> TABLE_NAME,
-> INDEX_NAME
-> FROM information_schema.statistics s
-> LEFT JOIN mysql.schema_index_statistics i ON s.TABLE_SCHEMA = i.TABLE_SCHEMA
-> AND s.TABLE_NAME = i.TABLE_NAME
-> AND s.INDEX_NAME = i.INDEX_NAME
-> WHERE s.TABLE_SCHEMA = ‘fgedudb’
-> AND s.NON_UNIQUE = 1
-> AND (i.ROWS_SELECTED IS NULL OR i.ROWS_SELECTED < 1000); +--------------+--------------+------------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | +--------------+--------------+------------+ | fgedudb | fgedu_orders | idx_old | +--------------+--------------+------------+ 1 row in set (0.03 sec) # 3. 监控索引大小 mysql> SELECT
-> TABLE_NAME,
-> INDEX_NAME,
-> ROUND(INDEX_LENGTH/1024/1024, 2) AS index_size_mb
-> FROM information_schema.statistics s
-> JOIN information_schema.tables t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME
-> WHERE s.TABLE_SCHEMA = ‘fgedudb’
-> GROUP BY TABLE_NAME, INDEX_NAME
-> ORDER BY index_size_mb DESC;
+————–+——————-+—————+
| TABLE_NAME | INDEX_NAME | index_size_mb |
+————–+——————-+—————+
| fgedu_orders | PRIMARY | 512.50 |
| fgedu_orders | idx_user_id | 256.30 |
| fgedu_orders | idx_status_created| 128.20 |
+————–+——————-+—————+
3 rows in set (0.02 sec)

风哥提示：定期Review索引使用情况，删除无用索引可以节省存储空间并提高写入性能。建议每季度进行一次索引健康检查。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 OLTP场景索引设计

# OLTP场景：电商订单系统
# 特点：高并发、短事务、点查为主

# 表结构设计
CREATE TABLE fgedu_orders (
order_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT ‘订单ID’,
order_no VARCHAR(32) NOT NULL COMMENT ‘订单编号’,
user_id BIGINT NOT NULL COMMENT ‘用户ID’,
product_id BIGINT NOT NULL COMMENT ‘商品ID’,
amount DECIMAL(10,2) NOT NULL COMMENT ‘订单金额’,
status VARCHAR(20) NOT NULL DEFAULT ‘pending’ COMMENT ‘订单状态’,
pay_time TIMESTAMP NULL COMMENT ‘支付时间’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,

— 唯一索引
UNIQUE KEY uk_order_no (order_no),

— 查询索引
KEY idx_user_id (user_id),
KEY idx_product_id (product_id),
KEY idx_status_created (status, created_at),

— 覆盖索引：用户订单列表查询
KEY idx_user_created (user_id, created_at, order_id, amount, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’订单表’;

# 典型查询优化

# 1. 根据订单号查询
SELECT * FROM fgedu_orders WHERE order_no = ‘FG20240409123456’;
— 使用uk_order_no索引，点查

# 2. 查询用户订单列表
SELECT order_id, amount, status, created_at
FROM fgedu_orders
WHERE user_id = 10001
ORDER BY created_at DESC
LIMIT 10;
— 使用idx_user_created覆盖索引，无需回表

# 3. 查询待支付订单
SELECT * FROM fgedu_orders
WHERE status = ‘pending’ AND created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR); -- 使用idx_status_created索引 # 4. 查询用户某段时间的订单 SELECT * FROM fgedu_orders WHERE user_id = 10001 AND created_at BETWEEN '2024-01-01' AND '2024-03-31'; -- 使用idx_user_id索引（回表）或idx_user_created（覆盖索引）

4.2 OLAP场景索引设计

# OLAP场景：数据分析报表系统
# 特点：复杂查询、全表扫描、聚合计算

# 表结构设计
CREATE TABLE fgedu_sales (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
category_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
quantity INT NOT NULL,
region VARCHAR(50) NOT NULL,
sale_date DATE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 分区表设计（更适合OLAP）
CREATE TABLE fgedu_sales_part (
id BIGINT,
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
category_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
quantity INT NOT NULL,
region VARCHAR(50) NOT NULL,
sale_date DATE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pfuture VALUES LESS THAN MAXVALUE
);

# 索引策略
# OLAP场景索引较少，主要依靠：
# 1. 分区裁剪
# 2. TiFlash列存引擎
# 3. 少量关键索引

# 创建必要索引
CREATE INDEX idx_sale_date ON fgedu_sales(sale_date);
CREATE INDEX idx_category_date ON fgedu_sales(category_id, sale_date);

# 典型分析查询
# 使用TiFlash加速
SELECT /*+ read_from_storage(tiflash[fgedu_sales]) */
category_id,
SUM(amount) AS total_amount,
SUM(quantity) AS total_quantity
FROM fgedu_sales
WHERE sale_date BETWEEN ‘2024-01-01’ AND ‘2024-03-31’
GROUP BY category_id;

4.3 HTAP场景索引设计

# HTAP场景：实时风控系统
# 特点：同时处理交易和分析查询

# 表结构设计
CREATE TABLE fgedu_transactions (
transaction_id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
transaction_no VARCHAR(64) NOT NULL,
amount DECIMAL(15,2) NOT NULL,
transaction_type VARCHAR(20) NOT NULL,
risk_score INT DEFAULT 0,
status VARCHAR(20) DEFAULT ‘pending’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

UNIQUE KEY uk_transaction_no (transaction_no),
KEY idx_user_id (user_id),
KEY idx_created_at (created_at),
KEY idx_risk_score (risk_score, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 添加TiFlash副本
ALTER TABLE fgedu_transactions SET TIFLASH REPLICA 1;

# 查询路由策略

# 1. TP查询：使用TiKV
SELECT * FROM fgedu_transactions
WHERE user_id = 10001
ORDER BY created_at DESC
LIMIT 10;
— 自动路由到TiKV

# 2. AP查询：使用TiFlash
SELECT /*+ read_from_storage(tiflash[fgedu_transactions]) */
transaction_type,
COUNT(*) AS cnt,
SUM(amount) AS total_amount,
AVG(risk_score) AS avg_risk
FROM fgedu_transactions
WHERE created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY transaction_type;
— 强制路由到TiFlash

# 3. 智能引擎选择
SET SESSION tidb_isolation_read_engines = “tikv,tiflash”;
— TiDB优化器自动选择最优引擎

风哥提示：HTAP场景需要平衡TP和AP的需求。关键TP查询使用TiKV索引，复杂AP查询使用TiFlash。合理设置TiFlash副本数，建议1-2个副本。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 索引设计最佳实践

先设计后实施：在表设计阶段就规划好索引
从小开始：先创建核心索引，根据查询需求逐步添加
避免过度索引：单表索引不超过5个
定期Review：每季度检查索引使用情况
文档化：记录每个索引的设计原因和使用场景

5.2 常见错误与避免

# 常见错误1：在低频字段上建索引
# 错误：在status字段（只有3个值）上单独建索引
CREATE INDEX idx_status ON fgedu_orders(status);
# 优化：作为组合索引的第一个字段
CREATE INDEX idx_status_created ON fgedu_orders(status, created_at);

# 常见错误2：忽略最左前缀原则
# 错误：组合索引(a,b,c)，但查询只用b和c
# 优化：调整字段顺序或创建新索引

# 常见错误3：大字段建索引
# 错误：在TEXT字段上建索引
CREATE INDEX idx_content ON fgedu_articles(content); — 报错
# 优化：使用前缀索引或全文索引
CREATE INDEX idx_content ON fgedu_articles(content(100));

# 常见错误4：索引字段参与运算
# 错误：WHERE YEAR(created_at) = 2024
# 优化：使用范围查询
WHERE created_at >= ‘2024-01-01’ AND created_at < '2025-01-01' # 常见错误5：频繁更新的字段建索引 # 问题：更新last_login_time需要维护索引 # 优化：评估是否必要，或降低更新频率

5.3 索引设计检查清单

#!/bin/bash
# tidb-index-checklist.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# TiDB索引设计检查清单

echo “=== TiDB索引设计检查清单 ===”

# 1. 基础检查
echo “[ ] 表是否有主键？”
echo “[ ] 主键是否为自增ID或雪花算法ID？”
echo “[ ] 是否有唯一约束字段需要唯一索引？”

# 2. 查询检查
echo “[ ] WHERE条件字段是否有索引支持？”
echo “[ ] JOIN条件字段是否有索引？”
echo “[ ] ORDER BY字段是否有索引？”
echo “[ ] GROUP BY字段是否有索引？”

# 3. 组合索引检查
echo “[ ] 组合索引字段顺序是否正确？”
echo “[ ] 是否遵循等值在前、范围在后的原则？”
echo “[ ] 是否有覆盖索引支持常见查询？”

# 4. 数量检查
echo “[ ] 单表索引数量是否不超过5个？”
echo “[ ] 是否有无用索引需要清理？”

# 5. 性能检查
echo “[ ] 是否使用EXPLAIN验证索引效果？”
echo “[ ] 是否监控索引使用情况？”
echo “[ ] 是否定期分析表和索引统计信息？”

# 6. 维护检查
echo “[ ] 大表加索引是否选择低峰期？”
echo “[ ] 是否有索引创建和删除的变更流程？”

echo “=== 检查完成 ===”

# 执行检查脚本
# mysql -h192.168.1.10 -P4000 -ufgedu -p’fgedu123′ -e ”
# SELECT
# TABLE_NAME,
# COUNT(*) AS index_count
# FROM information_schema.statistics
# WHERE TABLE_SCHEMA = ‘fgedudb’
# GROUP BY TABLE_NAME
# HAVING index_count > 5;
# “

风哥提示：索引设计是数据库性能优化的核心。建议建立索引设计规范，在开发阶段就进行索引审核，避免上线后出现性能问题。from tidb视频:www.itpux.com

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