SQL クエリの最適化手法

プラトン再発行

フォロワー： 0

SQL クエリの最適化手法
著者による画像

初心者レベルでは、SQL クエリを作成して実行することだけに集中します。実行にかかる時間や、数百万のレコードを処理できるかどうかは気にしません。しかし、中間レベルでは、人々はクエリが最適化され、実行にかかる時間が最小限であることを期待しています。

e コマースプラットフォームや銀行システムなど、何百万ものレコードを含む大規模なアプリケーションで最適化されたクエリを作成することは不可欠です。 XNUMX 万を超える製品を扱う電子商取引会社を所有していて、顧客が製品を検索したいとします。バックエンドで作成したクエリがデータベースからその製品を取得するのに XNUMX 分以上かかる場合はどうなるでしょうか? 顧客はあなたのウェブサイトから商品を購入すると思いますか?

SQL クエリの最適化の重要性を理解する必要があります。このチュートリアルでは、SQL クエリを最適化し、より高速に実行するためのヒントとコツを紹介します。主な前提条件は、SQL の基本的な知識があることです。

特定の要素がテーブルに存在するかどうかを確認するには、 EXIST() の代わりにキーワード COUNT() より最適化された方法でクエリを実行します。

使い方 COUNT()、クエリはその特定の要素のすべての出現をカウントする必要がありますが、データベースが大規模な場合は非効率になる可能性があります。一方で、 EXIST() その要素の最初の出現のみをチェックし、最初の出現が見つかったときに停止します。これにより、多くの時間を節約できます。

また、特定の要素が存在するかどうかを調べることにのみ関心があります。出現回数を見つけることに関心がありません。だからこそまた EXIST() 優れています。

SELECT EXISTS( SELECT * FROM table WHERE myColumn = 'val' );

上記のクエリは次を返します 1 少なくとも XNUMX つのテーブル行に、名前が付けられた列のエントリが含まれている場合 myColumn に等しい値を持つ ヴァル。それ以外の場合は、 0.

両方 char および varchar データ型は、文字列をテーブルに格納するために使用されます。しかし varchar よりもはるかにメモリ効率が良い char.

char データ型は、定義された固定長の文字列のみを格納できます。文字列の長さが固定長よりも短い場合は、設定された長さに等しくなるように空白を埋めます。これにより、パディングでメモリが不必要に浪費されます。例えば、CHAR(100) 100 文字格納しても XNUMX バイトのメモリを消費します。

一方、varchar データ型は、指定された最大長未満の長さの可変長文字列を格納します。空白を埋めず、文字列の実際の長さに等しいメモリのみを使用します。例えば、 VARCHAR(100) 単一の文字を格納する場合、1 バイトのメモリしか必要としません。

CREATE TABLE myTable ( id INT PRIMARY KEY, charCol CHAR(10), varcharCol VARCHAR(10)
);

上記の例では、テーブル myTable は XNUMX つの列で作成され、 charCol および varcharCol それぞれ char および varchar データ型を持ちます。 charCol 常に 10 バイトのメモリを使用します。対照的に、 varcharCol 格納されている文字列の実際のサイズに等しいメモリを使用します。

SQL クエリを最適化するために、WHERE 句内でサブクエリを使用しないようにする必要があります。サブクエリは、多数の行を返す場合、コストがかかり、実行が困難になる可能性があるためです。

サブクエリを使用する代わりに、結合操作を使用するか、相関サブクエリを記述して、同じ結果を得ることができます。相関サブクエリは、内部クエリが外部クエリに依存するサブクエリです。また、非相関サブクエリと比較して非常に効率的です。

以下は、両者の違いを理解するための例です。

# Using a subquery
SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE country = 'INDIA' ); # Using a join operation
SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.country = 'INDIA';

最初の例では、サブクエリは最初に INDIA に属するすべての顧客 ID を収集し、次に外側のクエリが選択された顧客 ID のすべての注文を取得します。 1 番目の例では、 customers および orders テーブルを作成し、顧客がインドに属している注文のみを選択します。

このようにして、WHERE 句内でのサブクエリの使用を回避し、読みやすく理解しやすくすることで、クエリを最適化できます。

適用 JOIN 大きなテーブルから小さなテーブルへの操作は、一般的な SQL 最適化手法です。大きなテーブルから小さなテーブルに結合すると、クエリの実行が速くなるためです。適用する場合 JOIN 小さなテーブルから大きなテーブルへの操作では、SQL エンジンは一致する行を大きなテーブルで検索する必要があります。これは、より多くのリソースを消費し、時間がかかります。しかし一方で、 JOIN 大きなテーブルから小さなテーブルに適用される場合、SQL エンジンは小さなテーブルで一致する行を検索する必要があります。

理解を深めるための例を次に示します。

# Order table is larger than the Customer table # Join from a larger table to a smaller table
SELECT * FROM Order JOIN Customer ON Customer.id = Order.id # Join from a smaller table to a larger table
SELECT * FROM Customer JOIN Order ON Customer.id = Order.id

異なり、 LIKE 句、 regexp_like パターン検索にも使用されます。の LIKE 句は、次のような基本的な操作のみを実行できる基本的なパターンマッチング演算子です。 _ or %、それぞれ単一の文字または任意の数の文字に一致するために使用されます。の LIKE 句は、特定のパターンを見つけるためにデータベース全体をスキャンする必要がありますが、大きなテーブルでは時間がかかります。

一方、 regexp_like は、より効率的で最適化された強力なパターン検索手法です。より複雑な正規表現を使用して、文字列内の特定のパターンを見つけます。これらの正規表現は、検索対象の正確なパターンを検索できるため、単純なワイルドカードマッチングよりも具体的です。これにより、検索する必要のあるデータの量が減り、クエリの実行が速くなります。

ということに注意してください regexp_like すべてのデータベース管理システムに存在するとは限りません。その構文と機能は、他のシステムでは異なる場合があります。

理解を深めるための例を次に示します。

# Query using the LIKE clause
SELECT * FROM mytable WHERE ( name LIKE 'A%' OR name LIKE 'B%' ); # Query using regexp_like clause
SELECT * FROM mytable WHERE regexp_like(name, '^[AB].*');

上記のクエリは、名前が A または B で始まる要素を見つけるために使用されます。最初の例では、 LIKE A または B で始まるすべての名前を検索するために使用されます。 A% 最初の文字が A であることを意味します。その後、任意の数の文字が存在できます。 XNUMX 番目の例では、 regexp_like 使用されている。中身 ^[AB], ^ 記号が文字列の先頭に一致することを表し、 [AB] 先頭文字が A または B であることを表し、 .* その後のすべての文字を表します。

使い方 regexp_like、データベースはパターンに一致しない行をすばやく除外できるため、パフォーマンスが向上し、リソースの使用量が削減されます。

この記事では、SQL クエリを最適化するためのさまざまな方法とヒントについて説明しました。この記事では、効率的な SQL クエリを作成する方法と、それらを最適化することの重要性を明確に理解できます。クエリを最適化する方法は他にもたくさんあります。たとえば、文字ではなく整数値の使用を優先したり、テーブルに重複が含まれていない場合に Union の代わりに Union All を使用したりするなどです。

アーリア人ガーグ B.Techです。電気工学科の学生で、現在は学部の最終学年です。彼の関心は、Web 開発と機械学習の分野にあります。彼はこの興味を追求してきており、これらの方向でもっと働きたいと思っています.