MySQL問題排查工具介紹

標籤：原因   show   innodb   強制   ora   ref   pos   1.7   arc   

原文連結: http://mrchenatu.com/2017/03/24/mysql-tool/

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

知識準備索引

• 索引是儲存引擎用於快速找到記錄的一種資料結構
• 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_bufferpool%’; 快取命中率=(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許可權

MySQL問題排查工具介紹