問題

生產某些功能執行較慢，找出來對應的sql，看看執行計劃，入下：
sql：

SELECT
 COUNT( 1 ) 
FROM
 fich_base t1
 LEFT JOIN cont_base t2 ON t1.contract_id = t2.id
 LEFT JOIN orga_base t3 ON t3.id = t1.transfer_organization_id
 LEFT JOIN user_base t4 ON t4.id = t2.sale_user_id
 LEFT JOIN empl_base t5 ON t5.id = t4.employee_id
 LEFT JOIN vehi_brand t6 ON t6.id = t2.vehicle_brand_id
 LEFT JOIN vehi_base t7 ON t2.vehicle_id = t7.id
 LEFT JOIN deal_base t8 ON t8.id = t2.dealer_id
 LEFT JOIN fich_review_node t9 ON t1.id = t9.final_check_id
 LEFT JOIN fich_review_process t10 ON t9.id = t10.review_node_id 
WHERE
 t1.obj_status = 1 
 AND t2.factory_end_date IS NOT NULL 
 AND t2.obj_status = 1 
 AND t2.number LIKE CONCAT( '%', CONCAT( 123, '%' ) ) 
 AND (
  t2.personal_name LIKE CONCAT( '%', CONCAT( 1234, '%' ) ) 
  OR t2.enterprise_name LIKE CONCAT( '%', CONCAT( 1234, '%' ) ) 
 ) 
 AND t2.vin_number LIKE CONCAT( '%', CONCAT( 123, '%' ) ) 
 AND t1.transfer_organization_id IN ( SELECT u.organization_id FROM user_rel_organization_view u WHERE u.user_id = 1 AND u.obj_status = 1 ) 
 AND t1.review_status = 2 
 AND t10.review_status = 0 
 AND t9.review_count = 1 
 AND t9.review_status = 0 
 AND timestampdiff( DAY, t2.factory_end_date, NOW( ) ) <= 30 
 AND t2.`status` != 3

執行計劃：

有說in 接子查詢會導致索引失效。

解決

調整子查詢為關聯查詢：

EXPLAIN 
SELECT
 COUNT( 1 ) 
FROM
 fich_base t1 
 LEFT JOIN cont_base t2 ON t1.contract_id = t2.id
 LEFT JOIN orga_base t3 ON t3.id = t1.transfer_organization_id
 LEFT JOIN user_base t4 ON t4.id = t2.sale_user_id
 LEFT JOIN empl_base t5 ON t5.id = t4.employee_id
 LEFT JOIN vehi_brand t6 ON t6.id = t2.vehicle_brand_id
 LEFT JOIN vehi_base t7 ON t2.vehicle_id = t7.id
 LEFT JOIN deal_base t8 ON t8.id = t2.dealer_id
 LEFT JOIN fich_review_node t9 ON t1.id = t9.final_check_id
 LEFT JOIN fich_review_process t10 ON t9.id = t10.review_node_id 
 # 這裡調整了
 left join user_rel_organization_view t11 on t1.transfer_organization_id=t11.organization_id
WHERE
 t1.obj_status = 1 
 AND t2.factory_end_date IS NOT NULL 
 AND t2.obj_status = 1 
 AND t2.number LIKE CONCAT( '%', CONCAT( 123, '%' ) ) 
 AND (
  t2.personal_name LIKE CONCAT( '%', CONCAT( 1234, '%' ) ) 
  OR t2.enterprise_name LIKE CONCAT( '%', CONCAT( 1234, '%' ) ) 
 ) 
 AND t2.vin_number LIKE CONCAT( '%', CONCAT( 123, '%' ) ) 
 # 這裡調整了
 AND t11.user_id = 1 AND t11.obj_status = 1
 AND t1.review_status = 2 
 AND t10.review_status = 0 
 AND t9.review_count = 1 
 AND t9.review_status = 0 
 AND timestampdiff( DAY, t2.factory_end_date, NOW( ) ) <= 30 
 AND t2.`status` != 3

執行計劃都走索引了

總結

對於sql，在編寫完成後，要關注執行計劃檢查，另外對於in,exist，要關注資料量的變化對索引的影響。