mysql5.6 分页查询优化

mysql5.6 分页查询优化

场景：

表结构：主键（非自增）contentCode（varchar），过滤条件列为updateTime（timeStamp），已经为timestamp建立索引。

搜索sql为：

1SELECT
2	*
3FROM
4	my_hello_table
5WHERE
6	updateTime >= '2019-04-21 14:37:38'
7AND updateTime <= '2019-04-27 16:36:57'
8LIMIT 599000,
9 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：

1SELECT
2	*
3FROM
4	my_hello_table
5WHERE
6	contentCode IN (
7		SELECT
8			contentCode
9		FROM
10			my_hello_table
11		WHERE
12			updateTime > '2019-04-21 14:37:38'
13		AND updateTime <= '2019-04-27 16:36:57'
14		LIMIT 599000,
15		1000
16	)
17);

但是，可惜的是，会有如下问题：

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：

1SELECT
2    *
3FROM
4    my_hello_table
5WHERE
6    contentCode IN (
7        SELECT
8            t.contentCode
9        FROM
10            (
11                SELECT
12                    contentCode
13                FROM
14                    my_hello_table
15                WHERE
16                    updateTime > '2019-04-21 14:37:38'
17                AND updateTime <= '2019-04-27 16:36:57'
18                LIMIT 599000,
19                1000
20            ) AS t
21    );

 伪表 表连接

1SELECT
2    a.*
3FROM
4    my_hello_table a
5INNER JOIN (
6    SELECT
7        contentCode
8    FROM
9        my_hello_table
10    WHERE
11        updateTime > '2019-04-21 14:37:38'
12    AND updateTime <= '2019-04-27 16:36:57'
13    LIMIT 599000,
14    1000
15) 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对结果集进行排序。