数据库优化-排序篇 (order by)_pg order by优化

数据库优化-排序篇 (order by)

背景

日常开发工作中, 经常使用order by对结果集进行排序, 但是对其原理和具体实现可能不是很了解. 这样无法进行有效的分析和针对性优化.

因此, 我写下本篇文章介绍一下 order by的基本原理和一些工作中常见的排序相关问题.

数据准备

因为在工作中使用pg多一些, 所以文中的数据库选择了 PostgreSQL 11.14 64-bit

造一些测试数据, 可以参考: pg 快速造1000w测试数据

CREATE TABLE public.testdata (
 id int4 NOT NULL,
 "name" varchar(20) NULL,
 course int4 NULL,
 grade numeric(4, 2) NULL,
 testtime date NULL,
 note text NULL,
 CONSTRAINT testdata_pkey PRIMARY KEY (id)
);
-- 在name字段上有索引
CREATE INDEX idx_testdata_name ON public.testdata USING btree (name);
1
2
3
4
5
6
7
8
9
10
11

我造了1亿数据的testdata表, 空间约11G. id为主键, name上有btree索引.

order by的基本原理

数据库在确保能够返回正确的数据集的下, 尽快地返回数据. 如果内存够，就要多利用内存，尽量减少磁盘访问。

在此目的下, 数据库在执行 order by 语句时, 会根据返回的结果集大小和offset等因素选择不同的策略.

一般有4种策略.

• top-N heap sort
• quicksort
• external merge
• index scan

top-N heap sort

如果返回的结果集比较小,且是top-n, 例如返回前10行, 前20行.

数据库会选择top-N heap sort 排序方式.

top-N heap sort 就是堆排序. 举例: 维护一个大小为3的小顶堆, 在遍历表中数据时, 如果发现有比堆中数据小的值, 就放入堆中, 然后将原来堆中最大的一个移除. 等遍历完表中的所有行之后, 小顶堆中的数据就是最小的3个.

这种排序方式的优点是

• 占用内存少, 只需要维护较小数据量的堆
• 排序速度快, 因为直接在内存中排序, 不需要访问硬盘

缺点是:

• 只能适用于前N条数据

explain analyze select * from testdata order by testtime  limit 10
1

Limit  (cost=2269506.38..2269507.55 rows=10 width=41) (actual time=14447.031..14463.813 rows=10 loops=1)
  ->  Gather Merge  (cost=2269506.38..11992407.50 rows=83333334 width=41) (actual time=14447.030..14463.811 rows=10 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=2268506.36..2372673.03 rows=41666667 width=41) (actual time=14408.032..14408.033 rows=10 loops=3)
              Sort Key: testtime
              Sort Method: top-N heapsort  Memory: 26kB
              Worker 0:  Sort Method: top-N heapsort  Memory: 26kB
              Worker 1:  Sort Method: 
1
2
3
4
5
6
7
8