MySQL Slow Query Tuning: Slow Log, EXPLAIN and Indexing

"The database is slow" usually means a handful of slow SQL statements. This guide offers a repeatable flow: use the slow log to circle the most expensive queries, read the plan with EXPLAIN, decide whether an index pays off, and spot the most common index-defeating patterns.

By 巧匠 Team·9 min read·Updated 2026-08-30

Step 1: enable the slow log, circle the costly queries

First confirm the slow log is on and check the threshold. In production set `long_query_time` to 0.1–0.5s — the 10s default hides queries that are "getting slow". `log_queries_not_using_indexes` additionally logs full scans.

With limited time, rank by total time, not by count: a 3s query running 100k times a day is worth far more than a 30s report run three times a day.

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_queries_not_using_indexes = ON;
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log   # 按总耗时取 Top10

Step 2: the four key EXPLAIN signals

Run `EXPLAIN` on the target SQL and focus on four columns: type (access type — ALL is a full scan; ref/range means the index is used); key (the index actually chosen — NULL means none); rows (estimated rows scanned, whose magnitude drives latency); Extra (Using filesort / Using temporary signal sort and temp-table cost, while Using index is good — a covering index avoids table lookups).

EXPLAIN SELECT ... ;
-- type=ALL + key=NULL  → 全表扫描，优化对象

Step 3: should you add an index

Add an index when the column appears in high-frequency WHERE / JOIN / ORDER BY clauses and has enough selectivity (a two-value column like gender gains little alone). Be restrained on write-heavy tables — every index slows writes.

For composite indexes remember the leftmost prefix: index (a, b) serves WHERE a=? and WHERE a=? AND b=?, but not WHERE b=?. Put equality columns before range columns — a general ordering rule. If the SELECT list is fully covered by the index you also get Using index, skipping the table lookup.

ALTER TABLE orders ADD INDEX idx_user_time (user_id, created_at);
-- 等值列在前、范围列在后

Six patterns that defeat an index

① Functions or arithmetic on the indexed column: `WHERE YEAR(created_at)=2026`; ② implicit type conversion: querying a string column with a number (`WHERE phone=13800000000`); ③ leading wildcard: `LIKE '%keyword'` (trailing `keyword%` is fine); ④ OR where one side lacks an index; ⑤ skipping the leftmost prefix; ⑥ skewed data making the optimizer abandon the index (stale stats — refresh with `ANALYZE TABLE`).

-- 失效：函数包裹索引列
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- 改写：范围条件，可走索引
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

Two things to do after optimizing

① Re-run EXPLAIN and compare plans (type going ALL → range means it worked); ② re-measure real latency, ideally at production-scale data — an index that wins on a 10k-row test database can lose at 100M rows (amplified lookups).

Record before/after metrics in the ticket or wiki so "why this index exists" stays answerable half a year later — preventing the next person from deleting it in confusion.

EXPLAIN SELECT ...;            -- 优化后复核
ANALYZE TABLE orders;          -- 刷新统计信息

When an index will not save you

Deep pagination (LIMIT 1000000, 20), big-table joins without indexes, SELECT * pulling every column, oversized single transactions — these are architecture problems: cursor pagination (remember the last id), denormalized fields instead of joins, selecting only needed columns, splitting transactions. Knowing when to stop tuning SQL and start changing the design is the final lesson of slow-query work.

-- 深分页优化：游标式
SELECT * FROM orders WHERE id > <last_id> ORDER BY id LIMIT 20;