インデックス作成によるMySQLクエリー速度の最適化
会社が使用する業務データベースはMySQLで、会社の業務の発展に従って、いくつかの表の中のデータはますます大きくなって、単表のデータ量が数十万を超えると、クエリーの速度は明らかに遅くなって、ユーザーの体験が低下します.そのため、データベース・クエリーの高速化が急がれています.インデックスを作成することでMySQLのクエリーの高速化を実現する方法を見てみましょう.
1つは、インデックスインデックスとは、特定の値を持つレコードをすばやく検索するために使用され、すべてのMySQLインデックスがBツリーとして保存されます.インデックスがない場合は、クエリを実行するときにMySQLは、必要に応じたレコードが見つかるまで、最初のレコードからテーブル全体のすべてのレコードをスキャンする必要があります.表の記録数が多ければ多いほど、この操作の代価は高くなります.検索条件となるカラムにインデックスが作成されている場合、MySQLはレコードをスキャンすることなく、ターゲットレコードの場所を迅速に取得できます.テーブルに1000個のレコードがある場合、インデックス検索レコードは、シーケンススキャンレコードよりも少なくとも100倍速くなります.
userというテーブルを作成したとします.
次に、1000個の異なるname値をpeopleテーブルに完全にランダムに挿入します.データファイルのname列には明確な順序はありません.名前列のインデックスを作成すると、MySQLはインデックス内の名前列をソートし、インデックス内の各項目について、MySQLは内部でデータファイルに実際に位置を記録するポインタを保存します.したがって、nameが「shiyong」レコードに等しいidを検索する場合(SQLコマンドは「SELECT id FROM user WHERE name='shiyong'」)、MySQLは、nameのインデックスで「shiyong」値を検索し、データファイル内の対応する行に直接移動し、行のidを正確に返すことができます.このプロセスでは、MySQLはローを1つ処理するだけで結果を返すことができます.「name」列のインデックスがない場合、MySQLはデータファイル内のすべてのレコード、すなわち1000レコードをスキャンします.MySQL処理が必要なレコードの数が少ないほど、タスクの完了速度が速くなることは明らかです.
二、どのようにインデックスを作成するかは簡単ですが、どのように必要なインデックスを作成するかは、考えなければなりません.インデックスがよくて、クエリーの速度を高めることができます.インデックスがよくないと、クエリーの速度を上げることができないだけでなく、データの新しい修正速度にも影響します.
パフォーマンスの最適化では、インデックスを作成する列を選択することが最も重要なステップの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';このクエリはインデックスを使用しません.
三つ目は、結合インデックスの作成は、単一のカラムインデックスであってもよいし、複数のカラムインデックスであってもよい.この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
別の側面から理解すると、(firstname,lastname,age)、(firstname,lastname)、および(firstname)これらのカラムの組合せのインデックスを作成したことに相当する.次のクエリでは、このfnameを使用できます.lname_age索引:
四、索引の使用上の注意事項1.インデックスにNULL値のカラムは含まれません
カラムにNULL値が含まれていてもインデックスに含まれない限り、複合インデックスにNULL値が含まれているカラムが1つある限り、この複合インデックスには無効です.したがって、データベース設計時にフィールドのデフォルト値をNULLにしないでください.
2.短い索引の使用
接頭辞の長さを指定する必要がある場合は、シリアル列をインデックスします.たとえば、CHAR(255)のカラムが1つある場合、最初の10文字または20文字以内に複数の数値が1つしかない場合は、カラム全体をインデックス化しないでください.短いインデックスを使用すると、クエリの速度が向上するだけでなく、ディスク容量とI/O操作を節約できます.
3.索引列のソート
MySQLクエリは1つのインデックスのみを使用するため、where句でインデックスが使用されている場合、order byのカラムはインデックスを使用しません.したがって、データベースのデフォルトのソートが要求に合致する場合は、ソート操作を使用しないでください.複数のカラムのソートはできるだけ含まないでください.必要に応じて、これらのカラムに複合インデックスを作成したほうがいいです.
4.like文の操作
一般的にlike操作は奨励されませんが、使用しなければならない場合は、どのように使用するかも問題です.like「%aaa%」はインデックスを使用しませんが、like「aaa%」はインデックスを使用できます.
5.列で演算しない
各ローで演算が行われ、インデックスが失効してテーブル全体がスキャンされるため、次のように変更できます.
6.NOT INおよび<>操作を使用しない
5、インデックスをまとめるとクエリーの速度が向上しますが、インデックスの使用が多すぎると乱用されます.したがって、インデックスにも欠点があります.1.インデックスはクエリの速度を大幅に向上させますが、INSERT、UPDATE、DELETEなどのテーブルの更新速度を低下させます.テーブルを更新するとき、MySQLはデータだけでなくインデックスファイルも保存します.
2.インデックスを作成すると、ディスク領域のインデックスファイルが使用されます.一般的にこの問題はそれほど深刻ではありませんが、大きなテーブルに複数の組合せインデックスを作成すると、インデックスファイルの膨張が速くなります.
インデックスは効率を向上させる要因の1つにすぎません.MySQLに大きなデータ量のテーブルがある場合は、最も優れたインデックスを作成したり、クエリー文を最適化したりするのに時間がかかります.
1つは、インデックスインデックスとは、特定の値を持つレコードをすばやく検索するために使用され、すべてのMySQLインデックスがBツリーとして保存されます.インデックスがない場合は、クエリを実行するときにMySQLは、必要に応じたレコードが見つかるまで、最初のレコードからテーブル全体のすべてのレコードをスキャンする必要があります.表の記録数が多ければ多いほど、この操作の代価は高くなります.検索条件となるカラムにインデックスが作成されている場合、MySQLはレコードをスキャンすることなく、ターゲットレコードの場所を迅速に取得できます.テーブルに1000個のレコードがある場合、インデックス検索レコードは、シーケンススキャンレコードよりも少なくとも100倍速くなります.
userというテーブルを作成したとします.
CREATE TABLE user ( id CHAR(36)NOT NULL, name CHAR(50) NOT NULL );次に、1000個の異なるname値をpeopleテーブルに完全にランダムに挿入します.データファイルのname列には明確な順序はありません.名前列のインデックスを作成すると、MySQLはインデックス内の名前列をソートし、インデックス内の各項目について、MySQLは内部でデータファイルに実際に位置を記録するポインタを保存します.したがって、nameが「shiyong」レコードに等しいidを検索する場合(SQLコマンドは「SELECT id FROM user WHERE name='shiyong'」)、MySQLは、nameのインデックスで「shiyong」値を検索し、データファイル内の対応する行に直接移動し、行のidを正確に返すことができます.このプロセスでは、MySQLはローを1つ処理するだけで結果を返すことができます.「name」列のインデックスがない場合、MySQLはデータファイル内のすべてのレコード、すなわち1000レコードをスキャンします.MySQL処理が必要なレコードの数が少ないほど、タスクの完了速度が速くなることは明らかです.
二、どのようにインデックスを作成するかは簡単ですが、どのように必要なインデックスを作成するかは、考えなければなりません.インデックスがよくて、クエリーの速度を高めることができます.インデックスがよくないと、クエリーの速度を上げることができないだけでなく、データの新しい修正速度にも影響します.
パフォーマンスの最適化では、インデックスを作成する列を選択することが最も重要なステップの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';このクエリはインデックスを使用しません.
三つ目は、結合インデックスの作成は、単一のカラムインデックスであってもよいし、複数のカラムインデックスであってもよい.この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.インデックスにNULL値のカラムは含まれません
カラムにNULL値が含まれていてもインデックスに含まれない限り、複合インデックスにNULL値が含まれているカラムが1つある限り、この複合インデックスには無効です.したがって、データベース設計時にフィールドのデフォルト値をNULLにしないでください.
2.短い索引の使用
接頭辞の長さを指定する必要がある場合は、シリアル列をインデックスします.たとえば、CHAR(255)のカラムが1つある場合、最初の10文字または20文字以内に複数の数値が1つしかない場合は、カラム全体をインデックス化しないでください.短いインデックスを使用すると、クエリの速度が向上するだけでなく、ディスク容量とI/O操作を節約できます.
3.索引列のソート
MySQLクエリは1つのインデックスのみを使用するため、where句でインデックスが使用されている場合、order byのカラムはインデックスを使用しません.したがって、データベースのデフォルトのソートが要求に合致する場合は、ソート操作を使用しないでください.複数のカラムのソートはできるだけ含まないでください.必要に応じて、これらのカラムに複合インデックスを作成したほうがいいです.
4.like文の操作
一般的にlike操作は奨励されませんが、使用しなければならない場合は、どのように使用するかも問題です.like「%aaa%」はインデックスを使用しませんが、like「aaa%」はインデックスを使用できます.
5.列で演算しない
select * from users where YEAR(adddate)<2007;各ローで演算が行われ、インデックスが失効してテーブル全体がスキャンされるため、次のように変更できます.
select * from users where adddate2007-01-01'; 6.NOT INおよび<>操作を使用しない
5、インデックスをまとめるとクエリーの速度が向上しますが、インデックスの使用が多すぎると乱用されます.したがって、インデックスにも欠点があります.1.インデックスはクエリの速度を大幅に向上させますが、INSERT、UPDATE、DELETEなどのテーブルの更新速度を低下させます.テーブルを更新するとき、MySQLはデータだけでなくインデックスファイルも保存します.
2.インデックスを作成すると、ディスク領域のインデックスファイルが使用されます.一般的にこの問題はそれほど深刻ではありませんが、大きなテーブルに複数の組合せインデックスを作成すると、インデックスファイルの膨張が速くなります.
インデックスは効率を向上させる要因の1つにすぎません.MySQLに大きなデータ量のテーブルがある場合は、最も優れたインデックスを作成したり、クエリー文を最適化したりするのに時間がかかります.