MySQL 性能優化之慢查詢

性能優化的思路



  1. 首先需要使用慢查詢功能，去獲取所有查詢時間比較長的SQL語句

  2. 其次使用explain命令去查詢由問題的SQL的執行計劃(腦補鏈接：點我直達1，點我直達2)

  3. 最後可以使用show profile[s] 查看由問題的SQL的性能使用情況

  4. 優化SQL語句


介紹


　　數據庫查詢快慢是影響項目性能的一大因素，對於數據庫，我們除了要優化SQL，更重要的是得先找到需要優化的SQL語句。


　　MySQL數據庫有一個"慢查詢日誌"功能，用來記錄查詢時間超過某個設定值的SQL，這將極大程度幫助我們快速定位到問題所在，以便對症下藥。


至於查詢時間的多少才算慢，每個項目、業務都有不同的要求。
     比如傳統企業的軟件允許查詢時間高於某個值，但是把這個標準方在互聯網項目或者訪問量大的網站上，估計就是一個Bug，甚至可能升級為一個功能缺陷。

　　MySQL的慢查詢日誌功能，默認是關閉的，需要手動開啟。


開啟慢查詢功能


查看是否開啟慢查詢功能



 



 


參數說明:



  • slow_query_log:是否開啟慢查詢,on為開啟,off為關閉;

  • log-slow-queries:舊版(5.6以下版本)MySQL數據庫慢查詢存儲路徑,可以不設置該參數,系統則會給一個缺省的文件:host_name-slow.log

  • long_query_time:慢查詢閥值,當查詢時間多於設置的閥值時,記錄日誌,單位為秒。


臨時開啟滿查詢功能


　　在MySQL執行SQL語句設置,但是如果重啟MySQL的話會失效。


set global slow_query_log=on;
 set global long_query_time=1;

永久性開啟慢查詢


　　修改：/etc/my.cnf，添加以下內容，然後重啟MySQL服務


[mysqld]
 lower_case_table_names=1
 slow_query_log=ON
 slow_query_log_file=/usr/local/mysql/data/chenyanbindeMacBook-Pro-slow.log
 long_query_time=1


 


查看滿查詢啟動狀態



演示慢查詢


　　為了演示方便，我們讓sql睡眠3秒！




格式說明：



  • 第一行，SQL查詢執行的具體時間

  • 第二行，執行SQL查詢的連接信息，用戶和連接IP

  • 第三行，記錄了一些我們比較有用的信息，

    • Query_timme，這條SQL執行的時間，越長則越慢

    • Lock_time，在MySQL服務器階段(不是在存儲引擎階段)等待表鎖時間

    • Rows_sent，查詢返回的行數

    • Rows_examined，查詢檢查的行數，越長就越浪費時間


  • 第四行，設置時間戳，沒有實際意義，只是和第一行對應執行時間。

  • 第五行，執行的SQL語句記錄信息


分析滿查詢日誌


MySQL自帶的mysqldumpslow


 


 



 


參數說明：



  • -s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；

  • -t, 是top n的意思，即為返回前面多少條的數據；

  • -g, 後邊可以寫一個正則匹配模式，大小寫不敏感的；


MySQL性能fenix語句show profile（重要）


介紹



  • Query Profiler是MySQL自帶的一種query診斷分析工具，通過它可以分析出一條SQL語句性能瓶頸在什麼地方。

  • 通常使用explain，以及slow query log都無法做到精確分析，但是Query profiler卻可以定位出一條SQL執行的各種資源消耗情況，比如CPU、IO等，以及該SQL執行所耗費的時間等。不過該工具只有在MySQL5.0.37以上版本中才有實現

  • 默認的情況下，MySQL的該功能沒有打開，需要自己手動打開


語句使用



  • show profile和show profiles語句可以展示當前會話（退出session后，profiling重置為0）中執行語句的資源使用情況。

  • show profiles：以列表形式显示最近發送到服務器上執行的語句的資源使用情況，显示的記錄數由變量：profiling_history_size控制，默認15條

  • show profile：只是最近一條語句執行的消息資源佔用信息，默認實現Status和Duration兩列


開啟Profile功能



  • Profile功能由MySQL會話變量：profiling控制，默認是OFF關閉狀態。

  • 查看是否開啟了Profile功能


select @@profiling;
 
 show variables like '%profil%';


 


打開profiling功能


set profiling=1;


 


show profile用法


SHOW PROFILE [type [, type] …… ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]
 
 type: { ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS }

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】



※教你寫出一流的銷售文案?



※廣告預算用在刀口上，台北網頁設計公司幫您達到更多曝光效益



※回頭車貨運收費標準



※別再煩惱如何寫文案,掌握八大原則!



※超省錢租車方案



※產品缺大量曝光嗎?你需要的是一流包裝設計!



Orignal From: MySQL 性能優化之慢查詢

留言

張貼留言