面试官：一千万的数据，你是怎么查询的？

您所在的位置：网站首页 › 十万个数字怎么写的图片 › 面试官：一千万的数据，你是怎么查询的？

面试官：一千万的数据，你是怎么查询的？

2024-07-09 17:16| 来源: 网络整理| 查看: 265

面试官：一千万的数据，你是怎么查询的？ 1 先给结论

对于1千万的数据查询，主要关注分页查询过程中的性能针对偏移量大导致查询速度慢：先对查询的字段创建唯一索引根据业务需求，先定位查询范围（对应主键id的范围，比如大于多少、小于多少、IN）查询时，将第2步确定的范围作为查询条件针对查询数据量大的导致查询速度慢：查询时，减少不需要的列，查询效率也可以得到明显提升一次尽可能按需查询较少的数据条数借助nosql缓存数据等来减轻mysql数据库的压力

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 造数据脚本

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

DELIMITER ;; CREATE DEFINER=`root`@`%` PROCEDURE `batch_insert_log`() BEGIN DECLARE i INT DEFAULT 1; DECLARE userId INT DEFAULT 10000000; set @execSql = 'INSERT INTO `big_data`.`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;

奇怪，走了索引啊，而且是主键索引，如下在这里插入图片描述带着十万个为什么和千万个不甘心，尝试给主键再加一层唯一索引

ALTER TABLE `big_data`.`user_operation_log` ADD UNIQUE INDEX `idx_id`(`id`) USING BTREE;

由于数据量有1000W，所以，加索引需要等待一会儿，毕竟创建1000W条数据的索引，一般机器没那么快。

然后再次执行上面的查询，结果如下：在这里插入图片描述天啊，这查询效率的差距不止十倍！！！

再次EXPLAIN分析一下：在这里插入图片描述命中的索引不一样，命中唯一索引的查询，效率高出不止十倍。

结论：

对于大表查询，不要太相信主键索引能够带来多少的性能提升，老老实实根据查询字段，添加相应索引吧！！！

但是上面的方法只适用于id是递增的情况，如果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);

查询所花费时间如下：在这里插入图片描述 EXPLAIN一下

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

在这里插入图片描述

3.2.2 采用 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;

在这里插入图片描述可以看出，查询效率是相当不错的

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

还是EXPLAIN分析一下

EXPLAIN SELECT * FROM `user_operation_log` WHERE id between 1000000 AND 1000100 LIMIT 100; EXPLAIN SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100;

在这里插入图片描述因此，针对分页查询，偏移量大导致查询慢的问题：

先对查询的字段创建唯一索引根据业务需求，先定位查询范围（对应主键id的范围，比如大于多少、小于多少、IN）查询时，将第2步确定的范围作为查询条件

【本文地址】

面试官：一千万的数据，你是怎么查询的？

面试官：一千万的数据，你是怎么查询的？

今日新闻

推荐新闻