一、MySQL深分頁(yè)問(wèn)題

我們?cè)谌粘ｉ_(kāi)發(fā)中，查詢數(shù)據(jù)量比較大的時(shí)候，后端基本都會(huì)通過(guò)前端，移動(dòng)端傳過(guò)來(lái)的頁(yè)碼，每頁(yè)數(shù)據(jù)行數(shù)，通過(guò)SQL中的 limit 進(jìn)行分頁(yè)，如果查詢頁(yè)數(shù)比較小的時(shí)候，不會(huì)出現(xiàn)太大問(wèn)題，但是如果查詢頁(yè)碼比較大的時(shí)候，性能就會(huì)出現(xiàn)急劇下降瓶頸

如：

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

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

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

這兩條語(yǔ)句查詢時(shí)間應(yīng)該在毫秒級(jí)完成

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

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

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

這種查詢的慢，其實(shí)是因?yàn)?limit 后面的偏移量太大導(dǎo)致的

1、limit 語(yǔ)法解讀

limit用于數(shù)據(jù)的分頁(yè)查詢，也會(huì)用于數(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開(kāi)始，截取5條記錄，即檢索行為1到5
SELECT column_name1,column_name2... FROM table_name表名 limit 0,5
-- 注意: 關(guān)鍵字limit后面的兩個(gè)參與用逗號(hào)分割

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

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

第三種：SELECT * FROM table LIMIT rows

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

2、回表

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

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

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

第一次先索引掃描，然后再通過(guò)主鍵去取索引中未能提供的數(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ù)，這里通過(guò)主鍵id定位到這一行，然后返回?cái)?shù)據(jù)

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

這里我們先通過(guò)普通索引，搜索 k 索引樹(shù)，得到 ID 的值為 500，再到 ID 索引樹(shù)搜索一次。這個(gè)過(guò)程即為回表

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

二、優(yōu)化方案

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

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

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

（二）索引覆蓋 + 子查詢

根據(jù)主鍵 id，在上面建了索引，先在索引樹(shù)中找到開(kāi)始位置的 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由前端分頁(yè) pageNum 和 pageIndex 計(jì)算出來(lái)

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

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

最新評(píng)論