如何優化一條 order by 的 SQL 語句？

02-01

數據結構如下圖（表引擎myisam）：
現有一條語句：
explain SELECT sql_no_cache id, word

FROM house_keyword
WHERE (
city = bj
OR city = cn
OR city = all
)
AND `word` LIKE 你好%
ORDER BY user_count DESC
LIMIT 10
結果如下：

疑問？？？
此處為什麼還會出現額外的排序呢？(using filesort)

user_count 沒有索引的原因。

mysql在處理這種查詢的時候，都是file sort

你想用一個複合索引解決如上所有問題，又是or ，又是 like，索引沒那麼智能

我對索引的理解有一個最簡單的思路，非常好用，

就是你把這些索引欄位（複合索引的按照順序粘在一起），當做是一個有序的序列，然後你琢磨，查找的時候，能否有二分法和連續區間命中，如果能，索引就體現效率，如果不能，那麼它會查找多少範圍？

比如 city=bj and word=你好 order by user_count 這個是對的，是避免file_sort的。樓上黃勇對索引的理解顯然是不太對的。

如果是 city=bj and word like 你好%『 order by user_count 雖然不能完全避免存在多餘的數據來排序，但是，基本上，效率還是有保證的。

這個SQL，我個人評估，雖然用到了 filesort ，但索引依然會發揮效率，通常問題不大。

優化的目標是適當控制 like 命中集。比如禁止 like空。如果在city=。。。的範疇內，word like的命中集太多，排序效率會有所降低。

我一般不會苛求索引效率最優化，看應用場景而定。

為什麼不出現排序?

這個例子挺有意思的，涉及到很多背景知識。比如，基數對 SQL 優化器/解釋器的影響，優化器對 OR 條件的解釋， LIKE 語句，ORDER BY 對排序的影響，降序是否可以優化，索引創建是否合理? 是否有效率...

還有，欄位類型的選擇(貌似有點問題)。

我來翻譯一下樓主的題目:

1) 我有一個index, 名字叫city, 欄位是 city, word, user_count

2) 我有一個sql 語句, 肉眼看到的是, where 裡面用到city 和 word, order裡面用到user_count

3) 在1) 2) 的前提下, 為蝦米還要用到using filesort咧?

我來解答一下樓主的困惑, 如果我說錯了, 請高人指正, 同時說一下我的理解

1) clustered index, 在mysql裡面是不能指定的, primary key 默認就是clustered index, 換句話說, 如果你沒有設置primary key, 那麼, 這個table是沒有clustered index的

2) 分開來看, 如果where 裡面只有city, 肯定是用到index的, 如果是where 裡面有city 和 word, 不管他們的順序如何, 也是可以用到索引的, 問題就在於為啥where 裡面有city 和 word, order 裡面有user_count 就需要用到using filesort列? 其實很簡單

你自己畫一顆BTree, 或者閉上眼睛想一下, 你覺得index的數據結構是怎麼樣的, 模擬一下就知道為啥了.

我不是個高手，但自認為看懂了這個問題，首先我來舉例說明一下為什麼會出現filesort。

首先得了解索引在硬碟中的存儲狀態，複合索引的保存是按照排列組合的順序排好的，（`city`,`word`,`user_count`）這種索引，他保存的順序你可以抽象的理解為

(acity,aword,auser_count)

(acity,aword,buser_count)

(acity,bword,auser_count)

(acity,bword,buser_count)

(bcity,aword,auser_count)

(bcity,aword,buser_count)

(bcity,bword,auser_count)

(bcity,bword,buser_count)

每一個括弧就是一條記錄。

這樣一種存儲狀態，複合索引中之所以where中不包含第一個欄位的時候用不上索引，就是因為這種存儲結構決定了去掉第一個欄位來看，對第二個欄位和第三個欄位完全是亂序的，你沒有辦法用二分法快速地定位到所需要的元素。只有在排序好的數據上才能用上二叉樹或者是多叉樹的快速搜索。

再來看看樓主的索引順序是，`city`,`word`，`user_count`，搜索時｀city`和`word`肯定是用上索引了，orderby的欄位加入到索引中所以確定了city 和word 之後的數據是按照user_count排好序的，mysql用不著再進行排序了。等於是三個欄位都用上了索引。沒有比這個更好的了。

你獲得的數據本來就是按序排序的

auser_count

buser_count

但這裡有一個變數是用上了模糊搜索，用了模糊搜索以後，這裡就會有一個問題產生：

你可以想像以你好開頭的記錄不止一條，所以索引里的數據是這樣的。

(ncity, 你好a,auser_count)

(ncity, 你好a,buser_count)

(ncity, 你好b,auser_count)

(ncity, 你好b,buser_count)

所以按照word的索引找出數據以後，得到的user_count的數據是

auser_count

buser_count

auser_count

buser_count

這個樣子的，當然你不能把第一行的auser_count和第三行的auser_count當成一樣的值，這只是為了說明原理。mysql得到這樣一堆數據以後肯定還需要再進行排序，這就是filesort出現的原因。

所以前兩個索引用上了，但最後一個沒有用上，這看起來沒什麼好辦法解決。

劉項說用上了索引，那是因為只搜了索引的那幾個欄位，如果搜其他的欄位就用不上索引了，而且這裡的索引只意味著是用了索引來搜索數據，並沒有告訴你效率是否就是高的。這種方式的效率估計只是比直接查資料庫高那麼一點？

"city = bj and word=你好 order by user_count就可以避免filesort了"，這個也不對，city這個索引建立時又不是按user_count大小順序建立的，怎麼可能會在輸出查詢結果時不排序呢？這個概念從哪裡獲得的？

第一眼的反應是這個SQL真是複雜。

再仔細看，三個欄位有建多列索引，LIKE 用的是xxx%，而不是『%xx%』，嗯，貌似也會用到索引。

然後題主問，為什麼會using filesort?

為什麼不會using filesort！？

我把題目簡化一下，把中間那個LIKE去掉。假如有建一個city和user_count兩個欄位的多列索引，SQL語句假如是：

explain SELECT sql_no_cache id, word

FROM house_keyword

WHERE (city = bj OR city = cn OR city = all)

ORDER BY user_count DESC

LIMIT 10

就算是這樣，都會出現using filesort的啊！

曾經為了優化 WHERE xx1 IN (a,b,c,d,e,f,g) ORDER BY xx2 這樣的語句頭疼了好久，在Mysql下面，這個...基本上...很難。

排序沒看太明白，我在想這個where：

SELECT sql_no_cache id, word

FROM house_keyword

WHERE

city in(bj,cn,all) AND left(word,2)=你好

ORDER BY user_count DESC

LIMIT 10

不知道和上面那個哪個效率高些？

explain

樓主，應該邀請zhihu上的mysql DBA來這個問題。