Cassandra 3.x中首个聚类键查询性能超预期的原因咨询

Great question! Let's break down why your third query performs drastically better than the second, even though neither uses the partition key.

First, let's recap your table schema for clarity:

CREATE TABLE sp_db.prod_tag (
    year int,
    name text,
    group int,
    sn text,
    factory_id bigint,
    PRIMARY KEY ((year), name, group)
) WITH CLUSTERING ORDER BY (name ASC, group ASC);

You ran three queries against 1 million rows of data:

1. Partition key query: select count(*) from sp_db.prod_tag where year = 2015; returned 33328 rows in ~90ms (expected fast performance, as it targets a single partition).
2. Second clustering key query: select count(*) from sp_db.prod_tag where group = 104 ALLOW FILTERING; returned 3938 rows in ~800ms (expected slow, as it requires full table scans of every partition).
3. First clustering key query: select count(*) from sp_db.prod_tag where name = '06d7d' ALLOW FILTERING; returned 3 rows in ~6ms (surprisingly fast, despite no partition key).

Here's the explanation for the third query's speed: #

• Clustering column order enables efficient seeks: Since name is the first clustering column, every partition (grouped by year) stores rows sorted by name first, then group. When Cassandra executes the third query, it can perform a targeted seek in each partition instead of scanning all rows. For each partition, it jumps directly to the position where name='06d7d' would exist (thanks to sorted storage). If no matching rows are found, it immediately moves to the next partition—no full partition scan needed.
• Minimal matching data reduces work: Your query only returns 3 rows, meaning name='06d7d' exists in very few partitions (possibly just one). Most partitions are skipped almost instantly after checking for the target name, cutting down the total work Cassandra has to do drastically.
• Contrast with the second query: For group=104 (the second clustering column), there's no inherent order to group values within a partition. Cassandra has to scan every single row in every partition to find matches, since rows are sorted by name first. Even though the result count is higher, the total number of rows scanned is orders of magnitude larger than in the third query.
• Cache may boost speed further: It's possible the small dataset for name='06d7d' was already cached in Cassandra's key cache or row cache, but even without caching, the sorted clustering column structure would make this query far faster than filtering on a later clustering column.

Note: While this query performed well here, ALLOW FILTERING is still not recommended for production use cases. For frequent queries by name without the partition key, consider creating a secondary index or a materialized view tailored to that access pattern.

内容的提问来源于stack exchange，提问作者brxie