由一条超级慢的全表查询sql导致MySQL服务故障引发的深思

1、起因

某晚上忽然发现应用报错：Could not open JDBC Connection for transaction，获取不到数据库连接了。数据库配置的最大连接数是2000，正常情况下，是不会出现这种问题的。那么，是什么原因导致这种现象出现的呢？

2、分析解决过程

于是，使用show full processlist查看数据库连接占用情况，发现连接数已经占用满了。而且连接占用的时间都比较长，就算是一个简单的sql也等待了很长时间还是没有执行完。所以导致无法获取连接的情况。

继续分析这些连接，发现其中有一个连接已经占用了18321秒（约5.1个小时）还没有释放。这个连接执行的是一个查询语句：select * from t_books where book_name = 'xxx'  limit 0,1000。一个查询语句，执行了这么长时间还没有有结束，肯定是有问题的。

3、解决

果断使用：KILL thread_id把这条占用了5个多小时的连接强行kill掉。过几分钟，再使用show full processlist查看连接数，发现连接数已经降下来了，小于100。至此问题已经解决，mysql服务已经恢复正常。

4、深思：为什么一条超级慢的查询sql，竟然会会导致mysql服务连接数占满了呢？

继续分析这条sql：select * from t_books where book_name = 'xxx'  limit 0,1000。这条sql并不是程序中执行的，而是开发在客户端执行的。

1、首先分析查询慢的原因：查看下t_books表，表有1亿+数据，并且book_name没有建索引，导致全表查询。无索引全表查询1亿+的数据，查询速度慢是在意料之中。

2、但是这条慢查询sql导致数据库连接占满的真正原因到底是什么呢？

大家都知道，mysql的数据实际是以文件的形式存储在磁盘上的，查询的时候会从磁盘将数据读取到内存，然后再进行处理，此时会产生IO。当然，mysql的innodb存储引擎在设计的时候已经想到了IO会影响数据库的性能，于是设计了缓冲池。当mysql进行查询的时候，首先将从磁盘读到的页存放在缓冲池中，这个过程称为将页“FIX”在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。问题的关键在于：缓冲池的大小直接影响着数据库的整体性能，全表查询1亿+的数据时，缓冲池的作用就微乎其微了，还是会发生大量的IO。于是分析可能是磁盘IO过高，导致数据库服务变慢。

3、接下来查看磁盘io情况：iostat -dxk 1 5，我的mysql的磁盘分区时vdc，所以看vdc的IO情况即可。（如果不知道自己mysql挂载在哪个磁盘的话，查看这篇文章https://blog.csdn.net/cold_Blooder/article/details/110494567）

没有执行select * from t_books where book_name = 'xxx'  limit 0,1000这条sql时的磁盘使用情况，如图：

执行select * from t_books where book_name = 'xxx'  limit 0,1000这条sql时的磁盘使用情况，如图：

然后，再将这条sql的连接kill掉后磁盘使用情况，如图：

通过对比可以发现，这条慢查询sql导致数据库连接占满的真正原因是因为：查询的数据量太大，查询过程中磁盘IO暴增，导致后续的连接的执行时间变长，无法及时释放连接，最终将连接占满了。

5、总结

mysql的常见瓶颈有：

①、CPU：CPU饱和的时候一般发生在数据装入内存或从磁盘上读数据的时候或执行复杂的SQL查询时；

②、IO：磁盘IO瓶颈发生在查询的数据量远大于内存时；（上述案例就是数据IO瓶颈）

③、服务器硬件的性能瓶颈，如：硬盘，影响读、写速度

通过解决这个故障，可以得到以后遇到这种问题的解决步骤：

①、使用show full processlist查看数据库连接占用情况；

②、对连接时长较长的连接进行分析，并kill掉该连接；

③、查看服务器监控和 MySQL 监控，分析服务器（CPU、内存、磁盘IO）以及 MySQL性能，有时候MySQL性能下降不一定是sql语句烂导致的，有时候也跟服务器性能有关。

④、如果是慢查询sql导致，看是否是程序中的sql还是开发在客户端执行的sql。如果是程序中的sql，需要进行相应的优化，如：索引优化。

6、参考文章

查看数据库最大连接数已经连接池使用情况，查看此文章：https://mp.csdn.net/editor/html/110456275

查mysql挂载磁盘，查看此文章：https://blog.csdn.net/cold_Blooder/article/details/110494567

查执行时间长的sql，并强行kill掉该sql占用的连接：https://mp.csdn.net/editor/html/110454070