程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL的一條慢SQL查詢導致整個網站宕機的解決方法,mysql整個網站

MySQL的一條慢SQL查詢導致整個網站宕機的解決方法,mysql整個網站

編輯:MySQL綜合教程

MySQL的一條慢SQL查詢導致整個網站宕機的解決方法,mysql整個網站


直接切入正題吧:

通常來說,我們看到的慢查詢一般還不致於導致掛站,頂多就是應用響應變慢
不過這個恰好今天被我撞見了,一個慢查詢把整個網站搞掛了
先看看這個SQL張撒樣子:

# Query_time: 70.472013 Lock_time: 0.000078 Rows_sent: 7915203 Rows_examined: 15984089 Rows_affected: 0
# Bytes_sent: 1258414478
use js_sku;
SET timestamp=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
FROM js_sgoods_sku
WHERE ss_si_id = 0 AND ss_status > 0
ORDER BY
ss_orderid DESC, ss_av_fid ASC;
這裡貼出來的就是 mysql slow log 的信息,查詢時間用了高達 70s!!
看到慢查詢我們一般第一反應是這個 語句沒有用到索引? 或者是索引不合理麼? 那我們會去看看執行計劃:

mysql> explain 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
-> FROM js_sgoods_sku
-> WHERE ss_si_id = 0 AND ss_status > 0
-> ORDER BY
-> ss_orderid DESC, ss_av_fid ASC;
+----+-------------+---------------+------+---------------+----------+---------+-------+---------+-----------------------------+
| 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 | Using where; Using filesort |
+----+-------------+---------------+------+---------------+----------+---------+-------+---------+-----------------------------+
1 row in set (0.00 sec)

這個看起來似乎用到了索引,可是為什麼掃描到行還是這麼多呢? 那我們就去看看表結構了,期望能從中找到點有價值的東西:
我們看到如下可用信息:
KEY `ss_si_id` (`ss_si_id`,`ss_av_zid`,`ss_av_fid`) USING BTREE,
`ss_si_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '對應js_sgoods_info.si_id',

我們看到 索引似乎還能比較能夠接受,但是我們看到 這個 ss_si_id 這個字段實際上是 goods_info 表的主鍵,也就是說它的離散程度應該是很大的,也就是區分度很大。
其實到這一步我們基本上可以認為 是由於我們這個表裡邊有很多 ss_si_id=0 導致,不過我們可以進一步的來證實我們的猜想:

1. 首先我們可以先確定我們的統計信息沒有問題
2. 其次我們再count ss_si_id=0 的這個值有多少數據,來進一步驗證我們的猜想。

那麼我們先查看以下這個索引的統計信息:
xiean@localhost:js_sku 03:27:42>show index from js_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_sku 03:27:47>analyze table js_sgoods_sku;
+----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| js_sku.js_sgoods_sku | analyze | status | OK |
+----------------------+---------+----------+----------+

but ,我們再次查看 這些索引的統計信息:
xiean@localhost:js_sku 03:28:14>show index from js_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. jstatck pid 無法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 條件中添加了一個條件: AND ss_si_id <> 0 ,在MySQL之行計劃層屏蔽掉此SQL;
2.DBA 開啟kill 掉這個查詢語句,避免DB服務器出現down機的情況,當然這個就用到了我們的 pt-kill 工具,不得不說這個工具相當好用


總結(經驗與教訓):
1.類似這種查詢 default 值的 SQL ,我們應該從源頭上杜絕這類查詢
2.限制查詢結果集大小,避免因查詢結果集太大導致服務死掉

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved