MySQL問題排查工具介紹

本總結來自美團內部分享，屏蔽了內部數據與工具

知識準備

索引

• 索引是存儲引擎用於快速找到記錄的一種數據結構
• B-Tree，適用於全鍵值，鍵值範圍或鍵最左首碼：(A,B,C): A, AB, ABC,B,C,BC
• 哪些列建議創建索引：WHERE, JOIN , GROUP BY, ORDER BY等語句使用的列
• 如何選擇索引列的順序：
  1. 經常被使用到的列優先
  2. 選擇性高的列優先：選擇性=distinct(col)/count(col)
  3. 寬度小的列優先：寬度 = 列的數據類型

慢查詢

原因

1. 未使用索引
2. 索引不優
3. 伺服器配置不佳
4. 死鎖
5. ...

命令

看版本

mysql -V 客戶端版本 select version 伺服器版本

explain 執行計劃，慢查詢分析神器

• type
  • const,system: 最多匹配一個行，使用主鍵或者unique進行索引
  • eq_ref: 返回一行數據，通常在聯接時出現，使用主鍵或者unique索引（內表索引連接類型）
  • ref: 使用key的最左首碼，且key不是主鍵或unique鍵
  • range: 索引範圍掃描，對索引的掃面開始於某一點，返回匹配的行
  • index:以索引的順序進行全表掃描，優點是不用排序，缺點是還要全表掃描
  • all: 全表掃描 no no no
• extra
  • using index : 索引覆蓋，只用到索引，可以避免訪問表
  • using where: 在存儲引擎檢索行後再做過濾
  • using temporary:使用臨時表，通常在使用GROUP BY，ORDER BY 時出現（嚴禁）
  • using filesort: 到非索引順序的額外排序，當order by col未使到索引時發生（嚴禁）
• possible_keys: 顯示查詢可能使用的索引
• key:優化器決定採用哪個索引來優化對該表的訪問
• rows:MySQL估算的為了找到所需行要檢索的數，優化選擇key的參考 （不是結果集的行數）
• key_len: 使用的索引左首碼的長度(位元組數)，亦可理解為使用了索引中哪些欄位
  • 定長欄位，int占4個位元組、date占3個位元組、timestamp占4個位元組，char(n)占n個位元組
  • NULL的欄位:需要加1個位元組，因此建議盡亮設計為NOT NULL
  • 變長欄位varchar(n)，則需要 (n * 編碼字元所占位元組數 + 2 、)個位元組，如utf8編碼的， 個字元
    占 3個位元組，則 度為 n * 3 + 2
• 強制使用索引: USE INDEX （建議）或 FORCE_INDEX （強制）

show 命令

• show status
  • 查看select語句的執行數 show global status like 'Com_select';
  • 查看慢查詢的個數 show global status like 'Slow_queries';
  • 表掃描情況 show global status like 'Handler_read%'; Handler_read_rnd_next / com_select > 4000 需要考慮優化索引
• show variables
  • 查看慢查詢相關的配置 show variables like 'long_query_time';
  • 將慢查詢時間線設置為2s set global long_query_time=2;
  • 查看InnoDB緩存 show variables like 'innodb_buffer_pool_size';
  • 查看InnoDB緩存的使用狀態 show status like 'Innodb_buffer_pool_%'; 緩存命中率=(1-Innodb_buffer_pool_reads/ Innodb_buffer_pool_read_requests) * 100%；緩存率=（Innodb_buffer_pool_pages_data/ Innodb_buffer_pool_pages_total) * 100%
  • SHOW PROFILES;該命令可以trace在整個執行過程中各資源消耗情況(會話級)
  • SHOW PROCESSLIST; 查看當前有哪些線程正在運行，並且處在何種狀態
  • SHOW ENGINE INNODB STATUS; 可用於分析死鎖，但需要super許可權