Mysqlの中のexplayinの役割の詳細解

9847 ワード

一、MYSQLのインデックス
インデックス（Index）：Mysqlが効率よくデータを取得するのを助けるデータ構造。検索効率を高めるために、辞書にたとえられます。並べ替えられた高速検索のデータ構造として簡単に理解できる。
インデックスの役割：検索と並べ替えに便利です。したがって、インデックスを追加すると、where文とorder by並べ替え文に影響を及ぼします。
データに加えて、データベースはまた、特定のルックアップアルゴリズムを満たすデータ構造を維持しており、これらのデータ構造は何らかの方法でデータを参照する。このようにして、高度なルックアップアルゴリズムは、これらのデータ構造において実現することができる。これらのデータ構造は索引です。
インデックス自体も大きく、すべてのメモリに保存することはできませんので、インデックスはしばしばインデックスファイルとしてディスクに保存されます。
私たちが普段話している索引は、特に指定されていない場合は、Bのインデックスが一般的です。統合索引、複合索引、プレフィックス索引、唯一のインデックスは、デフォルトではB＋ツリーインデックスです。Bツリーインデックス以外に、ハッシュインデックスもあります。
利点:
A、データ検索の効率を高め、データベースのIOコストを低減する。
B、インデックス列を通してデータを並べ替えて、データの並べ替えコストを下げて、CPUの消耗を低減しました。
短所:
A、索引も一枚の表であり、この表は主キーと索引フィールドを保存し、本体テーブルのレコードを指すので、インデックスもスペースを占有します。
B、テーブルに対してINSERT、UPDATE、DELETE操作を行うと、MYSQLはデータを更新するだけでなく、インデックスファイルを更新するたびにインデックス列フィールドを追加した該当情報を保存します。
実際の生産環境において、最適なインデックスを構築するためには逐次分析が必要です。そして、私たちの照会条件を最適化したいです。
索引の分類:
1、単値索引の1つの索引は1つのフィールドだけを含み、1つのテーブルは複数の単列索引があります。
2、唯一の索引の列の値は一意でなければなりませんが、空き値があります。
3、複合索引の一つの索引は複数の列を含む。
一つの表は5つ以内の索引を作成することを提案します。
構文:
1、CREATE［UNIQUE］INDEX indexName ON myTable（columnName）；
2、ALTER mTable Add[UNIQUE]INDEX[indexName]ON；
削除：DROP INDEX[indexName]ON myTable;
SHOW INDEX FROMテーブルname\G;
二、EXPLANの役割
EXPLAN：Mysql最適化器をシミュレートしてSQLクエリ文をどのように実行しますか？MysqlはどのようにあなたのSQL文を処理しますか？あなたの照会文または構造表の性能ボトルネックを分析します。


mysql> explain select * from tb_user;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE   | tb_user | ALL | NULL     | NULL | NULL  | NULL |  1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

（一）id列：
（1）、idは同じ実行順序で上から下へ


mysql> explain 
  -> SELECT*FROM tb_order tb1
  -> LEFT JOIN tb_product tb2 ON tb1.tb_product_id = tb2.id
  -> LEFT JOIN tb_user tb3 ON tb1.tb_user_id = tb3.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref            | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| 1 | SIMPLE   | tb1  | ALL  | NULL     | NULL  | NULL  | NULL           |  1 | NULL |
| 1 | SIMPLE   | tb2  | eq_ref | PRIMARY    | PRIMARY | 4    | product.tb1.tb_product_id |  1 | NULL |
| 1 | SIMPLE   | tb3  | eq_ref | PRIMARY    | PRIMARY | 4    | product.tb1.tb_user_id  |  1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+

（2）、サブクエリーであれば、ID番号は自動的に増加し、ID値が大きいほど優先度が高くなり、先に実行される。


mysql> EXPLAIN
  -> select * from tb_product tb1 where tb1.id = (select tb_product_id from tb_order tb2 where id = tb2.id =1);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref  | rows | Extra    |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY   | tb1  | const | PRIMARY    | PRIMARY | 4    | const |  1 | NULL    |
| 2 | SUBQUERY  | tb2  | ALL  | NULL     | NULL  | NULL  | NULL |  1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

（3）、idは同じではなく、同時に存在します。


mysql> EXPLAIN 
  -> select * from(select * from tb_order tb1 where tb1.id =1) s1,tb_user tb2 where s1.tb_user_id = tb2.id;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY   | <derived2> | system | NULL     | NULL  | NULL  | NULL |  1 | NULL |
| 1 | PRIMARY   | tb2    | const | PRIMARY    | PRIMARY | 4    | const |  1 | NULL |
| 2 | DERIVED   | tb1    | const | PRIMARY    | PRIMARY | 4    | const |  1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

dervied 2：派生表2は、派生的なid=2の表tb 1を表しています。
（二）select_type列：データ読出し操作の操作タイプ
1、SIM PLE：簡単なselectクエリ、SQLにはサブクエリまたはユニオンが含まれていません。
2、PRIMARY：クエリには複雑なサブクエリ部分が含まれています。最外層クエリはPRIMARYとしてマークされています。
3、SUBQUERY：selectまたはWHEREリストにサブクエリが含まれています。
4、DERIVED：FROMリストに含まれるサブクエリはDERIVED（デリバティブ）としてマークされ、MYSQLはこれらのサブクエリを再帰的に実行し、結果集をゼロ時テーブルにセットします。
5、UNION：2番目のSELECTがUNIONの後に現れたら、位置UNIONとします。UNIONがFROM子文のサブクエリに含まれている場合、外層SELECTはDERIVEDとしてマークされます。
6、UNION RESULT：UNIONテーブルから結果を取得するselect
（三）テーブル列：この行のデータはどの表についてですか？
（四）type列：アクセスタイプ良いから悪いまでsystem>const>eq_ref>ref>range>index>ALL
1、system：表は一つの記録しかないです。これはconstタイプの特例です。普段の業務には現れません。
2、const：インデックスを通して一度にデータを調べます。このタイプは主にprimrykeyまたはuniqueインデックスを比較するために使われます。一行のデータだけにマッチするので、とても速いです。メインキーをWHERE文の後ろに置くと、Mysqlはこのクエリを定数に変換することができます。
3、eq_ref:一意索引スキャンは、各索引キーに対して、表には一つのレコードだけがマッチします。主キーまたは一意の索引スキャンでよく見られます。
4、ref：一意の索引スキャンではなく、ある単独の値に合致するすべての行を返します。本質的にはインデックスアクセスで、ある個別の値に一致するすべての行を返します。つまり、条件に合った複数のデータを見つけることができるので、彼は検索とスキャンの混合体です。
5、range：与えられた範囲の行だけを検索して、索引を使って行を選択します。key列はどのインデックスを使用しているかを示します。普通はあなたのWHERE文の中でbetween、<、>、inなどの検索が現れて、このような所与の範囲はスキャンするのが全表より良いです。彼はインデックスのある点から始まるだけで、別の点で終わります。すべての索引をスキャンする必要はありません。
6、index：Full Index Scanはインデックスツリーをスキャンする（全表のインデックスをスキャンして、インデックスからデータを取得する）。
7、ALLは全表スキャンしてディスクからデータ百万レベルのデータALLタイプのデータを取得し、できるだけ最適化する。
（五）possible_keys列：この表に適用可能なインデックスを表示します。一つ以上。クエリーに関連するフィールドにインデックスが存在すると、インデックスがリストされますが、クエリーが実際に使用されるとは限りません。
（六）keys列：実際に使用された索引。NULLであれば、インデックスは使用されません。クエリーに上書きインデックスが使用されている場合、そのインデックスはkeyリストにのみ表示されます。カバーインデックス：select後のフィールドは、インデックスを作成するフィールドの個数と一致します。
（七）ken_len列：索引で使用されるバイト数を表し、クエリーで使用される索引の長さを列で計算することができます。精度を損なわない場合は、長さが短いほど良い。key_lenに表示される値は、インデックスフィールドの最大可能な長さであり、実際に使用される長さではなく、すなわちkey_lenは表の定義に基づいて計算したもので、表から検索したものではない。
（八）ref列：インデックスを表示するどの列が使用されていますか？できれば定数です。どの列または定数が索引の列の値を検索するために使用されますか？
（九）rows列（各表の何行が最適化器によって照会されているか）：表の統計情報と索引によって選択された場合、必要なレコードを見つけるために必要な行数を概算する。
（十）Extra列：拡張属性ですが、重要な情報です。
1、Using filesort：mysqlはテーブル内の既定の索引順に読み取れません。


 mysql> explain select order_number from tb_order order by order_money;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra     |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE   | tb_order | ALL | NULL     | NULL | NULL  | NULL |  1 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

説明：order_numberは表の中の唯一の索引列ですが、order byはインデックス列を使用していませんので、mysqlは別の列に並べ替えなければなりません。
2、Using temporary：Mysqlは一時テーブルを使って中間結果を保存しています。並べ替えorder byとグループクエリgroup byによくあります。


mysql> explain select order_number from tb_order group by order_money;
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra              |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE   | tb_order | ALL | NULL     | NULL | NULL  | NULL |  1 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

3、Using indexは対応するselect操作がインデックスをカバーすることを表しています。テーブルにアクセスしたデータ行を避けて、効率がいいです。
Using whereが同時に出現した場合、インデックスがインデックスキーの検索を実行するために使用されることを示します。
using whereが同時に出現していない場合は、検索動作ではなくインデックスを読み込むために使用されることを示しています。


mysql> explain select order_number from tb_order group by order_number;
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys   | key        | key_len | ref | rows | Extra    |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| 1 | SIMPLE   | tb_order | index | index_order_number | index_order_number | 99   | NULL |  1 | Using index |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

4、Using where検索
5、Using join buffer：現在sqlが接続キャッシュを使用していることを示します。
6、impossible where：where字句はいつもfalseで、mysqlはデータ行を取得できません。
7、select tables optimized away：
8、distinct：
締め括りをつける
以上がMysqlの中のexplaninの役割についての詳しい内容で、皆さんに役に立つことを願っています。興味のある方は、MYSQLサブクエリとネストクエリの最適化例解析、いくつかの比較的重要なMySQL変数、ORACLE SQL文の最適化技術ポイント解析などを参照してください。足りないところがあれば、コメントを歓迎します。友達のサポートに感謝します。

詳しくはmysqlの中の冗長性と重複インデックスを理解します。

jsでドロップダウンを設定して読み取り専用のテクニックにします。