データベースクエリを最適化する10の方法

大部分のプログラマーがする一般的なものの1つは、データベース質問を書くことです.最小の期待は、書き込まれたクエリから期待される結果を得ることです.つまり、クエリを書いて、一度だけ実行したり、小さなデータセットで作業することを期待してください.これらのケースでは、クエリが効率的かどうかは問題ではありません.
しかししかし、あなたの質問がウェブサイトのためにライブデータをフェッチするか、あなたの会社の巨大なデータセットからAnalyticsで到着するために何度も実行されることになっているケースを打ったとき.ここであなたのクエリは、あなたの時間とコストに影響を与えます.最適化されたファッションでそれらを書くことは行く方法です.
大部分のデータベースエンジンは、可能な最も効果的な方法であなたの質問を解釈するか、実行する質問最適化を持っています.しかし、多くの場合、より良い結果をもたらすことができるいくつかの戦略があります.
私は最適化されたクエリを書くための10の方法を共有したい.このブログではPostgresクエリを例として使用しています.
1 .必要なものを尋ねてください
これに続いて、平和な生活を送るのに役立つだけでなく、より高速な問い合わせを構築することにも役立ちます.
ほとんどの時間は“SELECT * FROM”で問い合わせを開始することを誘惑しています.あなたが必要とする列について考える必要がないので、それは便利でありえます.しかし、利便性は、コストが付属しています.ここでのコストは、クエリが処理するデータ量です.
の代わりに

SELECT * FROM PAYMENT 

用途

SELECT AMOUNT,
    PAYMENT_DATE
FROM PAYMENT; 

これはこれらの2つのカラムだけをロードします.一方、*は不要なデータも処理します.だから一人で必要なものを求める!
2 . VSがあるところ
レコードを取得する条件を指定するために、WHEREとWAITを使用します.しかし、それらを使用して交換可能なコストがあります.WHEREは条件を満たすレコードをフェッチするが、HAVINGは全てのレコードを取り出し、それから条件を適用する.
の代わりに

SELECT PAYMENT_DATE, COUNT(AMOUNT)
FROM PAYMENT
GROUP BY PAYMENT_DATE
HAVING PAYMENT_DATE >= '04-02-2007';

用途

SELECT PAYMENT_DATE, COUNT(AMOUNT)
FROM PAYMENT
WHERE PAYMENT_DATE >= '04-02-2007'
GROUP BY PAYMENT_DATE;

集計結果に対処したときのみ使用し、どこで使用できません.さもなければ、WHERE句はより速いでしょう.
あなたは、全体の袋を一握りで知っているかもしれません
あなたがチェックするのが好きであるならば、あなたの条件に合っている記録があなたのシステムに存在するかどうかにかかわらず、あなたはちょうど代わりにサンプルレコードの存在をチェックします.
の代わりに

SELECT * FROM PAYMENT WHERE PAYMENT_DATE >= '01-01-2021'

用途

SELECT EXISTS 
(SELECT 1 
FROM PAYMENT 
WHERE PAYMENT_DATE >= '01-01-2021');

が最初に一致するレコードに衝突した場合に停止します.また、結果セットに取得するデータには煩わされません.したがって、select 1(例に示すように)/0/null/{ anyrow column }は動作し、クエリの実行や結果に影響しません.
結合対サブクエリ
副問い合わせは書き込み/読み出しが容易であるかもしれませんが、結合はサーバにより最適化されます.
の代わりに

SELECT P.AMOUNT, P.PAYMENT_DATE 
FROM PAYMENT P
WHERE PAYMENT_DATE >= '04-02-2007' 
AND P.CUSTOMER_ID
IN 
(SELECT C.CUSTOMER_ID 
FROM CUSTOMER C 
WHERE C.LAST_NAME = 'SOTO');

用途

SELECT P.AMOUNT, P.PAYMENT_DATE FROM PAYMENT P
JOIN CUSTOMER C
ON P.CUSTOMER_ID = C.CUSTOMER_ID
WHERE PAYMENT_DATE >= '04-02-2007' AND C.LAST_NAME = 'SOTO';

データエンジンの大部分は、サブクエリを別々のクエリとして評価します.内部クエリを最初に実行し、結果セットからすべての結果を取得します.一方、結合は結果を1回で生成します.
5 .既存のVS
joinクエリ内で個別のレコードを取得することに興味がある場合は、既存のExceptionを使用するよりもClearlierを使用します.
の代わりに

SELECT DISTINCT C.FIRST_NAME
FROM CUSTOMER C
JOIN PAYMENT P ON P.CUSTOMER_ID = C.CUSTOMER_ID
WHERE AMOUNT = 4.99;

用途

SELECT C.FIRST_NAME FROM CUSTOMER C
WHERE EXISTS (SELECT 1 FROM PAYMENT P
              WHERE P.CUSTOMER_ID = C.CUSTOMER_ID
              AND AMOUNT = 4.99);

明確に、重複した行は最初にすべてのフェッチ結果をソートし、ソート結果から一意のものだけを返すことによって抑制されます.このソート操作はかなり高価であり、この場合、存在を使用して回避することができます.存在はちょうどサブクエリによって返される行の存在のためにチェックします、最初の出来事は考慮されるだけです、そして、ユニークなリストは1つの試みにおいて形成されます.高価なソート操作が必要でないので、存在は明白であるより優先される.
ユニオンvsユニオン
あなたが重複したレコードを心配していない場合は、組合の代わりにすべての組合のために行く!
の代わりに

SELECT  FIRST_NAME, LAST_NAME, 'ACTOR' AS ROLE  FROM ACTOR
UNION
SELECT FIRST_NAME, LAST_NAME, 'CUSTOMER' AS ROLE FROM CUSTOMER;

用途

SELECT FIRST_NAME, LAST_NAME, 'ACTOR' AS ROLE FROM ACTOR
UNION ALL
SELECT FIRST_NAME, LAST_NAME, 'CUSTOMER' AS ROLE FROM CUSTOMER;

ユニオンを使用することによって、我々は高価なソート操作を避けているので、ユニオンはすべてユニオンよりよく実行します.ソートは、内部的にunionによって引き起こされる別個の操作によって行われます.
7 .要求に応じてレコードを取得するインデックス
インデックスを頻繁にレコードを取得する場合は非常に便利です.あなたは頻繁に検索される膨大な列の情報を持つ行の数が少ないのを探しているなら、インデックスはあなたの仕事を速くする!
あなたが頻繁にタイトルを使用して映画のリストを取得する必要がある場合は言う.

SELECT * 
FROM FILM 
WHERE TITLE LIKE '{any_film_prefix}%';

常に時間がかかるシーケンシャルスキャンを行います.それで、あなたがホットフィールド' title 'にインデックスを作成するならば、

CREATE AN INDEX ON FILM(TITLE);

同じクエリははるかに速くなります.
クエリプランナーは、シーケンシャルスキャンの代わりにインデックススキャンを使用します.同様に複数の列にインデックスを作成できます.
注:クエリプランナーがインデックススキャンまたはシーケンシャルスキャンを使用しているかどうかを確認できます.

EXPLAIN ANALYSE SELECT * FROM FILM WHERE TITLE LIKE 'Bird%';

インデックスに入る前に留意すべき点はいくつかあります.

インデックススキャンは、取得された行の数が巨大ではない場合に発生します.そうであれば、クエリプランナーの大部分はインデックス付きおよび逐次スキャンを比較し、後者を対象にします.

SELECT *
FROM FILM
WHERE TITLE LIKE '%b%';

これらの多くの記録のためにインデックススキャンをして、それから記録を検索するのではなく、順次巨大なレコードセットを移動させるのは、より意味があります.

インデックススキャンは、インデックスされたキーに適用されるSQL機能で働きません.
以下のクエリはインデックススキャンを実行します.

SELECT *
FROM FILM
WHERE FILM_ID BETWEEN 20 AND 30;

以下のクエリはmod関数のためにシーケンシャルスキャンを実行します.

SELECT *
FROM FILM
WHERE MOD(FILM_ID,2) = 0;

インデックスは、コストが付属しています.索引を更新するためのインデックスとメンテナンスコストを格納するためのスペースコストがあります.

8 .クロス結合を使用する
この結合は、それ自体を必要としないかもしれないデカルト製品に終わります.あなたが映画や俳優に参加していると言う.

SELECT FILM.NAME, ACTOR.*
FROM FILM, ACTOR
WHERE ACTOR.ACTOR_ID = FILM.ACTOR_ID;

フィルムと俳優テーブルの各々に100のレコードがあるならば、それは最初に10000の記録(1つの映画記録のためにすべての100のActor Recordsが存在します)を形成します、そして、それはマッチしているActorKid IDを持っている記録を濾過します.
の代わりに

SELECT FILM.NAME, ACTOR.*
FROM FILM
CROSS JOIN ACTOR
WHERE ACTOR.ACTOR_ID = FILM.ACTOR_ID;

用途

SELECT FILM.NAME, ACTOR.*
FROM FILM
INNER JOIN ACTOR
WHERE ACTOR.ACTOR_ID = FILM.ACTOR_ID;

これは1つの移動で必要な100レコードだけを形成します.
注:クロス結合と'、'デカルトの製品結果セットを得るために使用することができます.
実体化された見解
より多くの結合または複雑な選択値でビューを使用している場合、ビューの基になるデータは頻繁に変更されません.

CREATE MATERIALIZED VIEW AVAILABLE_FILMS AS
SELECT ARRAY_AGG(F.TITLE) AS FILMS,
    C.NAME AS CATEGORY,
    COUNT(F.TITLE) AS NUMBER_OF_FILMS
FROM FILM F
INNER JOIN FILM_CATEGORY FC ON F.FILM_ID = FC.FILM_ID
INNER JOIN CATEGORY C ON FC.CATEGORY_ID = C.CATEGORY_ID
GROUP BY CATEGORY
HAVING COUNT(F.TITLE) > 15
ORDER BY COUNT(F.TITLE) DESC;

クエリ内で実体化されたビューが参照されると、データは単一のテーブルのように、実体化ビューから直接返されます.したがって、データが取得されるたびに、結合または選択のオーバーヘッドが発生しません.

SELECT *
FROM AVAILABLE_FILMS;

ここでのコストは、更新されたテーブルの値で具体化されたビューを維持/リフレッシュです.具体的なビューを定期的にトリガまたはコミットの変更を介して更新することができます.

REFRESH MATERIALIZED VIEW AVAILABLE_FILMS;

10 .頻繁に使用されるクエリの回避または条件
最初の名前が' GivenCage Input }で始まる顧客を得ることに興味があるか、またはメールアドレスが{ Jivenage Input }から始まるかに興味があると言ってください.'接尾辞'
例えば、与えられた入力はキムです

SELECT FIRST_NAME,
    LAST_NAME,
    EMAIL
FROM CUSTOMER
WHERE FIRST_NAME LIKE 'Kim%'
OR EMAIL Like 'kim%.org';

また、このクエリは頻繁に実行されます.したがって、両方の列にインデックスを作成するつもりです.

CREATE INDEX ON CUSTOMER(FIRST_NAME, EMAIL);

しかし、これはうまくいきません、問い合わせプランナーはまだ連続的な走査を使用するでしょう.
この場合、別々の列だけで別々のインデックスを作成するのは助けません.より良いパフォーマンスアプローチは、クエリを分割し、別のインデックスと一緒にunionでそれらを実行されます.

CREATE INDEX ON CUSTOMER(EMAIL);

CREATE INDEX ON CUSTOMER(FIRST_NAME);

SELECT FIRST_NAME,
    LAST_NAME,
    EMAIL
FROM CUSTOMER
WHERE EMAIL LIKE 'kim%.org'
UNION
SELECT FIRST_NAME,
    LAST_NAME,
    EMAIL
FROM CUSTOMER
WHERE FIRST_NAME LIKE 'Kim%';

このようにして、WHERE句に追加する別の列を収容できます.あなたが重複した記録を気にしないならば、言及されるように、より多くのPerformantであるユニオンAllと組合を置きます.
それは今のところ!次回のSQLクエリを書くときにこれらのヒントを考えて、あなたのユースケースを考えて、賢明なアプローチを選択してください.
ハッピーラーニング!