Cassandra中SASI索引与普通索引的差异及最优配置咨询

Hey there! Great questions about Cassandra's SASI indexes—let's dive into the details to clear things up for you.

1. Core Differences Between SASI Indexes and Regular Secondary Indexes #

First off, SASI isn't just a "LIKE-enabled" version of regular secondary indexes—there are some fundamental differences under the hood:

• Fuzzy Query Support: This is the most obvious and impactful difference. Regular secondary indexes only handle exact matches (e.g., WHERE username = 'johndoe'), while SASI indexes natively support prefix (LIKE 'john%'), suffix (LIKE '%doe'), and substring (LIKE '%ohn%') matches without forcing full table scans.
• Index Structure: Regular secondary indexes store entries tied to individual partitions, which means querying them on high-cardinality columns can lead to scattered lookups across multiple nodes (and slow performance). SASI uses an inverted index structure, similar to full-text search engines, making it far more efficient for text-based and high-cardinality column queries.
• Performance Tradeoffs: Regular indexes are lightweight during writes but struggle with complex queries. SASI indexes have higher write overhead because they need to build and update the inverted index, but they deliver way better read performance for fuzzy or high-cardinality use cases.
• Use Case Fit: Stick with regular secondary indexes for low-cardinality columns (like status or category) where you only need exact matches. SASI shines when you need partial string matches—think usernames, email addresses, or product names where users might search with partial terms.

2. Optimal Configurations for SASI Parameters & Official Guidelines #

Let's break down each key parameter and when to use what:

mode #

This controls what kind of fuzzy matches the index supports:

• PREFIX: Best for most common use cases (e.g., searching for users by the start of their username). It's the fastest mode because it only builds indexes for prefixes of your values. Use this if you only need LIKE 'X%' queries.
• SUFFIX: Use this if you need suffix matches (LIKE '%X'), like searching for emails ending with @example.com. Performance is worse than PREFIX because suffix indexing is more computationally heavy.
• CONTAINS: Use this only if you need substring matches (LIKE '%X%'). It's the slowest mode but the most flexible—reserve it for cases where users might search for terms anywhere in the string.

analyzer_class #

This defines how the index processes text values:

• org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer: Default and recommended for most text scenarios. It handles tokenization (splitting text into words), lowercase conversion (if case_sensitive is false), and removes stopwords (like "the" or "and") to keep the index efficient.
• org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer: Use this for non-text values or exact string matches (like IDs, serial numbers, or codes). It doesn't split values into tokens—so my-id-123 is treated as a single entry.
• org.apache.cassandra.index.sasi.analyzer.SimpleAnalyzer: Splits text on non-alphanumeric characters (so "john.doe" becomes "john" and "doe") and converts to lowercase. Good for simple text splitting without stopword removal.

case_sensitive #

• false: Recommended for most user-facing search scenarios. Users rarely care about case when searching for usernames, emails, or product names, so this avoids missing matches due to capitalization.
• true: Use this only when exact case matters—like distinguishing between "JohnDoe" and "johndoe" for security-sensitive usernames.

Official Recommendations #

Cassandra's official documentation provides clear guidelines for SASI index configuration. A common recommended setup for prefix-based username search looks like this:

CREATE CUSTOM INDEX idx_users_username ON users (username)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
    'mode': 'PREFIX',
    'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
    'case_sensitive': 'false'
};

Just keep in mind: SASI indexes have higher write latency than regular indexes, so avoid adding them to columns that get extremely frequent writes (like real-time event logs).

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