MysqlチューニングExplainツールの詳細な説明と実践的な演習（推奨）

MySQL チューニング ツールの詳細な説明と実践的な演習の説明 ツールの紹介の説明 分析例の説明 2 つのバリアントの説明 列インデックスの説明 最も実践的なインデックスの使用法のまとめ:
MySQL インストールドキュメントリファレンス

ツールの紹介を説明する

EXPLAIN キーワードを使用して、オプティマイザーをシミュレートし、SQL ステートメントを実行して、クエリ ステートメントまたは構造のパフォーマンス ボトルネックを分析します。select ステートメントの前に explain キーワードを追加すると、MySQL はクエリにマークを設定します。クエリを実行すると、SQL ステートメントを実行する代わりに、実行プラン情報が返されます。
注意: from にサブクエリが含まれている場合、サブクエリは実行され、結果は一時テーブルに配置されます。

分析例を説明する

公式ドキュメントを参照してください

例の表:
 `actor` が存在する場合はテーブルを削除します。
 テーブル「アクター」を作成する（
 `id` int(11) NULLではない、
 `name` varchar(45) デフォルト NULL,
 `update_time` 日時 デフォルト NULL、
 主キー (`id`)
 )ENGINE=InnoDB デフォルト文字セット=utf8;
 
 `actor` (`id`, `name`, `update_time`) に VALUES (1,'a','2017-12-22) を挿入します
15:27:18')、(2、'b'、'2017-12-22 15:27:18')、(3、'c'、'2017-12-22 15:27:18');

`film` が存在する場合はテーブルを削除します。
 テーブル「film」を作成する（
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) デフォルト NULL,
 主キー (`id`)、
 キー `idx_name` (`name`)
 )ENGINE=InnoDB デフォルト文字セット=utf8;
 `film` (`id`, `name`) に VALUES (3,'film0'),(1,'film1'),(2,'film2') を挿入します。
`film_actor` が存在する場合はテーブルを削除します。
 テーブル「film_actor」を作成します（
 `id` int(11) NULLではない、
 `film_id` int(11) NULLではない、
 `actor_id` int(11) NULLではない、
 `remark` varchar(255) デフォルト NULL,
 主キー (`id`)、
 キー `idx_film_actor_id` (`film_id`,`actor_id`)
 )ENGINE=InnoDB デフォルト文字セット=utf8;
 `film_actor` (`id`, `film_id`, `actor_id`) に値 (1,1,1),(2,1,2),(3,2,1); を挿入します。

mysql> explain select * from actor; 

クエリ内のテーブルごとに 1 行が出力されます。結合を介して 2 つのテーブルがクエリされた場合は、2 行が出力されます。

2つのバリエーションを説明する

1) explain 拡張: explain に基づいて追加のクエリ最適化情報を提供します。次に、show warnings コマンドを使用して最適化されたクエリ ステートメントを取得し、オプティマイザーによって最適化された内容を確認できます。追加のフィルター列もあり、これは半分の比率の値です。行数 * フィルター/100 は、explain の前のテーブルと結合される行数を推定できます (前のテーブルとは、explain の ID 値が現在のテーブルの ID 値よりも小さいテーブルを指します)。

2)パーティションの説明: explain と比較すると、パーティション フィールドが追加されています。クエリがパーティション テーブルに基づいている場合、クエリがアクセスするパーティションが表示されます。

MySQL 5.7 以降の新しいバージョンでは、追加情報をクエリするために extend を実行する必要はありません。MySQL 8.0 以降では explain Extended コマンドは廃止されており、explain のみを使用する必要があります。

説明の列

次に各列の情報を説明します。

id列
id 列の番号は、選択のシリアル番号です。選択の数と同じ数の id があり、選択が出現する順に id の順序が増加します。 id 列が大きいほど、実行優先順位が高くなります。id が同じ場合は上から下に実行されます。id が NULL の場合は最後に実行されます。
クエリに結合クエリがある場合は、複数の ID が表示されます。たとえば、クエリの結果が 1、2、3 の場合、ID 3 の SQL が最初に実行されます。クエリされた ID が 1、1、2、3 で、両方の ID が 1 の場合、上記の SQL が最初に実行されます。

select_type列
select_type は、対応する行が単純なクエリか複雑なクエリかを示します。
1).simple: シンプルなクエリ。クエリにはサブクエリとユニオンが含まれていません

 mysql> explain select * from film where id = 2; 

2).primary: 複雑なクエリの最も外側の選択
3).subquery: SELECT に含まれるサブクエリ (from 句には含まれません)
4).derived: from 句に含まれるサブクエリ。 MySQL は結果を一時テーブル (派生テーブルとも呼ばれます) に保存します。
この例を使用して、プライマリ、サブクエリ、派生型を理解します。

mysql> set session optimizer_switch='derived_merge=off'; #MySQL 5.7 で派生テーブルのマージ最適化を無効にします。 2 explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der; 

上記の select_type によってクエリされたグラフについて説明します。
まず、ID 3 の SQL について説明します。これは from の後のサブクエリであるため最初に実行され、対応する select_type は derived になります。ID 2 の SQL は、select のサブクエリに含まれているため (from 句ではなく)、その後に実行されます。そのため、対応する select_type は subquery になります。
ID 1 の SQL が最後に実行されます。これは複合クエリの最も外側の選択であるため、対応する select_type は primary です。
最後に、以前に変更した構成を復元することを忘れないでください。

mysql> set session optimizer_switch='derived_merge=on'; #デフォルト設定を復元

5).union: ユニオン内の2番目以降の選択

mysql> explain select 1 union all select 1; 

3.表の列

この列は、explain の行がどのテーブルにアクセスしているかを示します。
from 句にサブクエリがある場合、テーブル列は の形式になります。これは、現在のクエリが id=N のクエリに依存していることを示すため、id=N のクエリが最初に実行されます。
結合がある場合、UNION RESULT のテーブル列の値は <union1,2> になります。ここで、1 と 2 は結合に参加する選択行 ID を表します。
deriven3 は、最初に ID 3 の SQL をクエリすることを意味します。つまり、現在のクエリは ID = 3 のクエリに依存します。

4. タイプ列（より重要）

この列は、関連付けタイプまたはアクセス タイプを示します。これは、MySQL がテーブル内の行を検索する方法と、検索するデータ行レコードのおおよその範囲を決定するものです。 最良から最悪まで、 system > const > eq_ref > ref > range > index > ALL の順です。
一般的に言えば、クエリが範囲レベル、できれば参照レベルに到達することを確認する必要があります。
NULL : MySQL は、実行フェーズでテーブルやインデックスにアクセスせずに、最適化フェーズでクエリ ステートメントを分解できます。たとえば、インデックス列の最小値を選択するには、実行中にテーブルにアクセスすることなく、インデックスのみを検索することで実行できます。

mysql> explain select min(id) from film; 

MySQL の基盤となるインデックス データ構造は B+ ツリーであり、これは前回の記事でも説明しましたので、ここで説明しておきます。 B+ ツリーの下部にあるリーフ ノードは、左から右に向かって増加する順序で配置されます。これが左端プレフィックスの原則です。したがって、最小値を照会するには、検索せずに左端のインデックスから直接取得できます。これは非常に効率的です。

const、system : MySQL はクエリの一部を最適化し、定数に変換できます (show warnings の結果を参照)。主キーまたは一意キーに使用されるすべての列が定数と比較されると、テーブルには最大で 1 つの一致する行があり、一度読み取られるため、高速になります。 System は const の特殊なケースです。テーブル内に一致するタプルが 1 つしかない場合は system です。

mysql> explain 拡張 select * from (select * from film where id = 1) tmp; 

mysql> 警告を表示します。 

eq_ref : 主キーまたは一意キー インデックスのすべての部分が結合して使用され、条件を満たすレコードが最大 1 つ返されます。これはおそらく const 以外では最適な結合タイプであり、単純な選択クエリにはこのタイプは含まれません。

film_actor から * を選択して、 film_actor.film_id = film.id で film_actor を結合します。 

説明すると、上記の film_actor の film_id フィールドは結合インデックスなので、セカンダリ インデックスに基づいて別のテーブルに対応するクラスター化インデックス クエリは非常に高速です。
ref : eq_ref と比較すると、ユニーク インデックスは使用されず、通常のインデックスまたはユニーク インデックスの部分プレフィックスが使用されます。インデックスは特定の値と比較され、複数の条件に該当する行が見つかる場合があります。
1. 単純な選択クエリ、名前は共通インデックス（非一意インデックス）

mysql> explain select * from film where name = 'film1'; 

2. 関連テーブルをクエリします。idx_film_actor_id は film_id と actor_id の結合インデックスです。ここでは film_actor の左プレフィックスが使用されます。

mysql> film_id を film から選択し、 film_actor を film.id = film_actor.film_id に結合します。 

range : 範囲スキャンは通常、in()、between、>、<、>= などの操作で使用されます。インデックスを使用して、指定された範囲の行を取得します。

mysql> explain select * from actor where id > 1; 

index : インデックス全体をスキャンすることで結果を取得できます。通常はセカンダリ インデックスをスキャンします。このスキャンは、高速検索のためにインデックス ツリーのルート ノードから開始されるのではなく、セカンダリ インデックスのリーフ ノードを直接トラバースしてスキャンします。速度は依然として比較的低速です。このクエリでは通常、カバー インデックスが使用されます。セカンダリ インデックスは一般に小さいため、通常は ALL よりも高速です。

 mysql> explain select * from film; 

セカンダリ インデックスは現在のインデックスのデータのみを格納しますが、クラスター化インデックスはすべてのテーブル データを格納するため、セカンダリ インデックスがクラスター化インデックスよりも小さくなる理由を説明します。
ALL : クラスター化インデックスのすべてのリーフ ノードをスキャンする完全なテーブル スキャン。通常、最適化するにはインデックスを追加する必要があります

5. possible_keys列

この列には、クエリが検索に使用する可能性のあるインデックスが表示されます。
説明時に、possible_keys に列があるのに、key が NULL と表示されることがあります。これは、テーブルにデータがあまりなく、MySQL がこのクエリではインデックスがあまり役に立たないと判断し、テーブル全体をクエリすることを選択したためです。
列が NULL の場合、関連付けられたインデックスはありません。この場合、where 句を調べて適切なインデックスを作成できるかどうかを確認し、explain を使用してその効果を表示することで、クエリのパフォーマンスを向上させることができます。

6.キー列

この列には、テーブルへのアクセスを最適化するために MySQL が実際に使用するインデックスが表示されます。
インデックスが使用されていない場合、この列は NULL になります。 MySQL で possible_keys 列のインデックスを強制的に使用または無視する場合は、クエリで force index または ignore index を使用します。

7. key_len列

この列には、MySQL がインデックスで使用するバイト数が表示されます。この値を使用して、インデックス内のどの列が使用されているかを計算することができます。 たとえば、film_actor の結合インデックス idx_film_actor_id は、film_id と actor_id の 2 つの int 列で構成され、各 int は 4 バイトです。結果の key_len=4 から、クエリは最初の列である film_id 列を使用してインデックス検索を実行していることが推測できます。

mysql> film_actor から film_id = 2 を選択して * を説明します。 

key_len の計算規則は次のとおりです。

• 文字列、char(n)、varchar(n)、バージョン5.0.3以降では、nはバイト数ではなく文字数を表します。UTF-8の場合は、
• または、文字は1バイトを占め、漢字は3バイトを占めます。char(n): 漢字が格納されている場合、長さは3nバイトです。
• varchar(n): 中国語の文字を格納する場合、長さは 3n + 2 バイトです。varchar は可変長文字列であるため、追加の 2 バイトは文字列の長さを格納するために使用されます。
• 値の型 tinyint: 1 バイト
• smallint: 2 バイト
• int: 4バイト
• bigint: 8 バイト
• 時間タイプ
• 日付: 3 バイト
• タイムスタンプ: 4 バイト
• 日時: 8 バイト
• フィールドがNULLを許可する場合、NULLであるかどうかを記録するために1バイトが必要です。
• インデックスの最大長は 768 バイトです。文字列が長すぎる場合、MySQL は左プレフィックス インデックスと同様の処理を実行し、文字の前半を抽出してインデックスを作成します。

8.参照列

この列には、キー列レコードのインデックスでテーブル内の値を見つけるために使用される列または定数が表示されます。一般的なものは次のとおりです: const (定数)、フィールド名 (例: film.id)

9.行

この列は、MySQL が読み取りおよび検出すると推定する行数です。これは結果セット内の行数ではないことに注意してください。

10追加列

この列には追加情報が表示されます。共通の重要な値は次のとおりです。
1)インデックスの使用: カバーインデックスの使用
カバーリングインデックスの定義: MySQL実行計画のexplain結果のキーにusingインデックスがあり、select後にクエリされたフィールドがこのインデックスのツリーから取得できる場合、この状況は一般的にカバーリングインデックスを使用していると言えます。また、extraにもusingインデックスが存在します。カバーリングインデックスは一般的に補助インデックスを対象としており、補助インデックスツリーから主キーを見つけなくても、補助インデックスを通じてのみクエリ結果全体を取得でき、主キーを使用して主キーインデックスツリーから他のフィールド値を取得できます。
簡単に言えば、テーブルに戻る必要はありません。セカンダリ インデックス、つまり結合インデックスを通じて、目的の結果セットを取得できます。

mysql> film_id = 1 の場合、film_actor から film_id を選択します。 

2) where句の使用: where句を使用して結果を処理し、クエリされた列はインデックスでカバーされません。

mysql> explain select * from actor where name = 'a'; 

ここでは、アクター テーブルの名前にインデックスは追加されません。
3)インデックス条件の使用: クエリ対象の列はインデックスによって完全にはカバーされておらず、where 条件は先頭の列の範囲です。
4)一時テーブルの使用: MySQL はクエリを処理するために一時テーブルを作成する必要があります。このような場合、通常は最適化が必要となり、まず思い浮かぶのはインデックスを使用して最適化することです。

実際はもっとたくさんあるので、一つ一つ紹介することはしません。興味がある方は、MySQLの公式ドキュメントを自分で確認してみてください。

インデックス作成のベストプラクティス

例の表:
テーブル「従業員」を作成します（
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '名前',
 `age` int(11) NOT NULL デフォルト '0' コメント '年齢',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '位置',
 `hire_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '求人開始時間',
 主キー (`id`)、
 キー `idx_name_age_position` (`name`,`age`,`position`) BTREE の使用
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='従業員記録テーブル';

 従業員にINSERT INTO(名前、年齢、役職、雇用時間) VALUES('LiLei'、22、'マネージャー'、NOW());
 従業員にINSERT INTO(名前、年齢、役職、雇用時間) VALUES('HanMeimei'、23、'dev'、NOW());
 従業員にINSERT INTO(名前、年齢、役職、雇用時間) VALUES('Lucy'、23、'dev'、NOW());

完全な価値一致

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei'; 

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22; 

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22 AND position = 'manager'; 

2.左端プレフィックス ルール<br /> 複数の列にインデックスが付けられる場合は、左端プレフィックス ルールに従う必要があります。これは、クエリがインデックスの左端の列から開始され、インデックス内の列をスキップしないことを意味します。

 EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;
 EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
 EXPLAIN SELECT * FROM employees WHERE position = 'manager'; 

上記には 3 つの結果セットがあります。最初の SQL ステートメントのみが左端プレフィックスの原則に従い、クエリにインデックスを使用します。他の 2 つの SQL ステートメントは左端プレフィックスの原則に違反しています。つまり、クエリは名前フィールドから開始されないため、インデックスは使用されず、インデックス エラーが発生します。
3.インデックス列に対して、いかなる操作（計算、関数、（自動または手動の）型変換）も実行しないでください。インデックスが失敗し、テーブル全体のスキャンに切り替わります。

 EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei'; 

最初の SQL はインデックスを使用し、2 番目の SQL はインデックスの失敗を引き起こします。
4.ストレージエンジンは、インデックスの範囲条件の右側の列を使用できません。

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22 AND position = 'manager';
2 EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age > 22 AND position = 'manager'; 

varchar(n): 中国語の文字を格納する場合、長さは 3n + 2 バイトです。varchar は可変長文字列であるため、追加の 2 バイトは文字列の長さを格納するために使用されます。
名前フィールドは3*24+2=74バイトを占める
age フィールドは Int であり、4 バイトを占めます。
74+4=78
これにより、後続の位置インデックスが失敗します。
5.カバーインデックス（インデックスのみにアクセスするクエリ（インデックス列にクエリ列が含まれる））を使用して、select *ステートメントを減らすようにします。

EXPLAIN SELECT name,age FROM employees WHERE name = 'LiLei' AND age = 23 AND position = 'manager'; 

6. MySQLは、等しくない（!=または<>）、含まれない、存在しない場合、インデックスを使用できないため、テーブル全体のスキャンが発生します。
< 未満、> より大きい、<=、>= など。MySQL 内部オプティマイザは、検索率やテーブル サイズなどの複数の要素に基づいてインデックスを使用するかどうかを評価します。

null、nullではない 一般的に、インデックスは使用できません

EXPLAIN SELECT * FROM employees WHERE name is null

ワイルドカード文字 ('$abc...') で始まる場合、MySQL インデックスが無効になり、完全なテーブル スキャン操作が発生します。質問: '% string %' インデックスが使用されない問題を解決するにはどうすればよいですか?
a) カバーインデックスを使用する場合、クエリフィールドはカバーインデックスが作成されるフィールドである必要があります。

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

b) カバーインデックスが使用できない場合は、検索エンジンを使用する必要があるかもしれません。
9.一重引用符なしでは文字列のインデックス作成が失敗する

EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;

または は控えめに使用してください。クエリに使用した場合、MySQL はインデックスを使用しない場合があります。MySQL の内部オプティマイザは、検索率やテーブル サイズなどの複数の要因に基づいてインデックスを使用するかどうかを評価します。詳細については、「範囲クエリの最適化範囲クエリの最適化Age への単一値インデックスの追加」を参照してください。

 ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE; 
age >=1 かつ age <=2000 の employees から * を選択することを説明します。 

インデックスを使用しない理由: MySQL の内部オプティマイザは、検索率やテーブル サイズなどの複数の要素に基づいて、インデックスを使用するかどうかを評価します。たとえば、この例では、単一のデータ クエリが大きすぎるため、オプティマイザーはインデックス最適化方法を使用しないことを選択する場合があります。大きな範囲は複数の小さな範囲に分割できます。

 age >=1 かつ age <=1000 の employees から * を選択することを説明します。
 年齢 >=1001 かつ年齢 <=2000 の従業員から * を選択します。

元のインデックス状態を復元する

テーブル `employees` を変更し、インデックス `idx_age` を削除します。

インデックスの使用の概要:

MySQLチューニングExplainツールの詳しい説明と実践的な演習についてはこれで終わりです。MySQLチューニングExplainツールの関連コンテンツについては、123WORDPRESS.COMの過去の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも123WORDPRESS.COMをよろしくお願いいたします。