遅いクエリ?データベースインデックスによる10 xクエリ性能

良いデータベースインデックスimprove your SQL query speeds by 99% or more .
10億、16バイトの名前とディスクを持つテーブルを取りましょうa 10ms seek time and a 10MB/s transfer rate .
このテーブルに「John Smith」を見つけたいならば、シーケンシャル順序ですべての単一の名前をチェックしなければならない定期的な検索は2時間かかります.
データベースインデックスを使用して同じ検索を行うには、わずか0.3秒(時間は10 msを求めます.99.99 %の速度向上.
しかし、データベースインデックスも増加したオーバーヘッドを使用しますcan degrade performance if not used correctly .
この記事では、データベースの正しいインデックスを作成するための主な考慮事項を説明します

インデックス

正しいカラムの選択

作成するインデックスの選択

以下の例と一緒に従って、独自のデータベース上のインデックスを作成します.

データベースインデックスとは

データベースインデックスの例
データベースインデックスは、データを簡単に検索するためにデータを整理するために使用される構造です.
インデックスは一連のタプルから成ります.最初のタプル値は検索キーで、2番目はハードディスク上のブロックへのポインタであり、データの全体の行が格納されます.
次に、これらのタプルは、データベースインデックス型に応じて異なるデータ構造(すなわちB - tree、ハッシュなど)に編成されます.
ツリーデータ構造が検索性能を上げる方法を理解するためにinteractive visualizations オンライン.

どのPostgresインデックスタイプを使用するべきですか？

Postgresは異なるユースケースを解決するために6種類のインデックスタイプを提供しています.
ここに、彼らの長所と短所の内訳があります.
種類
パフォーマンス
いつ使用するか.
B - tree (最も一般的)
o ( log ( n ))挿入とクエリ
を使用することができます.
ハッシュ
o ( 1 )
等価性比較のためだけに動作します.ハッシュインデックスはPostgresthey can product inaccurate results
一般化探索木(GIST)
挿入と問い合わせのためのo ( log ( n ))
幾何学的データ型に対する等値と範囲比較を超えた演算に使用されます.
空間分割GIST(SP‐GIST)
挿入と問い合わせのためのo ( log ( n ))
Non-balanced, disk-based data structures (i.e. quad-trees, k-d trees)
一般化反転指数
o ( log ( n )クエリ用).長い挿入時間.
複数の値を1行(すなわち配列と全文検索)にマップするデータ型のインデックス
ブロックレンジインデックス
20X faster than B-tree and a 99%+ space savings
テーブルエントリは、ディスク上のデータと同じ形式で注文する必要があります

正しいデータベースインデックスの選択

インデックスを作成すると、より良いデータベースパフォーマンスが保証されません.インデックスを使用してテーブルに書き込むたびに、データベースエンジンはテーブルと任意の影響インデックスの両方を更新します.
これにより、インデックスに使用するテーブル列を決定できます.

頻繁に問い合わせされますが、頻繁に変更されない列を選択します.

カラムには参照整合性制約があります

列には一意のキー完全性制約があります.

すべての近代的なデータベースエンジンはまた、各クエリが実行される方法を決定するクエリプランナーを持っています.いくつかのシナリオでは、インデックスを使用することを期待するクエリは、実際にシーケンシャルスキャンを行うことが可能です.To check if the query plan is using your index, you can run EXPLAIN .
例えば、走るEXPLAIN on this example インデックスの代わりにシーケンシャルスキャンを使用していることを示します.

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

                         QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on tenk1 (cost=0.00..483.00 rows=7001 width=244)
   Filter: (unique1 < 7000)

どのように多くのインデックスを使用する必要がありますか？

それは違う.頻繁に変更されたテーブルを管理している場合は、インデックスのオーバヘッドが少ないでしょう.しかし、一方で、ほとんどの場合は、テーブルから読み取り、追加のインデックスを追加すると、おそらくパフォーマンスをスピードアップすることです.
新しいインデックスを追加する前にcheck if your current indexes are actually slowing down your CPU .

Postgresでインデックスを作成する方法

Postgresインデックスの例は次のセクションで提供されます.この記事の範囲では、例を示します

インデックスを作成します.

インデックスを削除します.

リストインデックス:インデックスをすべて一覧表示します.ユニークなインデックス:ユニークなインデックスを定義する.

ユニークなインデックス:ユニークなインデックスを定義する.

インデックスの作成方法

CREATE INDEX index_name ON table_name USING [method]
(
column_name [ASC | DESC] [NULLS {FIRST | LAST }],
...
);

オプションASC/DESC and NULLS LAST パラメータはソート順序で取得する際に計画するデータに対して有益です.

部分インデックスの作成方法

また、create an index on only a subset of a table .

CREATE INDEX employee_index ON employees (employee_id)
    WHERE employee_id > 200; 

部分インデックスは、同じインデックス値を持つデータの大きなクラスタがある場合に有益です.このデータがインデックスされていても、Postgresクエリプランナーは通常、データが同じ値を持つため、シーケンシャル検索を使用します.したがって、部分インデックスは同じインデックス値とスペースを保存するデータのクラスタを削除できます.

インデックスの削除方法

DROP INDEX index_name;

既存のインデックスを見つける方法

Postgresは自動的にpg_indexes データベース内の既存のインデックスを検索するクエリを実行するテーブルです.

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = 'employees';

Postgres reindex説明

Reindex テーブル内の既存のインデックスを削除し、現在のテーブル値を使用して再構築します.REINDEXを使用するための最も一般的なシナリオは、データが大幅に変更されたときですthere are now existing pages that are inefficiently using space .
あなたのデータベースのルーチンのreindexingは、インデックスサイズを減らして、パフォーマンスを改善することができます.

## Rebuild a specific index
REINDEX INDEX my_index;

## Rebuild every index in a table
REINDEX TABLE my_table;

## Rebuild every index in a database 
REINDEX DATABASE my_database;

持ち帰り

適切に作成されたデータベースインデックスは99 %以上のクエリ性能を向上させることができます.
この記事では、データベースのインデックスを作成するための主な考慮事項を取り上げました.

インデックス

正しいカラムの選択

作成するインデックスの選択

今すぐあなたのクエリのパフォーマンスを最適化して、それは時間ですspeed up your SQL workflow . アークタイプcollaborative SQL client 簡単にデータベース、クエリ、および誰とでも視覚化を共有することができます.