一站式SQL优化工具PawSQL Advisor

VIP课程网赚项目分享☞☞☞点击☞☞☞  资源网

概述在推出针对MySQL、PostgreSQL等开源数据库的智能索引推荐工具PawIndexAdvisor之后,PawSQL近日推出了一站式的SQL性能优化工具PawSQLAdvisor。PawSQLAdv

概述

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

Note:PawSQL Advisor已在IntelliJ应用市场上线,有兴趣的朋友请下载试用。

功能概述

PawSQL Advisor的功能框架如下,

一站式SQL优化工具PawSQL Advisor

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

优化规则

正确性规则

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

性能优化规则

  • 避免使用全模糊搜索或左模糊搜索;无法利用索引快速定位记录(PawSQL提示预警)
  • 对条件字段使用函数操作;索引无法使用,导致全表扫描(PawSQL对部分函数提供重写优化,提示预警)
  • 条件字段存在数值和字符的隐式转换;索引无法使用,导致全表扫描(PawSQL提示预警)
  • 不建议在非exists语句里使用select *语法;影响后续的优化逻辑,而且难以维护(PawSQL提示预警)
  • 避免使用max/min标量子查询;可以进行重写以利用B+树有序性来避免排序和访问不必要的数据(PawSQL 提供重写优化
  • 标量子查询(count(*)>0count(col)>0)应该改写为exists;避免访问不必要的数据(PawSQL 提供重写优化
  • union all代替unionunion需要分组去重,影响性能(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 updatefor update锁表或锁记录(PawSQL提示预警)
  • 单条insert语句,建议批量插入不超过阈值(默认为500)(PawSQL提示预警)

索引推荐

索引的作用有如下三个:

  • 快速定位数据记录(等值条件、范围条件);
  • 避免排序(order by/Group by/distinct/union/sort merge join);
  • 避免回表,即只访问索引文件而不访问数据表就可以完成查询操作。

PawSQL Advisor继承了Paw Index Advisor的智能索引推荐能力,它针对不同的语法结构,结合系统的数据字典、统计信息,分析评估所有的索引可选项,基于启发式算法和基于代价的算法,进行索引的推荐。具体信息请参考Paw Index Advisor的相关介绍。

优化验证(What-if)

PawSQL Advisor提供一个对推荐出来的SQL和索引进行验证的功能,它连接到一个测试库,并收集推荐之前、执行之后的执行计划及估算的代价等信息,对推荐信息进行验证,并把验证后有效的推荐呈现给用户。所以最终我们可以在最终的推荐结果里看到:

  1. 整体性能提升百分比(Overall performance inprovemement)
  2. 单个SQL的性能提升百分比(Performance improvement for single sql)
  3. 单个SQL优化前的执行计划(Query execution plan before tuning)
  4. 单个SQL优化后的执行计划(Query execution plan after tuning)

同时,所有推荐的索引都是在某个SQL的执行计划中出现过,并对此SQL的性能有帮助的。

插件安装

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

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

执行结果解读

整体优化建议(pawTuningSummary.txt)

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

  1. 整体优化概要
  2. 规则审查情况
  3. 推荐索引信息
  4. 单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优化详情

主要包括:

  1. 原始输入SQL
  2. 改写后的SQL
  3. 规则审查情况
  4. 索引推荐情况
  5. 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专注数据库性能优化工具研发,有兴趣的小伙伴请关注公众号 PawSQL,

一站式SQL优化工具PawSQL Advisor

VIP课程网赚项目分享☞☞☞点击☞☞☞  资源网

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 470473069@qq.com 举报,一经查实,本站将立刻删除。