帶表達式的索引

?專欄內容：

• postgresql使用入門基礎
• 手寫數據庫toadb
• 并發編程

個人主頁：我的主頁
管理社區：開源數據庫
座右銘：天行健，君子以自強不息；地勢坤，君子以厚德載物.

概述

---

在postgresql 中，一個索引不僅僅是基于表的一列或多列來創建，還可以基于函數，或者一個表達式來創建。

本文就來分享在postgresql 如何基于表達式來創建索引。

創建語法

---

基于表達式創建索引，它的SQL語法如下所示：

CREATE INDEX index_name 
ON table_name (expression);

• index_name 指定當前索引的名稱 ；
• ON子句 指定當前索引 引用的數據表；
• expression 指定表達式內容；普通索引這里指定的是列名；

場景分析

---

在大數據時代，查詢語句各式各樣，過濾條件中帶有函數，字符拼接等等，組成各種條件變量，下面我們按不同場景來舉例說明。

函數表達式

經常會遇到將字符串轉換為小字，或者在大小寫不敏感時，就可以轉換為大寫或者小寫，再來比較。

有一張人員信息表，名字分為first_name,last_name兩部分，而名字又是大小字不敏感，所以經常轉換為小寫字符來比較。

postgres=> create table userInfo (uid integer primary key, first_name varchar, last_name varchar);
CREATE TABLEpostgres=> INSERT INTO userinfo(uid, first_name, last_name)
select id, 'firstname' || id::int, 'lastname'||id::int FROM generate_series(1, 100000) as id;
INSERT 0 100000

表中插入了10萬條測試數據。

經常使用的SQL查詢如下。

select * from userinfo where lower(first_name) = 'mar';

其中就用到了函數轉換，先將first_name轉為小寫，再參與條件比較。

看一下它的執行計劃。

postgres=> explain select * from userinfo where lower(first_name) = 'mar';QUERY PLAN
--------------------------------------------------------------Seq Scan on userinfo  (cost=0.00..2324.00 rows=500 width=31)Filter: (lower((first_name)::text) = 'mar'::text)
(2 rows)

可以看到它使用了seq scan也就是順序掃描，從表起始一條條進行遍歷，如果此類查詢非常頻繁的話，相當損耗性能。

這里使用帶有表達式的索引嘗試來優化一下。

postgres=> explain select * from userinfo where lower(first_name) = 'mar';QUERY PLAN
------------------------------------------------------------------------------------Index Scan using idx_expre_userinfo on userinfo  (cost=0.42..8.44 rows=1 width=31)Index Cond: (lower((first_name)::text) = 'mar'::text)
(2 rows)

可以看到執行計劃中，使用到了剛才創建的索引，而且執行估算時間也是大幅提升。

普通表達式

繼續使用上面的測試數據來看另外一種場景。

當我們需要查詢某個用戶名是否存在時，會經常使用如下SQL語句。

postgres=> select * from userinfo where (first_name || ' ' || last_name) = 'firstname9999 lastname9999';uid  |  first_name   |  last_name
------+---------------+--------------9999 | firstname9999 | lastname9999
(1 row)Time: 7.905 ms
postgres=> explain select * from userinfo where (first_name || ' ' || last_name) = 'firstname9999 lastname9999';QUERY PLAN
-----------------------------------------------------------------------------------------------------------Seq Scan on userinfo  (cost=0.00..2574.00 rows=500 width=31)Filter: ((((first_name)::text || ' '::text) || (last_name)::text) = 'firstname9999 lastname9999'::text)
(2 rows)Time: 0.234 ms

篩選條件中，先將first_name和last_name拼接起來，再進行比較。

可以看到執行計劃中使用了順序掃描方式，執行時間也到了毫秒級，同樣使用表達式索引來優化一下。

postgres=> create index idx_userinfo_name on userinfo ((first_name || ' ' || last_name));
CREATE INDEX
Time: 307.842 ms

創建一個基于名字拼接表達式的索引。

下面再來看一下查詢計劃的情況。

postgres=> explain select * from userinfo where (first_name || ' ' || last_name) = 'firstname9999 lastname9999';QUERY PLAN
---------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan on userinfo  (cost=20.29..778.62 rows=500 width=31)Recheck Cond: ((((first_name)::text || ' '::text) || (last_name)::text) = 'firstname9999 lastname9999'::text)->  Bitmap Index Scan on idx_userinfo_name  (cost=0.00..20.17 rows=500 width=0)Index Cond: ((((first_name)::text || ' '::text) || (last_name)::text) = 'firstname9999 lastname9999'::text)
(4 rows)Time: 0.366 ms

可以看到剛才創建的索引被使用了 Bitmap Index Scan on idx_userinfo_name, 采用了bitmap掃描的方式；

下面看一下執行時間的變化。

postgres=> select * from userinfo where (first_name || ' ' || last_name) = 'firstname9999 lastname9999';uid  |  first_name   |  last_name
------+---------------+--------------9999 | firstname9999 | lastname9999
(1 row)Time: 0.274 ms

執行時間的提升，真得令人驚?，提升了二十來倍。

總結

---

以上就是本節的全部內容，在復雜的SQL查詢中，經常會用到各種表達式，字符運算，時間運算等，此時可以使用基于表達式或者函數的索引，使用索引進行優化效率。

結尾

---

非常感謝大家的支持，在瀏覽的同時別忘了留下您寶貴的評論，如果覺得值得鼓勵，請點贊，收藏，我會更加努力！

作者郵箱：study@senllang.onaliyun.com
如有錯誤或者疏漏歡迎指出，互相學習。

注：未經同意，不得轉載！