データベースのインデックスを作成する (2)

データ|データベース|インデックス

次に、もう少し複雑に 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"

CREATE

EXPLAIN SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;

注意:クエリプラン:

ソート(コスト=2.03) ..2.03 rows=1 width=16)
-> mytable_categoryid_userid_addda を使用した Index Scan
on mytable (cost=0.00..2.02 rows=1 width=16)

EXPLAIN

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:


_categoryid_userid_addda を使用して逆方向にインデックス スキャンmytable 上
(コスト=0.00 ..2.02 rows=1 width=16)

EXPLAIN

は、私たちが期待していたインデックスを使用するようになり、インデックスの後ろから読み取りを開始できることを認識して、並べ替えを回避するのに十分な賢さを備えています。

上記は少し詳細ですが、データベースが非常に巨大で、毎日のページリクエストが数百万件に達する場合、大きなメリットがあると思います。ただし、複数のテーブルを組み合わせてクエリを実行するなど、より複雑なクエリを実行する場合、特に where 制限句のフィールドが複数のテーブルから取得されている場合はどうすればよいでしょうか?データベースは各テーブルのすべてを結合してから不適切な行を除外する必要があり、非常にコストがかかる可能性があるため、私は通常このアプローチを避けるようにしています。

それが避けられない場合は、結合する各テーブルを確認し、上記の戦略を使用してインデックスを作成し、EXPLAIN コマンドを使用して、期待したインデックスが使用されているかどうかを確認する必要があります。そうであれば、OK。そうでない場合は、一時テーブルを作成してそれらを結合し、適切なインデックスを使用することをお勧めします。

各インデックスファイルを均等に更新する必要があるため、インデックスを作成しすぎると、更新と挿入の速度に影響することに注意してください。頻繁に更新と挿入が必要なテーブルの場合、めったに使用されない where 句用に別のインデックスを作成する必要はありません。小さなテーブルの場合、並べ替えのオーバーヘッドはそれほど大きくなく、追加のインデックスを作成する必要もありません。 。

上記は非常に基本的なものにすぎませんが、実際には、EXPLAIN だけでそのメソッドが最適化されているかどうかを判断することはできません。完璧ではありますが、クエリ中にどちらの方法が速いかを比較すると、たとえば、インデックスが不連続なストレージ領域に配置されている場合、ディスクの読み取りの負荷が増加することがあります。どちらが最適であるかは、実際の使用環境を通してテストする必要があります。

最初は、テーブルが大きくない場合は、インデックスを作成する必要はありません。MySQL の「OPTIMIZE TABLE」などのいくつかのコマンドを使用して、必要な場合にのみインデックスを作成することもできます。

要約すると、データベースに適切なインデックスを作成する方法について、いくつかの基本概念を理解する必要があります。