技術紅利，讓MySQL完美承接Oracle遷移

摘要： 最近有較多的客戶系統由原來由Oracle改造到MySQL後出現了效能問題CPU100%，或是背景CRM系統複雜SQL在商務高峰的時候出現堆積導致商務故障。在我的記憶裡面淘寶最初從Oracle遷移到MySQL期間也遇到了很多SQL的效能問題，記憶最為深刻的子查詢，當初的組建是MySQL5.1，這個組建對子查詢的優化較差，導致了很多從Oracle遷移到MySQL的系統出現過效能問題，所以後面的開發規格中規定前臺交易系統不要有複雜的表join。

幕後：最近有較多的客戶系統由原來由Oracle改造到MySQL後出現了效能問題CPU100%，或是背景CRM系統複雜SQL在商務高峰的時候出現堆積導致商務故障。在我的記憶裡面淘寶最初從Oracle遷移到MySQL期間也遇到了很多SQL的效能問題，記憶最為深刻的子查詢，當初的組建是MySQL5.1，這個組建對子查詢的優化較差，導致了很多從Oracle遷移到MySQL的系統出現過效能問題，所以後面的開發規格中規定前臺交易系統不要有複雜的表join。接下來我將列舉一些常見從Oracle遷移到MySQL程序中可能出現問題的點：

1. 當客戶進行去O資料移轉時，存在必須改、不用改和可改可不改的三大類SQL。對於可改可不改的，我們應提供一些指導性的建議，說明用戶規避將來碰到可能存在的問題。
2. 指導資料庫研發人員、資料庫管理員合理使用MySQL，發揮MySQL最優效能。

平行處理

1. 幕後介紹

Oracle能夠將一個大型串列任務（任何DML，一般的DDL）物理的劃分為叫多個小的部份，這些較小的部份可以同時得到處理，最後將每個較小部份得到的結果群組起來得到最終結果，所以Oracle在OLAP的套用場景中可以利用平行處理技術來執行非常複雜的SQL查詢。

啟動並行查詢幾種方式：

1. 在查詢中使用一個hint通知：select/*+ parallel(4)/ count() from test_a ;---指定一個並行度為4的並行查詢。
2. 利用altertable修改表：altertable test_a parallel 4;--告訴oracle，在建立這個表的執行計畫時，使用並行度4。

2. 改造建議

由於MySQL不支援平行處理，所以當套用從Oracle遷移到MySQL後，需要特別注意使用了平行處理的SQL語句。處理建議：

1. 在阿裡雲平臺上可以使用ADS這樣的剖析型資料庫產品來處理Oracle中的並行剖析查詢。
2. 將複雜SQL語句進行商務分解，拆解為單條的SQL語句，將計算結果放到套用中進行處理。

SQL執行邏輯讀，物理讀，消耗時間

1. 幕後介紹

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

物理讀：把資料從資料區塊讀取到buffercache中。 邏輯讀：指從BufferCache中讀取資料區塊。 執行時間：Oracle執行一條SQL所消耗的時間。

1. 第一次查詢一個表t select * from t ;
2. 第二次查詢： select * from t；

第一次查詢有6次物理讀，第二次查詢有0個物理讀，6個邏輯讀。當資料區塊第一次讀取到,就會快取到buffercache 中，而第二次讀取和修改該資料區塊時就在記憶體buffercache 了。

Oracle效能調優中，邏輯讀是個很重要的度量，它不僅容易收集，而且能夠告訴我們許多關於資料庫引擎投入量的資訊。邏輯讀是在執行SQL語句的時候從快取中讀取的塊數。

2. 改造建議

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

1. 單表查詢邏輯讀，物理讀和執行時間比較長的情況，SQL可能發生了全資料表掃描（dump需求）或者索引不優，可以使用唯讀節點來承受dump或者對索引進行優化。
2. 多表查詢邏輯讀，物理讀和執行時間比較長的情況，可以使用ADS剖析型資料庫產品來處理；
3. 多表查詢邏輯讀，物理讀和執行時間比較長的情況，可以進行商務分解，拆解為單條的SQL語句，將計算結果放到套用中進行處理。

備忘：邏輯讀和物理讀如果超過100W，執行時間超過5S，則屬於較大的SQL查詢。

In (…..)

1. 幕後介紹

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

2. 改造建議

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

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

子查詢

1. 幕後介紹 MySQL在5.6組建以前處理子查詢的時候由於優化器只支援nestedloop演算法，所以當關聯表較大的時候會帶來效能瓶頸。筆者曾經參加過一次大型項目從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表中的每一條記錄，代入到子查詢中中去

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組建中都存在較大風險，將子查詢改為關聯。 使用Mysql5.6的組建，可以避免麻煩的子查詢改寫的問題。

檢視優化

1. 幕後介紹

普通的檢視並沒有隱藏實際的資訊，它所動作的資料來自於基本表，所以在普通檢視上不可以建立索引。那當需要對檢視進行大量查詢，而查詢效率較低時，如何處理呢？Oracle中有物化檢視，物化檢視是物理真實存在的，可以建立索引。而MySQL並不支援物化檢視，所以當Oracle中的檢視遷移到MySQL後由於沒有物化檢視，可能導致效能下降。

2. 改造建議

將檢視進行商務分割，由套用進行實現。

函數索引

1. 幕後介紹

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

MySQL並不支援函數索引，所以當Oracle中有使用函數索引的SQL語句遷移到MySQL後，由於無法連絡索引導致全資料表掃描會出現效能下降。

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

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

即使在gmt_create上建立了索引，還是會全資料表掃描emp表，將裡面的gmt_create欄位去除掉時分秒後進行比較。如果我們建立一個基於函數的索引，比如：createindex emp_upper_idx on emp(date(gmt_create)); 這個時候，我們只需要按區間掃描小部份資料，然後追蹤rowid取存取表中的資料，這個速度是比較快的。

2. 改造建議

通過SQL改寫去除欄位上的函數，從而可以使用欄位上的索引：

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

總結

1. MySQL不支援並行查詢，需要進行改造（關鍵字：parallel）。
2. MySQL優化器較弱，對於邏輯讀，物理讀和執行時間較長的SQL需要注意。
3. MySQL對於in(…)參數數目建議不要超過100個。
4. MySQL對於子查詢優化不是很好，建議改造子查詢或者使用5.6資料庫組建。
5. MySQL不支援物化檢視，建議套用改造檢視。
6. MySQL不支函數索引，建議套用改寫SQL避免索引無法連絡。

相關產品：

1. 雲資料庫RDS