mysql5.6 分页查询优化

Wesley13
• 阅读 691

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对结果集进行排序。

点赞
收藏
评论区
推荐文章
Easter79 Easter79
2年前
sql优化之大数据量分页查询(mysql)
当需要从数据库查询的表有上万条记录的时候,一次性查询所有结果会变得很慢,特别是随着数据量的增加特别明显,这时就需要使用分页查询。对于数据库分页查询,也有很多种方法和优化的点。谈优化前的准备工作为了对下面列举的一些优化进行测试,需要使用已有的一张表作为实际例子。表名:order\_history。描述:某个业务的订单历史表。主要字段
Wesley13 Wesley13
2年前
MySQL使用on duplicate key update时导致主键不连续自增
使用onduplicatekeyupdate语法有时是很方便,但是会有一个影响:默认情况下,每次更新都会更新该表的自增主键ID,如果更新频率很快,会导致主键ID自增的很快,过段时间就超过数字类型的的范围了解决这个问题,有两种方式:(实际我目前使用的方式是把自增主键ID设置为bigint,也有一部分操作先查询再选择插入OR更新)方法一:拆分成两个
Wesley13 Wesley13
2年前
MySQL千万级别优化·中
MySQL千万级别的查询优化手段·中单列索引(假设在v\_record表中存在id列的索引)1、WHERE条件使用​EXPLAINSELECT\FROMv\_recordWHEREid2​结论:利用索引进行回表查询2、SELECT字段使用
Wesley13 Wesley13
2年前
MySQL基础学习笔记——数据库优化(2):SQL查询优化
数据库优化SQL查询优化1.避免全表扫描,应该考虑在where及orderby涉及的列上建立索引;2.查询时使用select明确指明所要查询的字段,避免使用select(keys,flushdb等)的操作;3.SQL语句尽量大写,
Wesley13 Wesley13
2年前
MySql 面试开发技术点汇总
表结构设计1、为什么一定要设一个主键?答:因为你不设主键的情况下,innodb也会帮你生成一个隐藏列,作为自增主键。所以啦,反正都要生成一个主键,那你还不如自己指定一个主键,在有些情况下,就能显式的用上主键索引,提高查询效率!2、你们主键是用自增还是UUID?答:肯定答自增啊。innod
Wesley13 Wesley13
2年前
MySQL · 性能优化 · MySQL常见SQL错误用法
1\.LIMIT语句分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,name,create\_time字段上加组合索引。这样条件排序都能有效的利用到索引,性能迅速提升。SELECTFROMoperationWHEREty
Wesley13 Wesley13
2年前
Mysql 5.6 “隐式转换”导致的索引失效和数据不准确
背景在一次进行SQl查询时,我试着对where条件中vachar类型的字段去掉单引号查询,这个时候发现这条本应该很快的语句竟然很慢。这个varchar字段有一个复合索引。其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据。使用的是mysql5.6版本,innoDB引擎实际情况如下
Wesley13 Wesley13
2年前
Hibernate常见知识汇总
1.在数据库中条件查询速度很慢的时候,如何优化?1.建索引2.减少表之间的关联3.优化sql,尽量让sql很快定位数据,不要让sql做全表查询,应该走索引,把数据量大的表排在前面4.简化查询字段,没用的字段不要,已经对返回结果的控制,尽量返回少量数据2.在Hibernate中进行多表查询,每个表中各取几个字段,也就是说查询出来的结果
Easter79 Easter79
2年前
TiDB 源码阅读系列文章(十三)索引范围计算简介
简述在数据库中处理查询请求时,如果可以尽早的将无关数据过滤掉,那么后续的算子就可以少做无用功,提升整个SQL的执行效率。过滤数据最常用的手段是使用索引,TiDB的优化器也会尽量采用索引过滤的方式处理请求,利用索引有序的特点来提升查询效率。比如当查询条件为 a1 时,如果a这一列上有索引,我们就可以利用索引很快的把满足 a
京东云开发者 京东云开发者
8个月前
高性能MySQL实战(二):索引 | 京东物流技术团队
我们在上篇:表结构中已经建立好了表结构,这篇我们则是针对已有的表结构和搜索条件为表创建索引。1\.根据搜索条件创建索引我们还是先将表结构的初始化SQL拿过来:CREATETABLEservicelog(idbigintUNSIGNEDNOTNULLAUTO