查询耗时高定位求助

【产品名称】

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)

你outline 把执行计划给固化下来, 重试一下,看看效果呢 

如何使用outline 参考这篇文章 https://open.oceanbase.com/answer/detail?id=13700401 (在问答里面搜索outline)


看一下日志第一次执行是在哪里报错的呢,这个sql看起来挺简单的,生成计划应该挺快的才对。

看起来应该走索引

做一次手动合并哪?