InnoDB复合索引的选择性、基数与列序优化及表设计咨询

Hey there! Let's dive into your InnoDB composite index questions, then optimize your table design and queries for your specific use case (since you mentioned INSERTs are rare and SELECT performance is king).

1. Selectivity vs. Cardinality: Key Differences #

Let's clarify these two terms clearly:

• Cardinality: This is the absolute number of unique values in a column (or index). For example, your one column has a maximum of 10 unique values, so its cardinality is ~10.
• Selectivity: This is the ratio of unique values to total rows in the table, calculated as Cardinality / Total Rows. It measures how effectively a column can filter data. If your table has 100,000 rows, one's selectivity is 10/100000 = 0.0001 (very low), while four's selectivity would be ~36500/100000 = 0.365 (much higher).

The core takeaway: High selectivity means each value maps to far fewer rows, making the index much more efficient at narrowing down results.

2. When to Prioritize Selectivity vs. Cardinality #

• Prioritize Selectivity for columns used in exact matches (= conditions) in the WHERE clause. Putting high-selectivity columns first in a composite index lets the database quickly prune most of the dataset, leaving only a small subset to process with subsequent columns.
• Prioritize Cardinality (in context) for columns used in range queries (>=, <) or sorting. Once you've filtered with high-selectivity columns, a higher-cardinality column in the index will keep the remaining data ordered efficiently, reducing the need for expensive operations like filesort. Note: Cardinality supports selectivity—higher cardinality (relative to table size) directly leads to higher selectivity.

3. Leftmost Prefix Rule, Cardinality, and Selectivity #

Your understanding of the leftmost prefix rule is correct, but let's refine the "filtering power" part:
The goal of ordering composite index columns is to place high-selectivity columns first, not just high-cardinality ones. For example, if your table has 1M rows, three (cardinality 1000) has a selectivity of 0.001, while four (cardinality 36500) has a selectivity of 0.0365—so four is better at filtering, even though its cardinality is higher.

When you build a composite index with high-selectivity columns first:

• The overall index cardinality will be very high (since the combination of unique values from each column creates many distinct index entries).
• Selectivity and cardinality are directly correlated: higher index cardinality (relative to total rows) means higher overall selectivity.

InnoDB's B+Tree stores composite index entries sorted by the leftmost column first, then the next, etc. So after exact matches on the left columns, the remaining columns in the index are already ordered—perfect for range queries and sorting.

4. Optimizing Indexes & Table Design for Your Queries #

First, let's recap your table's column cardinalities:

• one: ~10, two: ~100, three: ~1000, four: ~36500
• Core requirement: one+two+three+four must be unique, INSERTs are rare, SELECT performance is critical.

Best Index & Primary Key Choice #

Since you need uniqueness on those four columns and want maximum SELECT performance, set them as your InnoDB clustered primary key:

ALTER TABLE `data_list` ADD PRIMARY KEY (`one`, `two`, `three`, `four`);

InnoDB stores all table data directly in the clustered primary key's leaf nodes, so queries using this index won't need to "jump" to a secondary index (no "table lookup" overhead). This is ideal for your read-heavy use case.

How This Index Covers All Your Queries #

Let's break down each query:

Query A (Exact Match on All Columns)

The primary key allows the database to directly locate the single row (or small set of rows) matching all four conditions—this is the fastest possible lookup.

Query B (Exact Match on one/two/three + four Range + Sort)

After matching one, two, three exactly, the index's four values are sorted. The database can quickly scan the range of four values without extra sorting (no Using filesort), and since it's a clustered index, all columns are already available in the index.

Query C (Exact Match on one/two + four Range + Sort)

With one and two matched, the index is ordered by three then four. The database can scan the sorted entries, filter for the four range, and the existing order of three + four matches your ORDER BY two, three, four (since two is fixed, it's equivalent to sorting by three then four)—no extra sorting needed.

Query D (Join with three_list + Exact Match on one/two + four Range + Sort)

The join condition three = l_three acts like an IN clause on three. Since the index is ordered by three after one/two, the database can quickly locate all rows where three is in the three_list set. From there, it filters the four range and uses the index's sorted order to avoid filesort for your ORDER BY clause.

Is the Lack of an Auto-Increment Primary Key a Defect? #

For your use case: No, it's actually better. Auto-increment primary keys are great for write-heavy workloads because they avoid page splits during inserts, but your INSERTs are extremely rare. Using your unique business columns as the clustered primary key eliminates table lookups and makes all your critical queries as fast as possible.

Alternative Table Design (If Needed) #

If you ever need to add more columns or adjust, the current structure is solid. The only potential tweak would be adding a secondary index if you had a query that didn't use the left prefix of the primary key, but none of your current queries require that.

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