一千万的sql数据，你是怎么查询的？

Sonder

2023-04-26

6591字

16分钟

浏览 (1.2k)

很多小伙伴一看到一些千万级数据之类的面试题就不知道怎么去回答。

也许有些人没遇过上千万数据量的表，也不清楚查询上千万数据量的时候会发生什么。

今天就来带大家实操一下，这次是基于MySQL 5.7.26做测试

准备数据

没有一千万的数据怎么办？

没有数据自己不会造吗？

造数据难吗？

代码创建一千万？

那是不可能的，太慢了，可能真的要跑一天。可以采用数据库脚本执行速度快很多。

创建表

复制代码

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;

创建数据脚本

采用批量插入，效率会快很多，而且每1000条数就commit，数据量太大，也会导致批量插入效率慢

复制代码

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 ;

开始测试

电脑配置比较低：win10 标压渣渣i5 读写约500MB的SSD

由于配置低，本次测试只准备了3148000条数据，占用了磁盘5G(还没建索引的情况下)，跑了38min，电脑配置好的同学，可以插入多点数据测试

复制代码

SELECT count(1) FROM `user_operation_log`

返回结果：3148000

三次查询时间分别为：

14060 ms
13755 ms
13447 ms

普通分页查询

MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。

MySQL分页查询语法如下：

复制代码

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

第一个参数指定第一个返回记录行的偏移量
第二个参数指定返回记录行的最大数目

下面我们开始测试查询结果：

复制代码

SELECT * FROM `user_operation_log` LIMIT 10000, 10

查询3次时间分别为：

59 ms
49 ms
50 ms

这样看起来速度还行，不过是本地数据库，速度自然快点。

换个角度来测试

相同偏移量，不同数据量

复制代码

SELECT * FROM `user_operation_log` LIMIT 10000, 10  
SELECT * FROM `user_operation_log` LIMIT 10000, 100  
SELECT * FROM `user_operation_log` LIMIT 10000, 1000  
SELECT * FROM `user_operation_log` LIMIT 10000, 10000  
SELECT * FROM `user_operation_log` LIMIT 10000, 100000  
SELECT * FROM `user_operation_log` LIMIT 10000, 1000000

查询时间如下：

数量	第一次	第二次	第三次
10条	53ms	52ms	47ms
100条	50ms	60ms	55ms
1000条	61ms	74ms	60ms
10000条	164ms	180ms	217ms
100000条	1609ms	1741ms	1764ms
1000000条	16219ms	16889ms	17081ms

从上面结果可以得出结束：数据量越大，花费时间越长

相同数据量，不同偏移量

复制代码

SELECT * FROM `user_operation_log` LIMIT 100, 100  
SELECT * FROM `user_operation_log` LIMIT 1000, 100  
SELECT * FROM `user_operation_log` LIMIT 10000, 100  
SELECT * FROM `user_operation_log` LIMIT 100000, 100  
SELECT * FROM `user_operation_log` LIMIT 1000000, 100

偏移量	第一次	第二次	第三次
100	36ms	40ms	36ms
1000	31ms	38ms	32ms
10000	53ms	48ms	51ms
100000	622ms	576ms	627ms
1000000	4891ms	5076ms	4856ms

从上面结果可以得出结束：偏移量越大，花费时间越长

复制代码

SELECT * FROM `user_operation_log` LIMIT 100, 100  
SELECT id, attr FROM `user_operation_log` LIMIT 100, 100

如何优化

既然我们经过上面一番的折腾，也得出了结论，针对上面两个问题：偏移大、数据量大，我们分别着手优化

优化偏移量大问题

采用子查询方式

我们可以先定位偏移位置的 id，然后再查询数据

复制代码

SELECT * FROM `user_operation_log` LIMIT 1000000, 10  

SELECT id FROM `user_operation_log` LIMIT 1000000, 1  

SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10

查询结果如下：

sql	花费时间
第一条	4818ms
第二条(无索引情况下)	4329ms
第二条(有索引情况下)	199ms
第三条(无索引情况下)	4319ms
第三条(有索引情况下)	201ms

从上面结果得出结论：

第一条花费的时间最大，第三条比第一条稍微好点
子查询使用索引速度更快

缺点：只适用于id递增的情况

id非递增的情况可以使用以下写法，但这种缺点是分页查询只能放在子查询里面

注意：某些 mysql 版本不支持在 in 子句中使用 limit，所以采用了多个嵌套select

复制代码

SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t)

采用 id 限定方式

这种方法要求更高些，id必须是连续递增，而且还得计算id的范围，然后使用 between，sql如下

复制代码

SELECT * FROM `user_operation_log` WHERE id between 1000000 AND 1000100 LIMIT 100  

SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100

查询结果如下：

sql	花费时间
第一条	22ms
第二条	21ms

从结果可以看出这种方式非常快

注意：这里的 LIMIT 是限制了条数，没有采用偏移量

优化数据量大问题

返回结果的数据量也会直接影响速度

复制代码

SELECT * FROM `user_operation_log` LIMIT 1, 1000000  

SELECT id FROM `user_operation_log` LIMIT 1, 1000000  

SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM `user_operation_log` LIMIT 1, 1000000

查询结果如下：

sql	花费时间
第一条	15676ms
第二条	7298ms
第三条	15960ms

从结果可以看出减少不需要的列，查询效率也可以得到明显提升

第一条和第三条查询速度差不多，这时候你肯定会吐槽，那我还写那么多字段干啥呢，直接 * 不就完事了

注意本人的 MySQL 服务器和客户端是在_同一台机器_上，所以查询数据相差不多，有条件的同学可以测测客户端与MySQL分开

SELECT * 它不香吗？

在这里顺便补充一下为什么要禁止 SELECT *。难道简单无脑，它不香吗？

主要两点：

用 SELECT * 数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。
增大网络开销，* 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。特别是MySQL和应用程序不在同一台机器，这种开销非常明显。

本文转自 https://mp.weixin.qq.com/s/xK1gqHNdAMQRAeYjCL2tdw，如有侵权，请联系删除。

上一篇 / 初学者的Python基本语法笔记下一篇 / 解决Python的pip命令在 Power shell中无法识别问题