本文转载自微信公众号《码农的私房话》，作者GoQeng。转载本文请联系码农包间公众号。一般情况下，慢SQL查询一般只会导致应用服务响应慢，但是去年双十一的时候，遇到了慢查询让整个网站崩溃的问题。查询请求过程慢的场景是在去年双十一的晚上。突然接到产品经理的“心灵呼唤”，告诉我整个网站的页面都被菊花了，数据无法展示。我吓得赶紧拿出电脑。登录服务器后，发现业务内存使用率接近100%，CPU负载长期居高不下。我赶紧排查代码是否存在死循环、大对象内存泄漏等问题，但经过排查，发现代码是正常的。然后使用jstatckpid命令dump线程堆栈信息，发现很多业务线程处于BLOCKED状态，同时使用jstat-gcutil观察到FULLGC相当频繁。通过分析jstack线程栈的dump文件内容和代码，发现线程无法获取数据库连接，大量处于等待状态。然后使用showprocesslist命令发现某条SQL查询耗时10s以上，查询的表数据量在1500W左右。迫不及待地分析慢SQLselect*fromt_orderwheremerchant_id=1349865679limit010;表面上好像用了索引，但是为什么还是扫描了那么多行呢？我会看表结构，希望能找到一些有价值的东西，通过showindexfromt_order；发现了以下有用的信息：从上面的结果我们可以看出，merchant_id索引的离散程度还是挺大的，其Cardinality值接近PRIMARY的值，说明比较正常。既然merchant_id索引没有问题，估计是用错了姿势。然后我通过explainselect*fromt_orderWHEREmerchant_id=1349865679limit0,10;分析了正在运行的SQL；发现索引没有生效。最后耐心的把代码和SQL对比了一下，发现SQL中的merchant_id是一个整数，而数据库其实是一个字符串，于是我把字段值改成字符串，执行explainselect*fromt_orderWHEREmerchant_id='1349865679'再次限制为0,10；，发现索引居然生效了。这时候我就确信字段类型转换导致的查询慢，MySQL不会自动帮我们转换字段值类型。定位到原因后，剩下的就是将字段值改为字符串。慢查询会导致系统崩溃？1、TCP连接和端口耗尽，无法响应请求。首先我们来看请求流程：从上图我们可以发现，用户的请求等待几秒后，服务器才向数据库发起执行命令。当时双十一活动很火爆，请求量大，导致数据库连接耗尽，服务器上有大量请求被阻塞。同时，系统不断地创建TCP连接，直到整个请求结束，这些连接才被释放和销毁。因此，当大量的请求堆积起来，不能及时处理时，服务就无法响应，进一步恶化为资源占用。2.堆内存中无法回收对象，导致内存不足。相信大家都知道，每个用户在发送请求和执行逻辑时，都需要分配JVM内存。当上一个请求线程阻塞时，越来越多的新请求继续申请内存分配，但是旧请求中的对象无法回收，内存被释放，最终导致内存爆炸，系统响应缓存，进一步演化系统崩溃。整个请求过程如下：为什么在请求的时候线程申请资源过程中会出现慢查询？说到查询慢，SQL写问题是最常见的因素，但实际上导致SQL慢的因素有很多，包括硬件和MySQL本身的bug等。下面几种情况可能会导致SQL慢：SQL写问题锁竞争激烈，业务实例相互干扰，争抢IO/CPU资源，服务器硬件配置MySQLBug，该问题是SQL写法引起的。根本原因是索引使用不当，查询时进行了全表扫描。如何优化SQL写的慢查询对于SQL写造成的慢查询，正确使用索引可以加快查询速度，避免全表扫描。SQL全表扫描数据流但是在写SQL的时候需要注意一些与索引相关的规则：字段类型转换导致索引失效，比如不带引号的string类型，带引号的number类型等，可能使索引失效，引起全表扫描；MySQL不支持函数转换，所以不能在索引字段中加入函数，否则索引将无法使用；不对索引字段进行计算，对于需要计算的字段，可以考虑将计算方法放在“=”后面；like对于模糊查询，一般禁止使用%前导，防止索引失效，比如like%liew;如果不使用select*，按需加载必填字段，查询无用列会增加数据传输和解析绑定时的网络IO和CPU请尽量使用升序排序，因为文件排序操作比较多倒序，执行效率变低，MySQL8开始支持降序索引解决排序性能问题；尽量使用union代替or，使用or可能会导致放弃使用索引而使用全表扫描；最左匹配原则，索引是有序的，在查询条件中缺少索引列之后，其他条件将不会使用索引，比如(a,b,c)索引，只能使用b,c索引，不能使用指数；尝试在orderby/groupby子查询字段中建立索引，以减少文件排序；除了以上索引使用规则外，在编写SQL时还需要特别注意以下几点：尽量避免大事务的SQL，大事务SQL会影响数据库的并发性能和主从同步；删除表中的所有记录，请使用truncate而不是delete，因为执行truncate时不会产生UNDO信息；InnoDB引擎请谨慎使用selectcount(*)语句，Statistics可能会扫描整张表的数据，MyISAM内置计数器可以直接获取总数；谨慎使用orderbyrand()，因为rand()会在orderby子句中执行多次，效率很低；NegativeQueries一般不使用索引，如!=、<>、notin、notlike等；删除不再使用或很少使用的索引，从而减少索引对更新操作的影响；避免和发现慢查询的措施针对的是SQL编写导致的慢查询，正确使用索引可以加快查询速度，避免全表扫描。在工作中，每个公司使用的MySQL版本可能差别很大，总会出现一些莫名其妙、不确定的问题。因此，为了验证索引的有效性，建议通过explain命令查看主SQL的执行计划。是否使用索引。explainselect*fromt_orderWHEREmerchant_id='1349865679'limit0,10;但是explain工具分析的结果只是MySQL评估反馈的执行计划，最终还是要看MySQL执行引擎会按照一定的算法执行：SQL执行流程，所以有可能是explain分析表明索引是生效的，但是实际执行的SQL语句是全表扫描。这时候就需要开启MySQL的慢查询功能，然后使用监控工具Zabbix或者Grafana辅助及时发现SQL慢查询、连接过多等问题并报警。写在最后的慢查询破坏性很大，从系统响应慢到系统响应慢，再到系统瘫痪不能用。因此，在日常开发中，我们需要合理设计和使用索引，避免慢查询。同时，我们需要借助工具实时监控数据库连接数和慢查询语句，并建立告警机制，以便及时主动发现和定位问题。尽可能减少对客户的损失。
