SQL Query Optimization in Practice: Locating Slow Queries Through Execution Plans

Start by finding the slow queries, then read the plan

Before optimising anything, settle how you know a query is slow. The slow query log is the baseline entry point: with `slow_query_log` enabled, MySQL records statements exceeding `long_query_time` (10 seconds by default, often lowered to 0.1-1s in practice) along with the statement text, elapsed time, rows examined, and whether an index was used.

The parameter that matters for diagnosis is `log_queries_not_using_indexes`, which also logs statements that used no index. That is invaluable while hunting missing indexes, but left on in production it floods the disk, so enable it only for a diagnostic window or alongside a low threshold.

From MySQL 8.0, performance_schema and the sys schema provide aggregated views. `sys.statement_analysis` sorted by total latency gives you digest_text, query_count, total_latency, and rows_examined_avg — ideal for ranking what to fix first. Once you have the individual statement, run `EXPLAIN` on it.

Distinguish "slow" from "a lot of scanning". A one-second query touching 20 rows and a 50-millisecond query scanning 500,000 rows are entirely different problems. The first is often lock waits or external dependencies; only the second lives in the index and execution-plan layer. Comparing `rows` against the actual returned row count before blaming anything saves a lot of wasted effort.

-- 临时开启并收紧阈值（会话级，排查窗口内使用）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_queries_not_using_indexes = ON;

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- MySQL 8.0：按总耗时排序找 Top 慢查询
SELECT query,
       exec_count,
       avg_timer_wait / 1000000000 AS avg_ms,
       avg_rows_examined      AS avg_rows,
       digest_text
FROM sys.statements_with_full_table_scans
ORDER BY avg_timer_wait DESC
LIMIT 20;

EXPLAIN field by field: how to read the plan

Each `EXPLAIN` row is one step the optimizer chose (a table-level access), a multi-table query returns several rows, and the order of the rows is the execution order. The fields to read first are `type`, `key`, `rows`, and `Extra`; the rest are supporting evidence.

`id` is the query block number — 1 for a single-table query, and for JOINs larger numbers execute first, which lets you read the nesting structure. `select_type` distinguishes SIMPLE, PRIMARY, SUBQUERY, DERIVED, and so on. `table` is the table or alias being accessed, with derived tables and subqueries shown as temporary results like `<derived2>`.

`type` is the field that matters most: how the table is accessed. Roughly best to worst: const (primary key or unique equality, a single hit), eq_ref (a unique index lookup per outer row, the ideal for JOINs), ref (non-unique index equality, possibly many rows), range (index range scan), index (full index scan — note this is not a full table scan), and ALL (full table scan). Seeing ALL makes a missing or unusable index the prime suspect.

`possible_keys` lists what the optimizer deemed available, `key` is what it actually picked, and `key_len` is the number of bytes of the index consumed. `key_len` tells you how many columns of a composite index were used: under utf8mb4 an INT column costs about 4 bytes, so if the key_len for index (a, b, c) is exactly the width of a, only the leftmost column was used — the most direct evidence of a broken composite index.

`rows` is the estimated number of rows to read and `filtered` the percentage left after WHERE filtering, so `rows × filtered / 100` approximates the result size. Both are estimates derived from statistics; stale statistics produce a wildly wrong plan, so when `rows` is far from reality run `ANALYZE TABLE` first.

`Extra` is the most under-read and most informative column. Danger signs: `Using filesort` means an extra sort pass, usually because no ordered index could be used; `Using temporary` means a temp table, expensive when the intermediate set is large; `Using where` alongside `type: ALL` marks a full scan; `Using join buffer (hash join)` means no usable index and a hash join, which is usually bad news. Conversely `Using index` (covering index, no table lookup) and `Using index condition` (index condition pushdown) are good signs.

On MySQL 8.0+, prefer `EXPLAIN FORMAT=TREE` or `EXPLAIN ANALYZE`, which report reality rather than estimates: FORMAT=TREE shows the operator tree, and EXPLAIN ANALYZE actually executes and reports real row counts and timings per step, letting you compare estimate against reality — a large gap points at statistics problems. Note that EXPLAIN ANALYZE genuinely runs the statement, so treat it with great care on UPDATE and DELETE.

EXPLAIN FORMAT=TREE
SELECT o.id, o.amount, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-09-01'
  AND o.status = 'paid'
ORDER BY o.amount DESC
LIMIT 100;

-- 真实执行：对比估算行数与实际行数
EXPLAIN ANALYZE
SELECT o.id, o.amount
FROM orders o
WHERE o.created_at >= '2026-09-01' AND o.status = 'paid'
ORDER BY o.amount DESC
LIMIT 100;

-- 统计信息过期时先刷新再看计划
ANALYZE TABLE orders;
SHOW INDEX FROM orders;

Common full-scan causes: reasoning backwards from type: ALL

First, confirm whether `type: ALL` is necessarily bad. If the query genuinely returns most of the table, index-driven random IO can be slower than a sequential scan, and the optimizer picking ALL may be correct. Judge by the ratio of `rows` to actual returned rows: near 1, ALL is reasonable; hundreds or thousands, the index is missing.

Genuinely pathological full scans have typical shapes. First, the WHERE column has no index. Second, an index exists but is far too low-cardinality — a three-valued `status`, or a boolean `is_deleted` — and the optimizer decides that index lookup plus row fetches costs more than a scan, so it abandons the index.

Third, a range predicate neutralises the tail of a composite index. On index (a, b), `WHERE a > 10 AND b = 5` can only use a, and the equality on b does nothing. Adding a standalone index on b rarely helps either; the real fix is a covering index or a different query shape. Fourth, stale statistics — `ANALYZE TABLE` alone can restore a sane plan.

Fifth and subtler: sort or pagination direction prevents using index order. `ORDER BY created_at DESC LIMIT 20` can usually walk the index backwards, but once the direction conflicts with the index or the sort column is not the leftmost one, index order is useless and `Using filesort` appears in the plan.

-- 判断 ALL 是否合理：看扫描行数与实际返回行数的比例
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE status = 'paid';

-- 刷新统计信息后重看计划
ANALYZE TABLE orders;

-- 低选择性列不适合单独立索引，观察分布先看清代价
SELECT status, COUNT(*) AS cnt, COUNT(*) / (SELECT COUNT(*) FROM orders) AS ratio
FROM orders
GROUP BY status;

-- 覆盖索引：让查询不回表，常能直接消除 filesort / temporary
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

Six scenarios where an index silently stops working

First, implicit type conversion. The indexed column is VARCHAR while the predicate supplies a number (or vice versa), so comparison converts types and the converted values no longer match the index ordering — the index is simply unused. This is especially sneaky in MySQL because passing a number against a string column raises no error; it just quietly scans everything. Compare the `key` in EXPLAIN against the index you expected.

Second, wrapping the indexed column in a function or expression. In `WHERE DATE(created_at) = '2026-10-02'`, `DATE()` is applied to the column so the index is unusable; rewriting as the range `created_at >= '2026-10-02' AND created_at < '2026-10-03'` fixes it. MySQL 8.0 functional indexes rescue some of these cases, but they have syntax limits on expressions and are not a universal replacement.

Third, a leading wildcard in LIKE. `LIKE '%abc'` and `LIKE '_abc'` cannot determine a prefix and must scan, while `LIKE 'abc%'` can use an index. When you need fuzzy search plus performance, the correct answer is a search engine or a separate inverted index, not a stubborn B-tree.

Fourth, breaking the leftmost-prefix rule on a composite index. Index (a, b, c) serves a, (a, b), and (a, b, c), but never b alone. Columns after a range predicate cannot be used for equality positioning either: on (a, b), `a > 1 AND b = 2` can only use a. Design composite indexes with the most frequent equality columns leftmost and range columns further right.

Fifth, OR mixing unrelated indexes. With separate indexes on a and b, `WHERE a = 1 OR b = 2` historically made MySQL pick just one; MySQL 8.0 added index_merge (`Using union`) to combine multiple index scans, but the optimizer does not always choose it and its cost estimates are fragile. The dependable fix is splitting into `UNION ALL` and merging results in the application.

Sixth, sort direction and type mismatch. `ORDER BY` listing columns in the reverse of the index order wastes the index; mixing string and numeric sorts triggers implicit conversion; and joining CHAR to VARCHAR with different character sets or collations can also defeat index use. Confirm with `SHOW INDEX` that the index `Collation` is `A`.

-- 1) 隐式类型转换：key 会变成 NULL
SELECT * FROM users WHERE phone = 13800138000;   -- phone 是 VARCHAR

-- 2) 函数包裹：改成范围条件
SELECT * FROM orders WHERE DATE(created_at) = '2026-10-02';   -- filesort / ALL
SELECT * FROM orders
WHERE created_at >= '2026-10-02' AND created_at < '2026-10-03';  -- 用得上索引

-- 3) 联合索引最左前缀
CREATE INDEX idx_ab ON t (a, b, c);
SELECT * FROM t WHERE a = 1 AND c = 3;   -- 只用到 a

-- 4) OR 混用 -> UNION ALL 拆分
SELECT id, name FROM t WHERE a = 1
UNION ALL
SELECT id, name FROM t WHERE b = 2;

What SELECT * actually costs, and how to rewrite it

The cost of SELECT * is consistently underestimated, and it has at least four layers. First, network and serialisation: more columns mean more bytes on the wire and a more expensive object graph in the driver layer, even when the application reads only one of them. Deserialisation cost then propagates up into JSON encoding and front-end rendering.

Second, the execution plan: the moment a covering index becomes impossible — typically because a WHERE predicate cannot use an index, or the sort column is not indexed — the optimizer must fetch full rows from the table. Worse, a query written as SELECT * might have been covered by an index, and switching to an explicit column list can forfeit that advantage. Column lists must therefore be designed alongside indexes, not bolted on afterwards.

Third, schema evolution cost. SELECT * couples the result shape to the table: someone adds a column and your app silently receives an extra field; someone drops or retypes one and your app may break outright. An explicit list turns an implicit contract into an explicit one, which is what makes large refactors safe.

Fourth, buffers and temp tables. The bigger the intermediate result — derived tables, UNIONs, GROUP BY inputs — the more IO spent on temp tables and sorts, and `Using temporary` / `Using filesort` costs grow with column count. Over-wide lists also inflate deep-pagination cost (`LIMIT 100000, 20`) because all preceding rows are scanned and discarded.

Practical rewriting principles: list exactly the columns the application uses; never fetch large fields (BLOB, JSON, long text) in list endpoints and load them on demand in a detail query; where a full-column export or report genuinely needs everything, at least shape the filter and sort so the query can use a covering index; and for deep pagination, switch to keyset (cursor) paging based on the previous result's primary key to avoid large LIMIT offsets.

-- 改写前：全列 + 深分页，慢
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 100000, 20;

-- 改写后：显式列 + 覆盖索引
CREATE INDEX idx_user_created ON orders (user_id, created_at, id);

SELECT id, status, amount, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

-- 深分页改游标翻页（seek method），OFFSET 为 0
SELECT id, status, amount, created_at
FROM orders
WHERE user_id = 42
  AND (created_at, id) < ('2026-09-01 10:00:00', 88123)
ORDER BY created_at DESC, id DESC
LIMIT 20;