PostgreSQL长事务与失效的索引查询浅析介绍-数据库

pg中的长事务会影响表中垃圾回收，导致表的年龄增长无法freeze。能消耗事务的只有当执行了一些DML或者DDL操作后才能算是我们通常说的长事务。否则只能算是我们常说的长连接，当然长连接也有很多弊端，例如占用内存、cpu等资源

最近刚写了一篇文章介绍了下长事务，以及一些长事务常见的危害，如无法及时的垃圾回收导致表膨胀之类的问题，最近刚好又碰到一个问题也是长事务所导致的。

上周六早上接到同事电话，说某个库CPU一直很高，看了下全是某张大表的全表扫描导致，但是奇怪的是相关的查询都有用到索引列，不知道为啥查询全部都没走索引。

当我连上去查看时发现确实如此，如果只是某个查询不走索引那可能是SQL本身写的有问题，但是这张表相关的所有SQL都不走索引，那自然会想到是索引本身的原因了。那是不是索引失效了呢？经过检查发现这张表上的索引状态均正常，并且我还将索引重建了，可仍然没起作用。

正当我迷茫的时候，偶然间再去执行相关SQL的时候发现竟然又都走索引了，这又是啥情况。。

我啥都没做你就自己恢复了，那不是显得我很呆？不行，必须得搞清楚啥原因。

当我再去查看相关索引的时候发现，该索引的pg_index中的indcheckxmin列均为true，这个字段我之前有写过一篇索引失效的文章里介绍过。那么什么情况下索引的该属性会被设置为true呢？两种情况：

当前事务中表上存在broken HOT chains；
当old_snapshot_threshold被设置时。

之前我们也介绍过，如果索引的该属性为true那么在创建索引的事务中该索引是不可用的，不过这种场景我们基本不太会遇到，因为在实际应用中我们基本不会在事务中创建完索引然后不提交该事务直接去使用。

而关于indcheckxmin的详细解释是：直到此pg_index行的xmin低于查询的TransactionXmin之前，查询都不能使用此索引。那么什么情况下会出现这种问题呢？长事务！

当我们创建索引的时候如果索引的indcheckxmin被设置为true，且数据库中此时存在长事务，那么直到该长事务提交前，该索引会一直不可用。

下面我们来模拟这种情况：

--会话一：打开一个长事务

bill=# begin;
BEGIN
bill=*# delete from t;
DELETE 1000
bill=*#

--会话二：创建索引

由于old_snapshot_threshold参数被设置，所以创建的索引indcheckxmin被设置为true了。

bill=# show old_snapshot_threshold ;
 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)

使用该索引列进行查询：

索引的确无法使用。

bill=# explain analyze select * from t1  where id = 100;
                                           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)

提交该长事务后再次查询：

索引变得可用了。

bill=# explain analyze select * from t1  where id = 100;
                                                  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长事务内容请搜索编程学习网以前的文章希望大家以后多多支持编程学习网！