MySQL シリーズ 9 MySQL クエリキャッシュとインデックス

チュートリアルシリーズ

MySQL シリーズ: MySQL リレーショナルデータベースの基本概念
MySQLシリーズのMariaDBサーバーのインストール
MySQL シリーズ II マルチインスタンス構成
MySQL シリーズ 3 基礎
MySQL シリーズ 4 SQL 構文
MySQLシリーズ5つのビュー、ストアド関数、ストアドプロシージャ、トリガー
MySQL シリーズ 6 のユーザーと認証
MySQL シリーズ 7 MySQL ストレージエンジン
MySQL シリーズ 8 MySQL サーバー変数
MySQL シリーズ 10 同時実行制御を実装するための MySQL トランザクション分離
MySQL シリーズ 11 ログ
MySQL シリーズ 12 バックアップとリカバリ
MySQL シリーズ 13 MySQL レプリケーション
MySQL シリーズ 14 MySQL 高可用性実装
MySQLシリーズ15 MySQL共通設定とパフォーマンスストレステスト

1. MySQL アーキテクチャ

コネクタ
接続プール、セキュリティ認証、スレッドプール、接続制限、メモリチェック、キャッシュ
SQL インターフェース DML、DDL
SQL パーサーは、SQL ステートメントの権限をチェックし、バイナリプログラムに解析します。
オプティマイザー、アクセスパスの最適化
キャッシュ、バッファ
ストレージエンジン innodb
ファイルシステム
ログ

クエリキャッシュ

SQL ステートメント
クエリキャッシュ
パーサー
解析ツリー
前処理
最適なクエリパスを見つける
クエリ最適化SQL文
実行計画
ストレージエンジンへのAPI呼び出し
データを呼び出して結果を返す

SELECT 操作または前処理されたクエリの結果セットと SQL 文をキャッシュします。新しい SELECT 文または前処理されたクエリ文の要求があった場合、最初にキャッシュを照会して、使用可能なレコードセットがあるかどうかを判断します。判断基準は、キャッシュされた SQL 文とまったく同じかどうか (大文字と小文字が区別されます) です。

SQL ステートメントを解析して実行する必要はありません。もちろん、最初に構文解析を行う必要があります。クエリキャッシュからクエリ結果を直接取得して、クエリパフォーマンスを向上させます。

クエリキャッシュの判断ルールは十分にスマートではないため、クエリキャッシュを使用するしきい値が高くなり、効率が低下します。クエリキャッシュを使用すると、クエリキャッシュ内のレコードセットのチェックとクリーンアップのコストが増加します。

キャッシュされないクエリ:

SQL_NO_CACHE パラメータがクエリステートメントに追加されます。
クエリステートメントには、NOW()、CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ() などのカスタム関数を含む、値を取得する関数が含まれています。
システムデータベースをクエリします: mysql、information_schema クエリステートメントのストアドプロシージャで SESSION レベルの変数またはローカル変数を使用します。
クエリステートメントは LOCK IN SHARE MODE および FOR UPDATE ステートメントを使用し、クエリステートメントはデータのエクスポート用の SELECT ...INTO ステートメントに似ています。
一時テーブルに対するクエリ操作、警告情報を含むクエリステートメント、テーブルまたはビューを含まないクエリステートメント、ユーザーが列レベルの権限のみを持つクエリステートメント。
トランザクション分離レベルが Serializable の場合、すべてのクエリステートメントをキャッシュすることはできません。

クエリキャッシュ関連のサーバー変数:

query_cache_min_res_unit: クエリキャッシュ内のメモリブロックの最小割り当て単位。デフォルト値は 4k です。値が小さいほど無駄は減りますが、メモリ割り当て操作の頻度が高くなります。値が大きいほど無駄が増え、断片化が過剰になり、メモリが不足します。
query_cache_limit: 単一のクエリ結果に対してキャッシュできる最大値。デフォルト値は 1M です。クエリ結果が大きすぎてキャッシュできないステートメントの場合は、SQL_NO_CACHE を使用することをお勧めします。
query_cache_size: クエリキャッシュに使用可能なメモリ領域の合計。単位: バイト。1024 の整数倍でなければなりません。最小値: 40 KB。これより小さいとアラームがトリガーされます。
query_cache_wlock_invalidate: テーブルが他のセッションによってロックされている場合でも、クエリキャッシュは結果を返すことができますか? デフォルト値は OFF です。これは、テーブルが他のセッションによってロックされている場合でも、キャッシュからデータを返し続けることができることを意味します。ON は、それが許可されないことを意味します。
query_cache_type: キャッシュ機能を有効にするかどうか。値はON、OFF、DEMANDで、デフォルト値はONです。
- 値が OFF または 0 の場合、クエリキャッシュ機能は無効になります。
- 値が ON または 1 の場合、クエリキャッシュ機能がオンになります。SELECT 結果は、キャッシュ条件を満たす場合にキャッシュされます。それ以外の場合はキャッシュされません。SQL_NO_CACHE が明示的に指定されている場合は、キャッシュされません。
- 値が DEMAND または 2 の場合、クエリキャッシュは要求に応じて実行され、SQL_CACHE を明示的に指定する SELECT ステートメントのみがキャッシュされ、その他はキャッシュされません。

MariaDB [(なし)]> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| 変数名 | 値 |
+------------------------------+----------+
| クエリキャッシュ制限 | 1048576 |
| クエリ キャッシュの最小解像度単位 | 4096 |
| クエリキャッシュサイズ | 33554432 |
| query_cache_strip_comments | オフ |
| クエリキャッシュタイプ | オン |
| query_cache_wlock_invalidate | オフ |
+------------------------------+----------+

クエリキャッシュを最適化します。

クエリキャッシュ関連のステータス変数:

Qcache_free_blocks: クエリキャッシュ内のアイドル状態にあるメモリブロックの数。
Qcache_free_memory: アイドル状態のクエリキャッシュメモリの合計量。
Qcache_hits: クエリキャッシュのヒット数。
Qcache_inserts: 新しいクエリキャッシュがクエリキャッシュに挿入された回数、つまりヒットがなかった回数。
Qcache_lowmem_prunes: クエリキャッシュのメモリ容量が不足している場合、新しいキャッシュオブジェクト用のスペースを確保するために古いクエリキャッシュを削除する必要がある回数。
Qcache_not_cached: キャッシュできない SQL ステートメントと query_cache_type 設定によりキャッシュされない SQL ステートメントを含む、キャッシュされていない SQL ステートメントの数。
Qcache_queries_in_cache: クエリキャッシュ内の SQL の数。
Qcache_total_blocks: クエリキャッシュ内のブロックの合計数。

MariaDB [(なし)]> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| 変数名 | 値 |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33536824 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 4 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+

ヒット率とメモリ使用量の推定:

クエリキャッシュ内のメモリブロックの最小割り当て単位 query_cache_min_res_unit: (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
クエリキャッシュヒット率: Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
クエリキャッシュメモリ使用量: (query_cache_size – qcache_free_memory) / query_cache_size * 100%

3. 索引

インデックスは特別なデータ構造です。検索時に検索条件として使用されるフィールドを定義し、インデックスはストレージエンジンに実装されます。

インデックスを使用すると、サービスがスキャンする必要があるデータの量を削減し、IO の数を減らすことができます。インデックスを使用すると、サーバーはソートや一時テーブルの使用を回避できます。インデックスを使用すると、ランダム I/O をシーケンシャル I/O に変換できます。
しかし、余分なスペースを占有し、挿入速度に影響します

1. インデックスタイプ:

B+ツリーインデックス: シーケンシャルストレージ、各リーフノードからルートまでの距離は同じ、左プレフィックスインデックス、範囲データのクエリに適しています。
- Bツリーインデックスの使用に適したクエリタイプ
- 完全な価値マッチ
- 左端の接頭辞に一致
- 一致範囲の値
- 1 つの列で完全一致、別の列で範囲一致 (複合インデックス)
- インデックスのみにアクセスするクエリ
- Bツリーインデックスの使用に適さないクエリタイプ
- 左端の列から開始しない
- インデックス内の列をスキップできません
- クエリ内の列が範囲クエリである場合、右側の列はインデックスを使用してクエリを最適化できなくなります。
ハッシュインデックス: ハッシュテーブルに基づいて、キーと値のペアのインデックスを構築します。完全一致インデックスのインデックス列に特に適しています。等価比較クエリ (IN、=、<>) のみをサポートします。シーケンシャルクエリには適しておらず、あいまい一致もサポートしていません。明示的なハッシュインデックスをサポートするのは、メモリストレージエンジンのみです。
空間インデックス（R-Tree）：MyISAMのみが空間インデックスをサポートします
全文インデックス: テキスト内のキーワードを検索

2. 高性能インデックス戦略:

列を独立して使用し、できるだけ計算に含めないようにしてください。
左プレフィックスインデックスを使用する: インデックス付けするフィールドの左側の文字数は、インデックス選択性によって評価されます。インデックス選択性: データテーブル内のレコードの総数に対する非重複インデックス値の比率
複数列インデックス: AND 演算では、各列に個別のインデックスを作成するよりも、複数列インデックスを使用する方が適切です。
適切なインデックス列の順序を選択します。並べ替えやグループ化が行われていない場合は、選択性が最も高い列を左側に配置します。

3. インデックス最適化の提案

列に NULL 値が含まれている限り、この場合はインデックスを設定しないことをお勧めします。複合インデックスに NULL 値が含まれている場合、インデックスが使用されるときにこの列は使用されません。
短いインデックスを使用し、可能であればプレフィックスの長さを指定してください。
where句でよく使用される列には、インデックスを設定するのが最適です。
複数の列を持つwhere句またはorder by句の場合は、複合インデックスを作成する必要があります。
類似ステートメントの場合、% または '-' で始まるとインデックスは使用されませんが、% で終わるとインデックスが使用されます。
列に対して演算（関数演算や式演算）を実行しないようにしてください。
not inや<>演算は使わないようにしましょう
複数のテーブルを結合する場合は、小さなテーブルを使用して大きなテーブルを駆動する、つまり小さなテーブルで大きなテーブルを結合するようにしてください。
1000万レベルでページングする場合の使用制限
頻繁に使用されるクエリについては、キャッシュを有効にすることができます
ほとんどの場合、結合はサブクエリよりもはるかに効率的です。

4. インデックスの作成と削除

インデックスを作成

CREATE INDEX index_name ON tbl_name (index_col_name,...);

MariaDB [hellodb]> CREATE INDEX index_name ON students(name); #シンプルなインデックスを作成する
MariaDB [hellodb]> CREATE INDEX index_name_age ON students(name,age); #複合インデックスを作成する

インデックスを表示

[db_name.]tbl_name からインデックスを表示します。

MariaDB [hellodb]> 学生からのインデックスを表示\G

インデックスの削除

DROP INDEX index_name ON tbl_name;

MariaDB [hellodb]> DROP INDEX index_name ON students;

表領域の最適化

MariaDB [hellodb]> OPTIMIZE TABLE students;

インデックスの使用状況を表示する

インデックスの使用状況のログ記録を有効にする: SET GLOBAL userstat=1;

インデックスの使用状況を表示します: SHOW INDEX_STATISTICS;

あまり使用されないインデックスを最適化のためにカウントできる

4. EXPLAINコマンド

EXPLAIN: EXPLAIN SELECT句を使用してインデックスの有効性を分析し、クエリ実行プラン情報を取得し、クエリオプティマイザがクエリを実行する方法を確認します。

MariaDB [hellodb]> EXPLAIN SELECT name FROM students WHERE name = 'Lin Daiyu'\G
************************** 1. 行 ****************************
           id: 1
  選択タイプ: シンプル
        表: 学生
         タイプ: ref
可能なキー: インデックス名年齢
          キー: index_name_age
      キーの長さ: 152
          参照: 定数
         行数: 1
        追加: where の使用; index の使用

id: 現在のクエリステートメント内の各 SELECT ステートメントの番号。複雑なクエリには、単純なサブクエリ、FROM 句で使用されるサブクエリ、およびユニオンクエリ (UNION、注: UNION クエリの分析結果には追加の匿名一時テーブルが表示されます) の 3 種類があります。
選択タイプ:
- SIMPLE: シンプルなクエリ
- サブクエリ: 単純なサブクエリ
- PRIMARY: 最も外側のSELECT
- DERIVED: FROM内のサブクエリに使用
- UNION: 最初のUNION文の後のSELECT文
- UNION RESULT: 匿名一時テーブル
テーブル: SELECT 文が関連付けられているテーブル
タイプ: 関連タイプまたはアクセスタイプ。MySQLがテーブル内の行をクエリする方法を決定します。次の順序で、パフォーマンスは低い順から高い順です。
- ALL: テーブル全体のスキャン
- インデックス: インデックスの順序に従って完全なテーブルスキャンを実行します。Extra 列に「インデックスを使用」と表示される場合は、完全なテーブルスキャンの代わりにカバーインデックスが使用されることを意味します。
- 範囲: 範囲スキャンは、範囲が限定されたインデックスに基づいて実行されます。スキャン位置は、インデックス内のあるポイントから始まり、別のポイントで終了します。
- ref: インデックスに従って単一の値に一致するテーブル内のすべての行を返します
- eq_ref: 1行のみを返しますが、参照値との追加の比較が必要です
- const、system: 1行を直接返す
possible_keys: クエリに使用できるインデックス
キー: クエリで使用されるインデックス
key_len: インデックスで使用されるバイト数
ref: キーフィールドで表されるインデックスを使用してクエリを完了するために使用される列または定数値
行数: MySQL がすべての対象行を見つけるために読み取る必要があると見積もった行数
追加情報
- インデックスの使用: MySQLはテーブルへのアクセスを避けるためにカバーインデックスを使用します
- where の使用: ストレージエンジンがフィルターを取得した後、MySQL サーバーは別のフィルターを実行します。
- 一時テーブルの使用: MySQLは結果をソートするときに一時テーブルを使用します
- ファイルソートの使用: 外部インデックスを使用して結果をソートする

5. SQL文のパフォーマンス最適化

クエリを実行するときは、可能な限り*の使用を避け、フィールド名全体を記述するようにしてください。
ほとんどの場合、結合はサブクエリよりもはるかに効率的です。
複数のテーブルを結合する場合は、小さなテーブルを使用して大きなテーブルを駆動する、つまり小さなテーブルで大きなテーブルを結合するようにしてください。
1000万レベルでページングする場合の使用制限
頻繁に使用されるクエリについては、キャッシュを有効にすることができます
Explain と Profile を使用してクエリステートメントを分析する
実行時間が長いSQL文の最適化を確認するには、スロークエリログを表示します。

これで、MySQL シリーズ 9: MySQL クエリキャッシュとインデックスに関するこの記事は終了です。MySQL クエリキャッシュとインデックスの詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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