java八股文面试[数据库]——分页查询优化

蔚落 2024-03-08 08:19 171阅读 0赞

*  一般性分页
    
    一般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：
    
        SELECT * FROM 表名 LIMIT [offset,] rows
    
     *  第一个参数指定第一个返回记录行的偏移量，注意从0开始；
     *  第二个参数指定返回记录行的最大数目；
     *  如果只给定一个参数，它表示返回最大的记录行数目；
    
    **思考1：如果偏移量固定，返回记录量对执行时间有什么影响？**
    
        select * from user limit 10000,1;
        select * from user limit 10000,10;
        select * from user limit 10000,100;
        select * from user limit 10000,1000;
        select * from user limit 10000,10000;
    
    结果：在查询记录时，返回记录量**低于100条**，查询时间基本没有变化，差距不大。随着查询**记录量越大**，所花费的时间也会**越来越多**。
    
    **思考2：如果查询偏移量变化，返回记录数固定对执行时间有什么影响？**
    
        select * from user limit 1,100;
        select * from user limit 10,100;
        select * from user limit 100,100;
        select * from user limit 1000,100;
        select * from user limit 10000,100;
    
    结果：在查询记录时，如果查询记录量相同，偏**移量超过100后就开始随着偏移量增大**，查询时间急剧的增加。（这种分页查询机制，每次都会从数据库**第一条记录开始扫描**，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。）
 *  分页优化方案
    
    **优化1: 通过索引进行分页**
    
    直接进行limit操作 会产生**全表扫描,**速度很慢. Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.
    
    假设ID是连续递增的,我们根据查询的页数和查询的记录数可以算出查询的**id的范围**，然后配合 limit使用
    
        EXPLAIN SELECT * FROM user WHERE id  >= 100001 LIMIT 100;
    
    **优化2：利用子查询优化**
    
        -- 首先定位偏移位置的id
        SELECT id FROM user_contacts LIMIT 100000,1;
        
        -- 根据获取到的id值向后查询.
        EXPLAIN SELECT * FROM user_contacts WHERE id >=
        (SELECT id FROM user_contacts LIMIT 100000,1) LIMIT 100;
    
    原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。

#### 常用分页查询 ####

##### （1）未用索引 #####

对于小数据量的表，我们经常采用`(select * from table limit x,y)`的形式来完成分页查询。例如：

select * from areas limit 0,20; （第一页）
    select * from areas limit 20,20;（第二页）
    select * from areas limit 40,20;（第三页）

使用这种方式进行查询并没有用到索引，所以会进行全表查询。当数据量较大时，速度会非常慢。

![在这里插入图片描述][20201108175214365.png_pic_center]

##### （2）使用索引 #####

当用到索引时，扫描记录数会明显降低。下面使用explain来查看查询情况：

explain select * from areas order by id desc limit 300,20;

![在这里插入图片描述][20201108175804560.png_pic_center]  
这里使用**`order by id`** 目的就是要使用**id中的主键索引**，否则我们的查询就会变为全表检索了。这里查询出的rows代表了我们查询时的扫描记录数，如果limit起始点是300那么记录数为320，如果起始点是1000000，那么查询记录数为1000020，这也就意味着，起始点越往后，查询速度越慢，如果起始点到了末尾，那么就相当于全表检索了，此时索引也就失去了意义。

这种索引的查询方式是我们经常使用的，但是这种查询需要进行回表，即需要进行二次查询，为了尽可能的避免回表操作，我们推荐使用延迟关联来进行查询，来最大程度的使用覆盖索引。

#### 分页查询优化 ####

##### 1 最大id查询法 #####

select * from areas where id>2000 limit 20;

![在这里插入图片描述][20201108180136203.png_pic_center]  
本次测试数据一共有3750条数据，id>2000，即查询的记录数为id=2000之后的数据。

**注意：**  
最大id查询法只能适用于**自增主键**，uuid生成的主键不适合这种方式。

##### 2 between and #####

select * from user where id between 2000 and 2010;

![在这里插入图片描述][20201108191034366.png_pic_center]  
这种方式也 只能适用于自增主键，并且**id没有断裂**，否则不推荐这种方式，使用BETWEEN AND的时候查询出来11条记录，这里需要注意BETWEEN AND包含了两边的边界条件。

##### 3 limit id #####

select * from areas where id>(select id from areas limit 2000,1)
    limit 20;

![在这里插入图片描述][20201108191140349.png_pic_center]  
这样虽然也是扫描了3750行记录，但是由于id是主键，拥有主键索引，所以对一个主键id进行limit范围查询，相比于`select * from areas limit 2000,20;`速度会快很多。

SUBQUERY：在SELECT或WHERE列表中包含了子查询

##### 4 延迟关联 #####

**覆盖索引**：InnoDB的二级索引在叶子节点中保存了行的主键值，所以二级主键能够覆盖查询，可以避免对主键索引的**二次查询**。所需的数据列只需要从索引中就能获得，不用再去回表，即不必再去主键索引区查找数据行。

延迟关联 ：为了尽可能的提高[sql语句][sql]的查询能力，让sql语句中的**部分使用覆盖索引来查询**。

我们在**aid字段**上创建了一个普通索引keyin.

select * from areas a join (select aid from areas 
    limit 2000,20) b on a.aid = b.aid;

![在这里插入图片描述][20201109085548181.png_pic_center]  
在查询的第一个阶段我们使用了覆盖索引`select aid from areas limit 2000,20`，在这个from子句的子查询中找到匹配的aid.然后根据这些aid在外层查询匹配获取所有的列值。虽然不能使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好。

**注意：**

*  4中的limit id也用到了覆盖索引，但是我们推荐使用**延迟关联**join来查询，避免使用子查询。当字段比较多，类型的长度比较长的时候，延迟关联也是比较有优势的。
 *  select操作中使用了覆盖索引时，explain中的extra中会显示Using index.
 *  id值越大，查询优先级越高。
 *  在FROM列表中包含的子查询被标记为DERIVED（衍生），`devived2`指的是areas的**衍生表**。

##### 5 分表查询 #####

mysql推荐一张表的存储不要超过500w数据，查询**400w不到1秒**对于一般的查询来说已经可以了，如果还要更快的话，建议使用分表存储，分表又分两种情况，水平分表于垂直分表。

**（1）水平分表**  
假如一张表的原始数据有900w条数据，可以分三张表存储，每张表存300万的数据，这样查询的时候压力就会小很多，并且效率也很高很多，那问题来了，如何这个水平水表如何实现呢？像可以借助`mycat`之类的中间件，阿里云也提供了数据库的分表技术，当然，你也可以自己手写分表，但是自己手写分表的时候需要注意id重复以及如何定义搭配当前id在那张表中，算法推荐使用hash值。

**（2）垂直分表**  
假如记录有100w，按正常来说查询速度应该不会太慢，但是由于这张表的字段超多，而且还有很多text类型的字段，这个时候我们可以将占用空间比较小的字段分在一张表，占用空间比较大的字段分在另一张表，两张表一一关联，这样，查询的时候就会快很多了。

**（3）冷热表**  
冷热表也是一种分表思想，例如银行查询账单的时候会发现只能查询近几个月的数据，之前的数据需要去柜台查询历史账单，银行查询数据就是用的冷热表的设计思想。

新建两个相同的表，一张表存放近三个月的记录：a表，另一张表存放三个月之前的数据：b表，用户产生的新记录可以存放在a表中，可以在每天凌晨的时候定时扫描a表，只要记录已经在三个月之前了，我们就可以将记录迁移到b表中，对于用户来说，查询近三个月的数据时他们比较敏感的，三个月之前的数据他们查询的可能并不多，所以这样的设计完全是合理的。

##### 6 索引 #####

添加索引可以提高查询效率，如果分页查询牵扯到条件的话，我们可以给条件添加索引，数据库会维护一张对应的索引表，查询的时候会先查询索引表，根据索引表返回的记录直接查询记录表，这样也减少了扫描的行数，但是需要注意，只要发生一下几点，索引**都有可能不会被触发**，一定要注意。

*  查询条件使用is not null
 *  like语句，比如 keyword like ‘%笔记本’，索引失效，%不能再最前面。
 *  OR 前后条件中只要有一个没有添加索引，那么将扫描全表，索引失效。
 *  组合索引：使用组合索引的时候必要要带上第一个索引的字段，否则组合索引不生效。
 *  `> 、< 、 <>。`
 *  **没有单引号的字符串**

##### 7 缓存 #####

把查询结果缓存到redis中，这样直接读取内存，而不用查询硬盘数据。

注意：  
查询优化的重点就在于如何能**扫描最少的记录**，返回查询的结果，使用缓存来降低数据库的访问，但这治标不治本，只有写出漂亮的sql才能让程序立于不败之地。

知识来源：马士兵教育

[分页查询优化方案总结\_长不大的大灰狼的博客-CSDN博客][-CSDN]

[20201108175214365.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/c8c1c9256be14bd48a544e66bf4a3b38.png
[20201108175804560.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/e78f815e66044f26976904f178531dd8.png
[20201108180136203.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/3da8dfcd87a041699d5d9b769403865b.png
[20201108191034366.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/df8db2b50fa54867a5f4f0b0c660099a.png
[20201108191140349.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/f2f7ee7f218b438c8dd4246a884c45fc.png
[sql]: https://so.csdn.net/so/search?q=sql%E8%AF%AD%E5%8F%A5&spm=1001.2101.3001.7020
[20201109085548181.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/bd4720090bb54fc4a0e8d15e28087503.png
[-CSDN]: https://blog.csdn.net/glpghz/article/details/109562262