Mysql優化實踐(分頁優化)

2022-11-24 21:26:10 字數 3449 閱讀 6086

當你和別人都能實現一個某個功能,這時候區分你們能力的不是誰幹活多少,而是誰能寫出效率更高的**。比如顯示一個訂單列表它不僅僅是寫一條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;

而我們一般會這樣寫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在索引中完成。

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

比較常見的匯出資料的應用場景就是使用者輸入搜尋條件然後按照搜尋條件匯出資料。資料的匯出不像列表頁的顯示。我們完全可以利用主鍵來操作。

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)

$line = 0;

$row_str = '';

foreach ($order_list as $key => $val)

}if (!empty($row_str))

}fclose($fp);

總結:如何優化?最主要的原則就是避免資料量大時掃描過多的記錄。