當你和別人都能實現一個某個功能，這時候區分你們能力的不是誰幹活多少，而是誰能寫出效率更高的代碼。比如顯示一個訂單列表它不僅僅是寫一條SELECT SQL那麼簡單，我們還需要很清楚的知道這條SQL他大概掃描了多少行數據，返回了多少行數據，是否需要創建索引，創建什麼樣的索引，索引是否生效，等等。這裡以訂單列表顯示和訂單導出為例來談談Mysql分頁優化。

發現問題

下邊是一個訂單表的簡單表結構。裡邊有大概270萬條數據，其中渠道ID為35的有132萬調數據。

CREATE TABLE IF NOT EXISTS `order_info` (
 `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '訂單ID',
 `order_sn` varchar(60) NOT NULL COMMENT '訂單號',
 `user_id` int(11) NOT NULL COMMENT '用戶ID',
 `channels_id` int(11) NOT NULL COMMENT '渠道ID',
 ……一些其他字段
 `order_time` datetime NOT NULL COMMENT '下單時間', 
 PRIMARY KEY (`order_id`),
 KEY `channels_id` (`channels_id`),
 KEY `order_sn` (`order_sn`),
 KEY `user_id` (`user_id`),
 KEY `order_time` (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

一個訂單列表頁面一般很多人是這麼寫的。顯示一個總數或者總頁數，然後是上一頁 1 2 3 4 5 下一頁

而我們一般會這樣寫sql語句去實現上邊的功能：

select count(1) as num from order_info where channels_id=35; 0.24 sec
select * from order_info where channels_id=35 order by order_id desc limit 0,20; 0.01 sec
select * from order_info where channels_id=35 order by order_id desc limit 1320000,20; 12.55 sec 即便是第二次查詢也用了4.27 sec（mysql自身也會有查詢緩存機制）

這裡獲取數據總數用了相當長的時間。隨著你數據量的增多需要的時間也會更長。在獲取第一頁的數據的時候也沒用多長時間，但是越往後需要的時間也就越長。

在多人操作尤其是大併發量的情況下，大量的數據被掃描造成系統IO和CPU資源消耗完，進而導致整個數據庫不可服務。而cpu 消耗過大通常情況下都是由於慢sql 造成的，這裡的慢sql 包括全表掃描，掃描數據量過大，內存排序，磁盤排序，鎖爭用等待等；表現現象為：sql 執行狀態為：sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result，locked;

如何優化

普通的limit M,N 的寫法越往後查詢越慢。因為mysql總是會去掃描M+N條數據來得到你想要的數據。

我們來看一下京東的分頁

上邊是京東的搜索和分頁。京東的訂單很明顯根據時間維度做了分庫或者分表，也可能根據用戶維度又做了分庫分表。京東沒有顯示總數，但是顯示了頁碼 1 2 3 4 5

獲取數據總數的優化

儘量不要去獲取數據總數。如果業務確實需要獲取當前搜索條件下的數據總數也建議使用ajax讓用戶點擊按鈕觸發後獲取總數，或者根據時間維度做數據的分表。大多數用戶在點擊訂單列表的時候關心的不是訂單總數，也不是很久之前的訂單，而是最近一段時間下的訂單。

獲取數據的優化

下邊我們利用索引只獲取主鍵ID。用了0.40 sec，比上邊的sql少了很多。

select order_id from order_info where channels_id=35 order by order_id desc limit 1320000,20; 0.40 sec

所以我們可以有這樣的優化寫法：

select * from order_info,(select order_id from order_info where channels_id=35 order by order_id desc limit 1320000,20) order_info_tmp where order_info.order_id = order_info_tmp.order_id; 0.47 sec
select * from order_info,(select order_id from order_info where channels_id=35 order by order_id desc limit 0,20) order_info_tmp where order_info.order_id = order_info_tmp.order_id; 0.00 sec

先查詢翻頁中需要的N條數據的主鍵id，然後根據主鍵id去查詢你所需要的N條數據，此過程中查詢N條數據的主鍵ID在索引中完成。

這裡我們儘量只顯示上一頁或者下一頁。那麼如何去判斷下一頁是否有數據呢（沒有數據的時候把下一頁的按鈕置灰）?參考laravel的簡單分頁設計。比如每頁顯示20條數據，而我顯示當前頁面的時候去獲取21條數據，根據是否存在第21條數據來判斷是否需要顯示下一頁。

上邊的方法雖然快了不少，可是依然掃描了很多的數據行，在數據量大的情況下依然會很慢，尤其是在做數據導出的時候。

比較常見的導出數據的應用場景就是用戶輸入搜索條件然後按照搜索條件導出數據。數據的導出不像列表頁的顯示。我們完全可以利用主鍵來操作。

select * from order_info where channels_id=35 AND order_id <=54388 order by order_id desc limit 20; 0.00 sec

我們主要是利用了主鍵ID，這裡你可以看到即便是非常往後的數據也是很快的速度就能獲取到。這樣寫能很大程度上減少表掃描的行數，減少數據查詢的時間。

//auth by duxiaokong 2016-08-23
$fp = fopen('php://output', 'a');
$num_limit = 1000;
$order_id = 0;
$order_list = [];
while (true) {
 //執行sql select * from order_info where $where AND order_id > $order_id order by order_id ASC limit $num_limit; 得到$order_list訂單列表
 //這裡一定要注意 order_id > $order_id 和 order_id ASC的排序
 if (empty($order_list)) {
 break;
 }
 $line = 0;
 $row_str = '';
 foreach ($order_list as $key => $val) {
 $order_id = $val['order_id']; //這行代碼一定要記得賦值不然會造成死循環
 $line++;
 // 獲取導出數據
 $row = [
 $val['order_sn'],
 $val['order_time'],
 $val['user_name']
 // ……
 ];
 //$row 過濾 $row中的非法字符
 $row_str .= mb_convert_encoding(implode(',', $row), 'gbk', 'utf-8') . PHP_EOL; 

 //每獲取20次記錄寫入一次數據庫，減少IO
 if ($line >= 20) {
 fwrite($fp, $row_str);
 $line = 0;
 $row_str = '';
 }
 }
 if (!empty($row_str)) {
 fwrite($fp, $row_str);
 $line = 0;
 $row_str = '';
 }
}
fclose($fp);