当使用mysql进行分页查询时,如果表中的数据量比较大超过百万级别,当进行深分页查询的时候,sql的执行时间会变的比较慢,解决方法也很简单,通过join加子查询的方式可以轻松解决这个问题,但是这个的前提是待查询表中的数据不能超过千万(其实500W左右sql的执行时间就已经比较长了)下面我们通过一个实验来验证这个说法。
实验准备
服务器配置为8C16G,使用的mysql版本为5.7(默认配置);待查询表中数据量为600W,这里教大家一个小技巧可以用下面的sql快速的制造一个百万甚至千万级别数据量的表。
insert into table_name (column1, column2) select column1, column2 from table_name;
进行实验
首先进行最普通的分页查询,查询结果按照主键进行升序排序;sql如下,执行时间大概为:3900毫秒,如果是我们自己用的XX管理系统,这个时间还说的过去,但是如果是客户使用的系统或APP,这个时间就已经有点长了。
select * from t_user_info order by id asc limit 10 offset 5000000;
接下来我们把这个sql升级一下,还是一个分页查询,但是查询结果按照create_time进行排序,且create_time字段没有索引,执行耗时为:117.53秒
select * from t_user_info order by create_time asc limit 10 offset 5000000;
如果你看过一些书或者视频博客,大都会说索引要建立在区分度比较高的字段上,我们反其道而行之,对create_time这个区分度不高的字段创建一个普通索引,再次执行上面的sql耗时为:116.53秒,结果证明:索引确实要加在区分度比较高的字段上,我们乖乖的把这个索引删掉。
接下来我们对上面这两个sql进行一个优化,使用join加子查询的方式对sql进行优化,sql如下,其中按照主键进行排序的sql执行时间大概为:2200毫秒,按照create_time进行排序的sql执行时间大概为:4700毫秒;第二个sql的优化时间还是比较明显的,但是如果仅仅是这样只能满足XX管理系统的要求,对于一个面向客户的系统或APP来说,还是不行。
在来说一下为什么这么写sql会快,其实很简单,优化之前的sql比优化之后的sql多进行了5000000次回表操作
select u.* from t_user_info u
join (select id from t_user_info order by id asc limit 10 offset 5000000) j on u.id = j.id;
select u.* from t_user_info u
join (select id from t_user_info order by create_time asc limit 10 offset 5000000) j on u.id = j.id;
我查阅了很多博客和文章,大多数写到到这里基本就结束了,个人认为有点不负责任,因为如果表中的数据超过100W,sql的执行时间还是很长,下面我将自己工作中的经验和方法和大家进行分享。
终极解决办法
我们数据库中表的主键都是自增id(和业务没有关系,也不影响分库分表),在进行分页查询的时候,sql语句中一定要返回表对应的主键,然后将主键和当前的业务场景结合,拼接成一个唯一的字符串存储到redis中(如:userId:orderInfo:queryCondition:pageUrl),当下次在来查询时,直接从redis中进行匹配,看看能否匹配到,如果可以,直接用id作为where条件进行查询,这样一个分页查询就标称了一个主键查询,可以在毫秒级返回数据。