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

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