1 mysql中的操作
show index from tbiri20;
alter table tbiri20 add index index_test(RULE_CODE);
alter table tbiri20 drop index index_test;
增加索引之前
(1)查询记录总量
select count(1) from tbiri20;
输出9305949,耗时1.915s。
(2)查询最早的时间
select min(START_TIME) from tbiri20;
输出2022-11-03 12:35:31.043,耗时2.336s。
(3)查询每个规则报警的最新时间
select RULE_CODE,max(START_TIME) from tbiri20 GROUP BY RULE_CODE;
输出11469条,耗时7.273s。
select RULE_CODE,max(START_TIME) from tbiri20 where START_TIME > CURDATE() GROUP BY RULE_CODE;
输出10421条,耗时6.556s。
(4)查询每个规则当天报警的最新时间
select RULE_CODE,newtime from (select RULE_CODE,max(START_TIME) as newtime from tbiri20 GROUP BY RULE_CODE) t1 where newtime> CURDATE();
输出10421条,耗时7.375条。
(5)查询条数
select count(1) from (select RULE_CODE,max(START_TIME) from tbiri20 where START_TIME > CURDATE() GROUP BY RULE_CODE) t1;
输出10421条,耗时6.297s。
2 clickhouse使用mysql引擎
2.1 方式一
想数据存入mysql的时候,能向clickhouse中也存一份,方便后期的查询,
那么clickhouse的mysql引擎完美支持。
我们向mysql中插入数据,数据会同步到clickhouse中。
我们向clickhouse中插入数据,数据也会同步到mysql中。
mysql中建表
CREATE TABLE `person` (
`id` int NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
clickhouse中建表
CREATE TABLE default.mysql_to_ch_test1
(
`id` String,
`name` String
)
ENGINE = MySQL('192.168.43.232:3306',
'iplatdb',
'person',
'root',
'bigdata');
2.2 方式二
这个只是一次性的读入,后期双方的修改都不会同步。
CREATE TABLE mysql_to_ch_test2 ENGINE = MergeTree ORDER BY id AS SELECT * FROM mysql('192.168.43.232:3306', 'iplatdb', 'person', 'root', 'bigdata');
3 clickhouse的操作
(1)查询记录总量
select count(1) from tbiri20;
输出9305949,耗时4ms。
(2)查询最早的时间
select min(START_TIME) from tbiri20;
输出2022-11-03 12:35:31.043,耗时37ms。
(3)查询每个规则报警的最新时间
select RULE_CODE,max(START_TIME) from tbiri20 GROUP BY RULE_CODE;
输出11469条,耗时499ms。
select RULE_CODE,max(START_TIME) from tbiri20 where START_TIME > today() GROUP BY RULE_CODE;
输出10421条,耗时449ms。
(4)查询每个规则当天报警的最新时间
select RULE_CODE,newtime from (select RULE_CODE,max(START_TIME) as newtime from tbiri20 GROUP BY RULE_CODE) t1 where newtime> today();
输出10421条,耗时416ms。
(5)查询条数
select count(1) from (select RULE_CODE,max(START_TIME) from tbiri20 where START_TIME > today() GROUP BY RULE_CODE) t1;
输出10421条,耗时465ms。