# 開啟慢查詢日志 使用mysql慢查詢日志對有效率問題的sql進行監控 ~~~ # 查看慢查詢是否開啟 show variables like 'slow_query_log' # 設置全局慢查詢日志文件存放 set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log' # 設置沒有用到索引的sql記錄到慢查詢中 set global log_queries_not_using_indexes=on; # 設置超時多長時間的就捕獲 set global long_query_time=1 #這邊設置的是1秒,一般我們設置100ms也就是0.01 ~~~ # 慢查詢日志的格式  --- ## mysqldumpslow工具 使用mysqldumpslow工具對慢查詢日志進行查看 ~~~ mysqldumpslow -h ~~~ 查看幫助命令 ~~~ 通過什么方式對慢查詢結果進行排序 mysqldumpslow -s 查看前多少條日志 mysqldumpslow -t num 文件路徑 ~~~ -s指定按那種排序方式輸出結果  --- ## pt-query-digest 工具 這個工具對慢查詢日志的分析比mysqldumpslow好  如何通過慢查詢日志發現有問題的sql?  ## 實時獲取有性能問題的sql 在information_schema數據庫中查詢processlist表 # 查詢速度為什么會慢 5步 * 客戶端發送sql請求給服務器 * 服務器檢查是否可以在查詢緩存中命中該sql,有就直接返回給客戶端 * 服務器端進行sql解析,預處理,再由優化器生成對應的執行計劃 * 根據執行計劃,調用存儲引擎API來查詢數據 * 將結果返回給客戶端 **第二步** 優先檢查這個查詢是否命中查詢緩存中的數據 通過一個對大小寫敏感的哈希查找實現 hash查找只能進行全值匹配 在查詢緩存中命中該sql也會對緩存加鎖,對于一個讀寫頻繁的系統使用查詢緩存很可能會降低查詢處理的效率,如果這種情況下不建議使用 如果我們事先知道查詢結果很大不會緩存,就加上sql_no_cache可以提高效率 設置變量 query_cache_type設置查詢緩存是否可用 on,off,DEMAND DEMAND表示在查詢語句中使用sql_cache和sql_no_cache來控制是否需要緩存 query_cache_size設置查詢緩存的內存大小,1024整數倍 query_cache_limit設置查詢緩存可用存儲的最大值 query_cache_wlock_invalidate設置表被鎖后是否返回緩存中的數據 query_cache_min_res_unit設置查詢緩存分配的內存塊最小單位 --- **第三步** mysql依照這個執行計劃和存儲引擎進行交互 這個階段包括了多個子進程: 解析sql,預處理,優化sql執行計劃 語法解析階段是通過關鍵字對mysql語句進行解析,并生成一棵對應的"解析樹" mysql解析器將使用mysql語法規則驗證和解析查詢,包括檢查語法是否使用了正確的關鍵字 關鍵字的順序是否正確 預處理階段是根據mysql規則進一步檢查解析樹是否合法 檢查查詢中所涉及的表和數據列是否存在及名字或別名是否存在歧義等等 語法檢查全都通過了,查詢優化器就可以生成查詢計劃了 會造成mysql生成錯誤的執行計劃的原因 * 統計信息不準確,mysql依賴存儲引擎提供的信息來評估這個成本,不同的存儲引擎提供的信息不同 * * 執行計劃中的成本估算不等于實際的執行計劃的成本 mysql服務層并不知道哪些頁面在內存中 哪些頁面在磁盤上,哪些需要順序讀取,哪些需要隨機讀取 * mysql查詢優化器認為最優的可能與你認為的最優不一樣 mysql是基于成本模型給出的最優執行計劃 * mysql從不考慮其他的并發查詢,這可能影響當前的查詢的速度,比如鎖 * mysql有時候也會基于一些固定規則來生成執行計劃,不如使用了索引,mysql還是會使用全表掃描 * mysql不會考慮其不受控制的成本,存儲過程,用戶自定義函數這些都不在mysql考慮的范圍之內 mysql優化器可以優化的sql類型 * 重新定義表的關聯順序 * 將外連接轉化為內連接,left join where * 使用等價變化規則 * 優化count(),min(),max() 我們有時候會看到這樣一個信息 select tables optimized away 優化器已經從執行計劃中移除了該表,并以一個常數取而代之 * 將一個表達式轉化為常數表達式 * 子查詢優化 * 提前終止查詢 * 對in()優化 如何確定查詢處理各個階段所消耗的時間 使用profile set profiling=1; 執行查詢 show profiles; show profile for query N; N就是上面結果的query_id 查詢每個階段消耗的時間 show profiling cpu for query N; 查詢cpu的消耗 使用performance_schema 啟動 update 'setup_instruments' set enabled='yes' where name like 'stage%'; update setup_consumers set enabled='yes' where name like 'events%'; 分析