PostgreSQL长事务与失效的索引查询浅析介绍
最近刚写了一篇文章介绍了下长事务，和一些长事务常见的危害，如没法及时的垃圾回收致使表膨胀之类的问题，最近恰好又碰到一个问题也是长事务所致使的。
上周六早上接到同事电话，说某个库CPU一直很高，看了下全是某张大表的全表扫描致使，但是奇怪的是相关的查询都有用到索引列，不知道为啥查询全部都没走索引。
当我连上去查看时发现确切如此，如果只是某个查询不走索引那多是SQL本身写的有问题，但是这张表相关的所有SQL都不走索引，那自然会想到是索引本身的缘由了。那是不是是索引失效了呢？经过检查发现这张表上的索引状态均正常，并且我还将索引重建了，可依然没起作用。
正当我迷茫的时候，偶然间再去履行相关SQL的时候发现居然又都走索引了，这又是啥情况。。
我啥都没做你就自己恢复了，那不是显得我很呆？不行，一定要得弄清楚啥缘由。
当我再去查看相关索引的时候发现，该索引的pg_index中的indcheckxmin列均为true，这个字段我之前有写过一篇索引失效的文章里介绍过。那末甚么情况下索引的该属性会被设置为true呢？两种情况：
- 当前事务中表上存在broken HOT chains；
- 当old_snapshot_threshold被设置时。
之前我们也介绍过，如果索引的该属性为true那末在创建索引的事务中该索引是不可用的，不过这类场景我们基本不太会遇到，由于在实际利用中我们基本不会在事务中创建完索引然后不提交该事务直接去使用。
而关于indcheckxmin的详细解释是：直到此pg_index行的xmin低于查询的TransactionXmin之前，查询都不能使用此索引。那末甚么情况下会出现这类问题呢？长事务！
当我们创建索引的时候如果索引的indcheckxmin被设置为true，且数据库中此时存在长事务，那末直到该长事务提交前，该索引会一直不可用。
下面我们来摹拟这类情况：
–会话一：打开一个长事务
BEGIN
bill=*# delete from t;
DELETE 1000
bill=*#
–会话二：创建索引
由于old_snapshot_threshold参数被设置，所以创建的索引indcheckxmin被设置为true了。
old_snapshot_threshold
————————
1h
(1 row)
bill=# create index idx_t1 on t1(id);
CREATE INDEX
bill=# select indisvalid,indcheckxmin,indisready,indislive from pg_index where indexrelid = ‘idx_t1’::regclass;
indisvalid | indcheckxmin | indisready | indislive
————+————–+————+———–
t | t | t | t
(1 row)
使用该索引列进行查询：
索引的确没法使用。
QUERY PLAN
————————————————————————————————-
Seq Scan on t1 (cost=0.00..209.00 rows=51 width=37) (actual time=0.010..0.692 rows=51 loops=1)
Filter: (id = 100)
Rows Removed by Filter: 9949
Planning Time: 0.150 ms
Execution Time: 0.706 ms
(5 rows)
bill=# set enable_seqscan = off;
SET
bill=# explain analyze select * from t1 where id = 100;
QUERY PLAN
——————————————————————————————————————-
Seq Scan on t1 (cost=10000000000.00..10000000209.00 rows=51 width=37) (actual time=0.063..0.732 rows=51 loops=1)
Filter: (id = 100)
Rows Removed by Filter: 9949
Planning Time: 0.089 ms
Execution Time: 0.796 ms
(5 rows)
提交该长事务后再次查询：
索引变得可用了。
QUERY PLAN
—————————————————————————————————————
Index Scan using idx_t1 on t1 (cost=0.29..54.48 rows=51 width=37) (actual time=0.013..0.052 rows=51 loops=1)
Index Cond: (id = 100)
Planning Time: 0.061 ms
Execution Time: 0.067 ms
(4 rows)
果然是长事务的坑啊！
由于我们的库中基本都打开了old_snapshot_threshold参数，致使创建的索引的indcheckxmin一定是true。但这其实其实不会有甚么太大影响，问题在于在创建索引的同时数据库中存在长事务，这就致使了索引在创建完以后第一时间变得不可用了，需要直到该长事务被提交后才可用。
后来和同事求证发现他们之前也常常碰到这类CPU变高然后又自己降下来的情况，之前并没有注意是这张表的全表扫描致使的。由于该表是由pg_pathman创建的自动分区，每天都会自己去创建一个新的分区，因此如果每天自动创建分区的时候存在长事务，那末创建完以后相关的分区上的索引均是不可用的，这也是为何查询不走索引然后又自己恢复的缘由了。
这个案例其实我们在实际生产中遇到的可能性或者很大的，如果你的数据库打开了old_snapshot_threshold参数，同时没有做好长事务的监控，那末创建的索引就会出现这类不可用的情况。长事务危害不浅啊！
到此这篇关于PostgreSQL长事务与失效的索引查询浅析介绍的文章就介绍到这了,更多相关PostgreSQL长事务内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持！
文章来源：丸子建站
文章标题：PostgreSQL长事务与失效的索引查询浅析介绍
https://www.wanzijz.com/view/68868.html
