Mysql中怎么優化千萬級快速分頁

155次閱讀

共計 3166 個字符，預計需要花費 8 分鐘才能閱讀完成。

本篇文章為大家展示了 Mysql 中怎么優化千萬級快速分頁，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

數據表 collect (id, title ,info ,vtype) 就這 4 個字段，其中 title 用定長，info 用 text, id 是逐漸，vtype 是 tinyint，vtype 是索引。這是一個基本的新聞系統的簡單模型。現在往里面填充數據，填充 10 萬篇新聞。
最后 collect 為 10 萬條記錄，數據庫表占用硬盤 1.6G。OK , 看下面這條 sql 語句：
select id,title from collect limit 1000,10; 很快；基本上 0.01 秒就 OK，再看下面的
select id,title from collect limit 90000,10; 從 9 萬條開始分頁，結果？
8- 9 秒完成，my god 哪出問題了？？？？其實要優化這條數據，網上找得到答案。看下面一條語句:
select id from collect order by id limit 90000,10; 很快，0.04 秒就 OK。為什么？因為用了 id 主鍵做索引當然快。網上的改法是：
select id,title from collect where id =(select id from collect order by id limit 90000,1) limit 10;
這就是用了 id 做索引的結果。可是問題復雜那么一點點，就完了。看下面的語句
select id from collect where vtype=1 order by id limit 90000,10; 很慢，用了 8 - 9 秒！
到了這里我相信很多人會和我一樣，有崩潰感覺！vtype 做了索引了啊？怎么會慢呢？vtype 做了索引是不錯，你直接 select id from collect where vtype=1 limit 1000,10; 是很快的，基本上 0.05 秒，可是提高 90 倍，從 9 萬開始，那就是 0.05*90=4.5 秒的速度了。和測試結果 8 - 9 秒到了一個數量級。從這里開始有人提出了分表的思路，這個和 discuz 是一樣的思路。思路如下：
建一個索引表：t (id,title,vtype) 并設置成定長，然后做分頁，分頁出結果再到 collect 里面去找 info。是否可行呢？實驗下就知道了。
10 萬條記錄到 t(id,title,vtype) 里，數據表大小 20M 左右。用
select id from t where vtype=1 order by id limit 90000,10; 很快了。基本上 0.1-0.2 秒可以跑完。為什么會這樣呢？我猜想是因為 collect 數據太多，所以分頁要跑很長的路。limit 完全和數據表的大小有關的。其實這樣做還是全表掃描，只是因為數據量小，只有 10 萬才快。OK，來個瘋狂的實驗，加到 100 萬條，測試性能。
加了 10 倍的數據，馬上 t 表就到了 200 多 M，而且是定長。還是剛才的查詢語句，時間是 0.1-0.2 秒完成！分表性能沒問題？錯！因為我們的 limit 還是 9 萬，所以快。給個大的，90 萬開始
select id from t where vtype=1 order by id limit 900000,10; 看看結果，時間是 1 - 2 秒！
why ?? 分表了時間還是這么長，非常之郁悶！有人說定長會提高 limit 的性能，開始我也以為，因為一條記錄的長度是固定的，mysql 應該可以算出 90 萬的位置才對啊？可是我們高估了 mysql 的智能，他不是商務數據庫，事實證明定長和非定長對 limit 影響不大？怪不得有人說 discuz 到了 100 萬條記錄就會很慢，我相信這是真的，這個和數據庫設計有關！
難道 MySQL 無法突破 100 萬的限制嗎？？？到了 100 萬的分頁就真的到了極限？？？
答案是：NO !!!! 為什么突破不了 100 萬是因為不會設計 mysql 造成的。下面介紹非分表法，來個瘋狂的測試！一張表搞定 100 萬記錄，并且 10G 數據庫，如何快速分頁！
好了，我們的測試又回到 collect 表，開始測試結論是：30 萬數據，用分表法可行，超過 30 萬他的速度會慢道你無法忍受！當然如果用分表 + 我這種方法，那是絕對完美的。但是用了我這種方法后，不用分表也可以完美解決！
答案就是：復合索引！有一次設計 mysql 索引的時候，無意中發現索引名字可以任取，可以選擇幾個字段進來，這有什么用呢？開始的 select id from collect order by id limit 90000,10; 這么快就是因為走了索引，可是如果加了 where 就不走索引了。抱著試試看的想法加了 search(vtype,id) 這樣的索引。然后測試
select id from collect where vtype=1 limit 90000,10; 非常快！0.04 秒完成！
再測試: select id ,title from collect where vtype=1 limit 90000,10; 非常遺憾，8- 9 秒，沒走 search 索引！
再測試：search(id,vtype)，還是 select id 這個語句，也非常遺憾，0.5 秒。
綜上：如果對于有 where 條件，又想走索引用 limit 的，必須設計一個索引，將 where 放第一位，limit 用到的主鍵放第 2 位，而且只能 select 主鍵！
完美解決了分頁問題了。可以快速返回 id 就有希望優化 limit，按這樣的邏輯，百萬級的 limit 應該在 0.0x 秒就可以分完。看來 mysql 語句的優化和索引時非常重要的！
好了，回到原題，如何將上面的研究成功快速應用于開發呢？如果用復合查詢，我的輕量級框架就沒的用了。分頁字符串還得自己寫，那多麻煩？這里再看一個例子，思路就出來了：
select * from collect where id in (9000,12,50,7000); 竟然 0 秒就可以查完！
mygod，mysql 的索引竟然對于 in 語句同樣有效！看來網上說 in 無法用索引是錯誤的！
有了這個結論，就可以很簡單的應用于輕量級框架了：
代碼如下：
$db=dblink();
$db- pagesize=20;
$sql= select id from collect where vtype=$vtype
$db- execute($sql);
$strpage=$db- strpage(); // 將分頁字符串保存在臨時變量，方便輸出
while($rs=$db- fetch_array()){
$strid.=$rs[id]. ,
}
$strid=substr($strid,0,strlen($strid)-1); // 構造出 id 字符串
$db- pagesize=0; // 很關鍵，在不注銷類的情況下，將分頁清空，這樣只需要用一次數據庫連接，不需要再開；
$db- execute(select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)
? while($rs=$db- fetch_array()): ?
tr
td nbsp; ?php echo $rs[id ? /td
td nbsp; ?php echo $rs[url ? /td
td nbsp; ?php echo $rs[sTime ? /td
td nbsp; ?php echo $rs[gTime ? /td
td nbsp; ?php echo $rs[vtype ? /td
td nbsp; a href= ?act=show id= ?php echo $rs[id ? target= _blank ?php echo $rs[ title ? /a /td
td nbsp; ?php echo $rs[tag ? /td
/tr
?php endwhile; ?
/table
?php
echo $strpage;