First See What a Slow SQL’s Execution Plan Looks Like
Before touching any index, enable the slow-query log, locate the exact offending SQL, and run EXPLAIN on that single statement. Do not guess from your mental model of the data volume; the plan will tell you whether it does a full scan, which index it used, how many rows it scanned, and whether it had to go back to the table.
Take an order query as an example: the type column is the heart of the output. ALL means a full table scan, ref or eq_ref means a normal or unique-index lookup, and index means it used a covering index but still walked the tree. rows is the estimated scanned-row count, and Extra flags like Using filesort or Using temporary are signals to optimize.
EXPLAIN only reports estimates and, below MySQL 8.0, gives no precise cost, but it is enough for relative comparison: if rows drop noticeably and type moves from ALL up to range/ref after a change, the change worked.
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; # 超过 1 秒记录
EXPLAIN SELECT order_id, user_id, amount, status
FROM orders
WHERE user_id = 10086 AND status = 'UNPAID'
ORDER BY created_at DESC;Composite Indexes Have an Order: Equal, Then Range, Then Sort
The composite index (a, b, c) works only under a leftmost-prefix match, and the column order determines whether range conditions can be used and whether sorting can be skipped. Rule of thumb: put equality-filter columns first, range columns in the middle, and the sort column last — because once a column does a range comparison, columns after it mostly stop using the index.
For a query with equality on user_id, equality on status, and ORDER BY created_at, building (user_id, status, created_at) both filters through the index and lets MySQL return rows in index order, avoiding a filesort. If you instead build (status, user_id, created_at), the equality columns get split and much of the composite index benefit is wasted.
-- 推荐：等值(user_id,status) + 排序列放最后
CREATE INDEX idx_usr_status_created ON orders(user_id, status, created_at);
-- 结果：Extra 不再出现 Using filesortCovering Indexes Give You a Free Round-Trip
When every column in SELECT lives inside the index, MySQL scans only the index and skips the table round-trip — that is a covering index. It is a clear win on large-table reads and suits high-frequency detail/list pages that touch small columns.
That means selecting the columns you need instead of SELECT *. For a list page needing only id, title and updated_at, build a covering index on (category_id, updated_at, title). When EXPLAIN’s Extra shows Using index, you hit a covering index, and even a less-ideal type is usually acceptable. The cost is storage per column and slower writes with more indexes, so covering indexes pay off most on read-heavy list scenarios.
EXPLAIN SELECT id, title, updated_at FROM articles WHERE category_id = 5;
-- Extra: Using index <- 命中覆盖索引，未回表A Handful of Common Index-Failure Traps
Just building an index does not mean it will be used. The three most common failures: applying a function or expression to an indexed column, such as WHERE DATE(created_at) = '2026-09-06', which kills the index; an implicit type cast on an indexed column, like comparing a string column to a number; and leading-wildcard LIKE, since WHERE name LIKE '%keyword%' cannot use an index.
All are fixable by rewriting the SQL: move the function to the right side or use a range form such as created_at BETWEEN '...' AND '...'; make the parameter type match the column type; and switch to a full-text index or a third-party search engine for fuzzy search. Verify simply by re-running EXPLAIN and confirming type still reads ref/range.
-- 陷阱写法（DATE 函数使 created_at 索引失效）
SELECT * FROM orders WHERE DATE(created_at) = '2026-09-06';
-- 修复写法（范围扫描可走索引）
SELECT * FROM orders WHERE created_at >= '2026-09-06 00:00:00' AND created_at < '2026-09-07';Confirm the Fix with Your Live Slow-Log
The standard for closing an optimization is not "the EXPLAIN looks nice" but that live slow queries actually drop. With the slow-query log on, watch over time to see whether the statement still tops the list, and compare average elapsed time and scanned rows before and after.
Record a baseline (elapsed time, rows, type) before touching anything, then re-run the same statement with identical parameters after the change. If Using filesort/Using temporary disappears from Extra and type moves up to range/ref, it is effectively confirmed. If it is still slow, use the optimizer trace to check whether the optimizer skipped your intended index, and force the index with a hint to validate the hypothesis.
SHOW VARIABLES LIKE 'long_query_time';
# 查看某条 SQL 的优化器选择
SET optimizer_trace = 'enabled=on';
EXPLAIN SELECT ... ;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;