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

相关文章

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

1 背景

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

2 分析

分页性能问题，特别是在数据量大的情况下，是一个常见的问题。通常，当我们使用类似 LIMIT 和 OFFSET 的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条数据给用户，这种取法明显不合理。

在《高性能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 总结

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