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

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

1). 當客戶進行去O數據遷移時,存在必須改、不用改和可改可不改的三大類SQL.對于可改可不改的,我們應提供一些指導性的建議,贊助用戶規避將來碰到可能存在的問題.

2). 指導數據庫研發人員、數據庫管理員合理使用MySQL,施展MySQL最優性能.

1 并行處置

1.1 配景介紹

Oracle能夠將一個大型串行任務(任何DML,一般的DDL)物理的劃分為叫多個小的部分,這些較小的部分可以同時得到處理,最后將每個較小部分得到的結果組合起來得到最閉幕果,所以Oracle在OLAP的應用場景中可以利用并行處理技術來運行非常復雜的SQL查詢.

啟動并行查詢幾種方式：

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

(2)、利用alter table修改表：alter table test_a parallel 4;--告訴oracle,在創立這個表的執行計劃時,使用并行度4.

1.2 改革建議

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

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

(2)、將復雜SQL語句進行業務分解,拆解為單條的SQL語句,將計算成果放到應用中進行處理.

2 SQL執行邏輯讀,物理讀,消耗光陰

2.1 配景介紹

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

物理讀：把數據從數據塊讀取到buffer cache中.

邏輯讀：指從Buffer Cache中讀取數據塊.

執行光陰：Oracle執行一條SQL所消耗的光陰.

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

select * from t ;

(2)、第二次查詢：

select * from t；

第一次查詢有6次物理讀,第二次查詢有0個物理讀,6個邏輯讀.當數據塊第一次讀取到,就會緩存到buffer cache 中,而第二次讀取和改動該數據塊時就在內存buffer cache 了.

Oracle性能調優中,邏輯讀是個很重要的度量值,它不僅容易收集,并且能夠告訴我們許多關于數據庫引擎工作量的信息.邏輯讀是在執行SQL語句的時候從高速緩存中讀取的塊數.

2.2 改革建議

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

(1)、單表查詢邏輯讀,物理讀和執行時間比擬長的情況,SQL可能發生了全表掃描(dump需求)或者索引不優,可以使用只讀節點來承受dump或者對索引進行優化.

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

(3)、多表查詢邏輯讀,物理讀和執行時間比擬長的情況,可以進行業務分解,拆解為單條的SQL語句,將計算結果放到應用中進行處理.

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

3.In (…..)

3.1 配景介紹

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

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版本以前處理子查詢的時候由于優化器只支持nested loop算法,所以當關聯表較大的時候會帶來性能瓶頸.筆者曾經加入過一次大型項目從Oracle遷移的MySQL的遷移,當時數據庫的版本是5.5,原Oracle應用中存在大量的子查詢,當遷移到MySQL后SQL執行出現堆積,連接數打滿,數據庫的cpu很快耗完,最后將子查詢修改后系統才恢復.

典型子查詢

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版本中都存在較年夜風險,將子查詢改為關聯.

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

5 視圖優化

5.1 配景介紹

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

5.2 改革建議

將視圖進行業務拆分,由利用進行實現.

6 函數索引

6.1 配景介紹

基于函數的索引,類似于普通的索引,只是普通的索引是建立在列上,而它是建立在函數上.當然這回對插入數據有一定影響,因為需要通過函數計算一下,然后生成索引.但是插入數據一般都是少量插入,而查詢數據一般數據量比擬大.為了優化查詢速度,稍微降低點插入速度是可以承擔的.

MySQL并不支持函數索引,所以當Oracle中有使用函數索引的SQL語句遷移到MySQL后,由于無法使用索引導致全表掃描會呈現性能下降.

好比執行如下一條SQL語句：

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

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

6.2 改革建議

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

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

7 總結

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

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

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

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

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

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