PG中的索引失效总结

0    116    1

Tags:

👉 本文共约6688个字,系统预计阅读时间或需26分钟。

前言

按照大的分类,索引失效又可以细分为两类:

  1. 建的索引无法使用,不能用
  2. 优化器不选择走索引,不想用

图片

Either it can’t use the index, or it doesn’t think using the index will be faster.

图片

why.png

不能用

那么哪些情况下会导致索引无法使用?整理了以下几个原因 👇🏻

索引类型不匹配

首先最常见的情形便是索引的类型不匹配,比如

两列上各有一个索引,假如过滤条件带有函数,那么索引便无法使用

一个可行的方式是建一个函数索引,不过函数索引要求对应的函数必须是 immutable 的,可以选择自己套一层,函数三态自行了解。

与函数索引类似的是,过滤条件(谓词)必须位于右侧,否则也会导致索引失效

可以看到虽然这三条SQL的逻辑和结果集一模一样,但是第三条SQL就无法走索引,而第二条SQL优化器则自动做了转化——常量折叠,这个在之前的优化器篇章里面有介绍过优化器逻辑推理,此处不再过多解释。

collate不一致

当我们在使用 initdb 初始化数据库时提供了一个叫做“本地化”的参数 locale,不幸的是,locale与encoding的默认配置取决于操作系统的配置,因此假如不指定的话会沿用操作系统的配置。locale是根据计算机用户所使用的语言,所在国家或者地区,以及当地的文化传统所定义的一个软件运行时的语言环境。locale把按照所涉及到的文化传统的各个方面分成12个大类,这12个大类分别是:

  1. 语言符号及其分类(LC_CTYPE)
  2. 数字(LC_NUMERIC)
  3. 比较和排序习惯(LC_COLLATE)
  4. 时间显示格式(LC_TIME)
  5. 货币单位(LC_MONETARY)
  6. ...

所以说,locale就是某一个地域内的人们的语言习惯和文化传统和生活习惯。本地化设置对以下SQL特性有影响:

  1. 排序和比较操作 : Sort order in queries using ORDER BY or the standard comparison operators on textual data
  2. 内置函数 : The upper, lower, and initcap functions
  3. 模式匹配 : Pattern matching operators (LIKE, SIMILAR TO, and POSIX-style regular expressions); locales affect both case insensitive matching and the classification of characters by character-class regular expressions
  4. to_char相关函数 : The to_char family of functions
  5. LIKE能否使用索引 : The ability to use indexes with LIKE clauses

其中 collate 是最容易被忽略的一点,简而言之,collate会影响到order by语句的顺序,会影响到where条件中大于小于号筛选出来的结果等,比如下面我这个实例,encoding是UTF8,collate是en_US.UTF-8 👇🏻

那么影响何如?看个栗子

可以看到不同的collate会影响字符的排序比较,另外就是索引失效了

因为locale关于字符串的等价规则有一套自己的定义,比如在MySQL里面,德语字母“ß”,在utf8mb4_unicode_ci中是等价于”ss”两个字母的,而在utf8mb4_general_ci中,它却和字母“s”等价。因此只有最朴素的数据库自身提供的C LOCALE,才能够正常地进行模式匹配。"C" LOCALE的比较规则非常简单,就是挨个比较字符码位。

当然还有一种方式就是指定额外的操作符了,不过这会增加额外的维护成本,也意味着原来主键/唯一约束自带的索引无法使用

因此建议在初始化的时候就直接指定 C,目前还没有后悔药,无法直接通过 alter database 的语法进行修改,只能采用导出再导入的方式。

数据类型不一致

这个很好理解,column类型和过滤条件类型不一致

第三条SQL更加干脆,直接报错,那么int与text类型比较,为何会报错?在示例中,‘=’为二元操作符,左操作数类型为int,右操作数类型为text,在pg_operator系统目录中,不存在该operator和相应操作数类型的定义:

而且int和text并没有在pg_cast系统目录中定义为可相互转换,这种情况可以考虑自己创建一个cast转换。

old_snapshot_threshold作祟

假如有一直关注的小伙伴,应该对这个参数十分了解,真是又爱又恨呀,不熟悉的可以参照之前的文章《生产案例 | 费解的索引失效》,看个例子(此处开启了old_snapshot_threshold参数)

然后新开一个会话,使其获取一个事务ID

然后第一个会话创建索引,可以看到即使设置了disable cost,也无法走索引

因为此时数据库认为这个索引是 unsafe 的,不安全,所以无法使用该索引

If true, queries must not use the index until the xmin of this pg_index row is below their TransactionXmin event horizon, because the table may contain broken HOT chains with incompatible rows that they can see

如果为真,直到此pg_index行的xmin低于查询的TransactionXmin视界之前,查询都不能使用此索引,因为表可能包含具有它们可见的不相容行的损坏HOT链。

当然解决办法也很简单,第一个会话提交之后即可正常使用

broken hot chain

让我们回过头来再看一下设置indcheckxmin的逻辑,这里截取部分代码

可以看到,源码注释里,我们可以得出如下一些结论

  1. 如果我们发现任何可能损坏的HOT链,就将索引标记为不可用,直到当前事务低于视界范围。
  2. 如果为堆表启用了早期修剪/清理,也会设置此项。
  3. 在concurrent create index的时候也不需要设置indcheckxmin,因为有indisvalid控制

而EarlyPruningEnabled,则很清晰了,判断old_snapshot_threshold >= 0,同时判断是否是普通表(即unlogged、临时表)、是否是系统表等。

那么到这,我们可以有一些系统性的结论了:

  1. 开启了old_snapshot_threshold之后,会设置indcheckxmin为true
  2. 在CIC的时候,不需要设置indcheckxmin,但是也要控制长事务,即使不是同一个对象,也会阻塞索引的创建,可以参考之前的文章,需要预防HOT unsafe的问题
  3. 存在HOT Broken chain的时候,会设置indcheckxmin为true
  4. reindex的时候,不需要设置indcheckxmin

那什么是broken hot chain?在HOT的README里面有解释:

Broken HOT Chain:A HOT chain in which the key value for an index has changed.This is not allowed to occur normally but if a new index is created it can happen. In that case various strategies are used to ensure that no transaction for which the older tuples are visible can use the index.

断开的HOT链: 其中索引的键值已更改的HOT链。这是不允许正常发生的,但是如果创建了一个新的索引,它就会发生。在这种情况下,将使用各种策略来确保不会有可见旧元组的事务使用索引。

可以看到,发生了 HOT 更新之后,索引在事务内也是无法使用的。

条件不支持

每种索引有自己特定的场景,也有不支持的操作符,这一点我们可以通过查询系统表来判断

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

通过以上查询,可以知道 GIN 索引不支持排序、唯一、排除约束,也不支持大于小于等,因此假如有这些查询,GIN 索引当然会失效了,臣妾做不到啊。

NameDescription
clusterableCan the index be used in a CLUSTER command?
index_scanDoes the index support plain (non-bitmap) scans?
bitmap_scanDoes the index support bitmap scans?
backward_scanCan the scan direction be changed in mid-scan (to support FETCH BACKWARD on a cursor without needing materialization)?
NameDescription
ascDoes the column sort in ascending order on a forward scan?
descDoes the column sort in descending order on a forward scan?
nulls_firstDoes the column sort with nulls first on a forward scan?
nulls_lastDoes the column sort with nulls last on a forward scan?
orderableDoes the column possess any defined sort ordering?
distance_orderableCan the column be scanned in order by a “distance” operator, for example ORDER BY col <-> constant ?
returnableCan the column value be returned by an index-only scan?
search_arrayDoes the column natively support col = ANY(array) searches?
search_nullsDoes the column support IS NULL and IS NOT NULL searches?

不想用

不想用,顾名思义,优化器认为走索引还不如走顺序扫描来得快,因为索引扫描对应的是离散IO,我们可以通过调整 random_page_cost 以告诉优化器,随机IO和顺序IO的比值。看几个例子:

表太小

这个是开发经常容易犯的错误,为什么我建了索引不走呢?还是那句话,优化器认为索引太慢了!

可以看到索引的总成本8.16要比顺序扫描2.25高了4倍左右,优化器当然选择走顺序扫描了。

关联度

参照此图 👆🏻,高效的索引扫描 (绿色部分),只需扫描几次 (traverse) 就能获取到所需数据,而糟糕的索引扫描往往需要返回大量数据,如图中红色部分,需要多次扫描,每次都要经历树根 → 树干 → 树枝 → 树叶,导致大量的离散IO,因此优化器也会将这个关联度考虑进去。

好的顺序扫描数据更加紧凑,都集中在一块,这样磁头扫过去,只需扫描有限个数据块,就可以获取到大部分所需数据。而糟糕的顺序扫描则数据相对离散,需要扫描大量数据并过滤才能获取到想要的数据,这个也就是我在统计信息里面提到的correlation字段的作用,表示列的物理顺序和逻辑顺序的相关性,相关性越高,走索引扫描的离散块扫描更少,走索引扫描的离散块扫描代价越低。还是老例子,假如现在有一个表如下

表上有三个索引,其中

  1. col_asc索引是顺序递增的
  2. col_desc索引是倒序递减的
  3. col_rand索引则是随机分布的

因此假如有个SQL需要查询2到4的数据 SELECT * FROM tbl_corr WHERE col_asc BETWEEN 2 AND 4;,对于col_asc,只需要读第一个页面即可

图片

而假如是对随机列进行查询的话,SELECT * FROM tbl_corr WHERE col_rand BETWEEN 2 AND 4;则需要读取所有的页面

图片

因此在索引扫描的时候也会将correlation考虑进去 'table IO cost'=max_IO_cost+indexCorrelation2×(min_IO_cost−max_IO_cost).,更多细节请参考 https://www.interdb.jp/pg/pgsql03.html

This way, the index correlation is a statistical correlation that reflects the influence of random access caused by the twist between the index ordering and the physical tuple ordering in the table in estimating the index scan cost.

在PostgreSQL中,我们可以使用cluster命令进行聚簇,对于某些时序类的数据进行范围查询,会有性能提升。

返回的数据集过大

这个也是类似的原理,索引会对应大量的离散IO,选择率这个词想必各位也耳熟能详,就是这个道理。

最左原则

索引的最左原则?非也非也,看个栗子👇🏻

可以看到,我创建了一个复合索引,假设是(a,b,c)的复合索引,那么会先按照a列排序存储,接着按照b列,最后是c列,因此假如SQL是直接查询的b或者c,基本也需要访问整棵索引树了。

另外不是所有这种例子都不会选择走索引,我曾经看过greenplum的一篇文章,让我的印象十分深刻,👉🏻 《巧用复合索引,优化查询性能》,究其原因,也还是成本cost的比较,让优化器选择了复合索引。

由查询计划可以看到,尽管id并不是索引(score,class,id)的某一个前缀,但是依然可以使用到这个复合索引。笔者早年在Mysql上具有多年工作经验,深知Mysql索引的最左前缀原则,因此对于Greenplum中的这个索引优化效果非常惊奇,那是不是查询计划有误呢?我们来对比一下这个查询分别使用顺序扫描和索引扫描的实际运行时间...

冗余索引

顾名思义,存在同类型功能相同的索引,我们可以使用如下SQL检查:

更多SQL可以参考wiki:https://wiki.postgresql.org/wiki/Index_Maintenance

优化器刺客

没错,还是limit!俗称优化器刺客,这是一个很 tricky 的问题,你可能会发现比如像 select * from abc where col_1 = 5 limit 1; 没有走索引,但是却发现 select * from abc where col_1 = 5;使用了索引,当优化器认为 pg_stats 中 col_1 列在表中有足够多的随机值时,就会发生这种情况,因为优化器认为从数据库中获取所需数量的连续块的总成本将小于先获取索引块然后再获取相应的数据块的成本,优化器认为数据分布均匀,只需扫描有限数据即可获得所需数据,中止继续扫描,但是一旦数据分布不均,全部挤在了一坨,就会很糟糕,最糟糕的情况莫过于需要扫描大量的数据。

关于limit导致问题的案例太多了,还是那个来自ken师傅的经典案例:一个有趣的SQL优化案例,优化器刺客。

糟糕的统计信息

顾名思义,过时的、糟糕的统计信息会让优化器产生误判,甚至没有统计信息,比如修改字段长度、类型后,需要做analyze手动收集统计信息,不然会选择默认选择率。

小结

知其然,知其所以然。对于一条SQL,优化器判断能否走索引是需要经过多方面权衡和比较的,另外对于broken hot chain和old_snapshot_threshold,多加小心长事务!

参考

https://github.com/digoal/blog/blob/master/202211/20221111_02.md

https://www.gojek.io/blog/the-case-s-of-postgres-not-using-index

https://www.pgmustard.com/blog/why-isnt-postgres-using-my-index

https://mp.weixin.qq.com/s/A6SZTiSME7QGZwxPLCLpEw

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

5 × 1 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部