記事を理解するために必要な知識のレベル:データベースと SQL に関する一般的な理解、DBMS に関する実践経験。
![SQL パフォーマンスの問題の原因]()
おそらく、効果的な SQL クエリを作成するために学ぶことができる最も重要なことは、インデックス作成です。しかし、そのすぐ後ろの 2 位は、多くの SQL クライアントがデータベースに多くの「不必要だが必要な作業」を要求するという知識です。私の後に繰り返して：
「不要不急の業務」とは何でしょうか？キャプテン・オブビアスはこう語っています。
![SQL パフォーマンスの問題の原因]()
何も珍しいことはありません。私たちは映画データベース ( Sakila データベースなど) を使用しており、すべての映画のタイトルと評価をユーザーに表示したいと考えています。次のクエリにより、必要な結果が得られます。
![SQL パフォーマンスの問題の原因]()
右側には、いくつかの複雑な JSON が読み込まれているのがわかります。
![Java-университет]()
![SQL パフォーマンスの問題の原因]()
明らかに、メモリなどを無駄にしています。次のクエリを代替案として考えてみましょう。
アクター テーブルには 4 つの列しかないため、ステートメント 1 と 2 のパフォーマンスの違いはそれほど大きくありませんが、依然として重大であることに注意してください。また、オプティマイザーにクエリに対して 1 つまたは別の特定のインデックスを選択させるために、Oracle オプティマイザー ヒントを使用したことにも注意してください。オペレーター 3 は、誰もが認めるこのレースの勝者です。パフォーマンスははるかに優れており、非常に単純なクエリについて話しています。繰り返しになりますが、SELECT * を記述すると、データベースに対して最適化できない不必要ではあるが必須の作業が作成されます。彼女は、カバー インデックスを選択しません。これは、選択した LAST_NAME インデックスよりもオーバーヘッドがわずかに高く、特に、たとえば、役に立たない LAST_UPDATE 列を取得するためにテーブルにアクセスする必要があるためです。しかし、SELECT * を深く分析すればするほど、事態はさらに悪いことが判明します。について話しましょう...
![SQL パフォーマンスの問題の原因]()
マジで、そうですよね？今、あなたは私が何を言っているのか理解し始めています。しかし、過去の間違いから何かを学び、次のより最適なクエリを実行したと想像してください。
しかし、そんなことは起こらない。オプティマイザ (またはパーサー) は、EXISTS 述語 (SELECT ..) 内の選択リスト要素がクエリの結果を変更しないことを保証できるため、クエリを実行する必要はありません。このような！
| 不必要だが必要な作業 |
不要
クライアント アプリケーションに次のデータが必要だとします。
SELECT title, rating
FROM filmSELECT *
FROM film
- ディスクから
- キャッシュする
- 有線で
- クライアントの思い出に
- そして最終的には[不要なものとして]捨てられる
必須
そして今 - 最悪の部分。オプティマイザーは多くのことを実行できるようになりましたが、これらのアクションはデータベースにとって必須です。データベースは、クライアント アプリケーションがこのデータの 95% を必要としていないことを知る方法がありません。そして、これは最も単純な例にすぎません。複数のテーブルを接続することを想像してみてください... それで、データベースは高速だと言いますか? おそらくあなたが考えたこともなかったいくつかのことについて説明させてください。もちろん、個々のリクエストの実行時間は実際には何の影響も与えません。わかりました、実行速度は 1.5 倍遅くなりましたが、なんとか乗り越えられるでしょう? 便宜のためですか？時々これは真実です。しかし、利便性のために常にパフォーマンスを犠牲にしていると、これらの小さなことが積み重なり始めます。もはやパフォーマンス (個々のリクエストの実行速度) について話すのではなく、スループット (システムの応答時間) について話すようになります。そうすれば、解決するのがそれほど簡単ではない深刻な問題が始まります。そうなるとスケーラビリティが失われます。実行プラン、この場合は Oracle DBMS を見てみましょう。--------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 166K|
| 1 | TABLE ACCESS FULL| FILM | 1000 | 166K|
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 20000 |
| 1 | TABLE ACCESS FULL| FILM | 1000 | 20000 |
--------------------------------------------------
インデックスの使用
今日のほとんどのデータベースは、インデックスをカバーするという概念をすでに認識しています。カバリングインデックス自体は特別なタイプのインデックスではありません。ただし、「偶然」または意図的に、特定のクエリの「特別なインデックス」になる場合があります。次のクエリを考えてみましょう。SELECT *
FROM actor
WHERE last_name LIKE 'A%'-------------------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 |
| 1 | TABLE ACCESS BY INDEX ROWID| ACTOR | 8 |
|* 2 | INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME | 8 |
-------------------------------------------------------------------
SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 8 |
|* 1 | INDEX RANGE SCAN| IDX_ACTOR_NAMES | 8 |
----------------------------------------------------SET SERVEROUTPUT ON
DECLARE
v_ts TIMESTAMP;
v_repeat CONSTANT NUMBER := 100000;
BEGIN
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Наихудший вариант requestа: перерасход памяти ПЛЮС доступ к таблице
SELECT *
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Улучшенный request, но все равно с доступом к таблице
SELECT /*+INDEX(actor(last_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (
-- Оптимальный request: покрывающий индекс
SELECT /*+INDEX(actor(last_name, first_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
) LOOP
NULL;
END LOOP;
END LOOP;
dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/|
結果として、次のことが得られます。 演算子 1: +000000000 00:00:02.479000000 演算子 2: +000000000 00:00:02.261000000 演算子 3: +000000000 00:00:01.857000000 |
SQL変換
オプティマイザーは SQL クエリを変換するため、非常に優れたパフォーマンスを発揮します (これがどのように機能するかについては、チューリッヒでの Voxxed Days での最近の講演で説明しました)。たとえば、非常に強力な「例外 JOIN」変換があります。これらすべてのテーブルを毎回手動で結合することを避けるために作成する必要があった次のヘルパー ビューについて考えてみましょう。CREATE VIEW v_customer AS
SELECT
c.first_name, c.last_name,
a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)SELECT *
FROM v_customer----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 599 | 47920 | 14 |
|* 1 | HASH JOIN | | 599 | 47920 | 14 |
| 2 | TABLE ACCESS FULL | COUNTRY | 109 | 1526 | 2 |
|* 3 | HASH JOIN | | 599 | 39534 | 11 |
| 4 | TABLE ACCESS FULL | CITY | 600 | 10800 | 3 |
|* 5 | HASH JOIN | | 599 | 28752 | 8 |
| 6 | TABLE ACCESS FULL| CUSTOMER | 599 | 11381 | 4 |
| 7 | TABLE ACCESS FULL| ADDRESS | 603 | 17487 | 3 |
----------------------------------------------------------------
SELECT first_name, last_name
FROM v_customer------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 599 | 16173 | 4 |
| 1 | NESTED LOOPS | | 599 | 16173 | 4 |
| 2 | TABLE ACCESS FULL| CUSTOMER | 599 | 11381 | 4 |
|* 3 | INDEX UNIQUE SCAN| SYS_C007120 | 1 | 8 | 0 |
-------------------------------------------------------------------- Oracle
SELECT CASE WHEN EXISTS (
SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)SELECT 1 / 0 FROM dual|
起こりました： ORA-01476: 除数はゼロに等しいです |
その間...
ORM に関する最も厄介な問題の 1 つは、ORM が SELECT * クエリを非常に簡単に作成できることです。実際、たとえば HQL / JPQL では通常、デフォルトで使用されます。エンティティ全体を取得するので、SELECT 句を完全に省略できますね。例えば：FROM v_customer行を数えて何かが存在するかどうかを確認する
リソースの最悪の無駄の 1 つは、データベースに何かがあるかどうかを確認するためだけに COUNT(*) クエリを実行することです。たとえば、特定のユーザーに注文があるかどうかを確認する必要があります。そしてリクエストを実行します。SELECT count(*)
FROM orders
WHERE user_id = :user_id-- Oracle
SELECT CASE WHEN EXISTS (
SELECT *
FROM orders
WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
SELECT *
FROM orders
WHERE user_id = :user_id
)
GO TO FULL VERSION