mysql优化记录

起因:

上周三晚上,审帖人员说系统很卡。然后我就像往常一样,登服务器查看。查了一圈发现系统负载不高,io也没问题。只查到了有人在当时的时间点做了一些别的操作。当时断定是其他人操作影响的。之后发现一个接口卡了很久。我就猜测是这个sql语句卡了。但是测了一下发现也不慢,就有点不知所措。然后开始找这个语句的慢查询,发现也就两秒多。第二天,和老大讨论,我说可能是nginx卡住了。他说nginx一般不会卡住,可能是别的卡住了。然后我们就登上管理后台看。发现主数据库对应的服务器前一天晚上负载很高。怀疑是数据库有慢查询导致系统卡住。我当时找了一下找到了一个很慢的sql, 但老大说这个sql已经优化了很久,没法优化了。因为只出现了一次,就暂时不考虑解决。

问题严重

这周一的时候,这个问题又出现了。这回知道是数据库了。我把线上把一台服务器的读切到了从数据库对应的服务器,来减缓压力。这样暂时解决了。
第二天没管它,想着先看看这样是不是就好了。然而,两个数据库都卡了,当时很是慌张。。。

下决心找到解决方法:

周三到公司,决定花时间把它搞定,查了表数据和索引大小,以及表压缩节省空间的方式。然后和老大一起看这个问题,发现了一个特别慢的sql语句,它能卡到1000s以上。之前没注意看到的。就是类似下面这样的。

SELECT user_id, data, pre_id, trade_key, issue FROM trade WHERE trade=2019130 AND lottery_key=‘three’ AND trade_id IN (1868,1949,1526,1944,343,811,2177,16365458,718,714,712,421,1499,2991,1567,2316,1060,23302,950,952,42,43,41,1110,1298,1769,1443,1116,1292,1561,1291,2182,1295,2648,2649,2721,2646,484,483,1803,1802,972188,2004,1361,1368);

这个sql做了一下改动。后面的数字还很长,我删除了一部分。
发现索引只用了一个user_id, 然后就加了一个联合索引。这里当时只加了一个index, 但是担心锁表,提前做好了脚本,今天晚上2点多上的。
早上来了才发现,mysql5.6加索引不会出现锁表的情况。。5.6支持在线ddl
上线之后,又执行了上面的sql语句,发现还是不行。很慢。然后就各种找原因。最后发现测试服数据库是mysql5.7, 而线上数据库是mysql5.6, 不知道接下来怎么弄。 老大看这个的时候,发现测试服的mysql会有warning(版本高才会有), 然后show warnings之后,看到了trade是数字,而这个字段本来是varchar类型的。改完之后瞬间就查完了。而且还发现了一个MRR的优化项。而线上也没有开这个优化项。

解决

后来改成字符串,问题得以解决。即

select user_id, data, pre_id, trade_key,issue from trad where trade=‘2019130’ and lotteryKey=“three” …;

为啥会出现这个问题,因为这些代码都是sql拼接的!而不是参数化查询。而参数化的好处,还可以防止sql注入。
为啥之前没出现?因为之前数据量不够大,数据量的变化会影响使用索引的。

然后顺带把之前可能会有的类似的问题代码都给过了一遍,又查出来一个。今天晚上应该就没问题了。

思考:

为啥最开始没思考到时连接的数据库的问题?因为经验不足吗?

我觉得有点管窥心理,就只盯着当前业务了,只要有点关联就朝上靠。

遇到warning为啥没有去看?我当时也发现explain里面的rows线上线下不太一样,为什么没有注意到呢?

感觉主要原因可能还是知识不到位,后续需要深入学习sql优化问题。避免向救火队员那样,容易管窥,容易犯错。下次再优化sql会考虑用mysql5.7先看看有没有mysql给的建议。

还有一个问题,查慢查询的时候,先过滤的时间,然后一条一条的根据时间去看。那我能不能把所有的慢查询都给一次查出来?是否需要把后续用到的脚本放到gitlab的一个地方进行归类,方便他人使用及保存代码。甚至是后续搭建一个系统监控的后台。不能仅仅是出问题了才上线查看,而要防患于未然,有问题提前解决。
加油!

整理相关的知识点

show processlist
MRR
mysql全量,增量,差异备份
mysql优化
mysql explain
mysql 备份脚本
mysql 查看库,表数据索引大小

发布了199 篇原创文章 · 获赞 62 · 访问量 21万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览