MySQL数据库优化SELECT查询避免全表扫描的几种方法

在MySQL性能优化中，EXPLAIN输出的type列是查询效率的“晴雨表”。如果发现type=ALL，意味着查询正在执行全表扫描——这是MySQL查询中最糟糕的情况，每次执行都会逐行扫描整张表，无论数据量大小，都会浪费I/O和CPU资源。简单来说，全表扫描不仅是性能瓶颈，更是一个必须立即优化的警报。只要EXPLAIN显示type = ALL，就说明这条查询没有使用索引，即便只查10行数据，也已经造成了资源浪费。

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

不要轻视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 < '2023-01-02'
• ❌ 错误写法：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了新表，或者上线前压测时。漏掉一次，可能就是线上接口雪崩的起点。