更多深度文章,請存眷云計(jì)算頻道：https://yq.aliyun.com/cloud

配景：最近有較多的客戶系統(tǒng)由原來由Oracle改造到MySQL后出現(xiàn)了性能問題CPU 100%,或是后臺的CRM系統(tǒng)復(fù)雜SQL在業(yè)務(wù)高峰的時(shí)候出現(xiàn)堆積導(dǎo)致業(yè)務(wù)故障.在我的記憶里面淘寶最初從Oracle遷移到MySQL期間也遇到了很多SQL的性能問題,記憶最為深刻的子查詢,當(dāng)初的版本是MySQL5.1,這個(gè)版本對子查詢的優(yōu)化較差,導(dǎo)致了很多從Oracle遷移到MySQL的系統(tǒng)出現(xiàn)過性能問題,所以后面的開發(fā)規(guī)范中規(guī)定前臺交易系統(tǒng)不要有復(fù)雜的表join.接下來我將列舉一些常見從Oracle遷移到MySQL過程中可能出現(xiàn)問題的點(diǎn)：

1). 當(dāng)客戶進(jìn)行去O數(shù)據(jù)遷移時(shí),存在必須改、不用改和可改可不改的三大類SQL.對于可改可不改的,我們應(yīng)提供一些指導(dǎo)性的建議,贊助用戶規(guī)避將來碰到可能存在的問題.

2). 指導(dǎo)數(shù)據(jù)庫研發(fā)人員、數(shù)據(jù)庫管理員合理使用MySQL,施展MySQL最優(yōu)性能.

1 并行處置

1.1 配景介紹

Oracle能夠?qū)⒁粋€(gè)大型串行任務(wù)(任何DML,一般的DDL)物理的劃分為叫多個(gè)小的部分,這些較小的部分可以同時(shí)得到處理,最后將每個(gè)較小部分得到的結(jié)果組合起來得到最閉幕果,所以O(shè)racle在OLAP的應(yīng)用場景中可以利用并行處理技術(shù)來運(yùn)行非常復(fù)雜的SQL查詢.

啟動并行查詢幾種方式：

(1)、在查詢中使用一個(gè)hint提示：select /*+ parallel(4) / count() from test_a ;---指定一個(gè)并行度為4的并行查詢.

(2)、利用alter table修改表：alter table test_a parallel 4;--告訴oracle,在創(chuàng)立這個(gè)表的執(zhí)行計(jì)劃時(shí),使用并行度4.

1.2 改革建議

由于MySQL不支持并行處理,所以當(dāng)應(yīng)用從Oracle遷移到MySQL后,必要特別注意使用了并行處理的SQL語句.處理建議：

(1)、在阿里云平臺上可以使用ADS這樣的分析型數(shù)據(jù)庫產(chǎn)物來處理Oracle中的并行分析查詢.

(2)、將復(fù)雜SQL語句進(jìn)行業(yè)務(wù)分解,拆解為單條的SQL語句,將計(jì)算成果放到應(yīng)用中進(jìn)行處理.

2 SQL執(zhí)行邏輯讀,物理讀,消耗光陰

2.1 配景介紹

對比MySQL的優(yōu)化器,Oracle的優(yōu)化器有著豐富和完善的優(yōu)化算法,僅表連接上Oracle支持nested loop、hash join、sort-merge join三種算法 ,而MySQL僅僅支持其中的nested loop算法,所以在一些大表關(guān)聯(lián)以及多表關(guān)聯(lián)的復(fù)雜查詢中MySQL的處理能力會明顯下降.那該如何去辨別一些不適合遷移到MySQL的查詢?可以根據(jù)SQL執(zhí)行中的一些關(guān)鍵數(shù)據(jù)：邏輯讀,物理讀,消耗時(shí)間來判斷.

物理讀：把數(shù)據(jù)從數(shù)據(jù)塊讀取到buffer cache中.

邏輯讀：指從Buffer Cache中讀取數(shù)據(jù)塊.

執(zhí)行光陰：Oracle執(zhí)行一條SQL所消耗的光陰.

(1)、第一次查詢一個(gè)表t

select * from t ;

(2)、第二次查詢：

select * from t；

第一次查詢有6次物理讀,第二次查詢有0個(gè)物理讀,6個(gè)邏輯讀.當(dāng)數(shù)據(jù)塊第一次讀取到,就會緩存到buffer cache 中,而第二次讀取和改動該數(shù)據(jù)塊時(shí)就在內(nèi)存buffer cache 了.

Oracle性能調(diào)優(yōu)中,邏輯讀是個(gè)很重要的度量值,它不僅容易收集,并且能夠告訴我們許多關(guān)于數(shù)據(jù)庫引擎工作量的信息.邏輯讀是在執(zhí)行SQL語句的時(shí)候從高速緩存中讀取的塊數(shù).

2.2 改革建議

MySQL對于簡單的SQL語句執(zhí)行是非常快的,對于Oracle應(yīng)用中邏輯讀,物理讀或者執(zhí)行時(shí)間非常高的SQL遷移到MySQL后則不在適合了,必要進(jìn)行改造：

(1)、單表查詢邏輯讀,物理讀和執(zhí)行時(shí)間比擬長的情況,SQL可能發(fā)生了全表掃描(dump需求)或者索引不優(yōu),可以使用只讀節(jié)點(diǎn)來承受dump或者對索引進(jìn)行優(yōu)化.

(2)、多表查詢邏輯讀,物理讀和執(zhí)行時(shí)間比擬長的情況,可以使用ADS分析型數(shù)據(jù)庫產(chǎn)品來處理；

(3)、多表查詢邏輯讀,物理讀和執(zhí)行時(shí)間比擬長的情況,可以進(jìn)行業(yè)務(wù)分解,拆解為單條的SQL語句,將計(jì)算結(jié)果放到應(yīng)用中進(jìn)行處理.

備注： 邏輯讀和物理讀如果跨越100W,執(zhí)行時(shí)間跨越5S,則屬于較大的SQL查詢.

3.In (…..)

3.1 配景介紹

Oracle中對in(….)的參數(shù)限制是1000個(gè),在MySQL中雖然沒有個(gè)數(shù)限制但有SQL長度的限制,同時(shí)優(yōu)化器在對in(…)的查詢進(jìn)行優(yōu)化的時(shí)候采納二分查找,所以in(...)的個(gè)數(shù)越多性能會越差,所以建議控制in的數(shù)目,不要超過100個(gè).

3.2 改革建議

Oracle：select * from t where id in(id1,id2…..id1000);

MySQL：select * from t where id in(id1,id2…..id100);

4 子查詢

4.1 配景介紹

MySQL在5.6版本以前處理子查詢的時(shí)候由于優(yōu)化器只支持nested loop算法,所以當(dāng)關(guān)聯(lián)表較大的時(shí)候會帶來性能瓶頸.筆者曾經(jīng)加入過一次大型項(xiàng)目從Oracle遷移的MySQL的遷移,當(dāng)時(shí)數(shù)據(jù)庫的版本是5.5,原Oracle應(yīng)用中存在大量的子查詢,當(dāng)遷移到MySQL后SQL執(zhí)行出現(xiàn)堆積,連接數(shù)打滿,數(shù)據(jù)庫的cpu很快耗完,最后將子查詢修改后系統(tǒng)才恢復(fù).

典型子查詢

SELECT first_name

FROM employees

WHERE emp_no IN

(SELECT emp_no FROM salaries_2000 WHERE salary = 5000);

MySQL的處置邏輯是遍歷employees表中的每一條記錄,代入到子查詢中中去

4.2 改革建議

改寫子查詢

SELECT first_name

FROM employees emp,

(SELECT emp_no FROM salaries_2000 WHERE salary = 5000) sal

WHERE emp.emp_no = sal.emp_no;

備注：子查詢在5.1,5.5版本中都存在較年夜風(fēng)險(xiǎn),將子查詢改為關(guān)聯(lián).

使用Mysql 5.6的版本,可以避免麻煩的子查詢改寫的問題.

5 視圖優(yōu)化

5.1 配景介紹

普通的視圖并沒有存儲實(shí)際的信息,它所操作的數(shù)據(jù)來自于基本表,所以在普通視圖上不可以創(chuàng)建索引.那當(dāng)必要對視圖進(jìn)行大量查詢,而查詢效率較低時(shí),如何處理呢?Oracle 中有物化視圖,物化視圖是物理真實(shí)存在的,可以創(chuàng)建索引.而MySQL并不支持物化視圖,所以當(dāng)Oracle中的視圖遷移到MySQL后由于沒有物化視圖,可能導(dǎo)致性能下降.

5.2 改革建議

將視圖進(jìn)行業(yè)務(wù)拆分,由利用進(jìn)行實(shí)現(xiàn).

6 函數(shù)索引

6.1 配景介紹

基于函數(shù)的索引,類似于普通的索引,只是普通的索引是建立在列上,而它是建立在函數(shù)上.當(dāng)然這回對插入數(shù)據(jù)有一定影響,因?yàn)樾枰ㄟ^函數(shù)計(jì)算一下,然后生成索引.但是插入數(shù)據(jù)一般都是少量插入,而查詢數(shù)據(jù)一般數(shù)據(jù)量比擬大.為了優(yōu)化查詢速度,稍微降低點(diǎn)插入速度是可以承擔(dān)的.

MySQL并不支持函數(shù)索引,所以當(dāng)Oracle中有使用函數(shù)索引的SQL語句遷移到MySQL后,由于無法使用索引導(dǎo)致全表掃描會呈現(xiàn)性能下降.

好比執(zhí)行如下一條SQL語句：

select * from emp where date(gmt_create) = '2017-02-20'

即使在gmt_create上建立了索引,還是會全表掃描emp表,將里面的gmt_create字段去除掉時(shí)分秒后進(jìn)行比較.如果我們建立一個(gè)基于函數(shù)的索引,比如：create index emp_upper_idx on emp(date(gmt_create)); 這個(gè)時(shí)候,我們只需要按區(qū)間掃描小部分?jǐn)?shù)據(jù),然后獲取rowid取拜訪表中的數(shù)據(jù),這個(gè)速度是比較快的.

6.2 改革建議

通過SQL改寫去除字段上的函數(shù),從而可以使用字段上的索引：

select * from emp where gmt_create>='2017-01-20 00:00:00’ and gmt_created<’2017-01-21 00:00:00’

7 總結(jié)

(1).MySQL不支持并行查詢,必要進(jìn)行改造(關(guān)鍵字：parallel).

(2).MySQL優(yōu)化器較弱,對于邏輯讀,物理讀和執(zhí)行時(shí)間較長的SQL必要注意.

(3).MySQL對于in(…)參數(shù)數(shù)目建議不要跨越100個(gè).

(4).MySQL對付子查詢優(yōu)化不是很好,建議改造子查詢或者使用5.6數(shù)據(jù)庫版本.

(5).MySQL不支持物化視圖,建議應(yīng)用改革視圖.

(6).MySQL不支函數(shù)索引,建議利用改寫SQL避免索引無法使用.