MySQL 慢查询优化：从 5 秒到 50 毫秒的实战记录
MySQL Slow Query Optimization — From 5 Seconds to 50ms in Practice
| iDev Team | 2026-08-13T09:46:26
一条 SQL 跑了 5 秒，加了索引还是慢？可能不是索引的问题。本文分享一次完整的慢查询排查和优化过程。
A SQL query taking 5 seconds — adding an index didn't help? Maybe it's not an index problem. A complete slow query diagnosis and optimization walkthrough.
问题现场客户的订单列表页加载要 5 秒。后端日志显示一条 SQL 耗时 4.8 秒。表数据量 50 万行，不算大，但就是慢。第一步：EXPLAIN 分析EXPLAIN 显示 type=ALL（全表扫描），rows=500000。WHERE 条件里有 status = 1 AND DATE(created_at) >= '2026-01-01'。看起来应该走索引，但 DATE() 函数包裹了索引列，导致索引失效。第二步：去掉函数包裹把 DATE(created_at) >= '2026-01-01' 改成 created_at >= '2026-01-01 00:00:00'。耗时从 4.8 秒降到 1.2 秒。好了一些，但还不够。第三步：组合索引原来只有 created_at 单列索引。加了 (status, created_at) 组合索引后，MySQL 可以同时用两个条件过滤。耗时降到 200ms。第四步：SELECT 字段瘦身原来 SELECT * 包含了一个 TEXT 类型的 remark 字段（平均 2KB），50 万行数据光读这个字段就要传输 1GB。改成只 SELECT 需要的字段后，耗时降到 50ms。总结WHERE 条件不要用函数包裹索引列高频查询组合加组合索引不要 SELECT *，尤其有大字段的表EXPLAIN 是第一个要用的工具，不是最后一个
The SceneA client's order list page took 5 seconds to load. Backend logs showed a single SQL query taking 4.8 seconds. Table had 500K rows — not huge, but painfully slow.Step 1: EXPLAIN AnalysisEXPLAIN showed type=ALL (full table scan), rows=500000. WHERE clause: status = 1 AND DATE(created_at) >= '2026-01-01'. Looks like it should use an index, but the DATE() function wrapping the indexed column prevents index usage.Step 2: Remove Function WrappingChanged DATE(created_at) >= '2026-01-01' to created_at >= '2026-01-01 00:00:00'. Query time dropped from 4.8s to 1.2s. Better, but not enough.Step 3: Composite IndexOriginally only a single-column index on created_at. Added a composite index (status, created_at) so MySQL can filter on both conditions. Time dropped to 200ms.Step 4: SELECT Field SlimmingThe original SELECT * included a TEXT-type remark field (average 2KB per row). For 500K rows, just reading this field transfers ~1GB. Selecting only needed fields brought it down to 50ms.TakeawaysDon't wrap indexed columns in functions in WHERE clausesAdd composite indexes for frequent query combinationsNever SELECT * — especially with tables containing large fieldsEXPLAIN is the first tool to use, not the last