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 查看库，表数据索引大小