故事背景:公司之前的数据库存储使用的都是oracle,后面为了跟上时代步伐 进行了一次浩浩荡荡的去o过程,首当其冲的就是将oracle换成mysql,在后期帮甲方开发项目我们都统一使用mysql,由于我们的程序是一套程序适配两个数据库类型,这样就出问题了,问题点是突然某一天甲方反馈用户在高峰期 操作有点慢,对于这种情况,我们一般情况就是优化数据库创建索引,我们查询条件主要是前端传入时间范围条件,但是没有做好充分准备(数据库在甲方没有做监控),用创建oracle索引方式来创建mysql导致 mysql优化器走错索引,最终在上线第二天用户高峰期 导致系统基本处于瘫痪状态,由于数据库在甲方 不好排查,后面要求甲方技术将改表导出 我们做了一次分析 才发现是因为索引失效,重新创建索引后问题解决。
后面做了一次问题过程的回顾 和分析 现分析如下
Mysql5.8和oracle11.2 表结构一模一样 字段60个 数据量66w
场景一:创建联合索引 aa(A_DATE,A_ID) 时间列在前查询语句 这是当时出问题的时候的索引
题外话,mysql分析优化器使用explain查询 EXPLAIN FOR SELECT * FROM aa;
Oracle分析优化器使用SQL> EXPLAIN PLAN FOR SELECT * FROM aa;
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'))
Mysql查询结果
Oralce查询结果
场景二:创建联合索引 aa(A_ID,A_DATE) 时间列在后 查询语句
Mysql查询结果
Oracle查询结果
结论:根据场景一二,可以证明时间字段作为联合索引首例容易导致mysql索引优化器异常 甚至不会走改索引而全表扫描,而oracle不会走错索引,后面我们在查询oracle分析之前的索引,发现很多索引都是时间列在前,当时也没怎么想,也直接将oralce的建索引语句直接灌到mysql,导致mysql优化器失效。你以为问题就这么结束了吗,no,当我们将a1索引删除 创建a2索引发现 查询还是不理想,后面定位sql发现查询语句sql会变,也就是如果我们联合索引(列1 列2 列3) 而列1 后端程序会根据不同的条件时而不传 时而传,这种查询条件应该经常遇到,我们也做了一次 验证
场景一:我们创建联合索引a3( A_ID ,SERIAL_NUMBER,A_TASK_ID),但是我们查询条件 where a_id='401500000007' AND SERIAL_NUMBER='1042503619011674112' 两个条件根据前导列查询原则,mysql和oracle查询均走a3索引
Mysql查询结果
Oracle查询结果:
场景二:我们还是创建联合索引a3( A_ID ,SERIAL_NUMBER,A_TASK_ID),但是我们查询条件 where SERIAL_NUMBER='1042503619011674112' and A_TASK_ID='401500000007' 查询没有前导列 两者结果如何呢
Mysql查询结果
Oracle查询结果
结论:如果联合索引查询条件没有前导列都会导致数据库不走索引,而只要保证前导列存在 不管顺序如何都会走索引,后面根据优化结果 如果前端确实不想传改条件 我们重新根据业务条件 将开联合索引拆分创建两个联合索引 问题得到完美解决
总结:
- 在oracle转mysql的过程中不仅要改造sql语法 想对应的索引优化也需要根据不同的优化器去优化,oracle和mysql底层设计有很多不同,特别是针对索引这块,程序开发脚本编写以及优化都需要小心再小心
- 我们平时在测试环境由于数据量不够大 所以创建索引感觉问题不大,但是线上数据量大 创建索引需要结合当时场景 否则不小心索引创建会锁表 导致线上查询拥堵
- 在优化查询的过程中一定要结合业务 程序做优化 不能脱离各个业务部门,要做好分析,提前做好预演,部门之间要做好衔接