mysql慢查詢分析
1. 慢查詢配置
開啟慢查詢日志
查詢狀態
show variables like 'slow_query_log'；
設置開啟或關閉
set global slow_query_log = ['ON'/'OFF']；
指定慢查詢日志log文件
set global slow_query_log_file = /var/lib/mysql/slowlog.log
設置超時則寫入到慢查詢日志中，可以有6位小數，到微秒；設置為0，則所有sql記錄到慢查詢日志中
set global long_query_time = xx.xxx秒
所有未使用索引的sql記錄到日志中
set globallog_queries_not _using_indexes= ['ON'/'OFF']
持久化配置：my.cnf中配置慢查詢配置
查詢慢查詢配置：show variables like '%query%';
2. mysqldumpslow命令分析慢查詢
/*傳入慢查詢日志作為參數*/
mysqldumpslow[ OPTS... ][ LOGS...]
mysqldumpslow slowlog.log
3.0 函數及存儲過程耗時分析
mysql分析sql語句耗時情況
mysql -h10.3.40.98 -uroot -p command
mysql> use viewdb;
mysql> set profiling=1;
call func();
show profiles;
4. 使用Percona Toolkit工具分析慢查詢
/* Percona Toolkit工具 源碼下載 */
/* 安裝依賴 */
yum install perl-DBI.x86_64
yum install perl-DBD-MySQL.x86_64
yum install perl-IO-Socket-SSL.noarch
yum install perl-Digest-MD5.x86_64
yum install perl-TermReadKey.x86_64
yum install perl-CPAN
yum install perl-Time-HiRes
/* 下載源碼 */
wget percona.com/get/percona-toolkit.tar.gz
tar zxf percona-toolkit.tar.gz
cd percona-toolkit-3.5.5
perl Makefile.PL PREFIX=/usr/local/percona-toolkit
/* 將容器中的log復制出來 */
docker cp ae:/var/lib/mysql/slowlog.log /home/bk
/* 示例 */
pt-query-digest [OPTIONS][FILES][DSN]
pt-query-digest /home/bk/slowlog.log
5.監控長時間運行的SQL
/* 只能查詢到自己賬戶下的sql */
SELECT id, 'user', 'host', DB, command, 'time', state, info FROM information_schema.PROCESSLIST WHERE TIME>=60;
6.EXPLAIN執行計劃內容分析
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| id | select_type | table | partitions | type | possible keys | key | key_len | ref | rows | filtered | Extra |
6.1 ID
- ID表示查詢執行的順序
- ID相同時由上到下執行
- ID不同時，由大到小執行
6.2 select_type
| 值 | 含義 |
|---|---|
| SIMPLE | 不包含子查詢或是UNION操作的查詢 |
| PRIMARY | 查詢中如果包含任何子查詢，那么最外層的查詢則被標記為PRIMARY |
| SUBQUERY | SELECT列表中的子查詢 |
| DEPENDENT SUBQUERY | 依賴外部結果的子查詢 |
| UNION | union操作的第二個或是之后的查詢的值為union |
| DEPENDENTUNION | 當UNION做為子查詢時，第二或是第二個后的查詢的select_type值 |
| UNION RESULT | UNION產生的結果集 |
| DERIVED | 出現在FROM子句中的子查詢 |
6.3 table
- 指明是從那個表中獲取數據
- "unionM,N"由ID為MN查詢union產生的結果集
- "derived N" 或者 "subquery N" 由ID為N的查詢產生的結果
6.4 partitions
- 對于分區表，顯示查詢的分區ID
- 對于非分區表，顯示NULL
6.5 type
| 性能 | 值 | 含義 |
|---|---|---|
| 高 | system | 這是const聯接類型的一個特例,當查詢的表只有一行時使用 |
| const | 表中有且只有一個匹配的行時使用，如對主鍵或是唯一索引的查詢，這是效率最高的聯接方式 | |
| eq_ref | 唯一索或主鍵引查找，對于每個索引鍵，表中只有一條記錄與之匹配 | |
| ref | 非唯一索引查找，返回匹配某個單獨值的所有行 | |
| ref or_null | 類似于ref類型的查詢，但是附加了對NULL值列的查詢 | |
| index_merge | 該聯接類型表示使用了索引合并優化方法 | |
| range | 引范圍掃描，常見于between、>、<這樣的查詢條件 | |
| index | FULLindex Scan全索掃描，同ALL的區別是，遍歷的是索引樹 | |
| 低 | DERIVED | 出現在FROM子句中的子查詢 |
6.6 possible keys
- 指出查詢中可能會用到的索引
6.7 key
- 指出查詢時實際用到的索引
6.8 key_len
- 實際使用索引的最大長度
6.9 ref
- 指出那些列或常量被用于索引查找
6.10 rows
- 跟據統計信息預估的掃描的行數
6.11 filtered
表示返回結果的行數占需讀取行數的百分比，越高越好
6.12 Extra
| 值 | 含義 |
|---|---|
| Distinct | 優化distinct操作，在找到第一匹配的元組后即停止找同樣值的動作 |
| Not exists | 使用notexists來優化查詢 |
| Using filesort | 使用文件來進行排序，通常會出現在order by 或group by查詢中 |
| Using index | 使用了覆蓋索引進行查詢 |
| Using temporary | MySQL需要使用臨時表來處理查詢，常見于排序，子查詢和分組查詢 |
| Using where | 需要在MySQL服務器層使用WHERE條件來過濾數據 |
| select tables optimizedaway | 直接通過索引來獲得數據，不用訪問表 |
浙公網安備 33010602011771號