内容简介:因为生产环境数据量越来越大,客户越来越多,项目功能也越来越多,项目本身也越来越多,导致之前的服务器内存、硬盘都已经渐渐的不够用了,当时出现了2种解决方案,增加服务器配置和新购服务器,但是就算是新增硬盘,也需要对数据库进行迁移,所以就采用了新购服务器的方案,并且因为之前是高效云盘,出现过IO占满的情况,所以对于新购的服务器采用了SSD硬盘,理论上速度会飞起来了,实际上我在新服务器上安装MySQL5.7,因为听说MySQL5.7性能提升了N倍,还支持json(虽然对我们没什么用),但是毕竟MySQL8已经出来
起因
因为生产环境数据量越来越大,客户越来越多,项目功能也越来越多,项目本身也越来越多,导致之前的服务器内存、硬盘都已经渐渐的不够用了,当时出现了2种解决方案,增加服务器配置和新购服务器,但是就算是新增硬盘,也需要对数据库进行迁移,所以就采用了新购服务器的方案,并且因为之前是高效云盘,出现过IO占满的情况,所以对于新购的服务器采用了SSD硬盘,理论上速度会飞起来了,实际上我在新服务器上安装 MySQL 5.7,因为听说MySQL5.7性能提升了N倍,还支持json(虽然对我们没什么用),但是毕竟MySQL8已经出来了,说明MySQL5.7也肯定稳定好了。so,就找了个夜深人静的晚上偷偷的吧数据库迁移过来了,然后开启慢查询日志,限制为5秒,于是开始各种测试,然后查看了一下慢查询日志,一堆慢查询日志,所以有了这篇文章,为什么会出现这么多慢查询,以及如何解决
开始排查
将慢查询 SQL 拿出来,发现主要的慢查询SQL都是链表查询的语句,也就是说查询语句本身非常复杂,所以就把SQL语句放回之前的数据库执行,发现之前的数据库都是不到1秒就查询出来了,而在新的数据库上最慢能达到140多秒,这明显就不正常了,毕竟新的MySQL服务器无论是CPU、内存、还是硬盘相对于以前的MySQL服务器来说,都是好了不止一星半点,如果说性能差不多还能接受,但是一下子慢了这么多,就明显是有异常了,于是开始挨个排查
排查第一步:配置问题
首先2边服务器的配置文件是一模一样的,因为就是从旧服务器吧配置文件复制过来的,但是因为MySQL的版本不一致,所以怀疑是因为新版有些配置不一样,所以导致的,于是百度了MySQL5.7的优化配置,同时根据机器的内存CPU等硬件情况调整了部分配置,重启MySQL,执行语句,效果不明显,平均下来能快1秒左右,但是这1秒本身也可以认为是查询波动,所以不是配置的问题
排查第二步:硬件问题
首先CPU和内存应该不会有什么问题,唯一可能性就是SSD硬盘的问题,因为之前看到过因为SSD硬盘导致K,V键值存储性能低下,跟机械硬盘完全不在同一个等级上,所以怀疑SSD是否需要开启什么特别的配置才行,于是百度,发现虽然有针对SSD的优化配置,但是也没有因为用了SSD导致速度非常慢的情况,于是针对SSD进行了优化配置,重启MySQL,执行语句,效果微乎其微
排查第三步:语句问题
也是实在没办法了,才想到这个问题,但是我自己都觉得不大可能,而且语句本身也优化的差不多了,小结果集驱动大结果集,索引根据where条件创建等。毕竟就算MySQL升级也不会说改变SQL语法之类的,最多就是在优化SQL的进行了一些特殊处理,所以先查看一下SQL语句的索引执行情况于是desc sql语句查看,跟旧库上面差别
旧库(MySQL5.6)
新库(MySQL5.7) 在2个库数据,索引相同的情况下,居然会出现索引引用和命中不一样的情况,所以怀疑是否是迁移数据库的时候导致索引数据被破坏,于是百度去找,还真的发现了一个例子,也是迁移数据库后查询非常慢,后面重建索引之后恢复了,于是准备重建索引,由于表非常多,所以写了一个 工具 类来重建索引(唯一索引和普通索引,不包含主键索引),核心代码如下:
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有没有更新什么新特性,看到了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优化器是有必要的,以及不要轻易更新版本,天知道会出现什么莫名其妙的问题。。。。。
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- webpack 4 升级迁移
- Gradle Plugin 升级迁移及构建优化
- iOS CoreData (二) 版本升级和数据库迁移
- 谷歌推出最新AngularJS升级工具,可快速迁移至Angular
- 银行核心海量数据无损迁移:TDSQL数据库多源异构迁移方案
- 再无需从头训练迁移学习模型!亚马逊开源迁移学习数据库 Xfer
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Dreamweaver基础教程
李振华、季小武、季小武、李振华 / 清华大学 / 2005-6 / 23.00元
本书通过实例的方式介绍了Macromedia公司的Dreamweaver MX 2004的使用方法和技巧。 全书由14章组成,第1章和第2章介绍了软件的应用领域、知识结构、界面组成等;第3章到第12章是本书的重点部分,通过实例制作介绍了站点的建立,表格、文本及样式的创建,链接、图像、行为的使用,层、表单、框架的创建和使用以及动画、多媒体的制作等主要知识点;第13章和第14章介绍了插件技......一起来看看 《Dreamweaver基础教程》 这本书的介绍吧!