MySQL避免索引列使用 OR 條件

這個虧已經吃過很多次了，在開發以前的sql程式碼裡面，許多以 or 作為where條件的查詢，甚至更新。這裡舉例來說明使用 or 的弊端，以及改進辦法。

select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and (f_mobile ='1234567891' or f_phone ='1234567891' ) limit 1

從查詢語句很容易看出，f_mobile和f_phone兩個欄位都有可能存電話號碼，一般思路都是用or 去一條sql解決，但表資料量一大簡直是災難：

t_tbanme1上有索引idx_id_mobile(f_xxx_id,f_mobile) , idx_phone(f_phone) , idx_id_email(f_id,f_email) ，explain 的結果卻使用了idx_id_email(f_id,f_email) ，explain 的結果卻使用了idx_id_email 索引可能走idx_id_mobile f_xxx_id

因為mysql的每個查詢，每個表上只能選擇一個索引。如果使用了 idx_id_mobile 索引，恰好有一條數據，因為有 limit 1 ，那麼恭喜很快得到結果；但如果 f_mobile 沒有數據，那 f_phone 字段只能在f_id條件下挨個查找，掃描12w行。或 跟and 不一樣，甚至有開發認為添加(f_xxx_id,f_mobile,f_phone) 不就完美了嗎，要吐血了~

95712e0b0bc62c64b1fddfac486f9cb8

那麼優化sql呢，很簡單（ 注意f_mobile,f_phone上都要有對應的索引 ）， 方法一 ：

(select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and f_mobile ='1234567891' limit 1 ) UNION ALL 
(select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and f_phone ='1234567891' limit 1 )

兩個獨立的sql都能用上索引，分查詢各自limit，如果都有結果集返回，隨便取一條就行。

還有最佳化方法，如果這種查詢特別頻繁（又無快取），改成單獨的sql執行，例如大部分號碼值都在f_mobile上，那就先執行分sql1，有結果則結束，判斷沒有結果再執行分sql2 ，能減少資料庫查詢速度，讓程式碼去處理更多的事情， 方法二 偽代碼：

sql1 = select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and f_mobile ='1234567891' limit 1;
sq1.execute();
if no result sql1:
  sql1 = select f_crm_id from d_dbname1.t_tbname1 where f_xxx_id = 926067 and f_phone ='1234567891' limit 1;
    sql1.execute();

複雜一點的場景是止回傳一筆記錄那麼簡單，limit 2：

select a.f_crm_id from d_dbname1.t_tbname1 as a where (a.f_create_time > from_unixtime('1464397527') or a.f_modify_time > from_unixtime('1464397527') ) limit 0,200

這種情況方法一、二都需要改造，因為f_create_time，f_modify_time 都可能都滿足判斷條件，這樣就會傳回重複的資料。

方法一需要改造：

(select a.f_crm_id from d_dbname1.t_tbname1 as a where a.f_create_time > from_unixtime('1464397527') limit 0,200 ) UNION ALL
(select a.f_crm_id from d_dbname1.t_tbname1 as a where a.f_modify_time > from_unixtime('1464397527')and a.f_create_time <= from_unixtime('1464397527') limit 0,200)

有人說 把 UNION ALL 改成 UNION 不就去重了嗎？如果說查詢比較頻繁，或是limit比較大，資料庫還是會有壓力，所以需要做trade off。

這種情況比較多或適合方法二，包括有可能需要 order by limit 情況。改造偽代碼：

sql1 = (select a.f_crm_id from d_dbname1.t_tbname1 as a where a.f_create_time > from_unixtime('1464397527') limit 0,200 );
sql1.execute();
sql1_count = sql1.result.count
if sql1_count < 200 :
  sql2 = (select a.f_crm_id from d_dbname1.t_tbname1 as a where a.f_modify_time > from_unixtime('1464397527') 
  and a.f_create_time <= from_unixtime('1464397527') limit 0, (200 - sql1_count) );
  sql2.execute();

final_result = paste(sql1,sql2);

or條件在資料庫上很難優化，能在程式碼裡優化邏輯，不至於拖垮資料庫。只有在 or 條件下無需索引時（且需要比較的資料量小），才考慮。

相同欄位 or 可改成 in，如 f_id=1 or f_id=100 -> f_id in (1,100) 。 效率問題請參考文章 mysql中or和in的效率問題 。

上述最佳化情境都是儲存引擎在 InnoDB 情況下，在MyISAM有不同，請參閱 mysql or條件可以使用索引而避免全表 。

以上就是MySQL避免索引列使用 OR 條件的內容，更多相關內容請關注PHP中文網（www.php.cn）！