文档首页/ MapReduce服务 MRS/ 组件应用开发规范/ ClickHouse应用开发规范/ ClickHouse数据库开发/ ClickHouse数据查询
更新时间：2024-11-20 GMT+08:00
查看PDF
分享

ClickHouse数据查询

数据查询规则

  • 禁止select *查询

    只查询需要的字段可以减少磁盘io和网络io，提升查询性能。

  • 使用uniqCombined替代distinct

    uniqCombined对去重逻辑进行了优化，通过近似去重提升十倍查询性能，如果对查询允许有误差，可以使用uniqCombined替代，否则还继续使用distinct语法。

  • 降低对表的修改频次

    默认场景下ClickHouse执行alter语句是异步执行，对同一张表频繁执行alter操作可能导致业务失败。

  • 多表复杂join拆分为两表join或子查询

    多表复杂join场景，建议拆分为两两表join，且两表join为大小表join，小小表join，尽量避免大大表join。也可以将多表复杂join拆分为子查询模式。

    SELECT name FROM tab_a WHERE id IN (SELECT id FROM tab_b WHERE name = 'xx');

    这里说的大表为条件过滤后的总数据量，千万级以上的数据量可定义为大表。

  • 关联查询必须大表join小表

    对于ClickHouse来说，原则上需要把多表join模型提前加工为宽表模型，但是在一些情况下，多个表，甚至是维度表变化比较频繁情况下，不太适合进行宽表加工处理，不得已必须使用Join模型以实时查询到最新数据。那么join，建议2表join，大表join小表，小表在后（大表join小表），并必须有关联条件。小表的数据量控制在百万~千万行级别，且需要在join前尽量把小表数据通过条件进行有效过滤。

  • join/in/not in需要添加Global关键字

    在通常的join/in/not in时候，需要在前面添加Global关键字，避免查询放大问题。

数据查询建议

  • 建议查询指定分区

    通过指定分区字段会减少底层数据库扫描的文件数量，提升查询性能，实际经验：700个分区的千列大表，需要查询一个分区中有7000万数据，其他699个分区中无数据，虽然只有一个分区有数据，其他分区无数据，但是查询指定分区为百毫秒级性能，没有指定分区查询性能为1~2秒左右，性能相差20倍。

  • 慎用final查询

    在查询语句的最后跟上final，通常是对于ReplacingMergeTree引擎，数据不能完全去重情况下，有些开发人员习惯写final关键字进行实时合并去重操作（merge-on-read），保证查询数据无重复数据。可以通过argMax函数或其他方式规避此问题。

数据修改

  • 建议慎用delete、update的mutation操作

    标准SQL的更新、删除操作是同步的，即客户端要等服务端返回执行结果（通常是int值）；而ClickHouse的update、delete是通过异步方式实现的，当执行update语句时，服务端立即返回执行成功还是失败结果，但是实际上此时数据还没有修改完成，而是在后台排队等着进行真正的修改，可能会出现操作覆盖的情况，也无法保证操作的原子性。

    1. 业务场景要求有update、delete等操作，建议使用ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree引擎，使用方式参见：https://clickhouse.tech/docs/zh/engines/table-engines/mergetree-family/collapsingmergetree/。
  • 建议少或不增删数据列

    业务提前规划列个数，如果将来有更多列要使用，可以规划预留多列，避免在生产系统跑业务过程中进行大量的alter table modify列操作，导致不可以预知的性能、数据一致性问题。

  • 对于批量数据清理，建议根据分区来操作：

    ALTER TABLE table_name DROP PARTITION partition_name;

  • 禁止修改索引列

    对索引列的修改会导致现有索引失效，触发重建索引，期间查询数据不准确。

    如果业务场景必须修改索引列，推荐用ReplacingMergeTree引擎建表，使用数据写入+去重引擎代替数据更新场景：https://clickhouse.tech/docs/zh/engines/table-engines/mergetree-family/collapsingmergetree/。

数据merge

建议谨慎执行optimize操作，Optimize一般会对表做重写操作，建议在业务压力小时候进行操作，否则对IO/MEM/CPU资源有较大消耗，导致业务查询变慢或不可用。

相关文档