詳解如何定位postgreSQL數(shù)據(jù)庫中未被使用過的索引

更新時間：2024年03月20日 08:50:12 作者：云貝貝貝

在生產(chǎn)環(huán)境上,由于不規(guī)范的優(yōu)化措施,數(shù)據(jù)庫中可能存在大量的索引,并且相當一部分的索引重未被使用過,今天帶大家如何找出這些索引,本文給大家介紹了定位postgreSQL數(shù)據(jù)庫中未被使用過的索引的方法,需要的朋友可以參考下

前言

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

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

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

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

2. 占用存儲空間：索引本身會占用存儲空間。如果有大量未使用的索引，這些索引會浪費大量的磁盤空間，可能導致存儲成本增加。

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

4. 備份和恢復時間增加：如果有大量未使用的索引，備份和恢復數(shù)據(jù)庫的時間可能會增加，因為索引數(shù)據(jù)也需要被備份和恢復。

因此，定期審查和清理未使用的索引是一個很好的數(shù)據(jù)庫維護實踐。但在刪除索引之前，需要確保這些索引真的不再需要，因為有些索引可能只在特定的查詢或特定的時間（如每月的報表生成）中被使用。

二、實戰(zhàn)案例

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

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

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

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

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

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

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

2.5 查看此時表的體積

因為索引的原因，t1的體積更大。

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

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

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

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

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

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

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

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

最后，要注意的是，查詢優(yōu)化器的目標是盡可能快地返回查詢結果，而不一定是盡可能少地讀取磁盤頁面。因此，有時候即使存在索引，查詢優(yōu)化器也可能選擇全表掃描，因為它認為這樣會更快。