MySQL データ挿入最適化メソッドconcurrent_insert

スレッドがテーブルに対して DELAYED ステートメントを実行するときに、そのようなハンドラーが存在しない場合は、テーブルのすべての DELAYED ステートメントを処理するハンドラー スレッドが作成されます。

一般的に、 MyISAMの読み取りおよび書き込み操作はシリアルですが、同じテーブルをクエリして挿入する場合、ロック競合の頻度を減らすために、MyISAM は、concurrent_insert の設定に従ってクエリと挿入を並列に処理できます。

parallel_insert=0 の場合、同時挿入機能は許可されません。
parallel_insert=1 の場合、ホールのないテーブルに対して同時挿入が許可され、新しいデータはデータ ファイルの末尾に追加されます (デフォルト)。
parallel_insert=2 の場合、テーブルにホールがあるかどうかに関係なく、データ ファイルの末尾に同時挿入が許可されます。

どうやらconcurrent_insertを2に設定するとコスト効率が非常に良いようです。結果として生じるファイルの断片化に関しては、OPTIMIZE TABLE構文を使用して定期的に最適化することができます。

最大書き込みロック数:

デフォルトでは、書き込み操作の優先度は読み取り操作の優先度よりも高くなります。読み取り要求が最初に送信され、書き込み要求が後で送信された場合でも、書き込み要求が最初に処理され、その後に読み取り要求が処理されます。これにより問題が発生します。複数の書き込み要求を発行すると、すべての書き込み要求が処理されるまですべての読み取り要求がブロックされ、その後読み取り要求を処理できるようになります。現時点では、max_write_lock_count の使用を検討できます。

最大書き込みロック数=1

この設定では、システムが書き込み操作を処理するときに、読み取り操作を実行する機会を与えるために書き込み操作が一時停止されます。

優先度の低いアップデート:

もっと簡単に言えば、書き込み操作の優先度を直接下げて、読み取り操作の優先度を高くすることもできます。

低優先度アップデート=1

まとめると、concurrent_insert=2 が絶対に推奨されます。max_write_lock_count=1 と low-priority-updates=1 については、状況によって異なります。書き込み操作の優先度を下げることができる場合は low-priority-updates=1 を使用し、そうでない場合は max_write_lock_count=1 を使用します。

変数設定 = max_allowed_pa​​cket=1M
変数設定 = net_buffer_length=2K

MyISAMエンジンの下で

1. データを挿入するには、 insert into table_name values ​​(…), (…..), (…..) を使用するようにし、 inset into table_name values ​​(); inset into table_name values ​​(); inset into table_name values ​​(); の使用は避けてください。

2 bulk_insert_buffer_sizeを増やす（デフォルト8M）

3 テーブルが空でない場合は、alter table table_name enable keys を使用し、データを infile にロードして、データをインポートした後に実行します。

alter table table_name はキーを有効にします。空のテーブルの場合、この操作は必要ありません。MyISAM テーブルが空のテーブルにデータをインポートする場合、最初にデータをインポートしてからインデックスを作成するためです。

4 データを挿入するときは、次の使用を検討してください: insert delayed… この操作は、挿入操作をキューに入れて比較的集中的に挿入するため、より高速になります。

5. load data infile を使用すると、挿入操作を使用する場合よりも約 20 倍高速になります。この操作を試してみてください。

InnoDBエンジン

1. データをインポートする前に、set unique_checks=0 を実行して、一意のインデックスのチェックを無効にします。データをインポートした後、set unique_checks=1 を実行します。

2. データをインポートする前に、set foreign_key_checks=0 を実行して外部キー チェックを無効にします。データをインポートした後、set foreign_key_checks=1 を実行します。

3. データをインポートする前に、set autocommit=0 を実行して自動トランザクションの自動コミットを無効にします。データのインポートが完了したら、set autocommit=1 を実行して自動コミット操作を復元します。

Innodb エンジンを使用するテーブルの場合、物理ストレージは PK 順序で保存されます。 MyISAM のような無効化キーは使用できません。

ハードウェア上のディスク I/0 を改善すると、挿入速度が大幅に向上します (したがって、大量のデータをインポートまたはエクスポートする場合は、完了時間を短縮して問題を防ぐために、比較的優れたハードウェアで実行するようにしてください)。

スレッドがテーブルに対して DELAYED ステートメントを実行するときに、そのようなハンドラーが存在しない場合は、テーブルのすべての DELAYED ステートメントを処理するハンドラー スレッドが作成されます。

スレッドは、ハンドラがすでに DELAYED ロックを取得しているかどうかを確認します。取得していない場合は、ハンドラにロックを取得するように指示します。別のスレッドがテーブルに対して READ または WRITE ロックを持っている場合でも、DELAYED ロックを取得できます。ただし、ハンドラーは、テーブル構造が最新であることを確認するために、ALTER TABLE ロックまたは FLUSH TABLES を待機します。

スレッドは INSERT ステートメントを実行しますが、行をテーブルに書き込む代わりに、最後の行のコピーをプロセッサ スレッドによって管理されるキューに配置します。構文エラーはスレッドによって検出され、クライアント プログラムに報告されます。

クライアントは、結果の行の繰り返し回数や AUTO_INCREMENT 値を報告できません。挿入操作が完了する前に INSERT が返されるため、サーバーから取得できません。 C API を使用する場合、同じ理由で、mysql_info() 関数は意味のある結果を何も返しません。

行がテーブルに挿入されると、プロセッサ スレッドによって更新ログが更新されます。複数行の挿入の場合、最初の行が挿入されたときに更新ログが更新されます。
delayed_insert_limit 行を書き込んだ後、プロセッサは SELECT ステートメントがまだ未処理であるかどうかを確認し、未処理である場合は続行する前にそれらのステートメントを実行できるようにします。

プロセッサのキューに行がなくなると、テーブルはロック解除されます。 delayed_insert_timeout 秒以内に新しい INSERT DELAYED コマンドが受信されない場合、ハンドラーは終了します。

特定のプロセッサのキューに delayed_queue_size を超える行がすでに保留中の場合、スレッドはキューに空きができるまで待機します。これにより、mysqld サーバーが遅延メモリ キューにすべてのメモリを使用しないことが保証されます。

プロセッサ スレッドでは、MySQL プロセス テーブルのコマンド列に delayed_insert が表示されます。 FLUSH TABLES コマンドを実行するか、KILL thread_id を使用して強制終了すると、強制終了されますが、終了する前に、キューに入れられたすべての行がテーブルに格納されます。この間、他のスレッドからの新しい INSERT コマンドは受け入れられません。その後に INSERT DELAYED を実行すると、新しいプロセッサ スレッドが作成されます。

上記は、INSERT DELAYED ハンドラがすでに実行されている場合、INSERT DELAYED コマンドが通常の INSERT よりも優先されることを意味することに注意してください。その他の更新コマンドは、INSERT DELAY キューが空になるまで待機するか、プロセッサ スレッドを強制終了するか (KILL thread_id を使用)、FLUSH TABLES を実行する必要があります。

次のステータス変数は、INSERT DELAYED コマンドに関する情報を提供します: Delayed_insert_threads プロセッサ スレッドの数。

Delayed_writes INSERT DELAYEDで書き込まれた行数
Not_flushed_delayed_rows 書き込み待ちの行数

同時挿入ステートメントの高頻度処理に対するソリューション

序文

1. データの多重変更を防ぐ

1.1、ソリューションを挿入

1. 問題を解決するための一意の質問を追加する（重複している場合は更新する）

挿入は通常は問題ありませんが、一意性を制御するだけで、2 つの挿入が行われないようにします (重複した場合は更新操作が実行されます)。

2. 更新計画

1. Redis 分散ロック、メッセージ キュー (一度に挿入されるのは 1 つだけです)

2. MySQLロック（更新には楽観的ロックが使用可能）

2. 高い同時実行性におけるセキュリティ

1. オンライン Web サイトで大規模な DELETE または INSERT クエリを実行して、操作によって Web サイト全体が応答しなくなるのを回避します。これら 2 つの操作によりテーブルがロックされるため (一意の主キーまたはインデックスが指定されていない場合は更新でもテーブルがロックされます)、テーブルがロックされると他の操作は実行できなくなります。だから気をつけてください

2. テーブルを一定期間（たとえば 30 秒）ロックすると、アクセス数が多いサイトでは、この 30 秒間に蓄積されたアクセス プロセス/スレッド、データベース リンク、開いているファイルの数によって、Web サービスがクラッシュするだけでなく、サーバー全体が即座にハングアップする可能性があります。 >

2.1 解決策

2.1.1. テーブル調整

テーブルを列ごとに複数のテーブルに分割する方法により、テーブルの複雑さとフィールドの数を減らすことができ、最適化の目的を達成できます。 （フィールドが100以上あったら怖いですね）

例1:

ユーザーテーブルには、自宅住所というフィールドがあります。このフィールドはオプションのフィールドです。個人情報以外は、データベースを操作するときにこのフィールドを頻繁に読み取ったり書き換えたりする必要はありません。では、別のテーブルに置いてみてはいかがでしょうか?これにより、テーブルのパフォーマンスが向上します。考えてみてください。ほとんどの場合、ユーザー テーブルでは、ユーザー ID、ユーザー名、パスワード、ユーザー ロールなどのみが頻繁に使用されます。テーブルが小さいほど、パフォーマンスは常に向上します。

例2:

ユーザーがログインするたびに更新される「last_login」というフィールドがあります。ただし、更新するたびにテーブルのクエリ キャッシュがクリアされます。したがって、クエリ キャッシュによってパフォーマンスが大幅に向上するため、このフィールドを別のテーブルに配置すると、ユーザー ID、ユーザー名、およびユーザー ロールの継続的な読み取りに影響が及ばなくなります。 HP プログラマーのホーム

また、これらの分離されたフィールドによって形成されるテーブルが頻繁に結合されることは考えられないことにも注意が必要です。そうしないと、テーブルが分割されていない場合よりもパフォーマンスが悪くなり、極端な低下が発生します。