文档章节

mysql5.6 分页查询优化

爱吃窝窝头
 爱吃窝窝头
发布于 04/27 20:30
字数 660
阅读 25
收藏 5


mysql5.6 分页查询优化

场景:

表结构:主键(非自增)contentCode(varchar),过滤条件列为updateTime(timeStamp),已经为timestamp建立索引。

搜索sql为:

SELECT
	*
FROM
	my_hello_table
WHERE
	updateTime >= '2019-04-21 14:37:38'
AND updateTime <= '2019-04-27 16:36:57'
LIMIT 599000,
 1000

 

问题:数据在分页到60w后,分页查询时间为5.8s左右。无法忍受。

原因:虽然走了索引,但mysq5.6 对于分页的操作是先根据过滤条件去索引查询出所有的updateTime,然后根据updateTime依次查询出60w数据,然后抛弃前59w9k条查询出数据,然后获取最后的1k条。

分页的这种越到后面用时越长的问题,是mysql5的一个失误,在mysql8之后的版本貌似得到了解决。

优化:总体思路是走索引,走索引,还是走索引。

首先我们通过分页条件查询,只走updateTime索引,然后获取所有的主键,此时mysql是不回主表的。然后通过in 查询主表中所有在此范围的数据。

参考 https://www.cnblogs.com/lpfuture/p/5772055.html

有如下sql:

SELECT
	*
FROM
	my_hello_table
WHERE
	contentCode IN (
		SELECT
			contentCode
		FROM
			my_hello_table
		WHERE
			updateTime > '2019-04-21 14:37:38'
		AND updateTime <= '2019-04-27 16:36:57'
		LIMIT 599000,
		1000
	)
);

但是,可惜的是,会有如下问题:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

解决方法有 伪表进行表连接操作 和 in里面使用limit 参考:

https://www.cnblogs.com/c-h-y/p/9946813.html

最后 的sql为:

in 里面 用limit 的sql:

SELECT
    *
FROM
    my_hello_table
WHERE
    contentCode IN (
        SELECT
            t.contentCode
        FROM
            (
                SELECT
                    contentCode
                FROM
                    my_hello_table
                WHERE
                    updateTime > '2019-04-21 14:37:38'
                AND updateTime <= '2019-04-27 16:36:57'
                LIMIT 599000,
                1000
            ) AS t
    );


 伪表 表连接

SELECT
    a.*
FROM
    my_hello_table a
INNER JOIN (
    SELECT
        contentCode
    FROM
        my_hello_table
    WHERE
        updateTime > '2019-04-21 14:37:38'
    AND updateTime <= '2019-04-27 16:36:57'
    LIMIT 599000,
    1000
) AS b ON a.contentCode = b.contentCode

两种方式推荐第二种。避免了in语句。进行explain诊断会发现第一种效率高很多。

最后经过测试,查询时间由原来的5.8秒 优化到1.2s左右,优化率搞到400%。

记录下sql语句的完整执行顺序

1、from子句组装来自不同数据源的数据;

2、where子句基于指定的条件对记录行进行筛选; 

3、group by子句将数据划分为多个分组; 

4、使用聚集函数进行计算;

5、使用having子句筛选分组; 

6、计算所有的表达式; 

7、使用order by对结果集进行排序。

© 著作权归作者所有

上一篇: 动态代理模式
爱吃窝窝头
粉丝 1
博文 42
码字总数 14939
作品 0
南京
私信 提问
[MySQL优化案例]系列 — 分页优化

通常,我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询。例如下面这个SQL: SELECT * FROM t1 WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10; 或者像下面这个不带任何条件的分...

运维技术
2016/08/08
113
0
MySQL · 新特性分析 · 5.7中Derived table变形记

Derived table实际上是一种特殊的subquery,它位于SQL语句中FROM子句里面,可以看做是一个单独的表。MySQL5.7之前的处理都是对Derived table进行Materialize,生成一个临时表保存Derived tab...

阿里云RDS-数据库内核组
2017/03/06
0
0
Mysql 5.6 在性能、可伸缩性、可用性方面的新突破

Mysql 5.6 在性能、可伸缩性、可用性方面的新突破 更好的应用程序可用性:在线 DDL/模式 变化 如今基于web的应用程序被设计用来快速的进化以及自适应业务和生产需求。当一个应用程序必须迅速...

xiaocao13140
2018/06/08
0
0
Mysql 5.6 在性能、可伸缩性、可用性方面的新突破

Mysql 5.6 在性能、可伸缩性、可用性方面的新突破 更好的应用程序可用性:在线 DDL/模式 变化 如今基于web的应用程序被设计用来快速的进化以及自适应业务和生产需求。当一个应用程序必须迅速...

xiaocao13140
2018/06/08
0
0
MySQL 性能:使用 MySQL 5.7 实现每秒 50 万查询

本文提供 MySql5.7实现每秒50W查询 一文的细节以及基准测试结果,解释了我早期在Mysql Connect 发表的谈话。 回顾 MySQL / InnoDB 的改善历史。你能很容易发现。在MySQL 5.6稳定版本中从来没...

oschina
2013/10/09
74K
36

没有更多内容

加载失败,请刷新页面

加载更多

Spring Cloud Alibaba 实战(二) - 关于Spring Boot你不可不知道的实情

0 相关源码 1 什么是Spring Boot 一个快速开发的脚手架 作用 快速创建独立的、生产级的基于Spring的应用程序 特性 无需部署WAR文件 提供starter简化配置 尽可能自动配置Spring以及第三方库 ...

JavaEdge
今天
7
0
TensorFlow 机器学习秘籍中文第二版(初稿)

TensorFlow 入门 介绍 TensorFlow 如何工作 声明变量和张量 使用占位符和变量 使用矩阵 声明操作符 实现激活函数 使用数据源 其他资源 TensorFlow 的方式 介绍 计算图中的操作 对嵌套操作分层...

ApacheCN_飞龙
今天
7
0
五、Java设计模式之迪米特原则

定义:一个对象应该对其他对象保持最小的了解,又叫最小知道原则 尽量降低类与类之间的耦合 优点:降低类之间的耦合 强调只和朋友交流,不和陌生人说话 朋友:出现在成员变量、方法的输入、输...

东风破2019
昨天
23
0
jvm虚拟机结构

1:jvm可操作数据类型分为原始类型和引用类型,因此存在原始值和引用值被应用在赋值,参数,返回和运算操作中,jvm希望在运行时 明确变量的类型,即编译器编译成class文件需要对变量进行类型...

xpp_ba
昨天
5
0
聊聊nacos Service的processClientBeat

序 本文主要研究一下nacos Service的processClientBeat Service.processClientBeat nacos-1.1.3/naming/src/main/java/com/alibaba/nacos/naming/core/Service.java public class Service ex......

go4it
昨天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部