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

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