この記事では、データ読み込み操作の効率を向上させるための MySQL の戦略を紹介します。 SELECT クエリは最も一般的に使用されるクエリであり、最適化方法を決定するのは必ずしも簡単ではないため、SELECT クエリの最適化に悩むことがよくあります。データベースへのデータのロードは比較的簡単です。 SELECT クエリは最も一般的に使用されるクエリであり、最適化方法を決定するのは必ずしも簡単ではないため、SELECT クエリの最適化に悩むことがよくあります。データベースへのデータのロードは比較的簡単です。ただし、データ ロード操作の効率を向上させるために使用できる戦略はあります。その基本原則は次のとおりです。
一括ロードは、各レコードのロード後にインデックス キャッシュをフラッシュする必要がなく、レコードのバッチのロード後にフラッシュできるため、単一行のロードより高速です。
インデックスを使用せずにテーブルをロードすると、インデックスを作成してからロードするよりも高速になります。インデックスがある場合は、レコードをデータ ファイルに追加するだけでなく、新しいレコードの追加を反映するように各インデックスを変更する必要があります。
短い SQL ステートメントは、サーバー側での分析が少なく、ネットワークを介してクライアントからサーバーに送信する速度が速いため、長い SQL ステートメントよりも高速です。これらの要素の一部は些細なように見えるかもしれません (特に最後の要素) が、大量のデータをロードしている場合は、小さな要素でも結果に大きな違いが生じる可能性があります。上記の一般原則を使用して、データを最速でロードする方法に関するいくつかの実用的な結論を導き出すことができます。
LOAD DATA (すべての形式) は行をバッチでロードするため、INSERT よりも効率的です。インデックスの更新が少なくなり、サーバーは複数のステートメントではなく 1 つのステートメントを解析および解釈するだけで済みます。
LOAD DATA は LOAD DATA LOCAL よりも効率的です。 LOAD DATA を使用する場合、ファイルはサーバー上に配置され、FILE 権限が必要ですが、サーバーはディスクからファイルを直接読み取ることができます。 LOAD DATA LOCAL を使用すると、クライアントはファイルを読み取り、ネットワーク経由でサーバーに送信しますが、これには時間がかかります。
INSERT を使用する必要がある場合は、次のような 1 つのステートメントで複数の行を指定できる形式を使用する必要があります。
ステートメントで指定できる行が多いほど良いです。これにより、必要なステートメントの数が減り、インデックスの更新量が減ります。 mysqldump を使用してデータベース バックアップ ファイルを生成する場合は、ダンプ ファイルに複数行の INSERT ステートメントが含まれるように --extended-insert オプションを使用する必要があります。 --opt (最適化) を使用して、--extended-insert オプションを有効にすることもできます。逆に、mysqldump に対する --complete-insert オプションの使用は避けてください。このオプションを使用すると、INSERT ステートメントが 1 行になり、実行に時間がかかり、--complete-insert オプションを使用せずに生成されたステートメントよりも多くの分析が必要になります。
圧縮されたクライアント/サーバー プロトコルを使用して、ネットワーク データ トラフィックを削減します。ほとんどの MySQL クライアントでは、これは --compress コマンド ライン オプションで指定できます。圧縮には多くのプロセッサ時間が必要となるため、通常は低速ネットワークでのみ使用されます。
MySQL にデフォルト値を挿入させます。INSERT ステートメントでデフォルト値が割り当てられるカラムを指定しないでください。平均すると、これによりステートメントが短くなり、ネットワーク経由でサーバーに送信される文字数が減ります。さらに、含まれる値が少ないステートメントでは、サーバーによる分析と変換の必要性が少なくなります。
テーブルにインデックスが付けられている場合は、一括挿入 (LOAD DATA または複数行の INSERT ステートメント) を使用してインデックスのオーバーヘッドを削減できます。これにより、各行の後でではなく、すべての行が処理されたときにのみインデックスを更新する必要があるため、インデックスの更新の影響が最小限に抑えられます。
大量のデータを新しいテーブルにロードする必要がある場合は、テーブルを作成してインデックスが作成されていないときにロードし、データのロード後にインデックスを作成する必要があります。この方が高速です。インデックスを 1 回作成すると (行ごとに 1 回変更するよりも) 高速になります。
ロード前にインデックスを削除または無効にした場合、データのロード後にインデックスを再作成または有効にすると、ロードが高速化される可能性があります。データのロードに削除または無効化戦略を使用したい場合は、必ずいくつかの実験を行って、その価値があるかどうかを確認してください (少量のデータを大きなテーブルにロードしている場合は、再構築とインデックス作成にロードよりも時間がかかる場合があります)データ））。
DROP INDEX および CREATE INDEX を使用して、インデックスの削除と再構築を行うことができます。別の方法は、myisamchk または isamchk を使用してインデックスを無効にしてから有効にすることです。これには、テーブル ファイルへの書き込みアクセス権を持つ MySQL サーバー ホスト上のアカウントが必要です。テーブルインデックスを無効にするには、対応するデータベースディレクトリに入り、次のコマンドのいずれかを実行します。
.MYI 拡張子を持つインデックス ファイルを持つ MyISAM テーブルには myisamchk を使用し、.ISM 拡張子を持つインデックス ファイルを持つ ISAM テーブルには isamchk を使用します。データをテーブルにロードした後、次のようにインデックスをアクティブ化します。
インデックスの無効化とアクティブ化を使用する場合は、第 13 章で説明されているテーブル修復ロック プロトコルを使用して、サーバーがロックを同時に変更しないようにする必要があります (この時点ではテーブルは修復されませんが、テーブルと同様に変更されます)修復プロセスのため、同じロック プロトコルを使用する必要があります)。
上で説明したデータ読み込みの原則は、さまざまな操作を実行する必要があるクライアントに関連する固定クエリにも適用されます。たとえば、一般に、頻繁に更新されるテーブルに対して長い SELECT クエリを実行することは避けたいと考えられます。 SELECT クエリの実行時間が長いと、大量の競合が発生し、ライターのパフォーマンスが低下する可能性があります。考えられる解決策の 1 つは、書き込みが主に INSERT 操作である場合、最初に一時テーブルにレコードを格納し、その後定期的にレコードをメイン テーブルに追加することです。新しいレコードに即座にアクセスする必要がある場合、これは実現可能なアプローチではありません。ただし、この方法は、短期間アクセスされない限り使用できます。一時テーブルを使用することには 2 つの利点があります。まず、メイン テーブル上の SELECT クエリ ステートメントとの競合が軽減されるため、実行が高速化されます。第 2 に、一時テーブルからメイン テーブルにレコードをロードする合計時間は、レコードを個別にロードする合計時間よりも短くなります。対応するインデックス キャッシュは、各行の後ではなく、各バッチ ロードの終了時にのみ更新する必要があります。負荷。この戦略の応用例の 1 つは、Web サーバーの Web ページから MySQL データベースにアクセスすることです。このシナリオでは、メイン テーブルへのレコードの即時入力を保証する、より高いレベルの権限が存在しない可能性があります。
データがシステムのシャットダウン時に挿入される単一レコードの種類ではない場合、インデックスの更新を減らす別の戦略は、MyISAM テーブルの DELAYED_KEY_WRITE テーブル作成オプションを使用することです (これは、MySQL がシステムのシャットダウン時に挿入される場合に可能です)。データ入力作業が発生します)。このオプションを使用すると、インデックス キャッシュが挿入のたびではなく、時々のみ更新されます。
サーバー全体でインデックスの遅延更新を利用したい場合は、 --layed-key-write オプションを指定して mysqld を起動するだけです。このシナリオでは、他のインデックス値用のスペースを確保するためにブロックをフラッシュする必要があるまで、flush-tables コマンドが実行されるまで、またはインデックス テーブルが閉じられるまで、インデックス ブロックの書き込みは遅延されます。
-