iT邦幫忙

第 11 屆 iThome 鐵人賽

DAY 18
1
AI & Data

後端前進PostgreSQL系列 第 18

添加索引加快查詢速度

索引是什麼?

索引就像一本書的目錄一樣,方便查詢用的,目前市售的書幾乎都上百頁,例如我要找某一個章節,沒有目錄必須一頁一頁翻,時間就會被拉長。

PostgreSQL 預設的索引類型 是 B-Tree,還可以整合其他的索引,如果有時間會一一介紹(全文查詢的索引、GIS索引...)。

之前的範例資料表在添加主鍵或是UNIQUE約束條件,就已經自動加上索引了!也算在冥冥之中有用到這個功能。索引會把資料另外存使得加速查詢速度,相對的空間自然的也會增加,必須自行取捨,不是全部加上索引。

建立索引

CREATE INDEX email_idx ON users (email);

解釋 建立一個名稱為 email_idx 的索引,ON關鍵字再附上資料表以及用小括弧包著欄位名稱!

移除索引

DROP  INDEX email_idx;

關鍵字 DROP INDEX 即可移除索引。

用 EXPLAIN 來評斷查詢效能

這是PostgreSQL 專有的指令,非標準SQL,輸出內容會包含這道SQL的查詢計畫(query plan)。其中還會包含資料庫計畫如何掃描資料表、是否要用到索引等等。如果我們再加上關鍵字 ANALYZE,EXPLAIN 就會執行查詢,並顯示實際執行所需的時間。

全部索引?

剛剛有稍微提到索引可以大幅減少查詢時間,全部加上索引就好啦!等等 加入索引會影響到資料庫的大小,以及會對資料寫入動作有一定的影響。

可以判斷一下,最簡單的方式可以觀察常常需要 WHERE 的資料 欄位加上索引,並且可以多多嘗試利用EXPLAIN ANALYZE 看各種測試結果,找一個最好的方法,因此不要全部加上索引喔!

結論

索引在資料比較少的時候,不太會去設定索引,但是日久生資料,系統的效能會變慢,改善效能就是要思考的問題,添加索引就是可以考慮的地方。

跟我一開始的第一篇文章一樣,真的每一個部分 不管是後端、前端(切版)、設計師(UI\UX)、資料庫、行銷... 每一個部分都很深很深。

增加索引要加在哪裡,真的需要依照資料庫的設計,多多嘗試才能找到最合適的優化方式!


上一篇
資料表設計-約束條件-外鍵
下一篇
PostGIS 分析空間資料 (1) - 加入擴充元件
系列文
後端前進PostgreSQL30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言