* 【TiDB 版本】:
3.0.11
* 【问题描述】:
下面的SQL语句 执行后 多扫描了一次 partition:p201701
EXPLAIN SELECT
COUNT( * )
FROM
dc_flowdata_deliver_sale_range sale
WHERE
sale.seller_date BETWEEN '2020-03-01' AND '2020-03-31';
+----------------------+-------------+------+----------------------------------------------------------------------------------------------------------------------------+
| id | count | task | operator info |
+----------------------+-------------+------+----------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_13 | 1.00 | root | funcs:count(1) |
| └─Union_21 | 83310.01 | root | |
| ├─IndexReader_24 | 2113.01 | root | index:Selection_23 |
| │ └─Selection_23 | 2113.01 | cop | ge(prd2_pfizer.sale.seller_date, 2020-03-01 00:00:00.000000), le(prd2_pfizer.sale.seller_date, 2020-03-31 00:00:00.000000) |
| │ └─IndexScan_22 | 12908885.00 | cop | table:sale, partition:p201701, index:paas_id, seller_date, range:[NULL,+inf], keep order:false |
| └─IndexReader_27 | 81197.00 | root | index:Selection_26 |
| └─Selection_26 | 81197.00 | cop | ge(prd2_pfizer.sale.seller_date, 2020-03-01 00:00:00.000000), le(prd2_pfizer.sale.seller_date, 2020-03-31 00:00:00.000000) |
| └─IndexScan_25 | 81197.00 | cop | table:sale, partition:p202003, index:paas_id, seller_date, range:[NULL,+inf], keep order:false |
+----------------------+-------------+------+----------------------------------------------------------------------------------------------------------------------------+
8 rows in set
Time: 0.026s
确保 p201701 中没有 2020-03 的数据
SELECT
COUNT( * )
FROM
dc_flowdata_deliver_sale_range PARTITION (p201701)
WHERE
seller_date BETWEEN '2020-03-01' AND '2020-03-31';
+------------+
| COUNT( * ) |
+------------+
| 0 |
+------------+
1 row in set
Time: 2.077s
表分区信息
+----------+------------+------------------------+----------+----------------+
| 分区名称 | 原分区条件 | 分区表达式 | 分区条件 | 分区中数据行数 |
+----------+------------+------------------------+----------+----------------+
| p201701 | 2017-02-01 | to_days(`seller_date`) | 736726 | 12908885 |
| p201702 | 2017-03-01 | to_days(`seller_date`) | 736754 | 445191 |
| p201703 | 2017-04-01 | to_days(`seller_date`) | 736785 | 572334 |
| p201704 | 2017-05-01 | to_days(`seller_date`) | 736815 | 568233 |
| p201705 | 2017-06-01 | to_days(`seller_date`) | 736846 | 641184 |
| p201706 | 2017-07-01 | to_days(`seller_date`) | 736876 | 565191 |
| p201707 | 2017-08-01 | to_days(`seller_date`) | 736907 | 545262 |
| p201708 | 2017-09-01 | to_days(`seller_date`) | 736938 | 718690 |
| p201709 | 2017-10-01 | to_days(`seller_date`) | 736968 | 647633 |
| p201710 | 2017-11-01 | to_days(`seller_date`) | 736999 | 593606 |
| p201711 | 2017-12-01 | to_days(`seller_date`) | 737029 | 639424 |
| p201712 | 2018-01-01 | to_days(`seller_date`) | 737060 | 676133 |
| p201801 | 2018-02-01 | to_days(`seller_date`) | 737091 | 767891 |
| p201802 | 2018-03-01 | to_days(`seller_date`) | 737119 | 469169 |
| p201803 | 2018-04-01 | to_days(`seller_date`) | 737150 | 629613 |
| p201804 | 2018-05-01 | to_days(`seller_date`) | 737180 | 683604 |
| p201805 | 2018-06-01 | to_days(`seller_date`) | 737211 | 752039 |
| p201806 | 2018-07-01 | to_days(`seller_date`) | 737241 | 743466 |
| p201807 | 2018-08-01 | to_days(`seller_date`) | 737272 | 823009 |
| p201808 | 2018-09-01 | to_days(`seller_date`) | 737303 | 851376 |
| p201809 | 2018-10-01 | to_days(`seller_date`) | 737333 | 818211 |
| p201810 | 2018-11-01 | to_days(`seller_date`) | 737364 | 774612 |
| p201811 | 2018-12-01 | to_days(`seller_date`) | 737394 | 791762 |
| p201812 | 2019-01-01 | to_days(`seller_date`) | 737425 | 1005789 |
| p201901 | 2019-02-01 | to_days(`seller_date`) | 737456 | 1219524 |
| p201902 | 2019-03-01 | to_days(`seller_date`) | 737484 | 683138 |
| p201903 | 2019-04-01 | to_days(`seller_date`) | 737515 | 1046241 |
| p201904 | 2019-05-01 | to_days(`seller_date`) | 737545 | 1086249 |
| p201905 | 2019-06-01 | to_days(`seller_date`) | 737576 | 1101026 |
| p201906 | 2019-07-01 | to_days(`seller_date`) | 737606 | 1035751 |
| p201907 | 2019-08-01 | to_days(`seller_date`) | 737637 | 1121364 |
| p201908 | 2019-09-01 | to_days(`seller_date`) | 737668 | 1124801 |
| p201909 | 2019-10-01 | to_days(`seller_date`) | 737698 | 1152249 |
| p201910 | 2019-11-01 | to_days(`seller_date`) | 737729 | 1137912 |
| p201911 | 2019-12-01 | to_days(`seller_date`) | 737759 | 1143472 |
| p201912 | 2020-01-01 | to_days(`seller_date`) | 737790 | 1212102 |
| p202001 | 2020-02-01 | to_days(`seller_date`) | 737821 | 1138481 |
| p202002 | 2020-03-01 | to_days(`seller_date`) | 737850 | 540885 |
| p202003 | 2020-04-01 | to_days(`seller_date`) | 737881 | 81197 |
| p202004 | 2020-05-01 | to_days(`seller_date`) | 737911 | 0 |
| p202005 | 2020-06-01 | to_days(`seller_date`) | 737942 | 0 |
| p202006 | 2020-07-01 | to_days(`seller_date`) | 737972 | 0 |
| p202007 | 2020-08-01 | to_days(`seller_date`) | 738003 | 0 |
| p202008 | 2020-09-01 | to_days(`seller_date`) | 738034 | 0 |
| p202009 | 2020-10-01 | to_days(`seller_date`) | 738064 | 0 |
| p202010 | 2020-11-01 | to_days(`seller_date`) | 738095 | 0 |
| p202011 | 2020-12-01 | to_days(`seller_date`) | 738125 | 0 |
| p202012 | 2021-01-01 | to_days(`seller_date`) | 738156 | 0 |
| p202101 | 2021-02-01 | to_days(`seller_date`) | 738187 | 0 |
| p202102 | 2021-03-01 | to_days(`seller_date`) | 738215 | 0 |
| p202103 | 2021-04-01 | to_days(`seller_date`) | 738246 | 0 |
| p202104 | 2021-05-01 | to_days(`seller_date`) | 738276 | 0 |
| p202105 | 2021-06-01 | to_days(`seller_date`) | 738307 | 0 |
| p202106 | 2021-07-01 | to_days(`seller_date`) | 738337 | 0 |