這個(gè)虧已經(jīng)吃過(guò)很多次了，在開發(fā)以前的sql代碼里面，許多以 or 作為where條件的查詢，甚至更新。這里舉例來(lái)說(shuō)明使用 or 的弊端，以及改進(jìn)辦法。

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

從查詢語(yǔ)句很容易看出，f_mobile和f_phone兩個(gè)字段都有可能存電話號(hào)碼，一般思路都是用 or 去一條sql解決，但表數(shù)據(jù)量一大簡(jiǎn)直是災(zāi)難：

t_tbanme1上有索引 idx_id_mobile(f_xxx_id,f_mobile) , idx_phone(f_phone) , idx_id_email(f_id,f_email) ，explain 的結(jié)果卻使用了 idx_id_email 索引，有時(shí)候運(yùn)氣好可能走 idx_id_mobile f_xxx_id

因?yàn)閙ysql的每條查詢，每個(gè)表上只能選擇一個(gè)索引。如果使用了 idx_id_mobile 索引，恰好有一條數(shù)據(jù)，因?yàn)橛?limit 1 ，那么恭喜很快得到結(jié)果；但如果 f_mobile 沒(méi)有數(shù)據(jù)，那 f_phone 字段只能在f_id條件下挨個(gè)查找，掃描12w行。 or 跟 and 不一樣，甚至有開發(fā)認(rèn)為添加 (f_xxx_id,f_mobile,f_phone) 不就完美了嗎，要吐血了~

<!-- more -->

那么優(yōu)化sql呢，很簡(jiǎn)單（ 注意f_mobile,f_phone上都要有相應(yīng)的索引 ）， 方法一 ：

(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 )

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

還有一種優(yōu)化辦法，如果這種查詢特別頻繁（又無(wú)緩存），改成單獨(dú)的sql執(zhí)行，比如大部分號(hào)碼值都在f_mobile上，那就先執(zhí)行分sql1，有結(jié)果則結(jié)束，判斷沒(méi)有結(jié)果再執(zhí)行分sql2 ，能減少數(shù)據(jù)庫(kù)查詢速度，讓代碼去處理更多的事情， 方法二 偽代碼：

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();

復(fù)雜一點(diǎn)的場(chǎng)景是止返回一條記錄那么簡(jiǎn)單，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

這種情況方法一、二都需要改造，因?yàn)?f_create_time，f_modify_time 都可能均滿足判斷條件，這樣就會(huì)返回重復(fù)的數(shù)據(jù)。

方法一需要改造：

(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 )

有人說(shuō) 把 UNION ALL 改成 UNION 不就去重了嗎？如果說(shuō)查詢比較頻繁，或者limit比較大，數(shù)據(jù)庫(kù)還是會(huì)有壓力，所以需要做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.countif 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條件在數(shù)據(jù)庫(kù)上很難優(yōu)化，能在代碼里優(yōu)化邏輯，不至于拖垮數(shù)據(jù)庫(kù)。只有在 or 條件下無(wú)需索引時(shí)（且需要比較的數(shù)據(jù)量小），才考慮。

相同字段 or 可改成 in，如 f_id=1 or f_id=100 -> f_id in (1,100) 。 效率問(wèn)題見文章 mysql中or和in的效率問(wèn)題 。

上述優(yōu)化情景都是存儲(chǔ)引擎在 InnoDB 情況下，在MyISAM有不同，見 mysql or條件可以使用索引而避免全表 。

來(lái)自： https://segmentfault.com/a/1190000005644978