首先假設有一個資料表 news ，共有20萬筆資料 ， 排序的欄位為 sort，primary key 為 id
這時當我們要查詢第19999頁的資料時，會用這樣的語法
select * from news order By sort DESC limit 199990,10;
結果 Mysql 資料庫卻跑了2秒鐘，這真的太嚇人了
接著我們用語法
explain select * from news order By sort DESC limit 10000,10;
發現 possible_keys = null , extra = Using filesort 所以在order By sort時，mysql使用 filesort ，並沒有用index方式排序，不過這並不是最大的問題。
再試看看不加排序的語法:
select * from news limit 10000,10;
執行0.5秒，也就是說拿掉排序後，SQL 語法的執行時間就正常了，也就是說當資料量過大時，排序會影響到SQL的效能。
我們再試試下列兩句語法:
select * from news order By sort DESC limit 199990,10; 執行2秒
select id from news order By sort DESC limit 199990,10; 執行0.5秒
從這裡我們可以看出來 MySql 在排序的時間，讀取的欄位也會影響排序的效能，而且影響的程度，遠大於 Index。
最後我們使用下列的語法，來解決排序而造成搜尋時間過久的問題，方法是用 inner join 的方式，先 select primary key ，再用 primary key 去取得其它欄位的資料。
select * from news as t inner join ( select id from news order by sort DESC limit 140000,10 ) as k where t.id=k.id
Mysql performance 測試
現在再做一些詳細的測試，來比較各種狀況的SQL語法。
假設資料表 : book 欄位如下，並且資料表中有20萬筆資料。
| book_name 書名 | price 價格 | author_id 作者 |
- create table book(
- id int auto_increment,
- book_name varchar(255),
- price int,
- author_id int,
- sort int,
- sort_index int,
- sort_unique int,
- primary key (id),
- index (sort_index),
- unique (sort_unique)
- )engine=MyISAM DEFAULT CHARSET=utf8 ;
| 搜尋欄位 | SQL語法 | 執行時間(秒) |
| 抓最前10筆資料 並指定排序sort |
select * from book order by sort ASC limit 0,10 | 0.07 |
| 抓最後10筆資料 並指定排序sort | select * from book order by sort ASC limit 199990,10 | 2.16 |
| 抓book最後10筆資料， 以sort為排序，只取欄位ID |
select id from book order by sort limit 199990,10 | 0.1 |
| 抓最後10筆資料， 以sort(index)為排序 |
select * from book order by sort_index ASC limit 199990,10 | 1.85 |
| 抓最後10筆資料， 以sort(require)為排序 |
select * from book order by sort_unique ASC limit 199990,10 | 1.775 |
| 抓最後10筆資料， 以sort為排序，並優先取得id (優化語法) |
select * from `book` as t inner join (select id from book order by sort ASC limit 199990,10) as b on t.id=b.id order by t.sort ASC | 0.13 |
| 抓最後10筆資料， 以id為排序，並優先取得id (優化語法) |
select * from `book` as t inner join (select id from book order by id ASC limit 199990,10) as b on t.id=b.id order by t.id ASC | 0.065 |
| 排序id抓最後10筆資料，取欄位id,author_id book_name有文字後 | select id,book_name,author_id from book order by id limit 199990,10 有讀取文字時，速度會特別慢 |
1.8 |
橘色的SQL語法，代表執行速度過慢，應修改為優化後的語法，結論是：如果資料量很大，又加上 select 的欄位有文字，以及使用 order 排序時，SQL執行時間就會爆表 ( 含文字的欄位，資料越長，速度就越慢)。
這裡有一篇文章，在說明 limit offset 過大時，為什麼會造成 slow query 。