SQL - MySQL深分页

小鱼儿 2023-09-23 18:35 72阅读 0赞

## 一、MySQL深分页问题 ##

我们在日常开发中，查询数据量比较大的时候，后端基本都会通过前端，移动端传过来的页码，每页数据行数，通过SQL中的 **limit** 进行分页，如果查询页数比较小的时候，不会出现太大问题，但是如果查询页码比较大的时候，性能就会出现急剧下降瓶颈

如：

假设有一个千万量级的表，取1到10条数据

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;

这条语句执行之间在**秒级**完成，查询效率低下，还可能导致接口超时

使用 select column\_name1,column\_name2... from table\_name表名 limit offset, rows 的情况下直接⽤limit 1000000，10 扫描的是约100万条数据，并且是需要回表100W次，也就是说⼤部分性能都耗在随机访问上，到头来只⽤到10条数据（总共取1000010条数据只留10条记录）

这种查询的慢，其实是因为 limit 后面的偏移量太大导致的

#### 1、limit 语法解读 ####

limit用于数据的分页查询，也会用于数据的截取，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
        -- 注意: 关键字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两个关键字，并且各带一个参数，中间没有逗号分割
 *  第三种：SELECT \* FROM table LIMIT rows
    
        -- 截取记录的前五行数据，可以理解为offset的默认值为0
        SELECT column_name1,column_name2... FROM table_name表名 limit 5

#### 2、回表 ####

回表，顾名思义就是回到表中，也就是先通过普通索引扫描出数据所在的行，再通过行主键ID 取出索引中未包含的数据。所以回表的产生也是需要一定条件的，如果一次索引查询就能获得所有的select 记录就不需要回表，如果select 所需获得列中有其他的非索引列，就会发生回表动作。即基于非主键索引的查询需要多扫描一棵索引树

主键索引树的叶子节点直接就是我们要查询的**整行数据**，而非主键索引的叶子节点是**主键的值**，查到主键的值以后，还需要再通过主键的值再进行一次查询

回表，简单说就是mysql内部需要经过两次查询

第一次先索引扫描，然后再通过主键去取索引中未能提供的数据

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这一行的全部数据，这里通过主键id定位到这一行，然后返回数据

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 |
    +-----+---+-------+

## 二、优化方案 ##

### （一）**模仿百度、谷歌方案（前端业务控制）** ###

![c17d3b04aa4f97b7711e393d6cb6751f.png][]

类似于分段。我们给每次只能翻100页、超过一百页的需要重新加载后面的100页。这样就解决了每次加载数量数据大 速度慢的问题了

这种方式比较简单粗暴，就是不允许查看这么靠后的数据

### （二）索引覆盖 + 子查询 ###

根据主键 id，在上面建了索引，先在索引树中找到开始位置的 id 值，再根据找到的 id 值查询行数据

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