MySQL 最適化: キャッシュ最適化 (続き)

MySQL 内部には至るところにキャッシュがあります。MySQL のソースコードを読むと、キャッシュがどのように使用されているかを詳細に分析します。このセクションでは主に、さまざまな明示的なキャッシュを最適化します。

クエリキャッシュの最適化
結果セットキャッシュ
ソートキャッシュ
接続キャッシュに参加する
テーブルキャッシュキャッシュとテーブル構造定義キャッシュキャッシュ
テーブルスキャンバッファ
MyISAM インデックスキャッシュバッファ
ログキャッシュ
先読みメカニズム
遅延テーブルと一時テーブル

1. クエリキャッシュの最適化

クエリキャッシュは、クエリステートメントの構造をキャッシュするだけでなく、クエリの結果もキャッシュします。一定期間内に SQL が同じ場合は、結果がキャッシュから直接読み取られ、データの検索効率が向上します。しかし、キャッシュ内のデータがハードディスク上のデータと一致しない場合、キャッシュは無効になります。

mysql> '%query_cache%' のような変数を表示します。
+------------------------------+---------+
| 変数名 | 値 |
+------------------------------+---------+
| クエリキャッシュがある | はい |
| クエリキャッシュ制限 | 1048576 |
| クエリ キャッシュの最小解像度単位 | 4096 |
| クエリキャッシュサイズ | 1048576 |
| クエリキャッシュタイプ | オフ |
| query_cache_wlock_invalidate | オフ |
+------------------------------+---------+

have_query_cacheクエリキャッシュがサポートされているかどうか。

query_cache_limit選択ステートメントの結果セットのサイズが querycachelimit の値を超える場合、結果セットはクエリキャッシュに追加されません。

query_cache_min_res_unitクエリキャッシュはブロック単位のメモリ空間に適用され、適用される各ブロックのサイズは設定値です。 4K は非常に適切な値なので、変更する必要はありません。

query_cache_sizeクエリキャッシュのサイズ。

query_cache_typeクエリキャッシュタイプ。値は 0 (OFF)、1 (ON)、2 (DEMOND) です。 OFF はクエリキャッシュが無効であることを意味します。 ON は、SELECT ステートメントに sql_no_cache オプションが含まれていない限り、クエリは常に最初にクエリキャッシュを検索することを意味します。 DEMOND は、SELECT ステートメントに sql_cache オプションが含まれていない限り、キャッシュが適用されないことを意味します。

query_cache_wlock_invalidate このパラメータは、行レベルの排他ロックとクエリキャッシュの関係を設定するために使用されます。デフォルト値は 0 (OFF) です。これは、行レベルの排他ロックが適用されている間、テーブルのすべてのクエリキャッシュが有効なままであることを意味します。 1 (ON) に設定すると、行レベルの排他ロックが付与されている間、テーブルのすべてのクエリキャッシュが無効になります。

クエリキャッシュヒット率を表示する

mysql> 'Qcache%' のようなステータスを表示します。
+-------------------------+---------+
| 変数名 | 値 |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031360 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+

現在のキャッシュステータス情報を表示します。

Qキャッシュ空きブロック

クエリキャッシュ内の繰り返し状態にあるメモリブロックの数 (フラグメントの数) を示します。 Qcache_free_blocks の値が大きい場合、クエリキャッシュにフラグメントが多く存在し、クエリ結果セットが小さいことを示しています。この場合、query_cache_min_res_unit の値を減らすことができます。フラッシュクエリキャッシュを使用すると、キャッシュ内の複数のフラグメントが整理され、より大きな空きブロックが取得されます。キャッシュ断片化率 = Qcache_free_blocks/ Qcache_total_blocks * 100%

Qキャッシュ空きメモリ

現在の MySQL サービスインスタンスのクエリキャッシュに使用できるメモリの量を示します。

Qキャッシュヒット

クエリキャッシュが使用された回数を示します。値は順番に増加します。 Qcache_hits が比較的大きい場合は、クエリキャッシュが非常に頻繁に使用されていることを意味し、クエリキャッシュを増やす必要があります。

Qcache_挿入

クエリキャッシュにキャッシュされた SELECT ステートメント結果セットの合計数を示します。

Qcache_lowmen_prunes

クエリキャッシュがいっぱいでオーバーフローしたために MySQL が削除したクエリ結果の数を示します。この値が大きい場合は、クエリキャッシュが小さすぎることを示します。

Qキャッシュがキャッシュされていない

クエリキャッシュに入っていない選択の数を示します

Qcache_queryies_in_cache

クエリキャッシュにキャッシュされている選択ステートメントの結果セットの数を示します。

Qキャッシュ合計ブロック数

クエリキャッシュの合計数

キャッシュヒット率の計算方法: クエリキャッシュヒット率 = Qcache_hits / Com_select * 100%

Com_select は、現在の MySQL インスタンスによって実行された select ステートメントの数です。一般的には、Com_select = Qcache_insert + Qcache_not_cached です。 Qcache_not_cached には、頻繁なデータ変更によりクエリキャッシュの無効化を引き起こす選択ステートメントが含まれているため、ヒット率は一般的に低くなります。無効化の要因を無視すると、クエリキャッシュヒット率 = Qcache_hits / (Qcache_hits + Qcache_inserts) となります。この式を使用して計算されたクエリキャッシュヒット率が比較的高い場合、ほとんどの選択ステートメントがクエリキャッシュにヒットしていることを意味します。

現在のシステムで実行された選択ステートメントの数を表示するには、次のコマンドを使用します。

mysql> 'Com_select' のようなステータスを表示します。
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| Com_select | 1 |
+---------------+-------+

2. 結果セットキャッシュ

結果セットキャッシュは、MySQL クライアントがサーバーに正常に接続した後に作成されるセッションキャッシュです。 MySQL サーバーは、各 MySQL クライアントの結果セットキャッシュを維持します。 MySQL クライアント接続スレッドの接続情報をキャッシュし、MySQL クライアントに返される結果セット情報をキャッシュします。MySQL クライアントがサーバーに SELECT ステートメントを送信すると、MySQL は SELECT ステートメントの実行結果を結果セットキャッシュに一時的に保存します。結果セットのバッファサイズは、net_buffer_length パラメータ値によって定義されます。

mysql> 'net_buffer_length' のような変数を表示します。
+-------------------+------+
| 変数名 | 値 |
+-------------------+------+
| ネットバッファ長 | 16384 |
+-------------------+------+

結果セットが net_buffer_length で設定された値を超える場合、容量は自動的に拡張されますが、max_allowd_packet のしきい値を超えることはありません。

mysql> 'max_allowed_packet' のような変数を表示します。
+--------------------+---------+
| 変数名 | 値 |
+--------------------+---------+
| 最大許容パケット | 4194304 |
+--------------------+---------+

3. ソートキャッシュ

MySQL では通常、InnoDB と MyISAM という 2 つのデータストレージエンジンが使用されます。そのため、最適化を行う際には、各エンジンはそれぞれのエンジンに適した最適化手法を使用します。 MySQL と InnoDB のテーブル構造ファイルとデータログファイルの違いについては、まず私のブログ「MySQL ログシステム」を読んで、これらの基本概念を十分に理解してください。次に、エンジンの最適化方法を読んで、簡単に実行でき、退屈を感じないようにすることができます。

1. 通常のソートキャッシュ

クライアントからサーバーに送信された SQL ステートメントに、デザインソート用の order by 句または group by 句が含まれている場合、ソートキャッシュはセッションキャッシュになります。 MySQL は対応するソートアルゴリズムを選択し、共通のソートインデックスに基づいてソートして、ソート速度を向上させます。通常のソートインデックスのサイズは、sort_buffer_size パラメータによって定義されます。ソート速度を上げたい場合は、まず適切なインデックスを追加し、次にソートインデックスキャッシュ sort_buffer_size を増やす必要があります。

mysql> @@global.sort_buffer_size / 1024 を選択します。
+----------------------------------+
| @@global.sort_buffer_size / 1024 |
+----------------------------------+
| 256.0000 |
+----------------------------------+
セット内の 1 行 (0.00 秒)

次に、ソートキャッシュに関連するパラメータを見てみましょう。

mysql> '%sort%' のような変数を表示します。
+--------------------------------+---------------------+
| 変数名 | 値 |
+--------------------------------+---------------------+
| innodb_disable_sort_file_cache | オフ |
| innodb_ft_sort_pll_degree | 2 |
| innodb_sort_buffer_size | 1048576 |
| ソートデータの最大長 | 1024 |
| 最大ソート長 | 1024 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_sort_buffer_size | 8388608 |
| ソートバッファサイズ | 262144 |
+--------------------------------+---------------------+

mysql> '%sort%' のようなステータスを表示します。
+-------------------+------+
| 変数名 | 値 |
+-------------------+------+
| ソートマージパス | 0 |
| ソート範囲 | 0 |
| 行の並べ替え | 0 |
| ソートスキャン | 0 |
+-------------------+------+

ソートデータの最大長

デフォルトのサイズは 1024 バイトです。ソート操作は各列に対して実行されます。列の値の長さが長い場合は、このパラメータを増やすことで MySQL のパフォーマンスを向上させることができます。

最大ソート長

order by または group by を使用すると、列の最初の max_sort_length バイトがソートに使用されます。ソート操作が完了すると、ソート情報がこのセッションのステータスに記録されます。

ソート_マージ_パス

一時ファイルを使用してソート操作が完了した回数。 MySQL がソート操作を実行する場合、最初に通常のソートキャッシュ内でソートを完了しようとします。キャッシュスペースが不十分な場合、MySQL はキャッシュを使用して複数のソートを実行します。そして、それぞれのソート結果を一時ファイルに保存し、最後に一時ファイル内のデータを再度ソートします。 Sort_merge_passes 値は、ファイルがソートされた回数を記録します。ファイルの並べ替えには、ファイルの読み取り、ファイルハンドルのオープン、そしてファイルのクローズが含まれるためです。そのため、ファイルの読み取りによるシステム消費は比較的大きくなります。通常のソートキャッシュ sort_buffer_size を増やすことで、ソートに一時ファイルを使用する回数を減らすことができ、ソートのパフォーマンスが向上します。

並べ替え範囲

範囲ソートが使用される回数

行の並べ替え

ソートされた行数

ソートスキャン

フルテーブルスキャンによってソートが完了した回数

2. MyISAMソートキャッシュ

alter table ステートメントまたは create index ステートメントを使用して MyISAM テーブルのインデックスを作成したり、load data infile path を使用してデータの一部をインポートしたりすると、これらの操作によってインデックスが再構築されます。インデックスを再構築するときには、インデックスフィールドをソートする必要があります。インデックス再構築の効率を高速化するために、MyISAM はインデックスのソート作業を実装するためのソートキャッシュを提供します。これらのメソッドは、メモリ内でソート作業を完了しようとします。 MyISAM ソートキャッシュのサイズは、myisam_sort_buffer_size によって定義されます。インデックスが再構築されると、キャッシュはすぐに解放されます。

ただし、ソートキャッシュが myisam_sort_buffer_size のしきい値を超えると、一時ファイルでインデックスフィールドのソートを完了する必要があります。外部一時ファイルのサイズは、myisam_max_sort_file_size パラメータによって設定されます。インデックスが再構築されると、一時ファイルはすぐに削除されます。

mysql> @@global.myisam_sort_buffer_size/1024 を選択します。
+---------------------------------------+
| @@global.myisam_sort_buffer_size/1024 |
+---------------------------------------+
| 8192.0000 |
+---------------------------------------+

mysql> @@global.myisam_max_sort_file_size /1024 を選択します。
+------------------------------------------+
| @@global.myisam_max_sort_file_size /1024 |
+------------------------------------------+
|9007199254739967.7734 |
+------------------------------------------+

3. InnoDBソートキャッシュ

MyISAM エンジンと同様に、alter table または create index を実行すると、InnoDB はインデックスのソート用に 3 つの InnoDB ソートバッファを提供します。各キャッシュのサイズは innodb_sort_buffer_size によって定義されます。

mysql> @@global.innodb_sort_buffer_size/1024 を選択します。
+---------------------------------------+
| @@global.innodb_sort_buffer_size/1024 |
+---------------------------------------+
| 1024.0000 |
+---------------------------------------+

4. 接続キャッシュに参加する

結合キャッシュはセッションキャッシュです。2 つのテーブルが接続されているがインデックスが使用できない場合 (これが結合キャッシュを使用する前提です)、MySQL は各テーブルに結合キャッシュを割り当てます。

mysql> @@global.join_buffer_size/1024 を選択します。
+--------------------------------+
| @@global.join_buffer_size/1024 |
+--------------------------------+
| 256.0000 |
+--------------------------------+

join_buffer_size は、上記のように接続バッファのサイズを定義します。デフォルトは 256 です。

5. テーブルキャッシュとテーブル構造定義キャッシュ

MySQL サービスがデータベース内のテーブルにアクセスすると、MySQL は実際にファイルの読み取り操作を実行します。 MySQL データはハードディスク上のファイルに保存されます。これは、一部のメモリベースのデータベースとは異なります。クエリキャッシュの使用を考慮せずに、select ステートメントを使用してテーブルをクエリする場合、オペレーティングシステムは最初にファイルを開いて、ファイルの記述子を生成する必要があります。オペレーティングシステムはファイル記述子を MySQL に渡し、MySQL がデータベースに対して CURD 操作を実行できるようにします。ファイルを開いてファイル記述子を生成すると、システムリソースが消費され、アクセスが遅延します。 MySQL は、ファイル記述子を含む開かれたファイルをキャッシュするため、将来そのファイルに再度アクセスするときにファイルを開く必要がなくなり、ファイルの読み取り効率が向上します。

テーブル構造は頻繁に変更されません。テーブルにアクセスすると、MySQL のテーブルキャッシュにテーブルが埋め込まれるだけでなく、次回の使用に備えてテーブル構造がテーブル構造定義キャッシュにも保存されます。

mysql> 'table%' のような変数を表示します。
+----------------------------+-------+
| 変数名 | 値 |
+----------------------------+-------+
| テーブル定義キャッシュ | 1400 |
| テーブルオープンキャッシュ | 2000 |
| テーブルオープンキャッシュインスタンス | 1 |
+----------------------------+-------+

mysql> '%open%' のような変数を表示します。
+----------------------------+----------+
| 変数名 | 値 |
+----------------------------+----------+
| have_openssl | 無効 |
| innodb_open_files | 2000 |
| オープンファイル制限 | 65535 |
| テーブルオープンキャッシュ | 2000 |
| テーブルオープンキャッシュインスタンス | 1 |
+----------------------------+----------+

テーブルオープンキャッシュ

キャッシュできるテーブルとビューの数に制限を設定します

テーブル定義キャッシュ

保存できるfrmテーブル構造の数を設定します

MySQL MyISAM エンジンの場合、テーブル構造には、テーブル構造 frm に加えて、MYI と MYD が含まれます。MyISAM エンジンにアクセスする場合、2 つのファイル (MYI と MYD) を一度に開いて、2 つのファイル記述子を生成する必要があります。

オープンファイル制限

開いているファイルの最大数

innodb_open_files

InnoDB テーブルが独立したテーブルスペースファイル (ibd) を使用する場合、このパラメータは同時に開くことができるファイルの数を設定します。

以下は、テーブルを開くことに関連するステータス値です。

mysql> 'Open%' のようなステータスを表示します。
+--------------------------+-------+
| 変数名 | 値 |
+--------------------------+-------+
| 開いているファイル | 18 |
| オープンストリーム | 0 |
| テーブル定義を開く | 70 |
| オープンテーブル | 63 |
| 開いたファイル | 125 |
| オープンされたテーブル定義 | 0 |
| オープンしたテーブル | 0 |
+--------------------------+-------+

6. テーブルスキャンバッファ

テーブルスキャンは、シーケンシャルスキャンとランダムスキャンの 2 種類に分けられます。

MyISAM テーブルにインデックスがない場合、クエリ速度は完全なテーブルスキャンとなり、非常に非効率的になります。完全なテーブルスキャンの速度を上げるために、MySQL はシーケンシャルスキャンキャッシュ (読み取りバッファ) を提供します。このとき、MySQL は格納されたデータの格納順序に従ってすべてのデータブロックを読み取ります。読み取られた各データブロックは、シーケンシャルスキャンキャッシュにキャッシュされます。読み取りバッファがいっぱいになると、データは上位レベルの呼び出し元に返されます。

ランダムスキャン

テーブルにキャッシュがある場合、テーブルをスキャンするときに、まずテーブルのインデックスフィールドがメモリに格納され、ソートされ、次にソートされた順序に従ってハードディスク上でデータが検索されます。

7. MyISAMインデックスキャッシュバッファ

MYI インデックスファイルの内容をキャッシュすることで、インデックスの読み取り速度とインデックス作成速度を高速化できます。インデックスキャッシュは MyISAM テーブルに対してのみ機能し、すべてのスレッドで共有されます。クエリステートメントまたはインデックス更新がインデックスを介してテーブルデータにアクセスする場合、MySQL はまず、必要なインデックス情報がインデックスキャッシュにすでに存在するかどうかを確認します。存在する場合、キャッシュ内のインデックスを介して、インデックスに対応する MYD ファイルに直接アクセスできます。そうでない場合は、MYI ファイルが読み込まれ、対応するインデックスデータがキャッシュに読み込まれます。インデックスキャッシュは、MyISAM テーブルのアクセスパフォーマンスにおいて重要な役割を果たします。

mysql> 'key%' のような変数を表示します。
+--------------------------+---------+
| 変数名 | 値 |
+--------------------------+---------+
| キーバッファサイズ | 8388608 (8M)| 
| キーキャッシュの有効期間しきい値 | 300 |
| キーキャッシュブロックサイズ | 1024 |
| キーキャッシュ分割制限 | 100 |
+--------------------------+---------+

キーバッファサイズ

インデックスキャッシュのサイズを設定します。デフォルトは 8M です。改善を推奨します。

キーキャッシュブロックサイズ

各インデックスキャッシュのブロックサイズを指定します。4K、つまり4096に設定することをお勧めします。

キーキャッシュ分割制限

キャッシュを効果的に使用します。デフォルトでは、MySQL キャッシュはウォーム領域とホット領域の 2 つのインデックスキャッシュ領域に分割されます。 key_cache_division_limit パラメータは、インデックスキャッシュをパーセンテージの形式で複数の領域に分割します。デフォルト値が 100 の場合、インデックスキャッシュにはウォームゾーンのみがあり、インデックスキャッシュ内のインデックスを削除する LRU アルゴリズムが有効になります。

キーキャッシュの年齢しきい値

ウォームゾーンとホットゾーンのインデックスが昇格または降格されるタイミングを制御します。値が 100 未満の場合、ホットスポットが存在します。移動アルゴリズムは、LRU アルゴリズムとほぼ同じです。

現在の MySQL サービスインスタンスのインデックス読み取りとインデックス書き込みのステータス値を表示します。

mysql> 'Key%' のようなステータスを表示します。
+------------------------+-------+
| 変数名 | 値 |
+------------------------+-------+
| キーブロックがフラッシュされていません | 0 |
| 未使用のキーブロック | 6698 |
| 使用されたキーブロック | 0 |
| キー読み取り要求 | 0 |
| キー読み取り | 0 |
| キー書き込み要求 | 0 |
| キー書き込み | 0 |
+------------------------+-------+

8. ログキャッシュ

ログキャッシュはバイナリログキャッシュとInnoDB REDOログキャッシュに分かれています。

1. バイナリログキャッシュ

mysql> '%binlog%cache%' のような変数を表示します。
+----------------------------+----------------------+
| 変数名 | 値 |
+----------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| 最大バイナリログキャッシュサイズ | 18446744073709547520 |
| 最大 binlog_stmt キャッシュ サイズ | 18446744073709547520 |
+----------------------------+----------------------+

mysql> '%binlog%cache%' のようなステータスを表示します。
+----------------------------+-------+
| 変数名 | 値 |
+----------------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
+----------------------------+-------+

Mysql はデータを作成または更新すると、バイナリログを記録します。ただし、頻繁な I/O 操作は MySQL のパフォーマンスに大きな影響を与えます。そのため、MySQL はバイナリログキャッシュ binlog_cache_size をオープンしました。まず、操作がバイナリログに書き込まれ、操作が成功するとバイナリログがハードディスクに書き込まれます。

2. InnoDB REDOログキャッシュ

トランザクションがコミットされる前に、生成された redo ログが InnoDB redo ログキャッシュに書き込まれます。次に、InnoDB は [機会を選択して] ポーリング戦略を実行し、キャッシュ内の redo ログファイルを ib_logfile0 および ib_logfile1 redo ログに書き込みます。

mysql> 'innodb_log_buffer_size' のような変数を表示します。
+------------------------+----------+
| 変数名 | 値 |
+------------------------+----------+
| innodb_log_buffer_size | 8388608 |
+------------------------+----------+

InnoDB 再実行ログキャッシュは、トランザクション中に生成された再実行ログが、トランザクションがコミットされる前に InnoDB ログキャッシュに保存され、再実行ログファイルには書き込まれないことを保証します。書き込みのタイミングは、innodb_flush_log_at_trx_commit パラメータによって制御されます。

mysql> 'innodb_flush_log%' のような変数を表示します。
+--------------------------------+-------+
| 変数名 | 値 |
+--------------------------------+-------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+

0: キャッシュ内のREDOログファイルが1秒ごとにディスクキャッシュに書き込まれると同時にディスクにも更新されます。

1: トランザクションがコミットされるたびに、キャッシュ内の REDO ログが REDO ログファイルに書き込まれ、同時にハードディスクに書き込まれます。これがデフォルトの動作です。

2: トランザクションがコミットされると、キャッシュに書き込まれますが、ファイルシステムからハードディスクへの同期操作はトリガーされません。また、ハードディスクは 1 秒に 1 回同期されます。

9. 先読みメカニズム

事前読み取りメカニズムは、主に、以前の MySQL 最適化で説明した原則を使用します: 1. キャッシュ最適化。つまり、地域特性、空間的局所性、時間的局所性ですが、ここでは詳しく説明しません。

1. InnoDBの先読みメカニズム

InnoDB は先読みメカニズムを使用して、インデックスを含む「将来アクセスされるデータ」を先読みキャッシュにロードし、データの読み取りパフォーマンスを向上させます。 InnoDB は、線形先読みとランダム先読みの 2 つの方法をサポートしています。

データブロック (ページ) は、InnoDB ハードディスク管理の最小単位です。ゾーンは 64 個の連続したデータブロックで構成されます。シーケンシャル先読みの場合、InnoDB は、データが配置されているデータブロックを InnoDB キャッシュプールに配置することを優先します。これらのデータブロックの後続のブロックはすぐにアクセスされると予測できるため、これらのデータブロックと先行するデータブロックはメモリに配置されます。 innodb_read_ahead_threshold パラメータに基づいて、先読みするデータブロックの数を設定します。

mysql> 'innodb_read_ahead%' のような変数を表示します。
+-----------------------------+-------+
| 変数名 | 値 |
+-----------------------------+-------+
| innodb_read_ahead_threshold | 56 |
+-----------------------------+-------+

2. インデックスキャッシュのプリロード

データベース管理者は、MySQL コマンド load index into cache を使用して、MyISAM テーブルインデックスを事前ロードできます。

10. MyISAMテーブルの遅延挿入

mysql> '%delayed%' のような変数を表示します。
+----------------------------+-------+
| 変数名 | 値 |
+----------------------------+-------+
| 遅延挿入制限 | 100 |
| 遅延挿入タイムアウト | 300 |
| 遅延キューサイズ | 1000 |
| 最大遅延スレッド数 | 20 |
| 最大挿入遅延スレッド数 | 20 |
+----------------------------+-------+

この遅延挿入機能を見て、プロジェクト内の似たような機能を思い出し、それが私自身のアイデアのきっかけとなりました。

使用方法は次のとおりです: insert delyed into table values(*);

挿入制限の遅延

デフォルト値は 100 です。MySQL テーブルに 100 行が挿入された後、テーブル内で実行を待機している SELECT ステートメントがあるかどうかを確認します。ある場合は、INSERT ステートメントの実行を一時停止します。

遅延挿入タイムアウト

タイムアウト範囲内で遅延キューにデータがない場合、遅延挿入スレッドはシャットダウンされます。

遅延キューサイズ

遅延挿入のキューの長さ。これを超えると、十分なスペースができるまでブロックされます。

最大遅延スレッド数

遅延挿入のスレッド数。

MyISAM テーブルへのバッチ遅延挿入

テーブルに値(1)、値(2)、値(n)を挿入するのと同様です。 MyISAM はバッチ挿入を実行します。挿入されたデータをまずキャッシュに格納します。キャッシュがいっぱいになるかコミットされると、MySQL はキャッシュをすぐにディスクに書き込みます。バッチ挿入を使用すると、MySQL クライアントとサーバー間の接続構文分析やその他のコストが大幅に削減され、個々の挿入ステートメントを個別に実行するよりも効率が大幅に向上します。

mysql> @@global.bulk_insert_buffer_size/(1024*1024) を選択します。
+------------------------------------------------+
| @@global.bulk_insert_buffer_size/(1024*1024) |
+------------------------------------------------+
| 8.0000 |
+------------------------------------------------+

デフォルトのバッチ挿入サイズは 8M です。ビジネス上の必要に応じて、この値を大きく設定してバッチ挿入のパフォーマンスを向上させることができます。

MyISAM テーブルのインデックスの遅延更新

インデックスを使用すると、データの取得が高速化されますが、更新の場合は、レコードを変更するだけでなく、インデックスも変更する必要がある場合があります。そのため、インデックスを使用すると、データの更新操作が遅くなります。MySQL delay_key_write パラメータを 1 (ON) に設定すると、この欠点を補うことができます。更新操作を有効にすると、データが変更されると、まずハードディスクにデータ更新が送信され、インデックスキャッシュ内でインデックス更新がすべて完了します。テーブルを閉じるときに、ハードディスクに一緒に更新することで、インデックスの更新が速くなります。 MyISAM のみ有効です。

mysql> 'delay_key_write' のような変数を表示します。
+-----------------+-------+
| 変数名 | 値 |
+-----------------+-------+
| delay_key_write | オン |
+-----------------+-------+

InnoDB 遅延更新

非クラスター化インデックスの更新操作は通常、ランダム I/O を引き起こし、InoDB のパフォーマンスを低下させます。非クラスター化インデックスのデータを更新 (挿入、削除、更新 = 挿入 + 削除) する場合、まず非クラスター化インデックスページが InnoDB バッファプールにあるかどうかを確認します。ある場合は、直接更新されます。そうでない場合は、まず「情報の変更」が更新バッファに記録されます。

このブログには多くのコンテンツが含まれているため、後で確認できるように要約します。まず、MySQL 全体を最適化するための全体的なフレームワークを用意し、段階的に進歩させる必要があります。これらのパラメータを覚えておく必要はありません。必要な場合は、ブログまたは Baidu で調べてください。 Taoを理解し、テクニックを知っていれば、最適化プロセスを完了できます。退屈な原理を暗記するよりも原理を知るほうがずっと簡単です。 MySQL の最適化に興味のあるブロガーは、私のブログをフォローして、今後の共有内容を確認することができます。

以下もご興味があるかもしれません: