首先假設有一個資料表 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 。
目前回應 Comments(5 comments)
HIM 2016/03/25
謝謝幫忙:D
關於SQL 漏洞，有些看不懂
例如: $name = preg_replace("/['"]+/" , '' ,$name); 我只放在backend 的login_submit 足夠防護嗎?
其他的不知道那個合適和放在那裡作防護>"
HIM 2016/03/24
$rowsPerPage =2;
Reply$pageNum = 1;
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}
$offset = ($pageNum - 1) * $rowsPerPage;
$link = mysqli_connect( "localhost", "root", "password" ) or die(mysqli_error($link));
mysqli_query("SET names 'UTF8' ");
mysqli_select_db( "test" );
$sql = "SELECT * FROM news WHERE ORDER BY id DESC LIMIT $offset, $rowsPerPage";
if ( $search != "" )
{
$sql = $sql . " WHERE subject LIKE '%$search%' OR description LIKE '%$search%' ";
}
$result = mysqli_query( $sql, $link) or die ("Error Query [".$sql."]");
while ($line = mysqli_fetch_array($result, MYSQL_ASSOC)) {
如果加了search function 出現了Error Query [SELECT * FROM news WHERE ORDER BY id DESC LIMIT 0, 2] 應該怎樣處理?
Admin
Himmlms40 2016/01/19
Nice code!
Duck 2014/11/11
Great!
Rooc 2014/04/16
Awesome!