[[194306]]

在編寫高效 SQL 時(shí)，你可能遇到的最有影響的事情就是索引。但是，一個(gè)很重要的事實(shí)就是很多 SQL 客戶端要求數(shù)據(jù)庫做很多“不必要的強(qiáng)制性工作”。

跟我再重復(fù)一遍：

不必要的強(qiáng)制性工作

什么是“不必要的強(qiáng)制性工作”?這個(gè)意思包括兩個(gè)方面：

不必要的

假設(shè)你的客戶端應(yīng)用程序需要這些信息：

這沒什么特別的。我們運(yùn)行著一個(gè)電影數(shù)據(jù)庫(例如 Sakila 數(shù)據(jù)庫)，我們想要給用戶顯示每部電影的名稱和評分。

這是能產(chǎn)生上面結(jié)果的查詢：

SELECT title, rating 
FROM film 

然而，我們的應(yīng)用程序(或者我們的 ORM(LCTT 譯注：對象關(guān)系映射(Object-Relational Mapping)))運(yùn)行的查詢卻是：

SELECT * 
FROM film 

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

甚至一些復(fù)雜的 JSON 數(shù)據(jù)全程在下列環(huán)節(jié)中加載：

• 從磁盤
• 加載到緩存
• 通過總線
• 進(jìn)入客戶端內(nèi)存
• 然后被丟棄

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

強(qiáng)制性

這是最糟糕的部分?，F(xiàn)今隨著優(yōu)化器變得越來越聰明，這些工作對于數(shù)據(jù)庫來說都是強(qiáng)制執(zhí)行的。數(shù)據(jù)庫沒有辦法知道客戶端應(yīng)用程序?qū)嶋H上不需要其中 95% 的數(shù)據(jù)。這只是一個(gè)簡單的例子。想象一下如果我們連接更多的表...

你想想那會怎樣呢?數(shù)據(jù)庫還快嗎?讓我們來看看一些之前你可能沒有想到的地方：

內(nèi)存消耗

當(dāng)然，單次執(zhí)行時(shí)間不會變化很大。可能是慢 1.5 倍，但我們可以忍受，是吧?為方便起見，有時(shí)候確實(shí)如此。但是如果你每次都為了方便而犧牲性能，這事情就大了。我們不說性能問題(單個(gè)查詢的速度)，而是關(guān)注在吞吐量上時(shí)(系統(tǒng)響應(yīng)時(shí)間)，事情就變得困難而難以解決。你就會受阻于規(guī)模的擴(kuò)大。

讓我們來看看執(zhí)行計(jì)劃，這是 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 | 
-------------------------------------------------- 

當(dāng)執(zhí)行 SELECT * 而不是 SELECT film, rating 的時(shí)候，我們在數(shù)據(jù)庫中使用了 8 倍之多的內(nèi)存。這并不奇怪，對吧?我們早就知道了。在很多我們并不需要其中全部數(shù)據(jù)的查詢中我們都是這樣做的。我們?yōu)閿?shù)據(jù)庫產(chǎn)生了不必要的強(qiáng)制性工作，其后果累加了起來，就是我們使用了多達(dá) 8 倍的內(nèi)存(當(dāng)然，數(shù)值可能有些不同)。

而現(xiàn)在，所有其它的步驟(比如，磁盤 I/O、總線傳輸、客戶端內(nèi)存消耗)也受到相同的影響，我這里就跳過了。另外，我還想看看...

索引使用

如今大部分?jǐn)?shù)據(jù)庫都有涵蓋索引(LCTT 譯注：covering index，包括了你查詢所需列、甚至更多列的索引，可以直接從索引中獲取所有需要的數(shù)據(jù)，而無需訪問物理表)的概念。涵蓋索引并不是特殊的索引。但對于一個(gè)特定的查詢，它可以“意外地”或人為地轉(zhuǎn)變?yōu)橐粋€(gè)“特殊索引”。

看看這個(gè)查詢：

SELECT * 
FROM actor 
WHERE last_name LIKE 'A%' 

執(zhí)行計(jì)劃中沒有什么特別之處。它只是個(gè)簡單的查詢。索引范圍掃描、表訪問，就結(jié)束了：

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

這是個(gè)好計(jì)劃嗎?如果我們只是想要這些，那么它就不是：

當(dāng)然，我們浪費(fèi)了內(nèi)存之類的。再來看看這個(gè)查詢：

SELECT first_name, last_name  
FROM actor 
WHERE last_name LIKE 'A%' 

它的計(jì)劃是：

---------------------------------------------------- 
| Id  | Operation        | Name            | Rows  | 
---------------------------------------------------- 
|   0 | SELECT STATEMENT |                 |     8 | 
|*  1 |  INDEX RANGE SCAN| IDX_ACTOR_NAMES |     8 | 
---------------------------------------------------- 

現(xiàn)在我們可以完全消除表訪問，因?yàn)橛幸粋€(gè)索引涵蓋了我們查詢需要的所有東西……一個(gè)涵蓋索引。這很重要嗎?當(dāng)然!這種方法可以將你的某些查詢加速一個(gè)數(shù)量級(如果在某個(gè)更改后你的索引不再涵蓋，可能會降低一個(gè)數(shù)量級)。

你不能總是從涵蓋索引中獲利。索引也有它們自己的成本，你不應(yīng)該添加太多索引，例如像這種情況就是不明智的。讓我們來做個(gè)測試：

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

結(jié)果是：

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 的提示來強(qiáng)制優(yōu)化器為查詢選擇一個(gè)或其它索引。在這種情況下語句 3 明顯勝利。這是一個(gè)好很多的查詢，也是一個(gè)十分簡單的查詢。

當(dāng)我們寫 SELECT * 語句時(shí)，我們?yōu)閿?shù)據(jù)庫帶來了不必要的強(qiáng)制性工作，這是無法優(yōu)化的。它不會使用涵蓋索引，因?yàn)楸绕鹚褂玫?LAST_NAME 索引，涵蓋索引開銷更多一點(diǎn)，不管怎樣，它都要訪問表以獲取無用的 LAST_UPDATE 列。

使用 SELECT * 會變得更糟?？紤]一下……

SQL 轉(zhuǎn)換

優(yōu)化器工作的很好，因?yàn)樗鼈冝D(zhuǎn)換了你的 SQL 查詢(看我最近在 Voxxed Days Zurich 關(guān)于這方面的演講)。例如，其中有一個(gè)稱為“表連接消除”的轉(zhuǎn)換，它真的很強(qiáng)大?？纯催@個(gè)輔助視圖，我們寫了這個(gè)視圖是因?yàn)槲覀兎浅Ｓ憛捒偸沁B接所有這些表：

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) 

這個(gè)視圖僅僅是把 CUSTOMER 和他們不同的 ADDRESS 部分所有“對一”關(guān)系連接起來。謝天謝地，它很工整。

現(xiàn)在，使用這個(gè)視圖一段時(shí)間之后，想象我們非常習(xí)慣這個(gè)視圖，我們都忘了所有它底層的表。然后，我們運(yùn)行了這個(gè)查詢：

SELECT * 
FROM v_customer 

我們得到了一個(gè)相當(dāng)令人印象深刻的計(jì)劃：

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

當(dāng)然是這樣。我們運(yùn)行了所有這些表連接以及全表掃描，因?yàn)檫@就是我們讓數(shù)據(jù)庫去做的：獲取所有的數(shù)據(jù)。

現(xiàn)在，再一次想一下，對于一個(gè)特定場景，我們真正想要的是：

是啊，對吧?現(xiàn)在你應(yīng)該知道我的意圖了。但想像一下，我們確實(shí)從前面的錯誤中學(xué)到了東西，現(xiàn)在我們實(shí)際上運(yùn)行下面一個(gè)比較好的查詢：

SELECT first_name, last_name 
FROM v_customer 

再來看看結(jié)果!

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

這是執(zhí)行計(jì)劃一個(gè)極大的進(jìn)步。我們的表連接被消除了，因?yàn)閮?yōu)化器可以證明它們是不必要的，因此一旦它可以證明這點(diǎn)(而且你不會因使用 select * 而使其成為強(qiáng)制性工作)，它就可以移除這些工作并不執(zhí)行它。為什么會發(fā)生這種情況?

每個(gè) CUSTOMER.ADDRESS_ID 外鍵保證了有且只有一個(gè) ADDRESS.ADDRESS_ID 主鍵值，因此可以保證 JOIN 操作是對一連接，它不會產(chǎn)生或者刪除行。如果我們甚至不選擇行或查詢行，當(dāng)然我們就不需要真正地去加載行?？梢宰C實(shí)地移除 JOIN 并不會改變查詢的結(jié)果。

數(shù)據(jù)庫總是會做這些事情。你可以在大部分?jǐn)?shù)據(jù)庫上嘗試它：

-- Oracle 
SELECT CASE WHEN EXISTS ( 
  SELECT 1 / 0 FROM dual 
) THEN 1 ELSE 0 END 
FROM dual 
-- 更合理的 SQL 語句，例如 PostgreSQL 
SELECT EXISTS (SELECT 1 / 0) 

在這種情況下，當(dāng)你運(yùn)行這個(gè)查詢時(shí)你可能預(yù)料到會拋出算術(shù)異常：

SELECT 1 / 0 FROM dual 

產(chǎn)生了：

ORA-01476: divisor is equal to zero 

但它并沒有發(fā)生。優(yōu)化器(甚至解析器)可以證明 EXISTS (SELECT ..) 謂詞內(nèi)的任何 SELECT 列表達(dá)式不會改變查詢的結(jié)果，因此也就沒有必要計(jì)算它的值。呵!

同時(shí)……

大部分 ORM 最不幸問題就是事實(shí)上他們很隨意就寫出了 SELECT * 查詢。事實(shí)上，例如 HQL / JPQL，就設(shè)置默認(rèn)使用它。你甚至可以完全拋棄 SELECT 從句，因?yàn)楫吘鼓阆胍@取所有實(shí)體，正如聲明的那樣，對吧?

例如：

FROM v_customer

例如 Vlad Mihalcea(一個(gè) Hibernate 專家和 Hibernate 開發(fā)倡導(dǎo)者)建議你每次確定不想要在獲取后進(jìn)行任何更改時(shí)再使用查詢。ORM 使解決對象圖持久化問題變得簡單。注意：持久化。真正修改對象圖并持久化修改的想法是固有的。

但如果你不想那樣做，為什么要抓取實(shí)體呢?為什么不寫一個(gè)查詢?讓我們清楚一點(diǎn)：從性能角度，針對你正在解決的用例寫一個(gè)查詢總是會勝過其它選項(xiàng)。你可以不會在意，因?yàn)槟愕臄?shù)據(jù)集很小，沒關(guān)系?？梢?。但最終，你需要擴(kuò)展并重新設(shè)計(jì)你的應(yīng)用程序以便在強(qiáng)制實(shí)體圖遍歷之上支持查詢語言，就會變得很困難。你也需要做其它事情。

計(jì)算出現(xiàn)次數(shù)

資源浪費(fèi)最嚴(yán)重的情況是在只是想要檢驗(yàn)存在性時(shí)運(yùn)行 COUNT(*) 查詢。例如：

這個(gè)用戶有沒有訂單?

我們會運(yùn)行：

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

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

性能可能不會很差，因?yàn)槲覀兛赡苡幸粋€(gè) ORDERS.USER_ID 列上的索引。但是和下面的這個(gè)相比你認(rèn)為上面的性能是怎樣呢：

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

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

因?yàn)槲覀儾辉诤蹙唧w的次數(shù)。我們告訴數(shù)據(jù)庫去計(jì)算它(不必要的)，而數(shù)據(jù)庫也不知道我們會丟棄所有大于 1 的結(jié)果(強(qiáng)制性)。

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

近期我有關(guān)于該情況的博客以及在不同數(shù)據(jù)庫上的測試。去看看吧。

總結(jié)

這篇文章的立場很“明顯”。別讓數(shù)據(jù)庫做不必要的強(qiáng)制性工作。

它不必要，因?yàn)閷τ谀憬o定的需求，你知道一些特定的工作不需要完成。但是，你告訴數(shù)據(jù)庫去做。

它強(qiáng)制性，因?yàn)閿?shù)據(jù)庫無法證明它是不必要的。這些信息只包含在客戶端中，對于服務(wù)器來說無法訪問。因此，數(shù)據(jù)庫需要去做。

這篇文章大部分在介紹 SELECT *，因?yàn)檫@是一個(gè)很簡單的目標(biāo)。但是這并不僅限于數(shù)據(jù)庫。這關(guān)系到客戶端要求服務(wù)器完成不必要的強(qiáng)制性工作的任何分布式算法。你的 AngularJS 應(yīng)用程序平均有多少個(gè) N+1 問題，UI 在服務(wù)結(jié)果 A 上循環(huán)，多次調(diào)用服務(wù) B，而不是把所有對 B 的調(diào)用打包為一個(gè)調(diào)用?這是一個(gè)復(fù)發(fā)的模式。

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

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

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

(題圖：Pixabay， CC0)