性能是至关重要的一个方面的一个数据库管理系统。很少可以更恼人的和令人沮丧的用户比表现不佳,长时间运行的查询和意义在前端高响应时间。

最有效的方法之一解决性能改善有适当的索引的表列。索引可以节省很多时间在数据访问和查询收集结果最快的方式。

在PostgreSQL,有不同的方法可以利用索引来产生最有效的计划。

在这篇文章中,我们将回顾以下三个不同的PostgreSQL扫描类型取决于索引表,查询检索,使用过滤器:

  • 位图索引扫描
  • 索引扫描
  • 只索引扫描

构建的测试场景

我们将使用一个表和一个指数以下练习和例子和评论如何扫描策略可以改变根据查询条件。

下表定义。我包括创建语句序列id列自一个最佳实践总是有一个主键列,但我们会通过具体的例子,我们不需要它。

采取的一些灵感如何生成测试数据与SQL数据库雷竞技下载官网Percona博客,我们将使用以下SQL语句插入一千万行这个表:

现在我们有我们的测试表和一些假数据我们可以练习。

索引数据

正如我们之前说的,一个最佳实践是添加一个表的主键,但我们跳过这一步,添加一个综合指数,将帮助我们检查不同的扫描类型。

我们创建这个多列索引如下:

在这里,我们认为三列不同基数,这意味着不同值的比例的总行数。下面是列下令从高到低基数:

  1. register_date。我们把10 m行设置这一列的帮助下随机()功能,所以不同值的数量从这三列是最大的。
  2. 年龄。当我们装载数据,我们还使用了随机()功能,但我们“有限”的结果地板()功能,所以1和99之间的所有不同的值。
  3. is_active。这列数据类型是布尔,所以只有两个不同的值是可能的,真正的和假。

必须考虑一个列的数据基数计划指标时,甚至在这之前,过滤器,我们将执行对数据。

例如,在上面的列中,有一个索引的is_active列将不会添加任何优势,因为,从所有的10 m行,只有两个值是可能的,如果我们想要过滤的所有is_active = true行,毫无疑问的规划师将使用顺序扫描。

一种验证一个列的不同值的数量是通过查询pg_stats在我们的数据库视图。这是很重要的,确保统计是新鲜的;在这种情况下,我们运行分析命令:

前一列,下面是查询的结果pg_stats观点:

和我们确认年龄而列有99个不同的值is_active只有2。的register_date列显示的负值,因为文档中所描述的,分析认为不同值的数量可能是一样的总行数:

[…]1显示一个独特的列中不同值的数量是一样的行数。

一个索引中,不同的扫描类型

现在我们已经表数据和索引,我们可以测试不同的扫描类型。首先,有一个起点,让我们验证PostgreSQL如何将解决一个查询所有表数据没有过滤器:

正如所料,从表中检索所有数据,计划决定顺序扫描,所有的10 m行。它是有意义的,因为它是所有行。总时间结束了1183毫秒(~ 1.1秒)。

位图索引扫描

规划师选择这个索引扫描方法,当查询要求一个足够大的数据量,可以利用大部分阅读的好处,像顺序扫描,但实际上并不大,需要处理所有桌子上。我们能想到的位图索引扫描是之间的顺序和索引扫描。

的位图索引扫描总是在结对工作位图堆扫描;第一个扫描索引找到所有合适的行位置和构建位图,第二个使用它位图扫描堆页面一个接一个,收集行。

下面是一个例子位图索引扫描使用我们之前建立的表和索引:

首先在内部节点(执行)位图索引扫描在idx_person_age_date_active索引。它创建位图与所有适当的行位置并将其传递到它的父节点(后执行)平行的位图堆扫描在人表。这一阶段通过单独的页面,执行重新检查过滤条件,返回结果数据集。

比较,考虑如何使用只是一个相同的操作执行顺序扫描:

考虑这个查询了101 k行,约占总数的1%的行。的位图索引扫描利用“顺序扫描”风格大部分阅读有限数量的页面和一个更好的结果比直接生产顺序扫描,执行快2倍。

索引扫描

可能是扫描方法你觉得当听到类似,“嘿,这个查询的方法是行善;它使用索引…。“这个方法的基本定义是通过索引访问数据。

的索引扫描包括两个步骤,第一是让行位置从索引中,第二个是收集实际数据从堆中或表页面。所以每索引扫描访问两个读操作。但是,这是一个最有效的方法从一个表中检索数据。

规划师挑选这种扫描方法在检索的行数很小,所以执行两步索引扫描操作是“便宜”,速度比单独收集表格处理的数据页。

使用我们的测试表,下面的一个例子索引扫描:

查询中看到,我们使用之前,我们已经添加了一个新的筛选器表达式:和register_date = 2023-03-23 19:50:03.22938 + 00::时间戳。的register_date列是多列索引的一部分idx_person_age_date_active。因为我们是由奇异值过滤,同样有一个索引条目,所以PostgreSQL的特定行位置指数在一读,然后从表中所有的行数据页面内的位置。整个查询了0.064毫秒;这是快!

在上面的例子中,通过特定的查询过滤器时间戳值register_date列,但是PostgreSQL仍然选择索引扫描多行如果的行数很小,例如,在以下:

查询过滤register_date列的使用范围之间的操作符。根据统计,规划师认为结果会一行并选择了索引扫描。最后,结果集是八行,所以八对读操作的存在。不过,查询很快解决,0.337毫秒。

只索引扫描

最后,我们将审查只索引扫描方法。这是一个很好的方法,PostgreSQL使用改善标准索引扫描方法。

PostgreSQL时使用这种方法查询要求的所有数据已经存在于索引;换句话说,/表达式列选择和在哪里条款应该是索引的一部分,这样就可以避免第二次读操作从表中获取数据并返回结果页面数据只能从索引读取操作。

在下面,我们使用几乎相同的查询使用索引扫描的例子,但而不是要求所有行列(*),我们只是检索三列用于构建多列索引:

看到解释输出现在说只索引扫描,而且,它证实了没有访问堆(表页)线堆获取:0。的时间甚至比索引扫描之前,只有0.058毫秒。这种扫描方法可以帮助获得最佳性能的查询符合条件。

记住,索引所有所以该指数包含的列所有相同的数据表”不是一个好主意。“如果是这样的话,PostgreSQL不会看到任何使用索引的优势,将选择顺序扫描的方法。见以下:

在上面,在单个事务中,我们放弃了我们使用多列索引在前面的例子中,并创建了一个新的考虑所有表列,然后刷新统计数据,尝试一个查询要求所有列(*)在一个特定的过滤器,因此,计划选择顺序扫描,它想提高它与并行执行操作。不过,最后的执行时间是远离我们的好结果。

最后的建议

既然我们已经回顾了不同的扫描选项PostgreSQL可以使用在一个指数根据存储数据和查询过滤条件让我分享一些最终的想法你可以找到有用的在规划查询过滤器和表的索引。

  1. 索引列基数最多的。这后,查询时可以执行最佳过滤由同一列。有较低的基数上的索引列将产生相反的效果,因为这只会增加额外的维护,高确定性,规划师将不会使用它们。
  2. 计划你的查询小(特定的)数据集,而不是更大的。如果你的工作量和服务设计提供,考虑过滤数据考虑检索几行。正如我们看到的,索引扫描是一种有效和优化技术更快地检索数据,和PostgreSQL将使用它如果结果数据是足够小。
  3. 检索所需要的列。通过这样做,PostgreSQL可以利用只索引扫描从堆中,避免“额外”阅读(表页)。这些节省了将产生一个臭名昭著的高体积查询环境中良好的效果。记住,多列索引并不是唯一的方法来领导计划选择只索引扫描;你也可以考虑覆盖索引(另一个博客,也许)。
  4. 调优random_page_cost参数。降低,这将导致规划师喜欢索引扫描而不是顺序扫描。现代SSD可以提供更好的吞吐量随机读访问权,所以你可能会相应地分析调整这个参数。
  5. 调优effective_cache_size参数。这个参数设置为更高的价值(RAM总额的近75%,如果你的机器是致力于PostgreSQL服务)将帮助规划者选择索引扫描顺序扫描。

记住每个实现都是不同的,和细节问题,所以在调整任何生产是明智的分析和测试在一个较低的环境影响。

雷竞技下载官网Percona分布PostgreSQL提供最好的和最关键的企业组件从开源社区在一个分布,设计和测试一起工作。

raybet52

订阅
通知的
客人”src=