LEFT JION 比 INNER JION 快很多？

mao_siyu · 2020 年3 月 17 日 02:52

* 【TiDB 版本】：

3.0.11

* 【前置条件】：

表1 dc_flowdata_deliver_sale 数据量：43397632

表2 dc_product_master 数据量：117

INNER JION 的查询计划

EXPLAIN ANALYZE
SELECT
	sale.normal_product_code,
	sum( IFNULL( sale.normal_quantity, 0 ) ) sale_sum 
FROM
	dc_flowdata_deliver_sale sale INNER JOIN	dc_product_master p ON p.paas_is_del = '0' AND p.bu_type = '2' AND sale.normal_product_code = p.product_code 
WHERE
	sale.seller_date BETWEEN '2019-10-01'	AND '2019-12-31'
	AND sale.data_flag = '0' 
	AND sale.paas_is_del = '0' 
	AND sale.br_status = '3' 
	AND sale.normal_seller_code != '99999' 
	AND sale.normal_organ_code != '99999'  
	AND sale.normal_product_code != '99999' 
GROUP BY
	sale.normal_product_code;


+--------------------------+-----------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+----------------------+
| id                       | count     | task | operator info                                                                                                                                                                                                                                                                                                                                  | execution info                                                                        | memory               |
+--------------------------+-----------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+----------------------+
| Projection_8             | 1.00      | root | prd2_pfizer.sale.normal_product_code, 5_col_0                                                                                                                                                                                                                                                                                                  | time:50.400521527s, loops:1, rows:0                                                   | N/A                  |
| └─HashAgg_11             | 1.00      | root | group by:col_2, funcs:sum(col_0), firstrow(col_1)                                                                                                                                                                                                                                                                                              | time:50.400518419s, loops:1, rows:0                                                   | N/A                  |
|   └─Projection_77        | 0.00      | root | ifnull(prd2_pfizer.sale.normal_quantity, 0), prd2_pfizer.sale.normal_product_code, prd2_pfizer.sale.normal_product_code                                                                                                                                                                                                                        | time:50.400348663s, loops:1, rows:0                                                   | N/A                  |
|     └─IndexJoin_26       | 0.00      | root | inner join, inner:IndexLookUp_25, outer key:prd2_pfizer.p.product_code, inner key:prd2_pfizer.sale.normal_product_code                                                                                                                                                                                                                         | time:50.400341511s, loops:1, rows:0                                                   | 16.77734375 KB       |
|       ├─IndexLookUp_25   | 0.00      | root |                                                                                                                                                                                                                                                                                                                                                | time:50.363556176s, loops:1, rows:0                                                   | 77.28210639953613 MB |
|       │ ├─Selection_23   | 307909.13 | cop  | ne(prd2_pfizer.sale.normal_product_code, "99999")                                                                                                                                                                                                                                                                                              | proc max:2.008s, min:16ms, p80:1.656s, p95:2.008s, rows:8946846, iters:8803, tasks:14 | N/A                  |
|       │ │ └─IndexScan_21 | 307909.13 | cop  | table:sale, index:normal_product_code, range: decided by [eq(prd2_pfizer.sale.normal_product_code, prd2_pfizer.p.product_code)], keep order:false                                                                                                                                                                                              | proc max:1.667s, min:15ms, p80:1.343s, p95:1.667s, rows:8946846, iters:8803, tasks:14 | N/A                  |
|       │ └─Selection_24   | 0.00      | cop  | eq(prd2_pfizer.sale.br_status, "3"), eq(prd2_pfizer.sale.data_flag, "0"), eq(prd2_pfizer.sale.paas_is_del, "0"), ge(prd2_pfizer.sale.seller_date, 2019-10-01 00:00:00.000000), le(prd2_pfizer.sale.seller_date, 2019-12-31 00:00:00.000000), ne(prd2_pfizer.sale.normal_organ_code, "99999"), ne(prd2_pfizer.sale.normal_seller_code, "99999") | proc max:975ms, min:0s, p80:73ms, p95:230ms, rows:0, iters:35833, tasks:8030          | N/A                  |
|       │   └─TableScan_22 | 307909.13 | cop  | table:dc_flowdata_deliver_sale, keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                 | proc max:966ms, min:0s, p80:72ms, p95:225ms, rows:8946846, iters:35833, tasks:8030    | N/A                  |
|       └─TableReader_60   | 0.00      | root | data:Selection_59                                                                                                                                                                                                                                                                                                                              | time:36.43248ms, loops:2, rows:78                                                     | 1.150390625 KB       |
|         └─Selection_59   | 0.00      | cop  | eq(prd2_pfizer.p.bu_type, "2"), eq(prd2_pfizer.p.paas_is_del, "0"), ne(prd2_pfizer.p.product_code, "99999")                                                                                                                                                                                                                                    | time:0s, loops:3, rows:78                                                             | N/A                  |
|           └─TableScan_58 | 117.00    | cop  | table:p, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                     | time:0s, loops:3, rows:117                                                            | N/A                  |
+--------------------------+-----------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+----------------------+

12 rows in set
Time: 50.429s

LEFT JION 的查询计划

+--------------------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+----------------------+
| id                       | count      | task | operator info                                                                                                                                                                                                                                                         | execution info                                                                    | memory               |
+--------------------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+----------------------+
| Projection_7             | 1.00       | root | prd2_pfizer.sale.normal_product_code, 5_col_0                                                                                                                                                                                                                         | time:5.95689746s, loops:1, rows:0                                                 | N/A                  |
| └─HashAgg_10             | 1.00       | root | group by:col_2, funcs:sum(col_0), firstrow(col_1)                                                                                                                                                                                                                     | time:5.956894649s, loops:1, rows:0                                                | N/A                  |
|   └─Projection_64        | 0.00       | root | ifnull(prd2_pfizer.sale.normal_quantity, 0), prd2_pfizer.sale.normal_product_code, prd2_pfizer.sale.normal_product_code                                                                                                                                               | time:5.956816353s, loops:1, rows:0                                                | N/A                  |
|     └─IndexJoin_19       | 0.00       | root | left outer join, inner:IndexLookUp_18, outer key:prd2_pfizer.sale.normal_product_code, inner key:prd2_pfizer.p.product_code                                                                                                                                           | time:5.956809056s, loops:1, rows:0                                                | 4.984375 KB          |
|       ├─IndexLookUp_49   | 0.00       | root |                                                                                                                                                                                                                                                                       | time:5.95689225s, loops:1, rows:0                                                 | 32.59805965423584 MB |
|       │ ├─IndexScan_46   | 3466612.91 | cop  | table:sale, index:seller_date, range:[2019-10-01,2019-12-31], keep order:false                                                                                                                                                                                        | proc max:607ms, min:57ms, p80:607ms, p95:607ms, rows:3493486, iters:3434, tasks:5 | N/A                  |
|       │ └─Selection_48   | 0.00       | cop  | eq(prd2_pfizer.sale.br_status, "3"), eq(prd2_pfizer.sale.data_flag, "0"), eq(prd2_pfizer.sale.paas_is_del, "0"), ne(prd2_pfizer.sale.normal_organ_code, "99999"), ne(prd2_pfizer.sale.normal_product_code, "99999"), ne(prd2_pfizer.sale.normal_seller_code, "99999") | proc max:292ms, min:0s, p80:87ms, p95:144ms, rows:0, iters:5576, tasks:490        | N/A                  |
|       │   └─TableScan_47 | 3466612.91 | cop  | table:dc_flowdata_deliver_sale, keep order:false                                                                                                                                                                                                                      | proc max:292ms, min:0s, p80:87ms, p95:142ms, rows:3493486, iters:5576, tasks:490  | N/A                  |
|       └─IndexLookUp_18   | 0.00       | root |                                                                                                                                                                                                                                                                       | time:0ns, loops:0, rows:0                                                         | N/A                  |
|         ├─Selection_16   | 0.08       | cop  | ne(prd2_pfizer.p.product_code, "99999")                                                                                                                                                                                                                               | time:0ns, loops:0, rows:0                                                         | N/A                  |
|         │ └─IndexScan_14 | 0.12       | cop  | table:p, index:product_code, range: decided by [eq(prd2_pfizer.p.product_code, prd2_pfizer.sale.normal_product_code)], keep order:false, stats:pseudo                                                                                                                 | time:0ns, loops:0, rows:0                                                         | N/A                  |
|         └─Selection_17   | 0.00       | cop  | eq(prd2_pfizer.p.bu_type, "2"), eq(prd2_pfizer.p.paas_is_del, "0")                                                                                                                                                                                                    | time:0ns, loops:0, rows:0                                                         | N/A                  |
|           └─TableScan_15 | 0.08       | cop  | table:dc_product_master, keep order:false, stats:pseudo                                                                                                                                                                                                               | time:0ns, loops:0, rows:0                                                         | N/A                  |
+--------------------------+------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+----------------------+

13 rows in set
Time: 5.991s

* 【问题描述】：

同样的语句，同样的数据，LEFT JION 怎么会比 INNER JION 快这么多？
大表关联小表调优思路方式是什么呢？
查看查询计划

image1498×309 31.3 KB

qizheng · 2020 年3 月 17 日 03:20

当执行多表 Join 时，优化器会选择一个它认为合适的 Join Order，上面执行计划中 TableScan/IndexScan 这一行显示 stats:pseudo，可能存在 table 的统计信息过期，导致优化器估算不准，建议先收集更新统计信息

qizheng · 2020 年3 月 17 日 05:02

Inner Join 的执行计划 sale 表是 inner 表，p 表是 outer 表，关联方式是 IndexJoin；当 Inner 表的数据量比较大，Outer 表的数据量比较小，大部分情况下使用 Index Join 会比其他 Join 算法更快。
优化器估算的 sale 表 IndexScan 的 row count 为 307909.13，实际扫描的 rows: 8946846 偏差比较大，sale 作为 inner 表会被扫描多次（扫描次数取决于 outer 表返回的行数，实际 rows 为78），且每次扫描都存在大量数据的回表操作，导致查询效率偏低，所以建议先收集 sale 表的统计信息，然后再看执行计划是否变化。

mao_siyu · 2020 年3 月 17 日 05:45

重新执行了一次

ANALYZE TABLE dc_flowdata_deliver_sale;

EXPLAIN ANALYZE
SELECT
   sale.normal_product_code,
   sum( IFNULL( sale.normal_quantity, 0 ) ) sale_sum
FROM
   dc_flowdata_deliver_sale sale INNER JOIN    dc_product_master p ON p.paas_is_del = '0' AND p.bu_type = '2' AND sale.normal_product_code = p.product_
code
WHERE
   sale.seller_date BETWEEN '2019-10-01'    AND '2019-12-31'
   AND sale.data_flag = '0'
   AND sale.paas_is_del = '0'
   AND sale.br_status = '3'
   AND sale.normal_seller_code != '99999'
   AND sale.normal_organ_code != '99999'
   AND sale.normal_product_code != '99999'
GROUP BY
   sale.normal_product_code;

Query OK, 0 rows affected
Time: 168.207s


+--------------------------+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------------+
| id                       | count  | task | operator info                                                                                                                                                                                                                                                                                                               | execution info                                                                  | memory         |
+--------------------------+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------------+
| Projection_8             | 1.00   | root | prd2_pfizer.sale.normal_product_code, 5_col_0                                                                                                                                                                                                                                                                               | time:345.194756ms, loops:1, rows:0                                              | N/A            |
| └─HashAgg_11             | 1.00   | root | group by:col_2, funcs:sum(col_0), firstrow(col_1)                                                                                                                                                                                                                                                                           | time:345.191673ms, loops:1, rows:0                                              | N/A            |
|   └─Projection_92        | 0.00   | root | ifnull(prd2_pfizer.sale.normal_quantity, 0), prd2_pfizer.sale.normal_product_code, prd2_pfizer.sale.normal_product_code                                                                                                                                                                                                     | time:345.113725ms, loops:1, rows:0                                              | N/A            |
|     └─IndexJoin_29       | 0.00   | root | inner join, inner:IndexLookUp_28, outer key:prd2_pfizer.p.product_code, inner key:prd2_pfizer.sale.normal_product_code                                                                                                                                                                                                      | time:345.107985ms, loops:1, rows:0                                              | 16.77734375 KB |
|       ├─IndexLookUp_28   | 0.00   | root |                                                                                                                                                                                                                                                                                                                             | time:329.267676ms, loops:1, rows:0                                              | 314 Bytes      |
|       │ ├─Selection_26   | 0.00   | cop  | eq(prd2_pfizer.sale.br_status, "3"), eq(prd2_pfizer.sale.data_flag, "0"), eq(prd2_pfizer.sale.paas_is_del, "0"), ne(prd2_pfizer.sale.normal_product_code, "99999")                                                                                                                                                          | proc max:315ms, min:25ms, p80:107ms, p95:315ms, rows:0, iters:728, tasks:10     | N/A            |
|       │ │ └─IndexScan_24 | 208.33 | cop  | table:sale, index:normal_product_code, seller_date, paas_is_del, data_flag, br_status, range: decided by [eq(prd2_pfizer.sale.normal_product_code, prd2_pfizer.p.product_code) ge(prd2_pfizer.sale.seller_date, 2019-10-01 00:00:00.000000) le(prd2_pfizer.sale.seller_date, 2019-12-31 00:00:00.000000)], keep order:false | proc max:277ms, min:11ms, p80:85ms, p95:277ms, rows:698652, iters:728, tasks:10 | N/A            |
|       │ └─Selection_27   | 0.00   | cop  | ne(prd2_pfizer.sale.normal_organ_code, "99999"), ne(prd2_pfizer.sale.normal_seller_code, "99999")                                                                                                                                                                                                                           | time:0ns, loops:0, rows:0                                                       | N/A            |
|       │   └─TableScan_25 | 0.00   | cop  | table:dc_flowdata_deliver_sale, keep order:false, stats:pseudo                                                                                                                                                                                                                                                              | time:0s, loops:0, rows:0                                                        | N/A            |
|       └─TableReader_74   | 0.00   | root | data:Selection_73                                                                                                                                                                                                                                                                                                           | time:15.609954ms, loops:2, rows:78                                              | 1.15625 KB     |
|         └─Selection_73   | 0.00   | cop  | eq(prd2_pfizer.p.bu_type, "2"), eq(prd2_pfizer.p.paas_is_del, "0"), ne(prd2_pfizer.p.product_code, "99999")                                                                                                                                                                                                                 | time:15ms, loops:3, rows:78                                                     | N/A            |
|           └─TableScan_72 | 117.00 | cop  | table:p, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                  | time:14ms, loops:3, rows:117                                                    | N/A            |
+--------------------------+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------------+

12 rows in set
Time: 0.363s

qizheng · 2020 年3 月 17 日 06:12

收集统计信息后，执行效率明显提高，主要是执行计划中 sale 表的 IndexScan 扫描的 rows:698652 比之前减少了很多，过滤条件参考了更多字段 seller_date, paas_is_del, data_flag, br_status。

mao_siyu · 2020 年3 月 17 日 06:37

是啊，怎么差这么多？而且同样的SQL 同样的数据，我在 3.0.5查询，还是慢的像蜗牛；

之后我在 3.0.5的 TiDB 也做了同样的操作，查询时间还是 200秒的查询，这是什么情况啊。。。。

qizheng · 2020 年3 月 17 日 06:41

方便的话执行 show create table 看下表结构