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 profileshow 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 }

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

【其他文章推薦】

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

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

※回頭車貨運收費標準

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

※超省錢租車方案

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