一次去o索引建错引起的血案

故事背景:公司之前的数据库存储使用的都是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查询结果

 

 

结论:如果联合索引查询条件没有前导列都会导致数据库不走索引，而只要保证前导列存在 不管顺序如何都会走索引，后面根据优化结果 如果前端确实不想传改条件 我们重新根据业务条件 将开联合索引拆分创建两个联合索引 问题得到完美解决

 

总结：

1. 在oracle转mysql的过程中不仅要改造sql语法 想对应的索引优化也需要根据不同的优化器去优化,oracle和mysql底层设计有很多不同，特别是针对索引这块，程序开发脚本编写以及优化都需要小心再小心
2. 我们平时在测试环境由于数据量不够大 所以创建索引感觉问题不大，但是线上数据量大 创建索引需要结合当时场景 否则不小心索引创建会锁表 导致线上查询拥堵
3. 在优化查询的过程中一定要结合业务 程序做优化 不能脱离各个业务部门,要做好分析，提前做好预演，部门之间要做好衔接