MySql で、存在しない場合は挿入し、存在する場合は更新する方法

まとめ

シナリオによっては、レコードがない場合は挿入し、レコードがある場合は更新するという要件がある場合があります。たとえば、ID 番号を一意の識別子として使用して新しいユーザーを追加するときに、挿入するか更新するかを決定する前にまずレコードが存在するかどうかを確認すると、同時実行性が高い状況では必然的に問題が発生します。この記事では 3 つの解決策を紹介します。

解決策1: ロック

この問題は、同期ロック、ReentranLock ロック、または分散ロックを使用することで解決できます。欠点は、ロックがパフォーマンスに影響することです。方法 2 と 3 はどちらもデータベース レベルのソリューションであり、個人的には方法 1 よりも優れていると感じています。

解決策 2: Unique と Replace Into ... SELECT ...

まず、一意のフィールドに一意のインデックスを追加します: ALTER TABLE tb_name ADD UNIQUE (col1, col2...)。一意のインデックスにより、データの一意性が保証されます。

ユニークインデックスを追加した後、同じデータをINSERT INTOで挿入するとエラーになります。この場合、REPLACE INTOを使用してデータを挿入する必要があります。使い方は同じです。 REPLACE INTO を使用してデータを挿入する場合、同じデータが存在する場合は、以前のレコードが削除され、データが再挿入されます。欠点は、最初に削除してから挿入するプロセスがあり、SQL はすべてのデータ列を考慮する必要があることです。そうしないと、一部の列のデータが失われます。欠点は、一意のインデックスを作成すると挿入効率に影響が出ることです。具体的な例は以下の通りです。

# インデックスを作成する ALTER TABLE user ADD UNIQUE (id_card);
# ユーザー テーブルには id、name、id_card の 3 つのフィールドのみがあり、id フィールドは自動的にインクリメントされると想定します。
# ここで、name=ly、id_card=142733 のレコードを挿入する必要があります。
# ただし、id_card=142733 のレコードがある場合は、name=ly を変更するだけです。
ユーザー (id,name,id_card) に置き換えます 
SELECT id,'ly',142733 FROM user RIGHT JOIN (SELECT 1) AS tab 
オン user.id_card = 142733;

RIGHT JOIN (SELECT 1) により、id_card=142733 のレコードがある場合、SQL 実行後に元の id が一時結果セットに保存され、name および id_card とともに挿入されます。レコードが存在しない場合は、name および id_card とともに null が id として挿入されます。最終的な実装

解決策3: 挿入前のステートメントを使用してレコードが存在するかどうかを判断する

挿入前ステートメントを使用して挿入を試行し、変更されたレコードが 0 より大きいかどうかを判断します。0 より大きい場合は、挿入が成功したことを意味します。0 の場合は、レコードが既に存在しており、更新する必要があることを意味します。

# 事前挿入 INSERT INTO user (name,id_card)
DUALから「ly」、142733を選択 
存在しない場合 (ID カードを選択、ユーザーから ID カードを選択、ID カード = 142733)
# 挿入前ステートメントが正常に挿入された場合 (変更されたレコードの数 = 1)、後続の操作は必要ありません。それ以外の場合は、更新操作を実行します。
ユーザーを更新します。SET name = 'ly' WHERE id_card = 142733;

NOT EXISTE条件により、id_card=142733のレコードがある場合、疑似テーブルDUAL内のレコードは空になり、pre-insertステートメントによってレコードが0に変更されます。このとき、更新操作を実行する必要があります。

id_card=142733 のレコードがない場合、疑似テーブル DUAL は、コンテンツ 'ly',142733 の 1 つの行を記録します。挿入前ステートメントによってレコードが 1 に変更されるため、更新ステートメントを実行する必要はありません。

MySQL バッチ挿入および更新パフォーマンスの最適化

大量のデータを挿入および更新する場合、IO/CPU などのパフォーマンスボトルネックにより、多くの時間がかかります。現在主流の最適化には、主にプリコンパイル、単一の SQL ステートメントによる複数データの挿入、トランザクションの挿入などがあります。以下は、詳細な紹介です。

単一挿入 (Mybatis)

SYS_CITY (CITY_CODE、CITY_NAME、PROVINCE_NAME、ALIAS、ABBRE_PY) 値に挿入
(${cityCode}、${cityName}、${provinceName}、${alias}、${abbrePy})

単一のプリコンパイル済み挿入 (Mybatis)

プリコンパイルによりMySQLサービスの解析時間を節約できます。Mytatisは#variableを使用します

SYS_CITY (CITY_CODE、CITY_NAME、PROVINCE_NAME、ALIAS、ABBRE_PY) 値に挿入
(#{cityCode}、#{cityName}、#{provinceName}、#{alias}、#{abbrePy})

複数のレコードを単一のSQL文に挿入する

つまり、SQL を結合し、1 つの SQL で複数のデータを挿入したり、複数のデータを更新したりします。

SYS_CITY (CITY_CODE、CITY_NAME、PROVINCE_NAME、ALIAS、ABBRE_PY) 値に挿入
("cityCode1", "cityName1", "provinceName1" "alias1", "abbrePy1"),("cityCode2", "cityName2", "provinceName2" "alias2", "abbrePy2")

速い理由

1. マージ後のログの量（MySQL binlog と innodb トランザクション ログ）が削減され、ログ フラッシュの量と頻度が削減され、効率が向上します。

2. SQL ステートメントをマージしてネットワーク転送 IO を削減します。

3. SQL ステートメントをマージして SQL ステートメントの解析回数を減らします。

予防

1. データベース SQL の長さには制限があります。SQL の長さを超えないようにしてください。そうしないと、エラーが報告されます。

2. 順序どおりに挿入しない場合、速度が innodb_buffer の容量を超えます。インデックスの配置ごとにディスクの読み取りと書き込みの操作が増え、パフォーマンスが急激に低下します。

トランザクション挿入

トランザクションの挿入とは、挿入前にトランザクションを開き、挿入後にトランザクションを閉じてコミットすることを意味します。

速い理由

1. INSERT 操作を実行すると、MySQL は内部的にトランザクションを作成し、実際の挿入処理操作はトランザクション内で実行されます。トランザクションを使用すると、トランザクションの作成コストを削減できます。

予防

1. トランザクションは大きすぎることはできません。MySQL には innodb_log_buffer_size 設定項目があります。トランザクションがこれを超えると、ディスクがフラッシュされ、パフォーマンスが低下します。

2. 順序どおりに挿入しない場合、速度が innodb_buffer の容量を超えます。インデックスの配置ごとにディスクの読み取りと書き込みの操作が増え、パフォーマンスが急激に低下します。

テスト結果

環境: i5-4200U 1.6GHZ、12G メモリ、ソリッド ステート ドライブ

要約する

マージされた SQL とトランザクション挿入の組み合わせが最も効率的です。順序どおりに挿入しない場合、速度が innodb_buffer の容量を超えます。各インデックスの配置には、より多くのディスク読み取りおよび書き込み操作が含まれ、パフォーマンスが急速に低下します。順序どおりに挿入しない方法を使用するようにしてください。上記は私の個人的な経験です。参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。