索引与PostgreSQL新手

2022-02-10 00:00:00 索引查询数据分析数据库

索引是优化数据库工作负载和减少查询时间的关键。PostgreSQL现在支持多种类型的索引，了解基础知识是使用PostgreSQL的关键部分。

数据库索引的作用类似于书后的索引部分。数据库索引存储有关数据行在表中的位置的信息，因此数据库不必扫描整个表以获取信息。当数据库有要检索的查询时，它首先转到索引，然后使用该信息检索请求的数据。

索引是它们自己的数据结构，它们是PostgreSQL数据定义语言 (DDL) 的一部分。它们与数据表和其他对象一起存储在磁盘上。

B-tree 索引是常见的索引类型，如果您创建索引但不指定类型，它将是默认索引。B-tree索引非常适合对您经常查询的信息进行通用索引。
BRIN 索引是块范围索引，专门针对您正在搜索的数据以块为单位的非常大的数据集，例如时间戳和日期范围。众所周知，它们非常高效且节省空间。
GIST 索引在您的数据库中构建搜索树，常用于空间数据库和全文搜索用例。
当您在单个列中有多个值时， GIN 索引很有用，这在您存储数组或 json 数据时很常见。

下面用一个实例在Crunchy Bridge上进行了所有测试，这对于这种快速数据加载和测试工作来说非常好。如果您想了解使用的数据，这篇文章旁边提供了一些示例。您还可以使用 Crunchy 的学习门户做一个索引教程。

使用解释分析

解释分析将为您提供查询计划、执行时间和任何查询的其他有用的信息。因此，当您使用索引时，您将使用解释分析检查索引以查看查询路径和查询时间。

您会看到查询计划指示“顺序扫描”。这意味着它会扫描表中的每个数据行以查看它是否与查询条件匹配。您可能会猜到，对于较大的表，顺序扫描可能会占用相当多的时间，因此索引可以节省您的数据库工作量。

Seq Scan on weather (cost=0.00..168.00 rows=496 width=102) (actual time=0.011...181 rows=100 loops=1)

如果您使用索引，您将在解释结果中看到索引扫描。

Bitmap Index Scan on idx_weather_type  (cost=.00..8.00 rows=496 width=) (actual time=.027...027 rows=496 loops=1

分析数据库

ANALYZE命令收集有关内部查询计划程序使用的表或数据库的信息。在某些情况下，您可能希望在添加索引之前或之后运行它，以便数据库使用新的查询计划。在我的测试中，这似乎会影响更大的数据库。

B-Tree样本

对于B-Tree样本，我使用了一些带有类型、损坏、时间和地点的数据事件的开放天气数据。作为一个非常基本的指标，我要找到所有与冬季风暴有关的指标。

一旦我添加了这个索引，这意味着要获得这个数据，数据库不必扫描所有的天气事件来获得关于恶劣天气事件的额外数据，它已经知道在哪里查找这些数据。

开始查询

SELECT * FROM weather where event_type='Winter Storm'

索引分析之前

Seq Scan on weather  (cost=0.00..9204.64 rows=3158 width=853) (actual time=0.008..27.619 rows=3182 loops=1)
Execution Time: 27.778 ms

创建索引

CREATE INDEX idx_weather_type ON weather(event_type);

索引分析之后

Bitmap Index Scan on idx_weather_type  (cost=0.00..35.98 rows=3158 width=) (actual time=0.247...247 rows=3182 loops=1)
Execution Time: 3.005 ms

看看查询时间的下降！

多列 B 树索引

索引并不总是只为单列创建，Postgres 还支持多列索引。如果您知道一次要在多个列上进行大量查询，这些可能会很有用。

开始查询

SELECT * FROM weather WHERE event_type='Winter Storm'   AND damage_crops > '0'

索引分析之前

Seq Scan on weather  (cost=0.00..9402.36 rows=2586 width=853) (actual time=0.007..67.365 rows=2896 loops=1)
Execution Time: 67.499 ms

创建多列索引

CREATE INDEX idx_storm_crop ON weather(event_type,damage_crops);

索引分析之后

Bitmap Index Scan on idx_storm_crop  (cost=0.00..38.15 rows=2586 width=) (actual time=0.339...339 rows=2896 loops=1)
Execution Time: 2.204 ms

再次减少了查询时间！

如果您对索引的明细不太清楚，下面查询将显示特定表上的所有索引:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'weatherevents';

Drop index indexname如果您想再次测试，将删除它。

BRIN 指数样本

BRIN 通常在使用大型数据集时非常有用，特别是在使用时间序列或时间戳数据的情况下。为此，我使用了来自 IoT 数据集的样本，每天有数千行数据。

开始查询

SELECT device, humidity FROM iot WHERE ts between '2020-07-13 0:00' AND '2020-07-14 0:00'

分析之前

Parallel Seq Scan on iot  (cost=0.00..10363.95 rows=896 width=28) (actual time=12.710..42.080 rows=16707 loops=3)
Execution Time: 67.851 ms

创建索引

CREATE INDEX iot_time ON iot USING brin(ts);

分析之后

Bitmap Index Scan on iot_time  (cost=0.00..12.26 rows=54025 width=) (actual time=0.046...047 rows=10240 loops=1)
Execution Time: 10.513 ms

您经常会听到 BRIN 索引非常节省空间。因此，当您处理索引时，您可能想要查询实际索引的大小，例如：

pg_size_pretty(pg_relation_size('iot_time'));

使用GIST的基本空间索引

如果您在数据库中使用空间数据，您可能有很多数据，索引可能是至关重要的。对于空间索引，我使用了PostGIS 教程3中的数据和示例。如果您刚开始使用空间数据，我强烈推荐本教程。

开始查询

SELECT count(blocks.blkid)
FROM nyc_census_blocks blocks
JOIN nyc_subway_stations subways
ON ST_Contains(blocks.geom, subways.geom)
WHERE subways.name LIKE 'B%';

分析之前

Timing: Generation 4.364 ms, Inlining 360.628 ms, Optimization 615.663 ms, Emission 559.573 ms, Total 1540.227 ms
Execution Time: 1467.916 ms

创建空间 GIST 索引

CREATE INDEX nyc_census_blocks_geom_idx
ON nyc_census_blocks
USING GIST (geom);

索引分析之后

Execution Time: 7.575 ms

空间索引产生了巨大的影响！

JSON的GIN索引

JSON 数据已被 Postgres 用户广泛使用，并且 Postgres 核心项目已经接受了具有广泛特性的 JSON数据类型的使用。如果您的数据在每个字段中列出了多个对象，那么有些索引类型会非常有用。GIN索引类型通常用于此目的。对于这个例子，我使用了一个来自 NASA 的 json 文件，其中包含流星位置信息。

开始查询

SELECT data -> 'name' as name FROM meteors WHERE data @> '{"mass": "100"}';

索引分析之前

Parallel Seq Scan on meteors  (cost=0.00..23926.28 rows=4245 width=32) (actual time=0.065..114.114 rows=1024 loops=3)
Execution Time: 123.698 ms

创建索引

CREATE INDEX gin_test ON meteors USING gin(data)

解释分析之后

Bitmap Index Scan on gin_test  (cost=0.00..116.40 rows=10187 width=) (actual time=12.164..12.164 rows=3072 loops=1)
Execution Time: 22.017 ms

找到合适的索引

当您即将运行一次性查询时，您不应该及时创建索引。一个好的索引计划需要计划和测试。索引存储在磁盘上，因此它们也占用空间，所以这也是一个考虑因素。

对于插入的每个新数据行或更新的现有数据行，数据库会自动更新索引条目。索引也会对数据库写入操作的性能产生影响，所以一定要研究一些。就像 Craig近说的，每个人都需要找到自己的 Goldilocks of indexes：不要太大，不要太小，但要恰到好处。

原文链接：https://mp.weixin.qq.com/s/yIYx9I1cwExhg20fzDO8zA

相关文章