索引推荐神器Paw Index Advisor使用手册(2)-语法支持

PawSQL 2022-05-10

490

PawSQL推出的针对MySQL、PostgreSQL、Openguass等开源数据库系统的索引推荐工具Paw Index Advisor支持ANSI标准的DML语法，并对其结构进行分析推荐索引以提升数据库查询性能。

在时间都去哪儿了 - 深入了解SQL优化中，我们知道索引的作用有如下三个：

快速定位数据记录（等值条件、范围条件）；
避免排序（order by/group by/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,  lineitem
where l_orderkey = o_orderkey and l_orderkey = 'ORD1234';
-- 重写为，orders表上新增一个等值条件o_orderkey = 'ORD1234'
select  o_custkey as cust_no,  l_extendedprice * (1 - l_discount)
from  orders,  lineitem
where l_orderkey = o_orderkey 
and 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专注数据库性能优化工具研发，有兴趣的小伙伴请关注公众号，

Paw Index Advisor已登录Jetbrains Marketplace和Eclipse Marketplace, 小伙伴可以在应用市场通过名称搜索“Paw Index Advisor”安装。关于Paw Index Advisor完整的使用手册请点击下方的原文连接。

select 索引 mysql索引

文章转载自PawSQL，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

索引推荐神器Paw Index Advisor使用手册(2)-语法支持

评论