mysql + left join

一、mysql left join 的原則上有兩點

    1、左表為小表
    2、右表的關聯欄位是索引 

      （無視以上兩點，一般不要用left join~~！ ）

遇到的問題：右表（c）是大表，分別 有兩個小表(a和b)

當 a left join c 的時候可以使用上c的關聯欄位索引，

但是b left join c 的時候無法使用上索引，全文搜尋了，

兩個小表的結構表面看起來一樣，後面使用 show full columns from [tables]

發現不能使用索引的 b,c 表的 關聯欄位字符集 不一樣

 

二、left join 情況下 使用 order  by 排序

mysql在多個left join的情況下使用order by排序，就算是其中一個表的主鍵也仍然使用file sort排序，資料量多的話就相當的慢。

優化前語句

select * from a left join b on a.id=b.a_id order a.id desc

優化後語句

select * from a left join b on a.id=b.a_id join (select id from a order by id desc) a_order on a.id = a_order.id

實際工作中100W+的幾個表使用LEFT JOIN要20分鐘才能得到結果，語句優化後3秒。

 

三、索引失效的部分總結

1、對單欄位建了索引，where條件多欄位。

例：建了以下索引：

查詢語句：

select * from template t  where t.logicdb_id = 4 and t.sync_status = 1

2、建立聯合索引，where條件單欄位。與上面情況正好相反。

例：建了以下索引：

查詢語句：

select * from template t  where t.sync_status = 4

3、對索引列運算，運算包括（+、-、*、/、！、<>、%、like'%_'（%放在前面）、or、in、exist等），導致索引失效。

4、型別錯誤，如欄位型別為varchar，where條件用number。

例：template_id欄位是varchar型別。

錯誤寫法：select * from template t where t.template_id = 1

正確寫法：select * from template t where t.template_id = '1'

5、對索引應用內部函式，這種情況下應該建立基於函式的索引。

例：select * from template t  where ROUND(t.logicdb_id) = 1

此時應該建ROUND(t.logicdb_id)為索引。

6、查詢表的效率要比應用索引查詢快的時候。

7、is null 索引失效；is not null Betree索引生效。導致的原因，個人認為應該是，mysql沒有在null寫進索引。還要看應用的資料庫而定。

 

四、mysql犧牲了group by來增加left join的速度（前提是加了索引）。

        https://blog.csdn.net/zhangjq520/article/details/73836042