問題
生產某些功能執行較慢，找出來對應的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，要關注資料量的變化對索引的影響，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/205101.html
標籤：其他
上一篇：O(1) 時間插入、洗掉和獲取隨機元素，允許元素重復
下一篇：SQL中哪些情況會引起全表掃描