大數據

MySQL 百萬級數據量分頁查詢方法及其優化

廣告
廣告

作者 | 大神養成記 原文 |  http://t.cn/RnvCJnm

方法1: 直接使用數據庫提供的SQL語句

  • 語句樣式: MySQL中,可用如下方法: SELECT * FROM 表名稱 LIMIT M,N
  • 適應場景: 適用于數據量較少的情況(元組百/千級)
  • 原因/缺點: 全表掃描,速度會很慢 且 有的數據庫結果集返回不穩定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是從結果集的M位置處取出N條輸出,其余拋棄.

方法2: 建立主鍵或唯一索引, 利用索引(假設每頁10條)

  • 語句樣式: MySQL中,可用如下方法: SELECT * FROM 表名稱 WHERE id_pk > (pageNum*10) LIMIT M
  • 適應場景: 適用于數據量多的情況(元組數上萬)
  • 原因: 索引掃描,速度會很快. 有朋友提出: 因為數據查詢出來并不是按照pk_id排序的,所以會有漏掉數據的情況,只能方法3

方法3: 基于索引再排序

  • 語句樣式: MySQL中,可用如下方法: SELECT * FROM 表名稱 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
  • 適應場景: 適用于數據量多的情況(元組數上萬). 最好ORDER BY后的列對象是主鍵或唯一所以,使得ORDERBY操作能利用索引被消除但結果集是穩定的(穩定的含義,參見方法1)
  • 原因: 索引掃描,速度會很快. 但MySQL的排序操作,只有ASC沒有DESC(DESC是假的,未來會做真正的DESC,期待…).

方法4: 基于索引使用prepare

第一個問號表示pageNum,第二個?表示每頁元組數

  • 語句樣式: MySQL中,可用如下方法: PREPARE stmt_name FROM SELECT * FROM 表名稱 WHERE id_pk > (?* ?) ORDER BY id_pk ASC LIMIT M
  • 適應場景: 大數據量
  • 原因: 索引掃描,速度會很快. prepare語句又比一般的查詢語句快一點。

方法5: 利用MySQL支持ORDER操作可以利用索引快速定位部分元組,避免全表掃描

比如: 讀第1000到1019行元組(pk是主鍵/唯一鍵).

SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

方法6: 利用”子查詢/連接+索引”快速定位元組的位置,然后再讀取元組. 

比如(id是主鍵/唯一鍵,藍色字體時變量)

利用子查詢示例:

SELECT * FROM your_table WHERE id <= 
(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc 
LIMIT $pagesize

利用連接示例:

SELECT * FROM your_table AS t1 
JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2 
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

mysql大數據量使用limit分頁,隨著頁碼的增大,查詢效率越低下。

測試實驗

1.   直接用limit start, count分頁語句, 也是我程序中用的方法:

select * from product limit start, count

當起始頁較小時,查詢沒有性能問題,我們分別看下從10, 100, 1000, 10000開始分頁的執行時間(每頁取20條)。

如下:

select * from product limit 10, 20   0.016秒
select * from product limit 100, 20   0.016秒
select * from product limit 1000, 20   0.047秒
select * from product limit 10000, 20   0.094秒

我們已經看出隨著起始記錄的增加,時間也隨著增大, 這說明分頁語句limit跟起始頁碼是有很大關系的,那么我們把起始記錄改為40w看下(也就是記錄的一般左右)     

select * from product limit 400000, 20   3.229秒

再看我們取最后一頁記錄的時間

select * from product limit 866613, 20   37.44秒

像這種分頁最大的頁碼頁顯然這種時間是無法忍受的。

從中我們也能總結出兩件事情:

  1. limit語句的查詢時間與起始記錄的位置成正比
  2. mysql的limit語句是很方便,但是對記錄很多的表并不適合直接使用。

2.   對limit分頁問題的性能優化方法

利用表的覆蓋索引來加速分頁查詢
我們都知道,利用了索引查詢的語句中如果只包含了那個索引列(覆蓋索引),那么這種情況會查詢很快。

因為利用索引查找有優化算法,且數據就在查詢索引上面,不用再去找相關的數據地址了,這樣節省了很多時間。另外Mysql中也有相關的索引緩存,在并發高的時候利用緩存就效果更好了。

在我們的例子中,我們知道id字段是主鍵,自然就包含了默認的主鍵索引。現在讓我們看看利用覆蓋索引的查詢效果如何。

這次我們之間查詢最后一頁的數據(利用覆蓋索引,只包含id列),如下:

select id from product limit 866613, 20 0.2秒

相對于查詢了所有列的37.44秒,提升了大概100多倍的速度

那么如果我們也要查詢所有列,有兩種方法,一種是id>=的形式,另一種就是利用join,看下實際情況:

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

查詢時間為0.2秒!

另一種寫法

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

查詢時間也很短!

3.  復合索引優化方法

MySql 性能到底能有多高?MySql 這個數據庫絕對是適合dba級的高手去玩的,一般做一點1萬篇新聞的小型系統怎么寫都可以,用xx框架可以實現快速開發。可是數據量到了10萬,百萬至千萬,他的性能還能那么高嗎?一點小小的失誤,可能造成整個系統的改寫,甚至更本系統無法正常運行!好了,不那么多廢話了。

用事實說話,看例子:

數據表 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秒到了一個數量級。

從這里開始有人提出了分表的思路,這個和dis #cuz 論壇是一樣的思路。思路如下:

建一個索引表: 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 語句的優化和索引時非常重要的!

我還沒有學會寫個人說明!

中國銀聯跨中心,異構數據同步技術與實踐

上一篇

分布式時序數據庫QTSDB的設計與實現

下一篇

你也可能喜歡

MySQL 百萬級數據量分頁查詢方法及其優化

長按儲存圖像,分享給朋友

ITPUB 每周精要將以郵件的形式發放至您的郵箱


微信掃一掃

微信掃一掃
重庆百变王牌开奖结果 微信农场赚钱 麻将app平台 上传什么视频才能赚钱 兔聊怎么设置赚钱 流通渠道通畅 赚钱 保时捷彩票首页 赚钱闲鱼是真的 175单号怎么赚钱 人拼命赚钱的意义陪伴家人 99电玩千炮捕鱼下载 怎么平台可以赚钱 网上的佩奇传奇兼职赚钱吗 凤凰彩票安卓 学生做什么代理好赚钱 魔兽世界地图 种血橙子赚钱吗