mysql怎么優(yōu)化分頁 mysql優(yōu)化分頁查詢

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

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

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

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

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

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

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

帶條件的查詢：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

很多應用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導航欄。然而，如何通過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)存中。就算我們假設大部分網(wǎng)站的用戶只訪問前幾頁數(shù)據(jù)，但少量的大的分頁偏移量的請求也會對整個系統(tǒng)造成危害。Facebook意識到了這一點，但Facebook并沒有為了每秒可以處理更多的請求而去優(yōu)化數(shù)據(jù)庫，而是將重心放在將請求響應時間的方差變小。

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

SELECT COUNT(*)

FROM city;

然而，上面的SQL在采用InnoDB為存儲引擎時需要耗費9.28sec。一個不正確的優(yōu)化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能夠在分頁查詢時事先準備好符合條件的記錄數(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情況就會復雜一些，因為InnoDB不保存表的具體行數(shù)。

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

SELECT COUNT(*)

FROM city

USE INDEX(PRIMARY);

獲取記錄

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

CREATE TABLE news(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(128) NOT NULL

) ENGINE=InnoDB;

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

SELECT *

FROM news WHERE id $last_id

ORDER BY id DESC

LIMIT $perpage

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

SELECT *

FROM news WHERE id $last_id

ORDER BY id ASC

LIMIT $perpage

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

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

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

SET p:= 0;

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

當然，也可以新增一個專用于分頁的表，可以用個后臺程序來維護。

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ù)集相對小，并且沒有可用的索引的情況下—比如處理搜索結果時。在一個普通的服務器上執(zhí)行下面的查詢，當有2M條記錄時，要耗費2sec左右。這種方式比較簡單，創(chuàng)建一個用來存儲所有Id的臨時表即可(這也是最耗費性能的地方)。

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í)行分頁查詢語句即可。

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

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

簡化后的對評論表進行分頁查詢的SQL：

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

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

評論表最核心的索引 index_product_id ，所以正常肯定走這索引：

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

但本案例不是這樣，因為

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

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

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

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

的數(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í)行計劃里會針對這個子查詢的結果集，一個臨時表，進行全表掃描，拿到20條數(shù)據(jù)，再對20條數(shù)據(jù)遍歷，每條數(shù)據(jù)都按id去聚簇索引查找一下完整數(shù)據(jù)。

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

然后再做一頁20條數(shù)據(jù)的20次回表查詢即可。當時做了該分頁優(yōu)化后，發(fā)現(xiàn)分頁語句一下子執(zhí)行時間降低到了幾百ms，達到優(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ù)據(jù)表的id是連續(xù)遞增的，我們根據(jù)查詢的頁數(shù)和查詢的記錄數(shù)可以算出查詢的id的范圍，可以使用 id between and 來查詢：

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

但是使用這種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ù)，所以偏移量越大，性能就越差。

方法一、

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

方法二、

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

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

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

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

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

廣告

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