PG中的索引扫描类型

简介

性能是数据库管理系统的基本要素之一。性能低下意味着查询时间过长和前端响应时间过高。提高性能的最有效方法之一是为表列建立适当的索引。索引可以节省大量的数据访问时间，并引导查询以最快的方式收集结果。在PostgreSQL中，有不同的方法可以利用索引来产生最有效的计划。

本文中，我们将回顾以下三种不同的索引扫描类型，这取决于表、查询检索的内容和使用的过滤器：

·位图索引扫描

·索引扫描

·仅索引扫描

构建测试场景

在下面的练习和示例中，我们将使用带有单一索引的表，并回顾扫描策略如何根据查询条件而改变。

定义表：

在这里，我们考虑了三列不同的选择性，即不同值占总行数的比例。下面是从高到低排列的列：

·register_date：我们使用random()函数加载了1000万条记录，所以这三列中，register_date唯一的值是最多的。

·age：当我们加载数据时，也使用了random()函数，但是用floor()函数限制了结果，所以所有不同的值都在1到99之间。

·is_active. 这一列的数据类型为布尔型，因此只有两个不同的值，即true和false。

在规划索引时，必须考虑列的选择性。

例如，在上面的列中，在is_active列上使用单个索引不会增加任何优势，因为在所有的10M行中，只有两个值是可能的，所以如果我们想过滤所有is_active = true的行，优化器将毫无疑问地使用顺序扫描。

验证列的不同值数量的一种方法是查询数据库中的pg_stats视图。在这种情况下，我们运行ANALYZE命令：

可以看到，age列不同的值是99，而is_active列只是2；对于register_date列的值，之所以是-1，是应为analyze认为该列不同的值和所有行数相等。

不带过滤条件，则会顺序扫描（全表扫）

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

不出所料，为了从表中获取所有数据，优化器决定进行顺序扫描，获取全部1000万条记录。这是有道理的，因为它是一次性获取所有行。总耗时超过81347.117 ms。

位图索引扫描(Bitmap Index Scan)和位图堆扫描（Bitmap Heap Scan）

当查询请求的数据量足够大，可以利用批量读取的优势（如顺序扫描），但又不至于需要处理整个表时，优化器就会选择这种索引扫描方法。我们可以把位图索引扫描看作是介于顺序扫描和索引扫描之间的一种方法。

关于bitmap index scan：

• 核心是传统的index scan每次从索引中去取一个tuple的指针，然后立马去表中取数据，每一次会造成一次随机io。如果数据量较多的情况下，会比较低效。而bitmap scan一次性将符合条件的tuple-pointers全部取出来，然后在内存中进行地址排序，然后去取出数据，这时的读取数据由于进行的地址排序，读取时就变成了顺序的读。其实就是一个随机读转化为顺序读取的过程，但是取出的数据由于进行了地址的排序，就没有顺序。同时，对于limit这种sql，bitmap index scan这种就不适合，因为它一次会取出所有数据。
• 也就是说bitmap index scan是为了减小index scan扫描同一个页次数过多代价太高而设计的，bitmap index scan适合获取表中的数据量有点大（10%-20%）这样会带了额外的随机扫描开销。有了bitmap index scan就可以先扫描索引，然后符合条件的多条数据如果在同一个page页那么只需要扫描一次页就能获取这些数据，所以index scan一般比bitmap的代价要高，所以优化器优先选择了bitmap index scan。
• 如果人为的降低这个代价，那么优化器又会选择index scan，而人为的修改方法就有降低random_page_cost

位图索引扫描总是和位图堆扫描（Bitmap Heap Scan）一起工作；第一次位图索引扫描找到所有合适的行位置并建立位图，然后第二次使用位图堆扫描逐个扫描堆页面并收集行。

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章

验证码：

请关注本站微信公众号，回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。