韋美雁，段華斌，周新林

（1.湖南科技學(xué)院電子與信息工程學(xué)院，永州 425199；2.湖南祁陽(yáng)大忠橋第二中學(xué)，永州 426100）

0 引言

MySQL數(shù)據(jù)庫(kù)是目前互聯(lián)網(wǎng)公司最常用的數(shù)據(jù)庫(kù)，因?yàn)槠渚哂虚_源、高性能、跨平臺(tái)支持、簡(jiǎn)單易用、支持多用戶等特點(diǎn)而被廣大用戶喜歡。然而在互聯(lián)網(wǎng)時(shí)代，各類數(shù)據(jù)量井噴，面對(duì)高并發(fā)、高訪問量的情況，數(shù)據(jù)庫(kù)研發(fā)人員和DBA對(duì)數(shù)據(jù)庫(kù)的優(yōu)化便顯得尤為重要。

1 MySQL優(yōu)化策略

為了提高數(shù)據(jù)庫(kù)的效率，我們需要考慮實(shí)際的應(yīng)用環(huán)境，不同的應(yīng)用環(huán)境下，我們選擇的優(yōu)化措施會(huì)有不同的特點(diǎn)。

1.1 存儲(chǔ)引擎的選擇

存儲(chǔ)引擎是MySQL的概念，選擇存儲(chǔ)引擎其實(shí)就是為數(shù)據(jù)存儲(chǔ)選擇合適的存儲(chǔ)機(jī)制和相應(yīng)的功能機(jī)制，MySQL常用的存儲(chǔ)引擎有InnoDB、MyISAM、Memo?ry、Merge[1，2]等。它們的性能特點(diǎn)詳見表1常見引擎性能比較。

（1）InnoDB

InnoDB存儲(chǔ)引擎是目前MySQL唯一擁有事務(wù)控制能力的存儲(chǔ)引擎、除了外鍵這一特點(diǎn)，從MySQL 5.6開始不僅支持全文索引，同時(shí)作為系統(tǒng)默認(rèn)的存儲(chǔ)引擎存在。其優(yōu)點(diǎn)在于能夠進(jìn)行事務(wù)處理，具有一定的并發(fā)控制能力以及系統(tǒng)崩潰時(shí)的修復(fù)能力，相對(duì)于MyISAM而言，它的讀寫效率相對(duì)較低，占用數(shù)據(jù)空間相對(duì)較大[3]。

表1 常見引擎性能比較

（2）Memory

Memory，支持hash索引，訪問速度快，并且因?yàn)樗臄?shù)據(jù)保留在內(nèi)存中，容易因?yàn)榉?wù)關(guān)閉（或重啟）而丟失，并且無法對(duì)它進(jìn)行恢復(fù)。因此，數(shù)據(jù)的安全性很低。

（3）MyISAM

在MySQL 5.5之前，MyISAM存儲(chǔ)引擎是默認(rèn)的存儲(chǔ)引擎，它支持3種存儲(chǔ)格式，分別是靜態(tài)類型、動(dòng)態(tài)類型和壓縮類型的，系統(tǒng)默認(rèn)為靜態(tài)類型，此時(shí)，字段長(zhǎng)度固定；對(duì)于動(dòng)態(tài)類型的其包含變長(zhǎng)字段；壓縮類型則需要使用myisampack工具。

該類存儲(chǔ)引擎的特點(diǎn)是占用空間小，處理速度較快，但是不能進(jìn)行事務(wù)處理。對(duì)表操作時(shí)以讀和插入為主。

（4）Merge

Merge存儲(chǔ)引擎表是由一組MyISAM表組成的邏輯結(jié)構(gòu)，但是，這些表的結(jié)構(gòu)要求完全相同。它可作為一個(gè)對(duì)象被使用，類似于視圖，本身也不存儲(chǔ)數(shù)據(jù)。

我們?cè)诮?shù)據(jù)表時(shí)需要根據(jù)應(yīng)用特點(diǎn)去選擇合適的存儲(chǔ)引擎，當(dāng)應(yīng)用是以插入、讀操作為主，且對(duì)事務(wù)的ACID特性沒有要求時(shí)，選擇MyISAM存儲(chǔ)引擎比較合適；如果要數(shù)據(jù)量大于單個(gè)MyISAM的大小，選擇Merge，它將不同的表分布在數(shù)個(gè)不同的磁盤上，從而改善Merge表的讀寫效率；如果對(duì)事務(wù)ACID要求較高，對(duì)數(shù)據(jù)的操作不僅有查詢和插入，還有更新、刪除等，那么應(yīng)該選取InnoDB存儲(chǔ)引擎；如果要求讀寫速度很快，且對(duì)數(shù)據(jù)的安全性要求較低，同時(shí)數(shù)據(jù)表比較小，那么可以選擇Memory。

1.2 SQL優(yōu)化

（1）開發(fā)期的優(yōu)化

在應(yīng)用開發(fā)初期，開發(fā)人員考慮更多的是功能的實(shí)現(xiàn)，然而在產(chǎn)品投入使用后，隨著數(shù)據(jù)量的激增，一些SQL語(yǔ)句可能會(huì)成為整個(gè)系統(tǒng)的性能瓶頸，因此，在產(chǎn)品上線前，作為開發(fā)者盡量能夠?qū)QL語(yǔ)句優(yōu)化處理，避免一些可以預(yù)知的問題的產(chǎn)生。

①避免進(jìn)行全表掃描

為了查詢方便，我們往往會(huì)在在where子句和or?der by子句相關(guān)的列上創(chuàng)建索引。理想的情況是，查詢時(shí)直接使用索引從而提高效率。但是，會(huì)有如下幾種情況[4]導(dǎo)致系統(tǒng)放棄索引不用而進(jìn)行全表掃描的現(xiàn)象，這樣勢(shì)必會(huì)降低系統(tǒng)的查詢性能。

●where子句中對(duì)null進(jìn)行判斷。

如出現(xiàn)select sname from s where sdept is null，那么會(huì)出現(xiàn)全表掃描的現(xiàn)象，為了避免全表掃描，建議可以先對(duì)sdept進(jìn)行默認(rèn)數(shù)據(jù)設(shè)置，確保查詢列中沒有null。

●where子句中使用了!=或者＜>操作符。

●where子句中使用了or連接運(yùn)算符。

where子句中使用or運(yùn)算符時(shí)容易導(dǎo)致全表掃描發(fā)生，此時(shí)可以使用union all進(jìn)行連接從而避免全表掃描現(xiàn)象。假設(shè)我們從學(xué)生選課系統(tǒng)中選擇計(jì)算機(jī)系和數(shù)學(xué)系的學(xué)生時(shí)有命令A(yù)：select sname from s where sdept='cs'or sdept='ma';命令 B:select sname from s where sdept='cs'union all select sname where sdept='ma';兩種表達(dá)，執(zhí)行命令A(yù)會(huì)進(jìn)行全表掃描，執(zhí)行命令B會(huì)進(jìn)行索引查詢（前提是以sdept為關(guān)鍵字創(chuàng)建了索引。）

●in，not in也會(huì)導(dǎo)致全表掃描，盡量避免使用，如果可以用between解決，就不用in。

●where子句中使用參數(shù)。

如：select sname from s where sdept=@sdept。此時(shí)，可以改為使用強(qiáng)制查詢使用索引命令：select sname from s with(index(索引名))where sdept=@sdept。

●like模糊查詢時(shí)字符串首字符為不確定值。

●在where子句中對(duì)屬性列進(jìn)行表達(dá)式操作。

例如：select sname from s where substring(sdept,1,2)='ma';可改為 select sname from s where sdept like'ma%';又如：select bookid,bookname from books where price/2＜30;改寫為 select bookid,bookname from books where price＜30*2;

總之，對(duì)屬性進(jìn)行求庫(kù)函數(shù)或者其他的表達(dá)式操作會(huì)導(dǎo)致表掃描，建議where子句的表達(dá)式左端只有屬性名不帶任何其他的附加操作，把操作都置于比較運(yùn)算符的右邊為好。

②臨時(shí)表的使用

●使用臨時(shí)表會(huì)占用較多的系統(tǒng)資源。若不斷的創(chuàng)建刪除，會(huì)降低系統(tǒng)的性能，因此，我們對(duì)臨時(shí)表的使用要注意節(jié)制。

●臨時(shí)表使用完成后，首先truncate顯式清除表中的所有數(shù)據(jù)，再drop表結(jié)構(gòu)，以免占用空間同時(shí)可能被長(zhǎng)時(shí)間鎖定。

③其他

●避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力；

●某操作導(dǎo)致向客戶端返回?cái)?shù)據(jù)量極大，需要重新審視應(yīng)用需求是否合理；

●游標(biāo)操作數(shù)據(jù)量比較大（超過1萬）時(shí)，建議改用其他方式完成，因?yàn)橛螛?biāo)效率低；

●建立聚簇索引時(shí)要注意考慮索引列是否會(huì)常被更新，如果更新頻繁，則該列不適合創(chuàng)建聚簇索引。

（2）上線后的SQL優(yōu)化

如果應(yīng)用系統(tǒng)已經(jīng)上線，那么對(duì)SQL優(yōu)化就需要進(jìn)行性能瓶頸定位，通常我們會(huì)首先使用show status確定服務(wù)器狀態(tài)，初步了解應(yīng)用是以查詢?yōu)橹鬟€是以更新為主等情況；其次，慢查詢?nèi)罩究梢詫?duì)低效的SQL進(jìn)行定位，而explain可以了解低效SQL執(zhí)行計(jì)劃，show profile分析資源消耗、trace優(yōu)化器等一系列操作來確定瓶頸所在。步驟[5，6]如下：

①show status命令

show status展示的參數(shù)有356條（5.7版本），其中以com開頭的參數(shù)記錄了各種數(shù)據(jù)庫(kù)的操作（創(chuàng)建、修改、刪除等）數(shù)，如 com_insert，執(zhí)行 insert的次數(shù)（批量插入的insert操作，計(jì)數(shù)一次）。如果是InnoDB開頭的參數(shù)，那么則只針對(duì)InnoDB存儲(chǔ)引擎表，這些參數(shù)記錄了緩沖區(qū)、讀寫等狀態(tài)。

我們還可以通過show status like來獲取具體的某類或者某個(gè)參數(shù)的狀態(tài)信息。例如：show status like'InnoDB_ROW%';從而更有目的地了解系統(tǒng)的狀態(tài)。

②定位SQL執(zhí)行效率低的語(yǔ)句

定位SQL低效的語(yǔ)句可以通過慢查詢?nèi)罩净蛘遱how processlist兩種方法。其中使用show processlist命令后顯示信息如圖1。各參數(shù)含義如表2。表2中的state的狀態(tài)有26種之多，它是一個(gè)非常重要的參數(shù)，通過該參數(shù)狀態(tài)的表達(dá)，我們可以了解SQL正在檢查數(shù)據(jù)表或者被鎖或者正在做排序，等等，從而，我們可以分析出低效SQL。

③explain分析低效SQL執(zhí)行計(jì)劃[5，6]

當(dāng)我們打開慢查詢捕捉到執(zhí)行效率差的SQL，此時(shí)我們還需要知道該SQL的執(zhí)行計(jì)劃，例如是全表掃描，還是索引掃描，這些都需要通過explain去完成。explain命令是查看優(yōu)化器決定執(zhí)行查詢的方法，該命令有助于理解MySQL的優(yōu)化器計(jì)劃決策。

圖1 show processlist命令

④show profile、trace

show profile命令是展示剛執(zhí)行過的SQL語(yǔ)句的持續(xù)使用資源的時(shí)間，show profiles則會(huì)把命令show warning使用資源的時(shí)間也列出來。如果前面執(zhí)行了數(shù)條SQL語(yǔ)句，而此時(shí)想查看指定的語(yǔ)句的資源使用情況，則可以根據(jù)show profile命令展示的query_ID來進(jìn)行，例如前面執(zhí)行了5條SQL命令（show profile之類的除外），編號(hào)query_ID從1到5代表相對(duì)應(yīng)的SQL命令，我們可以使用命令show profile for query編號(hào)查看具體的SQL的資源使用情況。show profile后面還可以直接跟type for query編號(hào)，此時(shí)的type可以是all、block IO、context switches、CPU、memory等特定的資源，從而更準(zhǔn)確地了解SQL的資源使用情況。

另外，我們也可以使用trace優(yōu)化器來了解跟蹤SQL語(yǔ)句。打開trace，設(shè)置格式為JSON，接著運(yùn)行想跟蹤的 SQL語(yǔ)句，查詢 information_schema.optimiz?er_trace表，跟蹤文件會(huì)展示優(yōu)化器邏輯優(yōu)化和物理優(yōu)化全過程，并據(jù)此確定選擇執(zhí)行計(jì)劃。

表2 show processlist的參數(shù)及含義

⑤確定響應(yīng)優(yōu)化措施

由上述步驟可以了解問題所在，采取相應(yīng)措施，進(jìn)行優(yōu)化，提高執(zhí)行效率。

1.3 表的優(yōu)化

（1）表的數(shù)據(jù)類型優(yōu)化

MySQL支持的數(shù)據(jù)類型很多，要獲取高性能數(shù)據(jù)庫(kù)，選擇正確的數(shù)據(jù)類型起著及其重要的作用。我們?cè)谶x擇數(shù)據(jù)類型時(shí)要遵循2個(gè)原則[9,10]：

●更小原則。這里的小，指的是存儲(chǔ)大小，例如使用tinyint就可以存儲(chǔ)數(shù)據(jù)，就不要再選用int來進(jìn)行存儲(chǔ)數(shù)據(jù)。在能夠保證數(shù)據(jù)正確存儲(chǔ)的前提下，我們選擇更小的數(shù)據(jù)類型。因?yàn)楣?jié)約資源（無論是CPU緩存還是磁盤亦或是內(nèi)存，甚至于系統(tǒng)處理數(shù)據(jù)對(duì)CPU的需求周期），并且讀寫速度更高效。

●簡(jiǎn)單原則。整數(shù)類型小于字符類型，這是由于字符集和排序規(guī)則導(dǎo)致字符類型數(shù)據(jù)更復(fù)雜。MySQL內(nèi)建類型（timestamp，date）優(yōu)于使用字符串保存。簡(jiǎn)單數(shù)據(jù)類型的操作通常需要更少的CPU周期。

如果表已經(jīng)創(chuàng)建好，屬于上線使用的情況，那么，想優(yōu)化表的數(shù)據(jù)類型，可以通過函數(shù)procedure analyse()對(duì)當(dāng)前應(yīng)用的表進(jìn)行分析，函數(shù)procedure analyse()可還以給對(duì)數(shù)據(jù)表中的列的數(shù)據(jù)類型提出優(yōu)化建議。當(dāng)然，還需要用戶最終自己確定是否采納。

（2）表的拆分

預(yù)測(cè)到單表數(shù)據(jù)未來會(huì)一直不斷上漲，整型表數(shù)據(jù)達(dá)千萬級(jí)，字符串為主的表達(dá)500萬級(jí)的情況下，可以考慮拆分表，但是拆分不作為首選技術(shù)，因?yàn)椴鸱謺?huì)帶來邏輯、部署、運(yùn)維的各種復(fù)雜度。常用的拆分方法有兩種：垂直拆分和水平拆分[10,11]。

①垂直拆分

垂直拆分是把表中的屬性按照常用和不常用兩部分進(jìn)行區(qū)分，然后將主碼和常用的屬性列部分放到一個(gè)表中，主碼和另外的不常用的屬性列放到另外一個(gè)表中。這樣一個(gè)表被分成兩個(gè)表，表的數(shù)量增加，但是常用的部分屬性列在一起，使用效率得以提高，而且降低計(jì)算機(jī)服務(wù)器的緩存等資源的占有率。

②水平拆分

水平拆分目的是通過某種策略（例如一列或者多列的列值）將數(shù)據(jù)分片來存儲(chǔ)。水平拆分有庫(kù)內(nèi)分表和分庫(kù)兩部分。庫(kù)內(nèi)分表其實(shí)就是分區(qū)，此時(shí)，在一定程度上能提升效能，但并不能真正的達(dá)到分布式的效果；分庫(kù)時(shí)數(shù)據(jù)會(huì)分散到不同的MySQL庫(kù)，從而達(dá)到分布式的效果，降低數(shù)據(jù)對(duì)同一個(gè)服務(wù)器的I/O操作，從而提高性能。表經(jīng)過水平拆分后，能夠支持非常大的數(shù)據(jù)量。

拆分原則：

●能不拆分就不拆分，可進(jìn)行SQL優(yōu)化處理；

●拆分時(shí)分片數(shù)量要盡量少，分片盡量均勻分布在多個(gè)數(shù)據(jù)結(jié)點(diǎn)上，因?yàn)橐粋€(gè)查詢SQL跨分片越多，則總體性能越差?？梢愿鶕?jù)需要在必要的時(shí)候擴(kuò)容。

●分片規(guī)則需要慎重選擇做好提前規(guī)劃，分片規(guī)則的選擇，需要考慮數(shù)據(jù)增加特點(diǎn)和訪問特點(diǎn)，同時(shí)也要考慮分片關(guān)聯(lián)性問題，以及分片擴(kuò)容問題等。常用的分片策略為范圍分片、枚舉分片、一致性Hash分片，這幾種分片都有利于擴(kuò)容。

水平拆分牽涉的邏輯比較復(fù)雜，我們可以采用一些客戶端架構(gòu)或者代理架構(gòu)來解決，如：MySQL官方出品的代理架構(gòu)MySQL Fabric，阿里巴巴的代理架構(gòu)Co?bar，阿里巴巴客戶端架構(gòu)Cobar client，淘寶的客戶端架構(gòu)TDDL等。

（3）逆規(guī)范化

逆規(guī)范化是一種通過添加冗余數(shù)據(jù)的數(shù)據(jù)庫(kù)優(yōu)化技術(shù)，其目的是為了是減少表與表的連接，減少外鍵和索引的數(shù)量，減少表的數(shù)量，它具有檢索速度快而簡(jiǎn)單的特點(diǎn)。缺點(diǎn)是更新和插入操作更費(fèi)事，腳本更難寫，數(shù)據(jù)可能不一致并且存在數(shù)據(jù)冗余。做逆規(guī)范前，要仔細(xì)考慮得與失，應(yīng)該首先分析應(yīng)用的存取數(shù)據(jù)的需求和性能特點(diǎn)，如果可以使用好的索引或者其他優(yōu)化方法能夠解決性能問題，就不必采用逆規(guī)范的做法。

常用的逆規(guī)范技術(shù)有增加冗余列、增加派生列、重新組表和拆分表[6,11]。

●增加冗余列：在表中增加其他表中已經(jīng)存在的列，它的存在往往是為了避免查詢時(shí)的連接操作。

●增加派生列：指增加的列來自其他表中的數(shù)據(jù)，由其他表中的數(shù)據(jù)經(jīng)過計(jì)算生成。增加派生列的作用是在查詢時(shí)減少連接操作，避免使用集函數(shù)。

●重新組表：當(dāng)兩個(gè)表連接后的結(jié)果數(shù)據(jù)經(jīng)常被查詢使用，那么把這兩個(gè)表重新組成一個(gè)表來減少連接以提高性能。

由于逆規(guī)范技術(shù)操作，可能破壞數(shù)據(jù)的完整性，為了管理好數(shù)據(jù)，我們通常采用觸發(fā)器等方式進(jìn)行維護(hù)。而觸發(fā)器有著良好的實(shí)時(shí)性、維護(hù)簡(jiǎn)單的特點(diǎn)，故它也是也是逆規(guī)范技術(shù)推薦的維護(hù)策略。

2 結(jié)語(yǔ)

MySQL是目前非常受歡迎的數(shù)據(jù)庫(kù)管理系統(tǒng)，本文首先討論了存儲(chǔ)引擎在不同的需求下的選取，然后討論了在系統(tǒng)開發(fā)和應(yīng)用的不同階段進(jìn)行SQL的優(yōu)化，最后討論了在大數(shù)據(jù)量的情況下優(yōu)化表格的方法，并提出了設(shè)計(jì)階段要注意的事項(xiàng)以及遇到性能瓶頸的處理措施，以提升數(shù)據(jù)庫(kù)的性能。