合 TiDB查看执行计划和绑定执行计划
Tags： TiDB执行计划SQL优化EXPLAIN绑定执行计划hint
👉 本文共约277个字，系统预计阅读时间或需2分钟。
详细理论知识可以参考：https://docs.pingcap.com/zh/tidb/stable/sql-tuning-overview
使用 EXPLAIN 和 EXPLAIN ANALYZE 命令来查看执行计划
准备环境
1 2 3 4 5 6 7 8 9 10 11 | create database test; use test; create table t1(a int, b int); create table t2(a int, b int, index idx(b)); -- 向 t1 和 t2 表各插入 10000 行数据: for i in `seq 10000`; do mysql -uroot -P4000 -h172.17.0.4 -e "insert into test.t1 values($i, FLOOR(RAND()*10000000))"; done; for i in `seq 10000`; do mysql -uroot -P4000 -h172.17.0.4 -e "insert into test.t2 values($i, FLOOR(RAND()*10000000))"; done; |
通过 EXPLAIN 查看执行计划:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | MySQL [test]> explain select * from t1 where t1.a < (select sum(t2.a) from t2 where t2.b = t1.b); +-------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------+ | HashJoin_11 | 7997.28 | root | | inner join, equal:[eq(test.t1.b, test.t2.b)], other cond:lt(cast(test.t1.a, decimal(20,0) BINARY), Column#7) | | ├─HashAgg_23(Build) | 7992.00 | root | | group by:test.t2.b, funcs:sum(Column#8)->Column#7, funcs:firstrow(test.t2.b)->test.t2.b | | │ └─TableReader_24 | 7992.00 | root | | data:HashAgg_16 | | │ └─HashAgg_16 | 7992.00 | cop[tikv] | | group by:test.t2.b, funcs:sum(test.t2.a)->Column#8 | | │ └─Selection_22 | 9990.00 | cop[tikv] | | not(isnull(test.t2.b)) | | │ └─TableFullScan_21 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo | | └─TableReader_15(Probe) | 10036.95 | root | | data:Selection_14 | | └─Selection_14 | 10036.95 | cop[tikv] | | not(isnull(test.t1.b)) | | └─TableFullScan_13 | 10047.00 | cop[tikv] | table:t1 | keep order:false | +-------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------+ 9 rows in set (0.06 sec) |
通过 EXPLAIN ANALYZE 查看执行计划：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
