PostgreSQL全表扫描慢到崩溃？建索引+改查询+更统计信息三招能破？

一、为什么要避免全表扫描？

全表扫描（Sequential Scan）是PostgreSQL最“直白”的查询方式——它会像翻书一样逐行读取表中所有数据，不管你要的内容在开头还是结尾。比如你有一本1000页的字典，要找“PostgreSQL”这个词，如果没有目录（索引），你得从第一页翻到最后一页，这就是全表扫描。
当表的数据量很小（比如几百行），全表扫描的代价可以忽略；但如果表有100万行甚至1亿行，全表扫描会吃掉大量磁盘I/O和内存资源，导致查询卡半天，还会影响其他业务的正常运行。

二、PostgreSQL是怎么选择执行计划的？

要避免全表扫描，得先理解PostgreSQL的“大脑”——查询规划器（Query Planner）。它的工作流程像“导航软件”：

1. 接收查询：比如你写了SELECT * FROM users WHERE age > 30;。
2. 生成候选计划：规划器会想出几种执行方式——比如全表扫描、用age列的索引扫描、甚至哈希扫描。
3. 计算代价：规划器根据统计信息（比如表的大小、age列有多少不同值）给每个计划打分，代价最低的胜出。
4. 执行计划：PostgreSQL按照最优计划执行查询。

你可以用EXPLAIN ANALYZE命令“看”规划器的选择，比如：

-- 查看执行计划（带实际执行统计）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

如果输出里有Seq Scan on users，说明用了全表扫描；如果有Index Scan using idx_users_age on users，说明用了索引扫描。

三、避免全表扫描的核心：给“关键列”建索引

索引是PostgreSQL的“目录”，能快速定位到你要的数据。最常用的索引类型是B-tree（适用于等值查询、范围查询），就像字典的拼音目录——按顺序排列，找起来很快。

1. 哪些列需要建索引？

• WHERE子句里的列：比如age（WHERE age > 30）、email（WHERE email = '[email protected]'）。
• JOIN条件里的列：比如订单表orders的user_id（JOIN users ON orders.user_id = users.id）。
• 排序/分组的列：比如ORDER BY create_time、GROUP BY category_id。

2. 建索引的正确姿势

以users表的age列为例，创建B-tree索引：

-- 为users表的age列创建B-tree索引
CREATE INDEX idx_users_age ON users(age);

• idx_users_age：索引名称（建议用“idx_表名_列名”的格式，容易识别）。
• ON users(age)：指定要建索引的表和列。

3. 哪些情况不适合建索引？

• 低基数列：比如gender（只有“男”“女”两个值），建索引反而会增加维护成本（插入/更新时要同步更新索引）。
• 经常更新的列：比如last_login_time（每次登录都要更新），频繁更新会导致索引频繁重构，影响性能。
• 小表：比如只有100行的config表，全表扫描比索引扫描更快（索引本身也需要读取磁盘）。

四、查询语句改写：让规划器“愿意”用索引

有时候不是没有索引，而是你的SQL写法让规划器“放弃”了索引。以下是常见的“坑”和改写技巧：

1. 避免前缀通配符

坏例子（会全表扫描）：

-- 查找邮箱以@example.com结尾的用户（前缀通配符%导致索引失效）
SELECT * FROM users WHERE email LIKE '%@example.com';

好例子（会用索引）：

-- 查找邮箱以user开头的用户（后缀通配符%不影响索引）
SELECT * FROM users WHERE email LIKE 'user%@example.com';

原理：B-tree索引是按字符串顺序存储的，比如“[email protected]”“[email protected]”会排在一起。前缀通配符（%在开头）会破坏顺序，规划器无法快速定位；后缀通配符（%在结尾）不影响顺序，可以用索引。

2. 避免对列用函数

坏例子（会全表扫描）：

-- 查找邮箱前4个字符是user的用户（SUBSTRING函数导致索引失效）
SELECT * FROM users WHERE SUBSTRING(email, 1, 4) = 'user';

好例子（会用索引）：

-- 等价于上面的查询，但能用email列的索引
SELECT * FROM users WHERE email LIKE 'user%';

原理：规划器无法“提前计算”函数的结果——它不能把SUBSTRING(email,1,4)和索引里的email值直接对比，所以只能全表扫描。

3. 避免隐式类型转换

坏例子（会全表扫描）：

-- id是整数类型，但查询用了字符串（隐式类型转换导致索引失效）
SELECT * FROM users WHERE id = '123';

好例子（会用索引）：

-- 使用正确的整数类型
SELECT * FROM users WHERE id = 123;

原理：PostgreSQL会把id列的每一行都转换成字符串再和'123'对比，这个过程无法用索引。

4. 用显式JOIN代替隐式JOIN

坏例子（隐式JOIN，规划器可能选差的执行计划）：

-- 隐式JOIN（用逗号连接表，WHERE写连接条件）
SELECT u.name, o.order_id 
FROM users u, orders o 
WHERE u.id = o.user_id;

好例子（显式INNER JOIN，规划器更易优化）：

-- 显式INNER JOIN（用JOIN关键字，ON写连接条件）
SELECT u.name, o.order_id 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id;

原理：显式JOIN让规划器更清楚表之间的关系，能更智能地选择连接顺序（比如先查小表再关联大表）和连接方式（比如Nested Loop Join适合小结果集，Hash Join适合大结果集）。

五、统计信息：规划器的“眼睛”

PostgreSQL的规划器不是“猜”执行计划的，它靠统计信息（比如表的行数、列的不同值数量、数据分布）来计算代价。如果统计信息过时，规划器会做出错误的选择——比如明明age>30的行只有10%，却因为统计信息没更新，继续用全表扫描。

1. 手动更新统计信息

用ANALYZE命令更新表的统计信息：

-- 更新users表的统计信息
ANALYZE users;

2. 自动更新统计信息

PostgreSQL有个autovacuum进程（默认开启），会定期自动运行ANALYZE，保持统计信息新鲜。你可以通过以下参数调整：

• autovacuum_analyze_threshold：触发自动ANALYZE的行数阈值（默认50行）。
• autovacuum_analyze_scale_factor：触发自动ANALYZE的比例阈值（默认0.1，即10%）。

六、课后Quiz

1. 问题1（基础题）

以下查询为什么会全表扫描？如何优化？

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

答案解析：
如果order_date列没有索引，PostgreSQL会全表扫描所有订单，过滤出2023年的订单。优化方法是为order_date列创建索引：

CREATE INDEX idx_orders_order_date ON orders(order_date);

2. 问题2（原理题）

为什么WHERE LOWER(email) = '[email protected]'不会使用email列的索引？如何优化？ 答案解析：
因为对email列用了LOWER函数，规划器无法将函数结果与索引中的email值直接匹配，所以不会用索引。优化方法是：

• 方法1：将email列存储为小写（插入时转小写），查询时不用函数：WHERE email = '[email protected]'。
• 方法2：创建函数索引（针对函数结果建索引）：

  CREATE INDEX idx_users_email_lower ON users(LOWER(email));
  

七、常见报错解决方案

1. 报错：ERROR: syntax error at or near "INDEX"

产生原因：创建索引的语法错误（比如漏写ON关键字、拼写错误）。
错误例子：

-- 漏写ON关键字，导致语法错误
CREATE INDEX idx_users_age users(age);

解决办法：检查语法，正确写法是CREATE INDEX 索引名 ON 表名(列名);。

2. 报错：ERROR: duplicate key value violates unique constraint "idx_users_email"

产生原因：尝试插入重复值到有唯一索引的列（比如email列建了唯一索引，却插入了相同的邮箱）。
解决办法：

• 检查插入的数据，确保email列的值唯一。
• 如果是误操作，删除重复值后重新插入。

3. 报错：ERROR: index "idx_users_age" does not exist

产生原因：试图删除或使用不存在的索引（比如索引名称拼写错误）。
解决办法：

• 用d 表名查看表的索引（比如d users查看users表的索引）。
• 确认索引名称正确后再操作。

参考链接

1. www.postgresql.org/docs/17/sql…
2. www.postgresql.org/docs/17/per…
3. www.postgresql.org/docs/17/sql…