インタビュアー: 数千万のデータをすばやくクエリするにはどうすればよいですか?-mysql チュートリアル-php.cn

最初にインタビューのシナリオを見てみましょう:

インタビュアー: 1,000 万件のデータについて話しましょう。問い合わせますか？
弟: ページングによって直接クエリを実行します。制限ページングを使用します。
インタビュアー: 実際にやったことはありますか?
弟: 必ず 1 つあるはずです。

友達の中には、数千万のデータを含むテーブルに一度も遭遇したことがない人もいるかもしれません。クエリの方法がわかりません。数千万のデータがある場合はどうなりますか?

今日は練習方法を紹介します今回は MySQL 5.7.26 バージョンをベースにしてテストします

データの準備

1,000 万のデータがない場合はどうすればよいですか?

作成してください

1,000 万を生み出すコードはありますか?それは不可能です、遅すぎます、そして丸一日かかるかもしれません。データベーススクリプトを使用すると、より高速に実行できます。

テーブルの作成

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 項目ごとにコミットされます。データの量が多すぎる場合は、大きい場合、一括挿入にもつながります。挿入効率が遅くなります。

DELIMITER ;;
CREATE PROCEDURE batch_insert_log()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE userId INT DEFAULT 10000000;
 set @execSql = &#39;INSERT INTO `test`.`user_operation_log`(`user_id`, `ip`, `op_data`, `attr1`, `attr2`, `attr3`, `attr4`, `attr5`, `attr6`, `attr7`, `attr8`, `attr9`, `attr10`, `attr11`, `attr12`) VALUES&#39;;
 set @execData = &#39;&#39;;
  WHILE i<=10000000 DO
   set @attr = "&#39;测试很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长的属性&#39;";
  set @execData = concat(@execData, "(", userId + i, ", &#39;10.0.69.175&#39;, &#39;用户登录操作&#39;", ",", @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(还没建索引的情况下)，跑了38min，电脑配置好的同学，可以插入多点数据测试

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

ログイン後にコピー

查询时间如下：

##1000 アイテム61ms74ms60ms

数量

初回

2回目

3回目

10 アイテム

53ms

52ms

47ms

100 アイテム

50ms

60ms

55ms

10000 アイテム

164ms

180ms

217ms

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時間をかけてください最初の記事2 番目の記事 (インデックスなし)2 番目の記事 (インデックスあり)3 番目の記事 (インデックスなし)3 番目のバー (インデックスあり)##

从上面结果得出结论：

第一条花费的时间最大，第三条比第一条稍微好点
子查询使用索引速度更快

缺点：只适用于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

ログイン後にコピー

查询结果如下：


4818ms
4329ms
199ms
4319ms
201ms

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 *」を使用すると、データベースはより多くのオブジェクト、フィールド、権限、属性、およびその他の関連コンテンツを解析する必要があります。複雑で難しい解析回数が多いと、データベースに大きな負担がかかります。
ネットワークオーバーヘッドの増加。* ログや IconMD5 などの役に立たない大きなテキストフィールドが誤って追加される場合があり、データ転送サイズが幾何級数的に増加します。特に、MySQL とアプリケーションは同じマシン上にないため、このオーバーヘッドは非常に明白です。