sql如何避免全表扫描

在 SQL 中，“避免全表扫描（Full Table Scan）”的核心目标是让数据库能够使用索引来定位数据，而不是逐行扫描整张表。下面从建表、写 SQL、数据库配置三个层面系统说明。

一、索引层面（最根本）

1. 合理建立索引

• 在 WHERE、JOIN、ORDER BY、GROUP BY 常用字段上建索引

CREATE INDEX idx_user_age ON user(age);

2. 使用联合索引时注意最左前缀原则

INDEX(a, b, c)

✅ 能命中：

WHERE a=1
WHERE a=1 AND b=2
WHERE a=1 AND b=2 AND c=3

❌ 不会命中：

WHERE b=2
WHERE c=3

二、SQL 写法层面（非常重要）

3. 不要在索引列上使用函数或运算

❌ 全表扫描：

WHERE YEAR(create_time) = 2024

✅ 改写：

WHERE create_time >= '2024-01-01'
  AND create_time < '2025-01-01'

4. 避免隐式类型转换

❌

WHERE phone = 13800000000  -- phone 是 varchar

✅

WHERE phone = '13800000000'

5. 避免使用 OR（可用 UNION 替代）

❌

WHERE a=1 OR b=2

✅

SELECT * FROM t WHERE a=1
UNION
SELECT * FROM t WHERE b=2

6. 不要用 !=、<>、NOT IN、NOT LIKE

这些通常导致索引失效 ❌

WHERE status != 1

✅ 用范围或正向条件

7. LIKE 避免前模糊

❌

WHERE name LIKE '%张'

✅

WHERE name LIKE '张%'

三、查询设计层面

8. 只查需要的列（覆盖索引）

SELECT id, name FROM user WHERE age=20

如果 (age, name, id) 是联合索引，可避免回表。

9. 避免 SELECT *

• 增加 IO
• 难以使用覆盖索引

四、数据库与统计信息

10. 保证统计信息最新

-- MySQL
ANALYZE TABLE user;

-- PostgreSQL
ANALYZE user;

11. 强制使用索引（谨慎）

SELECT * FROM user FORCE INDEX(idx_age) WHERE age=20;

五、架构层面

12. 数据量过大时

• 分库分表
• 冷热数据分离
• 使用 ES / ClickHouse 等专用引擎

快速自查清单 ✅

• [ ] WHERE 条件是否用到索引
• [ ] 是否对索引列做了函数/运算
• [ ] 是否存在隐式转换
• [ ] LIKE 是否左前缀
• [ ] 是否 SELECT *

如果你有具体 SQL 或表结构，我可以直接帮你分析为什么会全表扫描，并给出改写方案。