mysql使用in效率低下_MySQL的in查詢效率太低的解決辦法之一與其它優化示例

最近在做一個MySQL數據庫的查詢(查詢出指定時間之后凡是上傳過圖片的用戶所在的鎮和鎮的管理員名)，查詢語句如下：

SELECT DISTINCT user_name,town_name FROM t_farmers WHERE id IN

(SELECT DISTINCT farmer_id FROM t_farmers_images WHERE create_time>='2017-07-05')

其中farmers表有六千多記錄，farmers_images表有近20萬條記錄，查詢效率極低，此查詢估計能夠耗十分鍾的時間，無法忍受，於是尋找解決辦法，

網上有說加索引解決的，但是個人感覺這個數據量並不大，加索引即使能夠解決問題，等日后數據量逐漸增大之時這似乎並不是一個非常好的辦法。

網上還有說把in改為exist，但是查詢效率似乎並沒有什么改變。通過搜閱資料得知in適合用於子表小的情況，而exist適合子表大主表小的情況，(僅代表一家之言，可能有不到之處，日后細究)。

解決方法如下：

經對數據庫方面的文章參考，最終找到了一個方法，把in改為左連接右連接的方式，於是把sql語句改為如下：

SELECT DISTINCT b.user_name,b.town_name FROM (SELECT DISTINCT farmer_id FROM t_farmers_images WHERE create_time>='2017-08-18') a

LEFT JOIN t_farmers b ON a.farmer_id=b.id 查詢效率瞬間提升，幾乎感覺不到有什么延遲。

詳細的左連接，右連接，內連接等的查詢和哪種適合左邊表大，哪種適合右邊表大，哪種查詢具體適合什么情形，請自行網上查詢。

查詢2月1號之后，總數之和超過300的用戶：

SELECT mm.*,c.user_name,c.town_name,c.name,c.tel,c.card_id,c.onecard_id FROM (

SELECT a.farmer_id,SUM(death_number) FROM t_farmers_details a

WHERE a.date_time>='2017-02-01' GROUP BY a.farmer_id HAVING SUM(death_number)>=300 ) mm

LEFT JOIN t_farmers c ON c.id=mm.farmer_id

此外做項目時有經常需要用到多表查詢的情況，這種情況下一般不適用笛卡爾積，因為笛卡爾積的開銷太大，查詢太慢，到現在才明白為什么兩年前別人的項目中都用左連接，右連接之類的查詢，而沒有使用笛卡爾積了，兩年了才明白別人當初的業務邏輯，還需要加把勁更加努力進步！

2017.12.07需要對已有系統進行優化，其中有一個導出Excel的功能，用戶量小的時候導出功能正常，但是用戶量大的時候導出特別慢，甚至網絡差的時候還會出現導出失敗的情況，起初以為是前端導出Excel效率低下的原因，后來經測試發現是數據庫查詢效率太差，

最初sql語句寫法為：

SELECT a.*,(SELECT SUM(death_number) death_number FROM t_farmers_details

WHERE date_time LIKE '%2017-12%'

AND farmer_id =a.id ) harmless_quantity

FROM t_farmers a WHERE 1=1

優化后sql語句寫法為：

SELECT a.*,b.harmless_quantity

FROM t_farmers a LEFT JOIN

(SELECT b.farmer_id,SUM(b.death_number) harmless_quantity FROM t_farmers_details b

WHERE b.date_time LIKE '%2017-12%' GROUP BY b.farmer_id ) b ON a.id=b.farmer_id

WHERE 1=1

核心修改時將笛卡爾積修改為了左連接，

這樣一來原來需要幾分鍾導出的一個Excel，現在只需幾秒鍾解決，特此記錄。