【MySQl】MyISAMとInnoDBインデックスの比較

一部の内容は:http://www.2cto.com/database/201211/172380.html
比較的良い文章:http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
MyISAM
MyISAMエンジンは、B＋Treeをインデックス構造として使用し、リーフノードのdataドメインはデータ記録のアドレスで保存されている.図はMyISAMインデックスの原理図です.

ここには表が全部で三列あります.もしCol 1をメインキーとするなら、上の図はMyISAM表のメインインデックスです.MyISAMのインデックスファイルはデータ記録の住所だけを保存していることが分かります.MyISAMにおいては、主インデックスと補助インデックスは構造的には何の違いもなく、主インデックスのみがkeyを必要とするが、補助インデックスのkeyは重複することができる.Col 2に補助インデックスを作成すると、このインデックスの構造は下の図のようになります.

 
同じB+Treeでもあります.データ記録の住所はdataドメインに保存されます.したがって、MyISAMでインデックス検索のアルゴリズムは、まずB＋Tree検索アルゴリズムに従ってインデックスを検索し、指定されたKeyが存在すると、そのdataドメインの値を取り出し、dataドメインの値をアドレスとして該当データレコードを読み出す.
MyISAMのインデックス方式は「非集約」とも呼ばれていますが、InnoDBの集約インデックスと区別するための呼称です.
 
InnoDB
InnoDBもインデックス構造としてB＋Treeを使用していますが、具体的にはMyISAMとは違っています.
最初の大きな違いはInnoDBのデータファイル自体がインデックスファイルです.上記から、MyISAMインデックスファイルとデータファイルは分離されており、インデックスファイルはデータレコードのアドレスのみを保存していることが分かります.InnoDBでは、テーブルデータファイル自体がB＋Treeのインデックス構造であり、このツリーのリーフノードdataドメインは完全なデータ記録を保存している.このインデックスのキーはデータテーブルのキーですので、InnoDBテーブルデータファイル自体がインデックスです.

上の図はInnoDBマスタインデックス(データファイルでもあります)の模式図で、リーフノードに完全なデータ記録が含まれていることが分かります.このインデックスは集約インデックスといいます.InnoDBのデータファイル自体はメインキーを押して集めますので、InnoDB要求表にはメインキーが必要です.明示的な指定がないとMySQL
システムは、データレコードを一意に識別できる列をプライマリキーとして自動的に選択します.このような列が存在しない場合、
MySQLは自動的にInnoDBテーブルにキーとして暗黙のフィールドを生成します.このフィールドの長さは6バイトで、タイプは長整形です.
二つ目はMyISAMインデックスとは異なり、InnoDBの補助インデックスdataドメインには、アドレスではなく、それぞれの記録キーの値が格納されている.つまり、InnoDBのすべての補助インデックスは、マスターキーをdataドメインとして参照します.例えば、次の図はCol 3に定義された補助インデックスである.

ここでは英語のASCIIコードを比較基準としています.インデックスを集めるというインプリメンテーションは、キーを押すと検索が非常に効率的になりますが、補助インデックス検索は2回のインデックスを検索する必要があります.まず、補助インデックスを検索してプライマリキーを獲得し、メインキーを使ってインデックス内でレコードを検索します.
異なる記憶エンジンのインデックスの実現方式を知ることは、インデックスの正確な使用と最適化に非常に役立ちます.例えば、InnoDBのインデックスの実現を知ると、なぜ長すぎるフィールドをキーとして使用することを勧めないのかが分かりやすくなります.
すべての補助インデックスが主インデックスを参照しているため、長すぎる主インデックスは補助インデックスを大きくしすぎます.例えば、
非単調なフィールドをメインキーとしてInnoDBにおいては良いアイデアではない.
InnoDBデータファイル自体がB＋Treeであるため、単調でないメインキーは、新たな記録を挿入する際にデータファイルがB＋Treeの特性を維持するために頻繁に分裂調整され、非常に効果が低く、自己増加フィールドをメインキーとして使用するのが良い選択です.
 
結合索引または複合索引と呼ばれる結合インデックスは、「MySQL技術内幕InnoDB記憶エンジン」の「5.6.4コンポジット」の章にいくつかの説明があります.(第二版の内容は更新されていません.)表の複数の列に索引を作成することです.コンポジットもB＋ツリーです.コンコーダンスファレンスのキーの数は1ではなく、2以上です.showdexは以下の通りです.

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `column_a` int(11) DEFAULT NULL,
  `column_b` int(11) DEFAULT NULL,
  `column_c` int(11) DEFAULT NULL,
  `column_d` varchar(10) DEFAULT NULL,
  KEY `index_a_b_c` (`column_a`,`column_b`,`column_c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show index  from t2 \G
*************************** 1. row ***************************
        Table: t2
   Non_unique: 1
     Key_name: index_a_b_c
 Seq_in_index: 1
  Column_name: column_a
    Collation: A
  Cardinality: 9
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: t2
   Non_unique: 1
     Key_name: index_a_b_c
 Seq_in_index: 2
  Column_name: column_b
    Collation: A
  Cardinality: 9
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: t2
   Non_unique: 1
     Key_name: index_a_b_c
 Seq_in_index: 3
  Column_name: column_c
    Collation: A
  Cardinality: 9
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.01 sec)

ショーindex文法の説明:

1、Table：       。
2、Non_unique：      ，    primary key 0，        。
3、Key_name：     ，           drop index。
4、Seq_in_index：        ，            。（      ）
5、Column_name：    。
6、Collation：            。   ‘A’  NULL。B+     A，    。     Heap    ，     Hash  ，      NULL。  Hash  Hash        ，          。
7、Cardinality：      ，               ，                      。Cardinality/             1，     ，                。          ，       ，      ANALYZE TABLE myisamchk -a       。
8、Sub_part：          。     index_a    ，    10，     a   10   。       ，     NULL。
9、Packed：        。       ，  NULL。
10、Null：        NULL 。    index_a_b_c   YES，       a、b、c   NULL 。
11、Index_type：     。InnoDB       B+   ，         BTREE。
12、Comment：  。

インデックスイメージの説明は、携帯電話の電話帳にたとえられます.インデックスが複数のキーのB＋ツリーの場合、シングルのインデックスのキーの順序と同じです.インデックスを使うのも、リーフノードの論理的な順序ですべてのデータを読み出します.たとえば、テーブルの中のcolumn 1とcolumn 2はindex_を作ります.1_2,このコンポジットはまずcolumn 1の順番で保存して、column 1の値が同じデータを使って、coiumn 2の順番で保存します.例えば、(1,1)、(1,2)、(2,1)、(2,4)、(3,1)、(3,2)は、(column 1,column 2)の順に格納される:

 
結合インデックスは、複数の列から構成される補助インデックスとしても考えられ、主キーをdataドメインとして参照します.
また、インデックスの使用は、最初のindex_のような法則を把握する必要があります.a_b_cこのインデックスを組み合わせて、本当にインデックスに使えるのは:

mysql> explain select * from t2 where column_a=1 and column_b=2 and column_c=3;
+----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref               | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | t2    | ref  | index_a_b_c   | index_a_b_c | 15      | const,const,const |    1 | Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t2 where column_a=1 and column_b=2;
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t2    | ref  | index_a_b_c   | index_a_b_c | 10      | const,const |    1 | Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t2 where column_a=1;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t2    | ref  | index_a_b_c   | index_a_b_c | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

また、column_に対してもaとcolumn_c 2列のクエリーは、実際にはグループインデックスの中のcolumn_だけを使用しています.a部分:

mysql> explain select * from t2 where column_a=1 and column_c=3;               
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t2    | ref  | index_a_b_c   | index_a_b_c | 5       | const |    2 | Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

キーが見えますlenは5で、クエリはインデックスindex_を通じて(通って)です.a_b_cのcolumn_aに該当するcolumn_が見つかった.a=1行の葉っぱノード論理位置エリアですが、条件がコロムンです.cインデックスに使用できません.column_を巡回しました.a=1のすべての行です
したがって、一般的によく言われているコンコーダンスファレンスの「最左プレフィックス」の原則は、コンコーダンスファレンスの一番左の列からの組み合わせだけを簡単に理解することである.
コンポジットのメカニズムを理解しています.何が使えないのかよく分かります.

mysql> explain select * from t2 where column_b=2 and column_c=3;  
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    9 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t2 where column_b=2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    9 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t2 where column_c=3;               
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    9 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)