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

206次阅读

共计 3492 个字符，预计需要花费 9 分钟才能阅读完成。

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 语句，将计算结果放到应用中进行处理。

对比 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 语句的时候从高速缓存中读取的块数。

MySQL 对于简单的 SQL 语句执行是非常快的，对于 Oracle 应用中逻辑读，物理读或者执行时间非常高的 SQL 迁移到 MySQL 后则不在适合了，需要进行改造：

(1)、单表查询逻辑读，物理读和执行时间比较长的情况，SQL 可能发生了全表扫描（dump 需求）或者索引不优，可以使用只读节点来承受 dump 或者对索引进行优化。
(2)、多表查询逻辑读，物理读和执行时间比较长的情况，可以使用 ADS 分析型数据库产品来处理；
(3)、多表查询逻辑读，物理读和执行时间比较长的情况，可以进行业务分解，拆解为单条的 SQL 语句，将计算结果放到应用中进行处理。

备注：逻辑读和物理读如果超过 100W，执行时间超过 5S，则属于较大的 SQL 查询。

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 避免索引无法使用。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-03/142257.htm

正文完

星哥玩云-微信公众号