为了后续分析索引执行情况，本文首先简单介绍一下pg中的explain语句的相关分析
explain输出分析
通过下列语句我们通常能够从pgsql获得一段json文本:
explain (analyze,verbose,timing,costs,buffers,format json) SELECT * FROM
yfr_test2
WHERE coordinates <@ box '(0, 0, 10, 10)';
[
{
"Plan": {
"Node Type": "Seq Scan", // 执行节点类型,此处为顺序扫描
"Parallel Aware": false, // 是否可以并行执行
"Relation Name": "yfr_test2", // 关系名,即表名
"Schema": "public", // 所在schema
"Alias": "yfr_test2", // 关系的别名
"Startup Cost": 0.00, // 启动花费成本估计
"Total Cost": 1.04, // 总成本估计
"Plan Rows": 1, // 计划行数估计
"Plan Width": 166, // 计划宽度,一行输出的平均大小
"Actual Startup Time": 0.013, // 实际启动时间
"Actual Total Time": 0.015, // 实际总时间
"Actual Rows": 3, // 实际扫描行数
"Actual Loops": 1, // 实际循环次数
"Output": ["id", "name", "coordinates"], // 输出列
"Filter": "(yfr_test2.coordinates <@ '(10,10),(0,0)'::box)", // 过滤条件
"Rows Removed by Filter": 0, // 过滤移除的行数
"Shared Hit Blocks": 1, // 共享块命中数
"Shared Read Blocks": 0, // 共享块读取数
"Shared Dirtied Blocks": 0, // 共享块脏块数
"Shared Written Blocks": 0, // 共享块写入数
"Local Hit Blocks": 0, // 本地块命中数
"Local Read Blocks": 0, // 本地块读取数
"Local Dirtied Blocks": 0, // 本地块脏块数
"Local Written Blocks": 0, // 本地块写入数
"Temp Read Blocks": 0, // 临时块读取数
"Temp Written Blocks": 0 // 临时块写入数
},
"Planning Time": 0.066, // 查询规划时间
"Triggers": [], // 触发器
"Execution Time": 0.045 // 执行时间
}
]
| 字段 | 说明 | 实际含义 |
|---|---|---|
| Node Type | 执行节点类型,此处为顺序扫描 | |
| Parallel Aware | 是否可以并行执行 | 取值为true更好,表示查询可以并行加速 |
| Relation Name | 关系名,即表名 | |
| Schema | 所在schema | |
| Alias | 关系的别名 | |
| Startup Cost | 启动花费成本估计 | 数值越小越好,表示启动查询的代价越低 |
| Total Cost | 总成本估计 | 数值越小越好,表示查询总成本越低 |
| Plan Rows | 计划行数估计 | 数值越接近Actual Rows越好,表示行数估计越准确 |
| Plan Width | 计划宽度,一行输出的平均大小 | 数值越小表示查询处理的平均数据越少 |
| Actual Startup Time | 实际启动时间 | 数值越小表示启动时间越短 |
| Actual Total Time | 实际总时间 | 数值越小表示查询时间越短 |
| Actual Rows | 实际扫描行数 | |
| Actual Loops | 实际循环次数 | 数值越小表示查询的循环次数越少 |
| Output | 输出列 | |
| Filter | 过滤条件 | |
| Rows Removed by Filter | 过滤移除的行数 | 数值越小表示过滤的行越少 |
| Shared Hit Blocks | 共享块命中数 | 数值越大表示共享缓存利用越高 |
| Shared Read Blocks | 共享块读取数 | 数值越小表示共享IO读取越少 |
| Shared Dirtied Blocks | 共享块脏块数 | 数值越小表示共享缓存污染越少 |
| Shared Written Blocks | 共享块写入数 | 数值越小表示共享写入IO越少 |
| Local Hit Blocks | 本地块命中数 | 数值越大表示本地缓存命中率越高 |
| Local Read Blocks | 本地块读取数 | 数值越小表示本地IO读取越少 |
| Local Dirtied Blocks | 本地块脏块数 | 数值越小表示本地缓存污染越少 |
| Local Written Blocks | 本地块写入数 | 数值越小表示本地写入IO越少 |
| Temp Read Blocks | 临时块读取数 | 数值越小表示通过临时表减少IO读次数 |
| Temp Written Blocks | 临时块写入数 | 数值越小表示通过临时表减少IO写次数 |
| Planning Time | 查询规划时间 | 数值越小表示规划时间更短 |
| Triggers | 触发器 | |
| Execution Time | 执行时间 | 数值越小表示执行时间更短 |
常用索引介绍
btree
b-tree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。
索引与递归查询结合，还能实现快速的稀疏检索。
hash
hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。
hash索引特别适用于字段VALUE非常长（不适合b-tree索引，因为b-tree一个PAGE至少要存储3个ENTRY，所以不支持特别长的VALUE）的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index。
gin
1、当需要搜索多值类型内的VALUE时，适合多值类型，例如数组、全文检索、TOKEN。（根据不同的类型，支持相交、包含、大于、在左边、在右边等搜索）
2、当用户的数据比较稀疏时，如果要搜索某个VALUE的值，可以适应btree_gin支持普通btree支持的类型。（支持btree的操作符）
3、当用户需要按任意列进行搜索时，gin支持多列展开单独建立索引域，同时支持内部多域索引的bitmapAnd, bitmapOr合并，快速的返回按任意列搜索请求的数据。
案例
给出三组使用 GIN 索引的案例,每组包括建表、插入数据、创建索引和查询语句,并说明与 Btree 索引的对比:
- 全文搜索
(1) 建表
CREATE TABLE documents (
id serial PRIMARY KEY,
title text,
content text
);
(2) 插入数据
INSERT INTO documents (title, content) VALUES
('PostgreSQL Tutorial', 'This is a PostgreSQL tutorial'),
('MySQL Tutorial', 'This is a MySQL tutorial');
(3) 创建索引
CREATE INDEX idx_search ON documents USING GIN (to_tsvector(content));
(4) 查询
SELECT * FROM documents
WHERE to_tsvector(content) @@ to_tsquery('postgresql');
如果使用 Btree,需要为每个词创建独立索引,查询效率低。
- 数组包含查找
(1) 建表
CREATE TABLE table (
id serial PRIMARY KEY,
int_array integer[]
);
(2) 插入数据
INSERT INTO table (int_array) VALUES
('{1,2,3}'),
('{4,5,6}');
(3) 创建索引
CREATE INDEX idx_gin ON table USING GIN (int_array);
(4) 查询
SELECT * FROM table WHERE int_array @> '{3}';
Btree 索引无法有效支持数组包含查找。
- 多列组合索引
(1) 建表
CREATE TABLE table (
id serial PRIMARY KEY,
col1 text,
col2 text
);
(2) 插入数据
INSERT INTO table VALUES
(1, 'abc', 'def'),
(2, 'ghi', 'jkl');
(3) 创建索引
CREATE INDEX idx_gin ON table USING GIN (col1, col2);
(4) 查询
SELECT * FROM table WHERE col1 = 'abc' AND col2 = 'def';
Btree组合列索引效果不佳,只能单列创建索引。
总结:
GIN索引支持Btree索引不擅长的查询场景,可以显著提升效率。
gist
支持各种数据类型如文本、地理位置等的索引。可以通过操作符类自定义索引方法。
GiST是一个通用的索引接口，可以使用GiST实现b-tree, r-tree等索引结构。不同的类型，支持的索引检索也各不一样。例如：
1、几何类型，支持位置搜索（包含、相交、在上下左右等），按距离排序。
2、范围类型，支持位置搜索（包含、相交、在左右等）。
3、IP类型，支持位置搜索（包含、相交、在左右等）。
4、空间类型（PostGIS），支持位置搜索（包含、相交、在上下左右等），按距离排序。
5、标量类型，支持按距离排序。
案例
- 建表
CREATE TABLE locations (
id serial PRIMARY KEY,
name varchar(64),
coordinates point
);
包含地理坐标的点数据。
- 插入数据
INSERT INTO locations (name, coordinates)
VALUES
('Store 1', point(3.2, 5.1)),
('Store 2', point(7.3, 2.4)),
('Store 3', point(5.1, 6.5));
- 创建GiST索引
CREATE INDEX idx_locations_coordinates ON locations USING GIST(coordinates);
在点数据上创建GiST索引。
- 查询
SELECT * FROM locations
WHERE coordinates <@ box '(0, 0, 10, 10)';
利用GiST索引可以有效地进行二维区域查找。
对于地理空间数据,GiST可以建立最小边界区域的索引,从而进行空间搜索和操作的优化。
brin
BRIN 索引是块级索引，有别于B-TREE等索引，BRIN记录并不是以行号为单位记录索引明细，而是记录每个数据块或者每段连续的数据块的统计信息。因此BRIN索引空间占用特别的小，对数据写入、更新、删除的影响也很小。
BRIN属于LOSSLY索引，当被索引列的值与物理存储相关性很强时，BRIN索引的效果非常的好。例如时序数据，在时间或序列字段创建BRIN索引，进行等值、范围查询时效果很棒。
案例
- 建表
CREATE TABLE test_table (
id bigint PRIMARY KEY,
comment text,
created_at timestamp
);
- 插入数据
INSERT INTO test_table
SELECT id, 'test comment ' || id, '2020-01-01'::timestamp + interval '1 day' * idFROM generate_series(1, 1000000) id;
这里插入100万条测试数据。
- 创建BRIN索引
CREATE INDEX idx_test_table_created_at ON test_table USING BRIN (created_at);
在created_at时间字段上创建BRIN索引。
- 解释查询
EXPLAIN ANALYZE
SELECT * FROM test_table
WHERE created_at > '2020-06-01' AND created_at < '2020-06-15';
这种范围查询可以利用BRIN索引进行块区间剪裁,避免全表扫描,提高查询效率。
BRIN索引占用空间很小,但可以对大表进行有效过滤,避免不必要的IO,提升范围查询性能。这展示了BRIN索引的典型优势。
参考文档
https://developer.aliyun.com/article/111793#slide-22
