partiton table unused best index

[sultan8252]

Performance Questions

• What version of TiDB are you using?
  v3.0.14

• What's the observed and your expected performance respectively?
  partiton table unused best index

• Have you compared TiDB with other databases? If yes, what's their difference?
  in mysql

 explain select                  t.*,                 s.total_m as total             from sta_mysql_db_history s               INNER JOIN             (SELECT                          DATE_FORMAT( h.statistic_time, '%Y-%m-%d' ) AS dateitem,                         h.env,                     h.db_name,                         MAX(h.statistic_time) as statistic_time             FROM sta_mysql_db_history h                 WHERE h.env = 'prod' AND h.db_name = 'cachecloud' AND h.statistic_time between '2020-09-03 00:00:00' and '2020-09-10 23:00:00'  group by dateitem             ) t on s.env=t.env and s.db_name=t.db_name and s.statistic_time=t.statistic_time             ORDER BY t.dateitem desc;
+------------------------------------+--------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                 | count  | task | operator info                                                                                                                                                                                                              |
+------------------------------------+--------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_11                            | 557.08 | root | t.dateitem:desc                                                                                                                                                                                                            |
| └─Projection_13                    | 557.08 | root | t.dateitem, db_slowlog.t.env, db_slowlog.t.db_name, t.statistic_time, db_slowlog.s.total_m                                                                                                                                 |
|   └─IndexJoin_18                   | 557.08 | root | inner join, inner:IndexLookUp_17, outer key:db_slowlog.t.env, db_slowlog.t.db_name, t.statistic_time, inner key:db_slowlog.s.env, db_slowlog.s.db_name, db_slowlog.s.statistic_time                                        |
|     ├─Projection_21                | 0.80   | root | date_format(db_slowlog.h.statistic_time, "%Y-%m-%d"), db_slowlog.h.env, db_slowlog.h.db_name, 4_col_0                                                                                                                      |
|     │ └─Selection_22               | 0.80   | root | not(isnull(4_col_0)), not(isnull(db_slowlog.h.env))                                                                                                                                                                        |
|     │   └─HashAgg_27               | 1.00   | root | group by:col_4, funcs:max(col_0), firstrow(col_1), firstrow(col_2), firstrow(col_3)                                                                                                                                        |
|     │     └─IndexReader_28         | 1.00   | root | index:HashAgg_23                                                                                                                                                                                                           |
|     │       └─HashAgg_23           | 1.00   | cop  | group by:date_format(db_slowlog.h.statistic_time, "%Y-%m-%d"), funcs:max(db_slowlog.h.statistic_time), firstrow(db_slowlog.h.statistic_time), firstrow(db_slowlog.h.env), firstrow(db_slowlog.h.db_name)                   |
|     │         └─IndexScan_26       | 116.65 | cop  | table:h, index:env, db_name, statistic_time, range:["prod" "cachecloud" 2020-09-03 00:00:00,"prod" "cachecloud" 2020-09-10 23:00:00], keep order:false                                                                     |
|     └─IndexLookUp_17               | 1.00   | root |                                                                                                                                                                                                                            |
|       ├─Selection_16               | 1.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                                              |
|       │ └─IndexScan_14             | 1.00   | cop  | table:s, index:env, db_name, statistic_time, range: decided by [eq(db_slowlog.s.env, db_slowlog.t.env) eq(db_slowlog.s.db_name, db_slowlog.t.db_name) eq(db_slowlog.s.statistic_time, t.statistic_time)], keep order:false |
|       └─TableScan_15               | 1.00   | cop  | table:sta_mysql_db_history, keep order:false, stats:pseudo                                                                                                                                                                 |
+------------------------------------+--------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

• For a specific slow SQL query, please provide the following information:

CREATE TABLE `sta_mysql_db_history_tmp` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `statistic_time` datetime NOT NULL COMMENT '统计按1小时一次',
  `env` varchar(20) COLLATE utf8_general_ci DEFAULT NULL COMMENT '环境',
  `db_name` varchar(64) COLLATE utf8_general_ci NOT NULL COMMENT '库名',
  `count_table` int(11) DEFAULT '0' COMMENT '当前统计表的数量',
  `data_m` decimal(24,2) DEFAULT '0' COMMENT '数据的大小，单位MB',
  `index_m` decimal(24,2) DEFAULT '0' COMMENT '索引的大小，单位MB',
  `total_m` decimal(24,2) DEFAULT '0' COMMENT '全部的大小，单位MB',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据写入时间',
  PRIMARY KEY (`id`,`statistic_time`),
  KEY `idx_s_day` (`statistic_time`),
  KEY `idx_env_dbname` (`env`,`db_name`,`statistic_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1212802 COMMENT='数据治理——库统计历史信息'
PARTITION BY RANGE ( to_days(`statistic_time`) ) (
  PARTITION p202008 VALUES LESS THAN (738034),
  PARTITION p202009 VALUES LESS THAN (738064),
  PARTITION p202010 VALUES LESS THAN (738095),
  PARTITION p202011 VALUES LESS THAN (738125),
  PARTITION p202012 VALUES LESS THAN (738156),
  PARTITION p202101 VALUES LESS THAN (738187),
  PARTITION p202102 VALUES LESS THAN (738215),
  PARTITION p202103 VALUES LESS THAN (738246),
  PARTITION p202104 VALUES LESS THAN (738276),
  PARTITION p202105 VALUES LESS THAN (738307),
  PARTITION p202106 VALUES LESS THAN (738337),
  PARTITION p202107 VALUES LESS THAN (738368),
  PARTITION p202108 VALUES LESS THAN (738399),
  PARTITION p202109 VALUES LESS THAN (738429),
  PARTITION p202110 VALUES LESS THAN (738460),
  PARTITION p202111 VALUES LESS THAN (738490),
  PARTITION p202112 VALUES LESS THAN (738521)
)

EXPLAIN result.

explain select                  t.*,                 s.total_m as total             from sta_mysql_db_history_tmp s               INNER JOIN             (SELECT                          DATE_FORMAT( h.statistic_time, '%Y-%m-%d' ) AS dateitem,                         h.env,                     h.db_name,                         MAX(h.statistic_time) as statistic_time             FROM sta_mysql_db_history_tmp h                 WHERE h.env = 'prod' AND h.db_name = 'cachecloud' AND h.statistic_time between '2020-09-03 00:00:00' and '2020-09-10 23:00:00'  group by dateitem             ) t on s.env=t.env and s.db_name=t.db_name and s.statistic_time=t.statistic_time             ORDER BY t.dateitem desc;
+------------------------------------+-----------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                 | count     | task | operator info                                                                                                                                                                                            |
+------------------------------------+-----------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_30                            | 3.89      | root | t.dateitem:desc                                                                                                                                                                                          |
| └─Projection_32                    | 3.89      | root | t.dateitem, db_slowlog.t.env, db_slowlog.t.db_name, t.statistic_time, db_slowlog.s.total_m                                                                                                               |
|   └─HashRightJoin_34               | 3.89      | root | inner join, inner:Projection_35, equal:[eq(db_slowlog.t.env, db_slowlog.s.env) eq(db_slowlog.t.db_name, db_slowlog.s.db_name) eq(t.statistic_time, db_slowlog.s.statistic_time)]                         |
|     ├─Projection_35                | 0.80      | root | date_format(db_slowlog.h.statistic_time, "%Y-%m-%d"), db_slowlog.h.env, db_slowlog.h.db_name, 4_col_0                                                                                                    |
|     │ └─Selection_36               | 0.80      | root | not(isnull(4_col_0)), not(isnull(db_slowlog.h.env))                                                                                                                                                      |
|     │   └─HashAgg_41               | 1.00      | root | group by:col_4, funcs:max(col_0), firstrow(col_1), firstrow(col_2), firstrow(col_3)                                                                                                                      |
|     │     └─IndexReader_42         | 1.00      | root | index:HashAgg_37                                                                                                                                                                                         |
|     │       └─HashAgg_37           | 1.00      | cop  | group by:date_format(db_slowlog.h.statistic_time, "%Y-%m-%d"), funcs:max(db_slowlog.h.statistic_time), firstrow(db_slowlog.h.statistic_time), firstrow(db_slowlog.h.env), firstrow(db_slowlog.h.db_name) |
|     │         └─IndexScan_40       | 90.90     | cop  | table:h, partition:p202009, index:env, db_name, statistic_time, range:["prod" "cachecloud" 2020-09-03 00:00:00,"prod" "cachecloud" 2020-09-10 23:00:00], keep order:false                                |
|     └─Union_45                     | 585076.00 | root |                                                                                                                                                                                                          |
|       ├─TableReader_48             | 248011.00 | root | data:Selection_47                                                                                                                                                                                        |
|       │ └─Selection_47             | 248011.00 | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_46           | 248011.00 | cop  | table:s, partition:p202008, range:[-inf,+inf], keep order:false                                                                                                                                          |
|       ├─TableReader_54             | 187215.00 | root | data:Selection_53                                                                                                                                                                                        |
|       │ └─Selection_53             | 187215.00 | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_52           | 187215.00 | cop  | table:s, partition:p202009, range:[-inf,+inf], keep order:false                                                                                                                                          |
|       ├─TableReader_60             | 9990.00   | root | data:Selection_59                                                                                                                                                                                        |
|       │ └─Selection_59             | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_58           | 10000.00  | cop  | table:s, partition:p202010, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_66             | 9990.00   | root | data:Selection_65                                                                                                                                                                                        |
|       │ └─Selection_65             | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_64           | 10000.00  | cop  | table:s, partition:p202011, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_72             | 9990.00   | root | data:Selection_71                                                                                                                                                                                        |
|       │ └─Selection_71             | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_70           | 10000.00  | cop  | table:s, partition:p202012, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_78             | 9990.00   | root | data:Selection_77                                                                                                                                                                                        |
|       │ └─Selection_77             | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_76           | 10000.00  | cop  | table:s, partition:p202101, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_84             | 9990.00   | root | data:Selection_83                                                                                                                                                                                        |
|       │ └─Selection_83             | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_82           | 10000.00  | cop  | table:s, partition:p202102, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_90             | 9990.00   | root | data:Selection_89                                                                                                                                                                                        |
|       │ └─Selection_89             | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_88           | 10000.00  | cop  | table:s, partition:p202103, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_96             | 9990.00   | root | data:Selection_95                                                                                                                                                                                        |
|       │ └─Selection_95             | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_94           | 10000.00  | cop  | table:s, partition:p202104, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_102            | 9990.00   | root | data:Selection_101                                                                                                                                                                                       |
|       │ └─Selection_101            | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_100          | 10000.00  | cop  | table:s, partition:p202105, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_108            | 9990.00   | root | data:Selection_107                                                                                                                                                                                       |
|       │ └─Selection_107            | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_106          | 10000.00  | cop  | table:s, partition:p202106, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_114            | 9990.00   | root | data:Selection_113                                                                                                                                                                                       |
|       │ └─Selection_113            | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_112          | 10000.00  | cop  | table:s, partition:p202107, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_120            | 9990.00   | root | data:Selection_119                                                                                                                                                                                       |
|       │ └─Selection_119            | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_118          | 10000.00  | cop  | table:s, partition:p202108, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_126            | 9990.00   | root | data:Selection_125                                                                                                                                                                                       |
|       │ └─Selection_125            | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_124          | 10000.00  | cop  | table:s, partition:p202109, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_132            | 9990.00   | root | data:Selection_131                                                                                                                                                                                       |
|       │ └─Selection_131            | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_130          | 10000.00  | cop  | table:s, partition:p202110, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       ├─TableReader_138            | 9990.00   | root | data:Selection_137                                                                                                                                                                                       |
|       │ └─Selection_137            | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|       │   └─TableScan_136          | 10000.00  | cop  | table:s, partition:p202111, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
|       └─TableReader_144            | 9990.00   | root | data:Selection_143                                                                                                                                                                                       |
|         └─Selection_143            | 9990.00   | cop  | not(isnull(db_slowlog.s.env))                                                                                                                                                                            |
|           └─TableScan_142          | 10000.00  | cop  | table:s, partition:p202112, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                                                            |
+------------------------------------+-----------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[lysu]

this question same as #15612

and has been partially addressed in the current master for this case

#19210 #18862 but maybe hard to backport to 4.0 or 3.0