면접관: 천만 개의 데이터를 어떻게 쿼리했나요?-Java인터뷰 질문들-php.cn

최근 모의면접과 이력서 최적화를 모두를 대상으로 진행했는데, 많은 분들이 수천만 건의 데이터와 같은질문을 보면 무릎이 약해지는 것을 발견했습니다.

수천만 개의 데이터가 포함된 테이블을 한 번도 접해본 적이 없고, 수천만 개의 데이터를 쿼리하면 어떤 일이 일어날지 모르는 사람들도 있을 것입니다.

오늘은 실습을 통해 안내해드리겠습니다. 이번에는MySQL 5.7.26테스트용

데이터 준비

데이터가 1,000만 개가 없으면 어떻게 해야 할까요? ?

데이터 없이는 직접 만들 수 없나요?

데이터 생성이 어려우신가요?

1천만 코드 생성?

그건 불가능해요. 너무 느리거든요. 달리는 데 정말 하루 종일 걸릴 수도 있어요. 데이터베이스 스크립트를 사용하면 훨씬 빠르게 실행할 수 있습니다.

Create table

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;

로그인 후 복사

Create data script

일괄 삽입을 사용하면 효율성이 훨씬 빨라지고, 데이터 양이 너무 많으면 1000개 항목마다 커밋되므로 효율성이 떨어집니다. 일괄 삽입도 느려집니다

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(아직 인덱싱 없음)로 38분 동안 실행되었습니다. 컴퓨터 구성이 좋은 학생들은 테스트를 위해 여러 포인트의 데이터를 삽입할 수 있습니다

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	21 7ms
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

로그인 후 복사

查询结果如下：