在web應用中,提倡sql簡單,所以在我們公司的應用中看不到jon,子查詢等語句的存在,所以間接oprder by 與 limit的使用占據大多數,其實很多技巧,別人都是總結過的,仔細分析,仔細學習別人的經驗才是正道.而不可浮躁,憑經驗主義.
1:order by with limit
2:make sure it uses index
對於order by with limit來說,不執行掃描和排序是非常重要的,所以索引是非常重要的,index range scan執行的結果:一旦特定數量的行返回就結束.
比如 select * from sites order by date_created desc limit 10,使用索引(date_created)將非常的快,
我們來看下一個查詢 select * from sites where category_id=5 order by date_created desc limit 10,在這個情況下date_created索引將工作,但可能並不是最有效的,因為category_id很大一部分將需要掃描找出10行,所以索引(category_id, date_created)是個很好的解決方案.
讓我們看一個更復雜的查詢 select * from sites where category_id in (5,10,15) order by date_created desc limit 10,這個查詢其實和上一個有很大不同,category_id因為有多個值,所以這個索引不能直接使用,這是btree索引的原因.
select from people where gender='m' and age between 18 and 28 and country_id=5 and city_id=345 order by last_online desc limit 10
這個有很多的限制因素,可以通過一些通用的搜索解決方案去解決.但是假如我們在選擇性比較好的列使用multiple indexes將是一個很好的性能解決方案,比如索引(gender,last_online),假定大部分的性別類型是固定的.這個索引將會比較有作用.
我們另外需要仔細觀察的事情,假如你沒有通過索引完全覆蓋where條件,這樣很多記錄將被掃描並進行排序(一般都在慢查詢中發現),假如只有50行記錄去執行並只提供10行將不會有多大問題,假如有5000行,你就需要重新考慮索引了.
記錄被掃描提供結果集是個動態的結果:同其他因素混合在一塊是經常變化的,舉個例子,假如僅僅使用索引last_online,假如我們尋找美國人,那麼10行會很快掃描到,假如是一個比價小的國家,這樣可能需要掃描很多次才能提供結果集.
在上面的例子中,我們通過最後一個列進行索引,實際上order by的字段一般都可以索引,而leading列進行排序,
注意,一些情況下列不能同時進行order by索引和過濾條件索引.
select * from tbl where c=5 order by a,b limit 10語句,索引(a,b,c) 索引能夠使用order by ,但是不能過濾c的結果,所以提倡使用索引(c,a,b)
3:sort by column in leading table
4:sort in one direction
5:beware of large limit
if you're dealing with LIMIT query with large offset efficiency will suffer . bug Using index to sort is efficIEnt if you need first few rows
6:Force index if needed
Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity or just better estimates instead of which allows you to do the sort.
7:Do not sort by expressions
是不要用MySQL內置函數和表達式去排序,而非過濾,對於我們文章庫x_rank字段為什麼不使用索引,是因為status是個range 掃描,即使加x_rank索引也是沒有效果的
select * from table where uid='' and status in (1,3) and x_rank &64=64 order by date2 desc
