【面试篇】:千万数据下如何优化limit

已于 2023-10-17 21:34:08 修改
阅读量1k 收藏 10
点赞数 3
分类专栏： MySQL 文章标签： mysql 面试
于 2023-06-30 17:35:20 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_60505735/article/details/131478719
版权

前言：在面试的时候，很容易被问到，在面对千万数据如何进行处理，而我们在日常开发中也不容易接触到千万级别的数据，所以，本文将在千万的数据量下分析如何优化limit。

1、limit 知识复习

1.1、语法

在MySQL中Limit有两种语法：

 limit offset, rows
 limit rows

其中offset表示偏移量，rows表示要返回的记录条数。

1.2、limit 存在的问题

当我们用 limit 1000000, 10 的时候，MySQL会先扫描满足条件的1000010行，扔掉前面的1000000行，返回后面的10行。所以offset越大的时候，扫描的行就越多，效率也就越慢了。

2、环境准备

2.1、创建表

创建一张用户登录表，代码如下：

CREATE TABLE `user_operation_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ip` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `op_data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `attr12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2.2 准备数据

创建一个存储过程，代码如下：

DELIMITER ;;
CREATE PROCEDURE batch_insert_log()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE userId INT DEFAULT 10000000;
 set @execSql = 'INSERT INTO `test`.`user_operation_log`(`user_id`, `ip`, `op_data`, `attr1`, `attr2`, `attr3`, `attr4`, `attr5`, `attr6`, `attr7`, `attr8`, `attr9`, `attr10`, `attr11`, `attr12`) VALUES';
 set @execData = '';
  WHILE i<=10000000 DO
   set @attr = "'测试很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长的属性'";
  set @execData = concat(@execData, "(", userId + i, ", '10.0.69.175', '用户登录操作'", ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ")");
  if i % 1000 = 0
  then
     set @stmtSql = concat(@execSql, @execData,";");
    prepare stmt from @stmtSql;
    execute stmt;
    DEALLOCATE prepare stmt;
    commit;
    set @execData = "";
   else
     set @execData = concat(@execData, ",");
   end if;
  SET i=i+1;
  END WHILE;

END;;
DELIMITER ;

执行存储过程，我的电脑大概跑了50分钟，创建了10785000条数据

3、百万数据量下

3.1 未建立联合索引

当没有为子查询或内连接表涉及的字段id和op_data创建联合索引时，执行下面SQL：

#1 、百万数据下
#1.1 直接查询
SELECT * FROM `user_operation_log` WHERE op_data = '用户登录操作' LIMIT 1000000, 10  # 1067 ms 
#1.2 子查询优化 ，id有序情况使用 、op_data未建立联合索引
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM   user_operation_log  WHERE op_data = '用户登录操作'  LIMIT 1000000 , 1 ) LIMIT 10  # 1040 ms
#1.3 内连接优化 ，id有序无序都可使用、op_data未建立联合索引
SELECT t.* FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作' LIMIT 1000000 , 10) temp ON t.id = temp.id  #1036ms
SQLTime
第一条1067 ms
第二条1040 ms
第三条1036ms

可以看到，速度并没有变快，因为此时仍然进行了全表扫描

3.2 建立联合索引

创建联合索引，涉及的字段为id，op_data。

CREATE INDEX id_op_data_index ON test.`user_operation_log`(id, op_data) ;

执行同样的SQL：

#2 、百万数据下
#2.1 直接查询
SELECT * FROM `user_operation_log` WHERE op_data = '用户登录操作' LIMIT 1000000, 10  # 1067 ms 
#2.2 子查询优化 ，id有序情况使用 、子查询涉及字段建立联合索引
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM   user_operation_log  WHERE op_data = '用户登录操作'  LIMIT 1000000 , 1 ) LIMIT 10  # 70ms
#2.3 内连接优化 ，id有序无序都可使用、连接表创建联合索引
SELECT t.* FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作' LIMIT 1000000 , 10) temp ON t.id = temp.id  #42ms
SQLTime
第一条1067 ms
第二条70ms
第三条42ms

可以看到速度有了明显提升，这是因为子查询和内连接都使用到了id_op_data_index这个联合索引，找到符合要求的数据，在查询需要的字段。

3.3 建立联合索引且只查询需要字段

只查询只要的字段，执行相同SQL：

#3 、百万数据下
#3.1 直接查询
SELECT id,op_data FROM `user_operation_log` WHERE op_data = '用户登录操作' LIMIT 1000000, 10  # 970 ms 
#3.2 子查询优化 ，id有序情况使用 、子查询涉及字段建立联合索引、只查询需要的字段（这里以建立了联合索引的为例）
SELECT id,op_data FROM `user_operation_log` WHERE id >= (SELECT id FROM   user_operation_log  WHERE op_data = '用户登录操作'  LIMIT 1000000 , 1 ) LIMIT 10  # 57ms
#3.3 内连接优化 ，id有序无序都可使用、连接表创建联合索引、只查询需要的字段（这里以建立了联合索引的为例）
SELECT t.id,t.op_data FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作' LIMIT 1000000 , 10) temp ON t.id = temp.id  #22ms
SQLTime
第一条970 ms
第二条57ms
第三条22ms

可以看到，速度会进一步提升，以inner join 为例子，MySQL在执行的时候，会选择左右两个表中的小表作为驱动表，大表作为被驱动表，在驱动表中取出数据放到join_buffer中可以根据索引去匹配被驱动表，满足条件的放到结果集中。在这原表user_operation_log 是被驱动表，而需要查询的字段，和连接条件都在id_op_data_index这个联合索引中，会使用索引覆盖，而不会去回表，从而加快速度。

4、千万数据量下

4.1 未建立联合索引

#4 、千万级数据下
#4.1 直接查询
SELECT * FROM `user_operation_log` WHERE op_data = '用户登录操作'   LIMIT 10000000, 10  # 29075 ms
#4.2 子查询查询、id有序情况使用 、op_data未建立联合索引
 SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 1 ) LIMIT 10  # 19004 ms
#4.3 内连接优化、id有序无序都可使用、op_data未建立联合索引
SELECT t.* FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 10) temp ON t.id = temp.id ; # 20041 ms
SQLTime
第一条29075 ms
第二条19004 ms
第三条20041 ms

可以看到，数据量过千万之后，时间飙升，所以千万不要说用limit直接查询。

4.2 建立联合索引

#5 、千万级数据下
#5.1 直接查询
SELECT * FROM `user_operation_log` WHERE op_data = '用户登录操作'   LIMIT 10000000, 10  # 28025 ms
#5.2 子查询查询、id有序情况使用、子查询涉及字段建立联合索引
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 1 ) LIMIT 10  # 1898 ms
#5.3 内连接优化、id有序无序都可使用、连接表创建联合索引
SELECT t.* FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 10) temp ON t.id = temp.id ; # 1741 ms
SQLTime
第一条28025 ms
第二条1898 ms
第三条1741 ms

原理与上面一样，都是利用索引。

4.3 建立联合索引且只查询需要字段

#6 、千万级数据下
#6.1 直接查询
SELECT id,op_data FROM `user_operation_log` WHERE op_data = '用户登录操作'   LIMIT 10000000, 10  # 2056 ms
#6.2 子查询查询、id有序情况使用、子查询涉及字段建立联合索引、只查询需要的字段（这里以建立了联合索引的为例）
SELECT id,op_data FROM `user_operation_log` WHERE id >= (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 1 ) LIMIT 10  # 1860 ms
#6.3 内连接优化、id有序无序都可使用、连接表创建联合索引、只查询需要的字段（这里以建立了联合索引的为例）
SELECT t.id,t.op_data FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 10) temp ON t.id = temp.id ; # 1707 ms
SQLTime
第一条2056 ms
第二条1860 ms
第三条1707 ms

可以看到，还是需要一秒多才能查询出来，但是在真正的生产环境下，服务器配置比我电脑上的一个虚拟机高得多，况且还会搭建集群，如果这时候还慢的话，那还可以做分库分表处理，数据量在百万的情况下，我的电脑执行都能达到几十毫秒，还是非常快的。

5、总结

  • 如果主键id有序，可以用子查询优化
  • 如果主键id是无序的，可以用inner join来优化
  • 子查询或者内连接中涉及的字段都要创建为联合索引
  • 使用索引覆盖减少回表
Chaimek
关注
  • 3
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
面试题-中国平安篇.zip
06-06
当只要⼀条数据时使⽤limit 1； c. 为搜索字段建索引； d. 避免select *； e. 字段尽量使⽤not null； f. 垂直分割； g. 拆分⼤的delete和insert语句：delete和insert会锁表； h. 分表分库分区。 4. 有没了解Docker...
MySQL面试题（含答案）
02-12
本篇将基于提供的面试题集锦，深入探讨MySQL的相关知识点。 1. **SQL基础** - **查询语句**：包括SELECT语句的使用，如JOIN、WHERE子句、GROUP BY与HAVING子句，以及ORDER BY和LIMIT子句，用于数据筛选、排序和...
Mysql数据库中limit 100万，10 查询很慢怎么优化 - 面试宝典
牛肉胡辣汤
08-30 845
需要注意的是，以上优化方法需要根据具体情况选择合适的方法，并且在实际应用中需要进行测试和评估，以确定最佳的优化策略。同时，还可以考虑调整数据库的配置参数，如增加缓冲区大小、调整查询缓存等，以提高查询的性能。​​子句进行分页查询时，如果查询的数据量非常大，可能会导致查询变得非常慢。MySQL数据库中，当使用​。
MYSQL分页limit速度太慢优化方法
iteye_16982的博客
10-10 1130
当一个表数据有几百万的数据时，分页的时候成了问题 如 select * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢，可以按照一下方法解决 最近一个网站的服务器评论被人刷死，导致mysql数据库异常发生too many open connections   引发的SQL语句  代码如下复制代码   SELECT ...
MySQL 用 limit 为什么会影响性能？有什么优化方案？
最新发布
创造自我、追求无我
04-09 690
Limit 是一种常用的分页查询语句，它可以指定返回记录行的偏移量和最大数目。这样的语句看起来很简单，但是在实际使用中，可能会出现性能问题。为什么呢？我们需要从 Mysql 的索引结构和查询过程来分析。
Mysql优化系列之limit查询
S_ZaiJiangHu的博客
07-16 3128
server层会调用innodb的接口，在innodb里的非主键索引中获取到第0条数据对应的主键id后，回表到主键索引中找到对应的完整行数据，然后返回给server层，server层将其放到结果集中，返回给客户端。但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。如果数据量很少，比如1k的量级，且长期不太可能有巨大的增长，还是用limitoffset,size的方案吧，整挺好，能用就行。...
你知道MySQL的Limit有性能问题吗
weixin_34402408的博客
05-08 238
MySQL的分页查询通常通过limit来实现。 MySQL的limit基本用法很简单。limit接收1或2个整数型参数，如果是2个参数，第一个是指定第一个返回记录行的偏移量，第二个是返回记录行的最大数目。初始记录行的偏移量是0。 为了与PostgreSQL兼容，limit也支持limit # offset #。 问题： 对于小的偏移量，直接使用limit来查询没有什么问题，但随着数据量的增大，越往...
[MySQL性能优化系列]LIMIT语句优化
qin_weilong的博客
08-23 795
1. 背景 假设有如下SQL语句： SELECT * FROM table1 LIMIT offset, rows 这是一条典型的LIMIT语句，常见的使用场景是，某些查询返回的内容特别多，而客户端处理能力有限，希望每次只取一部分结果进行处理。 上述SQL语句的实现机制是: 从“table”表中读取offset+rows行记录抛弃前面的offset行记录，返回后面的row
MySQL分页查询limit优化的两种思路
04-11 5795
我们知道，limit分页，当翻到比较靠后的页时，查询效率会越来越慢。如这则帖子所述： 浅谈PageHelper插件分页实现原理及大数据量下SQL查询效率问题解决 博主这也是一种不错的思路。其实针对分页这个问题，了解其为什么慢就知道优化方法了，按我的理解如下（不一定说的很准确哈）： （1）以前select * from table order by id limit 100, 10这种方式，分页是怎么分的呢，是从磁盘拿整一个数据页到存储引擎缓冲区，比如 limit 100, 10 那就要按顺序先取出前100条
解决MySQL使用limit偏移量较大效率慢的问题
hellokitty_nba的博客
03-29 4950
解决MySQL使用limit偏移量较大效率慢的问题
90道SpringCloud面试题及答案.docx
10-27
本篇文章将详细解析90道SpringCloud面试题中涉及的关键知识点。 1. **微服务**：微服务是一种架构风格，它提倡将单体应用分解为一组小型、独立的服务，每个服务都有自己的业务边界，可独立开发、部署和扩展。微服务...
金融业Java中级工程师岗位面试真题
02-25
这篇内容主要涵盖了Java中级工程师在金融行业的面试知识点，包括Redis的应用、持久化机制、MySQL的优化策略、Docker与虚拟机的区别，以及RabbitMQ的消息队列结构。以下是对这些知识点的详细解释： 1. Redis应用场景...
SQL面试经典版-整理篇
12-27
- 引擎优化：如选择合适的存储引擎、索引优化、查询优化等。 4. SQL Server数据库： - Transact-SQL（T-SQL）：SQL Server的扩展SQL语法，包括变量、流程控制、异常处理等。 - Service Broker：SQL Server的消息...
mysql分页优化解决limit偏移量变大的问题
ask_baidu的博客
12-31 434
mysql分页优化，解决limit偏移量变大的问题
MySql千万级limit优化方案
sunct的博客
05-22 1万+
经过实践，总结以下比较好的limit分页优化方案 1. 模仿百度、谷歌方案（前端业务控制） 类似于分段。我们给每次只能翻100页、超过一百页的需要重新加载后面的100页。这样就解决了每次加载数量数据大 速度慢的问题了 2. IN获取id select * from table_name where id in (select id from table_name where ( user = xx...
mysql limit 使用规范_小心避坑：MySQL分页时使用 limit+order by 会出现数据重复问题...
weixin_33603397的博客
01-27 485
本文转载自【微信公众号：java进阶架构师，ID:java_jiagoushi】经微信公众号授权转载，如需转载与原文作者联系1、问题描述在MySQL中我们通常会采用limit来进行翻页查询，比如limit(0,10)表示列出第一页的10条数据，limit(10,10)表示列出第二页。但是，当limit遇到order by的时候，可能会出现翻到第二页的时候，竟然又出现了第一页的记录。具体如下：SEL...
mysql limit千万级优化思路细说
热门推荐
qq_41642932的博客
08-24 1万+
首先我们先创建个数据表做测试 表名 test (id(int) , name(var char) , content(text) , pid(int) ) 往里面倒几百万条数据进去做测试。 我们都知道分页是 这样写的 select * from test limit 10,20; 比如每页显示perpage条，当前是第N页； 那么就是这么来写 select * from te...
mysql limit分页（偏移量）过大时优化问题
全场梦游c的博客
05-25 3603
先说一下limit分页语法 select * from user_address limit 100000,10 limit后跟两个参数，第一个参数为从第几个数据开始，第二个参数为取多少个数据。 第一个参数也叫偏移量，初始值是0 如果数据量很小，这么写分页当然没问题，但是当数据量大起来的时候，查询速度就会慢很多。 如：select * from user_address limit 100,10 查询用时0.011S select * from user_address limit 100000
MySQL limit使用及超大分页问题解决
qq_22075913的博客
07-27 2065
MySQL limit使用及超大分页问题解决
MySQL 80道面试题及答案.docx
07-08
Oracle在处理高并发和大数据量上表现出色，而MySQL则需要额外的优化策略，如集群来应对高负载。权限管理上，Oracle支持更精细的多用户权限控制，而MySQL相对简单，一般登录即可操作所有数据库。在安装大小和运行时...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值