一、MySQL深分頁問題

我們在日常開發(fā)中，查詢數(shù)據(jù)量比較大的時候，后端基本都會通過前端，移動端傳過來的頁碼，每頁數(shù)據(jù)行數(shù)，通過SQL中的 limit 進行分頁，如果查詢頁數(shù)比較小的時候，不會出現(xiàn)太大問題，但是如果查詢頁碼比較大的時候，性能就會出現(xiàn)急劇下降瓶頸

如：

假設(shè)有一個千萬量級的表，取1到10條數(shù)據(jù)

select column_name1，column_name2... from table limit 0,10;

select column_name1，column_name2... from table limit 1000,10;

這兩條語句查詢時間應該在毫秒級完成

select column_name1，column_name2... from table limit 1000000,10;

這條語句執(zhí)行之間在秒級完成，查詢效率低下，還可能導致接口超時

使用select column_name1,column_name2... from table_name表名 limit offset, rows 的情況下直接?limit 1000000，10 掃描的是約100萬條數(shù)據(jù)，并且是需要回表100W次，也就是說?部分性能都耗在隨機訪問上，到頭來只?到10條數(shù)據(jù)（總共取1000010條數(shù)據(jù)只留10條記錄）

這種查詢的慢，其實是因為 limit 后面的偏移量太大導致的

1、limit 語法解讀

limit用于數(shù)據(jù)的分頁查詢，也會用于數(shù)據(jù)的截取，limit的用法：

SELECT column_name1,column_name2... FROM table_name表名 LIMIT offset,rows
或
SELECT column_name1,column_name2... FROM table_name表名 LIMIT rows OFFSET offset  

注：

table_name ：表名

column_name：字段名

第一種：SELECT * FROM table LIMIT offset, rows # 常用形式

-- 從0開始，截取5條記錄，即檢索行為1到5
SELECT column_name1,column_name2... FROM table_name表名 limit 0,5
-- 注意: 關(guān)鍵字limit后面的兩個參與用逗號分割

第二種：SELECT * FROM table LIMIT rows OFFSET offset

-- 從0開始，截取5條記錄，即檢索行為1到5
SELECT column_name1,column_name2... FROM table_name表名 limit 5 offset 0
-- 注意: 使用limit和offset兩個關(guān)鍵字，并且各帶一個參數(shù)，中間沒有逗號分割

第三種：SELECT * FROM table LIMIT rows

-- 截取記錄的前五行數(shù)據(jù)，可以理解為offset的默認值為0
SELECT column_name1,column_name2... FROM table_name表名 limit 5

2、回表

回表，顧名思義就是回到表中，也就是先通過普通索引掃描出數(shù)據(jù)所在的行，再通過行主鍵ID取出索引中未包含的數(shù)據(jù)。所以回表的產(chǎn)生也是需要一定條件的，如果一次索引查詢就能獲得所有的select記錄就不需要回表，如果select所需獲得列中有其他的非索引列，就會發(fā)生回表動作。即基于非主鍵索引的查詢需要多掃描一棵索引樹

主鍵索引樹的葉子節(jié)點直接就是我們要查詢的整行數(shù)據(jù)，而非主鍵索引的葉子節(jié)點是主鍵的值，查到主鍵的值以后，還需要再通過主鍵的值再進行一次查詢

回表，簡單說就是mysql內(nèi)部需要經(jīng)過兩次查詢

第一次先索引掃描，然后再通過主鍵去取索引中未能提供的數(shù)據(jù)

create `table` tb_name(
    `id` int(11) not null auto_increment , 
    `k` int(11) default '0' ,
    `name` varchar(16),
    primary key(id)
    index (k)
)engine=InnoDB;

我們提取id=500這一行的全部數(shù)據(jù)，這里通過主鍵id定位到這一行，然后返回數(shù)據(jù)

select * from T where ID=500;
+-----+---+-------+
| id  | k | name  |
+-----+---+-------+
| 500 | 5 | name5 |
+-----+---+-------+

這里我們先通過普通索引，搜索 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜索一次。這個過程即為回表

select * from T where k=5;
+-----+---+-------+
| id  | k | name  |
+-----+---+-------+
| 500 | 5 | name5 |
+-----+---+-------+

二、優(yōu)化方案

（一）模仿百度、谷歌方案（前端業(yè)務控制）

類似于分段。我們給每次只能翻100頁、超過一百頁的需要重新加載后面的100頁。這樣就解決了每次加載數(shù)量數(shù)據(jù)大 速度慢的問題了

這種方式比較簡單粗暴，就是不允許查看這么靠后的數(shù)據(jù)

（二）索引覆蓋 + 子查詢

根據(jù)主鍵 id，在上面建了索引，先在索引樹中找到開始位置的 id 值，再根據(jù)找到的 id 值查詢行數(shù)據(jù)

SELECT 
    id,name,age 
FROM
    t_user user
WHERE
    user.id = (select MIN(id) from t_user where age = #{age})

SELECT 
    id,name,age 
FROM 
    t_user 
WHERE 
    id >= (SELECT id FROM t_user order by id LIMIT 80000,1) 
LIMIT 10

（三）起始位置重定義（記錄每次取出的最大id， 然后where id > 最大id）

這種方法適用于：除了主鍵ID等離散型字段外，也適用連續(xù)型字段datetime等

最大id由前端分頁 pageNum 和 pageIndex 計算出來

select * from table_name Where id > 最大id limit 10000, 10;

到此這篇關(guān)于MySQL深分頁問題解決思路的文章就介紹到這了,更多相關(guān)MySQL深分頁內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論