您的位置:首頁>正文

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; // 其作用下文會講

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種綁定變數類型之間的區別:

用戶端類比的綁定變數:用戶端的驅動程式接收一個帶參數的SQL,再將參數的值帶入其中,最後將完整的查詢發送到伺服器。

伺服器綁定變數:用戶端使用特殊的二進位協定將帶參數的SQL語句發送到伺服器端,然後使用二進位協定將具體的參數值發送給伺服器並執行。

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

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

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

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

這樣即使是基於時間分區,也無需再將其轉化成一個整數。

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

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; // 其作用下文會講

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種綁定變數類型之間的區別:

用戶端類比的綁定變數:用戶端的驅動程式接收一個帶參數的SQL,再將參數的值帶入其中,最後將完整的查詢發送到伺服器。

伺服器綁定變數:用戶端使用特殊的二進位協定將帶參數的SQL語句發送到伺服器端,然後使用二進位協定將具體的參數值發送給伺服器並執行。

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

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

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

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

同類文章
Next Article
喜欢就按个赞吧!!!
点击关闭提示