索引推荐神器Paw Index Advisor使用手册（2）-语法支持
长孙翔
Paw Index Advisor是PawSQL推出的面向数据库应用开发人员和DBA等数据库运维人员的推出的，针对MySQL、PostgreSQL、Openguass等开源数据库系统的自动化、智能化、基于代价的索引推荐工具。
上一篇（索引推荐神器Paw Index Advisor 使用手册- 配置）介绍了Paw Index Advisor的配置项及相关注意事项。本篇介绍Paw Index Advisor的支持语法，及相对应的索引结果。
根据数据库查询优化知识（深入学习SQL优化），我们知道索引的作用有如下三个：
-
快速定位数据记录（等值条件、范围条件）；
-
避免排序（order by/group by/distinct/union/sort merge join）；
-
避免回表，即只访问索引文件而不访问数据表就可以完成查询操作。
下面详细列出Paw Index Advisor针对不同的语法结构，给出的索引推荐效果，小伙伴们体会一下这个索引推荐神器的强大吧。
-
等值条件
-- 单列条件
select * from lineitem where l_shipdate = date '1998-12-01';
CREATE INDEX PAW_IDX1533504424 ON LINEITEM(L_SHIPDATE);
-- is null
select * from lineitem where l_shipmode is null;
CREATE INDEX PAW_IDX2000792795 ON LINEITEM(L_SHIPMODE);
-- in 单值
select * from lineitem where l_shipmode in ('0');
CREATE INDEX PAW_IDX2000792795 ON LINEITEM(L_SHIPMODE);
-- 多列条件，区分度大的列放在前面
select * from lineitem where l_shipdate = date '1998-12-01' and l_shipmode = '0';
CREATE INDEX PAW_IDX1942674217 ON LINEITEM(L_SHIPDATE,L_SHIPMODE);
-- 单值子查询
select * from lineitem where l_shipdate = (select max(l_shipdate) from lineitem);
CREATE INDEX PAW_IDX1533504424 ON LINEITEM(L_SHIPDATE);-
范围条件
-- 范围条件>、<、>=、<=
select * from lineitem where l_shipdate >= date '1998-12-01';
CREATE INDEX PAW_IDX0156881833 ON LINEITEM(L_SHIPDATE);
-- in 多值
select * from lineitem where l_shipmode in ('0','1');
CREATE INDEX PAW_IDX2000792795 ON LINEITEM(L_SHIPMODE);
-- between ... and ...
select * from lineitem where l_shipdate between date '2010-12-01' and date '2020-12-01';
CREATE INDEX PAW_IDX1241878058 ON LINEITEM(L_SHIPDATE)；
-- Like 一个左前缀
select * from customer where c_phone like "139%";
CREATE INDEX PAW_IDX0326568991 ON CUSTOMER(C_PHONE);-
分组 - 避免排序
-- grouping
select l_shipdate, count(*) as sum_qty from lineitem group by l_shipdate;
-- 推荐的索引为
CREATE INDEX PAW_IDX1614428511 ON LINEITEM(L_SHIPDATE); -
排序 - 避免排序
-- order by
select * from lineitem order by l_shipdate limit 10;
-- 推荐的索引为
CREATE INDEX PAW_IDX1424903467 ON LINEITEM(L_SHIPDATE);-
覆盖索引 - 避免回表
-- index only
select l_shipdate, sum(l_quantity) as sum_qty from lineitem group by l_shipdate;
-- 推荐的索引为
CREATE INDEX PAW_IDX1614428511 ON LINEITEM(L_SHIPDATE，L_QUANTITY);-
语法组合：等值+范围，等值条件放在前面
-- equal + range
select * from lineitem where l_shipdate = date '1998-12-01' and l_quantity >100;
-- 推荐的索引为
CREATE INDEX PAW_IDX2048143506 ON LINEITEM(L_SHIPDATE,L_QUANTITY);-
语法组合：等值+分组+覆盖，等值条件放在前面，覆盖列放到最后
-- where+group
select l_shipdate, sum(l_quantity) as sum_qty from lineitem where l_receiptdate = '2020-01-01' group by l_shipdate;
-- 推荐的索引为
CREATE INDEX PAW_IDX2000792795 ON LINEITEM(L_RECEIPTDATE,L_SHIPDATE,l_quantity);-
多表关联
-- 内连接，两个表都可以作为驱动表
SELECT * FROM ORDERS, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY;
-- 推荐的索引为
CREATE INDEX PAW_IDX2127618499 ON ORDERS(O_ORDERKEY);
CREATE INDEX PAW_IDX0339323878 ON LINEITEM(L_ORDERKEY);
-- 内连接，两个表都可以作为驱动表
SELECT * FROM ORDERS JOIN LINEITEM ON O_ORDERKEY = L_ORDERKEY;
-- 推荐的索引为
CREATE INDEX PAW_IDX1531629550 ON ORDERS(O_ORDERKEY);
CREATE INDEX PAW_IDX1365836084 ON LINEITEM(L_ORDERKEY);
-- 外连接，外表作为驱动表，join条件作为升级为等值条件，参与索引推荐
SELECT * FROM ORDERS LEFT JOIN LINEITEM ON O_ORDERKEY = L_ORDERKEY;
-- 推荐的索引为
CREATE INDEX PAW_IDX1336974557 ON LINEITEM(L_ORDERKEY);
SELECT * FROM ORDERS RIGHT JOIN LINEITEM ON O_ORDERKEY = L_ORDERKEY;
-- 推荐的索引为
CREATE INDEX PAW_IDX1002609246 ON ORDERS(O_ORDERKEY);
SELECT * FROM ORDERS LEFT JOIN LINEITEM ON O_ORDERKEY = L_ORDERKEY WHERE l_shipdate = date '1998-12-01';
-- 推荐的索引为
CREATE INDEX PAW_IDX0711368375 ON LINEITEM(L_ORDERKEY,L_SHIPDATE);-
DT子查询
-- Derived table，不同的查询块分别进行索引推荐，然后进行合并去重
select *
from supplier,(select l_suppkey, sum(l_extendedprice) as total_revenue from lineitem group by l_suppkey) revenue
where s_suppkey = revenue.l_suppkey;
-- 推荐的索引为
CREATE INDEX PAW_IDX0151075817 ON SUPPLIER(S_SUPPKEY);
CREATE INDEX PAW_IDX1968327707 ON LINEITEM(L_SUPPKEY,L_EXTENDEDPRICE);-
条件子查询
-- 条件子查询
select *
from
supplier
where
s_suppkey = (select l_suppkey from lineitem order by l_suppkey desc limit 1);
-- 推荐的索引为
CREATE INDEX PAW_IDX0664775210 ON SUPPLIER(S_SUPPKEY);
CREATE INDEX PAW_IDX1518532301 ON LINEITEM(L_SUPPKEY);-
CTE子查询
-- CTE
with revenue as
(select l_suppkey, sum(l_extendedprice) as total_revenue from lineitem group by l_suppkey)
select *
from
supplier,
revenue
where
s_suppkey = l_suppkey;
-- 推荐的索引为
CREATE INDEX PAW_IDX0343576594 ON SUPPLIER(S_SUPPKEY);
CREATE INDEX PAW_IDX1518532301 ON LINEITEM(L_SUPPKEY,L_EXTENDEDPRICE);-
传递闭包重写-重写后进行索引推荐
-- 传递闭包，重写后进行索引推荐
select o_custkey as cust_no, l_extendedprice * (1 - l_discount)from orders, lineitemwhere l_orderkey = o_orderkeyand l_orderkey = 'ORD1234';
-- 重写为，orders表上新增一个等值条件o_orderkey = 'ORD1234'
select o_custkey as cust_no, l_extendedprice * (1 - l_discount)from orders, lineitemwhere l_orderkey = o_orderkeyand l_orderkey = 'ORD1234'and o_orderkey = 'ORD1234';
-- 推荐的索引为CREATE INDEX PAW_IDX0837835805 ON ORDERS(O_ORDERKEY,O_CUSTKEY);CREATE INDEX PAW_IDX1989932894 ON LINEITEM(L_ORDERKEY,L_DISCOUNT,L_EXTENDEDPRICE);-
Ordinal重写-重写后进行索引推荐
-- orderby ordinal 重写
select L_SHIPDATE， count(*) as cnt, sum(bal) as bal from lineitem order by 1
-- 重写后的sql为
select L_SHIPDATE， count(*) as cnt, sum(bal) as bal from lineitem order by L_SHIPDATE
-- 推荐的索引为
CREATE INDEX PAW_IDX1424903467 ON LINEITEM(L_SHIPDATE);
-- groupby ordinal 重写
select l_shipdate, sum(l_quantity) as sum_qty from lineitem group by 1
-- 重写后的SQL为
select l_shipdate, sum(l_quantity) as sum_qty from lineitem group by l_shipdate
-- 推荐的索引为
CREATE INDEX PAW_IDX1614428511 ON LINEITEM(L_SHIPDATE,L_QUANTITY);-
DELETE语句
delete from lineitem where l_shipdate = date '1998-12-01';
CREATE INDEX PAW_IDX1533504424 ON LINEITEM(L_SHIPDATE);-
UPDATE语句
update lineitem set l_shipmode='' where l_shipmode is null;
CREATE INDEX PAW_IDX2000792795 ON LINEITEM(L_SHIPMODE);-
INSERT语句
insert into lineitem select * from lineitem where l_shipmode in ('0');
CREATE INDEX PAW_IDX2000792795 ON LINEITEM(L_SHIPMODE);-
Merge语句
replace into lineitem select * where l_shipdate = date '1998-12-01' and l_shipmode = '0';
CREATE INDEX PAW_IDX1942674217 ON LINEITEM(L_SHIPDATE,L_SHIPMODE);PawSQL专注数据库性能优化，有兴趣的小伙伴请在微信搜索关注公众号PawSQL或许最新分享。Paw Index Advisor已登录Jetbrains Marketplace和Eclipse Marketplace, 小伙伴可以通过名称搜索“Paw Index Advisor”安装。
类似资料：