如果有同學看完上一篇關于MySQL文章,文末留有兩個很開放的問題,如有興趣可以在腦袋里想想.本文也會試著回答這兩個問題,希望能給你一些參考.現(xiàn)在可以思考一個問題,如果數(shù)據(jù)量非常大的情況下,您根據(jù)業(yè)務選擇了合適的字段,精心設計了表和索引,還仔細的檢查了所有的SQL,并確認已經(jīng)沒什么問題,但性能仍然不能滿足您的要求,該怎么辦呢?還有其他優(yōu)化策略嗎?答案是肯定的.接下來繼續(xù)和您討論一些常用的MySQL高級特性以及其背后的工作原理.

分區(qū)表

合理的使用索引可以極大提升MySQL的查詢性能,但如果單表數(shù)據(jù)量達到一定的程度,索引就無法起作用,因為在數(shù)據(jù)量超大的情況下,除非覆蓋索引,因回表查詢會產(chǎn)生大量的隨機I/O,數(shù)據(jù)庫的響應時間可能會達到不可接受的程度.而且索引維護(磁盤空間、I/O操作)的代價也會非常大.

因此,當單表數(shù)據(jù)量達到一定程度時(在MySQL4.x時代,MyISAM存儲引擎業(yè)內(nèi)公認的性能拐點是500W行,MySQL5.x時代的性能拐點則為1KW ~ 2KW行級別,具體需根據(jù)實際情況測試),為了提升性能,最為常用的辦法就是分表.分表的策略可以是垂直拆分(比如：不同訂單狀態(tài)的訂單拆分到不同的表),也可以是水平拆分(比如：按月將訂單拆分到不同表).但總的來說,分表可以看作是從業(yè)務角度來解決大數(shù)據(jù)量問題,它在一定程度上可以提升性能,但也大大提升了編碼的復雜度,有過這種經(jīng)歷的同學可能深有體會.

在業(yè)務層分表大大增加了編碼的復雜程度,而且處理數(shù)據(jù)庫的相關代碼會大量散落在應用各處,維護困難.那是否可以將分表的邏輯抽象出來,統(tǒng)一處理,這樣業(yè)務層就不用關心底層是否分表,只需要專注在業(yè)務即可.答案當然是肯定的,目前有非常多的數(shù)據(jù)庫中間件都可以屏蔽分表后的細節(jié),讓業(yè)務層像查詢單表一樣查詢分表后的數(shù)據(jù).如果再將抽象的邏輯下移到數(shù)據(jù)庫的服務層,就是我們今天要講的分區(qū)表.

分區(qū)可以看作是從技術層面解決大數(shù)據(jù)問題的有效辦法,簡單的理解,可以認為是MySQL底層幫我們實現(xiàn)分表,分區(qū)表是一個獨立的邏輯表,底層由多個物理子表組成.存儲引擎管理分區(qū)的各個底層表和管理普通表一樣(所有底層表必須使用相同的存儲引擎),分區(qū)表的索引也是在各個底層表上各自加上一個完全相同的索引.從存儲引擎的角度來看,底層表和普通表沒有任何不同,存儲引擎也無須知道.在執(zhí)行查詢時,優(yōu)化器會根據(jù)分區(qū)的定義過濾那些沒有我們需要數(shù)據(jù)的分區(qū),這樣查詢就無需掃描所有分區(qū),只需要查找包含需要數(shù)據(jù)的分區(qū)就可以了.

更好的理解分區(qū)表,我們從一個示例入手：一張訂單表,數(shù)據(jù)量大概有10TB,如何設計才能使性能達到最優(yōu)?

首先可以肯定的是,因為數(shù)據(jù)量巨大,肯定不能走全表掃描.使用索引的話,你會發(fā)現(xiàn)數(shù)據(jù)并不是依照想要的方式聚集,而且會產(chǎn)生大量的碎片,最終會導致一個查詢產(chǎn)生成千上萬的隨機I/O,應用隨之僵死.所以需要選擇一些更粗粒度并且消耗更少的方式來檢索數(shù)據(jù).比如先根據(jù)索引找到一大塊數(shù)據(jù),然后再在這塊數(shù)據(jù)上順序掃描.

這正是分區(qū)要做的事情,理解分區(qū)時還可以將其當作索引的最初形態(tài),以代價非常小的方式定位到需要的數(shù)據(jù)在哪一片“區(qū)域”,在這片“區(qū)域”中,你可以順序掃描,可以建索引,還可以將數(shù)據(jù)都緩存在內(nèi)存中.因為分區(qū)無須額外的數(shù)據(jù)結(jié)構(gòu)記錄每個分區(qū)有哪些數(shù)據(jù),所以其代價非常低.只需要一個簡單的表達式就可以表達每個分區(qū)存放的是什么數(shù)據(jù).

對表分區(qū),可以在創(chuàng)建表時,使用如下語句：

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

)

分區(qū)子句中可以使用各種函數(shù),但表達式的返回值必須是一個確定的整數(shù),且不能是一個常數(shù).MySQL還支持一些其他分區(qū),比如鍵值、哈希、列表分區(qū),但在生產(chǎn)環(huán)境中很少見到.在MySQL5.5以后可以使用RANGE COLUMNS類型分區(qū),這樣即使是基于時間分區(qū),也無需再將其轉(zhuǎn)化成一個整數(shù).

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

SELECT

：當查詢一個分區(qū)表時,分區(qū)層先打開并鎖住所有的底層表,優(yōu)化器先判斷是否可以過濾部分分區(qū),然后在調(diào)用對應的存儲引擎接口拜訪各個分區(qū)的數(shù)據(jù)

INSERT

：當插入一條記錄時,分區(qū)層先打開并鎖住所有的底層表,然后確定哪個分區(qū)接收這條記錄,再將記錄寫入對應的底層表,

DELETE

操作與其類似

UPDATE

：當更新一條數(shù)據(jù)時,分區(qū)層先打開并鎖住所有的底層表,然后確定數(shù)據(jù)對應的分區(qū),然后取出數(shù)據(jù)并更新,再判斷更新后的數(shù)據(jù)應該存放到哪個分區(qū),最后對底層表進行寫入操作,并對原數(shù)據(jù)所在的底層表進行刪除操作

有些操作是支持條件過濾的.例如,當刪除一條記錄時,MySQL需要先找到這條記錄,如果

WHERE

條件恰好和分區(qū)表達式匹配,就可以將所有不包含這條記錄的分區(qū)都過濾掉,這對

UPDATE

語句同樣有效.如果是

INSERT

操作,本身就只命中一個分區(qū),其他分區(qū)都會被過濾.

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

在使用分區(qū)表時,為了保證大數(shù)據(jù)量的可擴展性,一般有兩個策略：

全量掃描數(shù)據(jù),不用索引.即只要能夠根據(jù)WHERE條件將需要查詢的數(shù)據(jù)限制在少數(shù)分區(qū)中,效率是不錯的

索引數(shù)據(jù),分離熱點.如果數(shù)據(jù)有明顯的“熱點”,而且除了這部分數(shù)據(jù),其他數(shù)據(jù)很少被拜訪到,那么可以將這部分熱點數(shù)據(jù)單獨存放在一個分區(qū)中,讓這個分區(qū)的數(shù)據(jù)能夠有機會都緩存在內(nèi)存中.這樣查詢就可以值拜訪一個很小的分區(qū)表,能夠使用索引,也能夠有效的利用緩存.

分區(qū)表的優(yōu)點是優(yōu)化器可以根據(jù)分區(qū)函數(shù)來過濾一些分區(qū),但很重要的一點是要在

WHERE

條件中帶入分區(qū)列,有時候即使看似多余的也要帶上,這樣就可以讓優(yōu)化器能夠過濾掉無須拜訪的分區(qū),如果沒有這些條件,MySQL就需要讓對應的存儲引擎拜訪這個表的所有分區(qū),如果表非常大的話,就可能會非常慢.

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

1、NULL值會使分區(qū)過濾無效

假設依照

PARTITION BY RANGE YEAR(order_date)

分區(qū),那么所有

order_date

為NULL或者非法值時,記錄都會被存放到第一個分區(qū).所以

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

,這個查詢會檢查兩個分區(qū),而不是我們認為的2017年這個分區(qū)(會額外的檢查第一個分區(qū)),是因為

YEAR()

在接收非法值時會返回NULL.如果第一個分區(qū)的數(shù)據(jù)量非常大,而且使用全表掃描的策略時,代價會非常大.為了辦理這個問題,我們可以創(chuàng)建一個無用的分區(qū),比如：

PARTITION p_null values less than (0)

.如果插入的數(shù)據(jù)都是有效的話,第一個分區(qū)就是空的.

在MySQL5.5以后就不需要這個技巧了,因為可以直接使用列本身而不是基于列的函數(shù)進行分區(qū)：

PARTITION BY RANGE COLUMNS(order_date)

.直接使用這個語法可避免這個問題.

2、分區(qū)列和索引列不匹配

當分區(qū)列和索引列不匹配時,可能會導致查詢無法進行分區(qū)過濾,除非每個查詢條件中都包含分區(qū)列.假設在列a上定義了索引,而在列b上進行分區(qū).因為每個分區(qū)都有其獨立的索引,所以在掃描列b上的索引就需要掃描每一個分區(qū)內(nèi)對應的索引,當然這種速度不會太慢,但是能夠跳過不匹配的分區(qū)肯定會更好.這個問題看起來很容易避免,但需要注意一種情況就是,關聯(lián)查詢.如果分區(qū)表是關聯(lián)順序的第2張表,并且關聯(lián)使用的索引與分區(qū)條件并不匹配,那么關聯(lián)時對第一張表中符合條件的每一行都需要拜訪并搜索第二張表的所有分區(qū)(關聯(lián)查詢原理,請參考前一篇文章)

3、選擇分區(qū)的成本可能很高

分區(qū)有很多種類型,不同類型的分區(qū)實現(xiàn)方式也不同,所以它們的性能也不盡相同,尤其是范圍分區(qū),在確認這一行屬于哪個分區(qū)時會掃描所有的分區(qū)定義,這樣的線性掃描效率并不高,所以隨著分區(qū)數(shù)的增長,成本會越來越高.特別是在批量插入數(shù)據(jù)時,由于每條記錄在插入前,都需要確認其屬于哪一個分區(qū),如果分區(qū)數(shù)太大,會造成插入性能的急劇下降.因此有必要限制分區(qū)數(shù)量,但也不用太過擔心,對于大多數(shù)系統(tǒng),100個左右的分區(qū)是沒有問題的.

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

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

5、維護分區(qū)的成本可能會很高

新增和刪除分區(qū)的速度都很快,但是修改分區(qū)會造成數(shù)據(jù)的復制,這與

ALTER TABLE

的原理類似,需要先創(chuàng)建一個歷史分區(qū),然后將數(shù)據(jù)復制到其中,最后刪除原分區(qū).因此,設計數(shù)據(jù)庫時,考慮業(yè)務的增長需要,合理的創(chuàng)建分區(qū)表是一個非常好的習慣.在MySQL5.6以后的版本可以使用

ALTER TABLE EXCHAGE PARTITION

語句來修改分區(qū),其性能會有很大提升.

分區(qū)表還有一些其他限制,比如所有的底層表必須使用相同的存儲引擎,某些存儲引擎也不支持分區(qū).分區(qū)一般應用于一臺服務器上,但一臺服務器的物理資源總是有限的,當數(shù)據(jù)達到這個極限時,即使分區(qū),性能也可能會很低,所以這個時候分庫是必須的.但不管是分區(qū)、分庫還是分表,它們的思想都是一樣的,大家可以好好體會下.

視圖

對于一些關聯(lián)表的復雜查詢,使用視圖有時候會大大簡化問題,因此在許多場合下都可以看到視圖的身影,但視圖真如我們所想那樣簡單嗎?它和直接使用

JOIN

的SQL語句有何區(qū)別?視圖背后的原理又了解多少?

視圖本身是一個虛擬表,不存放任何數(shù)據(jù),查詢視圖的數(shù)據(jù)集由其他表生成.MySQL底層通過兩種算法來實現(xiàn)視圖：臨時表算法(TEMPTABLE)和合并算法(MERGE).所謂臨時表算法就是將SELECT語句的結(jié)果存放到臨時表中,當需要拜訪視圖的時候,直接拜訪這個臨時表即可.而合并算法則是重寫包含視圖的查詢,將視圖定義的SQL直接包含進查詢SQL中.通過兩個簡單的示例來體會兩個算法的差異,創(chuàng)建如下視圖：

// 視圖的作用是查詢未支付訂單

CREATE VIEW unpay_order AS

SELECT * FROM sales WHERE status = 'new'

WITH CHECK OPTION; // 其作用下文會講

現(xiàn)要從未支付訂單中查詢購買者為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

來查看使用視圖的查詢重寫后的結(jié)果.如果采用臨時表算法實現(xiàn)的視圖,

EXPLAIN

中會顯示為派生表(

DERIVED

),注意EXPLAIN時需要實際執(zhí)行并產(chǎn)生臨時表,所以有可能會很慢.

明顯地,臨時表上沒有任何索引,而且優(yōu)化器也很難優(yōu)化臨時表上的查詢,因此,如有可能,盡量使用合并算法會有更好的性能.那么問題來了：合并算法(類似于直接查詢)有更好的性能,為什么還要使用視圖?

首先視圖可以簡化應用上層的操作,讓應用更專注于其所關心的數(shù)據(jù).其次,視圖能夠?qū)γ舾袛?shù)據(jù)提供平安保護,比如：對不同的用戶定義不同的視圖,可以使敏感數(shù)據(jù)不出現(xiàn)在不應該看到這些數(shù)據(jù)的用戶視圖上；也可以使用視圖實現(xiàn)基于列的權(quán)限控制,而不需要真正的在數(shù)據(jù)庫中創(chuàng)建列權(quán)限.再者,視圖可以方便系統(tǒng)運維,比如：在重構(gòu)schema的時候使用視圖,使得在修改視圖底層表結(jié)構(gòu)的時候,應用代碼還可以繼續(xù)運行不報錯.

基于此,使用視圖其實更多的是基于業(yè)務或者維護成本上的考慮,其本身并不會對性能提升有多大作用(注意：此處只是基于MySQL考慮,其他關系性數(shù)據(jù)庫中視圖可能會有更好的性能,比如

ORACLE

和

MS SQL SERVER

都支持物化視圖,它們都比MySQL視圖有更好的性能).而且使用臨時表算法實現(xiàn)的視圖,在某些時候性能可能會非常糟糕,比如：

// 視圖的作用是統(tǒng)計每日支出金額,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;

現(xiàn)要統(tǒng)計每日的收入與支出,有類似于上面的收入表,可以使用如下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先執(zhí)行視圖的SQL,生成臨時表,然后再將

sale_per_day

表和臨時表進行關聯(lián).這里WHERE字句中的BETWEEN

條件并不能下推到視圖中,因而視圖在創(chuàng)建時,會將所有的數(shù)據(jù)放到臨時表中,而不是一個月數(shù)據(jù),并且這個臨時表也不會有索引.

當然這個示例中的臨時表數(shù)據(jù)不會太大,畢竟日期的數(shù)量不會太多,但仍然要考慮生成臨時表的性能(如果costs表數(shù)據(jù)過大,GROUP BY有可能會比較慢).而且本示例中索引也不是問題,通過上一篇我們知道,如果MySQL將臨時表作為關聯(lián)順序中的第一張表,仍然可以使用

sale_per_day

中的索引.但如果是對兩個視圖做關聯(lián)的話,優(yōu)化器就沒有任何索引可以使用,這時就需要嚴格測試應用的性能是否滿足需求.

我們很少會在實際業(yè)務場景中去更新視圖,因此印象中,視圖是不能更新的.但實際上,在某些情況下,視圖是可以更新的.可更新視圖是指通過更新這個視圖來更新視圖涉及的相關表,只要指定了合適的條件,就可以更新、刪除甚至是向視圖中插入數(shù)據(jù).通過上文的了解,不難推斷出：更新視圖的實質(zhì)就是更新視圖關聯(lián)的表,將創(chuàng)建視圖的

WHERE

子句轉(zhuǎn)化為

UPDATE

語句的

WHERE

子句,只有使用合并算法的視圖才能更新,并且更新的列必須來自同一個表中.回顧上文創(chuàng)建視圖的SQL語句,其中有一句：

WITH CHECK OPTION

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

WHERE

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

check option failed

錯誤.

視圖還有一個容易造成誤解的地方：“對于一些簡單的查詢,視圖會使用合并算法,而對于一些比較復雜的查詢,視圖就會使用臨時表算法”.但實際上,視圖的實現(xiàn)算法是視圖本身的屬性決定的,跟作用在視圖上的SQL沒有任何關系.那什么時候視圖采用臨時表算法,什么時候采用合并算法呢?一般來說,只要原表記錄和視圖中的記錄無法建立一一映射的關系時,MySQL都將使用臨時表算法來實現(xiàn)視圖.比如創(chuàng)建視圖的SQL中包含

GROUP BY

、

DISTINCT

、

UNION

、聚合函數(shù)、子查詢的時候,視圖都將采用臨時表算法(這些規(guī)則在以后的版本中,可能會發(fā)生改變,具體請參考官方手冊).

相比于其它關系型數(shù)據(jù)庫的視圖,MySQL的視圖在功能上會弱很多,比如

ORACLE

和

MS SQL SERVER

都支持物化視圖.物化視圖是指將視圖結(jié)果數(shù)據(jù)存放在一個可以查詢的表中,并定期從原始表中刷新數(shù)據(jù)到這張表中,這張表和普通物理表一樣,可以創(chuàng)建索引、主鍵約束等等,性能相比于臨時表會有質(zhì)的提升.但遺憾的是MySQL目前并不支持物化視圖,當然MySQL也不支持在視圖中創(chuàng)建索引.

存儲過程與觸發(fā)器

回到第二個問題,有非常多的人在分享時都會拋出這樣一個觀點：盡可能不要使用存儲過程,存儲過程非常不容易維護,也會增加使用成本,應該把業(yè)務邏輯放到客戶端.既然客戶端都能干這些事,那為什么還要存儲過程?

如果有深入了解過存儲過程,就會發(fā)現(xiàn)存儲過程并沒有大家描述的那么不堪.我曾經(jīng)經(jīng)歷過一些重度使用存儲過程的產(chǎn)品,依賴到什么程度呢?就這么說吧,上層的應用基本上只處理交互與動效的邏輯,所有的業(yè)務邏輯,甚至是參數(shù)的校驗均在存儲過程中實現(xiàn).曾經(jīng)有出現(xiàn)過一個超大的存儲過程,其文件大小達到驚人的80K,可想而知,其業(yè)務邏輯有多么復雜.在大多數(shù)人眼中,這樣的技術架構(gòu)簡直有點不可理喻,但實際上這款產(chǎn)品非常成功.

其成功的原因在一定程度上得益于存儲過程的優(yōu)點,由于業(yè)務層代碼沒有任何侵入業(yè)務的代碼,在不改變前端展示效果的同時,可以非常快速的修復BUG、開發(fā)新功能.由于這款產(chǎn)品需要部署在客戶的私有環(huán)境上,快速響應客戶的需求就變得尤為重要,正是得益于這種架構(gòu),可以在客戶出現(xiàn)問題或者提出新需求時,快速響應,極端情況下,我們可以在1小時內(nèi)修復客戶遇到的問題.正是這種快速響應機制,讓我們獲得大量的客戶.

當然存儲過程還有其他的優(yōu)點,比如,可以非常方便的加密存儲過程代碼,而不用擔心應用部署到私有環(huán)境造成源代碼泄露、可以像調(diào)試其他應用程序一樣調(diào)試存儲過程、可以設定存儲過程的使用權(quán)限來保證數(shù)據(jù)平安等等.一切都非常美好,但我們的產(chǎn)品是基于

MS SQL SERVER

實現(xiàn)的,其可以通過

T-SQL

非常方便的實現(xiàn)復雜的業(yè)務邏輯.你可以把

T-SQL

看做是一門編程語言,其包含

SQL

的所有功能,還具備流程控制、批處理、定時任務等能力,你甚至可以用其來解析XML數(shù)據(jù).關于

T-SQL

的更多信息可以參考

MSDN

,主流的關系型數(shù)據(jù)庫目前只有

MS SQL SERVER

支持

T-SQL

,因此,MySQL并不具備上文描述的一些能力,比如,MySQL的存儲過程調(diào)試非常不方便(當然可以通過付費軟件來獲得很好的支持).

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

優(yōu)化器無法評估存儲過程的執(zhí)行成本

每個連接都有獨立的存儲過程執(zhí)行計劃緩存,如果有多個連接需要調(diào)用同一個存儲過程,將會浪費緩存空間來緩存相同的執(zhí)行計劃

因此,在MySQL中使用存儲過程并不是一個太好策略,特別是在一些大數(shù)據(jù)、高并發(fā)的場景下,將復雜的邏輯交給上層應用實現(xiàn),可以非常方便的擴展已有資源以便獲得更高的計算能力.而且對于熟悉的編程語言,其可讀性會比存儲過程更好一些,也更加靈活.不過,在某些場景下,如果存儲過程比其他實現(xiàn)會快很多,并且是一些較小的操作,可以適當考慮使用存儲過程.

和存儲過程類似的,還有觸發(fā)器,觸發(fā)器可以讓你在執(zhí)行

INSERT

、

UPDATE

和

DELETE

時,執(zhí)行一些特定的操作.在MySQL中可以選擇在SQL執(zhí)行之前觸發(fā)還是在SQL執(zhí)行后觸發(fā).觸發(fā)器一般用于實現(xiàn)一些強制的限制,這些限制如果在應用程序中實現(xiàn)會讓業(yè)務代碼變得非常復雜,而且它也可以減少客戶端與服務器之間的通信.MySQL觸發(fā)器的實現(xiàn)非常簡單,所以功能非常有限,如果你在其他數(shù)據(jù)庫產(chǎn)品中已經(jīng)重度依賴觸發(fā)器,那么在使用MySQL觸發(fā)器時候需要注意,因為MySQL觸發(fā)器的表現(xiàn)和預想的不一致.

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

MyISAM

引擎下觸發(fā)器執(zhí)行失敗了,也不能回滾.在

InnoDB

表上的觸發(fā)器是在同一個事務中執(zhí)行完成的,所以她們的執(zhí)行是原子的,原操作和觸發(fā)器操作會同時失敗或者成功.

雖然觸發(fā)器有這么多限制,但它仍有適用的場景,比如,當你需要記錄MySQL數(shù)據(jù)的變更日志,這時觸發(fā)器就非常方便了.

外鍵約束

目前在大多數(shù)互聯(lián)網(wǎng)項目,特別是在大數(shù)據(jù)的場景下,已經(jīng)不建議使用外鍵了,主要是考慮到外鍵的使用成本：

外鍵通常要求每次修改數(shù)據(jù)時都要在另外一張表中執(zhí)行一次查找操作.在InnoDB存儲引擎中會強制外鍵使用索引,但在大數(shù)據(jù)的情況下,仍然不能忽略外鍵檢查帶來的開銷,特別是當外鍵的選擇性很低時,會導致一個非常大且選擇性低的索引.

如果向子表中插入一條記錄,外鍵約束會讓InnoDB檢查對應的父表的記錄,也就需要對父表對應記錄進行加鎖操作,來確保這條記錄不會在這個事務完成之時就被刪除了.這會導致額外的鎖等待,甚至會導致一些死鎖.

高并發(fā)場景下,數(shù)據(jù)庫很容易成為性能瓶頸,自然而然的就希望數(shù)據(jù)庫可以水平擴展,這時就需要把數(shù)據(jù)的一致性控制放到應用層,也就是讓應用服務器可以承擔壓力,這種情況下,數(shù)據(jù)庫層面就不能使用外鍵.

因此,當不用過多考慮數(shù)據(jù)庫的性問題時,比如一些內(nèi)部項目或傳統(tǒng)行業(yè)項目(其使用人數(shù)有限,而且數(shù)據(jù)量一般不會太大),使用外鍵是一個不錯的選擇,畢竟想要確保相關表始終有一致的數(shù)據(jù),使用外鍵要比在應用程序中檢查一致性方便簡單許多,此外,外鍵在相關數(shù)據(jù)的刪除和更新操作上也會比在應用中要高效.

綁定變量

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

prepared statement

.綁定變量的SQL,使用問號標記可以接收參數(shù)的位置,當真正需要執(zhí)行具體查詢的時候,則使用具體的數(shù)值代替這些問號,比如：

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

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

當創(chuàng)建一個綁定變量SQL時,客戶端向服務器發(fā)送了一個SQL語句原型,服務器收到這個SQL語句的框架后,解析并存儲這個SQL語句的部分執(zhí)行計劃,返回給客戶端一個SQL語句處理句柄,從此以后,客戶端通過向服務器發(fā)送各個問號的取值和這個句柄來執(zhí)行一個具體查詢,這樣就可以更高效地執(zhí)行大量重復語句,因為：

服務器只需要解析一次SQL語句

服務器某些優(yōu)化器的優(yōu)化工作也只需要做一次,因為MySQL會緩存部分執(zhí)行計劃

通信中僅僅發(fā)送的是參數(shù),而不是整個語句,網(wǎng)絡開銷也會更小,而且以二進制發(fā)送參數(shù)和句柄要比發(fā)送ASCII文本的效率更高

需要注意的是,MySQL并不是總能緩存執(zhí)行計劃,如果某些執(zhí)行計劃需要根據(jù)參入的參數(shù)來計算時,MySQL就無法緩存這部分執(zhí)行計劃.比如：

// 這里假裝有一個例子,大家可以自己思考一下

使用綁定變量的最大陷阱是：你知道其原理,但不知道它是如何實現(xiàn)的.有時候,很難解釋如下3種綁定變量類型之間的區(qū)別：

客戶端模擬的綁定變量：客戶端的驅(qū)動程序接收一個帶參數(shù)的SQL,再將參數(shù)的值帶入其中,最后將完整的查詢發(fā)送到服務器.

服務器綁定變量：客戶端使用特殊的二進制協(xié)議將帶參數(shù)的SQL語句發(fā)送到服務器端,然后使用二進制協(xié)議將具體的參數(shù)值發(fā)送給服務器并執(zhí)行.

SQL接口的綁定變量：客戶端先發(fā)送一個帶參數(shù)的SQL語句到服務器端,這類似于使用

prepared

的SQL語句,然后發(fā)送設置的參數(shù),最后在發(fā)送

execute

指令來執(zhí)行SQL,所有這些都是用普通的文本傳輸協(xié)議.

比如某些不支持預編譯的JDBC驅(qū)動,在調(diào)用

connection.prepareStatement(sql)

時,并不會把SQL語句發(fā)送給數(shù)據(jù)庫做預處理,而是等到調(diào)用

executeQuery

辦法時才把整個語句發(fā)送到服務器,這種方式就類似于第1種情況.因此,在程序中使用綁定變量時,理解你使用的驅(qū)動通過哪種方式來實現(xiàn)就顯得很有必要.延伸開來說,對于自己使用的框架、開源工具,不應僅僅停留在會使用這個層面,有時間可以深入了解其原理和實現(xiàn),不然有可能被騙了都不知道哦.

用戶自定義函數(shù)

MySQL本身內(nèi)置了非常多的函數(shù),比如

SUM

、

COUNT

、

AVG

等等,可實際應用中,我們常常需要更多.大多數(shù)情況下,更強大的功能都是在應用層面實現(xiàn),但實際上MySQL也提供了機會讓我們可以去擴展MySQL函數(shù),這就是用戶自定義函數(shù)(

user-defined function

),也稱為：

UDF

.需要注意

UDF

與存儲過程和通過SQL創(chuàng)建函數(shù)的區(qū)別,存儲過程只能使用SQL來編寫,而

UDF

沒有這個限制,可以使用支持C語言調(diào)用約定的任何編程語言來實現(xiàn).

UDF

必須事先編譯好并動態(tài)鏈接到服務器上,這種平臺相關性使得

UDF

在很多方面都很強大,

UDF

速度非常快,而且可以拜訪大量操作系統(tǒng)功能,還可以使用大量庫函數(shù).如果需要一個MySQL不支持的統(tǒng)計聚合函數(shù),并且無法使用存儲過程來實現(xiàn),而且還想不同的語言都可以調(diào)用,那么

UDF

是不錯的選擇,至少不需要每種語言都來實現(xiàn)相同的邏輯.

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

UDF

中的一個錯誤可能直接讓服務器崩潰,甚至擾亂服務器的內(nèi)存和數(shù)據(jù),因此,使用時需要注意其潛在的風險.在MySQL版本升級時也需要注意,因為你可能需要重新編譯或者修改這些

UDF

,以便讓它們能在新版本中工作.

這里有一個簡單的示例來展示如何創(chuàng)建

UDF

：將結(jié)果集轉(zhuǎn)化為JSON,具體的代碼請參考：lib_mysqludf_json.

// 1、首先使用c語言實現(xiàn)功能

// 2、編譯

// 這里省略第1、2步,實現(xiàn)并編譯成.so

// 3、使用SQL創(chuàng)建函數(shù)

drop function json_array;

create function json_array returns string soname 'lib_mysqludf_json.so';

// 4、使用函數(shù)

select json_array(

customer_id

, first_name

, last_name

, last_update

) as customer

from customer

where customer_id =1;

// 5、得到的結(jié)果如下：

+------------------------------------------+

| customer |

+------------------------------------------+

| [1,"MARY","SMITH","2006-02-15 04:57:20"] |

+------------------------------------------+

其大致的實現(xiàn)流程：使用C語言實現(xiàn)邏輯 -> 編譯成

.so

文件 -> 創(chuàng)建函數(shù) -> 使用函數(shù).

UDF

在實際工作中可能很少使用,但作為開發(fā)者的我們,了解這么一款強大的工具,在辦理棘手問題時,也讓我們有了更多的選擇.

字符集

最后說說字符集.

關于字符集大多數(shù)人的第一印象可能就是：數(shù)據(jù)庫字符集盡量使用UTF8,因為UTF8

字符集是目前最適合于實現(xiàn)多種不同字符集之間的轉(zhuǎn)換的字符集,可以最大程度上避免亂碼問題,也可以方便以后的數(shù)據(jù)遷移.But why?

字符集是指一種從二進制編碼到某類字符符號的映射,可以參考如何使用一個字節(jié)來表示英文字母.校對規(guī)則是指一組用于某個字符集的排序規(guī)則,即采用何種規(guī)則對某類字符進行排序.MySQL每一類編碼字符都有其對應的字符集和校對規(guī)則.MySQL對各種字符集的支持都非常完善,但同時也帶來一些復雜性,某些場景下甚至會有一些性能犧牲.

一種字符集可能對應多種校對規(guī)則,且都有一個默認校對規(guī)則,那在MySQL中是如何使用字符集的?在MySQL中可以通過兩種方式設置字符集：創(chuàng)建對象時設置默認值、客戶端與服務器通信時顯式設置.

MySQL采用“階梯”式的方式來設定字符集默認值,每個數(shù)據(jù)庫,每張表都有自己的默認值,它們逐層繼承,最終最靠底層的默認設置將影響你創(chuàng)建的對象.比如,創(chuàng)建數(shù)據(jù)庫時,將根據(jù)服務器上的

character_set_server

來設置數(shù)據(jù)庫的默認字符集,同樣的道理,根據(jù)

database

的字符集來指定庫中所有表的字符集......不管是對數(shù)據(jù)庫,還是表和列,只有當它們沒有顯式指定字符集時,默認字符集才會起作用.

當客戶端與服務器通信時,它們可以使用不同的字符集,這時候服務器將進行必要的轉(zhuǎn)換工作.當客戶端向服務器發(fā)送哀求時,數(shù)據(jù)以

character_set_client

設置的字符集進行編碼；而當服務器收到客戶端的SQL或者數(shù)據(jù)時,會依照

character_set_connection

設置的字符集進行轉(zhuǎn)換；當服務器將要進行增刪改查等操作前會再次將數(shù)據(jù)轉(zhuǎn)換成

character_set_database(數(shù)據(jù)庫采用的字符集,沒有單獨配置即使用默認配置,具體參考上文)

,最后當服務器返回數(shù)據(jù)或者錯誤信息時,則將數(shù)據(jù)按

character_set_result

設置的字符集進行編碼.服務器端可以使用

SET CHARACTER SET

來改變上面的配置,客戶端也可以根據(jù)對應的API來改變字符集配置.客戶端和服務器端都使用正確的字符集才能避免在通信中出現(xiàn)問題.

那如何選擇字符集?

在考慮使用何種字符集時,最主要的衡量因素是存儲的內(nèi)容,在能夠滿足存儲內(nèi)容的前提下,盡量使用較小的字符集.因為更小的字符集意味著更少空間占用、以及更高的網(wǎng)絡傳輸效率,也間接提高了系統(tǒng)的性能.如果存儲的內(nèi)容是英文字符等拉丁語系字符的話,那么使用默認的

latin1

字符集完全沒有問題,如果需要存儲漢字、俄文、阿拉伯語等非拉丁語系字符,則建議使用

UTF8

字符集.當然不同字符在使用

UTF8

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

UTF8

字符集中只使用一個字節(jié),而一個漢字則占用3個字節(jié).

除了字符集,校對規(guī)則也是我們需要考慮的問題.對于校對規(guī)則,一般來說只需要考慮是否以大小寫敏感的方式比較字符串或者是否用字符串編碼的二進制來比較大小,其對應的校對規(guī)則的后綴分別是

_cs

、

_ci

和

_bin

.大小寫敏感和二進制校對規(guī)則的不同之處在于,二進制校對規(guī)則直接使用字符的字節(jié)進行比較,而大小寫敏感的校對規(guī)則在多字節(jié)字符集時,如德語,有更復雜的比較規(guī)則.舉個簡單的例子,

UTF8

字符集對應校對規(guī)則有三種：

utf8_bin

將字符串中的每一個字符用二進制數(shù)據(jù)存儲,區(qū)分大小寫

utf8_general_ci

不區(qū)分大小寫,

ci

為

case insensitive

的縮寫,即大小寫不敏感

utf8_general_cs

區(qū)分大小寫,

cs

為

case sensitive

的縮寫,即大小寫敏感

比如,創(chuàng)建一張表,使用

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

字符集是完全沒有問題的,但需要記住的是不要在一個數(shù)據(jù)庫中使用多個不同的字符集,不同字符集之間的不兼容問題很難纏.有時候,看起來一切正常,但是當某個特殊字符出現(xiàn)時,一切操作都會出錯,而且你很難發(fā)現(xiàn)錯誤的原因.

字符集對數(shù)據(jù)庫的性能有影響嗎?

某些字符集和校對規(guī)則可能會需要多個的CPU操作,可能會消耗更多的內(nèi)存和存儲空間,這點在前文已經(jīng)說過.特別是在同一個數(shù)據(jù)庫中使用不同的字符集,造成的影響可能會更大.

不同字符集和校對規(guī)則之間的轉(zhuǎn)換可能會帶來額外的系統(tǒng)開銷,比如,數(shù)據(jù)表

sales

在

buyer

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

ORDER BY

操作：

SELECT order_no,order_amount FROM sales ORDER BY buyer;

只有當SQL查詢中排序要求的字符集與服務器數(shù)據(jù)的字符集相同時,才能使用索引進行排序.你可能會說,這不是廢話嗎?其實不然,MySQL是可以單獨指定排序時使用的校對規(guī)則的,比如：

// 你說,這不是吃飽了撐的嗎?我覺得也是,也許會有其適用的場景吧

// 這時候就不能使用索引排序呢,只能使用文件排序

SELECT order_no,order_amount FROM sales ORDER BY buyer COLLATE utf8_bin;

當使用兩個字符集不同的列來關聯(lián)兩張表時,MySQL會嘗試轉(zhuǎn)換其中一個列的字符集.這和在數(shù)據(jù)列外面封裝一個函數(shù)一樣,會讓MySQL無法使用這個列上的索引.關于MySQL字符集還有一些坑,但在實際應用場景中遇到的字符集問題,其實不是特別的多,所以就此打住.

結(jié)語

MySQL還有一些其他高級特性,但在大多數(shù)場景下我們很少會使用,因此這里也沒有討論,但多了解一些總是好的,至少在需要的時候,你知道有這樣一個東西.我們非常多的人,總是會認為自己所學的知識就像碎片一樣不成體系,又找不到解決方法,那你有沒有想過也許是碎片不夠多的緣故?點太少,自然不能連接成線,線太少,自然不能結(jié)成網(wǎng).因而,沒有其他方法,保持好奇心、多學習、多積累,量變總有一天會質(zhì)變,寫在這兒,與大家共勉吧.

前面我寫的一些文章里面會有提到過,架構(gòu)設計是一種平衡的藝術,其實質(zhì)應該是一種妥協(xié),是對現(xiàn)有資源的一種妥協(xié).有時候我們會不自覺的陷入某一個點,比如,為了追求數(shù)據(jù)的擴展性,很多人一上來就開始分庫分表,然后把應用搞得非常復雜,到最后表里還沒有裝滿數(shù)據(jù),項目就已經(jīng)死了.所以在資源有限或者未來還不可知的情況下,盡量使用數(shù)據(jù)庫、語言本身的特性來完成相應的工作,是不是會更好一點.辦理大數(shù)據(jù)問題,也不只是分庫分表,你還應該還可以想到分區(qū)；有些業(yè)務即使在分布式環(huán)境下也不一定非要在業(yè)務層完成,合理使用存儲過程和觸發(fā)器,也許會讓你更輕松.