mysql作为常用的关系型数据库，查询性能直接影响业务系统的响应速度，全表扫描是拖慢查询效率的常见原因，指的是数据库引擎需要遍历表中所有记录来匹配查询条件，当表数据量达到百万甚至千万级别时，这类查询的耗时可能从毫秒级上升到秒级甚至分钟级。
全表扫描的常见触发场景
要明确如何避免全表扫描，首先需要知道哪些情况会导致数据库放弃使用索引，选择全表扫描：
- 查询条件中使用了
OR连接多个条件，且其中部分条件没有对应索引 - 对索引字段进行函数运算或者表达式计算，比如
WHERE YEAR(create_time) = 2023 - 查询条件使用
LIKE模糊匹配且通配符%放在开头，例如WHERE name LIKE '%张三' - 索引字段参与类型转换，比如字符串类型的字段用数字去匹配，
WHERE phone = 13800138000而phone是varchar类型 - 查询返回的数据量占全表比例过高，优化器判断全表扫描比走索引成本更低
避免全表扫描的核心优化技巧
1. 合理设计和使用索引
索引是避免全表扫描最有效的手段，创建索引时需要遵循以下原则：
- 为频繁作为查询条件、连接条件、排序条件的字段创建索引，比如用户表的user_id、订单表的create_time
- 避免创建过多冗余索引，每个索引都会占用额外的存储空间，还会降低写入数据的效率
- 联合索引要遵循最左前缀原则，比如创建
(a,b,c)的联合索引，查询条件包含a、a和b、a和b和c时才能命中索引，单独用b或者c则无法命中
以下是创建联合索引的示例：
-- 为订单表创建联合索引，优先匹配user_id，其次匹配create_time CREATE INDEX idx_user_create ON order_table (user_id, create_time);
2. 优化查询条件的写法
很多全表扫描问题可以通过调整查询语句的写法解决：
- 避免使用
SELECT *，只查询需要的字段，减少回表次数，也能避免返回过多无用数据 - 模糊查询尽量把通配符%放在末尾，比如
WHERE name LIKE '张%'可以命中name字段的索引 - 如果必须使用
OR条件，确保每个条件都有对应的索引，或者将OR查询拆分成多个UNION查询 - 不要在索引字段上做函数运算，比如要查询2023年的数据，可以改成范围查询：
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'
优化前后的查询语句对比如下：
-- 优化前，对索引字段做函数运算，会触发全表扫描 SELECT * FROM order_table WHERE YEAR(create_time) = 2023; -- 优化后，使用范围查询，可命中create_time索引 SELECT order_id, user_id, amount FROM order_table WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
3. 调整表结构和查询逻辑
部分场景下可以通过调整表结构从根源上避免全表扫描：
- 对大表进行分库分表，减少单表的数据量，即使出现全表扫描，影响范围也会缩小
- 如果查询需要返回大量数据，可以考虑增加过滤条件，缩小查询范围，避免返回全表大部分数据
- 对于状态类字段，如果取值很少，可以结合其他字段创建联合索引，提升查询效率
4. 利用执行计划分析查询
可以通过EXPLAIN命令查看查询语句的执行计划，判断是否存在全表扫描：
-- 查看查询语句的执行计划 EXPLAIN SELECT order_id FROM order_table WHERE user_id = 1001;
执行结果中，type字段是关键指标，如果出现ALL就代表是全表扫描，需要针对性优化；如果是ref、range、index等类型，则说明使用了索引。
注意事项
不是所有场景都要完全避免全表扫描，当查询需要返回表中绝大部分数据时，全表扫描的成本可能比走索引更低，此时优化器选择全表扫描是更合理的。优化时需要结合实际业务场景和数据量，不要盲目添加索引或者修改查询逻辑，避免带来其他性能问题。