MySQL百萬級數(shù)據(jù)量分頁查詢方法及其優(yōu)化建議

offset+limit方式的分頁查詢，當(dāng)數(shù)據(jù)表超過100w條記錄，性能會很差。

為菏澤等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計制作服務(wù)，及菏澤網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為網(wǎng)站設(shè)計制作、成都網(wǎng)站制作、菏澤網(wǎng)站設(shè)計，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會得到認(rèn)可，從而選擇與我們長期合作。這樣，我們也可以走得更遠(yuǎn)！

主要原因是offset limit的分頁方式是從頭開始查詢，然后舍棄前offset個記錄，所以offset偏移量越大，查詢速度越慢。

比如: 讀第10000到10019行元素(pk是主鍵/唯一鍵).

使用order by id可以在查詢時使用主鍵索引。

但是這種方式在id為uuid的時候就會出現(xiàn)問題?？梢允褂脀here in的方式解決：

帶條件的查詢：

如果在分頁查詢中添加了where條件例如 type = 'a’這樣的條件，sql變成 ：

這種情況因為type沒有使用索引也會導(dǎo)致查詢速度變慢。但是只添加type為索引查詢速度還是很慢,是因為查詢的數(shù)據(jù)量太多了。這個時候考慮添加組合索引，組合索引的順序要where條件字段在前，id在后，如 (type,id)，因為組合索引查詢時用到了type索引，而type跟id是組合索引的關(guān)系，如果只select id ，那么直接就可以按組合索引返回id，而不需要再進(jìn)行一次查詢?nèi)シ祷豬d

使用uuid作為主鍵不僅會帶來性能上的問題，在查詢時也會遇到問題。

因為在使用select id from table limit 10000,10 查詢id數(shù)據(jù)時，默認(rèn)是對id進(jìn)行排序，返回的是排序后的id結(jié)果，如果我們想按插入順序查詢結(jié)果，這樣查詢出來的結(jié)果就與我們的需求不相符。

聚集索引跟非聚集索引：聚集索引類似與新華字典的拼音，根據(jù)拼音搜索到的信息都是連續(xù)的，可以很快獲取到它前后的信息。非聚集索引類似于部首查詢，信息存放的位置可能不在一個區(qū)域。對經(jīng)常使用范圍查詢的字段考慮使用聚集索引。

InnoDB中索引分為聚簇索引（主鍵索引）和非聚簇索引（非主鍵索引），聚簇索引的葉子節(jié)點中保存的是整行記錄，而非聚簇索引的葉子節(jié)點中保存的是該行記錄的主鍵的值。

如果您的表上定義有主鍵，該主鍵索引是聚集索引。

如果你不定義為您的表的主鍵時，MySQL取第一個唯一索引（unique）而且只含非空列（NOT NULL）作為主鍵，InnoDB使用它作為聚集索引。

如果沒有這樣的列，InnoDB就自己產(chǎn)生一個這樣的ID值，

優(yōu)先選index key_len小的索引進(jìn)行count(*),盡量不使用聚簇索引

在沒有where條件的情況下，count(*)和count(常量)，如果有非聚簇索引，mysql會自動選擇非聚簇索引，因為非聚簇索引所占的空間小，如果沒有非聚簇索引會使用聚集索引。count(primary key)主鍵id為聚集索引，使用聚集索引。有where條件的情況下，是否使用索引會根據(jù)where條件判斷。

mysql使用limit分頁優(yōu)化方案

準(zhǔn)備數(shù)據(jù)是20000000條數(shù)據(jù)

在分頁場景下，使用limit start end，我們分別看下從10000， 100000， 1000000開始分頁的執(zhí)行時間（每頁取10條），如下圖

當(dāng)start較小時，查詢沒有性能問題，但是如上圖查詢時間所示，隨著start增大，查詢消耗時間也在遞增，在start=10000000時，分頁竟然消耗了2秒多，這是不能忍受的。

由此引出對limit分頁的優(yōu)化,首先來explain該語句，看到查詢沒有使用到任何的索引，進(jìn)行的是全表掃描，假如limit分頁用到了索引是不是會快很多呢！

explain分析一下,第一行是select * from user_innodb形成的臨時表使用的是全表掃描，第二行是 （SELECT id FROM user_innodb LIMIT 10000000, 10）形成的，使用的是eq_ref，第三行是全表掃描a和bjoin形成的派生表，使用到的是index，所以速度也會快很多

如何優(yōu)化Mysql千萬級快速分頁

很多應(yīng)用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導(dǎo)航欄。然而，如何通過MySQL更好的實現(xiàn)分頁，始終是比較令人頭疼的問題。雖然沒有拿來就能用的解決辦法，但了解數(shù)據(jù)庫的底層或多或少有助于優(yōu)化分頁查詢。

我們先從一個常用但性能很差的查詢來看一看。

SELECT *

FROM city

ORDER BY id DESC

LIMIT 0, 15

這個查詢耗時0.00sec。So，這個查詢有什么問題呢？實際上，這個查詢語句和參數(shù)都沒有問題，因為它用到了下面表的主鍵，而且只讀取15條記錄。

CREATE TABLE city (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

city varchar(128) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

真正的問題在于offset(分頁偏移量)很大的時候，像下面這樣：

SELECT *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

上面的查詢在有2M行記錄時需要0.22sec，通過EXPLAIN查看SQL的執(zhí)行計劃可以發(fā)現(xiàn)該SQL檢索了100015行，但最后只需要15行。大的分頁偏移量會增加使用的數(shù)據(jù)，MySQL會將大量最終不會使用的數(shù)據(jù)加載到內(nèi)存中。就算我們假設(shè)大部分網(wǎng)站的用戶只訪問前幾頁數(shù)據(jù)，但少量的大的分頁偏移量的請求也會對整個系統(tǒng)造成危害。Facebook意識到了這一點，但Facebook并沒有為了每秒可以處理更多的請求而去優(yōu)化數(shù)據(jù)庫，而是將重心放在將請求響應(yīng)時間的方差變小。

對于分頁請求，還有一個信息也很重要，就是總共的記錄數(shù)。我們可以通過下面的查詢很容易的獲取總的記錄數(shù)。

SELECT COUNT(*)

FROM city;

然而，上面的SQL在采用InnoDB為存儲引擎時需要耗費(fèi)9.28sec。一個不正確的優(yōu)化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能夠在分頁查詢時事先準(zhǔn)備好符合條件的記錄數(shù)，隨后只要執(zhí)行一句 select FOUND_ROWS(); 就能獲得總記錄數(shù)。但是在大多數(shù)情況下，查詢語句簡短并不意味著性能的提高。不幸的是，這種分頁查詢方式在許多主流框架中都有用到，下面看看這個語句的查詢性能。

SELECT SQL_CALC_FOUND_ROWS *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

這個語句耗時20.02sec，是上一個的兩倍。事實證明使用 SQL_CALC_FOUND_ROWS 做分頁是很糟糕的想法。

下面來看看到底如何優(yōu)化。文章分為兩部分，第一部分是如何獲取記錄的總數(shù)目，第二部分是獲取真正的記錄。

高效的計算行數(shù)

如果采用的引擎是MyISAM，可以直接執(zhí)行COUNT(*)去獲取行數(shù)即可。相似的，在堆表中也會將行數(shù)存儲到表的元信息中。但如果引擎是InnoDB情況就會復(fù)雜一些，因為InnoDB不保存表的具體行數(shù)。

我們可以將行數(shù)緩存起來，然后可以通過一個守護(hù)進(jìn)程定期更新或者用戶的某些操作導(dǎo)致緩存失效時，執(zhí)行下面的語句：

SELECT COUNT(*)

FROM city

USE INDEX(PRIMARY);

獲取記錄

下面進(jìn)入這篇文章最重要的部分，獲取分頁要展示的記錄。上面已經(jīng)說過了，大的偏移量會影響性能，所以我們要重寫查詢語句。為了演示，我們創(chuàng)建一個新的表“news”，按照時事性排序(最新發(fā)布的在最前面)，實現(xiàn)一個高性能的分頁。為了簡單，我們就假設(shè)最新發(fā)布的新聞的Id也是最大的。

CREATE TABLE news(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(128) NOT NULL

) ENGINE=InnoDB;

一個比較高效的方式是基于用戶展示的最后一個新聞Id。查詢下一頁的語句如下，需要傳入當(dāng)前頁面展示的最后一個Id。

SELECT *

FROM news WHERE id $last_id

ORDER BY id DESC

LIMIT $perpage

查詢上一頁的語句類似，只不過需要傳入當(dāng)前頁的第一個Id，并且要逆序。

SELECT *

FROM news WHERE id $last_id

ORDER BY id ASC

LIMIT $perpage

上面的查詢方式適合實現(xiàn)簡易的分頁，即不顯示具體的頁數(shù)導(dǎo)航，只顯示“上一頁”和“下一頁”，例如博客中頁腳顯示“上一頁”，“下一頁”的按鈕。但如果要實現(xiàn)真正的頁面導(dǎo)航還是很難的,下面看看另一種方式。

SELECT id

FROM (

SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt

FROM news

JOIN (SELECT @cnt:= 0)T

WHERE id $last_id

ORDER BY id DESC

LIMIT $perpage * $buttons

)C

WHERE cnt = 0;

通過上面的語句可以為每一個分頁的按鈕計算出一個offset對應(yīng)的id。這種方法還有一個好處。假設(shè)，網(wǎng)站上正在發(fā)布一片新的文章，那么所有文章的位置都會往后移一位，所以如果用戶在發(fā)布文章時換頁，那么他會看見一篇文章兩次。如果固定了每個按鈕的offset Id，這個問題就迎刃而解了。Mark Callaghan發(fā)表過一篇類似的博客，利用了組合索引和兩個位置變量，但是基本思想是一致的。

如果表中的記錄很少被刪除、修改，還可以將記錄對應(yīng)的頁碼存儲到表中，并在該列上創(chuàng)建合適的索引。采用這種方式，當(dāng)新增一個記錄的時候，需要執(zhí)行下面的查詢重新生成對應(yīng)的頁號。

SET p:= 0;

UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;

當(dāng)然，也可以新增一個專用于分頁的表，可以用個后臺程序來維護(hù)。

UPDATE pagination T

JOIN (

SELECT id, CEIL((p:= p + 1) / $perpage) page

FROM news

ORDER BY id

)C

ON C.id = T.id

SET T.page = C.page;

現(xiàn)在想獲取任意一頁的元素就很簡單了：

SELECT *

FROM news A

JOIN pagination B ON A.id=B.ID

WHERE page=$offset;

還有另外一種與上種方法比較相似的方法來做分頁，這種方式比較試用于數(shù)據(jù)集相對小，并且沒有可用的索引的情況下—比如處理搜索結(jié)果時。在一個普通的服務(wù)器上執(zhí)行下面的查詢，當(dāng)有2M條記錄時，要耗費(fèi)2sec左右。這種方式比較簡單，創(chuàng)建一個用來存儲所有Id的臨時表即可(這也是最耗費(fèi)性能的地方)。

CREATE TEMPORARY TABLE _tmp (KEY SORT(random))

SELECT id, FLOOR(RAND() * 0x8000000) random

FROM city;

ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;

接下來就可以向下面一樣執(zhí)行分頁查詢了。

SELECT *

FROM _tmp

WHERE OFFSET = $offset

ORDER BY OFFSET

LIMIT $perpage;

簡單來說，對于分頁的優(yōu)化就是。。。避免數(shù)據(jù)量大時掃描過多的記錄。

MySQL深分頁調(diào)優(yōu)實戰(zhàn)

商品評論系統(tǒng)數(shù)據(jù)量為十億量級，因此對評論數(shù)據(jù)庫做分庫分表，單表的評論數(shù)據(jù)在百萬級別。

每個商品的所有評論都是放在一個庫的一張表里，確保作為用戶在分頁查詢一個商品的評論時，一般都是直接從一個庫的一張表里執(zhí)行分頁查詢語句即可。

熱門商品銷量多達(dá)上百萬，商品評論可能多達(dá)幾十萬條。有些用戶就喜歡看商品評論，他就喜歡不停對某個熱門商品評論不斷進(jìn)行分頁，一頁一頁翻，有時候還會用上分頁跳轉(zhuǎn)功能，就是直接輸入自己要跳到第幾頁。

這就涉及針對一個商品幾十萬評論的深分頁問題。

簡化后的對評論表進(jìn)行分頁查詢的SQL：

比如用戶選擇了查看某個商品的評論，因此必須限定 Product_id ，同時還選了只看好評，所以 is_good_commit 也要限定，

接著看第5001頁評論，則limit的o?set=(5001 - 1) * 20，20是每頁的數(shù)量， 此時起始o(jì)?set就是100000，所以limit后100000,20。

評論表最核心的索引 index_product_id ，所以正?？隙ㄗ哌@索引：

該過程有幾十萬次回表查詢，還有十多萬條數(shù)據(jù)的磁盤文件排序，所以要跑個1~2s。如何優(yōu)化呢？

但本案例不是這樣，因為

這倆條件不是一個聯(lián)合索引，所以會出現(xiàn)大量回表，耗時嚴(yán)重。

因此對該案例，一般采取如下方式改造分頁查詢語句：

該SQL的執(zhí)行計劃就會徹底改變其執(zhí)行方式。

通常先執(zhí)行括號里的子查詢，子查詢反而會使用PRIMARY聚簇索引，按聚簇索引id值的倒序方向進(jìn)行掃描，掃描過程中就把符合

的數(shù)據(jù)篩選出來。

比如這里篩選出10w條數(shù)據(jù)，并不需要把符合條件的數(shù)據(jù)都找到，因為limit 100000,20，理論上，只要有100000+20條符合條件的數(shù)據(jù)，且按id有序的，此時就能執(zhí)行根據(jù)limit 100000,20提取到5001頁的這20條數(shù)據(jù)。

接著你會看到執(zhí)行計劃里會針對這個子查詢的結(jié)果集，一個臨時表，進(jìn)行全表掃描，拿到20條數(shù)據(jù)，再對20條數(shù)據(jù)遍歷，每條數(shù)據(jù)都按id去聚簇索引查找一下完整數(shù)據(jù)。

所以本案例，反而是優(yōu)化成這種方式來執(zhí)行分頁，更合適，他只有一個掃描【聚簇索引】篩選符合你分頁所有數(shù)據(jù)的成本：

然后再做一頁20條數(shù)據(jù)的20次回表查詢即可。當(dāng)時做了該分頁優(yōu)化后，發(fā)現(xiàn)分頁語句一下子執(zhí)行時間降低到了幾百ms，達(dá)到優(yōu)化目的。

SQL調(diào)優(yōu)沒有銀彈：

不同場景，要具體情況具體分析，到底慢在哪兒，再針對性優(yōu)化。

MySQL大數(shù)據(jù)量分頁查詢方法及其優(yōu)化

使用子查詢優(yōu)化大數(shù)據(jù)量分頁查詢

這種方式的做法是先定位偏移位置的id，然后再往后查詢，適用于id遞增的情況。

使用id限定優(yōu)化大數(shù)據(jù)量分頁查詢

使用這種方式需要先假設(shè)數(shù)據(jù)表的id是連續(xù)遞增的，我們根據(jù)查詢的頁數(shù)和查詢的記錄數(shù)可以算出查詢的id的范圍，可以使用 id between and 來查詢：

當(dāng)然了，也可以使用in的方式來進(jìn)行查詢，這種方式經(jīng)常用在多表關(guān)聯(lián)的情況下，使用其他表查詢的id集合來進(jìn)行查詢：

但是使用這種in查詢方式的時候要注意的是，某些MySQL版本并不支持在in子句中使用limit子句。

參考 sql優(yōu)化之大數(shù)據(jù)量分頁查詢（mysql） - yanggb - 博客園 (cnblogs.com)

MySQL大表數(shù)據(jù)LIMIT深分頁優(yōu)化

SELECT * FROM table_name LIMIT [offset ,] rows

優(yōu)化前：

原理：mysql會先查詢出10000010（一千萬零一十）條數(shù)據(jù)，然后丟棄前10000000（一千萬）條數(shù)據(jù)，返回最后10（十）條數(shù)據(jù)，所以偏移量越大，性能就越差。

方法一、

使用 主鍵索引 進(jìn)行關(guān)聯(lián)查詢

方法二、

每次記錄當(dāng)前頁的 最后一條id ，作為下一頁的查詢條件

一、正常情況下沒有人會翻到幾千頁，我們可以通過限制可以翻頁的數(shù)量解決這個問題，如：百度、谷歌。

二、手機(jī)端可以使用下拉方式進(jìn)行滾動翻頁，每次記錄當(dāng)前頁的最后一條id，作為下一頁的查詢條件。ES可以使用scroll API

分享名稱：mysql怎么優(yōu)化分頁 mysql優(yōu)化分頁查詢
鏈接地址：http://jinyejixie.com/article16/dossedg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供ChatGPT、、網(wǎng)站改版、品牌網(wǎng)站制作、定制網(wǎng)站、營銷型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)