网站建设要学哪些软件有哪些方面,宝安电子厂做高端网站,网站设计的一般流程,学软件工程好找工作吗前言
之前有看过到mysql大数据量分页情况下性能会很差#xff0c;但是没有探究过它的原因#xff0c;今天讲一讲mysql大数据量下偏移量很大#xff0c;性能很差的问题#xff0c;并附上解决方式。
原因
将原因前我们先做一个试验#xff0c;我做试验使用的是mysql5.7.2…前言
之前有看过到mysql大数据量分页情况下性能会很差但是没有探究过它的原因今天讲一讲mysql大数据量下偏移量很大性能很差的问题并附上解决方式。
原因
将原因前我们先做一个试验我做试验使用的是mysql5.7.24版本mysql8上我也试验出来同样的问题看看mysql是不是在偏移量比较大的时候分页会比较慢性能比较差
版本
mysql select version();
-----------
| version() |
-----------
| 5.7.24 |
-----------
1 row in set (0.00 sec)表结构
CREATE TABLE trace_monitor_log (id varchar(30) NOT NULL COMMENT 表主键id,user_id varchar(30) DEFAULT NULL COMMENT 用户id,trace_id varchar(30) DEFAULT NULL COMMENT 追踪id,trace_type varchar(30) DEFAULT NULL COMMENT 追踪类型,path mediumtext COMMENT 追踪路径,source_ip varchar(255) DEFAULT NULL COMMENT 来源ip,ext_params mediumtext COMMENT 请求扩展参数,costs int(11) DEFAULT 0 COMMENT 请求耗时(毫秒),exception mediumtext COMMENT 异常信息,create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,PRIMARY KEY (id),KEY trace_id (trace_id),KEY trace_type (trace_type),KEY create_time (create_time)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT监控日志表;试验过程
这个是我从测试环境找的一张日志表里面的数据量是580万左右我们先看看只查询普通10条数据的情况。
数据量
mysql select count(*) from trace_monitor_log;
----------
| count(*) |
----------
| 5806836 |
----------
1 row in set (1.66 sec)explain select * from trace_monitor_log order by trace_id limit 10;可以看到没有offset偏移量的时候可以直接走索引key是trace_id并且只查询了10条数据。
我们在来看看如果offset是1000的时候。
explain select * from trace_monitor_log order by trace_id limit 10 offset 1000;可以看到偏移量比较小的时候还是可以走索引rows是1010这时候发现虽然我们只要查询10条数据但是查询的时候还是会扫描1000条无用的索引记录。
我们接下往下把offset加到100万
explain select * from trace_monitor_log order by trace_id limit 10 offset 1000000;这个时候就会发现一个神奇的现象竟然没有走索引了type是ALL就是全表扫描了执行时间大概花了40多秒性能确实很差。这里的原因本来根据索引查出来100万条记录然后把不需要的数据给丢弃掉mysql会计算查询成本发现这样走索引还没有全表扫描快所以用了全表扫描但是全表扫描就为了拿到十条数据显然是性能很差的。mysql并不会自动判断先根据trace_id的索引找到偏移量需要的10条数据再根据这10条索引找到叶子节点的主键记录去回表查询数据导致了这么差的性能。
解决方式
1.延迟关联
先使用覆盖索引的方式找到对应order by 之后的limit条索引因为是覆盖索引直接用的索引记录没有回表所以很快。接着在使用join的方式将索引记录和原表关联起来就可以查出来对应的limit条数据。
explain select * from trace_monitor_log t1 join (select trace_id from trace_monitor_log order by trace_id limit 1000000,10) t2 on t1.trace_id t2.trace_id执行时间平均在500-600毫秒左右相比全表扫描快了很多。
2.书签记录
这个概念我也是从网上看到的还没找到具体这个概念的出处在哪里。不过不要困于这个概念只要理解是先找到对应要查询一条索引记录书签再根据这个索引去范围查询对应的limit条数数据就容易理解了。
explain select * from trace_monitor_log t1 where trace_id (select trace_id from trace_monitor_log order by trace_id limit 999999,1) order by trace_id limit 10执行时间和延迟关联差不多也都走了索引所以性能也比较好。
参考资料
1.mysql8官网limit优化
2.要想通过面试MySQL的Limit子句底层原理你不可不知
3.从官方文档中探索MySQL分页的几种方式及分页优化
