MySQL資料庫優化

子欽加油發表於2019-01-24

 

 

 

 

檢視是否開啟慢查詢日誌

檢視變數設定情況

我們要記錄未使用索引的查詢就要把變數設定為ON 用以下命令設定

 

再確認慢查詢時間的變數情況

把慢查詢日誌設定為開啟狀態

下面演示如下:

記錄的位置如下圖所示

檢視執行的sql語句是否記錄在了日誌檔案中

下圖是慢查詢日誌檔案記錄的sql語句

慢查詢日誌的儲存格式

 

 

MySQL慢查日誌分析工具之mysqldumpslow

 

檢視下工具可使用的引數

用工具檢視慢查詢日誌記錄的 前三條 用more檢視

檢視結果如下

MySQL慢查日誌分析工具之pt-query-digest(第二款工具)

 

檢視pt-query-digest工具引數情況

演示分析上述日誌檔案

分析

 

如何通過慢查日誌發現有問題的SQL 

 

通過explain查詢和分析SQL的執行計劃

 

下面用具體例子分析慢查詢以及優化

Count()和Max()的優化

上面看到sql語句執行要掃描15422行,這嚴重佔用IO,下面我們建立一個索引來提高效能

再來看下執行計劃

 在看下Count()如何優化的例子

下面是正確的查詢方式

 

 演示執行效果

 

子查詢的優化

 

通常我們優化成join的方式

 

當表之前出現1對多的關係的時候有可能會出現重複,所以要去重

group by的優化

 

以上查詢會出現對臨時表的查詢操作,我們把它優化成如下圖情況 (執行explain來檢視執行計劃)

 Limit查詢的優化

 

看下上面sql語句的執行計劃

上面sql執行計劃結果中採用表掃描的方式掃描了1030行,我們進行如下優化看下

看下執行計劃

上圖使用了主鍵key:PRIMARY和索引type:index查詢,

接下來我們進一步優化

 

看下執行計劃

上面執行計劃中我們可以看到只掃描了5行,這樣就大大提高了SQL執行效率

 

如何選擇合適的列建立索引

 

 索引優化SQL的方法 

 

 

用工具查重複和…索引,輸入命令如下圖紅框

檢視結果

 

索引維護的方法

此處省略……

資料庫表結構的優化:選擇合適的資料型別

 

資料庫表的正規化化優化

 

我們如何做呢,我們分析後可以把表拆分成三張表來處理

資料庫表的反正規化化優化

 

上面的查詢語句顯然增加了IO操作,大大降低查詢效能,我們接下來就要進行反正規化化的操作

資料庫表的垂直拆分

 

 儘量避免text屬性,能不用就不用,是在無法避免就把這些大的欄位拆分到另一張表中

 

拆分後的結果:主表film和表file_text

 

 

資料庫表的水平拆分

 

由於前臺業務量比後臺要求要高,我們們可以把前後臺查詢需要的表分開,後臺統計報表操作總表

資料庫系統配置優化

MySQL配置檔案優化

 

 

 

建議把關鍵引數innodb_file_per_table 設定為ON

 

第三方配置工具使用

 

 伺服器硬體優化

 

相關文章