clickhouse 慢查询解决方案

单表

使用 prewhere

多表

in代替 join 【等值查询】

记录： 第一次使用 clickhouse join 了 2 张表，耗时 用了 2-3 秒，速度不够

看网上的解决方法是，使用 小表 放 左边，因为 clickhouse 永远都是 左边 去 查右表的数据

JOIN操作时一定要把数据量小的表放在右表，ClickHouse中无论是Left Join 、Right Join还是Inner Join永远都是拿着左表中的每一条记录到右表中查找该记录是否存在，所以右表必须是小表

clickhouse 会把 所有的数据从右表加载到内存，右表数据量过大，还会爆内存，这就很难受

谓词下推

这里举个例子，我们来看两个查询结果一致的 sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


 select  count(vr.id),vr.result_type  ,idc.name,crg.project_name from (
  
	select * from v_result5 vr   

	   WHERE  db_archive_year = 2022  and  db_archive_month = 12  
 ) vr  
 
  join cmdb_idc idc on vr.idc_id = idc.id   

 join cmdb_resource_group crg  on    crg.resource_id  = vr.id 
 
 and crg.resource_type = vr.resource_type 
  
 
  where vr.db_archive_year = 2022 and vr.db_archive_month = 12    and vr.delete_flag = 0
    and crg.db_archive_year = 2022 and crg.db_archive_month = 12  and crg.delete_flag = 0
    and idc.db_archive_year = 2022 and idc.db_archive_month = 12  and  idc.delete_flag = 0
 
  
 group by crg.project_name, idc.name,vr.result_type
 limit 300

这个 sql 首次查询用 了 7秒, 后续查询 都是 接近了 1秒 ， 0.8 秒的查询速度

image-20211213115615770

我们使用谓词下推， join 里面 使用子查询，缩小 join 表的范围，就可以大大减少数据集，我们会看到 上面 有 125万条数据，明显 数据量过大了

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


 select  count(vr.id),vr.result_type  ,idc.name,crg.project_name from (
  
	  SELECT
	    id,
	    idc_id,
	    asset_number,
	    cabinet_id,
	    sn,
	    '服务器' AS result_type,
	    1 AS col_type,
	    2 AS resource_type,
	    db_archive_year,
	    db_archive_month
	    FROM test.cmdb_physical_machine AS cpm
	   WHERE cpm.db_archive_year = 2022  and cpm.db_archive_month = 12  and cpm.delete_flag = 0
 ) vr  
 
  join (select * from cmdb_idc idc  where  idc.db_archive_year = 2022 and idc.db_archive_month = 12  and  idc.delete_flag = 0)  idc on vr.idc_id = idc.id   

 join (select  * from cmdb_resource_group crg  where    crg.db_archive_year = 2022 and crg.db_archive_month = 12   )  crg  on    crg.resource_id  = vr.id 
 
 and crg.resource_type = vr.resource_type 

 group by crg.project_name, idc.name,vr.result_type
 limit 300

在 join 里面使用子查询，就减少为 几千行数据

image-20211213115815864