数据库系列:巨量数据表的分页性能问题

相关文章

数据库系列:MySQL慢查询分析和性能优化
数据库系列:MySQL索引优化总结(综合版)
数据库系列:高并发下的数据字段变更
数据库系列:覆盖索引和规避回表
数据库系列:数据库高可用及无损扩容
数据库系列:使用高区分度索引列提升性能
数据库系列:前缀索引和索引长度的取舍
数据库系列:MySQL引擎MyISAM和InnoDB的比较
数据库系列:InnoDB下实现高并发控制
数据库系列:事务的4种隔离级别
数据库系列:RR和RC下,快照读的区别
数据库系列:MySQL InnoDB锁机制介绍
数据库系列:MySQL不同操作分别用什么锁?
数据库系列:业内主流MySQL数据中间件梳理

1 背景

前段时间面试新员工,跟候选人沟通起来分页性能问题,正好之前遇到过这类问题,就拿出来再讨论下!

2 分析

分页性能问题,特别是在数据量大的情况下,是一个常见的问题。通常,当我们使用类似 LIMITOFFSET 的SQL语句进行分页时,性能问题尤其明显。这是因为随着 OFFSET 的增加,数据库需要跳过更多的行才能获取到需要的数据,这导致了查询时间的增加。

我们在查看前几页的时候,发现速度非常快,比如 limit 200,25,瞬间就出来了。但是越往后,速度就越慢,特别是百万条之后,卡到不行,那这个是什么原理呢。先看一下我们翻页翻到后面时,查询的sql是怎样的:

1 select * from t_name where c_name1='xxx' order by c_name2 limit 2000000,25;

这种查询的慢,其实是因为limit后面的偏移量太大导致的。比如像上面的 limit 2000000,25 ,这个等同于数据库要扫描出 2000025 条数据,然后再丢弃前面的 20000000 条数据,返回剩下25条数据给用户,这种取法明显不合理。

image

在《高性能MySQL》第六章:查询性能优化,对这个问题有过详细说明:

分页操作通常会使用limit加上偏移量的办法实现,同时再加上合适的order by子句。但这会出现一个常见问题:当偏移量非常大的时候,它会导致MySQL扫描大量不需要的行然后再抛弃掉。

3 优化

以下是一些优化分页性能的策略:
1. 使用索引+子查询优化
确保你的查询涉及的列(尤其是用于排序和过滤的列)都被索引,没有索引的列会导致数据库进行全表扫描,这会大大降低查询性能。
确保有索引之后,可以在索引树中找到开始位置的 id值,再根据找到的id值查询行数据。

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;
受影响的行: 0
时间: 0.106s

2. 使用更有效的分页技术
考虑使用基于游标或键的分页而不是基于 OFFSET 的分页。例如,如果你正在根据时间戳或ID排序,你可以记住上一页最后一个条目的时间戳或ID,并从那里开始下一页的查询。

记住上次查找结果的主键位置,避免使用偏移量 offset

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;
受影响的行: 0
时间: 0.001s

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;
受影响的行: 0
时间: 0.000s

3. 减少返回的数据量
只选择需要的列,而不是使用 SELECT * , 减少数据量可以显著提高查询速度。
这个好理解,获取数据,越精简越好,千万别都fetch回来,MySQL准入规范也是这么定的。

4. 分区表
对于非常大的表,考虑使用分区技术。通过将数据分布到不同的分区,可以提高查询性能,因为查询可以在更小的数据集上操作。

5. 使用缓存
对于经常访问的页面,可以考虑使用缓存技术,如Redis或Memcached,来存储查询结果。这样,对于相同的查询请求,可以直接从缓存中获取结果,而不是每次都查询数据库。

6. 考虑物理设计
数据库的物理设计,如硬盘的速度和类型(SSD vs HDD),服务器的内存大小,也会影响查询性能。

4 总结

通过实施上述策略,你可以显著提高数据库分页的性能,尤其是在处理大量数据时,每种方法都有其适用场景,因此我们需要根据具体需求和数据库环境来选择合适的优化策略。

posted @ 2024-07-24 09:15  Hello-Brand  阅读(715)  评论(0编辑  收藏  举报