MySQL中如何优化SELECT查询来避免全表扫描

type=ALL代表全表扫描，是必须避免的性能红灯，说明查询未走索引、每次执行都需逐行扫描整表，无论数据量大小都会浪费I/O和CPU资源。

直接说结论：全表扫描不是“慢”，而是“必须避免”的信号——只要 EXPLAIN 显示 type = ALL，就说明这条查询没走索引，哪怕只查10行，也已在浪费I/O和CPU。

为什么EXPLAIN里出现type = ALL就得马上处理

这不是性能“稍差”，而是MySQL被迫读取整张表每一行来过滤。500万行的表，rows = 5000000 意味着每次查询都在磁盘上扫一遍——延迟飙升、连接堆积、从库延迟加剧，都是连锁反应。更隐蔽的问题是：它会挤占Buffer Pool，把其他热数据顶出去，引发更多物理读。

常见诱因包括：

• WHERE 条件里对索引列用了函数，比如 WHERE YEAR(create_time) = 2023
• 联合索引只查了右列，比如索引是 (user_id, status)，但查询写了 WHERE status = 'paid'
• 字符串字段没加引号，比如 WHERE user_id = 123（而 user_id 是 VARCHAR）
• LIKE 以 % 开头，如 WHERE name LIKE '%三'

WHERE 条件怎么写才不丢索引

核心原则：索引列必须单独出现在比较操作符左侧，不能被函数、运算或隐式转换包裹。

• ❌ 错误：WHERE amount * 1.1 > 100 → 改为 WHERE amount > 90.9
• ❌ 错误：WHERE DATE(create_time) = '2023-01-01' → 改为 WHERE create_time >= '2023-01-01' AND create_time
• ❌ 错误：WHERE user_id = 1001（user_id 是 VARCHAR）→ 改为 WHERE user_id = '1001'
• ❌ 错误：WHERE status != 'cancelled' → 若业务允许，优先用 = 或 IN；否则考虑覆盖索引+强制使用

特别注意：OR 很危险。如果 WHERE a = 1 OR b = 2，且只有 a 有索引，优化器大概率放弃索引走全表扫描。改用 UNION ALL 或拆成两个查询更稳。

索引建在哪？哪些字段值得加

别凭感觉建索引。先看高频查询的 WHERE、JOIN、ORDER BY 字段，再判断选择性（唯一值占比越高越好）。

• 高价值字段：用户ID、订单号、创建时间、状态码（若状态值离散，如 'pending'/'shipped'/'done'）
• 低价值字段：性别、是否启用（只有 0/1）、地区编码（重复度极高）
• 联合索引顺序按查询频率排：比如 WHERE user_id = ? AND status = ? ORDER BY create_time DESC，那索引应为 (user_id, status, create_time)，不是反过来
• 单表索引总数控制在 3–5 个以内；每多一个索引，INSERT/UPDATE/DELETE 都要多维护一份B+树

覆盖索引是个强技巧：如果 SELECT id, user_id, status FROM orders WHERE user_id = 123，而你建了 (user_id, status, id) 联合索引，MySQL连主键回表都不用，直接从索引里取完所有字段。

分页查询卡顿，本质还是全表扫描

LIMIT 1000000, 20 看似只取20行，但MySQL必须先跳过前100万行——它得逐行计数，等于扫描了100万零20行。数据量一上千万，秒级延迟就来了。

• ✅ 替代方案：用游标分页，基于上一页最后一条的 id 或 create_time 做条件，如 WHERE id > 1234567 ORDER BY id LIMIT 20
• ✅ 大偏移量场景下，可先用子查询定位起始ID：SELECT * FROM orders WHERE id IN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20)（需确保 id 有索引）
• ⚠️ SQL_CALC_FOUND_ROWS 已废弃，别再用；总行数应由业务层缓存或异步统计

真正难的不是知道该怎么做，而是每次写完 SELECT 都习惯性跑一遍 EXPLAIN——尤其在WHERE条件加了新字段、JOIN了新表、或者上线前压测时。漏掉一次，可能就是线上接口雪崩的起点。