一站式SQL优化工具PawSQL Advisor

PawSQL 2022-08-09

1336

概述

在推出针对MySQL、PostgreSQL等开源数据库的智能索引推荐工具Paw Index Advisor之后，PawSQL近日推出了一站式的SQL性能优化工具PawSQL Advisor。PawSQL Advisor除了集成了Paw Index Advisor强大的索引推荐功能外，还把业界关于关系数据库查询优化最佳实践整合其中，从正确性和性能两个方面对SQL进行审查和重写优化，一站解决SQL性能优化问题。

Note：PawSQL Advisor已在IntelliJ应用市场上线，有兴趣的朋友请关注PawSQL公众号。

功能概述

PawSQL Advisor的功能框架如下，

基于规则的SQL审查，包括正确性审查和性能优化审查规则。
基于规则的重写优化，推荐语义等价、但执行效率更高的SQL。
智能索引推荐，满足各种SQL语法组合的场景下，推荐最优的索引组合。
优化验证，确保基于SQL重写和索引推荐的新方案有着更好的性能。

优化规则

正确性规则

=null
或是case when null
并不能判断表达式为空,判断表达式为空应该使用is null
（PawSQL 提供重写优化）
NOT IN
子查询且选择列可空，这种情况不会返回任何记录（PawSQL 提供重写优化）
ALL
修饰的条件子查询且选择列为空，这种情况不会返回任何记录（PawSQL 提供重写优化）
使用limit
的update语句可能导致非预期的操作结果（PawSQL提示预警）
未使用 ORDER BY
的 LIMIT
查询，可能导致多次执行的结果不一致（PawSQL提示预警）
使用没有where
条件、或where 1=1
等变相没有条件的update/merge/delete语句（PawSQL提示预警）
insert 语句必须指定column, 减少插入列与结果表错位的可能性（PawSQL提示预警）

性能优化规则

避免使用全模糊搜索或左模糊搜索；无法利用索引快速定位记录（PawSQL提示预警）
对条件字段使用函数操作；索引无法使用，导致全表扫描（PawSQL对部分函数提供重写优化，提示预警）
条件字段存在数值和字符的隐式转换；索引无法使用，导致全表扫描（PawSQL提示预警）
不建议在非exists
语句里使用select *
语法；影响后续的优化逻辑，而且难以维护（PawSQL提示预警）
避免使用max/min
标量子查询；可以进行重写以利用B+树有序性来避免排序和访问不必要的数据（PawSQL 提供重写优化）
标量子查询(count(*)>0
或count(col)>0
)应该改写为exists
；避免访问不必要的数据（PawSQL 提供重写优化）
用union all
代替union
；union
需要分组去重，影响性能（PawSQL提示预警）
delete/update 包含or条件,可以拆成多个独立的update/delete语句; 包含or的条件可能没有办法利用索引（PawSQL 提供重写优化）
or语句使用union/union all代替; 包含or的条件可能没有办法利用索引（PawSQL 提供重写优化）
条件下推，先过滤，后分组（having
子句放到where
子句里），提升性能（PawSQL 提供重写优化）
delete/update 语句不能有order by
而没有limit
；没有Limit
的排序没有意义，但是会使性能变差（PawSQL 提供重写优化）
使用没有where条件、或where 1=1
等变相没有条件的查询语句,且没有分组聚集和limit语句（PawSQL 提供重写优化）
嵌套查询超过指定阈值（默认值为大于等于2）；太复杂的SQL影响plan的性能，且容易产生性能较差的执行计划（PawSQL提示预警）
使用JOIN连接表查询数目建议不超过阈值（默认值为大于等于5）；太复杂的SQL影响plan的产生的性能，且容易产生性能较差的执行计划（PawSQL提示预警）
谨慎使用select ... for update
；for update
锁表或锁记录（PawSQL提示预警）
单条insert语句，建议批量插入不超过阈值（默认为500）（PawSQL提示预警）

安装使用

插件安装

和Paw Index Advisor一样，PawSQL Advisor也以常用IDE的插件提供服务，对于基于IntelliJ的IDE，

启动IntelliJ集成开发环境，打开项目Setting，选择Plugins；
在Marketplace输入"PawSQL Advisor"，点击安装；
在Setting页配置PawSQL Advisor的运行参数；
右键点击需要进行分析的SQL文件或是文件夹，点击“Run PawSQL Advisor”;

执行结果解读

整体优化建议（pawTuningSummary.txt）

查看pawTuningSummary文件获取优化的整体信息，主要包括四部分。

整体优化概要
规则审查情况
推荐索引信息
单sql优化概要

/********************************************************************\
*                                                                   *
*               PawSQL Optimization Summary                       *
*                                                                   *
*                 Powered by PawSQL(2022- )                         *
*                                                                   *
\********************************************************************/
1. Optimization Summary
- There are 104 query analyzed;
- There are 3 optimization rewrites for 2 queries;
- There are 207 violations of optimization rule against 68 queries;
- There are 79 indices recommended for 76 queries;
- There is 7151.69% performance improvement.


2. Optimization Rule Violations
- RuleCntGtThanZeroRewrite: [query41.sql-stmt1]
- RuleFuncWithColumnInPredicate: [query8.sql-stmt1, query64.sql-stmt1]
- RuleNoCond4NonAggSelect: [query8.sql-stmt1, query33.sql-stmt1, query77.sql-stmt1]
- RuleNumOfJoinTables: [query46.sql-stmt1, query84.sql-stmt1 ...]
...


3. Recommended Indices
- CREATE INDEX PAW_IDX0571264614 ON TPDS.ITEM(I_CURRENT_PRICE,I_ITEM_SK,I_ITEM_ID);
- CREATE INDEX PAW_IDX0205789795 ON TPDS.ITEM(I_CATEGORY,I_CURRENT_PRICE);
- CREATE INDEX PAW_IDX0866411600 ON TPDS.ITEM(I_MANUFACT_ID,I_PRODUCT_NAME);
- CREATE INDEX PAW_IDX1917304708 ON TPDS.ITEM(I_MANAGER_ID,I_ITEM_SK);
...


4. Optimized Query List
- query1.sql-stmt1, performance improves by 438790.26%[costBefore=71231.89,costAfter=16.23]
-- Contributing indices:[PAW_IDX0410610163, PAW_IDX0827928186, <AUTO_KEY0>, PRIMARY, PRIMARY]


- query10.sql-stmt1, performance improves by 3974905.54%[costBefore=1097101.53,costAfter=27.59]
-- Contributing indices:[PAW_IDX0410610163, PAW_IDX0426775377, PRIMARY, PAW_IDX0410610163, PAW_IDX0127412485, PAW_IDX0410610163, PAW_IDX1696683664, PRIMARY, PRIMARY]


- query11.sql-stmt1, performance improves by 28.56%[costBefore=322948.99,costAfter=251195.7199]
-- Contributing indices:[PAW_IDX0410610163, PAW_IDX2001631254, PRIMARY, PAW_IDX1991665978, PRIMARY, PRIMARY]
...

单SQL优化详情

主要包括：

原始输入SQL
改写后的SQL
规则审查情况
索引推荐情况
What-if Analysis（性能验证）

性能提升比
优化之前的执行计划
优化之后的执行计划

/********************************************************************\
*                                                                   *
*             SQL Tuning(Query and Index) Details                 *
*                                                                   *
*               Powered by PawSQL(2022 - )                         *
*                                                                   *
\*******************************************************************/
1. Original Query(formatted)
select c_orders.c_count, count(*) as custdist
 from (select customer.c_custkey, count(orders.o_orderkey) as c_count
          from TPCH.customer left outer join TPCH.orders on (customer.c_custkey = orders.o_custkey
   and orders.o_comment not like '%pending%deposits%')
          group by customer.c_custkey) as c_orders
 group by c_orders.c_count
 order by custdist desc, c_orders.c_count desc


2. Rewritten Query


3. Auditing Rule Violations
- Rule:RulePredicateLikeStartWithWildcard
- Violation Parts:
orders.o_comment not like '%pending%deposits%';


4. Index Tuning
- CREATE INDEX PAW_IDX2038863975 ON TPCH.CUSTOMER(C_CUSTKEY,C_NAME(16));
- CREATE INDEX PAW_IDX0890500073 ON TPCH.ORDERS(O_CUSTKEY,O_COMMENT(16),O_ORDERKEY);


5. Validation details
5.1 Performance improves by 69246.63%[cost_before=8.089240324150001E9,after_cost=1.166493588E7]
5.2 Query Plan(before)
-> Sort: custdist DESC, c_orders.c_count DESC
   -> Table scan on <temporary>
       -> Aggregate using temporary table
           -> Table scan on c_orders  (cost=2.50..2.50 rows=0)
               -> Materialize  (cost=2.50..2.50 rows=0)
                   -> Table scan on <temporary>
                       -> Aggregate using temporary table
                           -> Left hash join (orders.O_CUSTKEY = customer.C_CUSTKEY)  (cost=8089149945.61 rows=80891468955)
                               -> Table scan on customer  (cost=90373.50 rows=795135)
                               -> Hash
                                   -> Filter: (not((orders.O_COMMENT like '%pending%deposits%')))  (cost=0.02 rows=101733)
                                       -> Table scan on orders  (cost=0.02 rows=101733)


5.3 Query Plan(after)
-> Sort: custdist DESC, c_orders.c_count DESC
   -> Table scan on <temporary>
       -> Aggregate using temporary table
           -> Table scan on c_orders  (cost=0.01..102760.96 rows=8220678)
               -> Materialize  (cost=4611746.21..4714507.16 rows=8220678)
                   -> Group aggregate: count(orders.O_ORDERKEY)  (cost=3789678.43 rows=8220678)
                       -> Nested loop left join  (cost=2967610.67 rows=8220678)
                           -> Index scan on customer using PAW_IDX2038863975  (cost=90373.50 rows=795135)
                           -> Filter: (not((orders.O_COMMENT like '%pending%deposits%')))  (cost=2.58 rows=10)
                               -> Index lookup on orders using PAW_IDX0890500073 (O_CUSTKEY=customer.C_CUSTKEY)  (cost=2.58 rows=10)

对PawSQL Advisor感兴趣的小伙伴请在公众号申请试用。PawSQL专注数据库性能优化工具研发，有兴趣的小伙伴请关注公众号，

sql sql优化 table mysql索引

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