数学没学好连建个索引都建不好了吗

最新推荐文章于 2024-10-09 12:58:26 发布

DBAIOps社区

最新推荐文章于 2024-10-09 12:58:26 发布

阅读量542

点赞数 30

分类专栏： PG技术文章文章标签：数据库运维 dba

本文链接：https://blog.csdn.net/DBAIOps/article/details/136021409

版权

PG技术文章专栏收录该内容

30 篇文章 1 订阅

订阅专栏

如果有个需求，一张数亿条记录的大表，上面的读写操作都很多。这张表上有个应用，针对其中的十多个字段（这张表一共有不到20个字段）做等于条件的查询，不过每次查询的字段组合不固定，会根据用户在界面上的输入条件自动生成。这种业务如何通过索引来做优化呢？数亿条记录的大表，不建索引每次都全表扫描也受不了，建索引又不知道该怎么建。如果把这十几个字段建个复合索引，效果也不一定好，而且索引可能比表都大了。

这种全部采用等于条件的即席查询虽然不怎么常见，不过在我们这十多年的优化工作中还真遇到过几次。以前往往只能选择其中较为常见的业务去建几个复合索引，对于不常见的查询，就只能让客户去等了。不过一种新的索引形式让这一切都得到了改善，那就是bloom filter。Bloom filter是目前研发人员十分常用的一种过滤器协议，有过Redis穿透过滤编程经验的人肯定用过这个过滤器。在Redis上如果保留了一份Bloom Filter的索引数据，当我们想要判断某个键值是否在Redis中存在的时候，我们会通过这个过滤器去判断该键值是否已经存在于Redis，如果判断存在，则可以去Redis上查找。不过Bloom Filter是一种有损的过滤器，判定某个键值存在可能会有误判，不过误判比例比较低，因此如果误判了，那么就去数据库中取一次就可以了。

数据库中的Bloom Index也是类似的，通过Bloom Filter给十几个字段建一个Bloom索引，那么今后所有的这些字段的组合查询就都可以通过这个索引了。PostgreSQL 9.6开始引入了这个索引，不过Bloom 索引不是PG的标准索引，是通过一个Extension来提供的。所以如果要使用Bloom Index，我们必须先创建这个Extension。

CREATE EXTENSION bloom;

如果创建了这个Extension，那么我们就可以去创建一个Bloom Index了。下面的例子是我从BenchMark里拷贝了bmsql_oorder表生成的test_bloom表。

大家可以看见，我已经在表上创建了一个Bloom索引，包含了这张表中的大多数字段。建索引的语句如下：

CREATE INDEX idx_bloom ON test_bloom USING bloom(o_w_id, o_d_id, o_id, o_c_id, o_carrier_id, o_ol_cnt, o_all_local)

WITH (length=80 ,col1=2, col2=2, col3=2, col4=2, col5=2,col6=2, col7=2 );

我们可以看下这个索引的大小：

1200多万条记录，7个字段的索引，大小是188M，索引不算大。下面我们找几条SQL来试一试

可以看出上面的Sql走了一个Bitmap Index Scan，花了44.589毫秒完成了执行。

我们再用三个字段的组合条件，执行计划仍然是扫描这个索引，执行时间也差不多。看样子对于这样的应用场景，Bloom索引是有效的。是不是有些小兴奋，似乎Bloom索引是一个全能型的选手。其实也不尽然，一方面，Bloom过滤器是一种通过Hash函数定位的位图过滤器，只支持“=”操作，无法做范围检索，使其应用场景受到一定的限制。另外就是，如果一个索引上的字段数量太多，那么Bloom过滤器这种有损过滤器的效率也会受到影响。因为通过Bloom索引没有找到的行，肯定不符合条件，但是找到的行不一定就是符合条件的。因此索引扫描后还有一个过滤裁剪的步骤。如果裁剪的数量太多，那么SQL的执行效率就会大幅下降。

大家回过头来再去看刚才那条索引创建语句的语法，WITH后面有一组参数，其中length是签名的长度，也就是位图的长度，这个参数必须是16的倍数。后面的参数是每个列的位图投影的数量，我们可以简单的理解位某个字段在位图上被投影为几个点，每个点是由一个独立的HASH计算器产生的。只有当某个键值的这些投影点都是1，才说明这个键值在这个bloom集合中存在。

PG的手册中并没有十分明确的告诉我们该如何设置这些参数，只是说使用缺省值对大多数情况够用。如果错判太多，就说明length不足，需要加大，或者某个字段的位图数量太少。另外一个规则是针对某个字段的位图数的，选择性较强的字段可以使用较少的位图数量。根据这些规则我们可以大体上确定这些参数的设置，不过这种设置并不精确。

对于某些需要设置较为精确的参数的场景，那么我们就需要去学习Bloom Filter的数学原理，并通过数学推导来找到最佳的设置了。虽然需要如此精准计算的场景并不常见，但是在某些特殊的应用场景下，还是会需要的。尽可能及保障性能又不用太大的LENGTH和count，可以有效的减少索引的大小，同时又可以确保性能不受太大影响。

上面的例子是，我们用32字节的length重建了索引，所以大小从188M减少到了117M，不过查询语句的执行时间从45毫秒左右上上到了114毫秒，慢了整整一倍。

我们可以通过不断地尝试来获得这些参数地最佳值，不过这并不是最好的办法，如果我们了解Bloom Filter的数学原理，那么我们可以通过所需创建索引的表的基本信息去计算出这些参数来了。

Bloom索引是通过将一个称为签名的小型 Bloom过滤器与表的每一行相关联来构建的。对于每个索引列值，根据列号及其通过哈希函数传递的值计算出位数组，然后将这些签名汇总作为磁盘索引进行管理。当用索引进行搜索时，建立查询中提供的列对应的部分签名，然后过滤掉并检查签名中包含这些位的所有行。从这些原理来看，对于Bloom索引只有一种访问方式，就是位图扫描，因此只有当索引大小明显小于表大小时，这个索引才有价值否则扫描索引就不划算了，这意味着length必须明显小于行的长度。关于如何去推导最为适合的参数，以我这点数学基础，就不在这里献丑了。实际上这些成本之间的计算是十分复杂，的甚至还要考虑HDD/SDD存储之间的扫描成本差异。关于这个问题的详细数学推导过程可以参考Fabien Coelho大师的博客：Choosing PostgreSQL Bloom Index Parameters。对于数学不好的人，我们这里直接给出最终的推导公式：