【产品名称】
observer，obproxy
【产品版本】
oceanbase-ce 3.1.1
【问题描述】
我有个表，我的sql语句每次要查询一天的数据（大约12w行），这个sql语句第一次执行会超时，第二次大约5s左右返回，第三次就会在1.5s左右返回，后面就不变了。目前的问题是前两次的查询耗时太高，无法满足业务需求，麻烦帮忙定位下问题，谢谢！
建表语句如下：
CREATE TABLE
t_operation_statistic_20
(
id
bigint(20) NOT NULL DEFAULT ‘0’,
cluster_internal_id
int(11) DEFAULT NULL,
db_internal_id
int(11) DEFAULT NULL,
table_internal_id
int(11) DEFAULT NULL,
cmd
int(11) DEFAULT NULL,
ip
varchar(255) DEFAULT NULL,
req_num
bigint(20) unsigned DEFAULT NULL,
succ_num
bigint(20) unsigned DEFAULT NULL,
fail_num
bigint(20) unsigned DEFAULT NULL,
batch_fail_num
bigint(20) unsigned DEFAULT NULL COMMENT ‘批量数失败量’,
io_fail_num
bigint(20) unsigned DEFAULT NULL COMMENT ‘IO失败量’,
timeout_num
bigint(20) unsigned DEFAULT NULL,
abandon_num
bigint(20) unsigned DEFAULT NULL,
batch_num
bigint(20) unsigned DEFAULT NULL,
cas_num
bigint(20) unsigned DEFAULT NULL COMMENT ‘CAS使用数’,
req_pkg_size
bigint(20) unsigned DEFAULT NULL,
resp_pkg_size
bigint(20) unsigned DEFAULT NULL,
latency
bigint(20) unsigned DEFAULT NULL,
latency_200_num
bigint(20) unsigned DEFAULT NULL,
latency_800_num
bigint(20) unsigned DEFAULT NULL,
created_at
datetime NOT NULL,
z_option
int(11) NOT NULL DEFAULT ‘0’,
client_ip
varchar(255) NOT NULL DEFAULT ‘’,
less1
bigint(20) unsigned DEFAULT NULL,
less5
bigint(20) unsigned DEFAULT NULL,
less20
bigint(20) unsigned DEFAULT NULL,
less200
bigint(20) unsigned DEFAULT NULL,
less800
bigint(20) unsigned DEFAULT NULL,
over800
bigint(20) unsigned DEFAULT NULL,
costTime99999
int(11) DEFAULT NULL,
costTime9999
int(11) DEFAULT NULL,
costTime999
int(11) DEFAULT NULL,
costTime99
int(11) DEFAULT NULL,
costTime95
int(11) DEFAULT NULL,
PRIMARY KEY (
id, created_at
),
KEY
i_operation_statistics_created_at (created_at
) BLOCK_SIZE 16384 LOCAL,
KEY
i_cid_dbid_created_at (cluster_internal_id, db_internal_id, created_at
) BLOCK_SIZE 16384 LOCAL,
KEY
idx_lianhe (created_at, cluster_internal_id, cmd
) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by range columns(created_at)
(partition M20220101 values less than (‘2022-01-08 00:00:00’),
partition M20220102 values less than (‘2022-01-15 00:00:00’),
partition M20220103 values less than (‘2022-01-22 00:00:00’),
partition M20220104 values less than (‘2022-02-01 00:00:00’),
partition M20220201 values less than (‘2022-02-08 00:00:00’),
partition M20220202 values less than (‘2022-02-15 00:00:00’),
partition M20220203 values less than (‘2022-02-22 00:00:00’),
partition M20220204 values less than (‘2022-03-01 00:00:00’),
partition M20220301 values less than (‘2022-03-08 00:00:00’),
partition M20220302 values less than (‘2022-03-15 00:00:00’),
partition M20220303 values less than (‘2022-03-22 00:00:00’),
partition M20220304 values less than (‘2022-04-01 00:00:00’));
首先查询行数：
MySQL [dbwww58com_wtable]> SELECT count(*) FROM t_operation_statistic_20 WHERE cluster_internal_id = 2005 AND db_internal_id = 1 AND created_at >= ‘2022-02-05 00:00:00’ AND created_at < ‘2022-02-05 23:59:59’;
±---------+
| count(*) |
±---------+
| 124801 |
±---------+
1 row in set (0.067 sec)
耗时高的sql查询情况如下：
第一次：
MySQL [dbwww58com_wtable]> SELECT sum(req_num) FROM t_operation_statistic_20 WHERE cluster_internal_id = 2005 AND db_internal_id = 1 AND created_at >= ‘2022-02-05 00:00:00’ AND created_at < ‘2022-02-05 23:59:59’;
ERROR 4012 (HY000): Timeout
第二次：
MySQL [dbwww58com_wtable]> SELECT sum(req_num) FROM t_operation_statistic_20 WHERE cluster_internal_id = 2005 AND db_internal_id = 1 AND created_at >= ‘2022-02-05 00:00:00’ AND created_at < ‘2022-02-05 23:59:59’;
±-------------+
| sum(req_num) |
±-------------+
| 6771539050 |
±-------------+
1 row in set (4.941 sec)
第三次：
MySQL [dbwww58com_wtable]> SELECT sum(req_num) FROM t_operation_statistic_20 WHERE cluster_internal_id = 2005 AND db_internal_id = 1 AND created_at >= ‘2022-02-05 00:00:00’ AND created_at < ‘2022-02-05 23:59:59’;
±-------------+
| sum(req_num) |
±-------------+
| 6771539050 |
±-------------+
1 row in set (1.317 sec)
执行计划如下：
MySQL [dbwww58com_wtable]> explain SELECT sum(req_num) FROM t_operation_statistic_20 WHERE cluster_internal_id = 2005 AND db_internal_id = 1 AND created_at >= ‘2022-02-05 00:00:00’ AND created_at < ‘2022-02-05 23:59:59’;
| =====================================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------------------------------------------------
|0 |SCALAR GROUP BY| |1 |713420|
|1 | TABLE SCAN |t_operation_statistic_20(i_cid_dbid_created_at)|124801 |689582|
=====================================================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_SUM(t_operation_statistic_20.req_num)]), filter(nil),
group(nil), agg_func([T_FUN_SUM(t_operation_statistic_20.req_num)])
1 - output([t_operation_statistic_20.req_num]), filter(nil),
access([t_operation_statistic_20.req_num]), partitions(p4)
|
1 row in set (0.004 sec)