初窥 openGauss 之索引推荐 Index-advisor
TPC-H 是一个面向分析型业务(AP)的基准测试，它由一系列热点查询组成，这些热点查询都是高度复杂的，因此执行时间往往都比较长。
在本次实验测试中，将手动向数据库加载 TPC-H 数据，并保存在名为 tpch 的数据库中。默认 TPC-H 数据库的表缺少索引，数据库的参数并没有做任何优化，因此执行效率会比较差。
本实验内容比较浅显，使用 openGauss 的索引推荐(Index-advisor)功能，对数据库进行性能优化，同时也让大家对 Index-advisor 功能有一个初步的了解。
环境信息
OS： CentOS Linux release 7.6.1810
openGauss：2.0.0
CPU：1core
Memory：4GB
测试数据脚本清单如下：
[omm@lab01 ~]$ ls -l ~/tpch-kit-back/
total 1076780
-rw------- 1 omm dbgrp 24196144 Apr 24 15:39 customer.tbl
-rw------- 1 omm dbgrp 3814 Apr 24 15:39 dss.ddl
-rw------- 1 omm dbgrp 753862072 Apr 24 15:39 lineitem.tbl
-rw------- 1 omm dbgrp 287 May 25 10:52 load.sh
-rw------- 1 omm dbgrp 2199 Apr 24 15:16 nation.tbl
-rw------- 1 omm dbgrp 170452161 Apr 24 15:16 orders.tbl
-rw------- 1 omm dbgrp 10553197 Apr 24 15:11 out0
-rw------- 1 omm dbgrp 118184616 Apr 24 15:10 partsupp.tbl
-rw------- 1 omm dbgrp 23935125 Apr 24 15:11 part.tbl
drwx------ 3 omm dbgrp 4096 Apr 24 15:39 queries
-rw------- 1 omm dbgrp 384 Apr 24 15:07 region.tbl
-rw------- 1 omm dbgrp 1399184 Apr 24 15:07 supplier.tbl
1. 创建数据库并导入数据
-- 创建数据库tpch
[omm@lab01 ~]$ gsql -d postgres -p 26000 -c "create database tpch with encoding='UTF-8';"
-- 创建测试表
[omm@lab01 ~]$ gsql -d tpch -p 26000 -f ~/tpch-kit-back/dss.ddl
-- 加载测试数据并统计分析
[omm@lab01 ~]$ vi load.sh
---------------------------------------
for i in `ls *.tbl`; do
table=${i/.tbl/}
echo "Loading $table..."
sed 's/|$//' $i > /tmp/$i
gsql -d tpch -p 26000 -c "TRUNCATE $table"
gsql -d tpch -p 26000 -c "\\copy $table FROM '/home/omm/tpch-kit-back/$i' CSV DELIMITER '|'"
gsql -d tpch -p 26000 -c "ANALYZE $table"
done
---------------------------------------
sh load.sh
2. 执行第一次查询测试(耗时：106s)
[omm@lab01 ~]$ time gsql -d tpch -p 26000 -f /home/omm/tpch-kit-back/queries/queries.sql -o out0
total time: 105949 ms
real 1m46.063s
user 0m0.707s
sys 0m0.026s
3. 索引信息查询(当前没有任何索引)
[omm@lab01 ~]$ gsql -d tpch -p 26000 -r
tpch=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+----------+-------+-------+----------------------------------
public | customer | table | omm | {orientation=row,compression=no}
public | lineitem | table | omm | {orientation=row,compression=no}
public | nation | table | omm | {orientation=row,compression=no}
public | orders | table | omm | {orientation=row,compression=no}
public | part | table | omm | {orientation=row,compression=no}
public | partsupp | table | omm | {orientation=row,compression=no}
public | region | table | omm | {orientation=row,compression=no}
public | supplier | table | omm | {orientation=row,compression=no}
(8 rows)
tpch=# \di
No relations found.
tpch=# select * from pg_indexes where schemaname='public';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+----------
(0 rows)
4. 单条 SQL 查询索引推荐
-- 未添加索引的查询效率(约4.9s)
[omm@lab01 ~]$ time gsql -d tpch -p 26000 -c "select * from lineitem where l_orderkey < 100 and l_suppkey > 50;"
real 0m4.916s
user 0m0.014s
sys 0m0.001s
-- 使用索引推荐函数(gs_index_advise)获取优化建议
tpch=# select *from gs_index_advise('select * from lineitem where l_orderkey < 100 and l_suppkey > 50;');
table | column
----------+--------------
lineitem | (l_orderkey)
-- 创建索引
tpch=# create index idx1 on lineitem(l_orderkey);
-- 查看优化结果(约2.3s)
[omm@lab01 ~]$ time gsql -d tpch -p 26000 -c "select * from lineitem where l_orderkey < 100 and l_suppkey > 50;"
real 0m2.337s
user 0m0.009s
sys 0m0.007s
5. Workload 级别索引推荐(针对一批 SQL 语句的索引推荐)
-- 获取推荐索引
[omm@lab01 ~]$ cd /gauss/app/bin/dbmind/index_advisor/
[omm@lab01 index_advisor]$ python3 ./index_advisor_workload.py 26000 tpch ~/queries/queries.sql -- 端口：26000 数据库：tpch
####################################### Generate candidate indexes #######################################
table: lineitem columns: l_returnflag,l_linestatus
table: part columns: p_partkey,p_size
table: supplier columns: s_suppkey,s_nationkey
table: partsupp columns: ps_partkey,ps_suppkey
table: nation columns: n_nationkey,n_regionkey
table: orders columns: o_orderkey,o_custkey
table: customer columns: c_custkey,c_nationkey
table: orders columns: o_custkey,o_orderkey
table: lineitem columns: l_orderkey,l_suppkey
table: customer columns: c_custkey
table: part columns: p_partkey,p_type
table: supplier columns: s_suppkey
table: lineitem columns: l_suppkey,l_partkey,l_orderkey
table: part columns: p_partkey
table: lineitem columns: l_orderkey,l_partkey,l_suppkey
table: orders columns: o_orderkey
table: partsupp columns: ps_suppkey
table: lineitem columns: l_shipdate,l_receiptdate,l_commitdate,l_orderkey
table: lineitem columns: l_partkey
######################################## Determine optimal indexes ########################################
create index ind0 on lineitem(l_shipdate,l_receiptdate,l_commitdate,l_orderkey);
create index ind1 on lineitem(l_returnflag,l_linestatus);
create index ind2 on lineitem(l_suppkey,l_partkey,l_orderkey);
create index ind3 on orders(o_orderkey,o_custkey);
create index ind4 on partsupp(ps_partkey,ps_suppkey);
create index ind5 on part(p_partkey,p_size);
create index ind6 on part(p_partkey,p_type);
create index ind7 on customer(c_custkey,c_nationkey);
create index ind8 on supplier(s_suppkey,s_nationkey);
create index ind9 on nation(n_nationkey,n_regionkey);
-- 创建推荐的索引
[omm@lab01 ~]$ gsql -d tpch -p 26000 -r
tpch=# create index ind0 on lineitem(l_shipdate,l_receiptdate,l_commitdate,l_orderkey);
tpch=# create index ind1 on lineitem(l_returnflag,l_linestatus);
tpch=# create index ind2 on lineitem(l_suppkey,l_partkey,l_orderkey);
tpch=# create index ind3 on orders(o_orderkey,o_custkey);
tpch=# create index ind4 on partsupp(ps_partkey,ps_suppkey);
tpch=# create index ind5 on part(p_partkey,p_size);
tpch=# create index ind6 on part(p_partkey,p_type);
tpch=# create index ind7 on customer(c_custkey,c_nationkey);
tpch=# create index ind8 on supplier(s_suppkey,s_nationkey);
tpch=# create index ind9 on nation(n_nationkey,n_regionkey);
-- 测试查询脚本时间(耗时：77s，SQL查询相比之前快了29s)
[omm@lab01 ~]$ time gsql -d tpch -p 26000 -f /home/omm/tpch-kit-back/queries/queries.sql -o out0
total time: 77200 ms
real 1m17.233s
user 0m0.665s
sys 0m0.020s