tidb执行计划--三

90 阅读 0 评论 60 点赞

我是靠谱客的博主含糊枫叶，这篇文章主要介绍tidb执行计划--三，现在分享给大家，希望可以做个参考。

TiDB中有两种方法管理执行计划，Hunt和SPM

表结构如下

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `idx_a` (`a`),
KEY `idx_ab` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
mysql> select * from t1 limit 10;
+------+--------+
| a
| b
|
+------+--------+
|
1 | 753597 |
|
2 | 249532 |
|
3 | 545404 |
|
4 | 599160 |
|
5 | 112835 |
|
6 | 358078 |
|
7 |
20667 |
|
8 | 638018 |
|
9 | 317102 |
|
10 | 300015 |
+------+--------+

默认的执行计划如下，可以看到默认使用了idx_ab，走了IndexReader，且不需要回表；

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> desc select * from t1 where a < 10;
+------------------------+---------+-----------+------------------------------+-----------------------------------+
| id
| estRows | task
| access object
| operator info
|
+------------------------+---------+-----------+------------------------------+-----------------------------------+
| IndexReader_6
| 3323.33 | root
|
| index:IndexRangeScan_5
|
| └─IndexRangeScan_5
| 3323.33 | cop[tikv] | table:t1, index:idx_ab(a, b) | range:[-inf,10), keep order:false |
+------------------------+---------+-----------+------------------------------+-----------------------------------+
2 rows in set (0.00 sec)

使用Hint方法强制要求使用idx_a索引；

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> desc select * from t1 use index(idx_a) where a < 10;
+-------------------------------+---------+-----------+--------------------------+-----------------------------------+
| id
| estRows | task
| access object
| operator info
|
+-------------------------------+---------+-----------+--------------------------+-----------------------------------+
| IndexLookUp_7
| 9.00
| root
|
|
|
| ├─IndexRangeScan_5(Build)
| 9.00
| cop[tikv] | table:t1, index:idx_a(a) | range:[-inf,10), keep order:false |
| └─TableRowIDScan_6(Probe)
| 9.00
| cop[tikv] | table:t1
| keep order:false
|
+-------------------------------+---------+-----------+--------------------------+-----------------------------------+
3 rows in set (0.00 sec)

使用SPM方法

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> create binding for
-> select * from t1 where a < 10
-> using
-> select * from
t1 use index(idx_a) where a < 10;
Query OK, 0 rows affected (0.00 sec)
mysql> desc select * from t1 where a < 10;
+-------------------------------+---------+-----------+--------------------------+-----------------------------------+
| id
| estRows | task
| access object
| operator info
|
+-------------------------------+---------+-----------+--------------------------+-----------------------------------+
| IndexLookUp_7
| 9.00
| root
|
|
|
| ├─IndexRangeScan_5(Build)
| 9.00
| cop[tikv] | table:t1, index:idx_a(a) | range:[-inf,10), keep order:false |
| └─TableRowIDScan_6(Probe)
| 9.00
| cop[tikv] | table:t1
| keep order:false
|
+-------------------------------+---------+-----------+--------------------------+-----------------------------------+
3 rows in set (0.00 sec)

可以看到，使用了BINDING后，即使不适用Hint，也会强制走索引idx_a

查看使用的SPM计划

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> show bindingsG
*************************** 1. row ***************************
Original_sql: select * from `test` . `t1` where `a` < ?
Bind_sql: SELECT * FROM `TEST`.`t1` USE INDEX (`idx_a`) WHERE `a` < 10
Default_db: test
Status: using
Create_time: 2022-01-23 17:52:17.468
Update_time: 2022-01-23 17:52:17.468
Charset: utf8
Collation: utf8_general_ci
Source: manual
1 row in set (0.00 sec)
mysql>
mysql> --删除SPM计划：DROP [GLOBAL|SESSION] BINGING FOR ...
mysql> DROP session binding for SELECT * FROM `TEST`.`t1` USE INDEX (`idx_a`) WHERE `a` < 10;
Query OK, 0 rows affected (0.00 sec)
mysql> show bindingsG
Empty set (0.00 sec)
mysql>