日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學無先后，達者為師

網站首頁 編程語言 正文

PostgreSQL長事務與失效的索引查詢淺析介紹_PostgreSQL

作者：foucus、 ? 更新時間： 2022-11-08 編程語言

最近剛寫了一篇文章介紹了下長事務，以及一些長事務常見的危害，如無法及時的垃圾回收導致表膨脹之類的問題，最近剛好又碰到一個問題也是長事務所導致的。

上周六早上接到同事電話，說某個庫CPU一直很高，看了下全是某張大表的全表掃描導致，但是奇怪的是相關的查詢都有用到索引列，不知道為啥查詢全部都沒走索引。

當我連上去查看時發現確實如此，如果只是某個查詢不走索引那可能是SQL本身寫的有問題，但是這張表相關的所有SQL都不走索引，那自然會想到是索引本身的原因了。那是不是索引失效了呢？經過檢查發現這張表上的索引狀態均正常，并且我還將索引重建了，可仍然沒起作用。

正當我迷茫的時候，偶然間再去執行相關SQL的時候發現竟然又都走索引了，這又是啥情況。。

我啥都沒做你就自己恢復了，那不是顯得我很呆？不行，必須得搞清楚啥原因。

當我再去查看相關索引的時候發現，該索引的pg_index中的indcheckxmin列均為true，這個字段我之前有寫過一篇索引失效的文章里介紹過。那么什么情況下索引的該屬性會被設置為true呢？兩種情況：

  1. 當前事務中表上存在broken HOT chains；
  2. 當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參數，同時沒有做好長事務的監控，那么創建的索引就會出現這種不可用的情況。長事務危害不淺啊！

原文鏈接：https://foucus.blog.csdn.net/article/details/123345923

欄目分類
最近更新