本文档风哥主要介绍TiDB查询执行计划分析相关知识，包括执行计划基础、TiDB执行计划特性、执行算子、执行计划分析方法、执行计划优化策略、常见问题与解决方法、执行计划生成、执行计划分析、性能调优等内容，风哥教程参考TiDB官方文档执行计划章节，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 执行计划基础
执行计划的核心概念：
- 执行计划：数据库执行SQL语句的具体步骤和方式。
- 优化器：生成执行计划的组件，负责选择最优的执行路径。
- 算子：执行计划的基本执行单元，如TableScan、IndexScan、Join等。
- 执行模式：执行计划的执行方式，如root、cop[tikv]等。
- 估计行数：优化器估计的结果集大小。
- 了解SQL语句的执行方式
- 识别性能瓶颈
- 优化SQL语句
- 提高查询性能
1.2 TiDB执行计划特性
TiDB的执行计划特性：
## 分布式执行
– 执行计划在TiDB和TiKV之间分布式执行
– TiDB负责SQL解析和执行协调
– TiKV负责数据存储和计算
## 基于代价的优化
– 使用基于代价的优化器（CBO）
– 考虑CPU、内存、I/O等因素
– 选择最优的执行计划
## 执行计划缓存
– 缓存准备好的执行计划
– 提高查询性能
– 减少SQL解析和优化开销
## 并行执行
– 支持并行执行查询
– 提高查询性能
– 充分利用系统资源
风哥提示：
## 执行计划可视化
– 通过TiDB Dashboard可视化执行计划
– 便于分析和优化
1.3 执行算子
TiDB常见的执行算子：
## 1. 扫描算子
– TableScan：全表扫描
– IndexScan：索引扫描
– IndexRangeScan：索引范围扫描
– TableReader：从TiKV读取数据到TiDB
– IndexReader：从TiKV读取索引数据到TiDB
## 2. 连接算子
– HashJoin：哈希连接
– MergeJoin：合并连接
– IndexJoin：索引连接
– NestedLoopJoin：嵌套循环连接
## 3. 聚合算子
– HashAgg：哈希聚合
– StreamAgg：流式聚合
– Window：窗口函数
## 4. 排序算子
– Sort：排序
– TopN：取前N行
## 5. 其他算子
– Selection：过滤
– Projection：投影
– Limit：限制结果集大小
– Offset：偏移量
Part02-生产环境规划与建议
2.1 执行计划分析方法
执行计划分析方法：
## 1. 使用EXPLAIN命令
– 查看执行计划
– 了解SQL语句的执行方式
– 识别性能瓶颈
## 2. 使用EXPLAIN ANALYZE命令
– 查看实际执行计划
– 了解实际执行时间和行数
– 验证优化效果
## 3. 使用TiDB Dashboard
– 可视化执行计划
– 查看详细的执行统计信息
– 分析执行计划的性能瓶颈
## 4. 分析慢查询日志
– 识别慢查询
– 分析慢查询的执行计划
– 优化慢查询
## 5. 使用Performance Schema
– 监控SQL执行情况
– 分析执行计划的性能
– 识别性能瓶颈
2.2 执行计划优化策略
执行计划优化策略：
## 1. 优化扫描方式
– 避免全表扫描，使用索引扫描
– 为查询列创建合适的索引
– 使用覆盖索引，避免回表
## 2. 优化连接方式
– 小表驱动大表
– 为连接列创建索引
– 选择合适的连接算法
## 3. 优化聚合方式
– 为聚合列创建索引
– 使用合适的聚合算法
– 避免在聚合函数中使用复杂表达式
## 4. 优化排序方式学习交流加群风哥QQ113257174
– 为排序列创建索引
– 避免使用ORDER BY RAND()
– 限制排序结果集大小
## 5. 优化子查询
– 用JOIN替代子查询
– 避免相关子查询
– 使用临时表缓存子查询结果
## 6. 优化执行计划缓存
– 使用参数化查询
– 避免使用动态SQL
– 合理设置执行计划缓存大小
2.3 常见问题与解决方法
执行计划常见问题与解决方法：
## 1. 全表扫描
– 问题：查询使用全表扫描，性能差
– 解决方法：为查询列创建索引，使用WHERE条件过滤数据
## 2. 索引失效
– 问题：索引没有被使用
– 解决方法：避免在索引列上使用函数，使用正确的查询条件
## 3. 连接性能差
– 问题：连接操作执行时间长
– 解决方法：小表驱动大表，为连接列创建索引
## 4. 排序性能差
– 问题：排序操作执行时间长
– 解决方法：为排序列创建索引，限制排序结果集大小
## 5. 聚合性能差
– 问题：聚合操作执行时间长
– 解决方法：为聚合列创建索引，使用合适的聚合算法
## 6. 执行计划不稳定
– 问题：相同的SQL语句生成不同的执行计划
– 解决方法：更新统计信息，使用SQL绑定
Part03-生产环境项目实施方案
3.1 执行计划生成
3.1.1 查看执行计划
## 1. 使用EXPLAIN命令
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE age > 30 AND gender = ‘male’;”
## 2. 使用EXPLAIN ANALYZE命令
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “EXPLAIN ANALYZE SELECT * FROM fgedudb.fgedu_users WHERE age > 30 AND gender = ‘male’;”
## 3. 使用TiDB Dashboard
# 访问 http://192.168.1.100:10080/dashboard
# 查看SQL语句的执行计划
## 4. 查看慢查询的执行计划
$ tail -f /tidb/app/tidb-deploy/tidb-4000/log/tidb-slow.log
3.1.2 执行计划缓存
## 1. 查看执行计划缓存状态
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “SHOW GLOBAL VARIABLES LIKE ‘prepared_plan_cache%’;”
## 2. 配置执行计划缓存
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “SET GLOBAL prepared_plan_cache.enabled = 1; SET GLOBAL prepared_plan_cache.capacity = 1000;”
## 3. 使用参数化查询
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “PREPARE stmt FROM ‘SELECT * FROM fgedudb.fgedu_users WHERE age > ?’; EXECUTE stmt USING 30;”
3.2 执行计划分析
3.2.1 执行计划结构分析
## 1. 执行计划示例
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE age > 30 AND gender = ‘male’;”
+————————-+———+———–+——————————-+——————————–+ | id | estRows | task | access object | operator info | +————————-+———+———–+——————————-+——————————–+ | TableReader_7 | 33333.33 | root | | data:Selection_6 | | └─Selection_6 | 33333.33 | cop[tikv] | | gt(fgedu_users.age, 30), eq(fgedu_users.gender, “male”) | | └─TableFullScan_5 | 100000.00 | cop[tikv] | table:fgedu_users | keep order:false, stats:pseudo | +————————-+———+———–+——————————-+——————————–+ 3 rows in set (0.01 sec)
## 2. 执行计划字段说明
– id：算子ID
– estRows：估计行数
– task：执行任务类型（root或cop[tikv]）
– access object：访问的对象
– operator info：算子信息
## 3. 执行顺序
– 从下到上执行，最底层的算子先执行
– 每个算子的结果作为上层算子的输入
3.2.2 执行计划优化分析
## 1. 优化前执行计划
+————————-+———+———–+——————————-+——————————–+ | id | estRows | task | access object | operator info | +————————-+———+———–+——————————-+——————————–+ | TableReader_7 | 33333.33 | root | | data:Selection_6 | | └─Selection_6 | 33333.33 | cop[tikv] | | gt(fgedu_users.age, 30), eq(fgedu_users.gender, “male”) | | └─TableFullScan_5 | 100000.00 | cop[tikv] | table:fgedu_users | keep order:false, stats:pseudo | +————————-+———+———–+——————————-+——————————–+ 3 rows in set (0.01 sec)
## 2. 创建索引
$ mysql -h 192.168.1.100 -P 4000 -u fgedu -p -e “CREATE INDEX idx_age_gender ON fgedudb.fgedu_users(age, gender);”
## 3. 优化后执行计划
+————————-+———+———–+—————————————–+———————————————-+
| id | estRows | task | access object | operator info |
+————————-+———+———–+—————————————–+———————————————-+
| IndexReader_6 | 33333.33 | root | | data:IndexRangeScan_5 |
| └─IndexRangeScan_5 | 33333.33 | cop[tikv] | table:fgedu_users, index:idx_age_gender | range:[30,+inf], filter:eq(fgedu_users.gender, “male”), keep order:false |
+————————-+———+———–+—————————————–+———————————————-+
## 4. 优化效果分析
– 扫描方式：从全表扫描变为索引范围扫描
– 估计行数：保持不变
– 执行性能：显著提升
3.3 性能调优
3.3.1 SQL优化
## 1. 优化查询条件
– 使用索引列作为查询条件
– 避免在索引列上使用函数
– 使用范围查询替代NOT IN
## 2. 优化JOIN操作
– 小表驱动大表
– 为JOIN列创建索引
– 避免复杂的JOIN条件
## 3. 优化排序操作
– 为排序列创建索引
– 避免使用ORDER BY RAND()
– 限制排序结果集大小
## 4. 优化聚合操作
– 为聚合列创建索引
– 使用GROUP BY时包含索引列
– 避免在聚合函数中使用复杂表达式
## 5. 优化子查询
– 用JOIN替代子查询
– 避免相关子查询
– 使用临时表缓存子查询结果
3.3.2 系统参数优化
## 1. TiDB参数优化
$ tiup cluster edit-config fgedu-tidb-cluster
# 添加以下配置
tidb_servers:
– host: 192.168.1.100
config:
performance.max-procs: 8
prepared-plan-cache.enabled: true
tikv-client.max-batch-size: 128
tikv-client.batch-wait-time: 200000
## 2. TiKV参数优化
tikv_servers:
– host: 192.168.1.101
config:
storage.scheduler-worker-pool-size: 4
readpool.storage.num-threads: 4
readpool.coprocessor.num-threads: 4
rocksdb.max-background-jobs: 4
## 3. 重启集群使配置生效
$ tiup cluster reload fgedu-tidb-cluster
Part04-生产案例与实战讲解
4.1 电商行业执行计划优化案例
某电商平台执行计划优化案例：
– 业务场景：电商平台商品搜索
– 问题：商品搜索响应时间长，影响用户体验
– 数据量：商品表数据量达到1000万
– QPS：高峰期达到5000QPS
# 问题分析
1. 慢查询分析：发现商品搜索SQL执行时间超过1秒
2. 执行计划分析：
+————————-+———+———–+——————————-+——————————–+ | id | estRows | task | access object | operator info | +————————-+———+———–+——————————-+——————————–+ | TableReader_7 | 33333.33 | root | | data:Selection_6 | | └─Selection_6 | 33333.33 | cop[tikv] | | eq(fgedu_products.category_id, 1), gt(fgedu_products.price, 100) | | └─TableFullScan_5 | 100000.00 | cop[tikv] | table:fgedu_products | keep order:false, stats:pseudo | +————————-+———+———–+——————————-+——————————–+ 3 rows in set (0.01 sec)
# 优化措施
1. 创建联合索引：
CREATE INDEX idx_category_price_sales ON fgedu_products(category_id, price, sales);
2. 优化SQL：
SELECT id, name, price, sales FROM fgedu_products WHERE category_id = 1 AND price > 100 ORDER BY sales DESC LIMIT 20;
3. 优化后执行计划：
+————————-+———+———–+—————————————–+———————————————-+
| id | estRows | task | access object | operator info |
+————————-+———+———–+—————————————–+———————————————-+
| TopN_7 | 20.00 | root | | offset:0, count:20, sort:fgedu_products.sales DESC |
| └─IndexRangeScan_6 | 10000.00 | cop[tikv] | table:fgedu_products, index:idx_category_price_sales | range:[1,100,+inf], keep order:false |
+————————-+———+———–+—————————————–+———————————————-+
# 优化效果
– 查询响应时间：从1秒降低到100ms以内
– 系统QPS：从5000提升到10000
– 用户体验：明显改善，商品搜索速度大幅提升
4.2 金融行业执行计划优化案例
某银行执行计划优化案例：
– 业务场景：银行交易查询
– 问题：交易历史查询响应时间长，影响业务处理效率
– 数据量：交易表数据量达到5000万
– QPS：高峰期达到2000QPS
# 问题分析
1. 慢查询分析：发现交易历史查询SQL执行时间超过2秒
2. 执行计划分析：
+————————-+———+———–+——————————-+——————————–+ | id | estRows | task | access object | operator info | +————————-+———+———–+——————————-+——————————–+ | TableReader_7 | 10000.00 | root | | data:Selection_6 | | └─Selection_6 | 10000.00 | cop[tikv] | | eq(fgedu_transactions.user_id, 12345), between(fgedu_transactions.transaction_date, “2024-01-01”, “2024-01-31″) | | └─TableFullScan_5 | 5000000.00 | cop[tikv] | table:fgedu_transactions | keep order:false, stats:pseudo | +————————-+———+———–+——————————-+——————————–+ 3 rows in set (0.01 sec)
# 优化措施
1. 创建联合索引：
CREATE INDEX idx_user_date ON fgedu_transactions(user_id, transaction_date);
2. 优化SQL：
SELECT transaction_id, transaction_date, amount, type FROM fgedu_transactions WHERE user_id = 12345 AND transaction_date BETWEEN ‘2024-01-01’ AND ‘2024-01-31’ ORDER BY transaction_date DESC;
3. 优化后执行计划：
+————————-+———+———–+—————————————–+———————————————-+
| id | estRows | task | access object | operator info |
+————————-+———+———–+—————————————–+———————————————-+
| IndexReader_6 | 10000.00 | root | | data:IndexRangeScan_5 |
| └─IndexRangeScan_5 | 10000.00 | cop[tikv] | table:fgedu_transactions, index:idx_user_date | range:[12345,”2024-01-01″],[12345,”2024-01-31”], keep order:false |
+————————-+———+———–+—————————————–+———————————————-+
# 优化效果
– 查询响应时间：从2秒降低到200ms以内
– 系统QPS：从2000提升到5000
– 业务处理效率：显著提高，客户满意度提升
4.3 制造业执行计划优化案例
某制造企业执行计划优化案例：
– 业务场景：生产数据统计
– 问题：生产报表生成时间长，影响决策效率
– 数据量：生产表数据量达到2000万
– 执行频率：每天执行一次
# 问题分析
1. 慢查询分析：发现生产报表SQL执行时间超过5分钟
2. 执行计划分析：
+————————-+———+———–+——————————-+——————————–+ | id | estRows | task | access object | operator info | +————————-+———+———–+——————————-+——————————–+ | HashAgg_10 | 1000.00 | root | | group by:fgedu_products.product_name, agg:sum(fgedu_orders.quantity) as total_quantity, agg:sum(fgedu_orders.amount) as total_amount | | └─HashJoin_12 | 100000.00 | root | | inner join, equal:[fgedu_orders.product_id, fgedu_products.product_id] | | ├─TableReader_11 | 100000.00 | root | | data:Selection_10 | | │ └─Selection_10 | 100000.00 | cop[tikv] | | between(fgedu_orders.order_date, “2024-01-01”, “2024-01-31”) | | │ └─TableFullScan_9 | 2000000.00 | cop[tikv] | table:fgedu_orders | keep order:false, stats:pseudo | | └─TableReader_8 | 10000.00 | root | | data:TableFullScan_7 | | └─TableFullScan_7 | 10000.00 | cop[tikv] | table:fgedu_products | keep order:false, stats:pseudo | +————————-+———+———–+——————————-+——————————–+ 7 rows in set (0.01 sec)
# 优化措施
1. 创建索引：
CREATE INDEX idx_order_product_date ON fgedu_orders(product_id, order_date);
CREATE INDEX idx_product_name ON fgedu_products(product_name);
2. 优化SQL：
SELECT p.product_name, SUM(o.quantity) as total_quantity, SUM(o.amount) as total_amount FROM fgedu_orders o JOIN fgedu_products p ON o.product_id = p.product_id WHERE o.order_date BETWEEN ‘2024-01-01’ AND ‘2024-01-31’ GROUP BY p.product_id, p.product_name ORDER BY total_amount DESC;
3. 优化后执行计划：
+————————-+———+———–+—————————————–+———————————————-+
| id | estRows | task | access object | operator info |
+————————-+———+———–+—————————————–+———————————————-+
| HashAgg_10 | 1000.00 | root | | group by:fgedu_products.product_id, fgedu_products.product_name, agg:sum(fgedu_orders.quantity) as total_quantity, agg:sum(fgedu_orders.amount) as total_amount |
| └─HashJoin_12 | 100000.00 | root | | inner join, equal:[fgedu_orders.product_id, fgedu_products.product_id] |
| ├─IndexReader_11 | 100000.00 | root | | data:IndexRangeScan_10 |
| │ └─IndexRangeScan_10 | 100000.00 | cop[tikv] | table:fgedu_orders, index:idx_order_product_date | range:[“2024-01-01″,”2024-01-31”], keep order:false |
| └─TableReader_8 | 10000.00 | root | | data:TableFullScan_7 |
| └─TableFullScan_7 | 10000.00 | cop[tikv] | table:fgedu_products | keep order:false, stats:pseudo |
+————————-+———+———–+—————————————–+———————————————-+
# 优化效果
– 查询响应时间：从5分钟降低到30秒以内
– 报表生成效率：显著提高，决策速度加快
– 系统负载：明显降低
Part05-风哥经验总结与分享
5.1 执行计划分析最佳实践
执行计划分析的最佳实践：
- 定期分析执行计划：定期分析慢查询的执行计划，识别性能瓶颈。
- 使用EXPLAIN ANALYZE：使用EXPLAIN ANALYZE查看实际执行计划，验证优化效果。
- 优化索引：根据执行计划调整索引，提高查询性能。
- 更新统计信息：定期更新统计信息，确保优化器生成准确的执行计划。
- 使用TiDB Dashboard：使用TiDB Dashboard可视化执行计划，便于分析和优化。
- 监控执行计划缓存：监控执行计划缓存的使用情况，确保缓存有效。
- 持续优化：根据业务变化和数据增长，持续优化执行计划。
5.2 故障排查技巧
## 1. 慢查询排查
– 查看慢查询日志，识别慢查询
– 分析慢查询的执行计划
– 优化慢查询，提高性能
## 2. 执行计划不稳定排查
– 检查统计信息是否过时
– 使用SQL绑定，固定执行计划
– 优化SQL语句，减少执行计划的变化
## 3. 索引失效排查
– 检查索引是否被正确使用
– 检查查询条件是否符合索引使用规则
– 优化查询条件，确保索引被使用
## 4. 连接性能问题排查
– 检查连接方式是否合适
– 检查连接列是否有索引
– 优化连接操作，提高性能
## 5. 排序性能问题排查
– 检查排序操作是否使用索引
– 检查排序结果集大小
– 优化排序操作，提高性能
## 6. 聚合性能问题排查
– 检查聚合操作是否使用索引
– 检查聚合函数是否复杂
– 优化聚合操作，提高性能
5.3 持续改进建议
持续改进建议：
- 建立执行计划审核机制：对新编写的SQL进行执行计划审核，确保符合最佳实践。
- 定期分析慢查询：定期分析慢查询日志，识别需要优化的查询。
- 优化索引：根据执行计划调整索引，避免过度索引。
- 更新统计信息：定期更新统计信息，确保优化器生成准确的执行计划。
- 监控执行计划缓存：实时监控执行计划缓存的使用情况，及时调整缓存大小。
- 培训开发人员：对开发人员进行执行计划分析培训，提高SQL编写质量。
- 使用工具：使用执行计划分析工具，辅助识别和解决性能问题。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
