Linux中國

許多 SQL 性能問題來自於「不必要的強制性工作」

220 views0
By
Share

在編寫高效 SQL 時，你可能遇到的最有影響的事情就是索引。但是，一個很重要的事實就是很多 SQL 客戶端要求資料庫做很多「不必要的強制性工作」。

跟我再重複一遍：

不必要的強制性工作

什麼是「不必要的強制性工作」？這個意思包括兩個方面：

不必要的

假設你的客戶端應用程序需要這些信息：

這沒什麼特別的。我們運行著一個電影資料庫（例如 Sakila 資料庫），我們想要給用戶顯示每部電影的名稱和評分。

這是能產生上面結果的查詢：

SELECT title, rating
FROM film

然而，我們的應用程序（或者我們的 ORM（LCTT 譯註： 對象關係映射 （ Object-Relational Mapping ） ））運行的查詢卻是：

SELECT *
FROM film

我們得到什麼？猜一下。我們得到很多無用的信息：

甚至一些複雜的 JSON 數據全程在下列環節中載入：

  • 從磁碟
  • 載入到緩存
  • 通過匯流排
  • 進入客戶端內存
  • 然後被丟棄

是的，我們丟棄了其中大部分的信息。檢索它所做的工作完全就是不必要的。對吧？沒錯。

強制性

這是最糟糕的部分。現今隨著優化器變得越來越聰明，這些工作對於資料庫來說都是強制執行的。資料庫沒有辦法知道客戶端應用程序實際上不需要其中 95% 的數據。這只是一個簡單的例子。想像一下如果我們連接更多的表...

你想想那會怎樣呢？資料庫還快嗎？讓我們來看看一些之前你可能沒有想到的地方：

內存消耗

當然，單次執行時間不會變化很大。可能是慢 1.5 倍，但我們可以忍受，是吧？為方便起見，有時候確實如此。但是如果你每次都為了方便而犧牲性能，這事情就大了。我們不說性能問題（單個查詢的速度），而是關注在吞吐量上時（系統響應時間），事情就變得困難而難以解決。你就會受阻於規模的擴大。

讓我們來看看執行計劃，這是 Oracle 的：

-----------------------------------------------| 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 * 而不是 SELECT film, rating 的時候，我們在資料庫中使用了 8 倍之多的內存。這並不奇怪，對吧？我們早就知道了。在很多我們並不需要其中全部數據的查詢中我們都是這樣做的。我們為資料庫產生了不必要的強制性工作，其後果累加了起來，就是我們使用了多達 8 倍的內存（當然，數值可能有些不同）。

而現在，所有其它的步驟（比如，磁碟 I/O、匯流排傳輸、客戶端內存消耗）也受到相同的影響，我這裡就跳過了。另外，我還想看看...

索引使用

如今大部分資料庫都有涵蓋索引（LCTT 譯註：covering index，包括了你查詢所需列、甚至更多列的索引，可以直接從索引中獲取所有需要的數據，而無需訪問物理表）的概念。涵蓋索引並不是特殊的索引。但對於一個特定的查詢，它可以「意外地」或人為地轉變為一個「特殊索引」。

看看這個查詢：

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 (
      -- Worst query: Memory overhead AND table access
      SELECT *
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Statement 1 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Better query: Still table access
      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('Statement 2 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Best query: Covering index
      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('Statement 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/

結果是：

Statement 1 : +000000000 00:00:02.479000000
Statement 2 : +000000000 00:00:02.261000000
Statement 3 : +000000000 00:00:01.857000000

注意，表 actor 只有 4 列，因此語句 1 和 2 的差別並不是太令人印象深刻，但仍然很重要。還要注意我使用了 Oracle 的提示來強制優化器為查詢選擇一個或其它索引。在這種情況下語句 3 明顯勝利。這是一個好很多的查詢，也是一個十分簡單的查詢。

當我們寫 SELECT * 語句時，我們為資料庫帶來了不必要的強制性工作，這是無法優化的。它不會使用涵蓋索引，因為比起它所使用的 LAST_NAME 索引，涵蓋索引開銷更多一點，不管怎樣，它都要訪問表以獲取無用的 LAST_UPDATE 列。

使用 SELECT * 會變得更糟。考慮一下……

SQL 轉換

優化器工作的很好，因為它們轉換了你的 SQL 查詢（看我最近在 Voxxed Days Zurich 關於這方面的演講）。例如，其中有一個稱為「表連接消除」的轉換，它真的很強大。看看這個輔助視圖，我們寫了這個視圖是因為我們非常討厭總是連接所有這些表：

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)

這個視圖僅僅是把 CUSTOMER 和他們不同的 ADDRESS 部分所有「對一」關係連接起來。謝天謝地，它很工整。

現在，使用這個視圖一段時間之後，想像我們非常習慣這個視圖，我們都忘了所有它底層的表。然後，我們運行了這個查詢：

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 |
---------------------------------------------------------------

這是執行計劃一個極大的進步。我們的表連接被消除了，因為優化器可以證明它們是不必要的，因此一旦它可以證明這點（而且你不會因使用 select * 而使其成為強制性工作），它就可以移除這些工作並不執行它。為什麼會發生這種情況？

每個 CUSTOMER.ADDRESS_ID 外鍵保證了有且只有一個 ADDRESS.ADDRESS_ID 主鍵值，因此可以保證 JOIN 操作是對一連接，它不會產生或者刪除行。如果我們甚至不選擇行或查詢行，當然我們就不需要真正地去載入行。可以證實地移除 JOIN 並不會改變查詢的結果。

資料庫總是會做這些事情。你可以在大部分資料庫上嘗試它：

-- 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: divisor is equal to zero

但它並沒有發生。優化器（甚至解析器）可以證明 EXISTS (SELECT ..) 謂詞內的任何 SELECT 列表達式不會改變查詢的結果，因此也就沒有必要計算它的值。呵！

同時……

大部分 ORM 最不幸問題就是事實上他們很隨意就寫出了 SELECT * 查詢。事實上，例如 HQL / JPQL，就設置默認使用它。你甚至可以完全拋棄 SELECT 從句，因為畢竟你想要獲取所有實體，正如聲明的那樣，對吧？

例如：

FROM v_customer

例如 Vlad Mihalcea（一個 Hibernate 專家和 Hibernate 開發倡導者）建議你每次確定不想要在獲取後進行任何更改時再使用查詢。ORM 使解決對象圖持久化問題變得簡單。注意：持久化。真正修改對象圖並持久化修改的想法是固有的。

但如果你不想那樣做，為什麼要抓取實體呢？為什麼不寫一個查詢？讓我們清楚一點：從性能角度，針對你正在解決的用例寫一個查詢總是會勝過其它選項。你可以不會在意，因為你的數據集很小，沒關係。可以。但最終，你需要擴展並重新設計你的應用程序以便在強制實體圖遍歷之上支持查詢語言，就會變得很困難。你也需要做其它事情。

計算出現次數

資源浪費最嚴重的情況是在只是想要檢驗存在性時運行 COUNT(*) 查詢。例如：

這個用戶有沒有訂單？

我們會運行：

SELECT count(*)
FROM orders
WHERE user_id = :user_id

很簡單。如果 COUNT = 0：沒有訂單。否則：是的，有訂單。

性能可能不會很差，因為我們可能有一個 ORDERS.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
)

它不需要火箭科學家來確定，一旦它找到一個，實際存在謂詞就可以馬上停止尋找額外的行。因此，如果答案是「沒有訂單」，速度將會是差不多。但如果結果是「是的，有訂單」，那麼結果在我們不計算具體次數的情況下就會大幅加快。

因為我們不在乎具體的次數。我們告訴資料庫去計算它（不必要的），而資料庫也不知道我們會丟棄所有大於 1 的結果（強制性）。

當然，如果你在 JPA 支持的集合上調用 list.size() 做同樣的事情，情況會變得更糟！

近期我有關於該情況的博客以及在不同資料庫上的測試。去看看吧。

總結

這篇文章的立場很「明顯」。別讓資料庫做不必要的強制性工作。

它不必要，因為對於你給定的需求，你知道一些特定的工作不需要完成。但是，你告訴資料庫去做。

它強制性，因為資料庫無法證明它是不必要的。這些信息只包含在客戶端中，對於伺服器來說無法訪問。因此，資料庫需要去做。

這篇文章大部分在介紹 SELECT *，因為這是一個很簡單的目標。但是這並不僅限於資料庫。這關係到客戶端要求伺服器完成不必要的強制性工作的任何分散式演算法。你的 AngularJS 應用程序平均有多少個 N+1 問題，UI 在服務結果 A 上循環，多次調用服務 B，而不是把所有對 B 的調用打包為一個調用？這是一個複發的模式。

解決方法總是相同。你給執行你命令的實體越多信息，（理論上）它能更快執行這樣的命令。每次都寫一個好的查詢。你的整個系統都會為此感謝你的。

如果你喜歡這篇文章...

再看看近期我在 Voxxed Days Zurich 的演講，其中我展示了一些在數據處理演算法上為什麼 SQL 總是會勝過 Java 的雙曲線例子。

（題圖：Pixabay， CC0）

via: https://blog.jooq.org/2017/03/08/many-sql-performance-problems-stem-from-unnecessary-mandatory-work

作者：jooq 譯者：ictlyh 校對：wxy

本文由 LCTT 原創編譯，Linux中國 榮譽推出

本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive

對這篇文章感覺如何？

太棒了
0
不錯
0
愛死了
0
不太好
0
感覺很糟
0
雨落清風。心向陽
    Previous
    Next

    You may also like

    Leave a reply

    您的電子郵箱地址不會被公開。 必填項已用 * 標註

    此站點使用Akismet來減少垃圾評論。了解我們如何處理您的評論數據。

    More in:Linux中國