程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> Mysql優化之問題定位

Mysql優化之問題定位

編輯：MySQL綜合教程

Mysql優化之問題定位

先扯淡下，很久沒有來csdn寫博客了，最近在學燕18的mysql優化，並且這位老師講的高達上還接地氣，今天剛好有空可以來總結這段時間學到的東西

先上一張流程圖（這張圖引自燕18的教程）

當遇到一台db服務器有問題的時候，首先不是去看代碼哪裡有問題，想sql語句是否寫，表的結構是否合理之類的問題；而是需要從宏觀的角度去看哪些地方有問題

第一步找出服務器問題所在，是否是硬件有瓶頸

如果一台服務器硬件本身就不好，只能承受100M的io讀寫，如果你非要它提供的io達到200M，那麼就算你怎麼優化也搞不定是吧，那麼我們首先需要基准測試需要安裝sysbench，它提供了cpu， Io， memory, mysql等性能的測試，；

1.cpu測試

sysbench --test=cpu --cpu-max-prime=2000000 run

2.io測試

sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw prepare
sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw run
sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw cleanup

3.OLTP測試

sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=test --mysql-host=localhost --mysql-password=test prepare

通過這些測試之後差不過也能知道自己服務器的能力了， 如果發現服務器的性能不錯， 但是依然不能滿足用戶的需求， 那麼就只能是軟件方面的問題了， 就需要定位到底是哪一塊有問題

第二步，觀察mysql在某段時間的連接狀態，處理狀態

如果硬件問題不大，那麼我們就需要觀察mysql的狀態了，一般這個狀態不是一時半會兒能搞定的，都是需要寫個腳本在後台記錄mysql在某一個周期的壓力值記錄，比如是一天，一周為一個周期；查看mysql的狀態命令是show status;

這個命令返回好幾百行的東西，而我們只需要關注3行

1.Queries，當前已經發生過的查詢（可以用兩個時間段的查詢數量相減得到時間段內的查詢數）

2.Threads_connected ，當前有多少個連接連上mysql

3. Threads_running，當前有幾個線程正在運行

通常是Threads_connected >= Threads_running, 因為連上mysql也不一定要工作，可能阻塞，掛起之類的

獲取結果

1.我們寫個腳本

每隔一秒去讀取這三個數追加到mysql.status文件裡面2. 用ab工具模擬訪問，用50個並發，發送20000個請求（這個頁面的每一次請求會多次訪問mysql），這樣就能使上面那個腳本得到結果了ab -c 50 -n 2000 http://59.69.128.203/JudgeOnline/nyistoj/index.php/Problem/index

我們來查看這個mysql.status文件的內容

我們用上一行的第一個值減去下一行的第一個值就可以得到每一秒的訪問mysql數量，差不多是1000+，也可以看出基本上是有50個連接的，平均用兩個線程在處理請求；可以再次寫一個腳本做一下處理

這樣就得到每一秒的處理數量， 1000多一點兒，貌似不咋好的感覺

結果分析

1. 訪問mysql的頻率很穩定（如下圖），那就從mysql的其它部分優化，比如表的結構， sql語句的優化， mysql的配置，引擎的選擇，索引的優化等

2.mysql的訪問頻率呈周期性的變化（如下圖），那麼就是從峰值上優化；比如memcatch是否都是周期性失效，那麼就可以用隨機方式讓失效地更加均勻，或者是讓他在晚上3點左右失效，這個時候的訪問量不大，到了第二天時memcatch的緩沖也基本上建立好了；或者是從業務角度優化，比如12306的放票，可以分省分時間段分批放票，這樣就避免了全國各地大家集體搶票帶來的超高峰值；也可以在高峰期的時候開啟慢查詢， processlist等工具分析到具體的sql語句；

三. 查看mysql進程的狀態

如果需要知道mysql這個進程對處理sql語句的整體情況，那麼我們需要用到show processlist 這個工具，這個工具主要是能夠記錄下來每一條sql執行的過程；我們寫一個腳本抓取status，然後整體看看我們的mysql進程花的時間基本上都是在干什麼；show processlist\G

這裡的Status狀態可能情況比較多，不過我們主要是關注如下幾個狀態： 1. Create tmp table；創建臨時表，比如用了右連接就會新建一張臨時表 2. Sending Data ；發送數據，比如limit 1， 1000；那麼這樣就會傳送大量的數據而花費時間，可以limit小一點兒 3. SortIng for Group；正在為分組排序，這個時候就優化一般是借助復合索引 4. Copying to tmp table on desk; 正在將內存的表拷貝的硬盤，主要是表太大，比如join一下就產生很大的表只能放硬盤了，避免join 5. Locked; 鎖住數據，事務性方面優化，能不用事務就不用 6. Converting HEAP to MyISAM; 查詢的結果太大，正在想硬盤存結果；優化就是盡量一次稍差點兒數據，比如新聞列表的讀取一次少讀點兒，讀者很少一次性讀到幾百條以後；那麼我們寫一個腳本抓取這些status:

然後處理下mysql.process;

就能得到如下結果了：

從圖中可以看出很多次都是花在了Copying to tmp table ，Sending data, Sort result 的次數不少, 可以大致知道是業務邏輯導致需要取出的數據比較多，可以變化業務或者做緩沖服務器擋在mysql前面；
看看 Copying to tmp table; 首先打開profiles;