淺談PostgreSQL的索引

来源:http://www.cnblogs.com/lottu/archive/2017/09/15/7526700.html
-Advertisement-
Play Games

1. 索引的特性 1.1 加快條件的檢索的特性 當表數據量越來越大時查詢速度會下降，在表的條件欄位上使用索引，快速定位到可能滿足條件的記錄，不需要遍歷所有記錄。 #在這個案例中：執行同一條SQL。t2有索引的執行數據是0.052 ms；t1沒有索引的是：5.741 ms; 1.2 有序的特性 索引本 ...


1. 索引的特性

1.1 加快條件的檢索的特性

當表數據量越來越大時查詢速度會下降，在表的條件欄位上使用索引，快速定位到可能滿足條件的記錄，不需要遍歷所有記錄。

create table t(id int, info text);
insert into t select generate_series(1,10000),'lottu'||generate_series(1,10000);
create table t1 as select * from t;
create table t2 as select * from t;
create index ind_t2_id on t2(id);
lottu=# analyze t1;
ANALYZE
lottu=# analyze t2;
ANALYZE
# 沒有索引
lottu=# explain (analyze,buffers,verbose) select * from t1 where id < 10;
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Seq Scan on lottu.t1  (cost=0.00..180.00 rows=9 width=13) (actual time=0.073..5.650 rows=9 loops=1)
   Output: id, info
   Filter: (t1.id < 10)
   Rows Removed by Filter: 9991
   Buffers: shared hit=55
 Planning time: 25.904 ms
 Execution time: 5.741 ms
(7 rows)
# 有索引
lottu=# explain (analyze,verbose,buffers) select * from t2 where id < 10;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Index Scan using ind_t2_id on lottu.t2  (cost=0.29..8.44 rows=9 width=13) (actual time=0.008..0.014 rows=9 loops=1)
   Output: id, info
   Index Cond: (t2.id < 10)
   Buffers: shared hit=3
 Planning time: 0.400 ms
 Execution time: 0.052 ms
(6 rows)

#在這個案例中：執行同一條SQL。t2有索引的執行數據是0.052 ms；t1沒有索引的是：5.741 ms; 

1.2 有序的特性

索引本身就是有序的。

#沒有索引
lottu=# explain (analyze,verbose,buffers) select * from t1 where id > 2 order by id;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
Sort  (cost=844.31..869.31 rows=9999 width=13) (actual time=8.737..11.995 rows=9998 loops=1)
   Output: id, info
   Sort Key: t1.id
   Sort Method: quicksort  Memory: 853kB
   Buffers: shared hit=55
   ->  Seq Scan on lottu.t1  (cost=0.00..180.00 rows=9999 width=13) (actual time=0.038..5.133 rows=9998 loops=1)
         Output: id, info
         Filter: (t1.id > 2)
         Rows Removed by Filter: 2
         Buffers: shared hit=55
 Planning time: 0.116 ms
 Execution time: 15.205 ms
(12 rows)
 #有索引
lottu=# explain (analyze,verbose,buffers) select * from t2 where id > 2 order by id;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using ind_t2_id on lottu.t2  (cost=0.29..353.27 rows=9999 width=13) (actual time=0.030..5.304 rows=9998 loops=1)
   Output: id, info
   Index Cond: (t2.id > 2)
   Buffers: shared hit=84
 Planning time: 0.295 ms
 Execution time: 7.027 ms
(6 rows)

#在這個案例中：執行同一條SQL。

  • t2有索引的執行數據是7.027 ms；t1沒有索引的是：15.205 ms;
  • t1沒有索引執行還占用了 Memory: 853kB。

2. 索引掃描方式

索引的掃描方式有3種

2.1 Indexscan

先查索引找到匹配記錄的ctid，再通過ctid查堆表

2.2 bitmapscan

先查索引找到匹配記錄的ctid集合，把ctid通過bitmap做集合運算和排序後再查堆表

2.3 Indexonlyscan

如果索引欄位中包含了所有返回欄位，對可見性映射 (vm)中全為可見的數據塊，不查堆表直接返回索引中的值。

這裡談談Indexscan掃描方式和Indexonlyscan掃描方式
對這兩種掃描方式區別；借用oracle中索引掃描方式來講；Indexscan掃描方式會產生回表讀。根據上面解釋來說；Indexscan掃描方式：查完索引之後還需要查表。 Indexonlyscan掃描方式只需要查索引。也就是說：Indexonlyscan掃描方式要優於Indexscan掃描方式？我們來看看

現有表t；在欄位id上面建來ind_t_id索引
1. t表沒有VM文件。
lottu=# \d+ t
                           Table "lottu.t"
 Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
 id     | integer |           | plain    |              | 
 info   | text    |           | extended |              | 
Indexes:
    "ind_t_id" btree (id)

lottu=# explain (analyze,buffers,verbose) select id from t where id < 10;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using ind_t_id on lottu.t  (cost=0.29..8.44 rows=9 width=4) (actual time=0.009..0.015 rows=9 loops=1)
   Output: id
   Index Cond: (t.id < 10)
   Heap Fetches: 9
   Buffers: shared hit=3
 Planning time: 0.177 ms
 Execution time: 0.050 ms
(7 rows)
#人為更改執行計劃
lottu=# set enable_indexonlyscan = off;
SET
lottu=# explain (analyze,buffers,verbose) select id from t where id < 10;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using ind_t_id on lottu.t  (cost=0.29..8.44 rows=9 width=4) (actual time=0.008..0.014 rows=9 loops=1)
   Output: id
   Index Cond: (t.id < 10)
   Buffers: shared hit=3
 Planning time: 0.188 ms
 Execution time: 0.050 ms
(6 rows)
# 可以發現兩者幾乎沒有差異；唯一不同的是Indexonlyscan掃描方式存在掃描的Heap Fetches時間。 這個時間是不在Execution time裡面的。
2. t表有VM文件
lottu=# delete from t where id >200 and id < 500;
DELETE 299
lottu=# vacuum t;
VACUUM
lottu=# analyze t;
ANALYZE
lottu=# explain (analyze,buffers,verbose) select id from t where id < 10;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using ind_t_id on lottu.t  (cost=0.29..4.44 rows=9 width=4) (actual time=0.008..0.012 rows=9 loops=1)
   Output: id
   Index Cond: (t.id < 10)
   Heap Fetches: 0
   Buffers: shared hit=3
 Planning time: 0.174 ms
 Execution time: 0.048 ms
(7 rows)

lottu=# set enable_indexonlyscan = off;
SET
lottu=# explain (analyze,buffers,verbose) select id from t where id < 10;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using ind_t_id on lottu.t  (cost=0.29..8.44 rows=9 width=4) (actual time=0.012..0.022 rows=9 loops=1)
   Output: id
   Index Cond: (t.id < 10)
   Buffers: shared hit=3
 Planning time: 0.179 ms
 Execution time: 0.077 ms
(6 rows)

總結：

  • Index Only Scan在沒有VM文件的情況下, 速度比Index Scan要慢, 因為要掃描所有的Heap page。差異幾乎不大。
  • Index Only Scan存在VM文件的情況下，是要比Index Scan要快。

知識點1：

  • VM文件：稱為可見性映射文件；該文件存在表示：該數據塊沒有需要清理的行。即已經做了vaccum操作。

知識點2：

人為選擇執行計劃。可設置enable_xxx參數有

  • enable_bitmapscan
  • enable_hashagg
  • enable_hashjoin
  • enable_indexonlyscan
  • enable_indexscan
  • enable_material
  • enable_mergejoin
  • enable_nestloop
  • enable_seqscan
  • enable_sort
  • enable_tidscan

參考文獻

  • 參考德哥：《PostgreSQL 性能優化培訓 3 DAY.pdf》
  • https://www.postgresql.org/docs/9.6/static/runtime-config-query.html

3. 索引的類型

PostgreSQL 支持索引類型有: B-tree, Hash, GiST, SP-GiST, GIN and BRIN。

  • postgresql----Btree索引:http://www.cnblogs.com/alianbog/p/5621749.html
  • postgresql----hash索引：一般只用於簡單等值查詢。不常用。
  • postgresql----Gist索引:http://www.cnblogs.com/alianbog/p/5628543.html

4. 索引的管理

4.1 創建索引

創建索引語法：

lottu=# \h create index
Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]
接下來我們以t表為例。    
1. 關鍵字【UNIQUE】
#創建唯一索引；主鍵就是一種唯一索引
CREATE UNIQUE INDEX ind_t_id_1 on t (id);
2. 關鍵字【CONCURRENTLY】
# 這是併發創建索引。跟oracle的online創建索引作用是一樣的。創建索引過程中；不會阻塞表更新，插入，刪除操作。當然創建的時間就會很漫長。
CREATE INDEX CONCURRENTLY ind_t_id_2 on t (id);
3. 關鍵字【IF NOT EXISTS】
#用該命令是用於確認索引名是否存在。若存在；也不會報錯。
CREATE INDEX IF NOT EXISTS ind_t_id_3 on t (id);
4. 關鍵字【USING】
# 創建哪種類型的索引。 預設是B-tree。
CREATE INDEX ind_t_id_4 on t using btree (id);
5 關鍵字【[ ASC | DESC ] [ NULLS { FIRST | LAST]】
# 創建索引是採用降序還是升序。 若欄位存在null值，是把null值放在前面還是最後：例如採用降序，null放在前面。
CREATE INDEX ind_t_id_5 on t (id desc nulls first)
6. 關鍵字【WITH ( storage_parameter = value)】
#索引的填充因數設為。例如創建索引的填充因數設為75
CREATE INDEX ind_t_id_6 on t (id) with (fillfactor = 75);
7. 關鍵字【TABLESPACE】
#是把索引創建在哪個表空間。
CREATE INDEX ind_t_id_7 on t (id) TABLESPACE tsp_lottu;
8. 關鍵字【WHERE】
#只在自己感興趣的那部分數據上創建索引，而不是對每一行數據都創建索引，此種方式創建索引就需要使用WHERE條件了。
CREATE INDEX ind_t_id_8 on t (id) WHERE id < 1000;

4.2 修改索引

修改索引語法

lottu=# \h alter index
Command:     ALTER INDEX
Description: change the definition of an index
Syntax:
#把索引重新命名
ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
#把索引遷移表空間
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
#把索引重設置填充因數
ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] )
#把索引的填充因數設置為預設值
ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
#把表空間TSP1中索引遷移到新表空間
ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]

4.3 刪除索引

刪除索引語法

lottu=# \h drop index
Command:     DROP INDEX
Description: remove an index
Syntax:
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

5. 索引的維護

索引能帶來加快對錶中記錄的查詢，排序，以及唯一約束的作用。索引也是有代價

  • 索引需要增加資料庫的存儲空間。
  • 在表記錄執行插入，更新，刪除操作。索引也要更新。

5.1 查看索引的大小

select pg_size_pretty(pg_relation_size('ind_t_id'));

5.2 索引的利用率

--通過pg_stat_user_indexes.idx_scan可檢查利用索引進行掃描的次數；這樣可以確認那些索引可以清理掉。
select idx_scan from pg_stat_user_indexes where indexrelname = 'ind_t_id';

5.3 索引的重建

--如果一個表經過頻繁更新之後，索引性能不好；需要重建索引。
lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1')); 
 pg_size_pretty 
----------------
 2200 kB
(1 row)

lottu=# delete from t where id > 1000;
DELETE 99000

lottu=# analyze t;
ANALYZE
lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1')); 
 pg_size_pretty 
----------------
 2200 kB
 
lottu=# insert into t select generate_series(2000,100000),'lottu';
INSERT 0 98001

lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1')); 
 pg_size_pretty 
----------------
 4336 kB
(1 row)

lottu=# vacuum full t;
VACUUM

lottu=# select pg_size_pretty(pg_relation_size('ind_t_id_1')); 
 pg_size_pretty 
----------------
 2176 kB
 
重建方法： 
1. reindex：reindex不支持並行重建【CONCURRENTLY】;索引會鎖表；會進行阻塞。
2. vacuum full; 對錶進行重構；索引也會重建；同樣也會鎖表。
3. 創建一個新索引(索引名不同)；再刪除舊索引。

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 數組NSArrayt,NSMutableArray使用中的那些坑
    OC開發中一般都會使用model來處理數據，經常會碰到定義好的欄位因為各種原因 後臺突然改欄位或者空值 造成model中對應的數值為nil,這個時候 我們如果取這個值加入數組中使用就會發現數組中的參數個數少了，造成閃退。因為在OC中數組無需要預先申請空間 數組每增加或刪除一個元素，就增加或刪除一個節 ...
  • 給iPhone手機安裝*.ipa
    首先手機連接電腦，打開 ITunes. 左上角點擊文件，再點擊添加到資料庫。 找到你要安裝的ipa點擊選擇打開 好了ipa已經添加進去後，你會看你你剛纔添加進去的應用在列表內，點擊左上角一個手機的圖標。 點擊安裝，右下角同步 便成功安裝到手機上了。 此隨筆乃本人學習工作記錄，如有疑問歡迎在下麵評論， ...
  • Android 常用正則表達式
    移動的號段：134(0-8)、135、136、137、138、139、147（預計用於TD上網卡） * 、150、151、152、157（TD專用）、158、159、187（未啟用）、188（TD專用） 聯通的號段：130、131、132、155、156（世界風專用）、185（未啟用）、186（3g ...
  • Mac 下使用svn
    作為一個一直使用windows系統的人，還真不知道mac上的svn如何使用，偶然機會下現在需要。 查過後發現 mac 是自帶svn的，在我裝好xcode後，再安裝 Command Line Tools 1.打開終端 輸入 點擊安裝，過幾分鐘就安好了。 2.再輸入此命令查看版本號，有版本號就是安裝好了 ...
  • 使用IP地址方法登錄MySQL資料庫Can't connect to MySQL server的原因。mysql -h 192.168.1.104 -P3306 -uroot -p 失敗
    mysql -h 192.168.1.104 -P3306 -uroot -p 然後輸入你安裝時設置的MySQL密碼 發現Can't connect to MySQL server 你的IP 解決方法： （1）用localhost 登錄 mysql -h localhost -P3306 -uroo ...
  • Spark四大特性
    一 快速性 如果在記憶體中運行MapRaduce,要比Hadoop快100倍 如果在磁碟中運行,要比Hadoop快10倍 Spark使用先進的有向無環圖執行引擎來支持非迴圈的數據流在記憶體中計算 二 易用性 Spark提供超過80個高階運算元,這些運算元使其很容易構建並行應用 這些運算元支持多種語言 按照切合 ...
  • Mysql InnoDB的四個事務隔離級別和（分別逐級解決的問題）臟讀，不可重覆讀，虛讀
    MySqlInnoDB的事務隔離級別有四個：（預設是可重覆讀repeatable read） 未提交讀 read uncommit : 在另一個事務修改了數據，但尚未提交，在本事務中SELECT語句可能會查詢到這些未被提交的數據，而發生臟讀。 提交讀 read commit : 在一個事務中發生兩次 ...
  • ORACLE虛擬索引(Virtual Index)
    ORACLE虛擬索引(Virtual Index) 虛擬索引概念 虛擬索引（Virtual Indexes）是一個定義在數據字典中的假索引（fake index），它沒有相關的索引段。虛擬索引的目的是模擬索引的存在而不用真實的創建一個完整索引。這允許開發者創建虛擬索引來查看相關執行計劃而不用等到真實... ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類