记一次SQL优化

业务查询缓慢,要求优化查询速度,优化步骤一一枚举出来,防止忘了。

 

总结:

  1、尽量让SQL都使用索引,不要存在in这种不走索引的写法

  2、适当的时候,可以将子查询优化成为表连接

 

原SQL:

SELECT
 tab2.quoteOrderId,
 tab2.quoteOrderNumber,
 tab2.userUpdateTpye,
 tab2.quoteTitle,
 DATE_FORMAT( tab2.quoteClosingDate, '%Y-%m-%d' ) AS quoteClosingDate,
 DATE_FORMAT( tab2.expectedReceivingDate, '%Y-%m-%d' ) AS expectedReceivingDate,
 tab2.totalPrice,
 tab2.quoteDate,
 tab2.user_id AS clientCompany,
 tab2.type 
FROM
 (
 SELECT
  tab.quoteOrderId,
  tab.quoteOrderNumber,
  tab.userUpdateTpye,
  tab.quoteTitle,
  tab.quoteClosingDate,
  tab.expectedReceivingDate,
  tab.totalPrice,
  tab.quoteDate,
  tab.user_id,
  ( CASE WHEN TAB.countNum = TAB.notApprovedCount THEN 0 WHEN TAB.countNum = adoptCount THEN 2 ELSE 1 END ) AS type 
 FROM
  (
  SELECT
   t1.id AS quoteOrderId,
   t1.quote_order_number AS quoteOrderNumber,
   t1.user_update_tpye AS userUpdateTpye,
   t1.quote_title AS quoteTitle,
   t1.quote_closing_date AS quoteClosingDate,
   t1.expected_receiving_date AS expectedReceivingDate,
   SUM( CASE WHEN t3.approval_status IS NULL THEN 1 ELSE 0 END ) AS notApprovedCount,
   SUM( CASE t3.approval_status WHEN '1' THEN 1 ELSE 0 END ) AS adoptCount,
   SUM( CASE t3.approval_status WHEN '2' THEN 1 ELSE 0 END ) AS refuseCount,
   count( 1 ) countNum,
   SUM(
   t2.amount * IFNULL( t3.bj_unit_price_including_tax, 0 )) AS totalPrice,
   t1.quote_date AS quoteDate,
   t4.user_id 
  FROM
   quote_order_info t1
   LEFT JOIN quote_order_pro_info t2 ON t1.id = t2.quote_order_info_id
   LEFT JOIN quote_bj_order_pro_info t3 ON t3.id IN ( SELECT MAX( id ) FROM quote_bj_order_pro_info WHERE quote_order_pro_info_id = t2.id GROUP BY quote_order_pro_info_id ) 
   AND t3.approval_status = 1
   LEFT JOIN quote_order_user_info t4 ON t4.quote_order_info_id = t1.id 
  GROUP BY
   t1.id,
   t4.user_id 
  ) AS tab 
 ) AS tab2

 

简单理解sql,业务sql嵌套较多,拆分sql,分段,感觉外层无法优化,也就是说优化点要集中在内层sql:

SELECT
   t1.id AS quoteOrderId,
   t1.quote_order_number AS quoteOrderNumber,
   t1.user_update_tpye AS userUpdateTpye,
   t1.quote_title AS quoteTitle,
   t1.quote_closing_date AS quoteClosingDate,
   t1.expected_receiving_date AS expectedReceivingDate,
   SUM( CASE WHEN t3.approval_status IS NULL THEN 1 ELSE 0 END ) AS notApprovedCount,
   SUM( CASE t3.approval_status WHEN '1' THEN 1 ELSE 0 END ) AS adoptCount,
   SUM( CASE t3.approval_status WHEN '2' THEN 1 ELSE 0 END ) AS refuseCount,
   count( 1 ) countNum,
   SUM(
   t2.amount * IFNULL( t3.bj_unit_price_including_tax, 0 )) AS totalPrice,
   t1.quote_date AS quoteDate,
   t4.user_id 
  FROM
   quote_order_info t1
   LEFT JOIN quote_order_pro_info t2 ON t1.id = t2.quote_order_info_id
   LEFT JOIN quote_bj_order_pro_info t3 ON t3.id IN ( SELECT MAX( id ) FROM quote_bj_order_pro_info WHERE quote_order_pro_info_id = t2.id GROUP BY quote_order_pro_info_id ) 
   AND t3.approval_status = 1
   LEFT JOIN quote_order_user_info t4 ON t4.quote_order_info_id = t1.id 
  GROUP BY
   t1.id,
   t4.user_id 

 

原sql速率:12.4s

发现问题1:

 

 

 圈出来的这个sql,in可以改为=号, 原因:分组条件是quote_order_pro_info_id ,且quote_order_pro_info_id有限制= t2.id,也就是说这个group by是多此一举,聚合得到的值只有一个

修正sql后:

LEFT JOIN quote_bj_order_pro_info t3 ON t3.id = ( SELECT MAX( id ) FROM quote_bj_order_pro_info WHERE quote_order_pro_info_id = t2.id )

查询速率:0.175s

 

优化点2:子查询优化为表连接

子查询会导致外层查询使用不到索引

提取步骤:

  提取到与t3同级,也就是与t2同级,使用的t2的条件就应该变为join 的on 条件,因此除了查询出max(id)之外,分组条件 quote_order_pro_info_id 也要查询出来作为连接条件

  t3 的id = 查询列的maxId,则是t3关联t5的关联列在on中体现

修正后sql:

   left join (SELECT MAX(id) maxId, quote_order_pro_info_id  FROM quote_bj_order_pro_info group by quote_order_pro_info_id) t5 on t5.quote_order_pro_info_id = t2.id
   LEFT JOIN quote_bj_order_pro_info t3 ON t3.id = t5.maxId 
   AND t3.approval_status = 1

查询速率:0.068s

 

 

欢迎朋友有有趣的案例发表出来一起探讨

  

posted @ 2021-04-14 14:47  guodaxia  阅读(74)  评论(0编辑  收藏  举报