我必須得告訴大家的MySQL優化原理2

如果有同學看完上一篇關于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可以嵌套定義視圖，即在一個視圖上在定義另一個視圖，可以在EXPLAN 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字符集完全沒有問題，如果需要存儲漢字、俄文、阿拉伯語等非拉丁語系字符，則建議使用UTF8字符集。當然不同字符在使用UTF8字符集所占用的空間是不同的，比如英文字符在UTF8字符集中只使用一個字節，而一個漢字則占用3個字節。

除了字符集，校對規則也是我們需要考慮的問題。對于校對規則，一般來說只需要考慮是否以大小寫敏感的方式比較字符串或者是否用字符串編碼的二進制來比較大小，其對應的校對規則的后綴分別是_cs、_ci和_bin。大小寫敏感和二進制校對規則的不同之處在于，二進制校對規則直接使用字符的字節進行比較，而大小寫敏感的校對規則在多字節字符集時，如德語，有更復雜的比較規則。舉個簡單的例子，UTF8字符集對應校對規則有三種：

1. utf8_bin將字符串中的每一個字符用二進制數據存儲，區分大小寫

2. utf8_general_ci不區分大小寫，ci為case insensitive的縮寫，即大小寫不敏感

3. 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》，強烈建議大家讀一讀這本書。

參考資料

[1] Baron Scbwartz 等著；寧海元 周振興等譯；高性能MySQL（第三版）; 電子工業出版社， 2013

本文已經同步更新到微信公眾號：輕描淡寫CODE  >>  我必須得告訴大家的MySQL優化原理2