记一次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

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