The leftmost prefix rule falls straight out of B+ tree ordering
InnoDB secondary indexes are B+ trees, and the keys inside each node are sorted by the lexicographic comparison of the whole indexed column tuple. For an index on (a, b, c) the physical order compares a first, then b when a ties, then c when both a and b tie. The order is lexicographic, and once a differs, the remaining columns never get a chance to participate.
The leftmost prefix rule is a direct corollary of that ordering: only conditions starting at the first column and continuing consecutively let the optimizer build a bounded range to hand to the B+ tree. WHERE a = 1 can locate the whole a=1 region. Adding AND b = 2 narrows it further. Skipping a to ask WHERE b = 2 turns the question into "among every leaf in the tree, the ones where b equals 2" — there is no usable ordering left, so the only option is a full scan.
The dictionary analogy makes it stick: a name stored as the five characters surname, given name, and so on is sorted in dictionary order, so you can look up the last three characters efficiently because you know the first two confine the search to a region. You cannot start from the third character, because without knowing the values of the first two you have no starting point.
Range conditions are the second common misconception. With an index on (a, b, c) and a query of WHERE a = 1 AND b > 5 AND c = 3, the index is effective for a and b, but c cannot further shrink the scanned range. The values of b between 2 and 100 are spread across many regions, and c remains sorted within each of them, but those regions are not contiguous and cannot be combined into a single range. MySQL instead locates rows via a and b and applies c as a filter inside the index through Index Condition Pushdown, rather than filtering after the table lookup.
EXPLAIN FORMAT=JSON
SELECT id, name, created_at
FROM orders
WHERE user_id = 1001
AND status = 1
AND created_at > '2026-01-01'
AND channel = 'app';
-- key : idx_user_status_time -> a、b 定位区间，c(实际是第四列) 作为 ICP 过滤
-- key_parts : user_id, status -> 注意 created_at 之后的列不出现在 key_parts 里
-- Extra : "Using index condition" 表示条件下推生效，减少了回表次数Ordering the columns: equality, then range, with sorting treated separately
The first rule for column order is to place equality conditions (the = operator and IN) before range conditions. This is not folklore; it follows from the lexicographic order of the B+ tree. An equality condition cuts the tree into a few precisely determined regions, and a range condition only forms a locatable contiguous region if every preceding column has already been pinned down. Writing WHERE b > 5 AND a = 1 is semantically identical but gives the optimizer far less to work with.
The second consideration is selectivity. Put high-cardinality columns — many distinct values, few duplicates — first, so the index discards most data at the very first level. The opposite mistake is leading with something like gender or is_deleted. With two or three possible values they filter almost nothing at the tree root, merely occupy a prefix slot, and force every later column to be stored at a less useful alignment.
Selectivity is not the only criterion, though. Balance three factors: the relative position of equality and range conditions, cardinality, and where the sorting column lands. Consider a typical three-column query: orders for a given user_id, within the last 30 days via created_at, returned sorted by amount descending. Should the index be (user_id, amount, created_at) or (user_id, created_at, amount)?
The answer is the latter, because the range-plus-sort combination outranks the equality-plus-sort one. With created_at before amount, the index itself is ordered by created_at, so the rows for that user within the time window come out already sorted and almost no extra sorting is needed. amount, sitting after the range column, cannot contribute to ordering and survives only as a post-lookup filter. In the opposite order, amount is usable for sorting but created_at can no longer form a range, so MySQL must scan the user entire history and filter by time — potentially orders of magnitude more work. The pragmatic pattern is to build (user_id, created_at, amount) for the primary query, then add a narrower (user_id, amount) index for secondary access patterns that sort without a time window.
-- 主查询：用户 + 时间范围 + 按金额倒序
EXPLAIN SELECT id, amount FROM orders
WHERE user_id = 1001 AND created_at >= NOW() - INTERVAL 30 DAY
ORDER BY amount DESC LIMIT 20;
-- 推荐索引：范围列在前，排序靠索引有序性天然满足
ALTER TABLE orders ADD INDEX idx_user_time_amount (user_id, created_at, amount);
-- 辅助查询：用户 + 按金额排序，不限时间
ALTER TABLE orders ADD INDEX idx_user_amount (user_id, amount);
-- 对比：把排序列放在范围列之前的写法，Extra 里会多出 Using filesort
ALTER TABLE orders ADD INDEX idx_user_amount_time (user_id, amount, created_at);Covering indexes: making the table lookup disappear
The leaf nodes of an InnoDB secondary index hold the indexed column values plus the primary key, not the whole row. So a SELECT * must take that primary key and look the row up again in the clustered index — a table lookup. Those lookups are random, because the clustered index is physically ordered by primary key while the key you just read came from a differently ordered secondary index, and large volumes of random IO can multiply the cost of an otherwise fast query several times over.
A covering index solves this by including every column the query needs to read inside the index. Once the leftmost prefix matches and all required columns are available from the index, EXPLAIN reports Using index and no table lookup happens at all. Be precise about what "needs" means: the condition columns matter as much as the projection. Columns used in WHERE, JOIN, and ORDER BY all count, and leaving one out voids the whole exercise.
The cost is index size. A larger index means fewer index pages fit in the buffer pool, higher maintenance overhead on insert, and slower writes. Practically, aim covering indexes at high-frequency queries with a modest column count. As a rule of thumb, if the projection exceeds seven or eight columns, or includes TEXT and BLOB, a covering index is usually the wrong trade — accept the lookup instead.
Covering indexes have a useful side effect: they can rescue columns that would otherwise be unusable. In WHERE a = 1 AND b > 5 AND c = 3, column c after the range predicate can only filter. But with an index on (a, b, c, d) and a projection of just c and d, both columns live in the index, so MySQL never needs a table lookup to read c. c becomes a column it was going to read anyway, and the filter cost disappears together with the lookup cost. This is why the practical order of work is to read the query columns first and derive the index columns second, never the reverse.
-- 覆盖索引：SELECT 列表 + WHERE 条件全部落在索引内
ALTER TABLE orders ADD INDEX idx_cover (user_id, status, created_at, amount);
EXPLAIN SELECT amount, created_at FROM orders
WHERE user_id = 1001 AND status = 1 AND created_at > '2026-06-01';
-- type : ref
-- Extra : "Using index" -> 未回表
-- 反例：多查一列，覆盖失效，立刻出现 Using index condition
EXPLAIN SELECT amount, created_at, remark FROM orders
WHERE user_id = 1001 AND status = 1 AND created_at > '2026-06-01';
-- Extra : "Using where; Using index condition" -> 需要回表读 remark
-- 验证索引列顺序是否符合预期（key_parts 即按序匹配到的索引列）
EXPLAIN FORMAT=JSON SELECT amount FROM orders
WHERE user_id = 1001 AND status = 1 AND created_at > '2026-06-01';When indexes really stop working: real cases and false intuitions
The most classic category is a function or expression wrapped around an indexed column. In WHERE YEAR(created_at) = 2026 the indexed column is no longer bare, B+ tree ordering is broken, and the optimizer falls back to a full scan. Move the computation to the constant side instead: created_at >= '2026-01-01' AND created_at < '2027-01-01' is still monotonic, so the index works as before. The same applies to arithmetic, casts, and concatenation applied to indexed columns.
The second is implicit type conversion. Almost everyone believes this cannot happen, yet it is among the most frequently reported causes of index failure in the Chinese MySQL community. When the two sides of a comparison have different types, MySQL converts one side, and the direction often works against the index. The textbook case: phone is a varchar column but the query passes a number, so the string column is cast to numeric and the index stops being usable. Align the types and pass the parameter as a string. In the reverse case — a numeric column compared against a quoted string — the conversion happens on the constant side and the index still works.
The third is a leading wildcard. LIKE '%abc' has no locatable starting point and requires a full scan, while LIKE 'abc%' has a determined prefix and behaves like a range condition. LIKE '%abc%' is likewise unusable. If arbitrary substring matching is genuinely required, use a full-text index or a dedicated search system rather than a B+ tree index.
The fourth is a character set or collation mismatch. When joining two tables whose supposedly matching columns use different character sets — say utf8mb4 on one side and latin1 on the other — MySQL cannot compare the two index values directly and must convert one side, which disables the index. Standardize the charset and collation across the database and never use different collations on join keys.
A few apparent failures deserve clarification. Implicit conversion does not fire every time, because the direction determines which side is converted. Operators like != and NOT IN usually cannot use an index, but with very high selectivity a full scan can genuinely be faster. Type-mismatched join conditions can sometimes be handled by the index merge optimization, but you must actually see Using index merge in the plan to claim it worked. The only reliable arbiter is the execution plan, never reasoning about it in the abstract.
-- ① 函数包裹索引列 -> 全表扫描
EXPLAIN SELECT id FROM orders WHERE YEAR(created_at) = 2026;
-- type: ALL, key: NULL
-- 改写：运算移到常量侧，索引恢复
EXPLAIN SELECT id FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- type: range, key: idx_user_time_amount
-- ② 隐式类型转换：varchar 列与数字比较
EXPLAIN SELECT id FROM users WHERE phone = 13800138000;
-- type: ALL（字符串列被转为数值）
EXPLAIN SELECT id FROM users WHERE phone = '13800138000';
-- type: ref, key: idx_phone
-- ③ 前导通配符
EXPLAIN SELECT id FROM orders WHERE remark LIKE '%refund%'; -- type: ALL
EXPLAIN SELECT id FROM orders WHERE remark LIKE 'refund%'; -- type: range
-- ④ 字符集不一致的 JOIN
SELECT o.id, u.name FROM orders o
JOIN users u ON o.user_name = u.login_name
WHERE u.charset_col <> 'utf8mb4'; -- 排查是否存在跨 collation 连接
-- 通用体检：找出全表扫描占比高的语句
SELECT db, digest_text, count_star, sum_rows_examined, sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_rows_examined / NULLIF(sum_rows_sent, 0) > 100
ORDER BY sum_rows_examined DESC LIMIT 10;A repeatable design and verification workflow
Step one is always to collect the real queries instead of designing indexes from imagination. Pull the top N statements from performance_schema.events_statements_summary_by_digest ordered by execution count and total latency, and tabulate their WHERE predicates, join conditions, ORDER BY clauses, and projections. These high-frequency statements set the priority order — a new index for a query that runs twice a day is worth less than treating the one that runs two thousand times a second.
Step two is to write EXPLAIN for each hot query and record the baseline. All six columns matter: type, possible_keys, key, key_len, rows, and Extra. key_len is especially informative because it reflects how much of the indexed prefix was actually used — compare it against the expected prefix length and you immediately know how many columns failed to participate. rows is the estimated row count, and if it differs from the real result set by an order of magnitude the statistics are stale and the table needs ANALYZE TABLE.
Step three is to add the index and re-run EXPLAIN to compare. Do not skip this, because the optimizer may disagree with your expectation: it might prefer a single-column index of worse selectivity, or oscillate between two candidates. When a discrepancy appears, refresh statistics first, then consider a corrective hint such as FORCE INDEX, USE INDEX, or IGNORE INDEX, and record in a comment why the hint is needed.
Step four is long-term observation, since indexes are not correct forever. Data distributions drift, query patterns change, and a shift in write volume can push an index maintenance cost past its benefit. Once per quarter, pull the list of queries with the highest full-scan and lookup volume and review the index list for deletions as well as additions. An index collection that only ever grows eventually degrades write performance for reasons nobody can trace back.
-- 基线 EXPLAIN 关键列
EXPLAIN FORMAT=JSON
SELECT id, amount FROM orders
WHERE user_id = 1001 AND created_at >= '2026-01-01';
-- 统计信息过期时刷新
ANALYZE TABLE orders;
-- 临时纠正优化器选择（务必在代码注释里写明原因与复核日期）
SELECT id, amount FROM orders FORCE INDEX (idx_user_time_amount)
WHERE user_id = 1001 AND created_at >= '2026-01-01';
-- 查索引实际使用情况（需开启 performance_schema 的 instruments）
SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0
ORDER BY object_schema, object_name;
-- count_star = 0 的索引即为长期未被使用，是删除候选