记一次MySQL迁移并从MySQL5.6升级到5.7后查询慢了几十倍的问题
原由
由于生产环境数据量愈来愈大，客户愈来愈多，项目功能也愈来愈多，项目自己也愈来愈多，致使以前的服务器内存、硬盘都已经渐渐的不够用了，当时出现了2种解决方案，增长服务器配置和新购服务器，可是就算是新增硬盘，也须要对数据库进行迁移，因此就采用了新购服务器的方案，而且由于以前是高效云盘，出现过IO占满的状况，因此对于新购的服务器采用了SSD硬盘，理论上速度会飞起来了，实际上我在新服务器上安装MySQL5.7，由于据说MySQL5.7性能提高了N倍，还支持json（虽然对咱们没什么用），可是毕竟MySQL8已经出来了，说明MySQL5.7也确定稳定好了。so，就找了个夜深人静的晚上偷偷的吧数据库迁移过来了，而后开启慢查询日志，限制为5秒，因而开始各类测试，而后查看了一下慢查询日志，一堆慢查询日志，因此有了这篇文章，为何会出现这么多慢查询，以及如何解决mysql
开始排查
将慢查询SQL拿出来，发现主要的慢查询SQL都是链表查询的语句，也就是说查询语句自己很是复杂，因此就把SQL语句放回以前的数据库执行，发现以前的数据库都是不到1秒就查询出来了，而在新的数据库上最慢能达到140多秒，这明显就不正常了，毕竟新的MySQL服务器不管是CPU、内存、仍是硬盘相对于之前的MySQL服务器来讲，都是好了不止一星半点，若是说性能差很少还能接受，可是一会儿慢了这么多，就明显是有异常了，因而开始挨个排查web
排查第一步：配置问题
首先2边服务器的配置文件是如出一辙的，由于就是从旧服务器吧配置文件复制过来的，可是由于MySQL的版本不一致，因此怀疑是由于新版有些配置不同，因此致使的，因而百度了MySQL5.7的优化配置，同时根据机器的内存CPU等硬件状况调整了部分配置，重启MySQL，执行语句，效果不明显，平均下来能快1秒左右，可是这1秒自己也能够认为是查询波动，因此不是配置的问题sql
排查第二步：硬件问题
首先CPU和内存应该不会有什么问题，惟一可能性就是SSD硬盘的问题，由于以前看到过由于SSD硬盘致使K,V键值存储性能低下，跟机械硬盘彻底不在同一个等级上，因此怀疑SSD是否须要开启什么特别的配置才行，因而百度，发现虽然有针对SSD的优化配置，可是也没有由于用了SSD致使速度很是慢的状况，因而针对SSD进行了优化配置，重启MySQL，执行语句，效果微乎其微数据库
排查第三步：语句问题
也是实在没办法了，才想到这个问题，可是我本身都以为不大可能，并且语句自己也优化的差很少了，小结果集驱动大结果集，索引根据where条件建立等。毕竟就算MySQL升级也不会说改变SQL语法之类的，最多就是在优化SQL的进行了一些特殊处理，因此先查看一下SQL语句的索引执行状况因而desc sql语句查看，跟旧库上面差异json
旧库（MySQL5.6）
服务器
新库（MySQL5.7）
在2个库数据，索引相同的状况下，竟然会出现索引引用和命中不同的状况，因此怀疑是不是迁移数据库的时候致使索引数据被破坏，因而百度去找，还真的发现了一个例子，也是迁移数据库后查询很是慢，后面重建索引以后恢复了，因而准备重建索引，因为表很是多，因此写了一个工具类来重建索引（惟一索引和普通索引，不包含主键索引），核心代码以下：app
List<HashMap> list = mapper.select1();
HashMap<String,HashMap<String,Object>> temp = new HashMap<>();
for(HashMap map : list){
String tableName = map.get("TABLE_NAME").toString();
String indexName = map.get("INDEX_NAME").toString();
String nonUnique = map.get("NON_UNIQUE").toString();
String columnName = map.get("COLUMN_NAME").toString();
if(temp.containsKey(tableName+"|"+indexName)){
HashMap<String,Object> value = temp.get(tableName+"|"+indexName);
List<String> columns = (List<String>) value.get("columns");
columns.add(columnName);
}else{
HashMap<String,Object> value = new HashMap<>();
value.put("nonUnique",nonUnique);
List<String> columns = new ArrayList<>();
columns.add(columnName);
value.put("columns",columns);
value.put("indexName",indexName);
value.put("tableName",tableName);
temp.put(tableName+"|"+indexName,value);
}
}
List<String> creates = new ArrayList<>();
List<String> drops = new ArrayList<>();
for(Map.Entry<String,HashMap<String,Object>> entry:temp.entrySet()){
String create = null;
String tableName = entry.getValue().get("tableName").toString();
String indexName = entry.getValue().get("indexName").toString();
String nonUnique = entry.getValue().get("nonUnique").toString();
List<String> columns = (List<String>) entry.getValue().get("columns");
drops.add("DROP INDEX "+indexName+" ON "+tableName+";");
if("0".equals(nonUnique)){
//惟一键索引
create = "CREATE UNIQUE INDEX "+indexName+" ON "+tableName+" (";
}else{
//建立普通索引
create = "CREATE INDEX "+indexName+" ON "+tableName+" (";
}
for(int i = 0;i < columns.size();i++){
if(i == columns.size() - 1){
create += columns.get(i)+");";
}else{
create += columns.get(i)+",";
}
}
creates.add(create);
}
for(String str : drops){
System.out.println(str);
}
for(String str : creates){
System.out.println(str);
}
查询全部索引SQL代码以下：svg
select * from information_schema.statistics WHERE INDEX_SCHEMA='xxxx' AND INDEX_NAME<>'PRIMARY'
其中xxxx是数据库实例名，代码执行完成后将打印出来的SQL语句放进SQL里面执行便可，固然也能够在使用Java调用SQL执行，不过我为了随时观察情况，因此把SQL复制出来执行工具
重建索引完成后执行SQL语句，发现速度仍是没有明显变化，说明不是由于索引数据异常的问题。性能
检查MySQL5.7新特性
百度查看MySQL5.7有没有更新什么新特性，看到了derived_merge特性，由于derived_merge是MySQL5.7的新的SQL优化方式，因此试着将derived_merge关闭，执行SQL
set GLOBAL optimizer_switch='derived_merge=off'
执行SQL，发现速度比旧服务器还快，而后用desc查看SQL索引使用状况，跟旧服务器也同样，因而问题解决
关闭derived_merge后的新问题
原本觉得关闭derived_merge后就万事大吉了，可是服务器的CPU占满却说明事情没有那么简单，top命令查看服务器CPU占满的缘由发现是由于MySQL（确定是MySQL，毕竟服务器就这一个软件），执行命令：
show full processlist;
查看卡住的连接信息，发现有大量的视图查询卡住，因而把SQL语句复制出来，发现只是查询单条数据，理论上不会这样慢，为了找出缘由，中止测试，重启MySQL，执行视图SQL语句，发现彻底卡住几分钟都不能执行完成，强行中止，检查视图的SQL是否有异常，发现视图的SQL也是普通的SQL（4个表的关联查询），理论上来讲不会耗费这么久的时间，把建立视图的SQL语句拿出来跟执行的视图的SQL条件拼接起来，用desc查看，发现索引正常命中，因而试着执行一次SQL，结果很是意外，速度很是快，因此觉得是服务器发疯，可是为了测试好，就又执行一下视图的SQL，结果为卡死。也就是说视图自己的SQL执行没有任何问题，可是使用视图查询，就会进入卡死状态。因而使用desc 查看视图SQL索引命中状况，发现结果跟直接的SQL不一样，下面是对比图：
视图
视图SQL
视图的索引命中状况明显比视图SQL索引命中多了一个索引，可是为何会形成卡死呢，缘由就在多的那个索引身上，仔细看能够看到，索引命中的行有==83141272975==行，11位数，上百亿，难怪会卡死，索引命中了上百亿的数据，那跟没有命中索引也没有区别了，并且最为关键的是，咱们整个库全部表加起来应该也没有上百亿的数据啊，毕竟目前最大的表数据量也才近千万，因此这个索引确定是有问题，数据也有问题，可是具体什么问题，就不是我了解的了，毕竟不是专业搞数据库的，因此也但愿了解的能帮我解答一下。
知道问题后，感受解决就简单了，百度搜索了一下MySQL5.7对视图是否进行了优化，可是不论是百度仍是谷歌都没有找到合适的答案，毕竟视图自己也只是存储了一个SQL语句而已，并无保存实际数据，也就是说就算优化也是针对SQL语句自己进行优化，可是SQL语句自己执行又没有任何问题，并且心想MySQL不可能将这么大个bug放出来吧，因而回想以前调过的参数，是不是由于修改了配置致使的，由于以前主要修改特性的配置就derived_merge，因此怀疑是由于derived_merge致使的，因而又打开derived_merge
set GLOBAL optimizer_switch='derived_merge=on'
执行视图，一切正常
排查第四步：索引命中问题
因为关闭了derived_merge会致使视图查询问题，而系统中用到了不少视图，因此若是不用视图的话须要对系统进行大的改动致使关闭derived_merge不现实，也就只能另想他法了，查询之因此慢的缘由主要仍是由于索引没有命中致使的，也就是说解决了索引命中的问题，就能解决查询慢的问题，先对比2个库命中的索引，发现主要是链表查询的时候ON后面跟的条件在新库上面没有命中索引，ON后面的条件在主表是跟其余列有组成联合索引的，而被连接的表有部分表是跟其余列组合成联合索引，有些表的列则没有任何索引，因而尝试着在被连接的表建立ON后面的字段单独的索引，建立以后，速度明显快了一倍，可是仍是有部分索引没有命中，因此又在主表对ON后面的字段单首创建索引（若是ON后面有几个条件，就建立联合索引），建立完成后，执行语句，秒查询出来，问题解决
尝试在旧库上优化索引
因为新库建立了索引后速度上明细比旧库快了不少，固然跟配置自己也有关系，因而相到再旧库上也建立一样的索引会不会更快了，因而在旧库上建立了跟新库相同的索引，执行SQL语句，比未建立索引以前慢了一倍，查看索引命中状况，虽然命中了更多的索引，可是也致使了命中的索引的行数增长
感悟
MySQL不一样的版本有不一样的SQL优化器，并且不一样的版本可能会出现索引命中规则不一样，另外索引并非越多查询就真的能更快，不合理的索引建立不只会致使插入慢，还会致使查询变慢，因此了解MySQL索引命中规则和了解所用的MySQL的SQL优化器是有必要的，以及不要轻易更新版本，天知道会出现什么莫名其妙的问题。。。。。