資料庫開發過程中,索引是非常重要的一個概念。索引的目的是提高查詢的速度,對於一些資料量不大的表,是否建索引並不重要,但當資料量達到了萬級、百萬、千萬級別的時候,查詢速度就不能按照原有的循序查詢,必須按指定路線去查詢以達到提高查詢的目的。因此,效能最佳化很關鍵的一個技術點就是索引最佳化。
針對不同的使用場景,資料庫有許多種索引型別來應對,例如B-tree、 Hash、 GiST、 SP-GiST、GIN、 BRIN和Bloom等,由於優炫資料庫UXDB的MVCC內部機制,(MVCC是一種併發控制機制,資料庫引擎根據不同的事務隔離級別,透過查詢事務快照和事務提交日誌來完成元組的可見性檢查。)當執行大量的更新操作後,會出現“索引膨脹”的現象,這時候可以透過CREATE INDEX CONCURRENTLY在不阻塞查詢和更新的情況下,線上重新建立索引,建立好新的索引之後,再刪除原來有膨脹的索引,減小索引尺寸,提高查詢速度。
對於主鍵也可以使用這種方式進行重建,重建方法如下:
CREATE
UNIQUE
INDEX
CONCURRENTLY
ON
test
USING
btree(
id
);/*CONCURRENTLY引數並行建立索引*/
CREATE
INDEX
可以看到id欄位上同時有兩個索引test_pkey和test_id_idx,如下所示:
SELECT
schemaname,relname, indexrelname, ux_r el ation_size (indexrelid)
AS
index_size,
id
x_scan, idx tup_read , idx_tup_fetch
FROM
ux_stat_user_
indexes
WHERE
indexrelname
IN
(
SELECT
indexname
FROM
ux
indexes
WHERE
schemaname= ‘public ’AND tablename= ‘test’) ;
開啟事務刪除主鍵索引,同時將第二索引更新為主鍵的約束,如下所示:
BEGIN
;
ALTER
TABLE
test
DROP
CONSTRAINT
test_pkey;
ALTER
TABLE
test
ADD
CONSTRAINT
test_id_idx PRIMA RY
KEY
USING
INDEX
END
;
檢查表索引,現在只有第二索引了,如下所示:
SELECT
schemaname , relname, indexrelname, ux_r el ation_
size
(indexrelid )
AS
index_size , idx
scan
,idx_tup_read ,i dx_tup_fetch
FROM
ux_stat_user _ i ndexes
WHERE
indexrelname
IN
(
SELECT
indexname
FROM
ux
indexes
WHEREJI
這樣就完成了主鍵索引的重建,對於大規模的資料庫叢集,可以透過ux_repack工具進行定時的索引重建。
-FIN-