mysql5.6 分页查询优化

最新推荐文章于 2024-09-14 19:55:29 发布

weixin_34417200

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量108

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/1473861/blog/3043226

版权

2019独角兽企业重金招聘Python工程师标准>>>

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

转载于:https://my.oschina.net/u/1473861/blog/3043226