MOGDB/openGauss索引推薦及虛擬索引

T1YSL發表於2021-12-10
索引

索引推薦

在ORACLE的最佳化中，可能大家有接觸過SQL Tuning Advisor(SQL調優顧問，STA)，類似的MOGDB/openGauss的索引推薦(Index-advisor)功能也可以對你的查詢進行分析，並提出合理的建立索引的建議。ORACLE的STA輸出是以一種意見或者建議的形式，以及對每一項建議和期望效益的理由。該建議涉及物件的統計收集，新索引的建立，SQL語句的重組，或SQL概要的建立。你可以選擇該建議來完成SQL語句的調優。MOGDB/openGauss的索引推薦(Index-advisor)在這也是比較類似，但可能結果不如ORACLE的S他的最佳化報告詳盡。

如下為我對MOGDB/openGauss的索引推薦(Index-advisor)功能的使用測試，包括單條SQL查詢索引推薦、Workload級別索引推薦(針對一批SQL語句的索引推薦)等。

一、測試資料匯入

postgres=# create database ysla;
CREATE DATABASE
postgres=# \c ysla
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ysla" as user "omm".
ysla=# CREATE TABLE tab_ysl_1 (col1 int, col2 int, col3 text);
CREATE TABLE
ysla=# INSERT INTO tab_ysl_1 VALUES(generate_series(1, 3000),generate_series(1, 3000),repeat( chr(int4(random()*26)+65),4));
INSERT 0 3000
ysla=# ANALYZE tab_ysl_1;
ANALYZE
ysla=# CREATE TABLE tab_ysl_2 (col1 int, col2 int);
CREATE TABLE
ysla=# INSERT INTO tab_ysl_2 VALUES(generate_series(1, 1000),generate_series(1, 1000));
INSERT 0 1000
ysla=# ANALYZE tab_ysl_2;
ANALYZE

二、單條SQL查詢索引推薦

如下面所示，用gs_index_advise函式即可使用索引推薦，結果中包含表和可以建立索引的列。

1.測試where

ysla=# SELECT  * FROM gs_index_advise('SELECT * FROM tab_ysl_1 WHERE col1 = 10');
   table   | column
-----------+--------
 tab_ysl_1 | (col1)
(1 row)

2.測試join

ysla=# SELECT  * FROM gs_index_advise('SELECT * FROM tab_ysl_1 join tab_ysl_2 on tab_ysl_1.col1 = tab_ysl_2.col1');
   table   | column
-----------+--------
 tab_ysl_1 | (col1)
 tab_ysl_2 |
(2 rows)

3.測試多表

ysla=# SELECT  * FROM gs_index_advise('SELECT count(*), tab_ysl_2.col1 FROM tab_ysl_1 join tab_ysl_2 on tab_ysl_1.col2 = tab_ysl_2.col2 WHERE tab_ysl_2.col2 > 2 GROUP BY tab_ysl_2.col1 ORDER BY tab_ysl_2.col1');
   table   | column
-----------+--------
 tab_ysl_1 | (col2)
 tab_ysl_2 | (col1)
(2 rows)

4.測試order by

ysla=# SELECT  * FROM gs_index_advise('SELECT *, col2 FROM tab_ysl_1 ORDER BY 1, 3');
   table   | column
-----------+--------
 tab_ysl_1 |
(1 row)
ysla=# SELECT  * FROM gs_index_advise('SELECT * FROM tab_ysl_1 WHERE col1 > 10 ORDER BY 1,col2');
   table   | column
-----------+--------
 tab_ysl_1 |
(1 row)

5.測試過長字串

ysla=# SELECT  * FROM gs_index_advise('SELECT * FROM tab_ysl_1 where col3 in (''aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'',''bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'',''ccccccccccccccccccccccccccccccccccccccc'',''ddddddddddddddddddddddddddddddddddddddd'',''ffffffffffffffffffffffffffffffffffffffff'',''ggggggggggggggggggggggggggggggggggggggggggggggggggg'',''ttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt'',''vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv'',''ggmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm'')');
ERROR:  index_advisor.cpp : 983 : The parameter destMax is too small or parameter count is larger than macro parameter SECUREC_STRING_MAX_LEN. The second case only occures in functions strncat_s/strncpy_s.

三、Workload級別索引推薦

這種方式可以針對多條SQL,可以將待最佳化的SQL寫到檔案裡，透過指令碼獲得推薦索引。

指令碼目錄在安裝目錄的bin/dbmind/index_advisor下邊，我的目錄為

/opt/gaussdb/app/bin/dbmind/index_advisor/index_advisor_workload.py

將待最佳化的SQL放到檔案裡

[omm@node1 index_advisor]$ cat 1.sql
SELECT * FROM tab_ysl_1 WHERE col1 = 10;
SELECT count(*), tab_ysl_2.col1 FROM tab_ysl_1 join tab_ysl_2 on tab_ysl_1.col2 = tab_ysl_2.col2 WHERE tab_ysl_2.col2 > 2 GROUP BY tab_ysl_2.col1 ORDER BY tab_ysl_2.col1;
SELECT * FROM tab_ysl_1 join tab_ysl_2 on tab_ysl_1.col1 = tab_ysl_2.col1;

使用如下方式呼叫指令碼，可以批次獲取推薦索引，26000為我的資料庫埠，ysla為我的資料庫名，1.sql為我待最佳化的SQL存放的檔案

[omm@node1 index_advisor]$ pwd
/opt/gaussdb/app/bin/dbmind/index_advisor
[omm@node1 index_advisor]$ python3 ./index_advisor_workload.py 26000 ysla 1.sql
###### ############################################################## Generate candidate indexes
table:  tab_ysl_1 columns:  col1
table:  tab_ysl_1 columns:  col2
table:  tab_ysl_2 columns:  col1
###### ############################################################### Determine optimal indexes
create index ind0 on tab_ysl_1(col1);

四、索引效率檢視

這裡驗證下索引推薦給我們推薦的索引究竟是否起到最佳化作用。

[omm@node1 index_advisor]$ cat 1.sql
SELECT * FROM tab_ysl_1 WHERE col1 = 10;
[omm@node1 index_advisor]$  time gsql -d ysla -p 26000 -f 1.sql
 col1 | col2 | col3
------+------+------
   10 |   10 | SSSS
(1 row)
total time: 35  ms
real    0m0.050s
user    0m0.007s
sys     0m0.002s

可以看到上邊未最佳化的SQL執行時間為0m0.050s

[omm@node1 index_advisor]$ python3 ./index_advisor_workload.py 26000 ysla 1.sql
###### ############################################################## Generate candidate indexes
table:  tab_ysl_1 columns:  col1
###### ############################################################### Determine optimal indexes
create index ind0 on tab_ysl_1(col1);

透過Index-advisor獲取推薦索引。並建立索引

ysla=# create index ind0 on tab_ysl_1(col1);
CREATE INDEX

可以看到查詢的時間明顯減少。

[omm@node1 index_advisor]$  time gsql -d ysla -p 26000 -f 1.sql
 col1 | col2 | col3
------+------+------
   10 |   10 | SSSS
(1 row)
total time: 0  ms
real    0m0.016s
user    0m0.009s
sys     0m0.000s

虛擬索引

一般在加索引時，會堵塞DML（不過PG支援併發加索引，不堵塞DML） 。只有索引真正能起到最佳化作用，我們建立索引才是有意義的。虛擬索引是一個很有用的東西，沒有副作用，只是虛擬的索引，建立虛擬索引後，可以透過EXPLAIN來檢視加索引後的成本估算，判斷是否加索引COST會降低。

可以用虛擬索引檢驗索引的效果，根據效果可選擇是否建立真實的索引最佳化查詢。

#測試建立虛擬索引（hypopg_create_index）
ysla=# SELECT * FROM hypopg_create_index('CREATE INDEX ON tab_ysl_1(col1)');
 indexrelid |          indexname
------------+-----------------------------
      41453 | <41453>btree_tab_ysl_1_col1
(1 row)
#顯示所有建立的虛擬索引資訊（enable_hypo_index）
ysla=# select * from hypopg_display_index();
          indexname          | indexrelid |   table   | column
-----------------------------+------------+-----------+--------
 <41454>btree_tab_ysl_1_col1 |      41454 | tab_ysl_1 | (col1)
(1 row)
ysla=# set enable_hypo_index = on;explain SELECT * FROM tab_ysl_1 WHERE col1 = 100;
SET
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Index Scan using <41453>btree_tab_ysl_1_col1 on tab_ysl_1  (cost=0.00..8.27 rows=1 width=13)
   Index Cond: (col1 = 100)
(2 rows)
#測試刪除指定虛擬索引（hypopg_display_index）
使用函式hypopg_drop_index刪除指定oid的虛擬索引
ysla=# select * from hypopg_drop_index(41454);
 hypopg_drop_index
-------------------
 t
(1 row)
#使用函式hypopg_reset_index一次性清除所有建立的虛擬索引
ysla=# SELECT * FROM hypopg_reset_index();
 hypopg_reset_index
--------------------


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69990629/viewspace-2847117/，如需轉載，請註明出處，否則將追究法律責任。

相關文章