理解本文所需的知識水平：對資料庫和 SQL 有一般了解，並且有一些 DBMS 的實務經驗。
![SQL導致的效能問題]()
要編寫有效的 SQL 查詢，您可以學習的最重要的事情可能是索引。然而，緊隨其後的第二位是許多 SQL 用戶端要求資料庫執行大量「不必要但必要的工作」的知識。跟著我重複一遍：
什麼是「不必要但必須做的工作」？正如 Captain Obvious 告訴我們的那樣，她：
![SQL導致的效能問題]()
沒什麼不尋常的。我們正在使用電影資料庫（例如Sakila 資料庫），並希望向使用者顯示所有電影的標題和評分。下面的查詢可以給出我們需要的結果：
![SQL導致的效能問題]()
在右側，您甚至可以看到正在載入一些複雜的 JSON：
![Java-университет]()
![SQL導致的效能問題]()
顯然，我們正在浪費內存等。讓我們將此查詢視為替代方案：
請注意，actor 表只有 4 列，因此語句 1 和 2 之間的效能差異並不大，但仍然很顯著。我還要指出，我使用 Oracle 優化器提示讓優化器為查詢選擇一個或另一個特定索引。操作員 3 是我們比賽中無可爭議的獲勝者。它的性能要好得多，而且我們正在討論一個極其簡單的查詢。同樣，當我們編寫 SELECT * 時，我們為資料庫創建了不必要但強制性的工作，而資料庫無法最佳化。她不會選擇覆蓋索引，因為它的開銷比她選擇的 LAST_NAME 索引稍高，除其他外，她仍然必須訪問表以檢索無用的 LAST_UPDATE 列。但我們分析 SELECT * 越深入，事情就會變得越糟。讓我們來談談...
![SQL導致的效能問題]()
什麼，說真的，對吧？現在你開始明白我在說什麼了。但想像我們從過去的錯誤中學到了一些東西，並執行這個更優化的查詢：
但這並沒有發生。優化器（甚至解析器）可以確保 EXISTS 謂詞（SELECT ..）中的選擇清單元素不會更改查詢結果，因此無需執行它。像這樣！
| 不必要但必須的工作 |
不必要
讓我們的客戶端應用程式需要以下數據：
SELECT title, rating
FROM filmSELECT *
FROM film
- 從磁碟
- 快取
- 電匯
- 為了紀念客戶
- 最後丟掉[不必要]
強制的
現在 - 最糟糕的部分。儘管優化器現在可以做很多事情，但這些操作對於資料庫來說是強制性的。資料庫無法知道客戶端應用程式不需要 95% 的資料。這只是最簡單的例子。想像一下連接多個表...那麼，你會說，資料庫速度很快嗎？讓我來啟發您一些您可能沒有想到的事情。當然，單一請求的執行時間並不會真正影響任何事情。好吧，它跑得慢了一倍半，但我們會撐過去的，對吧？為了方便？有時這是真的。但如果你總是為了方便而犧牲性能，這些小事就會開始累積。我們將不再談論效能（單一請求的執行速度），而是談論吞吐量（系統回應時間），然後就會出現嚴重的問題，而這些問題並不那麼容易解決。那就是你失去可擴展性的時候。讓我們來看看執行計劃，在本例中是 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 最煩人的問題之一是它們很容易編寫 SELECT * 查詢。事實上，例如在HQL/JPQL中它們一般都是預設使用的。我們可以完全省略 SELECT 子句，因為我們將檢索整個實體，對吧？例如：FROM v_customer計算行數以查明是否存在某些內容
最嚴重的資源浪費之一是執行 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