大厂面试官：千万级数据量的表，如何进行快速查询？ |
您所在的位置：网站首页 › mysql单表千万 › 大厂面试官：千万级数据量的表，如何进行快速查询？ |
|
推荐阅读：
我总结了72份面试题，累计3170页，斩获了30+互联网公司offer（含BATJM） 2020首战告捷,这份Java面试神技Plus版，让我成功拿到了阿里、京东、字节跳动等大厂offer 疫情之下，收到美团电话面试(成功拿下offer)，附学习路线+刷题库 前言 面试官： 来说说，一千万的数据，你是怎么查询的？B哥：直接分页查询，使用limit分页。面试官：有实操过吗？B哥：肯定有呀此刻献上一首《凉凉》 也许有些人没遇过上千万数据量的表，也不清楚查询上千万数据量的时候会发生什么。 今天就来带大家实操一下，这次是基于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= ( 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 100SELECT * 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和应用程序不在同一台机器，这种开销非常明显。 结束最后还是希望大家自己去实操一下，肯定还可以收获更多，欢迎留言！！ 创建脚本我给你正好了，你还在等什么！！！ |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |