當(dāng)你和別人都能實(shí)現(xiàn)一個(gè)某個(gè)功能，這時(shí)候區(qū)分你們能力的不是誰干活多少，而是誰能寫出效率更高的代碼。比如顯示一個(gè)訂單列表它不僅僅是寫一條SELECT SQL那么簡單，我們還需要很清楚的知道這條SQL他大概掃描了多少行數(shù)據(jù)，返回了多少行數(shù)據(jù)，是否需要?jiǎng)?chuàng)建索引，創(chuàng)建什么樣的索引，索引是否生效，等等。這里以訂單列表顯示和訂單導(dǎo)出為例來談?wù)凪ysql分頁優(yōu)化。

發(fā)現(xiàn)問題

下邊是一個(gè)訂單表的簡單表結(jié)構(gòu)。里邊有大概270萬條數(shù)據(jù)，其中渠道ID為35的有132萬調(diào)數(shù)據(jù)。

CREATE TABLE IF NOT EXISTS `order_info` ( `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '訂單ID', `order_sn` varchar(60) NOT NULL COMMENT '訂單號(hào)', `user_id` int(11) NOT NULL COMMENT '用戶ID', `channels_id` int(11) NOT NULL COMMENT '渠道ID', ……一些其他字段 `order_time` datetime NOT NULL COMMENT '下單時(shí)間', 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;

一個(gè)訂單列表頁面一般很多人是這么寫的。顯示一個(gè)總數(shù)或者總頁數(shù)，然后是上一頁 1 2 3 4 5 下一頁

而我們一般會(huì)這樣寫sql語句去實(shí)現(xiàn)上邊的功能：

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

這里獲取數(shù)據(jù)總數(shù)用了相當(dāng)長的時(shí)間。隨著你數(shù)據(jù)量的增多需要的時(shí)間也會(huì)更長。在獲取第一頁的數(shù)據(jù)的時(shí)候也沒用多長時(shí)間，但是越往后需要的時(shí)間也就越長。

在多人操作尤其是大并發(fā)量的情況下，大量的數(shù)據(jù)被掃描造成系統(tǒng)IO和CPU資源消耗完，進(jìn)而導(dǎo)致整個(gè)數(shù)據(jù)庫不可服務(wù)。 而cpu 消耗過大通常情況下都是由于慢sql 造成的，這里的慢sql 包括全表掃描，掃描數(shù)據(jù)量過大，內(nèi)存排序，磁盤排序，鎖爭用等待等； 表現(xiàn)現(xiàn)象為：sql 執(zhí)行狀態(tài)為：sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result，locked;

如何優(yōu)化

普通的limit M,N 的寫法越往后查詢?cè)铰Ｒ驗(yàn)閙ysql總是會(huì)去掃描M+N條數(shù)據(jù)來得到你想要的數(shù)據(jù)。

我們來看一下京東的分頁

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

獲取數(shù)據(jù)總數(shù)的優(yōu)化

盡量不要去獲取數(shù)據(jù)總數(shù)。如果業(yè)務(wù)確實(shí)需要獲取當(dāng)前搜索條件下的數(shù)據(jù)總數(shù)也建議使用ajax讓用戶點(diǎn)擊按鈕觸發(fā)后獲取總數(shù)，或者根據(jù)時(shí)間維度做數(shù)據(jù)的分表。大多數(shù)用戶在點(diǎn)擊訂單列表的時(shí)候關(guān)心的不是訂單總數(shù)，也不是很久之前的訂單，而是最近一段時(shí)間下的訂單。

獲取數(shù)據(jù)的優(yōu)化

下邊我們利用索引只獲取主鍵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

所以我們可以有這樣的優(yōu)化寫法：

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 secselect * 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條數(shù)據(jù)的主鍵id，然后根據(jù)主鍵id去查詢你所需要的N條數(shù)據(jù)，此過程中查詢N條數(shù)據(jù)的主鍵ID在索引中完成。

這里我們盡量只顯示上一頁或者下一頁。那么如何去判斷下一頁是否有數(shù)據(jù)呢(沒有數(shù)據(jù)的時(shí)候把下一頁的按鈕置灰)?參考laravel的簡單分頁設(shè)計(jì)。比如每頁顯示20條數(shù)據(jù)，而我顯示當(dāng)前頁面的時(shí)候去獲取21條數(shù)據(jù)，根據(jù)是否存在第21條數(shù)據(jù)來判斷是否需要顯示下一頁。

上邊的方法雖然快了不少，可是依然掃描了很多的數(shù)據(jù)行，在數(shù)據(jù)量大的情況下依然會(huì)很慢，尤其是在做數(shù)據(jù)導(dǎo)出的時(shí)候。

比較常見的導(dǎo)出數(shù)據(jù)的應(yīng)用場(chǎng)景就是用戶輸入搜索條件然后按照搜索條件導(dǎo)出數(shù)據(jù)。數(shù)據(jù)的導(dǎo)出不像列表頁的顯示。我們完全可以利用主鍵來操作。

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

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

//auth by duxiaokong 2016-08-23$fp = fopen('php://output', 'a');$num_limit = 1000;$order_id = 0;$order_list = [];while (true) { //執(zhí)行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']; //這行代碼一定要記得賦值不然會(huì)造成死循環(huán) $line++; // 獲取導(dǎo)出數(shù)據(jù) $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次記錄寫入一次數(shù)據(jù)庫，減少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);

如何優(yōu)化？

最主要的原則就是避免數(shù)據(jù)量大時(shí)掃描過多的記錄。

總結(jié)

以上是生活随笔為你收集整理的ajax mysql搜索_京东分页优化之Mysql优化实践的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。