同一个sql在3.0.5与5.3.0版本性能差别

beebol · 2023 年5 月 29 日 03:35

【 TiDB 使用环境】生产环境
【 TiDB 版本】
新版本5.3.0
老版本3.0.5
如下在老版本中执行结果：

explain select o.account_id , sum(o.amount),sum(if(c.open_type = 1,o.amount,0)),sum(if(c.open_type = 1,1,0)),sum(if(c.open_type != 1,o.amount,0)),sum(if(c.open_type != 1,1,0))  
    ->                         from new_boss.`order` o left join new_boss.invoice i on o.uuid = i.order_uuid left join product p on i.product_id = p.id  
    ->                          left join channel c on c.id = o.channel_id  where o.account_id = 164121770 and i.end_time >=  '2023-05-18' and  i.delivered_at > '2023-05-10 15:05:45' and  i.delivered_at < '2023-05-24'  and p.cate =1 and i.status =1 and c.open_type in (1,2,3) ;
+--------------------------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | count | task | operator info                                                                                                                                                                                                                                      |
+--------------------------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_11                        | 1.00  | root | new_boss.o.account_id, 9_col_0, 9_col_1, 9_col_2, 9_col_3, 9_col_4                                                                                                                                                                                 |
| └─StreamAgg_16                       | 1.00  | root | funcs:sum(col_0), sum(col_1), sum(col_2), sum(col_3), sum(col_4), firstrow(col_5)                                                                                                                                                                  |
|   └─Projection_126                   | 0.00  | root | cast(new_boss.o.amount), cast(if(eq(boss.c.open_type, 1), new_boss.o.amount, 0)), cast(if(eq(boss.c.open_type, 1), 1, 0)), cast(if(ne(boss.c.open_type, 1), new_boss.o.amount, 0)), cast(if(ne(boss.c.open_type, 1), 1, 0)), new_boss.o.account_id |
|     └─IndexJoin_21                   | 0.00  | root | inner join, inner:TableReader_20, outer key:new_boss.o.channel_id, inner key:boss.c.id                                                                                                                                                             |
|       ├─IndexJoin_51                 | 0.00  | root | inner join, inner:TableReader_50, outer key:new_boss.i.product_id, inner key:boss.p.id                                                                                                                                                             |
|       │ ├─IndexJoin_77               | 0.00  | root | inner join, inner:IndexLookUp_76, outer key:new_boss.o.uuid, inner key:new_boss.i.order_uuid                                                                                                                                                       |
|       │ │ ├─IndexLookUp_100          | 0.00  | root |                                                                                                                                                                                                                                                    |
|       │ │ │ ├─IndexScan_98           | 0.00  | cop  | table:o, index:account_id, range:[164121770,164121770], keep order:false                                                                                                                                                                           |
|       │ │ │ └─TableScan_99           | 0.00  | cop  | table:order, keep order:false                                                                                                                                                                                                                      |
|       │ │ └─IndexLookUp_76           | 0.00  | root |                                                                                                                                                                                                                                                    |
|       │ │   ├─IndexScan_73           | 1.00  | cop  | table:i, index:order_uuid, range: decided by [eq(new_boss.i.order_uuid, new_boss.o.uuid)], keep order:false                                                                                                                                        |
|       │ │   └─Selection_75           | 0.00  | cop  | eq(new_boss.i.status, 1), ge(new_boss.i.end_time, 2023-05-18 00:00:00.000000), gt(new_boss.i.delivered_at, 2023-05-10 15:05:45.000000), lt(new_boss.i.delivered_at, 2023-05-24 00:00:00.000000), not(isnull(new_boss.i.product_id))                |
|       │ │     └─TableScan_74         | 1.00  | cop  | table:invoice, keep order:false                                                                                                                                                                                                                    |
|       │ └─TableReader_50             | 0.80  | root | data:Selection_49                                                                                                                                                                                                                                  |
|       │   └─Selection_49             | 0.80  | cop  | eq(boss.p.cate, 1)                                                                                                                                                                                                                                 |
|       │     └─TableScan_48           | 1.00  | cop  | table:p, range: decided by [new_boss.i.product_id], keep order:false                                                                                                                                                                               |
|       └─TableReader_20               | 0.80  | root | data:Selection_19                                                                                                                                                                                                                                  |
|         └─Selection_19               | 0.80  | cop  | in(boss.c.open_type, 1, 2, 3)                                                                                                                                                                                                                      |
|           └─TableScan_18             | 1.00  | cop  | table:c, range: decided by [new_boss.o.channel_id], keep order:false, stats:pseudo                                                                                                                                                                 |
+--------------------------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
19 rows in set (0.01 sec)

root 11:28:  [boss]> select o.account_id , sum(o.amount),sum(if(c.open_type = 1,o.amount,0)),sum(if(c.open_type = 1,1,0)),sum(if(c.open_type != 1,o.amount,0)),sum(if(c.open_type != 1,1,0))  
    ->                         from new_boss.`order` o left join new_boss.invoice i on o.uuid = i.order_uuid left join product p on i.product_id = p.id  
    ->                          left join channel c on c.id = o.channel_id  where o.account_id = 164121770 and i.end_time >=  '2023-05-18' and  i.delivered_at > '2023-05-10 15:05:45' and  i.delivered_at < '2023-05-24'  and p.cate =1 and i.status =1 and c.open_type in (1,2,3) ;
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
| account_id | sum(o.amount) | sum(if(c.open_type = 1,o.amount,0)) | sum(if(c.open_type = 1,1,0)) | sum(if(c.open_type != 1,o.amount,0)) | sum(if(c.open_type != 1,1,0)) |
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
|       NULL |          NULL |                                NULL |                         NULL |                                 NULL |                          NULL |
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
1 row in set (0.04 sec)

耗时比较正常，速度较快。
在新版本中执行如下：

explain select o.account_id , sum(o.amount),sum(if(c.open_type = 1,o.amount,0)),sum(if(c.open_type = 1,1,0)),sum(if(c.open_type != 1,o.amount,0)),sum(if(c.open_type != 1,1,0))  
    ->                         from new_boss.`order` o left join new_boss.invoice i on o.uuid = i.order_uuid left join product p on i.product_id = p.id  
    ->                          left join channel c on c.id = o.channel_id  where o.account_id = 164121770 and i.end_time >=  '2023-05-18' and  i.delivered_at > '2023-05-10 15:05:45' and  i.delivered_at < '2023-05-24'  and p.cate =1 and i.status =1 and c.open_type in (1,2,3) ;
+---------------------------------------------------+---------+--------------+-----------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                                | estRows | task         | access object                                 | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+---------------------------------------------------+---------+--------------+-----------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_17                                     | 1.00    | root         |                                               | new_boss.order.account_id, Column#130, Column#131, Column#132, Column#133, Column#134                                                                                                                                                                                                                                                                                                                                                                                |
| └─HashAgg_18                                      | 1.00    | root         |                                               | funcs:sum(Column#170)->Column#130, funcs:sum(Column#171)->Column#131, funcs:sum(Column#172)->Column#132, funcs:sum(Column#173)->Column#133, funcs:sum(Column#174)->Column#134, funcs:firstrow(Column#175)->new_boss.order.account_id                                                                                                                                                                                                                                 |
|   └─Projection_245                                | 489.00  | root         |                                               | cast(new_boss.order.amount, decimal(32,0) BINARY)->Column#170, cast(if(eq(boss.channel.open_type, 1), new_boss.order.amount, 0), decimal(32,0) BINARY)->Column#171, cast(if(eq(boss.channel.open_type, 1), 1, 0), decimal(22,0) BINARY)->Column#172, cast(if(ne(boss.channel.open_type, 1), new_boss.order.amount, 0), decimal(32,0) BINARY)->Column#173, cast(if(ne(boss.channel.open_type, 1), 1, 0), decimal(22,0) BINARY)->Column#174, new_boss.order.account_id |
|     └─IndexJoin_31                                | 489.00  | root         |                                               | inner join, inner:TableReader_27, outer key:new_boss.invoice.product_id, inner key:boss.product.id, equal cond:eq(new_boss.invoice.product_id, boss.product.id)                                                                                                                                                                                                                                                                                                      |
|       ├─HashJoin_106(Build)                       | 489.00  | root         |                                               | inner join, equal:[eq(new_boss.order.channel_id, boss.channel.id)]                                                                                                                                                                                                                                                                                                                                                                                                   |
|       │ ├─HashJoin_164(Build)                     | 489.00  | root         |                                               | inner join, equal:[eq(new_boss.order.uuid, new_boss.invoice.order_uuid)]                                                                                                                                                                                                                                                                                                                                                                                             |
|       │ │ ├─IndexLookUp_200(Build)                | 606.94  | root         |                                               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|       │ │ │ ├─IndexRangeScan_197(Build)           | 942.02  | cop[tikv]    | table:i, index:idx_delivered_at(delivered_at) | range:(2023-05-10 15:05:45,2023-05-24 00:00:00), keep order:false                                                                                                                                                                                                                                                                                                                                                                                                    |
|       │ │ │ └─Selection_199(Probe)                | 606.94  | cop[tikv]    |                                               | eq(new_boss.invoice.status, 1), ge(new_boss.invoice.end_time, 2023-05-18 00:00:00.000000), not(isnull(new_boss.invoice.product_id))                                                                                                                                                                                                                                                                                                                                  |
|       │ │ │   └─TableRowIDScan_198                | 942.02  | cop[tikv]    | table:i                                       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|       │ │ └─IndexLookUp_186(Probe)                | 611.25  | root         |                                               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|       │ │   ├─IndexRangeScan_184(Build)           | 611.25  | cop[tikv]    | table:o, index:idx_account_id(account_id)     | range:[164121770,164121770], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                        |
|       │ │   └─TableRowIDScan_185(Probe)           | 611.25  | cop[tikv]    | table:o                                       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|       │ └─TableReader_212(Probe)                  | 645.00  | root         |                                               | data:Selection_211                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|       │   └─Selection_211                         | 645.00  | cop[tiflash] |                                               | in(boss.channel.open_type, 1, 2, 3)                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|       │     └─TableFullScan_210                   | 671.00  | cop[tiflash] | table:c                                       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|       └─TableReader_27(Probe)                     | 0.32    | root         |                                               | data:Selection_26                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|         └─Selection_26                            | 0.32    | cop[tikv]    |                                               | eq(boss.product.cate, 1)                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|           └─TableRangeScan_25                     | 1.00    | cop[tikv]    | table:p                                       | range: decided by [new_boss.invoice.product_id], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                    |
+---------------------------------------------------+---------+--------------+-----------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
19 rows in set (0.01 sec)

root 11:28:  [boss]> select o.account_id , sum(o.amount),sum(if(c.open_type = 1,o.amount,0)),sum(if(c.open_type = 1,1,0)),sum(if(c.open_type != 1,o.amount,0)),sum(if(c.open_type != 1,1,0))  
    ->                         from new_boss.`order` o left join new_boss.invoice i on o.uuid = i.order_uuid left join product p on i.product_id = p.id  
    ->                          left join channel c on c.id = o.channel_id  where o.account_id = 164121770 and i.end_time >=  '2023-05-18' and  i.delivered_at > '2023-05-10 15:05:45' and  i.delivered_at < '2023-05-24'  and p.cate =1 and i.status =1 and c.open_type in (1,2,3) ;
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
| account_id | sum(o.amount) | sum(if(c.open_type = 1,o.amount,0)) | sum(if(c.open_type = 1,1,0)) | sum(if(c.open_type != 1,o.amount,0)) | sum(if(c.open_type != 1,1,0)) |
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
|       NULL |          NULL |                                NULL |                         NULL |                                 NULL |                          NULL |
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
1 row in set (19.31 sec)

新版本中有tiflash组件，整个执行耗时近20秒。
表结构，索引都是一样的。
就算强制按老版本的索引走，将mpp参数关掉，还是很慢。请问怎么解决这个问题，是哪些配置参数没有调整到位吗？

zhanggame1 · 2023 年5 月 29 日 03:38

数据量一样吗，重新做个表分析试试

xingzhenxiang · 2023 年5 月 29 日 05:36

show stats_healthy 看看表的健康度

tidb菜鸟一只 · 2023 年5 月 29 日 07:00

主要是new_boss.invoice这个表吧，走delivered_at字段上的索引有问题，收集下这个表的统计信息先。

beebol · 2023 年5 月 29 日 07:05

show stats_healthy;
| new_boss              | invoice                                                     |                |      97 |

beebol · 2023 年5 月 29 日 07:06

数据量一样，就是同一个表放到不同的集群

xingzhenxiang · 2023 年5 月 29 日 07:33

这个健康度正常，我也没有其他思路了

WalterWj · 2023 年5 月 29 日 07:34

直接 set engine ，只让他走 tikv,tidb 试试呢

beebol · 2023 年5 月 29 日 07:55

问题差不多找到了，但还不清楚原因。
1、将i表重更新统计信息后，执行计划把o表作为了驱动表了（和老版本一样了），但i表还是使用了idx_delivered_at索引，正常应该要使用join索引的（idx_order_uuid），不知道为什么没有使用，却去使用where中的索引。
2、强制使用idx_order_uuid索引后，速度和老集群一样了。

zhanggame1 · 2023 年5 月 29 日 23:29

执行计划选择出问题是所有数据库都有的，前几天我还在Oracle19上碰到一个单表查询不走主键索引的，where条件包含主键列

system · 2023 年7 月 28 日 23:30

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。