mysqlインデックス-メモ
7320 ワード
索引
mysqlで最も一般的なインデックス構造はbtree(O(log(n))ですが、パフォーマンスを向上させるために他のタイプのインデックスを使用することを望んでいる場合があります.hashは,例えばユーザ名でユーザidを取得する際,彼らは常に一対一の関係であり,使用するオペレータは=にすぎず,hashをインデックスデータ構造として使用すれば,時間複雑度をO(1)に下げることができる.残念なことに、現在のmysqlバージョン(5.6)では、hashはMEMORYとNDBの2つのエンジンのみをサポートしていますが、私たちが最もよく使うINNODBとMYISAMはhashタイプのインデックスをサポートしていません.
1、BTreeインデックス:
BTree(マルチサーチツリー、二叉ではない)は一般的なデータ構造である.BTree構造を使用すると、位置決め記録時に経験される中間プロセスを著しく低減し、アクセス速度を速めることができる.翻訳によると、BはBalanceの略称とされることが多い.このデータ構造は一般的にデータベースのインデックスに用いられ,総合効率が高い.
B−Treeインデックスは、例えば=,>,>=,<,<=およびBETWEENのような比較オペレータに用いることができる.また、クエリ条件がワイルドカードで始まる定数でない限り、LIKEオペレータにも使用できます.
idx_の追加nameインデックス:
すべてのWHEREのANDレベル条件を上書きしていないインデックスは使用されません.すなわち、インデックスを使用するには、このインデックスの最初のカラムが各ANDグループに表示される必要があります.
mysqlは、使用可能な場合でもインデックスを使用しない場合があります.たとえばmysqlがインデックスを使用すると、ほとんどのローデータが読み出されると推定されます.(この場合、インデックスを使用するよりも1回の全テーブルスキャンが高速になる可能性があります.取得が少ないためです).ただし、文でLIMITを使用して返されるローの数を制限する場合、mysqlはインデックスを使用します.結果のロー数が少ない場合、インデックスの使用効率が向上するためです.
2、Hash索引特徴:
Hashタイプのインデックスには、前述のような特徴がいくつかあります.正確な検索は非常に速く(=<>とinを含む)、その検索効率は非常に高く、インデックスの検索は一度に位置決めすることができ、BTreeインデックスがルートノードから枝点まで必要としないため、Hashインデックスのクエリー効率はB-Treeインデックスよりはるかに高いです.
不適切:
1、あいまいなクエリーと範囲クエリー(like,>,<,between......andなどを含む)には適していません.HashインデックスはHash演算を行った後のHash値を比較するため、等値のフィルタリングにのみ使用でき、範囲ベースのフィルタリングには使用できない.対応するHashアルゴリズム処理後のHash値の大きさ関係は、Hash演算前と完全に同じであることを保証できないからである.2、並べ替えに適していない.データベースはインデックスのデータを利用して並べ替え性能を向上させることができない.同様にHash値の大きさが不確定であるためである.3、複合インデックスは部分インデックスフィールドでクエリーできない.Hashインデックスは、Hash値を計算する際に、Hash値を単独で計算するのではなく、結合インデックス結合後にHash値を一緒に計算するので、結合インデックスの前の1つまたは複数のインデックスキーでクエリーを行う場合、Hashインデックスも利用できない.
3、備考:
1、インデックス列は、ビジネス判断に基づいてwhere条件に現れる列が必要です.2、インデックスを使用するには、性別などのカラムの値の分布を考慮する必要があります.「男性/女性」を検索すると、約半分のローが得られ、すべてのインデックスはあまり役に立ちません.3、短いインデックスを使うメリットが多い.文字列をインデックスする場合は、接頭辞の長さを指定します.インデックスの長さが小さく、インデックス空間とディスクIOを節約しながらインデックスを短くし、Mysqlはメモリにより多くの値を格納することができます.4、最左接頭辞を利用する.複数のインデックスを作成し、いくつかのインデックスとして機能します.インデックスの一番左の列を使用して行を一致させることができるからです.このようなカラムセットを最左接頭辞と呼びます.5、過度なインデックスは使用しないでください.追加のインデックスは、スペースを消費し、書き込みのパフォーマンスを低下させる必要があります.テーブルを変更するときはインデックスを更新する必要があり、再構築する必要がある場合があります.インデックスが多ければ多いほど時間がかかります.インデックスが多すぎると、MYSQLが最適なインデックスを選択できない可能性があります.6、INNODBストレージエンジンは、レコードをプライマリキーで優先的に保存し、プライマリキーがないがユニークインデックスがある場合はユニークインデックス列で保存する.両方がない場合、テーブルに内部カラムが生成されます.この列の順序で保存すると、InnoDBの通常のインデックスにはプライマリ・キーが保存されるので、プライマリ・キーはできるだけ短いデータ型を選択します.
7、できるだけNULLを避ける:NULLを保存しない限り、NOT NULLとして指定する必要があります.MySQLでは、インデックス、インデックスの統計、および比較演算が複雑になるため、空の値を含むカラムはクエリーの最適化が困難です.空の値の代わりに0、特殊な値、または空の列を使用する必要があります.
8、使用--現在のセッションで生成されたすべてのprofileを表示します.
mysqlで最も一般的なインデックス構造はbtree(O(log(n))ですが、パフォーマンスを向上させるために他のタイプのインデックスを使用することを望んでいる場合があります.hashは,例えばユーザ名でユーザidを取得する際,彼らは常に一対一の関係であり,使用するオペレータは=にすぎず,hashをインデックスデータ構造として使用すれば,時間複雑度をO(1)に下げることができる.残念なことに、現在のmysqlバージョン(5.6)では、hashはMEMORYとNDBの2つのエンジンのみをサポートしていますが、私たちが最もよく使うINNODBとMYISAMはhashタイプのインデックスをサポートしていません.
1、BTreeインデックス:
BTree(マルチサーチツリー、二叉ではない)は一般的なデータ構造である.BTree構造を使用すると、位置決め記録時に経験される中間プロセスを著しく低減し、アクセス速度を速めることができる.翻訳によると、BはBalanceの略称とされることが多い.このデータ構造は一般的にデータベースのインデックスに用いられ,総合効率が高い.
B−Treeインデックスは、例えば=,>,>=,<,<=およびBETWEENのような比較オペレータに用いることができる.また、クエリ条件がワイルドカードで始まる定数でない限り、LIKEオペレータにも使用できます.
EXPLAIN select * from blog.my_user where `name` in ('rhythmk251','rhythmk2151','rhythmk7251','rhythmk685');
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | my_user | ALL | NULL | NULL | NULL | NULL | 778424 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
1 row in set
idx_の追加nameインデックス:
ALTER TABLE blog.`my_user`
ADD INDEX `idx_name` (`name`) USING BTREE ;
mysql> EXPLAIN select * from blog.my_user where `name` in ('rhythmk251','rhythmk2151','rhythmk7251','rhythmk685');
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | my_user | range | idx_name | idx_name | 303 | NULL | 4 | Using where |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
mysql> EXPLAIN select * from blog.my_user where `name` LIKE 'rhythmk830%';
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | my_user | range | idx_name | idx_name | 303 | NULL | 11 | Using where |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
1 row in set
すべてのWHEREのANDレベル条件を上書きしていないインデックスは使用されません.すなわち、インデックスを使用するには、このインデックスの最初のカラムが各ANDグループに表示される必要があります.
mysql> EXPLAIN select * from blog.my_user where `name` LIKE 'rhythmk830%' or age>35;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | my_user | ALL | idx_name | NULL | NULL | NULL | 7266 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set
mysql> EXPLAIN select * from blog.my_user where `name` LIKE 'rhythmk830%' and age>35;
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | my_user | range | idx_name | idx_name | 303 | NULL | 11 | Using where |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
1 row in setmysqlは、使用可能な場合でもインデックスを使用しない場合があります.たとえばmysqlがインデックスを使用すると、ほとんどのローデータが読み出されると推定されます.(この場合、インデックスを使用するよりも1回の全テーブルスキャンが高速になる可能性があります.取得が少ないためです).ただし、文でLIMITを使用して返されるローの数を制限する場合、mysqlはインデックスを使用します.結果のロー数が少ない場合、インデックスの使用効率が向上するためです.
mysql> EXPLAIN select * from blog.my_user where `name` <> 'rhythmk830' ;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | my_user | ALL | idx_name | NULL | NULL | NULL | 7769 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set
mysql> EXPLAIN select * from blog.my_user where `name` <> 'rhythmk830' limit 5;
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | my_user | range | idx_name | idx_name | 303 | NULL | 5658 | Using where |
+----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+
1 row in set2、Hash索引特徴:
Hashタイプのインデックスには、前述のような特徴がいくつかあります.正確な検索は非常に速く(=<>とinを含む)、その検索効率は非常に高く、インデックスの検索は一度に位置決めすることができ、BTreeインデックスがルートノードから枝点まで必要としないため、Hashインデックスのクエリー効率はB-Treeインデックスよりはるかに高いです.
不適切:
1、あいまいなクエリーと範囲クエリー(like,>,<,between......andなどを含む)には適していません.HashインデックスはHash演算を行った後のHash値を比較するため、等値のフィルタリングにのみ使用でき、範囲ベースのフィルタリングには使用できない.対応するHashアルゴリズム処理後のHash値の大きさ関係は、Hash演算前と完全に同じであることを保証できないからである.2、並べ替えに適していない.データベースはインデックスのデータを利用して並べ替え性能を向上させることができない.同様にHash値の大きさが不確定であるためである.3、複合インデックスは部分インデックスフィールドでクエリーできない.Hashインデックスは、Hash値を計算する際に、Hash値を単独で計算するのではなく、結合インデックス結合後にHash値を一緒に計算するので、結合インデックスの前の1つまたは複数のインデックスキーでクエリーを行う場合、Hashインデックスも利用できない.
3、備考:
1、インデックス列は、ビジネス判断に基づいてwhere条件に現れる列が必要です.2、インデックスを使用するには、性別などのカラムの値の分布を考慮する必要があります.「男性/女性」を検索すると、約半分のローが得られ、すべてのインデックスはあまり役に立ちません.3、短いインデックスを使うメリットが多い.文字列をインデックスする場合は、接頭辞の長さを指定します.インデックスの長さが小さく、インデックス空間とディスクIOを節約しながらインデックスを短くし、Mysqlはメモリにより多くの値を格納することができます.4、最左接頭辞を利用する.複数のインデックスを作成し、いくつかのインデックスとして機能します.インデックスの一番左の列を使用して行を一致させることができるからです.このようなカラムセットを最左接頭辞と呼びます.5、過度なインデックスは使用しないでください.追加のインデックスは、スペースを消費し、書き込みのパフォーマンスを低下させる必要があります.テーブルを変更するときはインデックスを更新する必要があり、再構築する必要がある場合があります.インデックスが多ければ多いほど時間がかかります.インデックスが多すぎると、MYSQLが最適なインデックスを選択できない可能性があります.6、INNODBストレージエンジンは、レコードをプライマリキーで優先的に保存し、プライマリキーがないがユニークインデックスがある場合はユニークインデックス列で保存する.両方がない場合、テーブルに内部カラムが生成されます.この列の順序で保存すると、InnoDBの通常のインデックスにはプライマリ・キーが保存されるので、プライマリ・キーはできるだけ短いデータ型を選択します.
7、できるだけNULLを避ける:NULLを保存しない限り、NOT NULLとして指定する必要があります.MySQLでは、インデックス、インデックスの統計、および比較演算が複雑になるため、空の値を含むカラムはクエリーの最適化が困難です.空の値の代わりに0、特殊な値、または空の列を使用する必要があります.
8、使用--現在のセッションで生成されたすべてのprofileを表示します.
mysql> show profiles;