前言

在公司一次版本迭代中，有多了新的table還有新的欄位，因此有舊資料跟新資料之間要過度，原本用來join on的欄位也要跟著換

原本的如下

1
on student.id = player.id 

我為了要兼容舊資料，沒有動太多心思，就想說用or去處理就好

1
on student.id = player.id or member.id = player.id

就傻傻的用or去處理了。

gif

服務器爆炸

這個語法普遍出現在我所有需要做新舊資料兼容的SQL中，當時在寫的時候想說自己真是牛逼，多加了幾個字就解決兼容性的問題，真是個不世出的天才

niubi-quentin-gif

沒想到這段Code被發佈到正式環境時，就出現了jdbc連線超時的問題。

1
druid.pool.DataSourceClosedException

後來大家開始排查問題，想說為什麼jdbc會突然出問題，大概找了一個多小時，公司裡面一個大佬說

「這個sql有問題，有or導致整張表的查詢時間增加太多，就是這些or語法導致sql在查詢時耗時太久，因為測試環境的資料相對較少，所以這問題測試環境看不出來」

後來搜尋了一下，發現最主要的問題在於說where搭配or搜尋，會導致索引失效

原因

參考至SQL中使用or影响性能的解决办法的解決方案，裡面內文有說到，在where中使用or的情形中，可能會導致SQL放棄使用index，轉而去進行逐表Full Scan，如果是不會增長的資料還好，但會員數是會增長的，正式環境可能有上萬筆的會員資料，就等於SQL在執行時會像個白癡一樣傻傻的去找對應的資料，這件事情的複雜度是O(n)，如果真的有需要用到or的需求，也應該用union來替代，用or會導致SQL搜尋直接炸掉。

MySQL Explain

以下用我自己side Project的table來示範，這張table有兩個index，id跟stock_code

在MySQL中，我們可以在一個搜尋語句前面加上explain，來查看SQL語句的健檢報告

1
select * from daily_stock_data;

使用explain就像這樣

1
explain  select * from daily_stock_data;

使用explain時，執行的就不是這個sql所執行的結果，而是這個sql執行下來的一些指標

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	daily_stock_data	null	ALL	null	null	null	null	10428	100	null

我們可以注意possible_keys跟key，而possible_keys代表可能會使用到的index，key就代表實際有使用到的index，在這個搜尋中，我們基本上沒有用到任何的index（也很正常，畢竟我們本來就沒用到任何條件搜尋）

那我們接下來用stock_code來搜尋看看

1
explain  select stock_code from daily_stock_data where '2330'

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	daily_stock_data	null	index	null	idx_stock_code	403	null	10428	100	Using index

會發現我們的key有值了，就代表這次的搜尋我們有用到index去處理。

那我們接下來執行一個where搭配or的搜尋

1
explain  select stock_code from daily_stock_data where  date = '112/01/04' or stock_code = '2330';

在這個情況下，index就失效了，如果想要or搜尋能夠為使用索引，最簡單的方式就是將你要作為條件的兩個列都加上index，就能解決這個全表搜尋性能下降的問題

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	daily_stock_data	null	ALL	idx_stock_code	null	null	null	10428	11.91	Using where

關於explain的內容，可以查看什麼？我寫的一條SQL讓公司網站癱瘓了…SQL慢查詢改善來了解更多

可能導致索引失效的操作

全模糊搜尋跟左模糊搜尋(’％ABC％‘, ‘%ABC’) ，右模糊搜尋是不會影響到的(‘ABC%')
is null
Where 配上 or
in跟 not in ，可以使用between替代
使用select * 也會放棄index搜尋

更詳細的內容可以參考会引起全表扫描的几种SQL 以及sql优化（转）

參考資料

SQL中使用or影响性能的解决办法

SQL中使用or影响性能的解决办法转载

SQL UNION vs OR 性能原创

什麼？我寫的一條SQL讓公司網站癱瘓了…SQL慢查詢改善

会引起全表扫描的几种SQL 以及sql优化（转）

在SQL中使用or是一件非常危險的事情

在撈資料的時候，要省慎注意or的使用

前言

服務器爆炸

原因

MySQL Explain

可能導致索引失效的操作

參考資料