MySQL 優化原理（二）

如果有同學看完上一篇關于 MySQL 文章，文末留有兩個很開放的問題，如有興趣可以在腦袋裡想想。本文也會試着回答這兩個問題，希望能給你一些參考。現在可以思考一個問題，如果資料量非常大的情況下，您根據業務選擇了合适的字段，精心設計了表和索引，還仔細的檢查了所有的 SQL，并确認已經沒什麼問題，但性能仍然不能滿足您的要求，該怎麼辦呢？還有其他優化政策嗎？答案是肯定的。接下來繼續和您讨論一些常用的 MySQL 進階特性以及其背後的工作原理。

分區表

合理的使用索引可以極大提升 MySQL 的查詢性能，但如果單表資料量達到一定的程度，索引就無法起作用，因為在資料量超大的情況下，除非覆寫索引，因回表查詢會産生大量的随機 I/O，資料庫的響應時間可能會達到不可接受的程度。而且索引維護（磁盤空間、I/O 操作）的代價也會非常大。

是以，當單表資料量達到一定程度時（在 MySQL4.x 時代，MyISAM 存儲引擎業内公認的性能拐點是 500W 行，MySQL5.x 時代的性能拐點則為 1KW ~ 2KW 行級别，具體需根據實際情況測試），為了提升性能，最為常用的方法就是分表。分表的政策可以是垂直拆分（比如：不同訂單狀态的訂單拆分到不同的表），也可以是水準拆分（比如：按月将訂單拆分到不同表）。但總的來說，分表可以看作是從業務角度來解決大資料量問題，它在一定程度上可以提升性能，但也大大提升了編碼的複雜度，有過這種經曆的同學可能深有體會。

在業務層分表大大增加了編碼的複雜程度，而且處理資料庫的相關代碼會大量散落在應用各處，維護困難。那是否可以将分表的邏輯抽象出來，統一處理，這樣業務層就不用關心底層是否分表，隻需要專注在業務即可。答案當然是肯定的，目前有非常多的資料庫中間件都可以屏蔽分表後的細節，讓業務層像查詢單表一樣查詢分表後的資料。如果再将抽象的邏輯下移到資料庫的服務層，就是我們今天要講的分區表。

分區可以看作是從技術層面解決大資料問題的有效方法，簡單的了解，可以認為是 MySQL 底層幫我們實作分表，分區表是一個獨立的邏輯表，底層由多個實體子表組成。存儲引擎管理分區的各個底層表和管理普通表一樣（所有底層表必須使用相同的存儲引擎），分區表的索引也是在各個底層表上各自加上一個完全相同的索引。從存儲引擎的角度來看，底層表和普通表沒有任何不同，存儲引擎也無須知道。在執行查詢時，優化器會根據分區的定義過濾那些沒有我們需要資料的分區，這樣查詢就無需掃描所有分區，隻需要查找包含需要資料的分區就可以了。

更好的了解分區表，我們從一個示例入手：一張訂單表，資料量大概有 10TB，如何設計才能使性能達到最優？

首先可以肯定的是，因為資料量巨大，肯定不能走全表掃描。使用索引的話，你會發現資料并不是按照想要的方式聚集，而且會産生大量的碎片，最終會導緻一個查詢産生成千上萬的随機 I/O，應用随之僵死。是以需要選擇一些更粗粒度并且消耗更少的方式來檢索資料。比如先根據索引找到一大塊資料，然後再在這塊資料上順序掃描。

這正是分區要做的事情，了解分區時還可以将其當作索引的最初形态，以代價非常小的方式定位到需要的資料在哪一片 “區域”，在這片 “區域” 中，你可以順序掃描，可以建索引，還可以将資料都緩存在記憶體中。因為分區無須額外的資料結構記錄每個分區有哪些資料，是以其代價非常低。隻需要一個簡單的表達式就可以表達每個分區存放的是什麼資料。

對表分區，可以在建立表時，使用如下語句：

CREATE TABLE sales {
    order_date DATETIME NOT NULL
    -- other columns
} ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
    PARTITION p_2014 VALUES LESS THAN (2014),
    PARTITION p_2015 VALUES LESS THAN (2015)
    PARTITION p_2016 VALUES LESS THAN (2016)
    PARTITION p_2017 VALUES LESS THAN (2017)
    PARTITION p_catchall VALUES LESS THAN MAXVALUE
)
           

分區子句中可以使用各種函數，但表達式的傳回值必須是一個确定的整數，且不能是一個常數。MySQL 還支援一些其他分區，比如鍵值、哈希、清單分區，但在生産環境中很少見到。在 MySQL5.5 以後可以使用 RANGE COLUMNS 類型分區，這樣即使是基于時間分區，也無需再将其轉化成一個整數。

接下來簡單看下分區表上的各種操作邏輯：

• SELECT

  ：當查詢一個分區表時，分區層先打開并鎖住所有的底層表，優化器先判斷是否可以過濾部分分區，然後在調用對應的存儲引擎接口通路各個分區的資料

• INSERT

  ：當插入一條記錄時，分區層先打開并鎖住所有的底層表，然後确定哪個分區接收這條記錄，再将記錄寫入對應的底層表，

  DELETE

  操作與其類似

• UPDATE

  ：當更新一條資料時，分區層先打開并鎖住所有的底層表，然後确定資料對應的分區，然後取出資料并更新，再判斷更新後的資料應該存放到哪個分區，最後對底層表進行寫入操作，并對原資料所在的底層表進行删除操作

有些操作是支援條件過濾的。例如，當删除一條記錄時，MySQL 需要先找到這條記錄，如果

WHERE

條件恰好和分區表達式比對，就可以将所有不包含這條記錄的分區都過濾掉，這對

UPDATE

語句同樣有效。如果是

INSERT

操作，本身就隻命中一個分區，其他分區都會被過濾。

雖然每個操作都會 “先打開并鎖住所有的底層表”，但這并不是說分區表在處理過程中是鎖住全表的。如果存儲引擎能夠自己實作行級鎖，例如 InnoDB，則會在分區層釋放對應表鎖。這個加鎖和解鎖的操作過程與普通 InnoDB 上的查詢類似。

在使用分區表時，為了保證大資料量的可擴充性，一般有兩個政策：

• 全量掃描資料，不用索引。即隻要能夠根據 WHERE 條件将需要查詢的資料限制在少數分區中，效率是不錯的
• 索引資料，分離熱點。如果資料有明顯的 “熱點”，而且除了這部分資料，其他資料很少被通路到，那麼可以将這部分熱點資料單獨存放在一個分區中，讓這個分區的資料能夠有機會都緩存在記憶體中。這樣查詢就可以隻通路一個很小的分區表，能夠使用索引，也能夠有效的利用緩存。

分區表的優點是優化器可以根據分區函數來過濾一些分區，但很重要的一點是要在

WHERE

條件中帶入分區列，有時候即使看似多餘的也要帶上，這樣就可以讓優化器能夠過濾掉無須通路的分區，如果沒有這些條件，MySQL 就需要讓對應的存儲引擎通路這個表的所有分區，如果表非常大的話，就可能會非常慢。

上面兩個分區政策基于兩個非常重要的前提：查詢都能夠過濾掉很多額外的分區，分區本身并不會帶來很多額外的代價。而這兩個前提在某些場景下是有問題的，比如：

1、NULL 值會使分區過濾無效

假設按照

PARTITION BY RANGE YEAR(order_date)

分區，那麼所有

order_date

為 NULL 或者非法值時，記錄都會被存放到第一個分區。是以

WHERE order_date BETWEEN '2017-05-01' AND ‘2017-05-31’

，這個查詢會檢查兩個分區，而不是我們認為的 2017 年這個分區（會額外的檢查第一個分區），是因為

YEAR()

在接收非法值時會傳回 NULL。如果第一個分區的資料量非常大，而且使用全表掃描的政策時，代價會非常大。為了解決這個問題，我們可以建立一個無用的分區，比如：

PARTITION p_null values less than (0)

。如果插入的資料都是有效的話，第一個分區就是空的。

在 MySQL5.5 以後就不需要這個技巧了，因為可以直接使用列本身而不是基于列的函數進行分區：

PARTITION BY RANGE COLUMNS(order_date)

。直接使用這個文法可避免這個問題。

2、分區列和索引列不比對

當分區列和索引列不比對時，可能會導緻查詢無法進行分區過濾，除非每個查詢條件中都包含分區列。假設在列 a 上定義了索引，而在列 b 上進行分區。因為每個分區都有其獨立的索引，是以在掃描列 b 上的索引就需要掃描每一個分區内對應的索引，當然這種速度不會太慢，但是能夠跳過不比對的分區肯定會更好。這個問題看起來很容易避免，但需要注意一種情況就是，關聯查詢。如果分區表是關聯順序的第 2 張表，并且關聯使用的索引與分區條件并不比對，那麼關聯時對第一張表中符合條件的每一行都需要通路并搜尋第二張表的所有分區（關聯查詢原理，請參考前一篇文章）

3、選擇分區的成本可能很高

分區有很多種類型，不同類型的分區實作方式也不同，是以它們的性能也不盡相同，尤其是範圍分區，在确認這一行屬于哪個分區時會掃描所有的分區定義，這樣的線性掃描效率并不高，是以随着分區數的增長，成本會越來越高。特别是在批量插入資料時，由于每條記錄在插入前，都需要确認其屬于哪一個分區，如果分區數太大，會造成插入性能的急劇下降。是以有必要限制分區數量，但也不用太過擔心，對于大多數系統，100 個左右的分區是沒有問題的。

4、打開并鎖住所有底層表的成本在某些時候會很高

前面說過，打開并鎖住所有底層表并不會對性能有太大的影響，但在某些情況下，比如隻需要查詢主鍵，那麼鎖住的成本相對于主鍵的查詢來說，成本就略高。

5、維護分區的成本可能會很高

新增和删除分區的速度都很快，但是修改分區會造成資料的複制，這與

ALTER TABLE

的原理類似，需要先建立一個曆史分區，然後将資料複制到其中，最後删除原分區。是以，設計資料庫時，考慮業務的增長需要，合理的建立分區表是一個非常好的習慣。在 MySQL5.6 以後的版本可以使用

ALTER TABLE EXCHAGE PARTITION

語句來修改分區，其性能會有很大提升。

分區表還有一些其他限制，比如所有的底層表必須使用相同的存儲引擎，某些存儲引擎也不支援分區。分區一般應用于一台伺服器上，但一台伺服器的實體資源總是有限的，當資料達到這個極限時，即使分區，性能也可能會很低，是以這個時候分庫是必須的。但不管是分區、分庫還是分表，它們的思想都是一樣的，大家可以好好體會下。

視圖

對于一些關聯表的複雜查詢，使用視圖有時候會大大簡化問題，是以在許多場合下都可以看到視圖的身影，但視圖真如我們所想那樣簡單嗎？它和直接使用

JOIN

的 SQL 語句有何差別？視圖背後的原理又了解多少？

視圖本身是一個虛拟表，不存放任何資料，查詢視圖的資料集由其他表生成。MySQL 底層通過兩種算法來實作視圖：臨時表算法（TEMPTABLE）和合并算法（MERGE）。所謂臨時表算法就是将 SELECT 語句的結果存放到臨時表中，當需要通路視圖的時候，直接通路這個臨時表即可。而合并算法則是重寫包含視圖的查詢，将視圖定義的 SQL 直接包含進查詢 SQL 中。通過兩個簡單的示例來體會兩個算法的差異，建立如下視圖：

-- 視圖的作用是查詢未支付訂單
CREATE VIEW unpay_order AS
SELECT * FROM sales WHERE status = 'new'
WITH CHECK OPTION;   -- 其作用下文會講
           

現要從未支付訂單中查詢購買者為

csc

的訂單，可以使用如下查詢：

-- 查詢購買者為csc且未支付的訂單
SELECT order_id,order_amount,buyer FROM unpay_order WHERE buyer = 'csc';
           

使用臨時表來模拟視圖：

CREATE TEMPORARY TABLE tmp_order_unpay AS SELECT * FROM sales WHERE status = 'new';
SELECT order_id,order_amount,buyer FROM tmp_order_unpay WHERE buyer = 'csc';
           

使用合并算法将視圖定義的 SQL 合并進查詢 SQL 後的樣子：

SELECT order_id,order_amount,buyer FROM sales WHERE status = 'new' AND buyer = 'csc';
           

MySQL 可以嵌套定義視圖，即在一個視圖上在定義另一個視圖，可以在

EXPLAIN EXTENDED

之後使用

SHOW WARNINGS

來檢視使用視圖的查詢重寫後的結果。如果采用臨時表算法實作的視圖，

EXPLAIN

中會顯示為派生表（

DERIVED

），注意

EXPLAIN

時需要實際執行并産生臨時表，是以有可能會很慢。

明顯地，臨時表上沒有任何索引，而且優化器也很難優化臨時表上的查詢，是以，如有可能，盡量使用合并算法會有更好的性能。那麼問題來了：合并算法（類似于直接查詢）有更好的性能，為什麼還要使用視圖？

首先視圖可以簡化應用上層的操作，讓應用更專注于其所關心的資料。其次，視圖能夠對敏感資料提供安全保護，比如：對不同的使用者定義不同的視圖，可以使敏感資料不出現在不應該看到這些資料的使用者視圖上；也可以使用視圖實作基于列的權限控制，而不需要真正的在資料庫中建立列權限。再者，視圖可以友善系統運維，比如：在重構 schema 的時候使用視圖，使得在修改視圖底層表結構的時候，應用代碼還可以繼續運作不報錯。

基于此，使用視圖其實更多的是基于業務或者維護成本上的考慮，其本身并不會對性能提升有多大作用（注意：此處隻是基于 MySQL 考慮，其他關系性資料庫中視圖可能會有更好的性能，比如

ORACLE

和

MS SQL SERVER

都支援物化視圖，它們都比 MySQL 視圖有更好的性能）。而且使用臨時表算法實作的視圖，在某些時候性能可能會非常糟糕，比如：

-- 視圖的作用是統計每日支出金額，DATE('2017-06-15 12:00:23') = 2017-06-15
CREATE VIEW cost_per_day AS
SELECT DATE(create_time) AS date,SUM(cost) AS cost FROM costs GROUP BY date;
           

現要統計每日的收入與支出，有類似于上面的收入表，可以使用如下 SQL：

SELECT c.date,c.cost,s.amount
FROM cost_per_day AS c
JOIN sale_per_day AS s USING(date)
WHERE date BETWEEN '2017-06-01' AND '2017-06-30'
           

這個查詢中，MySQL 先執行視圖的 SQL，生成臨時表，然後再将

sale_per_day

表和臨時表進行關聯。這裡

WHERE

字句中的

BETWEEN

條件并不能下推到視圖中，因而視圖在建立時，會将所有的資料放到臨時表中，而不是一個月資料，并且這個臨時表也不會有索引。

當然這個示例中的臨時表資料不會太大，畢竟日期的數量不會太多，但仍然要考慮生成臨時表的性能（如果 costs 表資料過大，

GROUP BY

有可能會比較慢）。而且本示例中索引也不是問題，通過上一篇我們知道，如果 MySQL 将臨時表作為關聯順序中的第一張表，仍然可以使用

sale_per_day

中的索引。但如果是對兩個視圖做關聯的話，優化器就沒有任何索引可以使用，這時就需要嚴格測試應用的性能是否滿足需求。

我們很少會在實際業務場景中去更新視圖，是以印象中，視圖是不能更新的。但實際上，在某些情況下，視圖是可以更新的。可更新視圖是指通過更新這個視圖來更新視圖涉及的相關表，隻要指定了合适的條件，就可以更新、删除甚至是向視圖中插入資料。通過上文的了解，不難推斷出：更新視圖的實質就是更新視圖關聯的表，将建立視圖的

WHERE

子句轉化為

UPDATE

語句的

WHERE

子句，隻有使用合并算法的視圖才能更新，并且更新的列必須來自同一個表中。回顧上文建立視圖的 SQL 語句，其中有一句：

WITH CHECK OPTION

，其作用就是表示通過視圖更新的行，都必須符合視圖本身的

WHERE

條件定義，不能更新視圖定義列以外的列，否則就會抛出

check option failed

錯誤。

視圖還有一個容易造成誤解的地方：“對于一些簡單的查詢，視圖會使用合并算法，而對于一些比較複雜的查詢，視圖就會使用臨時表算法”。但實際上，視圖的實作算法是視圖本身的屬性決定的，跟作用在視圖上的 SQL 沒有任何關系。那什麼時候視圖采用臨時表算法，什麼時候采用合并算法呢？一般來說，隻要原表記錄和視圖中的記錄無法建立一一映射的關系時，MySQL 都将使用臨時表算法來實作視圖。比如建立視圖的 SQL 中包含

GROUP BY

、

DISTINCT

、

UNION

、聚合函數、子查詢的時候，視圖都将采用臨時表算法（這些規則在以後的版本中，可能會發生改變，具體請參考官方手冊）。

相比于其它關系型資料庫的視圖，MySQL 的視圖在功能上會弱很多，比如

ORACLE

和

MS SQL SERVER

都支援物化視圖。物化視圖是指将視圖結果資料存放在一個可以查詢的表中，并定期從原始表中重新整理資料到這張表中，這張表和普通實體表一樣，可以建立索引、主鍵限制等等，性能相比于臨時表會有質的提升。但遺憾的是 MySQL 目前并不支援物化視圖，當然 MySQL 也不支援在視圖中建立索引。

存儲過程與觸發器

回到第二個問題，有非常多的人在分享時都會抛出這樣一個觀點：盡可能不要使用存儲過程，存儲過程非常不容易維護，也會增加使用成本，應該把業務邏輯放到用戶端。既然用戶端都能幹這些事，那為什麼還要存儲過程？

如果有深入了解過存儲過程，就會發現存儲過程并沒有大家描述的那麼不堪。我曾經經曆過一些重度使用存儲過程的産品，依賴到什麼程度呢？就這麼說吧，上層的應用基本上隻處理互動與動效的邏輯，所有的業務邏輯，甚至是參數的校驗均在存儲過程中實作。曾經有出現過一個超大的存儲過程，其檔案大小達到驚人的 80K，可想而知，其業務邏輯有多麼複雜。在大多數人眼中，這樣的技術架構簡直有點不可理喻，但實際上這款産品非常成功。

其成功的原因在一定程度上得益于存儲過程的優點，由于業務層代碼沒有任何侵入業務的代碼，在不改變前端展示效果的同時，可以非常快速的修複 BUG、開發新功能。由于這款産品需要部署在客戶的私有環境上，快速響應客戶的需求就變得尤為重要，正是得益于這種架構，可以在客戶出現問題或者提出新需求時，快速響應，極端情況下，我們可以在 1 小時内修複客戶遇到的問題。正是這種快速響應機制，讓我們獲得大量的客戶。

當然存儲過程還有其他的優點，比如，可以非常友善的加密存儲過程代碼，而不用擔心應用部署到私有環境造成源代碼洩露、可以像調試其他應用程式一樣調試存儲過程、可以設定存儲過程的使用權限來保證資料安全等等。一切都非常美好，但我們的産品是基于

MS SQL SERVER

實作的，其可以通過

T-SQL

非常友善的實作複雜的業務邏輯。你可以把

T-SQL

看做是一門程式設計語言，其包含

SQL

的所有功能，還具備流程控制、批處理、定時任務等能力，你甚至可以用其來解析 XML 資料。關于

T-SQL

的更多資訊可以參考

MSDN

，主流的關系型資料庫目前隻有

MS SQL SERVER

支援

T-SQL

，是以，MySQL 并不具備上文描述的一些能力，比如，MySQL 的存儲過程調試非常不友善（當然可以通過付費軟體來獲得很好的支援）。

除此之外，MySQL 存儲過程還有一些其他的限制：

• 優化器無法評估存儲過程的執行成本
• 每個連接配接都有獨立的存儲過程執行計劃緩存，如果有多個連接配接需要調用同一個存儲過程，将會浪費緩存空間來緩存相同的執行計劃

是以，在 MySQL 中使用存儲過程并不是一個太好政策，特别是在一些大資料、高并發的場景下，将複雜的邏輯交給上層應用實作，可以非常友善的擴充已有資源以便獲得更高的計算能力。而且對于熟悉的程式設計語言，其可讀性會比存儲過程更好一些，也更加靈活。不過，在某些場景下，如果存儲過程比其他實作會快很多，并且是一些較小的操作，可以适當考慮使用存儲過程。

和存儲過程類似的，還有觸發器，觸發器可以讓你在執行

INSERT

、

UPDATE

和

DELETE

時，執行一些特定的操作。在 MySQL 中可以選擇在 SQL 執行之前觸發還是在 SQL 執行後觸發。觸發器一般用于實作一些強制的限制，這些限制如果在應用程式中實作會讓業務代碼變得非常複雜，而且它也可以減少用戶端與伺服器之間的通信。MySQL 觸發器的實作非常簡單，是以功能非常有限，如果你在其他資料庫産品中已經重度依賴觸發器，那麼在使用 MySQL 觸發器時候需要注意，因為 MySQL 觸發器的表現和預想的不一緻。

首先對一張表的每一個事件，最多隻能定義一個觸發器，而且它隻支援 “基于行的觸發”，也就是觸發器始終是針對一條記錄的，而不是針對整個 SQL 語句。如果是批量更新的話，效率可能會很低。其次，觸發器可以掩蓋伺服器本質工作，一個簡單的 SQL 語句背後，因為觸發器，可能包含了很多看不見的工作。再者，觸發器出現問題時很難排查。最後，觸發器并不一定能保證原子性，比如

MyISAM

引擎下觸發器執行失敗了，也不能復原。在

InnoDB

表上的觸發器是在同一個事務中執行完成的，是以他們的執行是原子的，原操作和觸發器操作會同時失敗或者成功。

雖然觸發器有這麼多限制，但它仍有适用的場景，比如，當你需要記錄 MySQL 資料的變更日志，這時觸發器就非常友善了。

外鍵限制

目前在大多數網際網路項目，特别是在大資料的場景下，已經不建議使用外鍵了，主要是考慮到外鍵的使用成本：

• 外鍵通常要求每次修改資料時都要在另外一張表中執行一次查找操作。在 InnoDB 存儲引擎中會強制外鍵使用索引，但在大資料的情況下，仍然不能忽略外鍵檢查帶來的開銷，特别是當外鍵的選擇性很低時，會導緻一個非常大且選擇性低的索引。
• 如果向子表中插入一條記錄，外鍵限制會讓 InnoDB 檢查對應的父表的記錄，也就需要對父表對應記錄進行加鎖操作，來確定這條記錄不會在這個事務完成之時就被删除了。這會導緻額外的鎖等待，甚至會導緻一些死鎖。
• 高并發場景下，資料庫很容易成為性能瓶頸，自然而然的就希望資料庫可以水準擴充，這時就需要把資料的一緻性控制放到應用層，也就是讓應用伺服器可以承擔壓力，這種情況下，資料庫層面就不能使用外鍵。

是以，當不用過多考慮資料庫的性能問題時，比如一些内部項目或傳統行業項目（其使用人數有限，而且資料量一般不會太大），使用外鍵是一個不錯的選擇，畢竟想要確定相關表始終有一緻的資料，使用外鍵要比在應用程式中檢查一緻性友善簡單許多，此外，外鍵在相關資料的删除和更新操作上也會比在應用中要高效。

綁定變量

可能大家看到 “綁定變量” 這個詞時，會有一點陌生，換個說法可能會熟悉一些：

prepared statement

。綁定變量的 SQL，使用問号标記可以接收參數的位置，當真正需要執行具體查詢的時候，則使用具體的數值代替這些問号，比如：

SELECT order_no, order_amount FROM sales WHERE order_status = ? and buyer = ?
           

為什麼要使用綁定變量？總所周知的原因是可以預先編譯，減少 SQL 注入的風險，除了這些呢？

當建立一個綁定變量 SQL 時，用戶端向伺服器發送了一個 SQL 語句原型，伺服器收到這個 SQL 語句的架構後，解析并存儲這個 SQL 語句的部分執行計劃，傳回給用戶端一個 SQL 語句處理句柄，從此以後，用戶端通過向伺服器發送各個問号的取值和這個句柄來執行一個具體查詢，這樣就可以更高效地執行大量重複語句，因為：

• 伺服器隻需要解析一次 SQL 語句
• 伺服器某些優化器的優化工作也隻需要做一次，因為 MySQL 會緩存部分執行計劃
• 通信中僅僅發送的是參數，而不是整個語句，網絡開銷也會更小，而且以二進制發送參數和句柄要比發送 ASCII 文本的效率更高

需要注意的是，MySQL 并不是總能緩存執行計劃，如果某些執行計劃需要根據參入的參數來計算時，MySQL 就無法緩存這部分執行計劃。比如：

-- 這裡假裝有一個例子，大家可以自己思考一下
           

使用綁定變量的最大陷阱是：你知道其原理，但不知道它是如何實作的。有時候，很難解釋如下 3 種綁定變量類型之間的差別：

1. 用戶端模拟的綁定變量：用戶端的驅動程式接收一個帶參數的 SQL，再将參數的值帶入其中，最後将完整的查詢發送到伺服器。
2. 伺服器綁定變量：用戶端使用特殊的二進制協定将帶參數的 SQL 語句發送到伺服器端，然後使用二進制協定将具體的參數值發送給伺服器并執行。
3. SQL 接口的綁定變量：用戶端先發送一個帶參數的 SQL 語句到伺服器端，這類似于使用

   prepared

   的 SQL 語句，然後發送設定的參數，最後在發送

   execute

   指令來執行 SQL，所有這些都是用普通的文本傳輸協定。

比如某些不支援預編譯的 JDBC 驅動，在調用

connection.prepareStatement(sql)

時，并不會把 SQL 語句發送給資料庫做預處理，而是等到調用

executeQuery

方法時才把整個語句發送到伺服器，這種方式就類似于第 1 種情況。是以，在程式中使用綁定變量時，了解你使用的驅動通過哪種方式來實作就顯得很有必要。延伸開來說，對于自己使用的架構、開源工具，不應僅僅停留在會使用這個層面，有時間可以深入了解其原理和實作，不然有可能被騙了都不知道哦。

使用者自定義函數

MySQL 本身内置了非常多的函數，比如

SUM

、

COUNT

、

AVG

等等，可實際應用中，我們常常需要更多。大多數情況下，更強大的功能都是在應用層面實作，但實際上 MySQL 也提供了機會讓我們可以去擴充 MySQL 函數，這就是使用者自定義函數 (

user-defined function

)，也稱為：

UDF

。需要注意

UDF

與存儲過程和通過 SQL 建立函數的差別，存儲過程隻能使用 SQL 來編寫，而

UDF

沒有這個限制，可以使用支援 C 語言調用約定的任何程式設計語言來實作。

UDF

必須事先編譯好并動态連結到伺服器上，這種平台相關性使得

UDF

在很多方面都很強大，

UDF

速度非常快，而且可以通路大量作業系統功能，還可以使用大量庫函數。如果需要一個 MySQL 不支援的統計聚合函數，并且無法使用存儲過程來實作，而且還想不同的語言都可以調用，那麼

UDF

是不錯的選擇，至少不需要每種語言都來實作相同的邏輯。

所謂能力越大，責任也就越大，

UDF

中的一個錯誤可能直接讓伺服器崩潰，甚至擾亂伺服器的記憶體和資料，是以，使用時需要注意其潛在的風險。在 MySQL 版本更新時也需要注意，因為你可能需要重新編譯或者修改這些

UDF

，以便讓它們能在新版本中工作。

這裡有一個簡單的示例來展示如何建立

UDF

：将結果集轉化為 JSON，具體的代碼請參考：lib_mysqludf_json。

-- 1、首先使用c語言實作功能
-- 2、編譯
-- 這裡省略第1、2步，實作并編譯成.so
-- 3、使用SQL建立函數
DROP FUNCTION json_array;
CREATE FUNCTION json_array RETURNS string soname 'lib_mysqludf_json.so';
-- 4、使用函數
SELECT
  json_array(
    customer_id,
    first_name,
    last_name,
    last_update
  ) as customer
FROM
  customer
WHERE
  customer_id = 1;
/*
5、得到的結果如下：
+------------------------------------------+
| customer                                 |
+------------------------------------------+
| [1,"MARY","SMITH","2006-02-15 04:57:20"] |
+------------------------------------------+
*/
           

其大緻的實作流程：使用 C 語言實作邏輯 -> 編譯成

.so

檔案 -> 建立函數 -> 使用函數。

UDF

在實際工作中可能很少使用，但作為開發者的我們，了解這麼一款強大的工具，在解決棘手問題時，也讓我們有了更多的選擇。

字元集

最後說說字元集。

關于字元集大多數人的第一印象可能就是：資料庫字元集盡量使用

UTF8

，因為

UTF8

字元集是目前最适合于實作多種不同字元集之間的轉換的字元集，可以最大程度上避免亂碼問題，也可以友善以後的資料遷移。But why？

字元集是指一種從二進制編碼到某類字元符号的映射，可以參考如何使用一個位元組來表示英文字母。校對規則是指一組用于某個字元集的排序規則，即采用何種規則對某類字元進行排序。MySQL 每一類編碼字元都有其對應的字元集和校對規則。MySQL 對各種字元集的支援都非常完善，但同時也帶來一些複雜性，某些場景下甚至會有一些性能犧牲。

一種字元集可能對應多種校對規則，且都有一個預設校對規則，那在 MySQL 中是如何使用字元集的？在 MySQL 中可以通過兩種方式設定字元集：建立對象時設定預設值、用戶端與伺服器通信時顯式設定。

MySQL 采用 “階梯” 式的方式來設定字元集預設值，每個資料庫，每張表都有自己的預設值，它們逐層繼承，最終最靠底層的預設設定将影響你建立的對象。比如，建立資料庫時，将根據伺服器上的

character_set_server

來設定資料庫的預設字元集，同樣的道理，根據

database

的字元集來指定庫中所有表的字元集…… 不管是對資料庫，還是表和列，隻有當它們沒有顯式指定字元集時，預設字元集才會起作用。

當用戶端與伺服器通信時，它們可以使用不同的字元集，這時候伺服器将進行必要的轉換工作。當用戶端向伺服器發送請求時，資料以

character_set_client

設定的字元集進行編碼；而當伺服器收到用戶端的 SQL 或者資料時，會按照

character_set_connection

設定的字元集進行轉換；當伺服器将要進行增删改查等操作前會再次将資料轉換成

character_set_database

(資料庫采用的字元集，沒有單獨配置即使用預設配置，具體參考上文)，最後當伺服器傳回資料或者錯誤資訊時，則将資料按

character_set_result

設定的字元集進行編碼。伺服器端可以使用

SET CHARACTER SET

來改變上面的配置，用戶端也可以根據對應的 API 來改變字元集配置。用戶端和伺服器端都使用正确的字元集才能避免在通信中出現問題。

那如何選擇字元集？

在考慮使用何種字元集時，最主要的衡量因素是存儲的内容，在能夠滿足存儲内容的前提下，盡量使用較小的字元集。因為更小的字元集意味着更少空間占用、以及更高的網絡傳輸效率，也間接提高了系統的性能。如果存儲的内容是英文字元等拉丁語系字元的話，那麼使用預設的

latin1

字元集完全沒有問題（MySQL 8 預設

utf8mb4

），如果需要存儲漢字、俄文、阿拉伯語等非拉丁語系字元，則建議使用

UTF8

字元集。當然不同字元在使用

UTF8

字元集所占用的空間是不同的，比如英文字元在

UTF8

字元集中隻使用一個位元組，而一個漢字則占用 3 個位元組。

除了字元集，校對規則也是我們需要考慮的問題。對于校對規則，一般來說隻需要考慮是否以大小寫敏感的方式比較字元串或者是否用字元串編碼的二進制來比較大小，其對應的校對規則的字尾分别是

_cs

、

_ci

和

_bin

。大小寫敏感和二進制校對規則的不同之處在于，二進制校對規則直接使用字元的位元組進行比較，而大小寫敏感的校對規則在多位元組字元集時，如德語，有更複雜的比較規則。舉個簡單的例子，

UTF8

字元集對應校對規則有三種：

• utf8_bin

  将字元串中的每一個字元用二進制資料存儲，區分大小寫

• utf8_general_ci

  不區分大小寫，

  ci

  為

  case insensitive

  的縮寫，即大小寫不敏感

• utf8_general_cs

  區分大小寫，

  cs

  為

  case sensitive

  的縮寫，即大小寫敏感

比如，建立一張表，使用

UTF8

編碼，且大小寫敏感時，可以使用如下語句：

CREATE TABLE sales (
    order_no VARCHAR(32) NOT NULL PRIMARY KEY,
    order_amount INT NOT NULL DEFAULT 0,
    ......
) ENGINE=InnoDB COLLATE=utf8_general_cs;
           

是以，在項目中直接使用

UTF8

字元集是完全沒有問題的，但需要記住的是不要在一個資料庫中使用多個不同的字元集，不同字元集之間的不相容問題很難纏。有時候，看起來一切正常，但是當某個特殊字元出現時，一切操作都會出錯，而且你很難發現錯誤的原因。

字元集對資料庫的性能有影響嗎？

某些字元集和校對規則可能會需要多個的 CPU 操作，可能會消耗更多的記憶體和存儲空間，這點在前文已經說過。特别是在同一個資料庫中使用不同的字元集，造成的影響可能會更大。

不同字元集和校對規則之間的轉換可能會帶來額外的系統開銷，比如，資料表

sales

在

buyer

字段上有索引，則可以加速下面的

ORDER BY

操作：

SELECT order_no,order_amount FROM sales ORDER BY buyer;
           

隻有當 SQL 查詢中排序要求的字元集與伺服器資料的字元集相同時，才能使用索引進行排序。你可能會說，這不是廢話嗎？其實不然，MySQL 是可以單獨指定排序時使用的校對規則的，比如：

-- 你說，這不是吃飽了撐的嗎？我覺得也是，也許會有其适用的場景吧
-- 這時候就不能使用索引排序呢，隻能使用檔案排序
SELECT order_no,order_amount FROM sales ORDER BY buyer COLLATE utf8_bin;
           

當使用兩個字元集不同的列來關聯兩張表時，MySQL 會嘗試轉換其中一個列的字元集。這和在資料列外面封裝一個函數一樣，會讓 MySQL 無法使用這個列上的索引。關于 MySQL 字元集還有一些坑，但在實際應用場景中遇到的字元集問題，其實不是特别的多，是以就此打住。

結語

MySQL 還有一些其他進階特性，但在大多數場景下我們很少會使用，是以這裡也沒有讨論，但多了解一些總是好的，至少在需要的時候，你知道有這樣一個東西。我們非常多的人，總是會認為自己所學的知識就像碎片一樣不成體系，又找不到解決辦法，那你有沒有想過也許是碎片不夠多的緣故？點太少，自然不能連接配接成線，線太少，自然不能結成網。因而，沒有其他辦法，保持好奇心、多學習、多積累，量變總有一天會質變，寫在這兒，與大家共勉吧。

前面我寫的一些文章裡面會有提到過，架構設計是一種平衡的藝術，其實質應該是一種妥協，是對現有資源的一種妥協。有時候我們會不自覺的陷入某一個點，比如，為了追求資料的擴充性，很多人一上來就開始分庫分表，然後把應用搞得非常複雜，到最後表裡還沒有裝滿資料，項目就已經死了。是以在資源有限或者未來還不可知的情況下，盡量使用資料庫、語言本身的特性來完成相應的工作，是不是會更好一點。解決大資料問題，也不隻是分庫分表，你還應該還可以想到分區；有些業務即使在分布式環境下也不一定非要在業務層完成，合理使用存儲過程和觸發器，也許會讓你更輕松……

最後，本文所讨論的知識點均出自《高性能 MySQL》，強烈建議大家讀一讀這本書。

參考資料

高性能 MySQL（第 3 版）

• 本文作者： Yibo
• 本文連結： https://windmt.com/2018/05/04/mysql-optimization-principle-2/
• 版權聲明： 本部落格所有文章除特别聲明外，均采用 CC BY-NC-SA 4.0 許可協定。轉載請注明出處！