Oracle迁移到MySQL性能下降的注意点

更多深度文章,请存眷云计算频道：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避免索引无法使用.