MySQLでのインデックスの問題
個人ブログ:Doubleのブログ
前言
インデックスは、データベースの最適化で最も一般的で最も重要な手段の1つであり、インデックスを使用することで、多くのSQLパフォーマンスの問題を解決することができます.次に、MySQLのインデックスの分類、格納、使用方法について詳しく説明します.
インデックスのストレージ分類
MyISAMストレージエンジンのテーブルのデータとインデックスは自動的に別々に格納され、それぞれ独立したファイルである.InnoDBストレージエンジンのテーブルのデータとインデックスは同じテーブルスペースに格納されますが、複数のファイルで構成できます.
MySQLのインデックスのストレージタイプは現在2種類しかありません(BTREEとHASH)、具体的にはテーブルのストレージエンジンと関連しています:MyISAMとInnoDBストレージエンジンはBTREEインデックスのみをサポートします;MEMORY/HEAPストレージエンジンは、HASHおよびBTREEインデックスをサポートします.
MySQLでは現在、関数インデックスはサポートされていませんが、カラムの前の部分にインデックスを入れることができます.たとえば、nameフィールドでは、nameの最初の4文字だけをインデックスすることができます.この特性はインデックスファイルのサイズを大幅に縮小することができ、ユーザーはテーブル構造を設計する際にテキスト列のルートを柔軟に設計することができます.接頭辞インデックスを作成する例を次に示します.
インデックスの使用
インデックスは、カラムに特定の値があるローをすばやく見つけるために使用されます.関連カラムにインデックスを使用することは、SELECTの操作性能を向上させるのに最適な方法です.
クエリーでインデックスを使用する最も主要な条件は、クエリー条件でインデックスキーを使用する必要があることです.複数のカラムインデックスの場合、クエリー条件が複数のカラムキーの一番左の接頭辞を使用している場合にのみインデックスを使用できます.そうしないと、インデックスは使用できません.
索引の使用
MySQLでは、次のような場合にインデックスを使用することができます.
(1)作成された複数のカラムインデックスについて,クエリの条件の中で一番左のカラムが使用されている限り,インデックスは一般的に用いられるが,例として次のように説明する.
まずはcompany_を押してid,moneysの順序で複合インデックスを作成します.具体的には、次のとおりです.
次に「company」を押します.idはテーブルクエリを行い、具体的には以下の通りである.
where条件で使われていなくてもcompany_idとmoneysの組合せ条件では、インデックスは依然として使用できます.これがインデックスのプレフィックス特性です.ただしmoneys条件のみでテーブルをクエリーすると、インデックスは使用されません.具体的には、次のようになります.
(2)likeを使用するクエリの場合、後続が定数であり、
最初の例はインデックスを使用していないことがわかりますが、2番目の例はインデックスを使用することができます.違いは「%」の位置が異なり、前者は「%」を1位に置くとインデックスを使用できませんが、後者は1位に置かずにインデックスを使用します.
また、likeの後ろに列の名前が付いている場合は、インデックスも使用されません.
(3)大きなテキストを検索する場合はlike'%...%'を用いずに全文インデックスを用いる.
(4)カラム名がインデックスの場合column_を使用name is nullはインデックスを使用します.次の例では、クエリnameがnullのレコードにインデックスが使用されます.
インデックスは存在しますが、インデックスは使用しません.
次の場合、インデックスは存在しますが、MySQLでは対応するインデックスは使用されません.
(1)MySQLが全テーブルスキャンよりもインデックスの使用が遅いと推定した場合、インデックスは使用されません.たとえば、カラムkey_part 1は1と100の間に均一に分布しており、次のクエリではインデックスを使用するのはよくありません.
(2)MEMORY/HEAPテーブルを使用し、where条件で「=」を使用してインデックス列を使用しない場合、インデックスは使用されません.heapテーブルは「=」の条件でのみインデックスが使用されます.
(3)orで分割された条件or前の条件の列にインデックスがあり、後の列にインデックスがない場合、関連するインデックスは使用されません.例えば:
上からyear列のみインデックスが表示され、次の実行計画が表示されます.
このSQL文は、yearというカラムにインデックス
(4)インデックス列の第1部でない場合、次の例を示します.
moneyには複合インデックスが作成されていますが、moneyはインデックスの最初の列ではないため、クエリではこのインデックスはMySQLに採用されません.
(5)likeが
nameにインデックスが作成されていますが、where条件でlikeの値の「%」が1位になったため、MySQLもこのインデックスを採用しません.
(6)列タイプが文字列であれば、where条件で文字定数値を引用符で引くことを忘れないでください.そうしないと、この列にインデックスがあってもMySQLは使いません.MySQLはデフォルトで入力した定数値を変換してから検索するからです.次の例ではcompany 2テーブルのnameフィールドは文字型であるが、SQL文の条件値294は数値型の値であるため、nameにインデックスがあってもMySQLはインデックスを正しく使うことができず、全テーブルスキャンを継続する.
上の例から分かるように、最初のSQL文では、文字型のカラムnameに数値型定数を割り当てています.では、name列にインデックスがありますが、使用されません.2番目のSQL文では、インデックスを正しく使用できます.
索引の使用状況の表示
インデックスが動作している場合、
上記の例から、現在使用されているMySQLデータベースのインデックスは理想的ではないことがわかります.
本文の多くは『深入浅出MySQL』から抜粋した.
前言
インデックスは、データベースの最適化で最も一般的で最も重要な手段の1つであり、インデックスを使用することで、多くのSQLパフォーマンスの問題を解決することができます.次に、MySQLのインデックスの分類、格納、使用方法について詳しく説明します.
インデックスのストレージ分類
MyISAMストレージエンジンのテーブルのデータとインデックスは自動的に別々に格納され、それぞれ独立したファイルである.InnoDBストレージエンジンのテーブルのデータとインデックスは同じテーブルスペースに格納されますが、複数のファイルで構成できます.
MySQLのインデックスのストレージタイプは現在2種類しかありません(BTREEとHASH)、具体的にはテーブルのストレージエンジンと関連しています:MyISAMとInnoDBストレージエンジンはBTREEインデックスのみをサポートします;MEMORY/HEAPストレージエンジンは、HASHおよびBTREEインデックスをサポートします.
MySQLでは現在、関数インデックスはサポートされていませんが、カラムの前の部分にインデックスを入れることができます.たとえば、nameフィールドでは、nameの最初の4文字だけをインデックスすることができます.この特性はインデックスファイルのサイズを大幅に縮小することができ、ユーザーはテーブル構造を設計する際にテキスト列のルートを柔軟に設計することができます.接頭辞インデックスを作成する例を次に示します.
mysql> create index ind_company2_name on company2(name(4));
Query OK,1000 rows affected(0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0
インデックスの使用
インデックスは、カラムに特定の値があるローをすばやく見つけるために使用されます.関連カラムにインデックスを使用することは、SELECTの操作性能を向上させるのに最適な方法です.
クエリーでインデックスを使用する最も主要な条件は、クエリー条件でインデックスキーを使用する必要があることです.複数のカラムインデックスの場合、クエリー条件が複数のカラムキーの一番左の接頭辞を使用している場合にのみインデックスを使用できます.そうしないと、インデックスは使用できません.
索引の使用
MySQLでは、次のような場合にインデックスを使用することができます.
(1)作成された複数のカラムインデックスについて,クエリの条件の中で一番左のカラムが使用されている限り,インデックスは一般的に用いられるが,例として次のように説明する.
まずはcompany_を押してid,moneysの順序で複合インデックスを作成します.具体的には、次のとおりです.
mysql> create index ind_sales2_companyid_moneys on sales2(company_id, moneys);
Query OK,1000 rows affected(0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0
次に「company」を押します.idはテーブルクエリを行い、具体的には以下の通りである.
mysql> explain select * from sales2 where company_id = 2006\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales2
type: ref
possible_keys: ind_sales2_companyid_moneys
key: ind_sales2_companyid_moneys
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set(0.00 sec)
where条件で使われていなくてもcompany_idとmoneysの組合せ条件では、インデックスは依然として使用できます.これがインデックスのプレフィックス特性です.ただしmoneys条件のみでテーブルをクエリーすると、インデックスは使用されません.具体的には、次のようになります.
mysql> explain select * from sales2 where moneys = 1\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set(0.00 sec)
(2)likeを使用するクエリの場合、後続が定数であり、
%番が最初の文字でない場合、インデックスが使用される可能性があります.次の2つの実行計画を見てみましょう.mysql> explain select * from company2 where name like '%3'\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set(0.00 sec)
mysql> explain select * from company2 where name like '3%'\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: range
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: NULL
rows: 103
Extra: Using where
1 row in set(0.00 sec)
最初の例はインデックスを使用していないことがわかりますが、2番目の例はインデックスを使用することができます.違いは「%」の位置が異なり、前者は「%」を1位に置くとインデックスを使用できませんが、後者は1位に置かずにインデックスを使用します.
また、likeの後ろに列の名前が付いている場合は、インデックスも使用されません.
(3)大きなテキストを検索する場合はlike'%...%'を用いずに全文インデックスを用いる.
(4)カラム名がインデックスの場合column_を使用name is nullはインデックスを使用します.次の例では、クエリnameがnullのレコードにインデックスが使用されます.
mysql> explain select * from company2 where name is null\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: const
rows: 1
Extra: Using where
1 row in set(0.00 sec)
インデックスは存在しますが、インデックスは使用しません.
次の場合、インデックスは存在しますが、MySQLでは対応するインデックスは使用されません.
(1)MySQLが全テーブルスキャンよりもインデックスの使用が遅いと推定した場合、インデックスは使用されません.たとえば、カラムkey_part 1は1と100の間に均一に分布しており、次のクエリではインデックスを使用するのはよくありません.
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;
(2)MEMORY/HEAPテーブルを使用し、where条件で「=」を使用してインデックス列を使用しない場合、インデックスは使用されません.heapテーブルは「=」の条件でのみインデックスが使用されます.
(3)orで分割された条件or前の条件の列にインデックスがあり、後の列にインデックスがない場合、関連するインデックスは使用されません.例えば:
mysql> show index from sales\G;
**************************** 1. row *************************************
Table: sales
Non_unique: 1
Key_name: ind_sales_year
Seq_in_index: 1
Column_name: year
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
1 row in set(0.00 sec)
上からyear列のみインデックスが表示され、次の実行計画が表示されます.
mysql> explain select * from sales where year = 2001 or country = 'China'\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: ind_sales_year
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
1 row in set(0.00 sec)
このSQL文は、yearというカラムにインデックス
ind_sales_yearが存在するが、orの条件のカラムにインデックスがないため、このインデックスを使用していないことがわかります.(4)インデックス列の第1部でない場合、次の例を示します.
mysql> explain select * from sales2 where moneys = 1\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set(0.00 sec)
moneyには複合インデックスが作成されていますが、moneyはインデックスの最初の列ではないため、クエリではこのインデックスはMySQLに採用されません.
(5)likeが
%で始まる場合、例えば:mysql> explain select * from company2 where name like '%3'\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set(0.00 sec)
nameにインデックスが作成されていますが、where条件でlikeの値の「%」が1位になったため、MySQLもこのインデックスを採用しません.
(6)列タイプが文字列であれば、where条件で文字定数値を引用符で引くことを忘れないでください.そうしないと、この列にインデックスがあってもMySQLは使いません.MySQLはデフォルトで入力した定数値を変換してから検索するからです.次の例ではcompany 2テーブルのnameフィールドは文字型であるが、SQL文の条件値294は数値型の値であるため、nameにインデックスがあってもMySQLはインデックスを正しく使うことができず、全テーブルスキャンを継続する.
mysql> explain select * from company2 where name = 294\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: ind_company2_name
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set(0.00 sec)
mysql> explain select * from company2 where name = '294'\G;
**************************** 1. row *************************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 23
ref: const
rows: 1
Extra: Using where
1 row in set(0.00 sec)
上の例から分かるように、最初のSQL文では、文字型のカラムnameに数値型定数を割り当てています.では、name列にインデックスがありますが、使用されません.2番目のSQL文では、インデックスを正しく使用できます.
索引の使用状況の表示
インデックスが動作している場合、
Handler_read_keyの値は高くなります.この値は、インデックス値によってローが読み出された回数を表します.低い値は、インデックスが頻繁に使用されないため、インデックスを増加させるパフォーマンスの改善が高くないことを示します.Handler_read_rnd_nextの値が高いと、クエリが非効率に実行され、インデックス・リカバリが確立される必要があります.この値は、データファイルで次のローを読み込むリクエストの数を意味します.大量のテーブルスキャンが行われている場合、Handler_read_rnd_nextの値が高い場合は、通常、テーブルインデックスが正しくないか、書き込まれたクエリがインデックスを利用していないことを示します.具体的には、次のようになります.mysql> show status like 'Handler_read%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| Handler_read_first | 0 |
| Handler_read_key | 5 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2055 |
+------------------------+----------+
6 rows in set(0.00 sec)
上記の例から、現在使用されているMySQLデータベースのインデックスは理想的ではないことがわかります.
本文の多くは『深入浅出MySQL』から抜粋した.