Oracle遷移到MySQL性能下降的注意點有哪些

Oracle遷移到MySQL性能下降的注意點有哪些，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

最近有較多的客戶系統由原來由Oracle改造到MySQL后出現了性能問題CPU 100%，或是后臺的CRM系統復雜SQL在業務高峰的時候出現堆積導致業務故障。

在我的記憶里面淘寶最初從Oracle遷移到MySQL期間也遇到了很多SQL的性能問題，記憶最為深刻的子查詢，當初的版本是MySQL5.1，這個版本對子查詢的優化較差，導致了很多從Oracle遷移到MySQL的系統出現過性能問題，所以后面的開發規范中規定前臺交易系統不要有復雜的表join。

接下來我將列舉一些常見從Oracle遷移到MySQL過程中可能出現問題的點：

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

2.  指導數據庫研發人員、數據庫管理員合理使用MySQL，發揮MySQL最優性能。

并行處理

背景介紹

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。

改造建議

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

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

   
   

2. 將復雜SQL語句進行業務分解，拆解為單條的SQL語句，將計算結果放到應用中進行處理。

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

背景介紹

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

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

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

3. 執行時間：Oracle執行一條SQL所消耗的時間。

• 第一次查詢一個表t
  select * from t ;

• 第二次查詢：
  select * from t；

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

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

改造建議

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

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

   
   

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

   
   

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

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

In (…..)

背景介紹

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

改造建議

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

子查詢

背景介紹

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表中的每一條記錄，代入到子查詢中中去

改造建議

改寫子查詢
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的版本，可以避免麻煩的子查詢改寫的問題。

視圖優化

背景介紹

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

改造建議

將視圖進行業務拆分，由應用進行實現。

函數索引

背景介紹

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

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取訪問表中的數據，這個速度是比較快的。

改造建議

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

select * from emp where gmt_create>=’2017-01-20 00:00:00’ and gmt_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避免索引無法使用。

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。