MySQL分页优化

最近,帮同事重写了一个MySQL SQL语句,该SQL语句涉及两张表,其中一张表是字典表(需返回一个字段),另一张表是业务表(本身就有150个字段,需全部返回),当然,字段的个数是否合理在这里不予评价。平时,返回的数据大概5w左右,系统尚能收到数据。但12月31日那天,数据量大概20w,导致SQL执行时间过长,未能在规定的时间内反馈结果,于是系统直接报错。

一般的思路是用MySQL的分页功能,即直接在原SQL语句后面增加LIMIT子句。但请注意,虽然你看到的反馈结果只是LIMIT后面指定的数量,于是想当然的以为MySQL只是检索了指定数量的数据,然后给予返回。其实,MySQL内部实现的原理是,检索所有符合where条件的记录,然后返回指定数量的记录。从这个角度来看,直接在原SQL语句后面添加LIMIT子句只能说是一种可以实现功能的方案,但未必最优。

具体在本例中,首先我们来看一下150个字段的表的统计信息:

一行大概就占2k,而Innodb默认页的大小为16k,这意味着,一个页中最多可存储8行的数据。随机读的可能性大大增加。而这无疑会对数据库系统的IO造成极大的压力。

优化前

如果采用上述方案,即直接在原SQL语句后面增加LIMIT子句,下面,我们来看看它的执行情况。

首先,直接添加LIMIT子句后的SQL语句如下(已省略a1表的150个字段和a2中的一个字段):

代码如下:

FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) WHERE STATUS<>'00' AND settle_date=20151230 limit 50000,10000;

其执行时间如下:

大概执行了32s,绝大部分都花费到Sending data上了。Sending data指的是服务器检索数据,读取数据,并将数据返回给客户端的时间。

关于上述执行结果,有以下几点需要说明:

1. 这是SQL语句多次执行后的结果,这样就可以排除结果缓存的影响,事实上,每次查询的时长都是32s左右。

2. 为什么选用的是limit 50000,10000,而不是0,10000,这个主要是考虑到对于LIMIT子句来说,越到后面,分页的成本越高。基于此,选择了中间值来作为分页的结果。

该语句的执行计划如下:

优化后:

优化的思路:

只对该表的主键进行分页,然后用返回的主键作为子查询的结果,来检索该表其它字段的值。

改写后的SQL语句如下:

代码如下:

FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) where seq_id in (select seq_id from (select seq_id FROM upay_csys_scquery_txn_log_his a1  WHERE STATUS<>'00' AND settle_date=20151230 order by 1 limit 50000,10000) as t);

其执行时间如下:

大概3s多,比第一种方案快了差不多10倍,效果显著。

下面来看看其执行计划(explain extended)

 总结:

1. 改写后的语句原本如下:

代码如下:

FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) where seq_id in (select seq_id FROM upay_csys_scquery_txn_log_his a1  WHERE STATUS<>'00' AND settle_date=20151230 order by 1 limit 50000,10000);

但MySQL报以下错误:

代码如下:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

需再增加一个嵌套子查询,

比如这样的语句是不能正确执行的。

代码如下:

select * from table where id in (select id from table limit 12);

但是,只要你再加一层就行。如:

代码如下:

select * from table where id in (select t.id from (select * from table limit 12)as t)

这样就可以绕开limit子查询的问题。
问题解决。

2. 如果想查看MySQL查询优化器等价改写后的SQL语句,可首先通过explain extended得到具体的执行计划,然后通过show warnings查看。

具体在本例中,等价改写后的SQL语句如下:

与设想中的执行顺序一致~

3. 如何查看MySQL语句各步骤的执行时间。

以上就是本文的全部内容,希望对大家MySQL分页优化有所帮助。

时间: 2016-04-19

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的一种简单分页优化方法

通常应用需要对表中的数据进行翻页,如果数据量很大,往往会带来性能上的问题: 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千万级快速分页

看例子: 数 据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引.这是一个基本的新闻系统的简单模型.现在往里面填充数据,填充10万篇新闻. 最后collect 为 10万条记录,数据库表占用硬盘1.6G. OK ,看下面这条sql语句: select id,title from collect limit 1000,10; 很快:基本上0.01秒就OK

mysql 分页优化解析

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

Mysql limit 优化,百万至千万级快速分页 复合索引的引用并应用于轻量级框架

MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇新闻的小型系统怎么写都可以,用xx框架可以实现快速开发.可是数据量到了10万,百万至千万,他的性能还能那么高吗?一点小小的失误,可能造成整个系统的改写,甚至更本系统无法正常运行!好了,不那么多废话了.用事实说话,看例子: 数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引.这是一个

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中分页优化的实例详解

通常,我们会采用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执行

Mysql中Join的使用实例详解

在前几章节中,我们已经学会了如果在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据表中读取数据. 本章节我们将向大家介绍如何使用MySQL 的 JOIN 在两个或多个表中查询数据. 你可以在SELECT, UPDATE 和 DELETE 语句中使用Mysql 的 join 来联合多表查询. 以下我们将演示MySQL LEFT JOIN 和 JOIN 的使用的不同之处. 在命令提示符中使用JOIN 我们在RUNOOB数据库中有两张表 tcount_tbl 和 runoob_t

mysql中的sql_mode模式实例详解

本文实例讲述了mysql中的sql_mode模式.分享给大家供大家参考,具体如下: mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法,数据校验等!我们可以通过以下方式查看当前数据库使用的sql_mode: mysql> select @@sql_mode; +----------------------------------------------------------------+ | @@sql_mode | +------------------

MySQL数据类型中DECIMAL的用法实例详解

MySQL数据类型中DECIMAL的用法实例详解 在MySQL数据类型中,例如INT,FLOAT,DOUBLE,CHAR,DECIMAL等,它们都有各自的作用,下面我们就主要来介绍一下MySQL数据类型中的DECIMAL类型的作用和用法. 一般赋予浮点列的值被四舍五入到这个列所指定的十进制数.如果在一个FLOAT(8, 1)的列中存储1. 2 3 4 5 6,则结果为1. 2.如果将相同的值存入FLOAT(8, 4) 的列中,则结果为1. 2 3 4 6. 这表示应该定义具有足够位数的浮点列以便

Oracle分页查询的实例详解

Oracle分页查询的实例详解 1.Oracle分页查询: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM tab) A WHERE ROWNUM <= 40 ) WHERE RN >= 21; 这个分页比下面的执行时间少,效率高. 2. select * from (select c.*,rownum rn from tab c) where rn between 21 and 40 对比这两种写法,绝大多数的情况下,第一个

Mybatis分页插件的实例详解

Mybatis分页插件的实例详解 1.前言: 我们知道,在MySQL中,分页的sql是使用limit来做,如果我们自己写sql,那分页肯定是没有任何问题的.但是一旦model多了起来,复杂了起来,我们很自然的想到使用mybatis的逆向工程来生成相应的po和mapper,但是同时也会带来弊端,比如这里的分页问题就不好解决了. 可能有人会说,我可以修改生成的文件,没错,这是可行的,但是一般我们通过逆向工程生成的文件,都不会去动它,所以这个时候,就需要使用分页插件来解决了. 如果你也在用Mybati

mysql记录耗时的sql实例详解

mysql记录耗时的sql mysql可以把耗时的sql或未使用索引的sql都记录在slow log里,供优化分析使用. 1.mysql慢查询日志启用: mysql慢查询日志对于跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句,那如何打开mysql的慢查询日志记录呢? mysql> show variables like 'log_slow_queries'; +------------------+-------+ | Variable_name | Value | +-

JDBC中resutset接口操作实例详解

本文主要向大家展示JDBC接口中resutset接口的用法实例,下面我们看看具体内容. 1. ResultSet细节1 功能:封锁结果集数据 操作:如何获得(取出)结果 package com.sjx.a; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import org.junit.Test; //1. next方

Android通过json向MySQL中读写数据的方法详解【读取篇】

本文实例讲述了Android通过json向MySQL中读取数据的方法.分享给大家供大家参考,具体如下: 首先 要定义几个解析json的方法parseJsonMulti,代码如下: private void parseJsonMulti(String strResult) { try { Log.v("strResult11","strResult11="+strResult); int index=strResult.indexOf("[");

Java中的动态和静态编译实例详解

Java中的动态和静态编译实例详解 首先,我们来说说动态和静态编译的问题. Q: java和javascript有什么区别?    总结了一下:有以下几点吧: 1.首先从运行环境来说java代码是在JVM上编译成class文件,而javascript则直接在浏览器上加载运行. 2.由第一点可看出,java代码需要编译,而javascript不需要编译. 3.从语言性质来说,java是一种高级编程语言,对变量检查要求严格,javascript只是一个简单的解释性的脚本语言,对变量检查及要求很弱.