ラーニングインデックス
一、インデックスとは何ですか.
インデックスは、特定の値を持つレコードをすばやく検索するために使用され、すべてのMySQLインデックスはBツリーとして保存されます.インデックスがない場合は、クエリを実行するときにMySQLは、必要に応じたレコードが見つかるまで、最初のレコードからテーブル全体のすべてのレコードをスキャンする必要があります.表の記録数が多ければ多いほど、この操作の代価は高くなります.検索条件となるカラムにインデックスが作成されている場合、MySQLはレコードをスキャンすることなく、ターゲットレコードの場所を迅速に取得できます.テーブルに1000個のレコードがある場合、インデックス検索レコードは、シーケンススキャンレコードよりも少なくとも100倍速くなります.
peopleというテーブルを作成したとします.
次に、1000個の異なるname値をpeopleテーブルに完全にランダムに挿入します.データファイルのname列には明確な順序はありません.名前列のインデックスを作成すると、MySQLはインデックス内の名前列をソートし、インデックス内の各項目について、MySQLは内部でデータファイルに実際に位置を記録するポインタを保存します.したがって、名前が「Mike」レコードに等しいpeopleidを検索する場合(SQLコマンドは「SELECT peopleid FROM people WHERE name='Mike';」)、MySQLは、nameのインデックスで「Mike」値を検索し、データファイル内の対応する行に直接移動し、その行のpeopleid(999)を正確に返すことができます.このプロセスでは、MySQLはローを1つ処理するだけで結果を返すことができます.「name」列のインデックスがない場合、MySQLはデータファイル内のすべてのレコード、すなわち1000レコードをスキャンします.MySQL処理が必要なレコードの数が少ないほど、タスクの完了速度が速くなることは明らかです.
二、索引の種類
MySQLでは、複数のインデックス・タイプを選択できます.
通常のインデックス:
これは最も基本的なインデックスタイプであり、一意性などの制限はありません.通常のインデックスは、次の方法で作成できます.
CREATE INDEX<インデックスの名前>ON tablename(列のリスト)などのインデックスを作成します.
ALTER TABLE tablename ADD INDEX[インデックスの名前](列のリスト)などのテーブルを変更します.
テーブルを作成するときに、CREATE TABLE tablename([...]などのインデックスを指定します.INDEX[インデックスの名前](列のリスト);
一意性インデックス:
このインデックスは、前の通常のインデックスとほぼ同じですが、インデックス列のすべての値が1回しか表示されません.つまり、一意でなければなりません.一意性インデックスは、次の方法で作成できます.
CREATE UNIQUE INDEX<インデックスの名前>ON tablename(列のリスト)などのインデックスを作成します.
表を変更します.たとえば、ALTER TABLE tablename ADD UNIQUE[インデックスの名前](列のリスト)です.
テーブルを作成するときに、CREATE TABLE tablename([...]などのインデックスを指定します.UNIQUE[インデックスの名前](列のリスト);
プライマリ・キー:
プライマリ・キーはユニークなインデックスですが、PRIMARY KEYとして指定する必要があります.AUTOを使ったことがあるならINCREMENTタイプの列は、プライマリ・キーなどの概念に詳しいかもしれません.プライマリ・キーは、一般的にテーブルの作成時に指定されます.たとえば、「CREATE TABLE tablename([...]PRIMARY KEY(列のリスト);「ただし、テーブルを変更することでプライマリ・キーを追加することもできます.たとえば、「ALTER TABLE tablename ADD PRIMARY KEY(列のリスト);「.各テーブルにはプライマリ・キーが1つしかありません.
全文索引:
MySQLは3.23.23版から全文インデックスと全文検索をサポートしています.MySQLでは全文インデックスのインデックスタイプはFULLTEXTです.フルテキストインデックスはVARCHARまたはTEXTタイプのカラムで作成できます.CREATE TABLEコマンドで作成するか、ALTER TABLEまたはCREATE INDEXコマンドで作成できます.大規模なデータセットの場合、ALTER TABLE(またはCREATE INDEX)コマンドによる全文インデックスの作成は、全文インデックス付きの空のテーブルにレコードを挿入するよりも高速です.この文書では、全文インデックスについては説明しません.詳細については、MySQL documentationを参照してください.
三、単列索引と多列索引
インデックスは、単一のカラムインデックスであってもよいし、複数のカラムインデックスであってもよい.この2つのインデックスの違いを具体的な例で説明します.次のようなpeopleテーブルがあるとします.
次に、このpeopleテーブルに挿入したデータを示します.
このデータの断片には「Mikes」という名前の4人(うち2人はSullivans、2人はMcConnells)、17歳の2人、変わった名前のJoe Smithがあります.
この表の主な用途は、指定したユーザーの姓、名前、年齢に応じてpeopleidを返すことです.たとえば、Mike Sullivanという名前の17歳のユーザーのpeopleid(SQLコマンドはSELECT peopleid FROM people WHERE firstname='Mike'AND lastname='Sullivan'AND age=17)を検索する必要があります.MySQLがクエリーを実行するたびにテーブル全体をスキャンしたくないので、インデックスの運用を検討する必要があります.
まず、firstname、lastname、ageなどのインデックスを単一のカラムに作成することを考慮します.firstname列のインデックス(ALTER TABLE people ADD INDEX firstname)を作成すると、MySQLは、このインデックスを使用して、firstname='Mike'のレコードに検索範囲を迅速に制限し、この「中間結果セット」で他の条件の検索を行います.まず、lastnameが「Sullivan」に等しくないレコードを排除し、ageが17に等しくないレコードを排除します.レコードがすべての検索条件を満たすと、MySQLは最終的な検索結果を返します.
firstname列のインデックスが作成されたため、テーブルの完全なスキャンを実行するよりもMySQLの効率が向上しましたが、MySQLスキャンの記録数は実際に必要なものをはるかに上回っています.firstnameカラムのインデックスを削除してlastnameまたはageカラムのインデックスを作成することができますが、どのカラムでインデックス検索を作成しても効率は似ています.
検索効率を向上させるためには,マルチカラムインデックスの運用を検討する必要がある.firstname、lastname、ageの3つのカラムに複数のカラムインデックスを作成すると、MySQLは1回の検索で正しい結果を見つけることができます!次は、このマルチカラムインデックスを作成するSQLコマンドです.
インデックスファイルがBツリー形式で保存されているため、MySQLはすぐに適切なfirstnameに移動し、適切なlastnameに移動し、最後に適切なageに移動することができます.データファイルの記録をスキャンしていない場合、MySQLは検索対象の記録を正確に見つけました!
では、firstname、lastname、ageの3つのカラムにそれぞれ1つのカラムインデックスを作成すると、firstname、lastname、ageの複数のカラムインデックスを作成するのと同じ効果が得られますか?答えは否定的で、両者は全く違います.クエリを実行するとき、MySQLは1つのインデックスしか使用できません.3つの単列インデックスがある場合、MySQLは最も制限の厳しいインデックスを選択しようとします.しかし、最も厳格な単一カラムインデックスを制限しても、firstname、lastname、ageの3つのカラムの複数カラムインデックスよりも制限能力が低いに違いありません.
四、一番左の接頭辞
マルチカラムインデックスには、最左接頭辞(Leftmost Prefixing)と呼ばれる概念によって表現されるもう一つの利点があります.前の例を考えてみると、firstname、lastname、age列の複数の列インデックスがあります.このインデックスをfnameと呼びます.lname_age.検索条件が次の各列の組合せである場合、MySQLはfname_を使用します.lname_age索引:
別の側面から理解すると、(firstname,lastname,age)、(firstname,lastname)、および(firstname)これらのカラムの組合せのインデックスを作成したことに相当する.次のクエリでは、このfnameを使用できます.lname_age索引:
五、索引列の選択
パフォーマンスの最適化では、インデックスを作成する列を選択することが最も重要なステップの1つです.インデックスを使用するには、主にWHERE句に現れる列、join句に現れる列の2種類が考えられます.次のクエリを参照してください.
このクエリは、前のクエリとは少し異なりますが、単純なクエリです.ageはSELECT部分で参照されるため、MySQLはカラム選択操作を制限しません.したがって、このクエリでは、ageカラムのインデックスを作成する必要はありません.より複雑な例を次に示します.
前の例と同様にfirstnameとlastnameがWHERE句に表示されるため、この2つの列はインデックスを作成する必要があります.それ以外に、townテーブルのtownid列がjoin句に表示されるため、カラムのインデックスの作成を検討する必要があります.では,WHERE句とjoin句に出現する各列をインデックスすべきであると簡単に考えることができるだろうか.差は多くないが、完全ではない.カラムを比較するオペレータタイプも考慮する必要があります.MySQLでは、<,<=,=,>,>=,BETWEEN,IN,およびある時のLIKEを使用するオペレータのみが使用されます.LIKEオペレーションでインデックスを使用できる場合は、他のオペレーション数がワイルドカード(%または_)でないことを意味します.冒頭の様子.例えば、「SELECT peopleid FROM people WHERE firstname LIKE'Mich%」;このクエリはインデックスを使用しますが、「SELECT peopleid FROM people WHERE firstname LIKE'%ike';」このクエリはインデックスを使用しません.
インデックスは、特定の値を持つレコードをすばやく検索するために使用され、すべてのMySQLインデックスはBツリーとして保存されます.インデックスがない場合は、クエリを実行するときにMySQLは、必要に応じたレコードが見つかるまで、最初のレコードからテーブル全体のすべてのレコードをスキャンする必要があります.表の記録数が多ければ多いほど、この操作の代価は高くなります.検索条件となるカラムにインデックスが作成されている場合、MySQLはレコードをスキャンすることなく、ターゲットレコードの場所を迅速に取得できます.テーブルに1000個のレコードがある場合、インデックス検索レコードは、シーケンススキャンレコードよりも少なくとも100倍速くなります.
peopleというテーブルを作成したとします.
CREATE TABLE people ( peopleid SMALLINT NOT NULL,
name CHAR(50) NOT NULL );次に、1000個の異なるname値をpeopleテーブルに完全にランダムに挿入します.データファイルのname列には明確な順序はありません.名前列のインデックスを作成すると、MySQLはインデックス内の名前列をソートし、インデックス内の各項目について、MySQLは内部でデータファイルに実際に位置を記録するポインタを保存します.したがって、名前が「Mike」レコードに等しいpeopleidを検索する場合(SQLコマンドは「SELECT peopleid FROM people WHERE name='Mike';」)、MySQLは、nameのインデックスで「Mike」値を検索し、データファイル内の対応する行に直接移動し、その行のpeopleid(999)を正確に返すことができます.このプロセスでは、MySQLはローを1つ処理するだけで結果を返すことができます.「name」列のインデックスがない場合、MySQLはデータファイル内のすべてのレコード、すなわち1000レコードをスキャンします.MySQL処理が必要なレコードの数が少ないほど、タスクの完了速度が速くなることは明らかです.
二、索引の種類
MySQLでは、複数のインデックス・タイプを選択できます.
通常のインデックス:
これは最も基本的なインデックスタイプであり、一意性などの制限はありません.通常のインデックスは、次の方法で作成できます.
CREATE INDEX<インデックスの名前>ON tablename(列のリスト)などのインデックスを作成します.
ALTER TABLE tablename ADD INDEX[インデックスの名前](列のリスト)などのテーブルを変更します.
テーブルを作成するときに、CREATE TABLE tablename([...]などのインデックスを指定します.INDEX[インデックスの名前](列のリスト);
一意性インデックス:
このインデックスは、前の通常のインデックスとほぼ同じですが、インデックス列のすべての値が1回しか表示されません.つまり、一意でなければなりません.一意性インデックスは、次の方法で作成できます.
CREATE UNIQUE INDEX<インデックスの名前>ON tablename(列のリスト)などのインデックスを作成します.
表を変更します.たとえば、ALTER TABLE tablename ADD UNIQUE[インデックスの名前](列のリスト)です.
テーブルを作成するときに、CREATE TABLE tablename([...]などのインデックスを指定します.UNIQUE[インデックスの名前](列のリスト);
プライマリ・キー:
プライマリ・キーはユニークなインデックスですが、PRIMARY KEYとして指定する必要があります.AUTOを使ったことがあるならINCREMENTタイプの列は、プライマリ・キーなどの概念に詳しいかもしれません.プライマリ・キーは、一般的にテーブルの作成時に指定されます.たとえば、「CREATE TABLE tablename([...]PRIMARY KEY(列のリスト);「ただし、テーブルを変更することでプライマリ・キーを追加することもできます.たとえば、「ALTER TABLE tablename ADD PRIMARY KEY(列のリスト);「.各テーブルにはプライマリ・キーが1つしかありません.
全文索引:
MySQLは3.23.23版から全文インデックスと全文検索をサポートしています.MySQLでは全文インデックスのインデックスタイプはFULLTEXTです.フルテキストインデックスはVARCHARまたはTEXTタイプのカラムで作成できます.CREATE TABLEコマンドで作成するか、ALTER TABLEまたはCREATE INDEXコマンドで作成できます.大規模なデータセットの場合、ALTER TABLE(またはCREATE INDEX)コマンドによる全文インデックスの作成は、全文インデックス付きの空のテーブルにレコードを挿入するよりも高速です.この文書では、全文インデックスについては説明しません.詳細については、MySQL documentationを参照してください.
三、単列索引と多列索引
インデックスは、単一のカラムインデックスであってもよいし、複数のカラムインデックスであってもよい.この2つのインデックスの違いを具体的な例で説明します.次のようなpeopleテーブルがあるとします.
CREATE TABLE people ( peopleid
SMALLINT NOT NULL AUTO_INCREMENT,
firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) );次に、このpeopleテーブルに挿入したデータを示します.
このデータの断片には「Mikes」という名前の4人(うち2人はSullivans、2人はMcConnells)、17歳の2人、変わった名前のJoe Smithがあります.
この表の主な用途は、指定したユーザーの姓、名前、年齢に応じてpeopleidを返すことです.たとえば、Mike Sullivanという名前の17歳のユーザーのpeopleid(SQLコマンドはSELECT peopleid FROM people WHERE firstname='Mike'AND lastname='Sullivan'AND age=17)を検索する必要があります.MySQLがクエリーを実行するたびにテーブル全体をスキャンしたくないので、インデックスの運用を検討する必要があります.
まず、firstname、lastname、ageなどのインデックスを単一のカラムに作成することを考慮します.firstname列のインデックス(ALTER TABLE people ADD INDEX firstname)を作成すると、MySQLは、このインデックスを使用して、firstname='Mike'のレコードに検索範囲を迅速に制限し、この「中間結果セット」で他の条件の検索を行います.まず、lastnameが「Sullivan」に等しくないレコードを排除し、ageが17に等しくないレコードを排除します.レコードがすべての検索条件を満たすと、MySQLは最終的な検索結果を返します.
firstname列のインデックスが作成されたため、テーブルの完全なスキャンを実行するよりもMySQLの効率が向上しましたが、MySQLスキャンの記録数は実際に必要なものをはるかに上回っています.firstnameカラムのインデックスを削除してlastnameまたはageカラムのインデックスを作成することができますが、どのカラムでインデックス検索を作成しても効率は似ています.
検索効率を向上させるためには,マルチカラムインデックスの運用を検討する必要がある.firstname、lastname、ageの3つのカラムに複数のカラムインデックスを作成すると、MySQLは1回の検索で正しい結果を見つけることができます!次は、このマルチカラムインデックスを作成するSQLコマンドです.
ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);
インデックスファイルがBツリー形式で保存されているため、MySQLはすぐに適切なfirstnameに移動し、適切なlastnameに移動し、最後に適切なageに移動することができます.データファイルの記録をスキャンしていない場合、MySQLは検索対象の記録を正確に見つけました!
では、firstname、lastname、ageの3つのカラムにそれぞれ1つのカラムインデックスを作成すると、firstname、lastname、ageの複数のカラムインデックスを作成するのと同じ効果が得られますか?答えは否定的で、両者は全く違います.クエリを実行するとき、MySQLは1つのインデックスしか使用できません.3つの単列インデックスがある場合、MySQLは最も制限の厳しいインデックスを選択しようとします.しかし、最も厳格な単一カラムインデックスを制限しても、firstname、lastname、ageの3つのカラムの複数カラムインデックスよりも制限能力が低いに違いありません.
四、一番左の接頭辞
マルチカラムインデックスには、最左接頭辞(Leftmost Prefixing)と呼ばれる概念によって表現されるもう一つの利点があります.前の例を考えてみると、firstname、lastname、age列の複数の列インデックスがあります.このインデックスをfnameと呼びます.lname_age.検索条件が次の各列の組合せである場合、MySQLはfname_を使用します.lname_age索引:
firstname，lastname，age
firstname，lastname
firstname
別の側面から理解すると、(firstname,lastname,age)、(firstname,lastname)、および(firstname)これらのカラムの組合せのインデックスを作成したことに相当する.次のクエリでは、このfnameを使用できます.lname_age索引:
SELECT peopleid FROM people
WHERE firstname='Mike' AND lastname='Sullivan' AND age='17';
SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan';
SELECT peopleid FROM people WHERE firstname='Mike';
The following queries cannot use the index at all:
SELECT peopleid FROM people WHERE lastname='Sullivan';
SELECT peopleid FROM people WHERE age='17';
SELECT peopleid FROM people WHERE lastname='Sullivan' AND age='17';
五、索引列の選択
パフォーマンスの最適化では、インデックスを作成する列を選択することが最も重要なステップの1つです.インデックスを使用するには、主にWHERE句に現れる列、join句に現れる列の2種類が考えられます.次のクエリを参照してください.
SELECT age ##
FROM people WHERE firstname='Mike' ##
AND lastname='Sullivan' ##
このクエリは、前のクエリとは少し異なりますが、単純なクエリです.ageはSELECT部分で参照されるため、MySQLはカラム選択操作を制限しません.したがって、このクエリでは、ageカラムのインデックスを作成する必要はありません.より複雑な例を次に示します.
SELECT people.age, ##
town.name ##
FROM people LEFT JOIN town ON
people.townid=town.townid ##
WHERE firstname='Mike' ##
AND lastname='Sullivan' ##
前の例と同様にfirstnameとlastnameがWHERE句に表示されるため、この2つの列はインデックスを作成する必要があります.それ以外に、townテーブルのtownid列がjoin句に表示されるため、カラムのインデックスの作成を検討する必要があります.では,WHERE句とjoin句に出現する各列をインデックスすべきであると簡単に考えることができるだろうか.差は多くないが、完全ではない.カラムを比較するオペレータタイプも考慮する必要があります.MySQLでは、<,<=,=,>,>=,BETWEEN,IN,およびある時のLIKEを使用するオペレータのみが使用されます.LIKEオペレーションでインデックスを使用できる場合は、他のオペレーション数がワイルドカード(%または_)でないことを意味します.冒頭の様子.例えば、「SELECT peopleid FROM people WHERE firstname LIKE'Mich%」;このクエリはインデックスを使用しますが、「SELECT peopleid FROM people WHERE firstname LIKE'%ike';」このクエリはインデックスを使用しません.