Postgresql INDEX HOT 原理與更好的 “玩轉” INDEX

隨着問問題的同學越來越多，公衆號內部私信回答問題已經很困難了，所以建立了一個羣，關於各種數據庫的問題都可以，目前主要是 POSTGRESQL, MYSQL ,MONGODB ,POLARDB ,REDIS，SQL SERVER 等，期待你的加入，加羣請添加微信 liuaustin3.  （羣裏有各方面的工作人員和專家）

好長時間不進行研究了，最近被突發的問題想到了 INDEX 的問題，隨機想到數據和 INDEX 存儲在一起會怎樣，我們將索引和數據進行分離後，會不會對數據庫的性能有優化的可能。

所以讓我想到了 HOT  heap only tuples 這個事情，但是在是記不清了，所以就的翻翻舊賬了。

首先 HOT ，heap only tuples 是 Postgres 用戶用於減少基於 UPDATE 後的大量的 IO 所做的工作，主要的問題就是在 MVCC 導致的 UPDATE 等於 INSERT + 廢棄行，以及新插入的行都需要對當前的索引負責。

相對於表本身需要 VACUUM 和 AUTOVACUUM 的情況下，我們的其實需要更多的 I/O 工作在針對這些操作針對索引的問題的 IO 消耗，因爲索引需要修改指針到新的行。

Postgres 爲了降低指針重新指向的問題，提出在一個行 UPDATE 後，就在原有的位置上插入他的新的版本的行，通過這樣的方式讓索引知道新的行就在老得行的下一個位置，避免大量的更新索引的操作，使用這樣的方式就可以在索引上直接指向原來的位置的下一個位置。

而要完成這個事情，需要一個特殊的條件就是，更新的列不能是當前的索引列。

下面是經典的兩個圖 ，1 如果沒有 HOT 的情況下   2 使用 HOT 的情況

所以結論是 POSTGRESQL 在頻繁的 UPDATE 當中，如果更新的字段是索引的情況下，將引發大量的索引更新，引起 IO 的消耗的情況。

在 POSTGRESQL 有這樣的問題的情況下，我們需要針對 POSTGRESQL 的索引更加的小心和謹慎。

所以我們需要注意以下的問題

1  unused indexes   無用的索引

2  bloated indexes   膨脹的索引

3  Duplicate and invalid index   重複的索引

爲什麼會產生以上的這些問題呢

1   添加索引是在業務確認之前添加的，也就是添加索引並不是完全確認了業務的情況下進行的。

2   添加的索引針對的業務下線了

3   服務器的資源提升了，增加了，暫時不使用索引可以達到更好的

4   業務發展，後期添加的索引替代了早期的索引

5   操作失誤，建立了同樣的索引

那麼針對以上的問題，我們需要

1  找到無用的索引

SELECT s.schemaname,

s.relname AS tablename,

s.indexrelname AS indexname,

pg_relation_size(s.indexrelid) AS index_size

FROM pg_catalog.pg_stat_user_indexes s

JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid

WHERE s.idx_scan = 0      

AND 0 <>ALL (i.indkey)  

AND NOT i.indisunique   

AND NOT EXISTS         

(SELECT 1 FROM pg_catalog.pg_constraint c

WHERE c.conindid = s.indexrelid)

ORDER BY pg_relation_size(s.indexrelid) DESC;

這裏的無用的索引的問題，在通過語句找出相關得信息，只能作爲一個借鑑的值，而不是一個可以完全借鑑的值。

得到這些信息，只能是還需要更多的分析，才能將這些索引清理掉。

2  索引的碎片率的問題，導致索引的性能的問題 ，基於 POSTGRESQL MVCC 以及相關的問題，導致表膨脹，這樣的情況下，也會導致索引碎片的問題，所以發現並重建索引是一個需要注意的問題。

create extension pgstattuple;

SELECT i.indexrelid::regclass,

       s.leaf_fragmentation

FROM pg_index AS i

   JOIN pg_class AS t ON i.indexrelid = t.oid

   JOIN pg_opclass AS opc ON i.indclass[0] = opc.oid

   JOIN pg_am ON opc.opcmethod = pg_am.oid

   CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s

WHERE t.relkind = 'i'

  AND pg_am.amname = 'btree' and s.leaf_fragmentation > 0 and s.leaf_fragmentation <> 'NaN';

通過上面的語句去查詢你索引的碎片率，通過這個來決定你的是否要進行索引的重建的工作。

3  重複索引的問題

基於上面的問題，索引不使用另外一種可能是有同類的索引，所以在發現索引不被使用的情況下，可以先看看是否有重複的索引的原因引起的，重複索引的害處可謂是 “罄竹難書”

1  衆所周知的重複索引，引起插入效率低

2  重複索引導致的數據量加大的問題

3  進行 VACUUM AUTOVACUUM 多餘的重複索引導致的操作時間和資源消耗過大的問題。

所以重複索引的問題一定要將多餘的索引清理出去

SELECT   indrelid::regclass table_name,

         att.attname column_name,

         amname index_method

FROM     pg_index i,

         pg_class c,

         pg_opclass o,

         pg_am a,

         pg_attribute att

WHERE    o.oid = ALL (indclass) 

AND      att.attnum = ANY(i.indkey)

AND      a.oid = o.opcmethod

AND      att.attrelid = c.oid

AND      c.oid = i.indrelid

GROUP BY table_name, 

         att.attname,

         indclass,

         amname, indkey

HAVING count(*) > 1;

SELECT    indrelid::regclass AS TableName    ,array_agg(indexrelid::regclass) AS Indexes 

FROM pg_index 

GROUP BY    indrelid    ,indkey 

HAVING COUNT(*) > 1;

通過上面的語句來查看當前的數據庫中是否有重複的索引。

除此以外，我們還可以針對索引做如下的一些工作

1 在 Postgresql 中創建針對索引的表空間，數據和索引進行分離，而不要將索引和數據創建在一個數據文件內。

postgres=# create tablespace index_storage location '/pgdata/index';

CREATE TABLESPACE

postgres=# create index idx_user_name on user_ini(user_name) tablespace index_storage;

CREATE INDEX

postgres=# 

2  針對當前的索引進行查詢和分析

1 針對當前有多少索引進行信息的獲取

SELECT CONCAT(n.nspname,'.', c.relname) AS table,i.relname AS index_name,x.indisunique as is_unique FROM pg_class c

INNER JOIN pg_index x ON c.oid = x.indrelid

INNER JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind = ANY (ARRAY['r', 't']) and c.relname not like 'pg%';

2  這對當前的索引的大小進行分析

SELECT 

    relname AS objectname,

    relkind AS objecttype,

    reltuples :: bigint AS "rows",

    pg_size_pretty(relpages::bigint81024) AS size

FROM pg_catalog.pg_class where relkind = 'i'

ORDER BY relpages DESC;

3 針對索引使用的次數進行統計，如每天索引被使用多少次，如果索引組最近一段時間使用的頻次明顯比之前要少，或者根本就不使用了，就需要分析有沒有可能是因爲索引損壞造成的問題。

SELECT s.relname AS table_name,

       indexrelname AS index_name,

       i.indisunique,

       idx_scan AS index_scans

FROM   pg_catalog.pg_stat_user_indexes s,

       pg_index i

WHERE  i.indexrelid = s.indexrelid;

另外，在索引的工作中，還有一些問題基於索引的損壞導致的問題，會發現如下的一些問題

1  本來有索引但是在查詢中不走索引而是走全表掃描

2  通過 pg_stat_user_tables 表中的 seq_scan  和 idx_scan  兩個字段的數值的對比來發現問題，如 seq_scan 瘋狂的增加數字，而 idx_scan 裏面不增長或增長很慢，（1 是否有對應的索引  2 索引是否損壞）

3  在查詢中出現錯誤的數據，如查詢範圍的明顯標定的很清楚，但是查詢的數據突破了這個範圍，也就是查詢的值不對。

以上的方式也可能是其他問題造成的，如數據庫表的 analyze 操作不及時，導致統計分析的數據出現偏差造成的。

基於以上的一些內容，索引的維護和信息的收集，以及問題的發現對於索引的維護是非常重要的。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/6CBdHy5Buxva9vAoaZ5_aQ