mysqlデータベースインデックスを追加し、実行計画を観察
3290 ワード
最も一般的な場合は、where句に表示されるフィールドにインデックスを作成します.簡単に説明するために、まず以下の表を作成します.
私たちが使っている文がSELECT*FROM mytable WHERE category_だと仮定します.id=1最も直接的な対応の道はcategory_id簡単なインデックスCREATE INDEX mytable_を作成するcategoryid ON mytable (category_id); もしあなたが1つ以上の選択条件を持っていたら?例:SELECT*FROM mytable WHERE category_id=1 AND user_id=2; 解決策は、複数のインデックスCREATE INDEX mytableを追加することです.categoryid_userid ON mytable (category_id,user_id); 「テーブル名_フィールド1名_フィールド2名」のネーミング方法を使用します.適切なフィールドにインデックスを作成しましたが、少し不安でしょう.データベースは本当にこれらのインデックスを使用しているのでしょうか.テストすればOKです.ほとんどのデータベースでは、EXPLAINを使用して計画コマンドを実行すれば簡単です.EXPLAIN SELECT*FROM mytable WHERE category_id=1 AND user_id=2;
This is what Postgres 7.1 returns (exactly as I expected) NOTICE: QUERY PLAN: Index Scan using mytable_categoryid_userid on mytable(cost=0.00...2.02 rows=1 width=16)次に、少し複雑なものをください.ORDER BYの文があれば?ほとんどのデータベースはorder byを使用する場合、インデックスから利益を得ます.SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY adddate DESC; 簡単です.where文のフィールドにインデックスを作成するように、ORDER BYのフィールドにもインデックスを作成します.CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate); 注意:「mytable_categoryid_userid_adddate」は「mytable_categoryid_userid_addda」EXPLAIN SELECT*FROM mytable WHERE category_に短縮されます.id=1 AND user_id=2 ORDER BY adddate DESC;
NOTICE: QUERY PLAN: Sort (cost=2.03…2.03 rows=1 width=16) -> Index Scan using mytable_categoryid_userid_addda on mytable(cost=0.00...2.02 rows=1 width=16)EXPLAINの出力を見てみると、少し怖いようですね.データベースは私たちが要求していないソートを多くしています.これで性能がどのように損なわれたかがわかります.私たちはデータベースの自身の動作に楽観的すぎるようです.では、データベースにヒントをあげましょう.
ソートをスキップするには、クエリー文を少し変更するだけで、他のインデックスは必要ありません.ここではpostgresを使用します.ORDER BY文にwhere文のフィールドを追加する追加のヒントをデータベースに与えます.これは技術的な処理にすぎず、必要ではありません.実際には他の2つのフィールドではソート操作はありませんが、追加するとpostgresはそれがすべきことを知っています.
EXPLAIN SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY category_id DESC,user_id DESC,adddate DESC;
NOTICE: QUERY PLAN:
Index Scan Backward using mytable_categoryid_userid_addda on mytable (cost=0.00…2.02 rows=1 width=16)
EXPLAIN
予想されたインデックスが使用され、インデックスの後ろから読むことができることを知っていて、ソートを避けることができます.
以上詳しく説明しましたが、もしあなたのデータベースが非常に巨大で、毎日のページ要求が百万に達したら、あなたは多くの利益を得ることができると思います.しかし、より複雑なクエリーを行う場合は、たとえば複数のテーブルを結合してクエリーを行います.特にwhere制限文のフィールドが1つ以上のテーブルから来た場合、どのように処理すればいいですか.私は通常、データベースが各テーブルのものを結合し、不適切なローを排除するため、コストがかかります.
避けられない場合は、結合する各テーブルを表示し、以上のポリシーを使用してインデックスを作成し、EXPLAINコマンドで予想通りのインデックスが使用されているかどうかを確認する必要があります.もしそうなら、OKです.そうでなければ、一時的なテーブルを作成して結合し、適切なインデックスを使用することができます.
インデックスの作成が多すぎると、各インデックスファイルを同じように更新する必要があるため、更新と挿入の速度に影響します.常に更新と挿入が必要なテーブルでは、あまり使用されていないwhere文にインデックスを個別に作成する必要はありません.比較的小さなテーブルでは、ソートのオーバーヘッドは大きくなく、別のインデックスを作成する必要もありません.
以上紹介したのはただいくつかの非常に基本的なもので、実は中の学問も少なくなくて、EXPLAINだけで私たちはこの方法が最適化されているかどうかを判定することができなくて、すべてのデータベースはすべて自分のいくつかのオプティマイザがあって、まだあまり完備していないかもしれませんが、しかしそれらはすべて検索する時にどの方式が比較的に速くて、いくつかの情況の下で、インデックスを創立するのは必ずしも速くありません.たとえば、インデックスが不連続なストレージスペースに配置されると、読み取りディスクの負担が増加するため、どちらが最適かは、実際の使用環境で確認する必要があります.
最初は、テーブルが大きくなければインデックスを作成する必要はありません.必要なときにインデックスを作成する必要があります.MySQLでは「OPTIMIZE TABLE」などのコマンドでテーブルを最適化することもできます.
上記のように、データベースに適切なインデックスを作成する方法については、基本的な概念があります.
CREATE TABLE mytable (
id serial primary key,
category_id int not null default 0,
user_id int not null default 0,
adddate int not null default 0
);
私たちが使っている文がSELECT*FROM mytable WHERE category_だと仮定します.id=1最も直接的な対応の道はcategory_id簡単なインデックスCREATE INDEX mytable_を作成するcategoryid ON mytable (category_id); もしあなたが1つ以上の選択条件を持っていたら?例:SELECT*FROM mytable WHERE category_id=1 AND user_id=2; 解決策は、複数のインデックスCREATE INDEX mytableを追加することです.categoryid_userid ON mytable (category_id,user_id); 「テーブル名_フィールド1名_フィールド2名」のネーミング方法を使用します.適切なフィールドにインデックスを作成しましたが、少し不安でしょう.データベースは本当にこれらのインデックスを使用しているのでしょうか.テストすればOKです.ほとんどのデータベースでは、EXPLAINを使用して計画コマンドを実行すれば簡単です.EXPLAIN SELECT*FROM mytable WHERE category_id=1 AND user_id=2;
This is what Postgres 7.1 returns (exactly as I expected) NOTICE: QUERY PLAN: Index Scan using mytable_categoryid_userid on mytable(cost=0.00...2.02 rows=1 width=16)次に、少し複雑なものをください.ORDER BYの文があれば?ほとんどのデータベースはorder byを使用する場合、インデックスから利益を得ます.SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY adddate DESC; 簡単です.where文のフィールドにインデックスを作成するように、ORDER BYのフィールドにもインデックスを作成します.CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate); 注意:「mytable_categoryid_userid_adddate」は「mytable_categoryid_userid_addda」EXPLAIN SELECT*FROM mytable WHERE category_に短縮されます.id=1 AND user_id=2 ORDER BY adddate DESC;
NOTICE: QUERY PLAN: Sort (cost=2.03…2.03 rows=1 width=16) -> Index Scan using mytable_categoryid_userid_addda on mytable(cost=0.00...2.02 rows=1 width=16)EXPLAINの出力を見てみると、少し怖いようですね.データベースは私たちが要求していないソートを多くしています.これで性能がどのように損なわれたかがわかります.私たちはデータベースの自身の動作に楽観的すぎるようです.では、データベースにヒントをあげましょう.
ソートをスキップするには、クエリー文を少し変更するだけで、他のインデックスは必要ありません.ここではpostgresを使用します.ORDER BY文にwhere文のフィールドを追加する追加のヒントをデータベースに与えます.これは技術的な処理にすぎず、必要ではありません.実際には他の2つのフィールドではソート操作はありませんが、追加するとpostgresはそれがすべきことを知っています.
EXPLAIN SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY category_id DESC,user_id DESC,adddate DESC;
NOTICE: QUERY PLAN:
Index Scan Backward using mytable_categoryid_userid_addda on mytable (cost=0.00…2.02 rows=1 width=16)
EXPLAIN
予想されたインデックスが使用され、インデックスの後ろから読むことができることを知っていて、ソートを避けることができます.
以上詳しく説明しましたが、もしあなたのデータベースが非常に巨大で、毎日のページ要求が百万に達したら、あなたは多くの利益を得ることができると思います.しかし、より複雑なクエリーを行う場合は、たとえば複数のテーブルを結合してクエリーを行います.特にwhere制限文のフィールドが1つ以上のテーブルから来た場合、どのように処理すればいいですか.私は通常、データベースが各テーブルのものを結合し、不適切なローを排除するため、コストがかかります.
避けられない場合は、結合する各テーブルを表示し、以上のポリシーを使用してインデックスを作成し、EXPLAINコマンドで予想通りのインデックスが使用されているかどうかを確認する必要があります.もしそうなら、OKです.そうでなければ、一時的なテーブルを作成して結合し、適切なインデックスを使用することができます.
インデックスの作成が多すぎると、各インデックスファイルを同じように更新する必要があるため、更新と挿入の速度に影響します.常に更新と挿入が必要なテーブルでは、あまり使用されていないwhere文にインデックスを個別に作成する必要はありません.比較的小さなテーブルでは、ソートのオーバーヘッドは大きくなく、別のインデックスを作成する必要もありません.
以上紹介したのはただいくつかの非常に基本的なもので、実は中の学問も少なくなくて、EXPLAINだけで私たちはこの方法が最適化されているかどうかを判定することができなくて、すべてのデータベースはすべて自分のいくつかのオプティマイザがあって、まだあまり完備していないかもしれませんが、しかしそれらはすべて検索する時にどの方式が比較的に速くて、いくつかの情況の下で、インデックスを創立するのは必ずしも速くありません.たとえば、インデックスが不連続なストレージスペースに配置されると、読み取りディスクの負担が増加するため、どちらが最適かは、実際の使用環境で確認する必要があります.
最初は、テーブルが大きくなければインデックスを作成する必要はありません.必要なときにインデックスを作成する必要があります.MySQLでは「OPTIMIZE TABLE」などのコマンドでテーブルを最適化することもできます.
上記のように、データベースに適切なインデックスを作成する方法については、基本的な概念があります.