TiDB SQL 优化

执行计划分析

查看执行计划

使用 EXPLAIN 或 EXPLAIN ANALYZE 语句查看执行计划：

EXPLAIN

sql

EXPLAIN SELECT * FROM users WHERE id = 1;

EXPLAIN ANALYZE

sql

EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

执行计划结构

执行计划包含以下关键信息：

id：执行计划节点 ID
estRows：估计的行数
actRows：实际执行的行数（仅 EXPLAIN ANALYZE 显示）
task：任务类型（root、cop、table_reader 等）
access object：访问的对象（表、索引等）
operator info：操作符信息
execution info：执行信息（仅 EXPLAIN ANALYZE 显示）
memory：内存使用情况（仅 EXPLAIN ANALYZE 显示）
disk：磁盘使用情况（仅 EXPLAIN ANALYZE 显示）

常见执行计划操作符

TableReader

从 TiKV 读取数据，是分布式执行计划的典型操作符。

IndexReader

通过索引读取数据，比全表扫描更高效。

IndexRangeScan

通过索引范围扫描获取数据。

TableFullScan

全表扫描，效率较低，应尽量避免。

HashJoin

哈希连接，适用于大表连接。

MergeJoin

归并连接，要求输入数据已排序。

IndexJoin

索引连接，利用索引加速连接操作。

Sort

排序操作，可能消耗大量资源。

Limit

限制结果集数量。

Aggregation

聚合操作，包括 COUNT、SUM、AVG 等。

执行计划优化建议

避免全表扫描（TableFullScan），尽量使用索引
优化连接操作，选择合适的连接算法
减少排序操作（Sort），利用索引排序
优化聚合操作，考虑使用覆盖索引
检查估计行数与实际行数的差异，及时更新统计信息

索引优化

索引类型

主键索引

TiDB 使用主键作为 RowID，是表的默认索引。

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

唯一索引

确保列或列组合的唯一性。

sql

CREATE UNIQUE INDEX idx_email ON users(email);

普通索引

最常用的索引类型，用于加速查询。

sql

CREATE INDEX idx_age ON users(age);

联合索引

多个列组成的索引，遵循最左前缀原则。

sql

CREATE INDEX idx_name_age ON users(name, age);

覆盖索引

索引包含查询所需的所有列，无需回表查询。

sql

-- 对于查询 SELECT id, name FROM users WHERE age = 25
CREATE INDEX idx_age_id_name ON users(age, id, name);

索引设计原则

1. 最左前缀原则

联合索引遵循最左前缀原则，查询时从索引的最左列开始匹配。

示例：

索引：idx_name_age (name, age)
可使用索引的查询：
- WHERE name = '张三'
- WHERE name = '张三' AND age = 25
- WHERE name LIKE '张%'
无法使用索引的查询：
- WHERE age = 25
- WHERE name LIKE '%三'

2. 选择高选择性的列作为索引

索引的选择性是指不重复值的比例，选择性越高，索引效果越好。

sql

-- 对于性别列，选择性较低，不适合作为索引
CREATE INDEX idx_gender ON users(gender); -- 不推荐

-- 对于邮箱列，选择性较高，适合作为索引
CREATE INDEX idx_email ON users(email); -- 推荐

3. 考虑查询场景

根据实际查询场景设计索引，优先为 WHERE 条件、ORDER BY、GROUP BY 和 JOIN 条件的列创建索引。

4. 避免过多索引

每个索引都会占用存储空间，并增加写操作的开销。建议每个表的索引数量不超过 5 个。

5. 使用覆盖索引

覆盖索引可以避免回表查询，提高查询效率。

索引使用优化

1. 避免索引失效

隐式类型转换：确保查询条件与列类型一致

sql

-- 错误：id 是 INT 类型，使用字符串查询会导致索引失效
SELECT * FROM users WHERE id = '1';

-- 正确
SELECT * FROM users WHERE id = 1;

使用函数或表达式：避免在索引列上使用函数或表达式

sql

-- 错误：在索引列上使用函数，导致索引失效
SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';

-- 正确：将条件转换为范围查询
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

NOT IN 和 != 操作：这些操作可能导致索引失效

sql

-- 可能导致索引失效
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

-- 可能导致索引失效
SELECT * FROM users WHERE status != 'active';

LIKE 通配符开头：LIKE 以通配符开头会导致索引失效

sql

-- 错误：以通配符开头，导致索引失效
SELECT * FROM users WHERE name LIKE '%三';

-- 正确：以常量开头，可以使用索引
SELECT * FROM users WHERE name LIKE '张%';

2. 强制使用索引

在某些情况下，优化器可能不会选择最优索引，可以使用 USE INDEX 或 FORCE INDEX 提示强制使用特定索引。

sql

SELECT * FROM users FORCE INDEX (idx_age) WHERE age > 25;

3. 查看索引使用情况

使用 SHOW INDEXES 命令查看表的索引信息：

sql

SHOW INDEXES FROM users;

统计信息管理

统计信息收集方式

自动收集

TiDB 会自动收集统计信息：

表的行数变化超过 20% 时
执行 ANALYZE TABLE 命令时

手动收集

使用 ANALYZE TABLE 命令手动收集统计信息：

sql

-- 收集全表统计信息
ANALYZE TABLE users;

-- 收集指定列的统计信息
ANALYZE TABLE users COLUMNS name, age;

-- 收集指定索引的统计信息
ANALYZE TABLE users INDEX idx_name_age;

统计信息查看

查看表的统计信息

sql

SHOW STATS_META WHERE table_name = 'users';

查看列的统计信息

sql

SHOW STATS_HISTOGRAMS WHERE table_name = 'users' AND column_name = 'age';

查看索引的统计信息

sql

SHOW STATS_HEALTHY WHERE table_name = 'users';

统计信息优化建议

定期更新统计信息，特别是在数据量变化较大时
对于大型表，可以只收集关键列和索引的统计信息
监控统计信息的健康度，健康度低于 60% 时建议更新
在执行复杂查询前，先更新相关表的统计信息

SQL 改写优化

1. 避免 SELECT *

只查询需要的列，减少数据传输和 I/O 开销。

sql

-- 错误：查询所有列
SELECT * FROM users WHERE age > 25;

-- 正确：只查询需要的列
SELECT id, name FROM users WHERE age > 25;

2. 优化 COUNT 查询

对于 COUNT(*)，TiDB 会选择最优索引
对于 COUNT(column)，需要考虑列的空值情况

sql

-- 高效：使用主键索引
SELECT COUNT(*) FROM users;

-- 低效：需要扫描整个表
SELECT COUNT(name) FROM users;

3. 优化 LIMIT 分页

使用主键或唯一索引进行分页，避免偏移量过大。

sql

-- 低效：偏移量过大，需要扫描大量数据
SELECT * FROM users ORDER BY id LIMIT 100000, 10;

-- 高效：使用主键过滤
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;

4. 优化 OR 查询

对于 OR 查询，考虑使用 UNION ALL 替代，或确保所有 OR 条件都有索引。

sql

-- 可能低效：如果只有部分列有索引
SELECT * FROM users WHERE name = '张三' OR age = 25;

-- 高效：使用 UNION ALL，假设 name 和 age 都有索引
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE age = 25 AND name != '张三';

5. 优化 IN 查询

对于大 IN 列表，考虑使用 JOIN 替代。

sql

-- 低效：大 IN 列表
SELECT * FROM users WHERE id IN (1, 2, 3, ..., 1000);

-- 高效：使用 JOIN
SELECT u.* FROM users u JOIN temp_ids t ON u.id = t.id;

6. 优化 JOIN 查询

确保 JOIN 条件有索引
小表驱动大表
选择合适的 JOIN 算法

sql

-- 高效：JOIN 条件有索引
SELECT u.*, o.* FROM users u JOIN orders o ON u.id = o.user_id;

-- 低效：JOIN 条件无索引
SELECT u.*, o.* FROM users u JOIN orders o ON u.name = o.user_name;

7. 优化 GROUP BY 查询

确保 GROUP BY 列有索引
考虑使用覆盖索引
避免在 GROUP BY 列上使用函数

sql

-- 高效：GROUP BY 列有索引
SELECT age, COUNT(*) FROM users GROUP BY age;

-- 低效：GROUP BY 列无索引，需要排序
SELECT DATE(create_time), COUNT(*) FROM users GROUP BY DATE(create_time);

事务优化

1. 减小事务范围

事务越短，锁定资源的时间越短，减少锁竞争。

sql

-- 低效：长事务
START TRANSACTION;
-- 执行多个操作
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- 执行其他耗时操作
SLEEP(10);
UPDATE orders SET status = 'paid' WHERE user_id = 1;
COMMIT;

-- 高效：短事务
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE orders SET status = 'paid' WHERE user_id = 1;
COMMIT;
-- 执行其他耗时操作
SLEEP(10);

2. 避免大事务

大事务会占用大量资源，增加回滚风险。

解决方法：

将大事务拆分为多个小事务
使用批量操作
避免在事务中执行大量查询

3. 合理设置隔离级别

根据业务需求选择合适的隔离级别，避免过度使用高隔离级别。

sql

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

4. 避免死锁

保持一致的锁顺序
减少锁的持有时间
使用 SELECT ... FOR UPDATE NOWAIT 避免锁等待

sql

-- 可能导致死锁
-- 事务1
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- 事务2
UPDATE users SET balance = balance - 100 WHERE id = 2;
UPDATE users SET balance = balance + 100 WHERE id = 1;

-- 避免死锁：保持一致的锁顺序
-- 事务1和事务2都先锁id=1，再锁id=2

分区表优化

分区类型

RANGE 分区

根据列值的范围进行分区。

sql

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_time DATETIME,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(order_time)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

LIST 分区

根据列值的列表进行分区。

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    region VARCHAR(20)
) PARTITION BY LIST (region) (
    PARTITION p_north VALUES IN ('北京', '天津', '河北'),
    PARTITION p_south VALUES IN ('上海', '浙江', '江苏')
);

HASH 分区

根据列值的哈希值进行分区。

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) PARTITION BY HASH (id) PARTITIONS 4;

分区表优化建议

选择合适的分区键，避免跨分区查询
定期清理历史分区数据
对于大型表，使用分区表可以提高查询效率
避免过多分区，建议每个表的分区数不超过 100 个

SQL 优化工具

TiDB Dashboard

TiDB Dashboard 提供了 SQL 优化相关的功能：

Top SQL：查看执行时间最长的 SQL
慢查询日志：查看慢查询详情
执行计划可视化：直观展示执行计划
SQL 顾问：提供优化建议

EXPLAIN FORMAT=dot

生成可视化的执行计划图。

sql

EXPLAIN FORMAT=dot SELECT * FROM users WHERE age > 25;

tidb-ansible

TiDB 官方提供的部署和管理工具，包含 SQL 优化相关的脚本。

第三方工具

Percona Toolkit：提供了多种 SQL 优化工具
pt-query-digest：分析慢查询日志
pt-index-usage：分析索引使用情况

SQL 优化最佳实践

1. 定期分析慢查询日志

配置合适的慢查询阈值
定期分析慢查询日志
针对慢查询进行优化

2. 监控 SQL 性能

监控 QPS、TPS、查询延迟等指标
监控慢查询数量
监控索引使用情况

3. 优化数据模型

合理设计表结构
选择合适的数据类型
避免冗余数据
设计合适的索引

4. 优化应用程序

使用连接池
减少数据库连接数
避免频繁的小查询
批量操作数据

5. 测试优化效果

在测试环境验证优化效果
使用基准测试工具（如 sysbench）
比较优化前后的性能差异

6. 持续优化

SQL 优化是一个持续的过程
随着数据量和查询模式的变化，定期重新评估和优化
关注 TiDB 新版本的优化特性

常见 SQL 优化案例

案例 1：全表扫描优化

问题：

sql

SELECT * FROM users WHERE age = 25;
-- 执行计划显示全表扫描（TableFullScan）

优化方案：

sql

-- 创建索引
CREATE INDEX idx_age ON users(age);

-- 再次执行查询，使用索引扫描（IndexRangeScan）
SELECT * FROM users WHERE age = 25;

案例 2：联合索引优化

问题：

sql

SELECT * FROM users WHERE name = '张三' AND age = 25;
-- 只有 name 列有索引，age 列无法使用索引

优化方案：

sql

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 再次执行查询，使用联合索引
SELECT * FROM users WHERE name = '张三' AND age = 25;

案例 3：覆盖索引优化

问题：

sql

SELECT id, name FROM users WHERE age = 25;
-- 需要回表查询

优化方案：

sql

-- 创建覆盖索引
CREATE INDEX idx_age_id_name ON users(age, id, name);

-- 再次执行查询，使用覆盖索引，无需回表
SELECT id, name FROM users WHERE age = 25;

案例 4：慢查询优化

问题：

sql

SELECT * FROM orders WHERE DATE(create_time) = '2023-01-01';
-- 执行时间超过 1 秒

优化方案：

sql

-- 改写查询，使用范围查询
SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

-- 创建索引
CREATE INDEX idx_create_time ON orders(create_time);

常见问题（FAQ）

Q1: 如何识别需要优化的 SQL？

A1: 可以通过以下方式识别：

查看慢查询日志
使用 TiDB Dashboard 的 Top SQL 功能
监控查询延迟指标
分析执行计划

Q2: 为什么优化器选择了低效的执行计划？

A2: 可能的原因：

统计信息不准确
索引选择性低
查询复杂，优化器无法找到最优计划
优化器版本限制

Q3: 如何更新统计信息？

A3: 使用 ANALYZE TABLE 命令：

sql

ANALYZE TABLE table_name;

Q4: 索引越多越好吗？

A4: 不是，索引越多，写操作的开销越大，建议每个表的索引数量不超过 5 个。

Q5: 如何选择合适的 JOIN 算法？

A5: TiDB 优化器会自动选择合适的 JOIN 算法：

小表连接大表时，使用 Hash Join
连接列已排序时，使用 Merge Join
其中一个表的连接列有索引时，使用 Index Join

Q6: 如何优化大事务？

A6: 可以通过以下方式优化：

减小事务范围
拆分为多个小事务
使用批量操作
避免在事务中执行大量查询

Q7: 如何优化分页查询？

A7: 对于大偏移量的分页查询，使用主键过滤替代 LIMIT OFFSET：

sql

-- 低效
SELECT * FROM users ORDER BY id LIMIT 100000, 10;

-- 高效
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;

Q8: 如何监控索引使用情况？

A8: 可以通过以下方式监控：

使用 SHOW INDEXES FROM table_name 查看索引信息
分析慢查询日志，查看是否使用了索引
使用 TiDB Dashboard 的索引使用统计

Q9: 如何处理统计信息不准确的问题？

A9: 可以通过以下方式处理：

手动执行 ANALYZE TABLE 更新统计信息
调整统计信息收集的采样率
对于大型表，只收集关键列和索引的统计信息

Q10: 如何优化 GROUP BY 查询？

A10: 可以通过以下方式优化：

确保 GROUP BY 列有索引
考虑使用覆盖索引
避免在 GROUP BY 列上使用函数
对于大表，考虑使用分区表

Q11: 如何优化 OR 查询？

A11: 可以通过以下方式优化：

使用 UNION ALL 替代 OR
确保所有 OR 条件都有索引
对于复杂的 OR 查询，考虑拆分为多个简单查询

Q12: 如何优化 IN 查询？

A12: 可以通过以下方式优化：

对于小 IN 列表，直接使用 IN
对于大 IN 列表，考虑使用 JOIN 替代
确保 IN 条件的列有索引

Q13: 如何优化 UPDATE 和 DELETE 语句？

A13: 可以通过以下方式优化：

确保 WHERE 条件有索引
批量操作，避免一次性修改大量数据
减小事务范围

Q14: 如何优化 LIKE 查询？

A14: 可以通过以下方式优化：

避免以通配符开头
使用全文索引替代 LIKE 查询（对于文本搜索）
考虑使用 Elasticsearch 等专门的搜索引擎

Q15: 如何验证 SQL 优化效果？

A15: 可以通过以下方式验证：

比较优化前后的执行时间
比较优化前后的执行计划
使用基准测试工具（如 sysbench）进行压力测试
监控生产环境的性能指标变化

TiDB SQL 优化 ​

执行计划分析 ​

查看执行计划 ​

EXPLAIN ​

EXPLAIN ANALYZE ​

执行计划结构 ​

常见执行计划操作符 ​

TableReader ​

IndexReader ​

IndexRangeScan ​

TableFullScan ​

HashJoin ​

MergeJoin ​

IndexJoin ​

Sort ​

Limit ​

Aggregation ​

执行计划优化建议 ​

索引优化 ​

索引类型 ​

主键索引 ​

唯一索引 ​

普通索引 ​

联合索引 ​

覆盖索引 ​

索引设计原则 ​

1. 最左前缀原则 ​

2. 选择高选择性的列作为索引 ​

3. 考虑查询场景 ​

4. 避免过多索引 ​

5. 使用覆盖索引 ​

索引使用优化 ​

1. 避免索引失效 ​

2. 强制使用索引 ​

3. 查看索引使用情况 ​

统计信息管理 ​

统计信息收集方式 ​

自动收集 ​

手动收集 ​

统计信息查看 ​

查看表的统计信息 ​

查看列的统计信息 ​

查看索引的统计信息 ​

统计信息优化建议 ​

SQL 改写优化 ​

1. 避免 SELECT * ​

2. 优化 COUNT 查询 ​

3. 优化 LIMIT 分页 ​

4. 优化 OR 查询 ​

5. 优化 IN 查询 ​

6. 优化 JOIN 查询 ​

7. 优化 GROUP BY 查询 ​

事务优化 ​

1. 减小事务范围 ​

2. 避免大事务 ​

3. 合理设置隔离级别 ​

4. 避免死锁 ​

分区表优化 ​

分区类型 ​

RANGE 分区 ​

LIST 分区 ​

HASH 分区 ​

分区表优化建议 ​

SQL 优化工具 ​

TiDB Dashboard ​

EXPLAIN FORMAT=dot ​

tidb-ansible ​

第三方工具 ​

SQL 优化最佳实践 ​

1. 定期分析慢查询日志 ​

2. 监控 SQL 性能 ​

3. 优化数据模型 ​

4. 优化应用程序 ​

5. 测试优化效果 ​

6. 持续优化 ​

常见 SQL 优化案例 ​

案例 1：全表扫描优化 ​

案例 2：联合索引优化 ​

案例 3：覆盖索引优化 ​

案例 4：慢查询优化 ​

TiDB SQL 优化

执行计划分析

查看执行计划

EXPLAIN

EXPLAIN ANALYZE

执行计划结构

常见执行计划操作符

TableReader

IndexReader

IndexRangeScan

TableFullScan

HashJoin

MergeJoin

IndexJoin

Sort

Limit

Aggregation

执行计划优化建议

索引优化

索引类型

主键索引

唯一索引

普通索引

联合索引

覆盖索引

索引设计原则

1. 最左前缀原则

2. 选择高选择性的列作为索引

3. 考虑查询场景

4. 避免过多索引

5. 使用覆盖索引

索引使用优化

1. 避免索引失效

2. 强制使用索引

3. 查看索引使用情况

统计信息管理

统计信息收集方式

自动收集

手动收集

统计信息查看

查看表的统计信息

查看列的统计信息

查看索引的统计信息

统计信息优化建议

SQL 改写优化

1. 避免 SELECT *

2. 优化 COUNT 查询

3. 优化 LIMIT 分页

4. 优化 OR 查询

5. 优化 IN 查询

6. 优化 JOIN 查询

7. 优化 GROUP BY 查询

事务优化

1. 减小事务范围

2. 避免大事务

3. 合理设置隔离级别

4. 避免死锁

分区表优化

分区类型

RANGE 分区

LIST 分区

HASH 分区

分区表优化建议

SQL 优化工具

TiDB Dashboard

EXPLAIN FORMAT=dot

tidb-ansible

第三方工具

SQL 优化最佳实践

1. 定期分析慢查询日志

2. 监控 SQL 性能

3. 优化数据模型

4. 优化应用程序

5. 测试优化效果

6. 持续优化

常见 SQL 优化案例

案例 1：全表扫描优化

案例 2：联合索引优化

案例 3：覆盖索引优化

案例 4：慢查询优化