索引推荐神器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”安装。