tidb教程FG084-TiDB索引失效重建实战

本文档风哥主要介绍TiDB索引失效的原因、检测方法和重建实战，包括索引相关概念、索引失效的原因、影响、索引类型、预防措施、检测步骤、重建方法、优化流程、实战案例和最佳实践等，风哥教程参考TiDB官方文档性能优化相关内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 索引相关概念

TiDB索引相关的基本概念：

# 索引相关概念

## 1. 索引定义
– **索引**：是一种数据结构，用于快速查找表中的数据
– **索引键**：用于构建索引的列
– **索引值**：索引键对应的值
– **索引树**：索引的数据结构，通常是B+树
– **回表**：通过索引找到数据后，需要再到主键索引中查找完整数据的过程

## 2. 索引特性
– **唯一性**：唯一索引确保列值唯一
– **有序性**：索引中的数据是有序存储的
– **选择性**：索引的区分度，选择性越高，索引效果越好
– **覆盖性**：覆盖索引可以直接从索引中获取所需数据，无需回表

## 3. 索引操作
– **创建索引**：为表添加新的索引
– **删除索引**：删除表中的索引
– **重建索引**：删除并重新创建索引
– **分析索引**：更新索引的统计信息

## 4. 索引状态
– **活跃**：正常使用的索引
– **失效**：无法被优化器使用的索引
– **冗余**：重复或不必要的索引
– **碎片化**：索引数据分布不均匀，影响性能

1.2 索引失效的原因

风哥提示：

TiDB索引失效的常见原因：

# 索引失效的原因

## 1. SQL语句问题
– **在索引列上使用函数**：会导致索引失效
“`sql
— 索引失效
SELECT * FROM test.table WHERE LOWER(name) = ‘test’;
“`

– **使用不等于操作符**：可能导致索引失效
“`sql
— 索引可能失效
SELECT * FROM test.table WHERE id != 100;
“`

– **使用LIKE ‘%xxx’**：会导致索引失效
“`sql
— 索引失效
SELECT * FROM test.table WHERE name LIKE ‘%test’;
“`

– **使用OR连接条件**：如果OR两侧的列都没有索引，会导致索引失效
“`sql
— 索引可能失效
SELECT * FROM test.table WHERE id = 100 OR name = ‘test’;
“`

– **类型转换**：索引列的类型与查询条件的类型不匹配
“`sql
— 索引失效（id是整数类型）
SELECT * FROM test.table WHERE id = ‘100’;
“`

## 2. 索引设计问题
– **索引列顺序不合理**：复合索引的顺序不符合查询条件
“`sql
— 复合索引 (name, age)
— 以下查询无法使用索引
SELECT * FROM test.table WHERE age = 30;
“`

– **索引选择性低**：索引列的区分度低，如性别列
“`sql
— 索引选择性低
CREATE INDEX idx_gender ON test.table(gender);
“`

– **索引覆盖不足**：索引无法覆盖查询的所有列，需要回表
“`sql
— 索引 (name)
— 需要回表获取age列
SELECT name, age FROM test.table WHERE name = ‘test’;
“`

– **索引过多**：表上的索引过多，影响写入性能
“`sql
— 过多索引
CREATE INDEX idx_name ON test.table(name);
CREATE INDEX idx_age ON test.table(age);
CREATE INDEX idx_address ON test.table(address);
— 更多索引…
“`

## 3. 统计信息问题
– **统计信息过时**：表的统计信息过时，导致优化器选择错误的执行计划
“`sql
— 统计信息过时
ANALYZE TABLE test.table;
“`

– **统计信息不准确**：统计信息收集不完整或不准确
“`sql
— 重新收集统计信息
ANALYZE TABLE test.table WITH 100 SAMPLES;
“`

## 4. 数据库版本问题
– **版本不支持**：某些索引特性在低版本中不支持
– **版本bug**：特定版本存在索引相关的bug

## 5. 其他原因
– **索引损坏**：索引数据损坏
– **表结构变更**：表结构变更导致索引失效
– **分区表问题**：分区表的索引处理不当

1.3 索引失效的影响

TiDB索引失效会产生以下影响：

索引失效的影响：

查询性能下降：索引失效会导致全表扫描，查询速度变慢
系统负载增加：全表扫描会消耗大量CPU和I/O资源
响应时间变长：查询执行时间增加，应用程序响应缓慢
并发性能下降：大量全表扫描会影响其他查询的执行
存储空间浪费：失效的索引仍然占用存储空间

学习交流加群风哥QQ113257174

维护成本增加：需要额外的维护工作来修复索引问题

1.4 索引类型

TiDB支持的索引类型：

# 索引类型

## 1. 主键索引
– **定义**：表的主键自动创建的索引
– **特点**：唯一且非空，加速主键查询
– **示例**：
“`sql
CREATE TABLE test.table (
id INT PRIMARY KEY,
name VARCHAR(255)
);
“`

## 2. 唯一索引
– **定义**：确保列值唯一的索引
– **特点**：加速唯一值查询，保证数据完整性
– **示例**：
“`sql
CREATE UNIQUE INDEX idx_name ON test.table(name);
“`

## 3. 普通索引
– **定义**：最基本的索引类型
– **特点**：加速查询，不保证唯一性
– **示例**：
“`sql
CREATE INDEX idx_age ON test.table(age);
“`

## 4. 复合索引
– **定义**：多个列组成的索引
– **特点**：加速多列组合查询
– **示例**：
“`sql
CREATE INDEX idx_name_age ON test.table(name, age);
“`

## 5. 前缀索引
– **定义**：对字符串的前缀部分创建索引
– **特点**：减少索引大小，加速字符串前缀查询
– **示例**：
“`sql
CREATE INDEX idx_email ON test.table(email(20));
“`

## 6. 表达式索引
– **定义**：对函数表达式创建索引
– **特点**：加速带函数的查询
– **示例**：
“`sql
CREATE INDEX idx_lower_name ON test.table((LOWER(name)));
“`

## 7. 空间索引
– **定义**：用于空间数据类型的索引
– **特点**：加速空间数据查询
– **示例**：
“`sql
CREATE SPATIAL INDEX idx_location ON test.table(location);
“`

## 8. 分区索引
– **定义**：分区表的索引
– **特点**：加速分区数据查询
– **示例**：
“`sql
CREATE TABLE test.table (
id INT,
created_at DATETIME
) PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);
CREATE INDEX idx_created_at ON test.table(created_at);
“`

风哥提示：索引失效是数据库性能问题的常见原因，需要及时检测和处理，避免影响业务运行。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 预防措施

为了避免TiDB索引失效，生产环境中应采取以下预防措施：

# 预防措施

## 1. 索引设计
– **合理创建索引**：根据查询条件创建合适的索引
– **选择高选择性列**：选择区分度高的列作为索引
– **复合索引顺序**：将选择性高的列放在前面
– **覆盖索引**：创建覆盖查询列的索引
– **避免过多索引**：避免创建不必要的索引

## 2. SQL编写
– **避免在索引列上使用函数**：会导致索引失效
– **使用合适的查询条件**：避免使用LIKE ‘%xxx’、不等于操作符等
– **保持类型一致**：确保查询条件的类型与索引列类型一致
– **使用最左前缀原则**：复合索引的查询条件应使用最左前缀

## 3. 统计信息维护
– **定期分析表**：定期更新表的统计信息
“`sql
ANALYZE TABLE test.table;
“`
– **使用合适的采样率**：根据表大小选择合适的采样率
“`sql
ANALYZE TABLE test.table WITH 100 SAMPLES;
“`
– **监控统计信息**：监控统计信息的更新情况

## 4. 索引监控
– **监控索引使用情况**：监控索引的使用频率
– **监控索引碎片**：监控索引的碎片化程度
– **监控索引大小**：监控索引的存储空间使用
– **设置索引告警**：设置索引相关的告警

## 5. 定期维护
– **定期检查索引**：定期检查索引的有效性
– **重建失效索引**：及时重建失效的索引
– **优化索引结构**：根据业务变化优化索引结构
– **清理冗余索引**：删除不必要的索引

## 6. 版本管理
– **使用稳定版本**：使用稳定的TiDB版本
– **关注版本更新**：关注版本更新中的索引相关修复
– **测试新版本**：在测试环境中测试新版本的索引性能

2.2 索引设计

索引设计是避免索引失效的关键：

# 索引设计

## 1. 设计原则
– **选择性原则**：选择区分度高的列作为索引
– **最小化原则**：索引列的长度应尽可能小
– **覆盖原则**：创建覆盖查询列的索引
– **最左前缀原则**：复合索引的顺序应符合查询条件
– **唯一性原则**：对需要唯一约束的列创建唯一索引

## 2. 设计步骤
– **分析查询模式**：分析应用程序的查询模式
– **识别查询条件**：识别频繁使用的查询条件
– **选择索引列**：根据查询条件选择合适的索引列
– **确定索引类型**：根据查询需求确定索引类型
– **评估索引效果**：评估索引的性能提升效果

## 3. 常见场景索引设计
– **点查询**：对单个列的精确查询，使用单列索引
“`sql
— 点查询
SELECT * FROM test.table WHERE id = 100;
— 索引设计
CREATE INDEX idx_id ON test.table(id);
“`

– **范围查询**：对列的范围查询，使用单列索引
“`sql
— 范围查询
SELECT * FROM test.table WHERE age BETWEEN 18 AND 30;
— 索引设计
CREATE INDEX idx_age ON test.table(age);
“`

– **排序查询**：需要排序的查询，使用索引排序
“`sql
— 排序查询
SELECT * FROM test.table ORDER BY name;
— 索引设计
CREATE INDEX idx_name ON test.table(name);
“`

– **组合查询**：多列组合查询，使用复合索引
“`sql
— 组合查询
SELECT * FROM test.table WHERE name = ‘test’ AND age = 30;
— 索引设计
CREATE INDEX idx_name_age ON test.table(name, age);
“`

– **覆盖查询**：只需要查询部分列，使用覆盖索引
“`sql
— 覆盖查询
SELECT name, age FROM test.table WHERE name = ‘test’;
— 索引设计
CREATE INDEX idx_name_age ON test.table(name, age);
“`

## 4. 索引设计工具
– **TiDB Dashboard**：查看索引使用情况
– **EXPLAIN**：分析SQL执行计划
– **SHOW INDEX**：查看表的索引
– **ANALYZE TABLE**：更新统计信息

2.3 监控配置

生产环境中应配置以下监控项，及时发现索引失效问题：

# 监控配置

## 1. 索引使用监控
– **索引使用率**：监控索引的使用频率
– **全表扫描次数**：监控全表扫描的次数
– **索引扫描次数**：监控索引扫描的次数
– **索引命中率**：监控索引的命中率

## 2. 索引性能监控
– **索引扫描时间**：监控索引扫描的时间
– **索引扫描行数**：监控索引扫描的数据行数
– **索引大小**：监控索引的存储空间使用
– **索引碎片**：监控索引的碎片化程度

## 3. 慢查询监控
– **慢查询中的全表扫描**：监控慢查询中的全表扫描
– **慢查询中的索引使用**：监控慢查询中的索引使用情况
– **慢查询执行计划**：分析慢查询的执行计划

## 4. 告警配置
– **全表扫描告警**：当全表扫描次数超过阈值时告警
– **索引使用率告警**：当索引使用率低于阈值时告警
– **慢查询告警**：当慢查询数量超过阈值时告警
– **索引碎片告警**：当索引碎片率超过阈值时告警

## 5. 监控工具
– **Prometheus**：收集和存储监控数据
– **Grafana**：可视化监控数据
– **TiDB Dashboard**：TiDB自带的监控工具
– **慢查询日志分析工具**：分析慢查询日志

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

3.1 检测步骤

TiDB索引失效的检测步骤：

# 检测步骤

## 1. 查看执行计划
– **步骤1**：使用EXPLAIN分析SQL执行计划
“`sql
EXPLAIN SELECT * FROM test.table WHERE condition;
“`

– **步骤2**：查看执行计划中的访问方式
– TableFullScan：全表扫描，索引失效
– IndexScan：索引扫描，索引有效
– IndexReader：通过索引读取数据

– **步骤3**：分析执行计划中的索引选择
“`sql
EXPLAIN FORMAT=’verbose’ SELECT * FROM test.table WHERE condition;
“`

## 2. 查看索引使用情况
– **步骤1**：查看表的索引
“`sql
SHOW INDEX FROM test.table;
“`

– **步骤2**：查看索引的统计信息
“`sql
SHOW STATS_HEALTHY WHERE table_name = ‘table’;
“`

– **步骤3**：查看索引的使用情况
“`sql
— 查看索引使用统计
SELECT * FROM information_schema.statistics WHERE table_schema = ‘test’ AND table_name = ‘table’;
“`

## 3. 分析慢查询
– **步骤1**：查看慢查询日志
“`bash
tail -f /tidb/log/tidb-slow.log
“`

– **步骤2**：分析慢查询中的索引使用
“`sql
— 查询慢查询中的全表扫描
SELECT * FROM information_schema.slow_query WHERE sql_text LIKE ‘%TableFullScan%’;
“`

– **步骤3**：分析慢查询的执行计划
“`sql
— 对慢查询进行EXPLAIN分析
EXPLAIN SELECT * FROM test.table WHERE condition;
“`

## 4. 检查统计信息
– **步骤1**：查看表的统计信息
“`sql
SHOW STATS_META WHERE table_name = ‘table’;
“`

– **步骤2**：查看列的统计信息
“`sql
SHOW STATS_HISTOGRAMS WHERE table_name = ‘table’;
“`

– **步骤3**：更新统计信息
“`sql
ANALYZE TABLE test.table;
“`

## 5. 检查索引碎片
– **步骤1**：查看表的碎片情况
“`sql
— 查看表的大小和碎片
SELECT table_name, data_length, index_length, data_free
FROM information_schema.tables
WHERE table_schema = ‘test’ AND table_name = ‘table’;
“`

– **步骤2**：分析索引的碎片化程度
“`sql
— 分析索引碎片
ANALYZE TABLE test.table COMPUTE STATISTICS FOR ALL COLUMNS;
“`

3.2 重建方法

TiDB索引重建的方法：

# 重建方法

## 1. 删除并重新创建索引
– **步骤1**：删除旧索引
“`sql
DROP INDEX idx_name ON test.table;
“`

– **步骤2**：创建新索引
“`sql
CREATE INDEX idx_name ON test.table(name);
“`

– **优点**：简单直接
– **缺点**：重建过程中索引不可用

## 2. 使用ALTER TABLE重建索引
– **步骤1**：使用ALTER TABLE重建索引
“`sql
ALTER TABLE test.table DROP INDEX idx_name, ADD INDEX idx_name (name);
“`

– **优点**：原子操作，减少索引不可用时间
– **缺点**：需要锁表，可能影响性能

## 3. 在线重建索引
– **步骤1**：使用在线重建方式
“`sql
— 创建新索引
CREATE INDEX idx_name_new ON test.table(name);
— 删除旧索引
DROP INDEX idx_name ON test.table;
— 重命名新索引
ALTER TABLE test.table RENAME INDEX idx_name_new TO idx_name;
“`

– **优点**：索引始终可用
– **缺点**：需要额外的存储空间

## 4. 重建所有索引
– **步骤1**：重建表的所有索引
“`sql
— 重建表
ALTER TABLE test.table ENGINE=InnoDB;
“`

– **优点**：同时重建所有索引
– **缺点**：需要锁表，可能影响性能

## 5. 使用TiDB工具重建索引
– **步骤1**：使用tiup工具重建索引
“`bash
tiup ctl tidb –host 192.168.1.10 –port 4000 rebuild-index –db test –table table –index idx_name
“`

– **优点**：支持在线重建
– **缺点**：需要TiDB企业版

## 6. 重建索引的注意事项
– **选择合适的时间**：在业务低峰期进行
– **备份数据**：重建前备份数据
– **监控进度**：监控重建过程
– **验证结果**：重建后验证索引是否有效

3.3 优化流程

TiDB索引优化的流程：

# 优化流程

## 1. 识别问题
– **步骤1**：通过监控发现索引问题
– **步骤2**：分析慢查询日志
– **步骤3**：使用EXPLAIN分析执行计划
– **步骤4**：确认索引失效的原因

## 2. 制定方案
– **步骤1**：根据问题原因制定优化方案
– **步骤2**：选择合适的索引类型
– **步骤3**：确定索引重建的方法
– **步骤4**：制定实施计划

## 3. 实施优化
– **步骤1**：在测试环境验证方案
– **步骤2**：选择合适的时间窗口
– **步骤3**：执行索引重建
– **步骤4**：监控重建过程

## 4. 验证结果
– **步骤1**：使用EXPLAIN验证索引是否有效
– **步骤2**：测试查询性能
– **步骤3**：监控系统负载
– **步骤4**：确认业务正常运行

## 5. 持续监控
– **步骤1**：监控索引使用情况
– **步骤2**：定期分析执行计划
– **步骤3**：更新统计信息
– **步骤4**：调整索引策略

风哥提示：索引重建需要谨慎操作，选择合适的时间窗口，避免影响业务运行。from tidb视频:www.itpux.com

Part04-生产案例与实战讲解

4.1 索引失效检测与重建

# 索引失效检测与重建

## 1. 环境信息
– **TiDB版本**：6.1.0
– **表结构**：test.users表，包含1000万行数据
– **索引情况**：name列有索引，但查询未使用
– **操作系统**：Oracle Linux 9.3

## 2. 故障现象
– **查询执行时间**：执行时间超过5秒
– **执行计划**：使用全表扫描
– **系统负载**：数据库CPU使用率高

## 3. 故障分析
– **SQL语句**：
“`sql
SELECT * FROM test.users WHERE LOWER(name) = ‘test’;
“`

– **原因**：在索引列上使用了LOWER函数，导致索引失效

## 4. 解决方案
– **步骤1**：创建表达式索引
“`sql
CREATE INDEX idx_lower_name ON test.users((LOWER(name)));
“`

– **步骤2**：验证索引效果
“`sql
EXPLAIN SELECT * FROM test.users WHERE LOWER(name) = ‘test’;
“`

– **步骤3**：测试执行时间
“`sql
SELECT /*+ time */ * FROM test.users WHERE LOWER(name) = ‘test’;
“`

– **执行时间**：从5秒以上降至0.01秒以下

## 5. 预防措施
– **避免在索引列上使用函数**：会导致索引失效
– **使用表达式索引**：如果需要在索引列上使用函数，创建表达式索引
– **定期检查执行计划**：定期分析SQL执行计划，发现索引失效问题
– **监控慢查询**：监控慢查询中的全表扫描

4.2 复合索引优化

# 复合索引优化

## 1. 环境信息
– **TiDB版本**：6.1.0
– **表结构**：test.orders表，包含500万行数据
– **索引情况**：有复合索引 (customer_id, order_date)
– **操作系统**：Oracle Linux 9.3

## 2. 故障现象
– **查询执行时间**：执行时间超过3秒
– **执行计划**：使用全表扫描
– **系统负载**：数据库CPU使用率高

## 3. 故障分析
– **SQL语句**：
“`sql
SELECT * FROM test.orders WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;
“`

– **原因**：复合索引的顺序是 (customer_id, order_date)，但查询条件只使用了 order_date，不符合最左前缀原则，导致索引失效

## 4. 解决方案
– **步骤1**：创建单列索引
“`sql
CREATE INDEX idx_order_date ON test.orders(order_date);
“`

– **步骤2**：验证索引效果
“`sql
EXPLAIN SELECT * FROM test.orders WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;
“`

– **步骤3**：测试执行时间
“`sql
SELECT /*+ time */ * FROM test.orders WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’ LIMIT 1000;
“`

– **执行时间**：从3秒以上降至0.1秒以下

## 5. 预防措施
– **遵循最左前缀原则**：复合索引的查询条件应使用最左前缀
– **合理设计复合索引**：根据查询模式设计复合索引的顺序
– **创建必要的单列索引**：对于单独使用的列，创建单列索引
– **定期分析查询模式**：根据业务变化调整索引策略

4.3 前缀索引重建

# 前缀索引重建

## 1. 环境信息
– **TiDB版本**：6.1.0
– **表结构**：test.users表，包含1000万行数据
– **索引情况**：email列有前缀索引 (email(20))
– **操作系统**：Oracle Linux 9.3

## 2. 故障现象
– **查询执行时间**：执行时间超过2秒
– **执行计划**：使用索引扫描，但性能不佳
– **系统负载**：数据库CPU使用率较高

## 3. 故障分析
– **SQL语句**：
“`sql
SELECT * FROM test.users WHERE email = ‘user@example.com’;
“`

– **原因**：前缀索引长度不足，导致索引选择性低，需要回表验证

## 4. 解决方案
– **步骤1**：分析email列的长度分布
“`sql
SELECT LENGTH(email), COUNT(*) FROM test.users GROUP BY LENGTH(email) ORDER BY LENGTH(email);
“`

– **步骤2**：重建前缀索引，调整长度
“`sql
— 删除旧索引
DROP INDEX idx_email ON test.users;
— 创建新的前缀索引
CREATE INDEX idx_email ON test.users(email(30));
“`

– **步骤3**：验证索引效果
“`sql
EXPLAIN SELECT * FROM test.users WHERE email = ‘user@example.com’;
“`

– **步骤4**：测试执行时间
“`sql
SELECT /*+ time */ * FROM test.users WHERE email = ‘user@example.com’;
“`

– **执行时间**：从2秒以上降至0.05秒以下

## 5. 预防措施
– **合理设置前缀索引长度**：根据列值的分布设置合适的前缀长度
– **分析列值分布**：分析列值的长度和分布情况
– **平衡索引大小和选择性**：在索引大小和选择性之间取得平衡
– **定期优化前缀索引**：根据数据变化调整前缀索引长度

4.4 索引碎片整理

# 索引碎片整理

## 1. 环境信息
– **TiDB版本**：6.1.0
– **表结构**：test.orders表，包含1000万行数据
– **索引情况**：有多个索引，存在碎片
– **操作系统**：Oracle Linux 9.3

## 2. 故障现象
– **查询执行时间**：执行时间逐渐增加
– **索引大小**：索引大小增长过快
– **系统负载**：I/O操作增加

## 3. 故障分析
– **查看表的碎片情况**：
“`sql
SELECT table_name, data_length, index_length, data_free
FROM information_schema.tables
WHERE table_schema = ‘test’ AND table_name = ‘orders’;
“`

– **结果**：
“`
+————+————-+————–+———–+
| table_name | data_length | index_length | data_free |
+————+————-+————–+———–+
| orders | 1073741824 | 536870912 | 268435456 |
+————+————-+————–+———–+
“`

– **原因**：频繁的插入、更新和删除操作导致索引碎片化

## 4. 解决方案
– **步骤1**：重建表的所有索引
“`sql
— 重建表
ALTER TABLE test.orders ENGINE=InnoDB;
“`

– **步骤2**：验证碎片情况
“`sql
SELECT table_name, data_length, index_length, data_free
FROM information_schema.tables
WHERE table_schema = ‘test’ AND table_name = ‘orders’;
“`

– **结果**：
“`
+————+————-+————–+———–+
| table_name | data_length | index_length | data_free |
+————+————-+————–+———–+
| orders | 1073741824 | 419430400 | 0 |
+————+————-+————–+———–+
“`

– **步骤3**：测试查询性能
“`sql
SELECT /*+ time */ * FROM test.orders WHERE customer_id = 1000 ORDER BY order_date DESC LIMIT 100;
“`

– **执行时间**：从1.5秒以上降至0.3秒以下

## 5. 预防措施
– **定期整理索引碎片**：定期重建索引，减少碎片
– **合理设计表结构**：避免频繁的更新和删除操作
– **使用合适的存储引擎**：选择适合业务场景的存储引擎
– **监控索引碎片**：监控索引的碎片化程度，及时处理

Part05-风哥经验总结与分享

5.1 常见问题与解决方案

TiDB索引失效和重建的常见问题与解决方案：

# 常见问题与解决方案

## 1. 索引失效
– **问题**：SQL语句使用了函数或表达式，导致索引失效
– **解决**：
– 避免在索引列上使用函数
– 创建表达式索引
– 优化SQL语句，移除函数调用

## 2. 最左前缀原则
– **问题**：复合索引的查询条件不符合最左前缀原则
– **解决**：
– 调整查询条件，使用复合索引的最左前缀
– 重新设计复合索引的顺序
– 创建单独的单列索引

## 3. 统计信息过时
– **问题**：统计信息过时，导致优化器选择错误的执行计划
– **解决**：
– 定期更新统计信息
– 使用合适的采样率
– 监控统计信息的更新情况

## 4. 索引碎片
– **问题**：索引碎片化，影响查询性能
– **解决**：
– 定期重建索引
– 使用ALTER TABLE重建表
– 优化数据操作，减少碎片产生

## 5. 索引过多
– **问题**：表上的索引过多，影响写入性能
– **解决**：
– 删除冗余索引
– 合并相似索引
– 评估索引的必要性

## 6. 索引选择性低
– **问题**：索引列的区分度低，索引效果不佳
– **解决**：
– 选择高选择性的列作为索引
– 使用复合索引提高选择性
– 避免对低选择性列创建索引

## 7. 索引覆盖不足
– **问题**：索引无法覆盖查询的所有列，需要回表
– **解决**：
– 创建覆盖索引
– 只查询需要的列
– 优化查询语句

## 8. 索引重建时间长
– **问题**：索引重建时间过长，影响业务
– **解决**：
– 选择业务低峰期进行
– 使用在线重建方式
– 分批重建索引

5.2 最佳实践

TiDB索引管理的最佳实践：

最佳实践：

合理设计索引：根据查询模式设计合适的索引
遵循最左前缀原则：复合索引的查询条件应使用最左前缀
选择高选择性列：选择区分度高的列作为索引
创建覆盖索引：创建覆盖查询列的索引，减少回表
避免过多索引：避免创建不必要的索引，影响写入性能
定期更新统计信息：确保优化器选择正确的执行计划
定期重建索引：减少索引碎片，提高查询性能
监控索引使用情况：及时发现索引失效问题
优化SQL语句：避免使用导致索引失效的语法
测试索引效果：在测试环境验证索引的性能提升

5.3 优化技巧

TiDB索引优化的实用技巧：

# 优化技巧

## 1. 索引设计技巧
– **复合索引顺序**：将选择性高的列放在前面
“`sql
— 优化前：选择性低的列在前
CREATE INDEX idx_gender_age ON test.table(gender, age);
— 优化后：选择性高的列在前
CREATE INDEX idx_age_gender ON test.table(age, gender);
“`

– **前缀索引长度**：根据列值分布设置合适的前缀长度
“`sql
— 分析列值长度
SELECT LENGTH(email), COUNT(*) FROM test.table GROUP BY LENGTH(email);
— 创建合适的前缀索引
CREATE INDEX idx_email ON test.table(email(30));
“`

– **覆盖索引**：创建覆盖查询列的索引
“`sql
— 查询需要name和age列
SELECT name, age FROM test.table WHERE name = ‘test’;
— 创建覆盖索引
CREATE INDEX idx_name_age ON test.table(name, age);
“`

## 2. SQL优化技巧
– **避免在索引列上使用函数**：
“`sql
— 优化前：使用函数导致索引失效
SELECT * FROM test.table WHERE LOWER(name) = ‘test’;
— 优化后：避免使用函数
SELECT * FROM test.table WHERE name = ‘test’;
“`

– **使用最左前缀**：
“`sql
— 复合索引 (name, age)
— 优化前：不使用最左前缀
SELECT * FROM test.table WHERE age = 30;
— 优化后：使用最左前缀
SELECT * FROM test.table WHERE name = ‘test’ AND age = 30;
“`

– **保持类型一致**：
“`sql
— 优化前：类型不一致
SELECT * FROM test.table WHERE id = ‘100’;
— 优化后：类型一致
SELECT * FROM test.table WHERE id = 100;
“`

## 3. 索引维护技巧
– **定期更新统计信息**：
“`sql
— 定期更新统计信息
ANALYZE TABLE test.table;
— 使用合适的采样率
ANALYZE TABLE test.table WITH 100 SAMPLES;
“`

– **定期重建索引**：
“`sql
— 重建单个索引
ALTER TABLE test.table DROP INDEX idx_name, ADD INDEX idx_name (name);
— 重建所有索引
ALTER TABLE test.table ENGINE=InnoDB;
“`

– **监控索引使用情况**：
“`sql
— 查看索引使用统计
SELECT * FROM information_schema.statistics WHERE table_schema = ‘test’ AND table_name = ‘table’;
“`

## 4. 工具使用技巧
– **使用EXPLAIN分析执行计划**：
“`sql
EXPLAIN SELECT * FROM test.table WHERE condition;
EXPLAIN FORMAT=’verbose’ SELECT * FROM test.table WHERE condition;
“`

– **使用TiDB Dashboard**：
– 查看索引使用情况
– 分析慢查询
– 监控索引性能

– **使用tiup工具**：
“`bash
# 重建索引
tiup ctl tidb –host 192.168.1.10 –port 4000 rebuild-index –db test –table table –index idx_name
“`

## 5. 性能调优技巧
– **使用TiFlash加速分析查询**：
“`sql
— 创建TiFlash副本
ALTER TABLE test.table SET TIFLASH REPLICA 1;
— 强制使用TiFlash
SELECT /*+ READ_FROM_STORAGE(TIFLASH[test.table]) */ * FROM test.table;
“`

– **分区表优化**：
“`sql
— 创建分区表
CREATE TABLE test.table (
id INT,
created_at DATETIME
) PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);
“`

– **数据压缩**：
“`sql
— 启用表压缩
ALTER TABLE test.table COMPRESSION = ‘LZ4’;
“`

风哥提示：索引优化是一个持续的过程，需要根据业务变化和数据分布及时调整，才能保持数据库的高性能。更多视频教程www.fgedu.net.cn

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