涉及范围查询时，索引是否应优先放置高基数列？（附files表示例）

Great question—this is one of those index design gotchas that trips up even experienced devs, especially when mixing high-cardinality columns with range filters. Let’s break this down step by step.

First: Should High-Cardinality Columns Come First in Range Queries? #

Short answer: No, not necessarily. The golden rule for composite index order actually takes priority over cardinality:

• Put columns used for equality filters (e.g., ext = 'pdf') first
• Follow with columns used for range filters (e.g., filetime BETWEEN '2023-01-01' AND '2023-12-31')

Cardinality matters only when comparing columns used for the same type of filter (like two equality columns). Once a range filter is hit in a composite index, InnoDB can’t use any columns to the right of that range for index-based filtering—only for covering queries if needed. So equality columns always get priority in the index order.

Choosing Between fe (filetime, ext) and ef (ext, filetime) #

Let’s tie this to your files table, where filetime is high-cardinality (almost unique) and ext is low-cardinality (limited values):

If your common queries look like this: #

SELECT * FROM files 
WHERE ext = 'jpg' 
  AND filetime >= '2024-01-01' AND filetime <= '2024-06-01';

Go with ef (ext, filetime).

• The index first narrows down to all rows matching ext = 'jpg' (a small subset, since ext has limited values)
• Then it uses the sorted filetime values in that subset to quickly find the range you need. This is efficient because filetime is ordered per ext, so the range scan is fast and avoids extra filtering.

If your common queries look like this: #

SELECT * FROM files 
WHERE filetime BETWEEN '2024-01-01' AND '2024-06-01'
  AND ext = 'pdf';

fe (filetime, ext) is better than no index, but not ideal.

• The index will scan all rows in the filetime range, but then has to check each row’s ext value manually (since ext is to the right of the range filter, it can’t be used to narrow the index scan). This means more rows are scanned than necessary, especially if the filetime range is large.

Edge case to consider: #

If you often run queries that only filter on filetime (no ext), then fe would work for those. But if you mix that with queries filtering on both columns, weigh which query is more frequent, or consider adding both indexes (note: this adds write overhead to the table).

Final Recommendation #

Unless your primary use case is filtering solely on filetime ranges, ef (ext, filetime) is the better choice for most mixed equality+range queries. It leverages the equality filter first to shrink the dataset, then uses sorted filetime values for efficient range scanning—something the fe index can’t do when filtering on both columns.

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