大數(shù)據(jù)環(huán)境下的MySQL優(yōu)化技術(shù)探討

韋美雁，段華斌，周新林

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

0 引言

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

1 MySQL優(yōu)化策略

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

1.1 存儲引擎的選擇

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

（1）InnoDB

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

表1 常見引擎性能比較

（2）Memory

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

（3）MyISAM

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

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

（4）Merge

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

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

1.2 SQL優(yōu)化

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

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

①避免進行全表掃描

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

●where子句中對null進行判斷。

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

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

●where子句中使用了or連接運算符。

where子句中使用or運算符時容易導致全表掃描發(fā)生，此時可以使用union all進行連接從而避免全表掃描現(xiàn)象。假設(shè)我們從學生選課系統(tǒng)中選擇計算機系和數(shù)學系的學生時有命令A：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';兩種表達，執(zhí)行命令A會進行全表掃描，執(zhí)行命令B會進行索引查詢（前提是以sdept為關(guān)鍵字創(chuàng)建了索引。）

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

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

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

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

●在where子句中對屬性列進行表達式操作。

例如：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;

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

②臨時表的使用

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

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

③其他

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

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

●游標操作數(shù)據(jù)量比較大（超過1萬）時，建議改用其他方式完成，因為游標效率低；

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

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

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

①show status命令

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

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

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

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

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

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

圖1 show processlist命令

④show profile、trace

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

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

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

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

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

1.3 表的優(yōu)化

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

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

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

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

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

（2）表的拆分

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

①垂直拆分

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

②水平拆分

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

拆分原則：

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

●拆分時分片數(shù)量要盡量少，分片盡量均勻分布在多個數(shù)據(jù)結(jié)點上，因為一個查詢SQL跨分片越多，則總體性能越差。可以根據(jù)需要在必要的時候擴容。

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

水平拆分牽涉的邏輯比較復雜，我們可以采用一些客戶端架構(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ù)庫優(yōu)化技術(shù)，其目的是為了是減少表與表的連接，減少外鍵和索引的數(shù)量，減少表的數(shù)量，它具有檢索速度快而簡單的特點。缺點是更新和插入操作更費事，腳本更難寫，數(shù)據(jù)可能不一致并且存在數(shù)據(jù)冗余。做逆規(guī)范前，要仔細考慮得與失，應(yīng)該首先分析應(yīng)用的存取數(shù)據(jù)的需求和性能特點，如果可以使用好的索引或者其他優(yōu)化方法能夠解決性能問題，就不必采用逆規(guī)范的做法。

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

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

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

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

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

2 結(jié)語

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