MySQLのexplain型の詳細な説明

導入：

多くの場合、さまざまな選択ステートメントを使用して必要なデータを照会した後、多くの人は作業が正常に完了したと考えます。
こうしたことは、職場に入ったばかりでデータベースの基礎が十分に身についていない学生や新人によく起こります。しかし、諺にあるように、学ぶ時期は人それぞれです。私たち新人が一生懸命勉強し、毎日進歩している限り、それはまだ非常に信頼できます。

SQL クエリ ステートメントを記述した時点では、作業はまだ半分しか終わっていません。次に重要なタスクは、記述した SQL の品質と効率を評価することです。 MySQL は、MySQL が受信した SQL ステートメントの実行プランを表示する非常に便利な補助ツールである explain を提供しています。 explain によって返される結果に基づいて、SQL がどのように記述されているか、またそれがクエリのボトルネックを引き起こすかどうかを知ることができます。同時に、結果に基づいてクエリ ステートメントを継続的に変更および調整して、SQL 最適化プロセスを完了することができます。

explain は多くの結果項目を返しますが、ここでは type、key、rows の 3 つのタイプにのみ焦点を当てます。 key はこの検索で​​使用されるインデックスを示し、rows はこの検索で​​スキャンされた行数を指します (最初はこのように理解できますが、実際には内部ループの数です)。タイプは、この記事で詳細に記録する接続タイプです。最初の 2 つの項目は重要かつ単純なので、これ以上説明する必要はありません。

タイプ -- 接続タイプ

タイプはタイプを意味します。ここでのタイプの正式なフルネームは「join type」で、「結合タイプ」を意味します。これにより、結合タイプには 2 つ以上のテーブルが必要であるという誤解が容易に生じます。実際、ここでの接続タイプは、その名前が示すほど狭い範囲ではありません。より正確には、データベース エンジンがテーブルを検索する方法です。書籍「High Performance MySQL」では、著者はこれをアクセス タイプと呼ぶ方が適切であると考えています。

MySQL 5.7 には 14 種類もの型があります。ここでは、最も重要で頻繁に使用される 6 つの型、つまり all、index、range、ref、eq_ref、const についてのみ記録し、説明します。左から右に行くほど効率が上がります。 SQL の具体的な適用環境やその他の要因に関係なく、SQL ステートメントの型が可能な限り正しいものになるように最適化する必要がありますが、実際の適用では、すべての側面を総合的に考慮する必要があります。

次に、これらの接続タイプを実証して再現するために、これら 5 つのタイプをよりよく理解するための新しいデータ テスト テーブルを作成しました。

| 従業員 | テーブル `従業員` を作成する (
 `rec_id` int(11) NOT NULL AUTO_INCREMENT,
 `no` varchar(10) NOT NULL,
 `name` varchar(20) NOT NULL,
 `位置` varchar(20) NOT NULL,
 `age` varchar(2) NOT NULL,
 主キー (`rec_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 デフォルト CHARSET=utf8 |

全て

これはいわゆる「フル テーブル スキャン」です。データ テーブル内のすべてのデータ項目を表示する場合は問題ありません。ただし、データ項目を検索する SQL ステートメントに all タイプが表示される場合、通常、SQL ステートメントは最もネイティブな状態にあり、最適化の余地がたくさんあることを意味します。
なぜそう言うのでしょうか?これらはすべて非常に乱暴かつ原始的な検索方法であるため、非常に時間がかかり、非効率的です。すべてを使ってデータを検索すると、次のようになります。S 学校には 20,000 人がいて、Xiao Ming を探すように指示されたら、どうしますか?もちろん、学校にいる2万人全員を一人ずつ捜索します。運が良ければ最初の人物としてシャオミンを見つけたとしても、2万人全員を捜索するまではもう一人のシャオミンがいるかどうか確認できないため、まだ止めることはできません。したがって、ほとんどの場合、必要がない限り、このタイプの検索は避けるべきです。
従業員テーブルを例にとると、次のシナリオはすべてのタイプの検索になります。

mysql> explain select * from employee where `no` = '20150001';
+----+-------------+----------+-------+---------------+-------+-------+------+-------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+----------+-------+---------------+-------+-------+------+-------------+
| 1 | SIMPLE | 従業員 | ALL | NULL | NULL | NULL | NULL | 5 | where の使用 |
+----+-------------+----------+-------+---------------+-------+-------+------+-------------+

これは、番号列が主キーでもインデックスでもないため、ターゲット番号を見つけるには完全なテーブルスキャンしか使用できないためです。

索引

このタイプの結合は、スキャン順序がインデックスの順序になることを除いて、完全なテーブルスキャンの別の形式です。このタイプのスキャンは、インデックスに基づいてテーブルからデータを取得します。「all」と比較すると、どちらもテーブル全体のデータを取得し、インデックスは最初にインデックスを読み取り、次にテーブルからランダムにデータを取得する必要があります。したがって、「index」は「all」（同じテーブルデータを取得）よりも高速になることはできません。しかし、公式マニュアルではなぜ「all」よりも効率が良いと記載されているのでしょうか。唯一の考えられる理由は、インデックスに従ってスキャンすると、テーブル全体のデータが順序付けされるためです。このように、結果は異なり、効率を比較しても意味がありません。
本当に効率を比較したい場合は、この表のデータを取得して並べ替えるだけで、どちらがより効率的かがわかります。

mysql> explain select * from employee order by `no`;
+----+-------------+----------+-------+---------------+-------+-------+------+----------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+----------+-------+---------------+-------+-------+------+----------------+
| 1 | SIMPLE | 従業員 | ALL | NULL | NULL | NULL | NULL | 5 | ファイルソートの使用 |
+----+-------------+----------+-------+---------------+-------+-------+------+----------------+
mysql> explain select * from employee order by rec_id;
+----+-------------+----------+--------+---------------+----------+-------+------+------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+----------+--------+---------------+----------+-------+------+------+
| 1 | SIMPLE | 従業員 | インデックス | NULL | PRIMARY | 4 | NULL | 5 | NULL |
+----+-------------+----------+--------+---------------+----------+-------+------+------+

上記のように、列のソートに基づく結合タイプは all タイプですが、追加の列はソートされている (filesort を使用) のに対し、rec_id 列のソートに基づく結合タイプは index であり、結果は追加のソートなしで自然に順序付けられていることに注意してください。おそらく、インデックスが他の方法よりも効率的であるのはそのためですが、これには同じ条件 (つまり、ソートが必要) が必要であることに注意してください。

接続タイプが type で、追加列の値が「インデックスを使用」の場合、この状況はインデックス カバーリングと呼ばれます。
インデックスカバーとはどういう意味ですか?次のようなシナリオを想像してください。新華語辞書がテーブルである場合、もちろん前のインデックス部分 (インデックスが部首に基づいていると仮定) はこのテーブルのインデックスであり、インデックス カバレッジは、部首インデックスに基づいて最初から最後の文字 (新華語辞書内のすべての文字) を取得することと同等です。辞書内のすべての単語を取得しますが、必要な単語はすべてインデックス、つまりインデックス カバーに既に含まれているため、テーブルを一度検索する必要はありません。

mysql> 従業員から rec_id を選択して説明します。
+----+-------------+----------+--------+---------------+----------+-------+------+-------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+----------+--------+---------------+----------+-------+------+-------------+
| 1 | SIMPLE | 従業員 | インデックス | NULL | PRIMARY | 4 | NULL | 5 | インデックスの使用 |
+----+-------------+----------+--------+---------------+----------+-------+------+-------------+

上記の例では、取得された rec_id はインデックス列であるため、データを取得するためにテーブルに戻る必要はありません。

範囲

範囲は範囲指定のインデックス スキャンを指します。インデックスの完全なインデックス スキャンと比較すると、範囲制限があるため、インデックスよりも優れています。範囲は比較的理解しやすいです。覚えておく必要があるのは、範囲が表示されるときは、インデックスに基づいている必要があるということです。明らかな between、and、'>'、'<' に加えて、in および or もインデックス範囲スキャンです。

参照

この接続タイプが発生する条件は次のとおりです: 検索条件列はインデックスを使用し、主キーでも一意でもありません。実際には、インデックスが使用されているものの、インデックス列の値が一意ではなく、重複があることを意味します。この方法では、インデックスを使用して最初のデータがすぐに見つかったとしても、プロセスを停止することはできず、ターゲット値付近の小さな範囲のスキャンを実行する必要があります。しかし、その利点は、インデックスが順序付けられているためテーブル全体をスキャンする必要がなく、重複する値があっても非常に狭い範囲でスキャンされることです。この状況を示すために、従業員テーブルの名前列に共通キーを追加します (重複値は許可されます)。

 従業員テーブルを変更し、キー I_EMPLOYEE_NAME(`name`) を追加します。

次に、従業員テーブルで名前でデータを検索するときに、MySQL オプティマイザーは ref 接続タイプを選択します。

mysql> explain select * from employee where `name` = '张三';
+----+-------------+----------+-------+--------------------------------+---------+-------+-----------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+----------+-------+--------------------------------+---------+-------+-----------------------+
| 1 | SIMPLE | 従業員 | ref | I_EMPLOYEE_NAM | I_EMPLOYEE_NAM | 62 | const | 1 | インデックス条件の使用 |
+----+-------------+----------+-------+--------------------------------+---------+-------+-----------------------+

参照

ref と比較して ref_eq が優れている点は、このタイプの検索には結果セットが 1 つしかないことを認識していることです。どのような状況で結果セットが 1 つだけになるのでしょうか?それは、主キーまたは一意のインデックスが検索に使用される場合です。たとえば、学生 ID 番号に基づいて特定の学校の学生を検索する場合、検索前に結果は 1 つだけであることがわかっているため、学生 ID 番号が初めて見つかったときにすぐに検索を停止します。このタイプの接続では、過剰なスキャンを行わずに毎回正確なクエリを実行するため、検索効率が高くなります。もちろん、列の一意性は実際の状況に基づいて判断する必要があります。
単一テーブルで、ref_eq の接続タイプを取得するために多くの方法を試しましたが、ほとんどの場合 const であったため、目的の接続タイプを取得するにはテーブルを接続する必要がありました。このテーブルのテーブル作成コードは次のとおりです。 （ブロガーは怠け者で、無関係なテーブルを2つつなげてしまいました、o(╯□╰)o）

テーブル「スコア」を作成します（
 `rec_id` INT(11) NOT NULL AUTO_INCREMENT,
 `stu_id` INT(11) NULLではない、
 `mark` INT(11) NOT NULL デフォルト '0',
 主キー (`rec_id`)、
 ユニークキー `UK_SCORE_STU_ID` (`stu_id`)
) エンジン=INNODB AUTO_INCREMENT=6 デフォルト文字セット=utf8

従業員テーブルには 5 つのレコードがあり、スコア テーブルにはそれに対応する 5 つのレコードがあります。従業員の rec_id とスコアの stu_id は 1 対 1 で対応しています。

mysql> explain select ep.name,sc.mark from employee ep,score sc where ep.rec_id = sc.stu_id;
+----+-------------+-------+--------+-----------------+----------+--------+-----------------+-------+-------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+--------+-----------------+----------+--------+-----------------+-------+-------+
| 1 | シンプル | sc | すべて | UK_SCORE_STU_ID | NULL | NULL | NULL | 5 | NULL |
| 1 | シンプル | ep | eq_ref | プライマリ | プライマリ | 4 | my_db.sc.stu_id | 1 | NULL |
+----+-------------+-------+--------+-----------------+----------+--------+-----------------+-------+-------+

上記から、スコア テーブルはフル テーブル スキャン タイプであることがわかります。行数 = 5 は、外部テーブルが 5 回ループされることを意味します (データが 5 つあるため)。しかし、従業員テーブルの行数が 1 なのはなぜでしょうか。どうしてそうなるのでしょうか。私も最初はとても混乱しました。これは MySQL のクエリ原理と密接に関係しています。行数は実際にはクエリの内部ループの数を反映しています。外側のレイヤーでデータが一致するたびに、従業員は 1 回のヒットでヒットするため、行数は 1 です。

定数

通常、条件クエリとして where 句の後に主キーを配置すると、MySQL オプティマイザはクエリを定数に最適化できます。変換方法とタイミングはオプティマイザーによって異なります。

要約する

Explain は鏡のようなものです。SQL を記述した後は必ず Explain するようにしてください。同時に、記事を書いているときに、明確に理解することがそれほど簡単ではない多くの事柄や詳細があることもわかりました。オペレーティングシステムとデータベースの基本的なクエリと操作の原則を明確に理解する必要があります。同時に、いくつかのタイプの型はほぼすべてインデックスに基づいているため、インデックスを深く理解する必要があります。explain の結果は、インデックスを追加するタイミングと追加しないタイミングをガイドし、インデックスをより有効に活用できるようにします。

以上がMySQLのexplainの種類の詳しい説明です。MySQLのexplainの種類についてさらに詳しく知りたい方は、123WORDPRESS.COMの他の関連記事もぜひご覧ください！