首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

Postgres索引详解

作者：王素维060615 | 来源：互联网 | 2023-09-12 11:25
适用于Postgres初学者的b-tree、GIN和GIST索引示例。数据库索引的作用类似于书后的索引部分。数据库索引存储有关数据行在表中的位置的信息，因此数据库不必扫描整个表以获


适用于 Postgres 初学者的 b-tree、GIN 和 GIST 索引示例。

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

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

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




使用 Explain analyze

Explain analyze会给你提供信息，如查询计划、执行时间，以及任何查询的其他有用信息。因此，当你在使用索引时，你将使用explain analyze检查索引，以审查查询路径和查询时间。

你会看到查询计划表明是一个 "Seq扫描"，或一个顺序扫描。这意味着它扫描了表中的每一条数据行，看它是否符合查询条件。你可能会猜到，对于较大的表，顺序扫描可能会占用相当多的时间，所以这就是索引节省数据库工作量的地方。

Seq Scan on weather (cost=0.00..168.00 rows=496 100%" cellpadding="0" cellspacing="0" border="0">

Bitmap Index Scan on idx_weather_type (cost=0.00..8.00 rows=496 100%" cellpadding="0" cellspacing="0" border="0">

SELECT * FROM weather where event_type='Winter Storm'





在持久索引之前的explain analyze：

Seq Scan on weather (cost=0.00..9204.64 rows=3158 100%" cellpadding="0" cellspacing="0" border="0">

CREATE INDEX idx_weather_type ON weather(event_type);





索引之后的explain 分析：

Bitmap Index Scan on idx_weather_type (cost=0.00..35.98 rows=3158 100%" cellpadding="0" cellspacing="0" border="0">

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



在索引之前的explain分析：

Seq Scan on weather (cost=0.00..9402.36 rows=2586 100%" cellpadding="0" cellspacing="0" border="0">

CREATE INDEX idx_storm_crop ON weather(event_type,damage_crops);



索引之后的explain分析：

Bitmap Index Scan on idx_storm_crop (cost=0.00..38.15 rows=2586 100%" cellpadding="0" cellspacing="0" border="0">

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





Drop index indexname可以删除索引





BRIN指数样本

在使用大型数据集，特别是有时间序列或时间戳的数据时，BRIN通常非常有用。我使用了一个物联网数据集2的样本，每天有成千上万的数据行。

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





索引之前的Explain分析：

Parallel Seq Scan on iot (cost=0.00..10363.95 rows=896 100%" cellpadding="0" cellspacing="0" border="0">

CREATE INDEX iot_time ON iot USING brin(ts);





再次explain分析结果：

Bitmap Index Scan on iot_time (cost=0.00..12.26 rows=54025 iot_time'));



使用GIST的基本空间索引

如果你在你的数据库中使用空间数据，你可能有很多数据，索引可能是至关重要的。对于空间索引，我使用了PostGIS教程3中的数据和例子。如果你刚开始使用空间数据，我强烈推荐这个教程的总体内容。

SQL query



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%';



开始 explain analyze



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





实现样别索引spatial GIST index



CREATE INDEX nyc_census_blocks_geom_idx
ON nyc_census_blocks
USING GIST (geom);





explain analyze时间：



Execution Time: 7.575 ms







用于JSON的GIN索引

JSON数据在Postgres用户中被广泛采用，Postgres核心项目也通过广泛的功能来支持JSON数据类型的采用。如果你的数据在每个字段中都列出了几个对象，那么有一些索引类型可以真正帮助你。GIN索引类型通常用于此。在这个例子中，我使用了NASA的一个json文件，其中有流星的位置信息。



SQL query

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





索引前的解释分析：



Parallel Seq Scan on meteors (cost=0.00..23926.28 rows=4245 100%" cellpadding="0" cellspacing="0" border="0">

CREATE INDEX gin_test ON meteors USING gin(data)





索引后解释分析：

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







用索引找到合适的位置

你不应该在你要运行一个一次性查询的时候临时创建一个索引。一个好的索引计划需要计划和测试。索引是存储在磁盘上的，所以它们也会占用空间，这也是一个考虑因素。对于每一个插入的新数据行或更新的现有数据行，数据库会自动更新索引条目。索引绝对也会对数据库写操作的性能产生影响，所以一定要研究一些。就像Craig最近说的，每个人都需要找到他们的Goldilocks索引：不要太大，不要太小，但要恰到好处。

推荐阅读
author-avatar
王素维060615
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有