PostgreSQL数据库千万级数据查询优化教程

admin 数据库教程 2025-10-24 4

处理千万级数据的朋友，是不是一到查询就头大？明明表结构看着没问题，可查个数据要等十几秒，页面加载半天没反应；更头疼的是，数据越存越多，之前好用的查询语句突然就变慢了，排查半天找不到原因？别着急，今天兔子哥就带大家搞定 PostgreSQL 千万级数据的查询优化，从索引到语句再到配置，全是实战技巧，哪怕你是刚接触大数据量的新手，跟着学也能让查询速度提几倍，一起往下看吧！

一、先说说：千万级数据查询慢，问题到底出在哪？

可能有朋友会说，数据少的时候查得快，数据多了自然慢，这不是正常的吗？其实不全是。千万级数据查询慢，多半是这几个地方没做好：

没建对索引，或者索引建多了、建错了，数据库只能全表扫描，就像在十万页的书里找一页没目录的内容；
查询语句写得太 “臃肿”，用了太多不必要的字段、复杂的子查询，数据库计算起来费劲；
数据库配置没调，默认参数应付小数据量还行，大数据量就 “跑不动” 了；
表结构设计不合理，比如字段类型选错、没分区，数据堆在一起难查询。

有个做数据分析的朋友吐槽：“之前订单表到 800 万条数据时，查个用户订单要 15 秒，后来调了索引，同样的查询 2 秒就出来了。” 这说明只要找对问题，大数据量查询也能变快。

二、索引优化：给数据加 “目录”，查询提速的关键

索引就像书的目录，是千万级数据优化的 “第一招”，但索引不是随便建的，这几点要记牢：

1. 哪些字段必须建索引？这 3 类字段别漏掉

建对索引能让查询快 10 倍，这几类字段一定要建：

经常出现在WHERE条件里的字段，比如订单表的 “用户 ID”“订单日期”，每次查询都用它们过滤数据；
用来排序（ORDER BY）或分组（GROUP BY）的字段，比如按 “创建时间” 排序查最新数据，建了索引排序不用临时计算；
关联查询（JOIN）的字段，比如两个表用 “商品 ID” 关联，这个字段建索引，关联时不用全表比对。

比如给订单表的user_id和create_time建索引：
sql

CREATE INDEX idx_orders_userid ON orders(user_id);CREATE INDEX idx_orders_createtime ON orders(create_time);

建完后查 “用户近 30 天的订单”，数据库直接用索引定位，不用扫全表。

2. 这些索引坑千万别踩！建错不如不建

虽然索引好，但建错了反而添乱，新手常犯这几个错：

给所有字段都建索引。索引会占存储空间，而且新增、修改数据时要更新索引，字段越多更新越慢，千万级数据下更明显；
给低基数字段建索引。比如 “性别” 这种只有 “男 / 女” 两个值的字段，建索引效果差，数据库可能还是会全表扫描；
索引没维护。数据删改多了，索引会产生碎片，就像目录页被撕得乱七八糟，查询时反而变慢，定期用REINDEX重建很重要。

不过话说回来，索引也不是万能的，比如查询要返回表中 90% 以上的数据，全表扫描可能比用索引还快，这时候索引反而成了负担。

三、查询语句优化：写对 SQL，少让数据库 “干活”

索引再好，语句写得烂也白搭。千万级数据查询，语句要 “精简”，这几招能帮你：

1. 只查需要的字段，别用`SELECT *`

很多人习惯用SELECT *查所有字段，其实没必要，千万级数据下查多余字段会增加数据传输和处理时间。比如只需要订单 ID 和金额，就明确写字段：
sql

-- 别这样写，查了不需要的字段SELECT * FROM orders WHERE user_id = 10086;-- 这样写，只查需要的字段SELECT order_id, total_amount FROM orders WHERE user_id = 10086;

字段越少，数据库处理和返回数据越快，尤其是大表，差距很明显。

2. 少用子查询，换成`JOIN`更高效

复杂的子查询在千万级数据下会很慢，能用JOIN就别用子查询。比如查 “买过商品 A 的用户信息”：
sql

-- 子查询可能慢SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE product_id = 'A');-- 换成JOIN更快SELECT u.* FROM users uJOIN orders o ON u.id = o.user_idWHERE o.product_id = 'A'GROUP BY u.id; -- 去重

JOIN比子查询更易被数据库优化，执行效率更高。

3. 避免`OR`和`NOT IN`，换成`UNION`或`LEFT JOIN`

OR和NOT IN在大数据量下容易导致全表扫描，比如查 “用户 ID 是 1001 或 2002 的订单”：
sql

-- 别用ORSELECT * FROM orders WHERE user_id = 1001 OR user_id = 2002;-- 换成UNIONSELECT * FROM orders WHERE user_id = 1001UNIONSELECT * FROM orders WHERE user_id = 2002;

UNION能分别用到两个索引，比OR效率高得多。

四、数据分区：把大表 “拆小”，查询只扫一部分

千万级数据全存在一个表里，就像把所有东西堆在一个大仓库，找东西自然慢。分区就是把大表拆成小表，查询时只扫相关分区，这招对时间或范围明确的表超有用：

1. 按时间分区：订单表常用

比如订单表按月份分区，每个月的数据存在一个分区里：
sql

-- 创建分区表CREATE TABLE orders (order_id bigint,user_id int,create_time timestamp) PARTITION BY RANGE (create_time);-- 建2024年1月分区CREATE TABLE orders_202401 PARTITION OF ordersFOR VALUES FROM ('2024-01-01') TO ('2024-02-01');-- 再建2月、3月...分区

查 2024 年 1 月的订单时，数据库只扫orders_202401分区，不用扫全表，速度自然快。

2. 按范围分区：适合 ID 或数值字段

比如用户表按user_id范围分区，1-100 万一个分区，100 万 - 200 万一个分区，查询特定范围的用户时效率更高。

五、数据库配置调整：让 PostgreSQL “跑更快”

默认配置是给小数据量用的，千万级数据得调参数，这几个关键参数改一改，效果立竿见影：

shared_buffers：数据库缓存大小，建议设成服务器内存的 1/4，内存越大缓存越多，查过的数据不用再读硬盘；
work_mem：排序和哈希操作的内存，大数据量排序可以调大，比如从默认的 4MB 调到 32MB；
maintenance_work_mem：建索引、 vacuum 时用的内存，调大能加快索引创建速度；
effective_cache_size：数据库估计的可用缓存，设成内存的 1/2，帮助优化器选更好的执行计划。

修改配置后要重启数据库生效，新手可以先小幅度调整，观察查询效果再慢慢优化。

六、实战案例：从 15 秒到 1 秒，千万级订单表优化过程

有个朋友的订单表 1200 万条数据，查 “用户近 30 天的订单总额” 要 15 秒，按这几步优化后变快到 1 秒：

检查索引：发现user_id和create_time没建联合索引，建了CREATE INDEX idx_orders_uid_ctime ON orders(user_id, create_time);；
优化语句：把SELECT *改成只查需要的order_id和amount，去掉不必要的排序；
分区表：按create_time拆成月度分区，查询时只扫近 30 天所在的分区；
调配置：shared_buffers从 1GB 调到 4GB，work_mem调到 16MB。

优化后同样的查询，数据库用索引定位到分区，扫描数据量从 1200 万降到 8 万，速度自然快了。这或许暗示只要多维度优化，千万级数据查询也能高效运行。

兔子哥的小建议

千万级数据优化没有 “一招通吃” 的方法，得结合表结构、查询场景一点点调。新手可以先从索引和语句入手，这两个优化成本低、效果明显，等熟悉了再试分区和配置调整。
平时要多观察慢查询，用EXPLAIN ANALYZE看看执行计划，比如有没有全表扫描、索引是否用到，针对性优化。另外，数据量增长是必然的，最好在表设计初期就考虑分区和索引策略，别等数据量大了再返工。
其实大数据量查询优化就像给车做保养，定期检查、及时调整，车就能跑得稳。PostgreSQL 处理千万级数据完全没问题，关键是用对方法，耐心调优。关于某些高级索引类型（比如 GIN 索引）在特定场景的优化效果，我目前也只是初步尝试，具体机制还得进一步研究，但基础优化做好，大部分场景都能应付，希望这些技巧能帮到你！

标签： PostgreSQL