ClickHouse 查询优化实用指南：第一部分

本文字数：18076；估计阅读时间：46分钟

数据集介绍

-- Create table with inferred schemaCREATE TABLE trips_small_inferredORDER BY () EMPTYAS SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet');
-- Insert data into table with inferred schemaINSERT INTO trips_small_inferredSELECT * FROM s3Cluster('default','https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet');

--- Display inferred table schemaSHOW CREATE TABLE trips_small_inferred
Query id: d97361fd-c050-478e-b831-369469f0784d
CREATE TABLE nyc_taxi.trips_small_inferred(    `vendor_id` Nullable(String),    `pickup_datetime` Nullable(DateTime64(6, 'UTC')),    `dropoff_datetime` Nullable(DateTime64(6, 'UTC')),    `passenger_count` Nullable(Int64),    `trip_distance` Nullable(Float64),    `ratecode_id` Nullable(String),    `pickup_location_id` Nullable(String),    `dropoff_location_id` Nullable(String),    `payment_type` Nullable(Int64),    `fare_amount` Nullable(Float64),    `extra` Nullable(Float64),    `mta_tax` Nullable(Float64),    `tip_amount` Nullable(Float64),    `tolls_amount` Nullable(Float64),    `total_amount` Nullable(Float64))ORDER BY tuple()

查询日志的使用

-- Find top 5 long running queries from nyc_taxi database in the last 1 hourSELECT    type,    event_time,    query_duration_ms,    query,    read_rows,    tablesFROM clusterAllReplicas(default, system.query_log)WHERE has(databases, 'nyc_taxi') AND (event_time >= (now() - toIntervalMinute(60))) AND type='QueryFinish'ORDER BY query_duration_ms DESCLIMIT 5FORMAT VERTICAL
Query id: e3d48c9f-32bb-49a4-8303-080f59ed1835
Row 1:──────type:              QueryFinishevent_time:        2024-11-27 11:12:36query_duration_ms: 2967query:             WITH  dateDiff('s', pickup_datetime, dropoff_datetime) as trip_time,  trip_distance / trip_time * 3600 AS speed_mphSELECT  quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)FROM  nyc_taxi.trips_small_inferredWHERE  speed_mph > 30FORMAT JSONread_rows:         329044175tables:            ['nyc_taxi.trips_small_inferred']
Row 2:──────type:              QueryFinishevent_time:        2024-11-27 11:11:33query_duration_ms: 2026query:             SELECT     payment_type,    COUNT() AS trip_count,    formatReadableQuantity(SUM(trip_distance)) AS total_distance,    AVG(total_amount) AS total_amount_avg,    AVG(tip_amount) AS tip_amount_avgFROM     nyc_taxi.trips_small_inferredWHERE     pickup_datetime >= '2009-01-01' AND pickup_datetime < '2009-04-01'GROUP BY     payment_typeORDER BY     trip_count DESC;
read_rows:         329044175tables:            ['nyc_taxi.trips_small_inferred']
Row 3:──────type:              QueryFinishevent_time:        2024-11-27 11:12:17query_duration_ms: 1860query:             SELECT  avg(dateDiff('s', pickup_datetime, dropoff_datetime))FROM nyc_taxi.trips_small_inferredWHERE passenger_count = 1 or passenger_count = 2FORMAT JSONread_rows:         329044175tables:            ['nyc_taxi.trips_small_inferred']
Row 4:──────type:              QueryFinishevent_time:        2024-11-27 11:12:31query_duration_ms: 690query:             SELECT avg(total_amount) FROM nyc_taxi.trips_small_inferred WHERE trip_distance > 5FORMAT JSONread_rows:         329044175tables:            ['nyc_taxi.trips_small_inferred']
Row 5:──────type:              QueryFinishevent_time:        2024-11-27 11:12:44query_duration_ms: 634query:             SELECTvendor_id,avg(total_amount),avg(trip_distance),FROMnyc_taxi.trips_small_inferredGROUP BY vendor_idORDER BY 1 DESCFORMAT JSONread_rows:         329044175tables:            ['nyc_taxi.trips_small_inferred']

-- Top queries by memory usage SELECT    type,    event_time,    query_id,    formatReadableSize(memory_usage) AS memory,    ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')] AS userCPU,    ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')] AS systemCPU,    (ProfileEvents['CachedReadBufferReadFromCacheMicroseconds']) / 1000000 AS FromCacheSeconds,    (ProfileEvents['CachedReadBufferReadFromSourceMicroseconds']) / 1000000 AS FromSourceSeconds,    normalized_query_hashFROM clusterAllReplicas(default, system.query_log)WHERE has(databases, 'nyc_taxi') AND (type='QueryFinish') AND ((event_time >= (now() - toIntervalDay(2))) AND (event_time <= now())) AND (user NOT ILIKE '%internal%')ORDER BY memory_usage DESCLIMIT 30

-- Disable filesystem cacheset enable_filesystem_cache = 0;
-- Run query 1WITH  dateDiff('s', pickup_datetime, dropoff_datetime) as trip_time,  trip_distance / trip_time * 3600 AS speed_mphSELECT  quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)FROM  nyc_taxi.trips_small_inferredWHERE  speed_mph > 30FORMAT JSON
----1 row in set. Elapsed: 1.699 sec. Processed 329.04 million rows, 8.88 GB (193.72 million rows/s., 5.23 GB/s.)Peak memory usage: 440.24 MiB.
-- Run query 2SELECT     payment_type,    COUNT() AS trip_count,    formatReadableQuantity(SUM(trip_distance)) AS total_distance,    AVG(total_amount) AS total_amount_avg,    AVG(tip_amount) AS tip_amount_avgFROM     nyc_taxi.trips_small_inferredWHERE     pickup_datetime >= '2009-01-01' AND pickup_datetime < '2009-04-01'GROUP BY     payment_typeORDER BY     trip_count DESC;
--- 4 rows in set. Elapsed: 1.419 sec. Processed 329.04 million rows, 5.72 GB (231.86 million rows/s., 4.03 GB/s.)Peak memory usage: 546.75 MiB.
-- Run query 3SELECT  avg(dateDiff('s', pickup_datetime, dropoff_datetime))FROM nyc_taxi.trips_small_inferredWHERE passenger_count = 1 or passenger_count = 2FORMAT JSON
---1 row in set. Elapsed: 1.414 sec. Processed 329.04 million rows, 8.88 GB (232.63 million rows/s., 6.28 GB/s.)Peak memory usage: 451.53 MiB.

-- Count number of rows in table SELECT count()FROM nyc_taxi.trips_small_inferred
Query id: 733372c5-deaf-4719-94e3-261540933b23
   ┌───count()─┐1. │ 329044175 │ -- 329.04 million   └───────────┘

深入分析慢查询：EXPLAIN 工具的使用

EXPLAIN indexes = 1WITH    dateDiff('s', pickup_datetime, dropoff_datetime) AS trip_time,    (trip_distance / trip_time) * 3600 AS speed_mphSELECT quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)FROM nyc_taxi.trips_small_inferredWHERE speed_mph > 30
Query id: f35c412a-edda-4089-914b-fa1622d69868
   ┌─explain─────────────────────────────────────────────┐1. │ Expression ((Projection + Before ORDER BY))         │2. │   Aggregating                                       │3. │     Expression (Before GROUP BY)                    │4. │       Filter (WHERE)                                │5. │         ReadFromMergeTree (nyc_taxi.trips_small_inferred) │   └─────────────────────────────────────────────────────┘

EXPLAIN PIPELINEWITH    dateDiff('s', pickup_datetime, dropoff_datetime) AS trip_time,    (trip_distance / trip_time) * 3600 AS speed_mphSELECT quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)FROM nyc_taxi.trips_small_inferredWHERE speed_mph > 30
Query id: c7e11e7b-d970-4e35-936c-ecfc24e3b879
    ┌─explain─────────────────────────────────────────────────────────────────────────────┐ 1. │ (Expression)                                                                        │ 2. │ ExpressionTransform × 59                                                            │ 3. │   (Aggregating)                                                                     │ 4. │   Resize 59 → 59                                                                    │ 5. │     AggregatingTransform × 59                                                       │ 6. │       StrictResize 59 → 59                                                          │ 7. │         (Expression)                                                                │ 8. │         ExpressionTransform × 59                                                    │ 9. │           (Filter)                                                                  │10. │           FilterTransform × 59                                                      │11. │             (ReadFromMergeTree)                                                     │12. │             MergeTreeSelect(pool: PrefetchedReadPool, algorithm: Thread) × 59 0 → 1 │

减少 Nullable 列的使用

-- Find non-null values columns SELECT    countIf(vendor_id IS NULL) AS vendor_id_nulls,    countIf(pickup_datetime IS NULL) AS pickup_datetime_nulls,    countIf(dropoff_datetime IS NULL) AS dropoff_datetime_nulls,    countIf(passenger_count IS NULL) AS passenger_count_nulls,    countIf(trip_distance IS NULL) AS trip_distance_nulls,    countIf(fare_amount IS NULL) AS fare_amount_nulls,    countIf(mta_tax IS NULL) AS mta_tax_nulls,    countIf(tip_amount IS NULL) AS tip_amount_nulls,    countIf(tolls_amount IS NULL) AS tolls_amount_nulls,    countIf(total_amount IS NULL) AS total_amount_nulls,    countIf(payment_type IS NULL) AS payment_type_nulls,    countIf(pickup_location_id IS NULL) AS pickup_location_id_nulls,    countIf(dropoff_location_id IS NULL) AS dropoff_location_id_nullsFROM trips_small_inferredFORMAT VERTICAL
Query id: 4a70fc5b-2501-41c8-813c-45ce241d85ae
Row 1:──────vendor_id_nulls:           0pickup_datetime_nulls:     0dropoff_datetime_nulls:    0passenger_count_nulls:     0trip_distance_nulls:       0fare_amount_nulls:         0mta_tax_nulls:             137946731tip_amount_nulls:          0tolls_amount_nulls:        0total_amount_nulls:        0payment_type_nulls:        69305pickup_location_id_nulls:  0dropoff_location_id_nulls: 0

-- Identify low cardinality columnsSELECT    uniq(ratecode_id),    uniq(pickup_location_id),    uniq(dropoff_location_id),    uniq(vendor_id)FROM trips_small_inferredFORMAT VERTICAL
Query id: d502c6a1-c9bc-4415-9d86-5de74dd6d932
Row 1:──────uniq(ratecode_id):         6uniq(pickup_location_id):  260uniq(dropoff_location_id): 260uniq(vendor_id):           3

选择合适的数据类型

-- Find min/max values for the payment_type fieldSELECT    min(payment_type),max(payment_type),    min(passenger_count), max(passenger_count)FROM trips_small_inferred
Query id: 4306a8e1-2a9c-4b06-97b4-4d902d2233eb
   ┌─min(payment_type)─┬─max(payment_type)─┐1. │                 1 │                 4 │   └───────────────────┴───────────────────┘

应用优化并评估效果

-- Create table with optimized data CREATE TABLE trips_small_no_pk(    `vendor_id` LowCardinality(String),    `pickup_datetime` DateTime,    `dropoff_datetime` DateTime,    `passenger_count` UInt8,    `trip_distance` Float32,    `ratecode_id` LowCardinality(String),    `pickup_location_id` LowCardinality(String),    `dropoff_location_id` LowCardinality(String),    `payment_type` Nullable(UInt8),    `fare_amount` Decimal32(2),    `extra` Decimal32(2),    `mta_tax` Nullable(Decimal32(2)),    `tip_amount` Decimal32(2),    `tolls_amount` Decimal32(2),    `total_amount` Decimal32(2))ORDER BY tuple();
-- Insert the data INSERT INTO trips_small_no_pk SELECT * FROM trips_small_inferred

SELECT    `table`,    formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,    formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,    sum(rows) AS rowsFROM system.partsWHERE (active = 1) AND ((`table` = 'trips_small_no_pk') OR (`table` = 'trips_small_inferred'))GROUP BY    database,    `table`ORDER BY size DESC
Query id: 72b5eb1c-ff33-4fdb-9d29-dd076ac6f532
   ┌─table────────────────┬─compressed─┬─uncompressed─┬──────rows─┐1. │ trips_small_inferred │ 7.38 GiB   │ 37.41 GiB    │ 329044175 │2. │ trips_small_no_pk    │ 4.89 GiB   │ 15.31 GiB    │ 329044175 │   └──────────────────────┴────────────┴──────────────┴───────────┘

如何通过主键优化查询性能

CREATE TABLE trips_small_pk(    `vendor_id` UInt8,    `pickup_datetime` DateTime,    `dropoff_datetime` DateTime,    `passenger_count` UInt8,    `trip_distance` Float32,    `ratecode_id` LowCardinality(String),    `pickup_location_id` UInt16,    `dropoff_location_id` UInt16,    `payment_type` Nullable(UInt8),    `fare_amount` Decimal32(2),    `extra` Decimal32(2),    `mta_tax` Nullable(Decimal32(2)),    `tip_amount` Decimal32(2),    `tolls_amount` Decimal32(2),    `total_amount` Decimal32(2))PRIMARY KEY (passenger_count, pickup_datetime, dropoff_datetime);
-- Insert the data INSERT INTO trips_small_pk SELECT * FROM trips_small_inferred

EXPLAIN indexes = 1SELECT    payment_type,    COUNT() AS trip_count,    formatReadableQuantity(SUM(trip_distance)) AS total_distance,    AVG(total_amount) AS total_amount_avg,    AVG(tip_amount) AS tip_amount_avgFROM nyc_taxi.trips_small_pkWHERE (pickup_datetime >= '2009-01-01') AND (pickup_datetime < '2009-04-01')GROUP BY payment_typeORDER BY trip_count DESC
Query id: 30116a77-ba86-4e9f-a9a2-a01670ad2e15
    ┌─explain──────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 1. │ Expression ((Projection + Before ORDER BY [lifted up part]))                                                     │ 2. │   Sorting (Sorting for ORDER BY)                                                                                 │ 3. │     Expression (Before ORDER BY)                                                                                 │ 4. │       Aggregating                                                                                                │ 5. │         Expression (Before GROUP BY)                                                                             │ 6. │           Expression                                                                                             │ 7. │             ReadFromMergeTree (nyc_taxi.trips_small_pk)                                                          │ 8. │             Indexes:                                                                                             │ 9. │               PrimaryKey                                                                                         │10. │                 Keys:                                                                                            │11. │                   pickup_datetime                                                                                │12. │                 Condition: and((pickup_datetime in (-Inf, 1238543999]), (pickup_datetime in [1230768000, +Inf))) │13. │                 Parts: 9/9                                                                                       │14. │                 Granules: 5061/40167                                                                             │    └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘