Intervieweur : Comment avez-vous interrogé 10 millions de données ?
- Java后端技术全栈avant
- 2023-08-15 16:34:591050parcourir
Récemment, j'ai fait des simulations d'entretiens et d'optimisation de CV pour tout le monde, et j'ai découvert que beaucoup de gens s'affaiblissent lorsqu'ils voient des questions comme des dizaines de millions de données.
Peut-être que certaines personnes n'ont jamais rencontré de table contenant des dizaines de millions de données et ne savent pas ce qui se passera lors de l'interrogation de dizaines de millions de données.
Aujourd'hui, je vais vous faire réaliser un exercice pratique. Cette fois, il est basé sur MySQL 5.7.26 pour les tests
Préparation des données
Que faire si vous n'avez pas 10 millions de données. ?
Vous ne pouvez pas le créer vous-même sans données ?
Est-il difficile de créer des données ?
10 millions de création de code ?
C’est impossible, c’est trop lent, ça peut vraiment te prendre une journée entière pour courir. Vous pouvez utiliser des scripts de base de données pour une exécution beaucoup plus rapide.
Créer une 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;
Créer un script de données
En utilisant l'insertion par lots, l'efficacité sera beaucoup plus rapide et tous les 1000 éléments seront validés si la quantité de données est trop importante, l'efficacité. de l'insertion par lots sera également lente
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 ;Démarrez le test
La configuration de mon ordinateur est relativement faible : win10 pression standard i5, lecture et écriture d'environ 500 Mo de SSD
En raison de la faible configuration, seulement 3148000 éléments de données ont été préparés pour ce test, occupant 5 Go de disque (sans encore indexation) et exécuté pendant 38 minutes. Les étudiants ayant une bonne configuration informatique peuvent insérer plusieurs points de données pour les tests
SELECT count(1) FROM `user_operation_log`
Résultat renvoyé : 3148000
Trois. les temps de requête sont :
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
查询时间如下：
| Quantité | Première fois | Deuxième fois | Troisième fois |
|---|---|---|---|
| 10 articles | 53ms | 52ms | 47ms |
| 100 articles | 50ms | 60ms | 55ms |
| 1000 articles | 61ms | 74ms | 60ms |
| 10000 articles | 164ms | 180ms | 21 7ms |
| 100000 articles | 1609ms | 1741ms | 1764ms |
| 1000000 articles | 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 |
Les résultats montrent qu'en réduisant les colonnes inutiles, l'efficacité des requêtes peut également être considérablement améliorée.
Les vitesses de la première et de la troisième requête sont presque les mêmes. À ce stade, vous vous plaindrez certainement, alors pourquoi devrais-je. écrivez autant de champs ? , juste * et vous avez terminé
Notez que mon serveur et mon client MySQL sont sur la même machine, donc les données de requête sont similaires. Les étudiants qualifiés peuvent tester le client et MySQL séparément
SELECT *. Ça ne sent pas bon ?
Au fait, je voudrais ajouter ici pourquoi nous devrions interdire SELECT *. N'est-ce pas délicieux parce que c'est simple et insensé ? SELECT *。难道简单无脑，它不香吗？
主要两点：
用 " SELECT *" 数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。-
增大网络开销， Deux points principaux :*
SELECT * " La base de données doit analyser plus d'objets, de champs, d'autorisations, d'attributs, etc. Contenu, lorsque les instructions SQL sont complexes et qu'il y a de nombreuses analyses difficiles, cela entraînera une lourde charge sur la base de données. * Parfois, log, IconMD5 et autres sont inclus par erreur Inutile et volumineux champs de texte, la taille de la transmission des données augmentera de façon exponentielle. D'autant plus que MySQL et l'application ne sont pas sur la même machine, cette surcharge est très évidente. Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!