MySQL百万数据分页查询优化

GangLuo7

已于 2023-08-19 18:23:03 修改

阅读量134

点赞数 1

文章标签： mysql 百万查询优化分页优化分页查询优化

于 2023-08-18 11:10:39 首次发布

原文链接：https://juejin.cn/post/7230979300828151865#heading-6

版权

创建表

创建表并初始化百万条数据至表中

CREATE TABLE `orm_user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '加密后的密码',
  `salt` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '加密使用的盐',
  `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮箱',
  `phone_number` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手机号码',
  `status` int NOT NULL DEFAULT '1' COMMENT '状态，-1：逻辑删除，0：禁用，1：启用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_login_time` datetime DEFAULT NULL COMMENT '上次登录时间',
  `last_update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上次更新时间',
  PRIMARY KEY (`id`),
  KEY `create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='示例表';

使用limit查询尝试

SQL_NO_CACHE
这个关键词是为了不让SQL查询走缓存。

SELECT SQL_NO_CACHE * from orm_user where create_time BETWEEN '2023-08-01 00:00:00' and  '2023-08-30 23:59:59' LIMIT 1, 10

查询前10条基本不消耗时间
查询前10条数据基本不消耗时间

SELECT SQL_NO_CACHE * from orm_user where create_time BETWEEN '2023-08-01 00:00:00' and  '2023-08-30 23:59:59' LIMIT 1600000, 10

将偏移量至百万，查询耗时1s左右
在这里插入图片描述

优化方案

SELECT SQL_NO_CACHE * from
(SELECT SQL_NO_CACHE id from orm_user orm_user where create_time BETWEEN '2023-08-01 00:00:00' and  '2023-08-30 23:59:59' LIMIT 1600000, 10) as temp INNER JOIN orm_user ou on ou.id = temp.id

耗时0.7s左右
在这里插入图片描述

原因分析

我们一般对于查询频率比较高的字段会建立索引。索引会提高我们的查询效率。我们上面的语句使用了SELECT * FROM orm_user，但是我们并不是所有的字段都建立了索引。当从索引文件中查询到符合条件的数据后，还需要从数据文件中查询到没有建立索引的字段。那么这个过程称之为回表。

覆盖索引

如果查询的字段正好创建了索引了，比如 SELECT create_time FROM orm_user，我们查询的字段是我们创建的索引，那么这个时候就不需要再去数据文件里面查询，也就不需要回表。这种情况我们称之为覆盖索引

IO

回表操作通常是IO操作，因为需要根据索引查找到数据行后，再根据数据行的主键或唯一索引去聚簇索引中查找具体的数据行。聚簇索引一般是存储在磁盘上的数据文件，因此在执行回表操作时需要从磁盘读取数据，而磁盘IO是相对较慢的操作。
LIMTI 1600000,10 ？
你有木有想过LIMIT 1600000,10会不会扫描1-1600000行，你之前有没有跟我一样，觉得数据是直接从1600000行开始取的，前面的根本没扫描或者不回表。其实这样的写法，一个完整的流程是查询数据，如果不能覆盖索引，那么也是要回表查询数据的。
现在你知道为什么越到后面查询越慢了吧！