MySQL 之慢查詢優化及慢日志管理-有解無憂

1、查詢優化神器 —— explain

? 使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢陳述句，從而知道MySQL是如何處理SQL陳述句的，分析查詢陳述句或是表結構的性能瓶頸，

(1)、通過EXPLAIN，可以分析出以下結果：

表的讀取順序
資料讀取操作的操作型別
哪些索引可以使用
哪些索引被實際使用
表之間的參考
每張表有多少行被優化器查詢

(2)、使用方式：

EXPLAIN + SQL陳述句

(3)、執行計劃包含的資訊

+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+

(4)、執行計劃各欄位的含義

<1>、id： select查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序

id相同，執行順序由上至下
id不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行
id相同不同，同時存在，id如果相同，可以認為是一組，從上往下執行，

<2>、select_type：分別用來表示查詢的型別，主要是用于區別普通查詢、聯合查詢、子查詢等的復雜查詢，

SIMPLE ：簡單的select查詢，查詢中不包含子查詢或者UNION
PRIMARY ：查詢中若包含任何復雜的子部分，最外層查詢則被標記為PRIMARY
SUBQUERY ：在SELECT或WHERE串列中包含了子查詢
DERIVED ：在FROM串列中包含的子查詢被標記為DERIVED（衍生），MySQL會遞回執行這些子查詢，把結果放在臨時表中
UNION ：若第二個SELECT出現在UNION之后，則被標記為UNION；若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為：DERIVED
UNION RESULT ：從UNION表獲取結果的SELECT

<3>、table：指的就是當前執行的表

<4>、type：顯示的是查詢使用了哪種型別

system：表只有一行記錄（等于系統表），這是const型別的特列，平時不會出現，這個也可以忽略不計
const：表示通過索引一次就找到了，const用于比較primary key 或者unique索引，因為只匹配一行資料，所以很快，如將主鍵置于where串列中，MySQL就能將該查詢轉換為一個常量，
首先進行子查詢得到一個結果的d1臨時表，子查詢條件為id = 1 是常量，所以type是const，id為1的相當于只查詢一條記錄，所以type為system，
eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配，常見于主鍵或唯一索引掃描，
ref：非唯一性索引掃描，回傳匹配某個單獨值的所有行，本質上也是一種索引訪問，它回傳所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以他應該屬于查找和掃描的混合體，
range：只檢索給定范圍的行，使用一個索引來選擇行，key列顯示使用了哪個索引，一般就是在你的where陳述句中出現between、< 、>、in等的查詢，這種范圍掃描索引比全表掃描要好，因為它只需要開始于索引的某一點，而結束于另一點，不用掃描全部索引，
index：Full Index Scan，Index與All區別為index型別只遍歷索引樹，這通常比ALL快，因為索引檔案通常比資料檔案小，（也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬碟讀取的）
id是主鍵，所以存在主鍵索引
all：Full Table Scan 將遍歷全表以找到匹配的行

從最好到最差依次是：system > const > eq_ref > ref > range > index > all，一般來說，得保證查詢至少達到range級別，最好能達到ref，

<5>、possible_keys：顯示可能應用在這張表中的索引，一個或多個，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢實際使用，

<6>、key：實際使用的索引，如果為NULL，則沒有使用索引，（可能原因包括沒有建立索引或索引失效），查詢中若使用了覆寫索引（select 后要查詢的欄位剛好和創建的索引欄位完全相同），則該索引僅出現在key串列中，

<7>、key_len：表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度，在不損失精確性的情況下，長度越短越好，key_len顯示的值為索引欄位的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的，

<8>、ref：顯示索引的那一列被使用了，如果可能的話，最好是一個常數，哪些列或常量被用于查找索引列上的值，

<9>、rows：根據表統計資訊及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數，也就是說，用的越少越好，

<10>、包含不適合在其他列中顯式但十分重要的額外資訊，

? 關于 explain 命令的具體用法和欄位含義可以參考官網explain-output，

需要強調 rows 是核心指標，絕大部分 rows 小的陳述句執行一定很快（也有例外），所以優化陳述句基本上都是在優化rows，

執行計劃：讓mysql預估執行操作(一般正確)
    all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
    id,email
    
    慢：
        select * from test where name='cai'
        
        explain select * from test where name='cai'
        type: ALL(全表掃描)
            select * from test limit 1;
    快：
        select * from test where email='cai@'
        type: const(走索引)

2、慢查詢優化的基本步驟

0.先運行看看是否真的很慢，注意設定 SQL_NO_CACHE
1.where條件單表查，鎖定最小回傳記錄表，這句話的意思是把查詢陳述句的where都應用到表中回傳的記錄數最小的表開始查起，單表每個欄位分別查詢，看哪個欄位的區分度最高
2.explain查看執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢）
3.order by limit 形式的sql陳述句讓排序的表優先查
4.了解業務方使用場景
5.加索引時參照建索引的幾大原則
6.觀察結果，不符合預期繼續從0分析

3、慢日志管理

? MySQL的慢查詢日志是MySQL提供的一種日志記錄，它用來記錄在MySQL中回應時間超過閥值的陳述句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中，long_query_time的默認值為10，意思是運行10S以上的陳述句，默認情況下，Mysql資料庫并不啟動慢查詢日志，需要手動來設定這個引數，當然，如果不是調優需要的話，一般不建議啟動該引數，因為開啟慢查詢日志會或多或少帶來一定的性能影響，慢查詢日志支持將日志記錄寫入檔案，也支持將日志記錄寫入資料庫表，

# 慢日志
    - 執行時間 > 10
    - 未命中索引
    - 日志檔案路徑

# 配置：
    - 記憶體
        show variables like '%query%';
        show variables like '%queries%';
        set global 變數名 = 值
    - 組態檔
    	mysqld --defaults-file='E:\cai\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'

# my.conf內容：
    slow_query_log = ON
    slow_query_log_file = D:/....

# 注意：修改組態檔之后，需要重啟服務

4、MySQL日志管理

(1)、MySQL日志

#========================================================
錯誤日志: 記錄 MySQL 服務器啟動、關閉及運行錯誤等資訊
二進制日志: 又稱binlog日志，以二進制檔案的方式記錄資料庫中除 SELECT 以外的操作
查詢日志: 記錄查詢的資訊
慢查詢日志: 記錄執行時間超過指定時間的操作
中繼日志： 備庫將主庫的二進制日志復制到自己的中繼日志中，從而在本地進行重放
通用日志： 審計哪個賬號、在哪個時段、做了哪些事件
事務日志或稱redo日志： 記錄Innodb事務相關的如事務執行時間、檢查點等
#========================================================

(2)、MySQL日志配置

## 一、bin-log
1. 啟用
# vim /etc/my.cnf
[mysqld]
log-bin[=dir\[filename]]
# service mysqld restart
2. 暫停
//僅當前會話
SET SQL_LOG_BIN=0;
SET SQL_LOG_BIN=1;
3. 查看
查看全部：
# mysqlbinlog mysql.000002
按時間：
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"
# mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 

按位元組數：
# mysqlbinlog mysql.000002 --start-position=260
# mysqlbinlog mysql.000002 --stop-position=260
# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930
4. 截斷bin-log（產生新的bin-log檔案）
a. 重啟mysql服務器
b. # mysql -uroot -p123 -e 'flush logs'
5. 洗掉bin-log檔案
# mysql -uroot -p123 -e 'reset master' 


## 二、查詢日志
啟用通用查詢日志
# vim /etc/my.cnf
[mysqld]
log[=dir\[filename]]
# service mysqld restart

## 三、慢查詢日志
啟用慢查詢日志
# vim /etc/my.cnf
[mysqld]
log-slow-queries[=dir\[filename]]
long_query_time=n
# service mysqld restart
MySQL 5.6:
slow-query-log=1
slow-query-log-file=slow.log
long_query_time=3  單位為秒
查看慢查詢日志
測驗:BENCHMARK(count,expr)
SELECT BENCHMARK(50000000,2*3);

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/204539.html

標籤：MySQL

上一篇：請教匯入mysql時報將 expression 轉換為資料型別 nvarchar 時出現算術溢位錯誤的錯誤

下一篇：MySQL 之慢查詢優化及慢日志管理

MySQL 之 慢查詢優化及慢日志管理