mysql5.6查询分页问题_mysql5.6 分页查询优化

灰太狼 2022-10-24 11:53 309阅读 0赞

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 查询主表中所有在此范围的数据。

有如下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 参考：

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

mysql5.6查询分页问题_mysql5.6 分页查询优化

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关优化mysql分页查询

相关 mysql分页limit查询优化

相关 MySQL分页查询优化

相关 mysql 大分页查询优化_Mysql优化大分页查询

相关 mysql分页查询优化

相关 mysql查询结果分页_分页MySQL查询结果

相关 Mysql 分页查询快照_ElasticSearch—分页查询

相关 mysql5.6查询分页问题_mysql5.6 分页查询优化

相关 MySQL分页查询

相关 MySQL分页查询优化