MySQL 慢查詢那點事

wacho發表於2020-03-30
MySql

1、簡介

開啟慢查詢日誌，可以讓MySQL記錄下查詢超過指定時間的語句，通過定位分析效能的瓶頸，才能更好的優化資料庫系統的效能。

2、引數介紹

slow_query_log 慢查詢開啟狀態
slow_query_log_file 慢查詢日誌存放的位置（這個目錄需要MySQL的執行帳號的可寫許可權，一般設定為MySQL的資料存放目錄）
long_query_time 查詢超過多少秒才記錄，預設是 10 秒

3、開啟慢查詢

1.檢視慢查詢相關引數

mysql> show variables like 'slow_query%';
+---------------------------+-----------------------------------+
| Variable_name             | Value                              |
+---------------------------+-----------------------------------+
| slow_query_log            | OFF                                |
| slow_query_log_file       | /usr/local/var/mysql/slow.log          |
+---------------------------+-----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

2.設定方法

方法一：全域性變數設定
將 slow_query_log 全域性變數設定為“ON”狀態

mysql> set global slow_query_log='ON';

設定慢查詢日誌存放的位置

mysql> set global slow_query_log_file='/usr/local/var/mysql/slow.log ';

設定慢查詢時間,查詢超過1秒就記錄

mysql> set global long_query_time=1;

方法二：配置檔案設定
修改配置檔案my.cnf，在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/var/mysql/slow.log 
long_query_time = 1

3.重啟MySQL服務

service mysqld restart

4、慢查詢日誌分析

1、擷取一段慢查詢日誌：

# Time: 180918 19:06:21
# User@Host: proxy[proxy] @  [192.168.0.16]  Id: 6707197
# Query_time: 1.015429  Lock_time: 0.000116 Rows_sent: 1  Rows_examined: 44438
SET timestamp=1537268781;
select
        id, user_id, device_uuid, bd_client_id, bd_user_id, bd_tag,
        nodisturb_mode, nodisturb_start_time,
        nodisturb_end_time, binding_time, device_os_type, app_type, state
        from app_mobile_device
        where user_id = '78436'
            and app_type = 'YGY'
        order by binding_time desc;
# User@Host: proxy[proxy] @  [192.168.0.16]  Id: 6707236
# Query_time: 1.021662  Lock_time: 0.000083 Rows_sent: 1  Rows_examined: 44438
SET timestamp=1537268781;
select
        id, user_id, device_uuid, bd_client_id, bd_user_id, bd_tag,
        nodisturb_mode, nodisturb_start_time,
        nodisturb_end_time, binding_time, device_os_type, app_type, state
        from app_mobile_device
        where user_id = '14433'
            and app_type = 'YGY'
        order by binding_time desc;

這裡可以看到：

Query_time (慢查詢語句的查詢時間) 都超過了設定的 1s,

Rows_sent (慢查詢返回記錄) 這裡只返回了 1 條

Rows_examined （慢查詢掃描過的行數） 44438 -> 通過這裡大概可以看出問題很大

2.現在將這個SQL語句放到資料庫去執行，並使用EXPLAIN分析 看下執行計劃

EXPLAIN                                
select                                 
        id, user_id, device_uuid, bd_client_id, bd_user_id, bd_tag,                        
        nodisturb_mode, nodisturb_start_time,                          
        nodisturb_end_time, binding_time, device_os_type, app_type, state                          
        from app_mobile_device                         
        where user_id = '78436'                            
            and app_type = 'YGY'                       
        order by binding_time desc;

查詢結果是：

img

解釋下引數：

SELECT識別符。這是SELECT的查詢序列號
select_type SELECT型別,可以為以下任何一種:SIMPLE:簡單SELECT(不使用UNION或子查詢)PRIMARY:最外面的SELECTUNION:UNION中的第二個或後面的SELECT語句DEPENDENT UNION:UNION中的第二個或後面的SELECT語句,取決於外面的查詢UNION RESULT:UNION 的結果SUBQUERY:子查詢中的第一個SELECTDEPENDENT SUBQUERY:子查詢中的第一個SELECT,取決於外面的查詢DERIVED:匯出表的SELECT(FROM子句的子查詢)
table 輸出的行所引用的表
type 聯接型別。下面給出各種聯接型別,按照從最佳型別到最壞型別進行排序:system:表僅有一行(=系統表)。這是const聯接型別的一個特例。const:表最多有一個匹配行,它將在查詢開始時被讀取。因為僅有一行,在這行的列值可被優化器剩餘部分認為是常數。const表很快,因為它們只讀取一次!eq_ref:對於每個來自於前面的表的行組合,從該表中讀取一行。這可能是最好的聯接型別,除了const型別。ref:對於每個來自於前面的表的行組合,所有有匹配索引值的行將從這張表中讀取。ref_or_null:該聯接型別如同ref,但是新增了MySQL可以專門搜尋包含NULL值的行。index_merge:該聯接型別表示使用了索引合併優化方法。unique_subquery:該型別替換了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個索引查詢函式,可以完全替換子查詢,效率更高。index_subquery:該聯接型別類似於unique_subquery。可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)range:只檢索給定範圍的行,使用一個索引來選擇行。index:該聯接型別與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引檔案通常比資料檔案小。ALL:對於每個來自於先前的表的行組合,進行完整的表掃描。
possible_keys 指出MySQL能使用哪個索引在該表中找到行
key 顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL。
key_len 顯示MySQL決定使用的鍵長度。如果鍵是NULL,則長度為NULL。
ref 顯示使用哪個列或常數與key一起從表中選擇行。
rows 顯示MySQL認為它執行查詢時必須檢查的行數。多行之間的資料相乘可以估算要處理的行數。
filtered 顯示了通過條件過濾出的行數的百分比估計值。
Extra 該列包含MySQL解決查詢的詳細資訊Distinct:MySQL發現第1個匹配行後,停止為當前的行組合搜尋更多的行。Not exists:MySQL能夠對查詢進行LEFT JOIN優化,發現1個匹配LEFT JOIN標準的行後,不再為前面的的行組合在該表內檢查更多的行。range checked for each record (index map: #):MySQL沒有發現好的可以使用的索引,但發現如果來自前面的表的列值已知,可能部分索引可以使用。Using filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行。Using index:從只使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。Using temporary:為了解決查詢,MySQL需要建立一個臨時表來容納結果。Using where:WHERE 子句用於限制哪一個行匹配下一個表或傳送到客戶。Using sort_union(…), Using union(…), Using intersect(…):這些函式說明如何為index_merge聯接型別合併索引掃描。Using index for group-by:類似於訪問表的Using index方式,Using index for group-by表示MySQL發現了一個索引,可以用來查 詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜尋硬碟訪問實際的表。

這裡可以發現：rows 為查詢的行數，查詢了4w多行，那慢是肯定的了。

因為這裡是好幾個條件，並且沒有使用一個索引，那就只能給新增索引了，

這裡給選擇新增普通多列索引，因為這個表在最開始設計出問題了，導致有重複的資料，不能設定唯一索引了。

ALTER  TABLE  app_mobile_device  ADD  INDEX user_app_type_only (  `user_id` ,`app_type` )

索引設定了，再看下剛的SQL的執行計劃。

img

可以發現rows 的檢查行數，很明顯的下降了。

到此，慢查詢的使用和優化就基本完成了。

參考文章：https://www.cnblogs.com/sunxun/p/9673788.h...

​ https://www.cnblogs.com/gxj521test/p/10964...

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章