前言

在生產(chǎn)環(huán)境上，由于不規(guī)范的優(yōu)化措施，數(shù)據(jù)庫中可能存在大量的索引，并且相當(dāng)一部分的索引重未被使用過，今天帶大家如何找出這些索引。

一、存在大量未使用的索引帶來的危害

在PostgreSQL中，存在大量未使用的索引可能會(huì)帶來以下幾個(gè)問題：

1. 性能下降：每當(dāng)對(duì)表進(jìn)行插入、刪除或更新操作時(shí)，相關(guān)的索引都需要被更新。如果有大量未使用的索引，這些索引的維護(hù)工作可能會(huì)消耗大量的CPU和I/O資源，導(dǎo)致數(shù)據(jù)庫性能下降。

2. 占用存儲(chǔ)空間：索引本身會(huì)占用存儲(chǔ)空間。如果有大量未使用的索引，這些索引會(huì)浪費(fèi)大量的磁盤空間，可能導(dǎo)致存儲(chǔ)成本增加。

3. 影響查詢優(yōu)化器的決策：PostgreSQL的查詢優(yōu)化器在決定查詢執(zhí)行計(jì)劃時(shí)，會(huì)考慮所有可用的索引。如果有大量未使用的索引，可能會(huì)增加查詢優(yōu)化器的計(jì)算復(fù)雜度，影響其決策的效率和準(zhǔn)確性。

4. 備份和恢復(fù)時(shí)間增加：如果有大量未使用的索引，備份和恢復(fù)數(shù)據(jù)庫的時(shí)間可能會(huì)增加，因?yàn)樗饕龜?shù)據(jù)也需要被備份和恢復(fù)。

因此，定期審查和清理未使用的索引是一個(gè)很好的數(shù)據(jù)庫維護(hù)實(shí)踐。但在刪除索引之前，需要確保這些索引真的不再需要，因?yàn)橛行┧饕赡苤辉谔囟ǖ牟樵兓蛱囟ǖ臅r(shí)間（如每月的報(bào)表生成）中被使用。

二、實(shí)戰(zhàn)案例

接下來，我們以實(shí)際案例來演示索引過多帶來的性能影響

2.1 創(chuàng)建帶索引的表

2.2 創(chuàng)建不帶索引的表

2.3 插入數(shù)據(jù)測試

上面結(jié)果可以看出，同樣結(jié)構(gòu)的表，插入數(shù)據(jù)的操作，有無索引的性能相關(guān)近5倍！

2.4 修改數(shù)據(jù)測試

上面結(jié)果可以看出，同樣結(jié)構(gòu)的表，修改數(shù)據(jù)的操作，有無索引的性能相關(guān)近3倍！

2.5 查看此時(shí)表的體積

因?yàn)樗饕脑?，t1的體積更大。

三、索引使用的規(guī)則

PostgreSQL的查詢優(yōu)化器使用一種稱為成本優(yōu)化的方法來決定是否使用索引，以及選擇使用哪個(gè)索引。以下是一些影響PostgreSQL使用索引的主要因素：

1. 查詢的選擇性：索引對(duì)于高選擇性的查詢最有用。選擇性是指查詢結(jié)果返回的記錄占表中總記錄的比例。例如，如果你正在查詢一個(gè)人口為幾十億的國家中的一小部分人，這個(gè)查詢就有很高的選擇性，使用索引會(huì)很有幫助。相反，如果你正在查詢的數(shù)據(jù)占總數(shù)據(jù)的大部分，那么全表掃描可能會(huì)更快。

2. 索引類型：PostgreSQL支持多種類型的索引，包括B-tree、Hash、GiST、SP-GiST、GIN和BRIN。每種索引類型都有其特定的用途和優(yōu)勢。例如，B-tree索引適用于等于、大于、小于等操作，而GIN索引適用于數(shù)組和全文搜索。

3. 數(shù)據(jù)分布：如果表中的數(shù)據(jù)分布不均勻，索引可能會(huì)更有效。例如，如果一個(gè)字段的值大部分都是唯一的，那么對(duì)這個(gè)字段創(chuàng)建索引可能會(huì)很有幫助。

4. 索引大小和深度：大的索引需要更多的磁盤I/O操作來讀取，而深的索引需要更多的磁盤I/O操作來遍歷。因此，如果一個(gè)索引很大或很深，查詢優(yōu)化器可能會(huì)選擇不使用它。

5. 硬件和系統(tǒng)配置：硬件的性能（如CPU速度、內(nèi)存大小、磁盤速度等）和PostgreSQL的配置（如工作內(nèi)存、隨機(jī)頁面成本等）也會(huì)影響查詢優(yōu)化器的決策。

6. 統(tǒng)計(jì)信息：PostgreSQL的查詢優(yōu)化器使用統(tǒng)計(jì)信息來估計(jì)查詢的成本。這些統(tǒng)計(jì)信息包括表的大小、索引的大小、數(shù)據(jù)的分布等。如果這些統(tǒng)計(jì)信息不準(zhǔn)確，查詢優(yōu)化器可能會(huì)做出錯(cuò)誤的決策。

最后，要注意的是，查詢優(yōu)化器的目標(biāo)是盡可能快地返回查詢結(jié)果，而不一定是盡可能少地讀取磁盤頁面。因此，有時(shí)候即使存在索引，查詢優(yōu)化器也可能選擇全表掃描，因?yàn)樗J(rèn)為這樣會(huì)更快。

四、如何找出未使用的索引

在pgv16的版本中，有一個(gè)視圖pg_stat_user_indexes可以查看索引使用情況

查看當(dāng)前索引使用的頻率

調(diào)用索引再次查看

SQL的執(zhí)行計(jì)劃中顯示調(diào)用了索引idx_a ，查詢pg_stat_user_indexes結(jié)果顯示索引的掃描次數(shù)沒有增加，為什么？原來explain并未真正執(zhí)行SQL。

那加上analyze參數(shù)

在v16之前的版本中,pg_stat_user_indexes.last_idx_scan字段是沒有的，只能通過手工編寫腳本來記錄索引掃描時(shí)間，也可以通過SELECT pg_stat_reset()函數(shù)重置索引的使用記錄，人工觀察一周來排查。這里不再贅述。

五、總結(jié)

通過以上實(shí)驗(yàn)，我們知道了表上索引過多的危害及如何定位未使用索引的方法，對(duì)pg數(shù)據(jù)庫的索引有進(jìn)一步的了解。

到此這篇關(guān)于詳解如何定位postgreSQL數(shù)據(jù)庫中未被使用過的索引的文章就介紹到這了,更多相關(guān)定位postgreSQL未使用的索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論