インタビュアー: 1,000 万件のデータをどのようにクエリしたのですか?
- Java后端技术全栈転載
- 2023-08-15 16:34:591091ブラウズ
最近、私は模擬面接や履歴書の最適化を全員に対して行っていますが、多くの人が 何千万ものデータを見ていることがわかりました。など 面接の質問はあなたを弱くします。
おそらく、数千万のデータを含むテーブルに遭遇したことがなく、数千万のデータをクエリすると何が起こるかわからない人もいるでしょう。
今日は実践的な操作を説明します。今回は、テスト用に
MySQL 5.7.26をベースにしています。
1,000 万のデータがない場合はどうすればよいですか?
データがないと作れませんか？
データ作成は難しいですか？
コードは 1,000 万を作成しますか?
それは不可能です、遅すぎるし、丸一日かかるかもしれません。データベース スクリプトを使用すると、より高速に実行できます。
テーブルの作成
データ スクリプトの作成バッチを使用挿入すると、効率が大幅に向上し、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
Due低構成に設定した場合、このテストの準備のみを行いましたが、3148,000 個のデータが取得され、5G のディスク (インデックスなし) を占有し、38 分間実行されました。適切なコンピューター構成を備えた学生は、テスト用に複数のポイントのデータを挿入できます
SELECT count(1) FROM `user_operation_log`
返される結果: 3148000
3 つのクエリ時間は次のとおりです:
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
查询时间如下：
| 数量 | 初回 | 2回目 | 3回目 | ||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 10 アイテム | 53ms | 52ms | 47ms | ||||||||||||||||||||||||||||||||||||||||||||||||||
| 100 アイテム | 50ms | 60ms | 55ms | ||||||||||||||||||||||||||||||||||||||||||||||||||
| 61ms | 74ms | 60ms | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 164ms | 180ms | 217ms | |||||||||||||||||||||||||||||||||||||||||||||||||||
| 1609ms | 1741ms | 1764ms | ##1000000 記事 | ||||||||||||||||||||||||||||||||||||||||||||||||||
| 16889ms | 17081ms |
| 偏移量 | 第一次 | 第二次 | 第三次 |
|---|---|---|---|
| 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 |
この結果から、不要な列を減らすことでクエリ効率も大幅に向上することがわかります。
1 回目と 3 回目のクエリ速度はほぼ同じです。 「間違いなく文句を言います。なぜそんなに多くのフィールドを書きますか? * だけで完了です。
私の MySQL サーバーとクライアントは同じマシン上にあるので、クエリ データは似ていることに注意してください。資格のある学生はそれをテストできます。」 . MySQL とは別にクライアントをテストする
#SELECT * おいしいでしょう？
ところで、SELECT * が禁止される理由を付け加えたいと思います。シンプルで無頓着だからこそ美味しいんじゃないでしょうか？
2 つの主要なポイント:
「 SELECT *」を使用すると、データベースはより多くのオブジェクト、フィールド、権限、属性、およびその他の関連コンテンツを解析する必要があります。 . SQL ステートメントが複雑でハード解析が多い場合、データベースに大きな負荷がかかります。- #ネットワーク オーバーヘッドが増加します。
# ログや IconMD5 などの役に立たない大きなテキスト フィールドが誤って追加される場合があり、データ転送サイズが幾何級数的に増加します。特に、MySQL とアプリケーションは同じマシン上にないため、このオーバーヘッドは非常に明白です。
以上がインタビュアー: 1,000 万件のデータをどのようにクエリしたのですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。