mysql查詢太慢，我們如何進行效能優化？

努力的老劉發表於2021-02-07
MySql優化
  • 老劉是即將找工作的研究生，自學大資料開發，一路走來，感慨頗深，網上大資料的資料良莠不齊，於是想寫一份詳細的大資料開發指南。這份指南把大資料的【基礎知識】【框架分析】【原始碼理解】都用自己的話描述出來，讓夥伴自學從此不求人。
  • 您的點贊是我持續更新的動力，禁止白嫖，看了就要有收穫，一起加油。

今天給大家分享的是MySQL效能優化，也是大資料開發指南MySQL的最後一部分。效能優化對於老劉來說，是必須掌握的一個手段，如何讓自己變得更加優秀，這塊內容還是好好看看！

本篇內容相對簡潔，核心內容在SQL優化經驗總結，通過這篇mysql的效能優化，大家能夠掌握如下內容：

  1. 會使用和分析慢查詢日誌
  2. 會使用和分析profile
  3. SQL優化經驗總結

如何進行效能分析？

一般進行效能分析，分如下三步：

  1. 首先需要使用慢查詢日誌功能，去獲取所有查詢時間比較長的SQL語句
  2. 其次檢視執行計劃檢視有問題的SQL的執行計劃 explain
  3. 最後可以使用show profile檢視有問題的SQL的效能使用情況

慢查詢日誌分析

首先我們要使用慢查詢日誌，因為它收集了查詢時間比較長的SQL語句，但使用之前必須開啟慢查詢日誌，在配置檔案my.cnf（一般為/etc/my.cnf）中的[mysqld] 增加如下引數：

slow_query_log=ON
long_query_time=3
slow_query_log_file=/var/lib/mysql/slow-log.log

增加這些引數之後，重啟MySQL，可以進行查詢慢查詢日誌是否開啟。

mysql查詢太慢，我們如何進行效能優化？

分析慢查詢日誌的工具

分析慢查詢日誌的工具有很多，老劉分享幾種工具，詳細的用法大家自行查詢。

  1. mysqldumpslow是MySQL自帶的慢查詢日誌工具，我們可以使用mysqldumpslow工具搜尋慢查詢日誌中的SQL語句。
  2. percona-toolkit是一組高階命令列工具的集合，可以檢視當前服務的摘要資訊，磁碟檢測，分析慢查詢日誌，查詢重複索引，實現表同步等等(有空單獨寫一篇關於percona-toolkit的入門部落格)。

explain檢視有問題的SQL語句

當SQL查詢速度比較慢的時候，我們可以用explain檢視這個SQL語句的相關情況，這部分內容已經在精通MySQL之索引篇講過，大家可以去看看。

show profile檢視有問題的SQL語句

Query Profiler是MySQL自帶的一種query診斷分析工具，通過它可以分析出一條SQL語句的硬體效能瓶頸在什麼地方。比如CPU，IO等，以及該SQL執行所耗費的時間等。不過該工具只有在MySQL 5.0.37以及以上版本中才有實現。預設的情況下，MYSQL的該功能沒有開啟，需要自己手動啟動。

SQL優化經驗總結

由於老劉還是研究生以及還沒工作，所以在SQL效能優化這塊只能總結別人的經驗分享給大家，老劉本篇主要想做的事情也是分享一些優秀工程師總結的SQL優化知識點，前面的內容寫的相對簡潔，希望大家不要埋怨！

  1. 任何地方都不要使用 select * from t，用具體的欄位列表代替“*“，不要返回用不到的任何欄位。

  2. 索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。

  3. 並不是所有索引對查詢都有效，SQL是根據表中資料來進行查詢優化的，當索引列有大量資料重複時，SQL查詢可能不會去利用索引，如一表中有欄位sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。

  4. 儘量使用數字型欄位，若只含數值資訊的欄位儘量不要設計為字元型，這會降低查詢和連線的效能，並會增加儲存開銷。這是因為引擎在處理查詢和連線時會逐個比較字串中每一個字元，而對於數字型而言只需要比較一次就夠了。

  5. 儘可能的使用 varchar 代替 char ，因為首先變長欄位儲存空間小，可以節省儲存空間， 其次對於查詢來說，在一個相對較小的欄位內搜尋效率顯然要高些。

  6. 如果使用到了臨時表，在儲存過程的最後務必將所有的臨時表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

  7. 對查詢進行優化，應儘量避免全表掃描，首先應考慮在 where和order by相關的列上建立索引。

  8. 應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。

    例如： select * from t where num is null

    我們可以在num上設定預設值0，確保表中num列沒有null值，然後這樣查詢：select * from t where num=0。

  9. 索引欄位上不要使用不等，索引欄位上使用（！= 或者 < >）判斷時，會導致索引失效而轉向全表掃描。

  10. 應儘量避免在 where 子句中使用 or 來連線條件，否則將導致引擎放棄使用索引而進行全表掃描。

    例如： select * from t where num=10 or num=20

    我們可以這樣查詢：select * from t where num=10 union all select * from t where num=20

  11. 應儘量避免在 where 子句中對欄位進行表示式操作，這將導致引擎放棄使用索引而進行全表掃描。

    例如：select * from t where num/2=100

    我們應該改為: select * from t where num=100*2

  12. 應儘量避免在where子句中對欄位進行函式操作，這將導致引擎放棄使用索引而進行全表掃描。

    例如：select * from t where substring(name,1,3)='abc' -- name以abc開頭的id

    我們應該改為: select * from t where name like 'abc%'

  13. 不要在 where 子句中的“=”左邊進行函式、算術運算或其他表示式運算，否則系統將可能無法正確使用索引。

  14. 很多時候用 exists 代替 in 是一個好的選擇。

    例如：select num from a where num in(select num from b)

    我們應該這樣替換：select num from a where exists(select 1 from b where num=a.num)

總結

本文作為大資料開發指南MySQL的最後一篇簡潔明練的講述了一些SQL效能優化的技巧，希望大家能夠跟著老劉的文章，好好捋捋思路，爭取能夠用自己的話把這些知識點講述出來！

儘管當前水平可能不及各位大佬，但老劉會努力變得更加優秀，讓各位小夥伴自學從此不求人！

大資料開發指南地址如下：

  • github：https://github.com/BigDataLaoLiu/BigDataGuide
  • 碼雲：https://gitee.com/BigDataLiu/BigDataGuide

如果有相關問題，聯絡公眾號：努力的老劉。文章都看到這了，點贊關注支援一波！

mysql查詢太慢，我們如何進行效能優化？

```

相關文章