MySQL优化教程之超大分页查询

背景

基本上只要是做后台开发，都会接触到分页这个需求或者功能吧。基本上大家都是会用MySQL的LIMIT来处理，而且我现在负责的项目也是这样写的。但是一旦数据量起来了，其实LIMIT的效率会极其的低，这一篇文章就来讲一下LIMIT子句优化的。

LIMIT优化

很多业务场景都需要用到分页这个功能，基本上都是用LIMIT来实现。

建表并且插入200万条数据：

# 新建一张t5表
CREATE TABLE `t5` (
 `id` int NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `text` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `ix_name` (`name`),
 KEY `ix_test` (`text`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 创建存储过程插入200万数据
CREATE PROCEDURE t5_insert_200w()
BEGIN
 DECLARE i INT;
 SET i=1000000;
 WHILE i<=3000000 DO
 INSERT INTO t5(`name`,text) VALUES('god-jiang666',concat('text', i));
 SET i=i+1;
 END WHILE;
END;

# 调用存储过程插入200万数据
call t5_insert_200w();

在翻页比较少的情况下，LIMIT是不会出现任何性能上的问题的。

但是如果用户需要查到最后面的页数呢？

通常情况下，我们要保证所有的页面可以正常跳转，因为不会使用order by xxx desc这样的倒序SQL来查询后面的页数，而是采用正序顺序来做分页查询：

select * from t5 order by text limit 100000, 10;

采用这种SQL查询分页的话，从200万数据中取出这10行数据的代价是非常大的，需要先排序查出前1000010条记录，然后抛弃前面1000000条。我的macbook pro跑出来花了5.578秒。

接下来我们来看一下，上面这条SQL语句的执行计划：

explain select * from t5 order by text limit 1000000, 10;

从执行计划可以看出，在大分页的情况下，MySQL没有走索引扫描，即使text字段我已经加上了索引。

这是为什么呢？

回到MySQL索引（二）如何设计索引中有提及到，MySQL数据库的查询优化器是采用了基于代价的，而查询代价的估算是基于CPU代价和IO代价。

如果MySQL在查询代价估算中，认为全表扫描方式比走索引扫描的方式效率更高的话，就会放弃索引，直接全表扫描。

这就是为什么在大分页的SQL查询中，明明给该字段加了索引，但是MySQL却走了全表扫描的原因。

然后我们继续用上面的查询SQL来验证我的猜想：

explain select * from t5 order by text limit 7774, 10;


explain select * from t5 order by text limit 7775, 10;

以上的实验均在我的mbp上运行的，在7774这个临界点上，MySQL分别采用了索引扫描和全表扫描的查询优化方式。

所以可以认为MySQL会根据它自己的代价查询优化器来判断是否使用索引。

由于MySQL的查询优化器的算法核心是我们无法人工干预的，所以我们的优化思路就要着手于如何让分页维持在最佳的的分页临界点。

优化方式

1、使用覆盖索引

如果一条SQL语句，通过索引可以直接获取查询的结果，不再需要回表查询，就称这个索引为覆盖索引。

在MySQL数据库中使用explain关键字查看执行计划，如果extra这一列显示Using index，就表示这条SQL语句使用了覆盖索引。

让我们来对比一下使用了覆盖索引，性能会提升多少吧。

# 没有使用覆盖索引
select * from t5 order by text limit 1000000, 10;

这次查询花了3.690秒，让我们看一下使用了覆盖索引优化会提升多少性能吧。

# 使用了覆盖索引
select id, `text` from t5 order by text limit 1000000, 10;

从上面的对比中，超大分页查询中，使用了覆盖索引之后，花了0.201秒，而没有使用覆盖索引花了3.690秒，提高了18倍多，这在实际开发中，就是一个大的性能优化了。（该数据在我的mbp上运行得出）

2、子查询优化

因为实际开发中，用SELECT查询一两列操作是非常少的，因此上述的覆盖索引的适用范围就比较有限。

所以我们可以通过把分页的SQL语句改写成子查询的方法获得性能上的提升。

select * from t5 where id>=(select id from t5 order by text limit 1000000, 1) limit 10;

其实使用这种方法，提升的效率和上面使用了覆盖索引基本一致。

但是这种优化方法也有局限性：

  • 这种写法，要求主键ID必须是连续的
  • Where子句不允许再添加其他条件

3、延迟关联

和上述的子查询做法类似，我们可以使用JOIN，先在索引列上完成分页操作，然后再回表获取所需要的列。

select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;

从实验中可以得出，在采用JOIN改写后，上面的两个局限性都已经解除了，而且SQL的执行效率也没有损失。

4、记录上次查询结束的位置

和上面使用的方法都不同，记录上次结束位置优化思路是使用某种变量记录上一次数据的位置，下次分页时直接从这个变量的位置开始扫描，从而避免MySQL扫描大量的数据再抛弃的操作。

select * from t5 where id>=1000000 limit 10;

根据以上实验，不难得出，由于使用了主键索引做分页操作，SQL的性能是最快的。

总结

  • 介绍了超大分页查询性能过差的原因，还有分享了几个优化思路
  • 超大分页的优化思路就是让分页的SQL尽量在最佳的性能区间执行，不要触发全表扫描即可
  • 希望以上的分享，可以让你们在MySQL这条路上少走弯路～～～

参考资料

  • 《MySQL性能优化》第六章 查询优化性能
  • 《数据库查询优化器的艺术》

到此这篇关于MySQL优化教程之超大分页查询的文章就介绍到这了,更多相关MySQL超大分页查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL百万级数据分页查询优化方案

    当需要从数据库查询的表有上万条记录的时候,一次性查询所有结果会变得很慢,特别是随着数据量的增加特别明显,这时需要使用分页查询.对于数据库分页查询,也有很多种方法和优化的点.下面简单说一下我知道的一些方法. 准备工作 为了对下面列举的一些优化进行测试,下面针对已有的一张表进行说明. 表名:order_history 描述:某个业务的订单历史表 主要字段:unsigned int id,tinyint(4) int type 字段情况:该表一共37个字段,不包含text等大型数组,最大为varcha

  • 在大数据情况下MySQL的一种简单分页优化方法

    通常应用需要对表中的数据进行翻页,如果数据量很大,往往会带来性能上的问题: root@sns 07:16:25>select count(*) from reply_0004 where thread_id = 5616385 and deleted = 0; +----+ | count(*) | +----+ | 1236795 | +----+ 1 row in set (0.44 sec) root@sns 07:16:30>select id from reply_0004 wher

  • MySQL 百万级分页优化(Mysql千万级快速分页)

    以下分享一点我的经验 一般刚开始学SQL的时候,会这样写 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在数据达到百万级的时候,这样写会慢死 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 也许耗费几十秒 网上很多优化的方法是这样的 复制代码 代码如下: SELECT * FROM table WHERE id >= (SELECT id FROM

  • MySQL limit使用方法以及超大分页问题解决

    前言 日常开发中,我们使用mysql来实现分页功能的时候,总是会用到mysql的limit语法.而怎么使用却很有讲究的,今天来总结一下. limit语法 limit语法支持两个参数,offset和limit,前者表示偏移量,后者表示取前limit条数据. 例如: ## 返回符合条件的前10条语句 select * from user limit 10 ## 返回符合条件的第11-20条数据 select * from user limit 10,20 从上面也可以看出来,limit n 等价于l

  • mysql limit分页优化方法分享

    同样是取10条数据 select * from yanxue8_visit limit 10000,10 和 select * from yanxue8_visit limit 0,10 就不是一个数量级别的. 网上也很多关于limit的五条优化准则,都是翻译自MySQL手册,虽然正确但不实用.今天发现一篇文章写了些关于limit优化的,很不错. 文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据.根据他的数据,明显要好于直接使用limit.这

  • MYSQL分页limit速度太慢的优化方法

    在mysql中limit可以实现快速分页,但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了,否则可能卡死你的服务器哦.    当一个表数据有几百万的数据的时候成了问题! 如 * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢,可以按照一下方法解决     第一页会很快    PERCONA PERFORMANCE CONFERENCE 2009上,来自雅虎的几位工程师带来了一篇"EfficientPag

  • mysql分页时offset过大的Sql优化经验分享

    发现问题 当我们展示一个列表中的内容时,难免会遇到分页问题,因为列表中的内容数量可能很多,但是用户能一次看到的界面大小是有限的,不可能一个界面展示所有的内容,从后端一次性取太多的数据也会给后端造成额外的压力. 通常分页查询的时候会使用这样的语句: SELECT * FROM table where condition1 = 0 and condition2 = 0 and condition3 = -1 and condition4 = -1 order by id asc LIMIT 2000

  • mysql千万级数据分页查询性能优化

    mysql数据量大时使用limit分页,随着页码的增大,查询效率越低下. 实验 1.直接使用用limit start, count分页语句: select * from order limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下: select * from order limit 10, 20 0.016秒 select * from order limit 100, 20

  • mysql 分页优化解析

    如果你的数据量有几十万条,用户又搜索一些很通俗的词,然后要依次读最后几页重温旧梦.mysql该很悲壮的不停操作硬盘. 所以,可以试着让mysql也存储分页,当然要程序配合.(这里只是提出一个设想,欢迎大家一起讨论) ASP的分页:在ASP系统中有Recordset对象来实现分页,但是大量数据放在内存中,而且不知道什么时候才失效(请ASP高手指点). SQL数据库分页:用存储过程+游标方式分页,具体实现原理不是很清楚,设想如果用一次查询就得到需要的结果,或者是id集,需要后续页时只要按照结果中的I

  • MySQL优化教程之超大分页查询

    背景 基本上只要是做后台开发,都会接触到分页这个需求或者功能吧.基本上大家都是会用MySQL的LIMIT来处理,而且我现在负责的项目也是这样写的.但是一旦数据量起来了,其实LIMIT的效率会极其的低,这一篇文章就来讲一下LIMIT子句优化的. LIMIT优化 很多业务场景都需要用到分页这个功能,基本上都是用LIMIT来实现. 建表并且插入200万条数据: # 新建一张t5表 CREATE TABLE `t5` ( `id` int NOT NULL AUTO_INCREMENT, `name`

  • MySQL百万级数据量分页查询方法及其优化建议

    数据库SQL优化是老生常谈的问题,在面对百万级数据量的分页查询,又有什么好的优化建议呢?下面将列举了一些常用的方法,供大家参考学习! 方法1: 直接使用数据库提供的SQL语句 语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N 适应场景: 适用于数据量较少的情况(元组百/千级) 原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余

  • mysql、mssql及oracle分页查询方法详解

    本文实例讲述了mysql.mssql及oracle分页查询方法.分享给大家供大家参考.具体分析如下: 分页查询在web开发中是最常见的一种技术,最近在通过查资料,有一点自己的心得 一.mysql中的分页查询 注: m=(pageNum-1)*pageSize;n= pageSize; pageNum是要查询的页码,pageSize是每次查询的数据量, 方法一: select * from table order by id limit m, n; 该语句的意思为,查询m+n条记录,去掉前m条,返

  • MySQL优化方案之开启慢查询日志

    MySQL优化方案之开启慢查询日志

    目录 前言 设置慢查询日志 测试 附:日志分析工具mysqldumpslow 总结 前言 本方案只适应于小的项目.项目未上线或者紧急情况下可采用这种方式,一旦开启慢日志查询会增加数据库的压力,所以一般采用后台对数据操作时间写入日志文件中,每一周定时进行清除日志 mysql优化方案:开启慢查询日志(查询sql执行超过一秒以上sql等等) 开启慢查询日志:可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能. 参数说明: slow_query_log

  • 详解MySQL的limit用法和分页查询语句的性能分析

    limit用法 在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能. SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT 接受一个或两个数字参数.参数必须是一个整数常量.如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目.初始记

  • 记一次因线上mysql优化器误判引起慢查询事件

    记一次因线上mysql优化器误判引起慢查询事件

    前言: 收到疯狂的慢查询及请求超时报警,通过metrics分析出来自mysql请求的异常,cli -> show proceslist 看到很多慢查询. 先前该sql是没有的,后面因为数据量的增长才出现了这问题. 虽然feeds表大到一个亿,但因为feeds流信息有近期热的特征,所以不是因为 innodb_buffer_pool_size 低效引起的io频繁. 后来经过进一步explain执行计划分析得出了原因,mysql查询优化器选择了他认为高效的索引. mysql查询优化器大多数情况是靠谱的

  • MySQL优化教程之慢查询日志实践

    MySQL优化教程之慢查询日志实践

    一.慢查询日志概念 对于SQL和索引的优化问题,我们会使用explain去分析SQL语句.但是真正的企业级项目有成千上万条SQL,我们不可能从头开始一条一条explain去分析.我们从什么地方可以获取那些运行时间长,耗性能的SQL?? 我们可以打开慢查询日志: 根据具体的业务和并发量来预估一个时间上限(20ms.100ms),设置好后开启业务,压测后打开慢查询日志,就会看到超过执行时间的SQL,然后使用explain分析这些耗时的SQL语句 步骤如下: 打开慢查询日志开关slow_query_l

  • MySQL 分页查询的优化技巧

    在有分页查询的应用中,包括 LIMIT 和 OFFSET 的查询十分常见,而且几乎每个都会有一个 ORDER BY 子句.如果使用索引排序的话将对性能优化十分有帮助,否则服务端需要做很多文件排序. 一个高频的问题是 offset 的值过大.如果查询类似 LIMIT 10000, 20,将会产生10020行,并将之前的10000行丢弃,这样的代价很高.假设所有的页使用相同的频次访问,这样的查询将平均扫描一半数据表.为了优化他们,你可以在分页视图中限制最多可访问的页数,或者让大便宜的查询更有效. 一

  • MySQL优化案例系列-mysql分页优化

    通常,我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询.例如下面这个SQL: SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10; 或者像下面这个不带任何条件的分页SQL: SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10; 一般而言,分页SQL的耗时随着 start 值的增加而急剧增加,我们来看下面这2个不同起始值的分页SQL执行

随机推荐