mysql深入最佳化篇

 

筆者根據博森瑞講師的公開課整理而成

 

<!--more--&gt

 

 

 

 

找到案發現場，就是說找這些sql在哪。

         學一門資料庫是需要過程的，需要總結發現問題，解決問題的思路。

         DBA是網際網路的警察，需要發現罪犯在哪（和破案一樣）。罪犯就是那些慢sql。

        

 

 

 

         一個使用者發起一個查詢請求，首先在buffer pool看有沒有快取這條sql，如果完全匹配，而且許可權又滿足，那麼直接就把這條sql返回給客戶。如果沒有完全匹配，就要把sql語句解析成資料庫認識的語言，即query id，也就是這條sql的id,透過這個id就能找到這條sql的所在位置。接下來，就需要對處理不了的東西進行預處理，即看有沒有語法錯誤，重新分析它的解析數，接著下一步，查詢最佳化器對連線、排序進行計算，對子查詢進行最佳化。把這些最佳化好的sql繼續往下一步走，生成最優的執行計劃，透過執行計劃就知道該走哪條路，能夠在最短的範圍內找到這條sql。然後呼叫儲存引擎到資料檔案裡找到所需要的資料。

         上述就是整個sql的運轉過程。

         我們要知道上述哪一步會帶來問題。

 

 

mysql把sql接過來之後，需要對sql進行解析和處理，處理完之後直接拋給儲存引擎，讓儲存引擎到資料庫裡面去找資料。在儲存引擎到資料檔案找資料的過程中，可能會有人做dml操作，這時就需要把這些dml操作記錄到日誌裡面。從日誌中我們就可以分析整個的執行過程。從中找出是哪些sql造成資料庫效能緩慢。

 

        

預設資料庫是關閉gereral全量查詢日誌，開啟該日誌很多人說會很耗效能。實際上，在cpu 40%，完全可以開啟該全量查詢的日誌，當我們取樣完畢了再把該功能關閉。所有的操作都會記錄到全量查詢日誌裡面。

找到慢sql，然後和開發溝通，說這個sql慢，問開發是改業務，還是在資料庫改sql語句。

全量查詢日誌可以記錄所有的查詢和dml語句。

 

long_query_time=0表示所有的都記錄。

 

mysql慢查詢的工具：

mysqldumpslow 找出top 10，按照時間的大小，按照次數的多少進行排序。

pt-query-digest

 

 

 

上圖包含：主機名，我的慢查詢日誌在哪個檔案裡面，所有sql的執行時間，這條sql的最大的執行時間，最小時間的執行時間、平均時間，這條sql鎖定的時間。我取樣的這個sql是從幾點到幾點的。我們可以取樣24小時內的，我們也可以取樣12小時內的。

 

 

 

根據這條sql query id，就可以找到這條sql語句。

calls，這個sql被呼叫的次數。

        

         上面的第一條sql執行了830s，執行了84次。這個sql太影響系統的效能，太需要最佳化了。

 

 

 

上述update子句中也包含select，透過對這個select 進行explain，看哪個欄位需要加索引，或者調整業務就可以。

不要抱怨任何人，開發寫的sql差，那我們就能寫好這個sql嗎。

        

         explain主要看是不是走索引了，再看掃描的行數。

         如果我想要做一件事，我需要把工具準備好，這樣我才能應用我們所學的知識。

         我們要最佳化影響資料庫主業務的sql，對不影響資料庫主業務的sql沒有必要最佳化。

         我們要最佳化併發度高的sql，和查詢慢的sql。

 

        

 

         記錄主從有沒有延遲，就需要用一些工具，把差異的資料記錄到一張表裡面。

         怎麼找導致主從之間延遲最厲害的sql，這樣導致主從之間延遲慢的sql不會記錄到慢查詢日誌裡面。

         我們可以開啟log_slow_slave_statements，可以記錄導致主從複製延遲的sql。