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

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

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

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

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

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

2.  指導數(shù)據(jù)庫研發(fā)人員、數(shù)據(jù)庫管理員合理使用MySQL，發(fā)揮MySQL最優(yōu)性能。

并行處理

背景介紹

Oracle能夠?qū)⒁粋€大型串行任務（任何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，在創(chuàng)建這個表的執(zhí)行計劃時，使用并行度4。

改造建議

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

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

   
   

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

SQL執(zhí)行邏輯讀，物理讀，消耗時間

背景介紹

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

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

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

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

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

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

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

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

改造建議

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

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

   
   

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

   
   

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

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

In (…..)

背景介紹

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

改造建議

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

子查詢

背景介紹

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

典型子查詢
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版本中都存在較大風險，將子查詢改為關聯(lián)。

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

視圖優(yōu)化

背景介紹

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

改造建議

將視圖進行業(yè)務拆分，由應用進行實現(xiàn)。

函數(shù)索引

背景介紹

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

MySQL并不支持函數(shù)索引，所以當Oracle中有使用函數(shù)索引的SQL語句遷移到MySQL后，由于無法使用索引導致全表掃描會出現(xiàn)性能下降。
比如執(zhí)行如下一條SQL語句：

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

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

改造建議

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

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優(yōu)化器較弱，對于邏輯讀，物理讀和執(zhí)行時間較長的SQL需要注意。

3. MySQL對于in(…)參數(shù)數(shù)目建議不要超過100個。

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

5. MySQL不支持物化視圖，建議應用改造視圖。

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

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