MySQL的一条慢SQL查询导致整个网站宕机的解决方法
直接切入正题吧:
通常来说,我们看到的慢查询一般还不致于导致挂站,顶多就是应用响应变慢
不过这个恰好今天被我撞见了,一个慢查询把整个网站搞挂了
先看看这个SQL张撒样子:
#Query_time: 70.472013 Lock_time:0.000078Rows_sent:7915203Rows_examined:15984089Rows_affected:0
#Bytes_sent:1258414478
usejs_sku;
SETtimestamp=1465850117;
SELECT
ss_id,ss_sa_id,ss_si_id,ss_av_zid,ss_av_fid,ss_artno,
ss_av_zvalue,ss_av_fvalue,ss_av_zpic,ss_av_fpic,ss_number,
ss_sales,ss_cprice,ss_price,ss_stock,ss_orderid,ss_status,
ss_add_time,ss_lastmodify
FROMjs_sgoods_sku
WHEREss_si_id=0ANDss_status>0
ORDERBY
ss_orderidDESC,ss_av_fidASC;
这里贴出来的就是mysqlslowlog的信息,查询时间用了高达70s!!
看到慢查询我们一般第一反应是这个语句没有用到索引?或者是索引不合理么?那我们会去看看执行计划:
mysql>explainSELECT
->ss_id,ss_sa_id,ss_si_id,ss_av_zid,ss_av_fid,ss_artno,
->ss_av_zvalue,ss_av_fvalue,ss_av_zpic,ss_av_fpic,ss_number,
->ss_sales,ss_cprice,ss_price,ss_stock,ss_orderid,ss_status,
->ss_add_time,ss_lastmodify
->FROMjs_sgoods_sku
->WHEREss_si_id=0ANDss_status>0
->ORDERBY
->ss_orderidDESC,ss_av_fidASC;
+----+-------------+---------------+------+---------------+----------+---------+-------+---------+-----------------------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+---------------+------+---------------+----------+---------+-------+---------+-----------------------------+
|1|SIMPLE|js_sgoods_sku|ref|ss_si_id|ss_si_id|4| const | 9516091 |Usingwhere;Usingfilesort|
+----+-------------+---------------+------+---------------+----------+---------+-------+---------+-----------------------------+
1rowinset(0.00sec)
这个看起来似乎用到了索引,可是为什么扫描到行还是这么多呢?那我们就去看看表结构了,期望能从中找到点有价值的东西:
我们看到如下可用信息:
KEY`ss_si_id`(`ss_si_id`,`ss_av_zid`,`ss_av_fid`)USINGBTREE,
`ss_si_id`int(11)unsignedNOTNULLDEFAULT'0'COMMENT'对应js_sgoods_info.si_id',
我们看到索引似乎还能比较能够接受,但是我们看到这个ss_si_id这个字段实际上是goods_info表的主键,也就是说它的离散程度应该是很大的,也就是区分度很大。
其实到这一步我们基本上可以认为是由于我们这个表里边有很多ss_si_id=0导致,不过我们可以进一步的来证实我们的猜想:
1.首先我们可以先确定我们的统计信息没有问题
2.其次我们再countss_si_id=0的这个值有多少数据,来进一步验证我们的猜想。
那么我们先查看以下这个索引的统计信息:
xiean@localhost:js_sku03:27:42>showindexfromjs_sgoods_sku;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|js_sgoods_sku|0|PRIMARY|1|ss_id |A|18115773|NULL|NULL||BTREE|||
|js_sgoods_sku|1|ss_si_id|1|ss_si_id |A |1811577 |NULL|NULL||BTREE|||
|js_sgoods_sku|1|ss_si_id|2|ss_av_zid|A|6038591 |NULL|NULL||BTREE|||
|js_sgoods_sku|1|ss_si_id|3|ss_av_fid|A|18115773|NULL|NULL||BTREE|||
|js_sgoods_sku|1|IDX_001|1|ss_sa_id|A|3623154 |NULL|NULL||BTREE|||
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
那么可以看到以下问题:
我们的ss_si_id这个字段并没有我们表面上看到的因为关联了某个表的主键,它的Cardinality值就应该接近于PRIMARY的值。而是差别比较大的,难道是索引的统计信息不准确?那我们尝试重新收集下索引的统计信息:
xiean@localhost:js_sku03:27:47>analyzetablejs_sgoods_sku;
+----------------------+---------+----------+----------+
|Table|Op|Msg_type|Msg_text|
+----------------------+---------+----------+----------+
|js_sku.js_sgoods_sku|analyze|status|OK|
+----------------------+---------+----------+----------+
but,我们再次查看这些索引的统计信息:
xiean@localhost:js_sku03:28:14>showindexfromjs_sgoods_sku;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|js_sgoods_sku|0|PRIMARY|1|ss_id |A|18621349|NULL|NULL||BTREE|||
|js_sgoods_sku|1|ss_si_id|1|ss_si_id |A|1551779 |NULL|NULL||BTREE|||
|js_sgoods_sku|1|ss_si_id|2|ss_av_zid|A|6207116 |NULL|NULL||BTREE|||
|js_sgoods_sku|1|ss_si_id|3|ss_av_fid|A|18621349|NULL|NULL||BTREE|||
|js_sgoods_sku|1|IDX_001|1|ss_sa_id|A|3724269 |NULL|NULL||BTREE|||
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
我们可以看到ss_si_id的离散程度(Cardinality)没有增加反而有向下波动的趋势,因为这个信息是采集部分页的来的,而每个页上边数据分布是不一样的,导致我们这个索引收集的统计信息就回有所变化。
好吧,到这里我们可以认为我们的统计信息没有失效,那么我们就看数据的分别情况咯:
+--------------++----------++------------------+
|ss_si_id=0;||count(*)||7994788/19048617|
+--------------++----------++------------------+
|7994788 ||19048617|| 0.4197 |
+--------------++----------++------------------+
额,不看不知道,一看吓一跳:我们这个表里边存在有大量的ss_si_id=0的情况,占了整个表数据量的41%!!!
好吧问题找到了,那么接下来我们需要知道,为什么这个SQL语句会导致挂站呢?
我们通过观看应用程序服务器的监控看到一些信息:我们的goods_service这个服务异常:异常情况如下:
1.cpu长期占用100%+
2.jstatckpid无法dump内存堆栈信息,必须强制dump-F
3.dump出来的内存信息发现,这个进程里边所有线程均处于BLOCKED状态
4.通过jstat-gcutil看到FGC相当频繁,10s左右就FGC一次
5.内存占用超过了分配的内存
那么最终的原因就是因为上边的慢查询查询了大量数据(最多有700w行数据),导致goods_service内存暴涨,出现服务无法响应,进一步的恶化就是挂占
OK,知道了为什么会挂占,那么我们是如何解决这个问题的呢?
既然我们知道是由于查询了ss_si_id=0导致的,那么我们屏蔽掉这个SQL不就好了么。屏蔽的办法可以有多种:
1.我们程序逻辑判断一下这类型的查询如果有查询ss_si_id=0的一律封杀掉
2.我们改改SQL配置文件,修改SQL语句
我们发现DB服务器上存在大量的这个慢查询,而且DB服务器负载已经从0.xx飙升到了50+了,随之而来的连接数也飙升的厉害,如果再不及时处理,估计DB服务器也挂掉了
那么我们最终采取以下处理办法:
1.运维配合研发修改SQL语句我们在这个WHERE条件中添加了一个条件:ANDss_si_id<>0,在MySQL之行计划层屏蔽掉此SQL;
2.DBA开启kill掉这个查询语句,避免DB服务器出现down机的情况,当然这个就用到了我们的pt-kill工具,不得不说这个工具相当好用
总结(经验与教训):
1.类似这种查询default值的SQL,我们应该从源头上杜绝这类查询
2.限制查询结果集大小,避免因查询结果集太大导致服务死掉