MySQL の挿入ステートメントの使用実体験

1. 挿入のいくつかの構文

1-1. 通常の挿入文

テーブルに INSERT INTO (`a`, `b`, `c`, ...) VALUES ('a', 'b', 'c', ...);

ここでは詳細には触れませんが、順序に注意してください。先頭の括弧内のコンテンツを削除することはお勧めしません。同僚に叱られる可能性があるので、理由を聞かないでください。

1-2. 挿入または更新

新しいレコードを挿入 (INSERT) するが、レコードが既に存在する場合は更新する場合は、「INSERT INTO ... ON DUPLICATE KEY UPDATE ...」ステートメントを使用できます。

シナリオ例: このテーブルには、ユーザーの過去のチャージ金額が格納されます。初めてのチャージの場合は、新しいレコードが追加されます。ユーザーが以前にチャージしたことがある場合は、過去のチャージ金額が累積されます。1 人のユーザーのデータが繰り返し入力されないようにする必要があります。

このとき、「INSERT INTO ... ON DUPLICATE KEY UPDATE ...」ステートメントを使用できます。

注: 「INSERT INTO ... ON DUPLICATE KEY UPDATE ...」ステートメントは、一意のインデックスまたは主キーに基づいて一意性 (存在) を決定します。次の SQL に示すように、ユーザー名フィールドに一意のインデックス (Unique) を作成し、transId を自動インクリメントに設定する必要があります。

-- ユーザー Chen Haha は 30 元をチャージして会員権を購入しました INSERT INTO total_transaction (t_transId, username, total_amount, last_transTime, last_remark) 
 値 (null, 'chenhaha', 30, '2020-06-11 20:00:20', 'メンバーシップを追加') 
 重複キー更新の場合 total_amount=total_amount + 30、last_transTime='2020-06-11 20:00:20'、last_remark ='充会员';
 
-- ユーザー Chen Haha は 100 元をチャージして、盲人の覇拳スキンを購入しました INSERT INTO total_transaction (t_transId, username, total_amount, last_transTime, last_remark) 
 VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', 'リー・シン シュプリーム フィスト スキンを購入') 
 重複キー更新時 total_amount=total_amount + 100、last_transTime='2020-06-11 21:00:00'、last_remark ='リー・シン シュプリーム フィスト スキンを購入';

ユーザー名が「chenhaha」のレコードが存在しない場合は、INSERT ステートメントによって新しいレコードが挿入されます。それ以外の場合は、ユーザー名が「chenhaha」の現在のレコードが更新され、更新されるフィールドは UPDATE によって指定されます。

ちなみに、ON DUPLICATE KEY UPDATE は MySQL 固有の構文です。例えば、MySQL を Oracle や他の DB に移行する場合、同様の文を MERGE INTO 構文に変更する必要があり、互換性の問題がひどく、呪いたくなるほどで​​す。しかし、WPS で記述された xlsx を Office で開くことができないのと同じように、方法はありません。

1-3. 挿入または交換

新しいレコードを挿入したい場合 (INSERT)、レコードがすでに存在する場合は、まず元のレコードを削除してから新しいレコードを挿入します。

シナリオ例: このテーブルには、各顧客の最新の取引注文情報が格納されます。単一ユーザーのデータが繰り返し入力されないようにし、実行効率を最大化し、データベースとのやり取りを最小限に抑え、データベースの高可用性をサポートする必要があります。

このとき、「REPLACE INTO」ステートメントを使用すれば、最初にクエリを実行してから削除するかどうかを決定し、その後に挿入する必要がなくなります。

「REPLACE INTO」ステートメントは、一意性 (存在) が一意のインデックスまたは主キーに基づくかどうかを決定します。
「REPLACE INTO」ステートメントは、一意性 (存在) が一意のインデックスまたは主キーに基づくかどうかを決定します。
「REPLACE INTO」ステートメントは、一意性 (存在) が一意のインデックスまたは主キーに基づくかどうかを決定します。

注: 次の SQL に示すように、ユーザー名フィールドに一意のインデックス (Unique) を作成し、transId を自動増分に設定する必要があります。

-- 20 ポイントの再チャージを last_transaction (transId,username,amount,trans_time,remark) に置き換えます 
 VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', 'メンバーリチャージ');
 
-- 21 時にスキンを購入 REPLACE INTO last_transaction (transId, username, amount, trans_time, remark) 
 VALUES (null, 'chenhaha', 100, '2020-06-11 21:00:00', 'リー・シン・シュプリーム・フィストスキンを購入');

ユーザー名が「chenhaha」のレコードが存在しない場合は、REPLACE ステートメントによって新しいレコードが挿入されます (最初の再充電)。それ以外の場合は、ユーザー名が「chenhaha」の現在のレコードが削除され、新しいレコードが挿入されます。

特別なビジネス要件がない限り、ID に特定の値を指定しないでください。そうしないと、SQL の実行に影響します。

ヒント:
ON DUPLICATE KEY UPDATE: 挿入された行に重複する一意のインデックスまたは主キーがある場合は、古い更新が実行されます。重複する一意のインデックスまたは主キーが発生しない場合は、新しい行が直接追加されます。
REPLACE INTO: 挿入された行に重複する一意のインデックスまたは主キーがある場合は、古いレコードを削除して新しいレコードを入力します。重複する一意のインデックスまたは主キーが発生しない場合は、新しい行を直接追加します。

重複更新時の置換と挿入:

1. 重複する主キーまたは一意のインデックスがない場合、replace into は重複更新に対する insert と同じです。

2. 主キーまたは一意のインデックスが重複している場合、replace は古いレコードを削除して新しいレコードを入力するため、元のレコードはすべてクリアされます。このとき、replace ステートメントのフィールドが不完全な場合は、c フィールドなどの元の値の一部にデフォルト値 (Null など) が自動的に入力されます。

3. 注意深い友人は、重複更新に対する挿入は 1 行のみに影響しますが、REPLACE INTO は複数の行に影響する可能性があることに気付くでしょう。なぜでしょうか?記事の最後の部分に書かれています〜

1-4. 挿入または無視

新しいレコードを挿入したいが (INSERT)、レコードがすでに存在する場合はそれを無視して何もしないようにしたいとします。この場合、INSERT IGNORE INTO ... ステートメントを使用できます。シナリオは多数あるため、例を挙げて詳しく説明することはしません。

注: 上記のように、「INSERT IGNORE INTO …」ステートメントは、一意のインデックスまたは主キーに基づいて一意性 (存在するかどうか) を決定します。ユーザー名フィールドに一意のインデックス (Unique) を作成し、transId を自動増分に設定する必要があります。

-- ユーザーが初めて追加されました INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) 
 値 (null, 'chenhaha', '男性', 26, 0, '2020-06-11 20:00:20');
 
-- 2 番目の追加、直接無視 INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) 
 値 (null, 'chenhaha', '男性', 26, 0, '2020-06-11 21:00:20');

2. 大量のデータの挿入

2-1. 3つの治療法

2-1-1. シングルループ挿入

100,000 個のデータを取得して、いくつかのテストを行いました。挿入方法は、プログラム トラバーサル ループで 1 つずつ挿入する方法です。 MySQL での行の挿入速度は 0.01 秒から 0.03 秒です。

1つずつ挿入する場合の平均速度は0.02*100000で、約33分です。

次のコードはテスト例です。

1. 通常のループで100,000件のレコードを挿入する時間テスト

 @テスト
 パブリックボイド insertUsers1() {
  
  ユーザー user = new User();
  
  user.setUserName("キャプテン・ティーモ");
  user.setPassword("死にかけ");
  ユーザー.setPrice(3150);
  user.setHobby("キノコを育てる");
  
  (int i = 0; i < 100000; i++) の場合 {
   user.setUserName("キャプテン・ティーモ" + i);
   //挿入メソッドを呼び出します userMapper.insertUser(user);
  }
 }

実行速度は30分、つまり0.018*100000です。非常に遅いと言えます。

一つずつ挿入して最適化するとコストが高すぎることがわかりました。次に、クエリの最適化方法に進みます。バッチ挿入方式により速度が大幅に向上することがわかりました。

10万件のデータレコードの挿入速度を1～2分程度に向上↓

2-1-2. バッチ挿入用のSQL文を変更する

user_info に挿入 (user_id、ユーザー名、パスワード、価格、趣味)
 値（null,'キャプテン ティーモ 1','123456',3150,'キノコの成長'）,(null,'ガレン','123456',450,'キノコを踏む');

バッチ挿入を使用して 100,000 件のレコードを挿入します。テスト コードは次のとおりです。

 @テスト
 パブリックボイド insertUsers2() {
   
  リスト<ユーザー> list= 新しい ArrayList<ユーザー>();
		
  ユーザー user = new User();
  user.setPassword("死にかけ");
  ユーザー.setPrice(3150);
  user.setHobby("キノコを育てる");
		
  (int i = 0; i < 100000; i++) の場合 {
   user.setUserName("キャプテン・ティーモ" + i);
   // パラメータ リストに単一のオブジェクトを追加します list.add(user);
   
  }
  userMapper.insertListUser(リスト);
 }

バッチ挿入には 0.046 秒かかります。これは、1 つまたは 2 つのデータを挿入する速度に相当します。したがって、バッチ挿入を使用すると、データ挿入速度が大幅に向上します。挿入するデータの量が多い場合は、バッチ挿入の最適化を使用します。

バッチ挿入の書き方:

DAO定義レイヤー方式:

整数 insertListUser(List<User> user);

mybatis Mapper での SQL の記述:

<挿入id="insertListUser" パラメータタイプ="java.util.List">
  `db`.`user_info` に挿入します
   ( `id`,
    `ユーザー名`、
    `パスワード`、
    「価格」、
    趣味 
   価値観
  <foreach collection="list" item="item" Separator="," index="index">
   (ヌル、
   #{item.userName},
   #{item.パスワード},
   #{item.price},
   #{item.趣味})
  </foreach>
 
 </挿入>

これにより、バッチ挿入操作が可能になります。

注意：ただし、バッチ操作データの量が多い場合。たとえば、SQL ステートメントに 100,000 件のレコードを挿入し、操作されるデータ パケットが 1M を超えると、MySQL は次のエラーを報告します。

エラーメッセージ:

Mysql サーバー上でこの値を変更するには、max_allowed_pa​​cket 変数を設定します。クエリのパケットが大きすぎます (6832997 > 1048576)。サーバー上でこの値を変更するには、max_allowed_pa​​cket 変数を設定します。

説明する：

クエリに使用されたパケットが大きすぎました (6832997 > 1048576)。 max_allowed_pa​​cket 変数を設定することで、サーバー上でこの値を変更できます。

説明から、操作に使用されるパケットが大きすぎることがわかります。ここで挿入されるSQLコンテンツデータのサイズは6Mなので、エラーが報告されます。

解決：

データベースはMySQL57です。情報を調べたところ、MySQLのシステムパラメータの問題であることがわかりました。

max_allowed_pa​​cket（デフォルト値は1048576（1M））

クエリ:

'%max_allowed_pa​​cket%' のような変数を表示します。

この変数の値を変更します: MySQLインストールディレクトリのmy.ini (Windows) または/etc/mysql.cnf (Linux) ファイルの [mysqld] セクション

max_allowed_pa​​cket = 1M、20Mに変更する場合（またはそれ以上、そのような行がない場合はこの行を追加します）、以下に示すように

MySQL サービスを保存して再起動します。 1M より大きく 20M 未満のサイズの SQL ステートメントを実行できるようになりました。

しかし、20M では不十分な場合はどうなるでしょうか?

2-1-3. バッチおよび複数サイクルで挿入する

データベース構成の変更が不便な場合や、挿入するコンテンツが多すぎる場合は、バックエンド コードを通じて制御することもできます。たとえば、100,000 個のデータを挿入する場合、100 回のバッチで 1,000 個ずつ挿入できます。これには数秒しかかかりません。もちろん、各データの内容が非常に大きい場合は、別の問題になります。 。

2-2. 挿入速度を最適化するその他の方法

A. show processlist コマンドを使用して、スレッド プール リソースを占有している他の長いプロセスや多数の短いプロセスがあるかどうかを確認します。いくつかのプロセスをバックアップ データベースに割り当てることでプライマリ データベースへの負荷を軽減できるか、または最初にいくつかの不要なプロセスを終了できるかを確認します。 (手動で頭を掻いていますo_O)

B. 大量のデータをインポートする場合は、最初にインデックスを閉じて、データのインポート後にインデックスを開くこともできます。

無効化: ALTER TABLE user_info DISABLE KEYS;
有効化: ALTER TABLE user_info ENABLE KEYS;

3. REPLACE INTO構文の落とし穴

上で述べたように、テーブルには複数の一意のインデックスがあるため、REPLACE は 3 つ以上のレコードに影響を与える可能性があります。この場合、REPLACE は各一意のインデックスを考慮し、各インデックスに対応する重複レコードを削除してから、新しいレコードを挿入します。 3 つのフィールド a、b、c を持つテーブル 1 があるとします。両方に一意のインデックスがある場合はどうなるでしょうか?先ほどのデータでテストしてみましょう。

-- テーブル作成をテストします。フィールド a、b、c にはすべて一意のインデックスがあります。CREATE TABLE table1(a INT NOT NULL UNIQUE、b INT NOT NULL UNIQUE、c INT NOT NULL UNIQUE);
-- 3つのテストデータを挿入します INSERT into table1 VALUES(1,1,1);
テーブル1にVALUES(2,2,2)を挿入します。
テーブル1にVALUES(3,3,3)を挿入します。

この時点で、table1 にはすでに 3 つのレコードがあり、フィールド a、b、c はすべて一意のインデックスです。

mysql> テーブル1から*を選択します。
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+---+---+---+
セット内の 3 行 (0.00 秒)

次に、REPLACE ステートメントを使用して、table1 にレコードを挿入します。

table1(a, b, c) の VALUES(1,2,3) を置き換えます。

mysql> table1(a, b, c) に VALUES(1,2,3) を代入します。
クエリは正常、4 行が影響を受けました (0.04 秒)

このとき、table1のレコードを次のようにクエリすると、データは1つだけ残ります。

mysql> テーブル1から*を選択します。
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
セット内の 1 行 (0.00 秒)

(ボス: 最初の 10w データを挿入し、さらに 5w データを挿入しても、まだ 8w のデータが残っていますか?? 私たちのデータを犬に食べさせたのですか?!)

REPLACE INTO 構文の確認: 挿入された行に重複する一意のインデックスまたは主キーがある場合は、古いレコードを削除して新しいレコードを入力します。重複する一意のインデックスまたは主キーが発生しない場合は、新しい行を直接追加します。

REPLACE INTO を使用すると、各ユニーク インデックスが影響を受け、データが誤って削除される可能性があることがわかります。したがって、複数のユニーク インデックスを持つテーブルでは REPLACE INTO を使用しないことをお勧めします。

要約する

これで、MySQL実践編のInsert文の使用経験に関する記事は終了です。MySQLのInsert文の使用経験に関するより関連性の高いコンテンツについては、123WORDPRESS.COMの過去の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも123WORDPRESS.COMをよろしくお願いいたします。