为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
- 【TiDB 版本】：v4.0.0
- 【问题描述】：有一条数据量不是很大的sql，查询慢
您好，有一条慢SQL帮忙看看能不能调优，数据量大小: 3614176，查询时间:7s~12s
- sql语句
SELECT
id,
SUM(djsl),
SUM(djje)
FROM
tablea
WHERE
time BETWEEN 201701 AND 201709
GROUP BY id
- 表健康检查：
show stats_healthy
为100
- 建表语句
show create table tablea;
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tablea | CREATE TABLE `tablea` (
`id` varchar(30) DEFAULT NULL,
`name` varchar(132) DEFAULT NULL,
`djsl` bigint(20) DEFAULT NULL,
`djje` decimal(18,2) DEFAULT NULL,
`numa` decimal(18,2) DEFAULT NULL,
`numb` decimal(18,2) DEFAULT NULL,
`numc` decimal(18,2) DEFAULT NULL,
`numd` decimal(18,2) DEFAULT NULL,
`time` int(11) DEFAULT NULL,
KEY `idx_table_id` (`id`),
KEY `idx_table_time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin/*!90000 SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3 */
PARTITION BY RANGE ( `` ) (
PARTITION `p201701` VALUES LESS THAN (201702),
PARTITION `p201702` VALUES LESS THAN (201703),
PARTITION `p201703` VALUES LESS THAN (201704),
PARTITION `p201704` VALUES LESS THAN (201705),
PARTITION `p201705` VALUES LESS THAN (201706),
PARTITION `p201706` VALUES LESS THAN (201707),
PARTITION `p201707` VALUES LESS THAN (201708),
PARTITION `p201708` VALUES LESS THAN (201709),
PARTITION `p201709` VALUES LESS THAN (201710),
PARTITION `p201710` VALUES LESS THAN (201711),
PARTITION `p201711` VALUES LESS THAN (201712),
PARTITION `p201712` VALUES LESS THAN (201801)
) |
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 分区情况(按time分区)
SELECT PARTITION_NAME,TABLE_ROWS,PARTITION_EXPRESSION,PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tablea';
+----------------+------------+----------------------+-----------------------+
| PARTITION_NAME | TABLE_ROWS | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------+----------------------+-----------------------+
| p201701 | 525995 | `time` | 201702 |
| p201702 | 486064 | `time` | 201703 |
| p201703 | 0 | `time` | 201704 |
| p201704 | 524834 | `time` | 201705 |
| p201705 | 555750 | `time` | 201706 |
| p201706 | 0 | `time` | 201707 |
| p201707 | 728309 | `time` | 201708 |
| p201708 | 0 | `time` | 201709 |
| p201709 | 793224 | `time` | 201710 |
| p201710 | 0 | `time` | 201711 |
| p201711 | 0 | `time` | 201712 |
| p201712 | 0 | `time` | 201801 |
+----------------+------------+----------------------+-----------------------+
- explain 和 explian analyze情况
explain.txt (47.4 KB)