欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

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

前言

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

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

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

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

2. 占用存儲空間:索引本身會占用存儲空間。如果有大量未使用的索引,這些索引會浪費大量的磁盤空間,可能導(dǎo)致存儲成本增加。

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

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

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

二、實戰(zhàn)案例

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

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 查看此時表的體積

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

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

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

1. 查詢的選擇性:索引對于高選擇性的查詢最有用。選擇性是指查詢結(jié)果返回的記錄占表中總記錄的比例。例如,如果你正在查詢一個人口為幾十億的國家中的一小部分人,這個查詢就有很高的選擇性,使用索引會很有幫助。相反,如果你正在查詢的數(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)計信息不準(zhǔn)確,查詢優(yōu)化器可能會做出錯誤的決策。

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

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

在pgv16的版本中,有一個視圖pg_stat_user_indexes可以查看索引使用情況

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

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

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

那加上analyze參數(shù)

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

五、總結(jié)

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

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

相關(guān)文章

  • PostgreSQL12.5中分區(qū)表的一些操作實例

    PostgreSQL12.5中分區(qū)表的一些操作實例

    PostgreSQL支持通過表繼承進行分區(qū),下面這篇文章主要給大家介紹了關(guān)于PostgreSQL12.5中分區(qū)表的一些操作的相關(guān)資料,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2022-08-08
  • PostgreSQL 默認(rèn)權(quán)限查看方式

    PostgreSQL 默認(rèn)權(quán)限查看方式

    這篇文章主要介紹了PostgreSQL 默認(rèn)權(quán)限查看方式,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • sqoop 實現(xiàn)將postgresql表導(dǎo)入hive表

    sqoop 實現(xiàn)將postgresql表導(dǎo)入hive表

    這篇文章主要介紹了sqoop 實現(xiàn)將postgresql表導(dǎo)入hive表,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2020-12-12
  • PostgreSQL 實現(xiàn)定時job執(zhí)行(pgAgent)

    PostgreSQL 實現(xiàn)定時job執(zhí)行(pgAgent)

    這篇文章主要介紹了PostgreSQL 實現(xiàn)定時job執(zhí)行(pgAgent),具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • postgresql 將逗號分隔的字符串轉(zhuǎn)為多行的實例

    postgresql 將逗號分隔的字符串轉(zhuǎn)為多行的實例

    這篇文章主要介紹了postgresql 將逗號分隔的字符串轉(zhuǎn)為多行的實例,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-02-02
  • PostgreSQL?16?新特性之正態(tài)分布隨機數(shù)函數(shù)的示例

    PostgreSQL?16?新特性之正態(tài)分布隨機數(shù)函數(shù)的示例

    這篇文章主要介紹了PostgreSQL?16?新特性之正態(tài)分布隨機數(shù)函數(shù),PostgreSQL 16 新增了一個內(nèi)置的 random_normal() 函數(shù),用于生成這種隨機數(shù),通過示例代碼給大家介紹的非常詳細(xì),需要的朋友可以參考下
    2023-01-01
  • Docker環(huán)境下升級PostgreSQL的步驟方法詳解

    Docker環(huán)境下升級PostgreSQL的步驟方法詳解

    這篇文章主要介紹了Docker環(huán)境下升級PostgreSQL的步驟方法詳解,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2021-01-01
  • PostgreSQL 對數(shù)組的遍歷操作

    PostgreSQL 對數(shù)組的遍歷操作

    這篇文章主要介紹了PostgreSQL 對數(shù)組的遍歷操作,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • PostgreSQL 日志文件的所在位置

    PostgreSQL 日志文件的所在位置

    這篇文章主要介紹了PostgreSQL 日志文件的所在位置,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • PostgreSQL數(shù)據(jù)庫中Sequence的使用方法詳解

    PostgreSQL數(shù)據(jù)庫中Sequence的使用方法詳解

    在 PostgreSQL 數(shù)據(jù)庫中,Sequence 是一種特殊的表對象,主要用于生成按順序遞增或遞減的數(shù)字序列,通常用于需要唯一標(biāo)識符的場景,例如自增 ID,以下是如何在 PostgreSQL 中使用 Sequence 的詳細(xì)步驟,需要的朋友可以參考下
    2024-11-11

最新評論