MySQL从MySQL5.6升级到5.7后查询变慢十倍的问题

生产环境数据量越来越大，客户越来越多，项目功能越来越多，而项目本身越来越多的结果是，之前服务器的内存和硬盘逐渐不足。当时就出现了两种解决方案，增加服务器配置和购买新服务器。不过即使加了新硬盘，数据库也得迁移，所以我们决定买一台新服务器，而且由于是高性能云盘，IO就满了，所以我用的是SSD硬盘新购买的服务器的磁盘。理论上来说，速度会暴涨。我实际上将其安装在新服务器上。 MySQL5.7因为听说MySQL5.7性能提升了N倍，而且还支持json（虽然对我们来说没什么用），但是毕竟MySQL8出来了，这意味着MySQL5.7一定是稳定的。于是我就在夜深人静的时候偷偷的迁移了数据库，然后开启了限时5秒的慢查询日志，于是我跑了各种测试，然后查看了慢查询日志。已经有很多慢查询日志了，所以看完这篇文章，下面就来说说为什么会有这么多慢查询以及如何解决

开始排查

把慢SQL查询出来，发现主要的慢SQL query是一个带有链表的查询语句，即查询语句本身就很复杂，所以我把SQL语句返回到之前的数据库去执行。我发现以前的数据库查询不到1秒，但在新数据库上最慢可以达到140秒以上。这显然是不正常的。毕竟，新的MySQL服务器在CPU、内存和硬盘方面都比以前的MySQL服务器要好很多。如果性能几乎可以接受，但突然慢了很多，这显然是一个问题。不正常，于是我开始一一删除

排查第一步：配置问题

首先，两台服务器的配置文件是一模一样的，因为配置文件是从旧服务器复制过来的，但是由于MySQL版本不一致，所以怀疑是新版本中一些配置不同导致的，于是百度优化了MySQL 5.7配置，根据机器内存、CPU等硬件情况调整了一些配置。我重新启动MySQL并执行命令。效果并不明显。平均下来可能会快1秒左右，但这1秒本身也可以认为是查询波动，所以不是配置问题

排查步骤二：硬件问题

首先应该是cpu和内存都没有问题，唯一可能是ssd硬盘的问题。之前看到K、V键值存储性能低是因为SSD硬盘，根本无法与机械硬盘相提并论。因此，我想SSD是否需要启用一些特殊的配置，于是百度了一下，发现虽然有针对SSD的优化配置，但是并没有出现因为使用SSD导致速度很慢的情况，所以我优化了配置对于SSD，重新启动MySQL并执行命令，但效果甚微

故障排除步骤3：命令问题

我想到这个问题是因为我实在没有其他选择，但我认为我自己和命令都不太可能本身就已经被优化得差不多了。小结果集驱动大结果集，根据where条件创建索引。毕竟，即使MySQL升级，也不会改变SQL语法。最多会做一些特殊的处理来优化SQL。所以首先检查index SQL命令的执行情况，然后检查desc sql命令的执行情况。与旧数据库不同

旧数据库（MySQL5.6）

新数据库（MySQL5.7） 对于相同数据的两个数据库，索引引用和访问实际上是不同的。 ，所以怀疑是不是数据库迁移的时候索引数据损坏了，于是百度搜索了一下，找到了例子。数据库迁移后查询也很慢。重建索引后恢复了，所以我准备重建索引。由于表太多了，所以写了一个工具类来重建索引（唯一索引和常规索引，主键索引除外）。基本代码如下：

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代码如下：

select * from information_schema.statistics WHERE INDEX_SCHEMA='xxxx' AND INDEX_NAME<>'PRIMARY'  

其中xxxx为数据库实例名称，代码运行完成后，将打印的SQL语句粘贴到SQL中即可并执行它。当然你也可以使用Java调用SQL来运行。不过为了随时监控情况，我复制了SQL命令并执行了

索引重建完成后运行SQL命令，发现速度依然没有变化，这说明是不是由于索引数据异常。

查看MySQL5.7新功能

百度查看MySQL5.7是否更新了新功能，看到了derivative_merge功能。因为衍生_合并是MySQL5.7新的SQL优化方法，所以尝试关闭衍生_合并并运行SQL

set GLOBAL optimizer_switch='derived_merge=off'  

运行SQL，看到速度比旧服务器快。然后使用desc查看SQL索引使用情况。和老服务器一样，所以问题解决了

关闭衍生_merge后新问题

原本以为关闭衍生_merge后就一切正常了，但是服务器的CPU满了，这说明事情没那么简单。使用top命令查看服务器CPU满的原因。原来是MySQL的原因（一定是MySQL，毕竟服务器是唯一的软件）。执行命令：

show full processlist;  

查看关于卡死的链接信息，发现大量的视图查询被卡住，于是我复制了SQL语句，发现只查询了一条数据。理论上不会那么慢。为了找出原因，我停止了测试，重新启动了MySQL，并执行了查看SQL命令。 ，他发现完全卡住了，有几分钟无法执行，于是被迫停下来检查视图的SQL是否有异常。发现视图的SQL也是普通SQL（4张表的相关查询）。理论上来说，不会花那么长时间。 ，取出创建视图的SQL语句与执行视图的SQL条件连接起来，用desc命令检查，看到索引命中正常，所以我就尝试执行一次SQL。结果很出乎意料，而且速度很高，所以我以为服务器疯了，但是为了好好测试一下，我又跑了一遍视图的SQL，结果是卡住了。也就是说，视图本身的SQL执行没有问题，但是在视图上使用查询的时候就卡住了。于是我用板子查看SQL索引视图情况，发现结果和直接SQL不一样。下面是对比表：

视图 SQL视图

索引视图的情况明显多于SQL视图索引命中索引创建的情况，但是为什么会卡住呢？原因就在于索引多了。仔细观察可以发现索引命中的行有83141272975行，11位，百亿。难怪会卡住。如果索引命中了百亿数据，那和索引没命中没有什么区别。而且最重要的是，我们整个数据库的表总数不超过数百亿条数据。毕竟表目前最大的数据量也才接近1000万，那么索引和数据肯定有问题，但具体问题我不知道。毕竟我不是数据库专业人士，希望懂的人帮我解答一下。

知道问题后，感觉解决办法很简单。我在百度上搜索了一下MySQL 5.7是否优化了显示，但是百度和Google都没有找到合适的答案。视图本身只存储 SQL 语句。就是这样，实际的数据并没有被保存，这意味着即使优化是优化SQL语句本身，执行SQL语句本身也没有问题，我还以为MySQL不会发出这么大的问题呢？ bug，所以我记得之前的编辑。是否因配置更改而更改了参数？因为之前主要修改函数的配置是derivative_merge，所以我怀疑是衍生_merge导致的，于是打开执行视图衍生_merge

set GLOBAL optimizer_switch='derived_merge=on'  

一切正常

排查第四步：索引命中问题

自关闭衍生_合并会导致查询视图时出现问题，而系统中有很多视图在使用，因此如果不使用视图，则需要对系统进行较大的更改，所以关闭衍生_合并是不现实的，所以只能再想一想。不管怎样，查询慢的原因主要是索引没有命中。也就是说，解决了索引访问的问题就可以解决查询慢的问题。首先对比两个库的索引访问，发现主要是链表查询。当post-ON条件没有命中新数据库中的索引时，post-ON条件在主表上与其他列组合形成公共索引，部分链接表与其他列组合形成公共索引。有些与其他列组合形成公共索引。表列没有索引，因此我尝试为链接表中ON之后的字段创建单独的索引。创建后，速度明显提升了一倍，但是还是缺少一些索引，所以我在主表中添加了ON。分别为以下字段创建索引（如果ON后有多个条件，则创建公共索引）。构建完成后，运行命令并在几秒钟内进行查询。问题解决了

尝试在旧数据库上优化索引

感谢新数据库 创建索引后，速度比旧数据库快很多。当然和配置本身也有关系。所以我想在旧数据库上创建相同的索引是否会更快，所以我在旧数据库上创建了一个新数据库。对于同一个索引，SQL语句的执行速度比创建索引前慢一倍。检查索引访问。虽然更多的索引受到影响，但这也会导致匹配索引中的行数增加。

灵感

MySQL 不同版本有不同的SQL优化器，不同版本可能有不同的索引访问规则。另外，索引越多，查询也不会越快。不明智的索引创建不仅会导致插入慢还会导致查询变慢，所以有必要了解MySQL索引命中规则并了解所使用的MySQL SQL优化器并且不要轻易更新版本，天知道会发生什么多么令人费解的问题啊。 。 。 。