1、前言

最近對mysql的操作比較多一些，主要是項目上線以後，難免會有一些數據上的問題。開始的時候還主要由後端來處理，後面數據問題確實比較多，於是我就找後端要來伺服器的帳號密碼，連上資料庫順便來看看數據的問題。

周五使用人數達到了高峰，總共有5300人在使用，今天截圖的時候是周六人數略有減少。

這是三個表數據比較大的表，目前大致運行兩周的時間就已經很大了。

這是數據量最多的一張表，大致已經410W條記錄了。

算是一個小小的系統，不算大，但是從目前數據量的增加來看，慢慢的數據量可能會越來越大。

2、mysql 索引

最開始項目剛上線的時候，因為沒有數據，所以根本沒什麼感覺，突然某一天，就感覺到接口的響應時間明顯的變慢了。但其實後端並沒有什麼線上的經驗，所以我藉機就要來了伺服器的帳號密碼。基本上除了主鍵以外，沒有加任何的索引。打到資料庫上的查詢就實打實的有一些慢了，（雖然這裡使用了一主四從），四個從庫相當於都是用來做查詢使用的，但是在沒有索引的情況下，真的有點慢了。我跟後端稍作溝通，我就準備直接在正式環境添加資料庫表的索引了。

這是平常小程序里接口的返回時間記錄。而且有時候根據訪問人數的不同，偶爾有時候會到三秒到四秒。

3、打開慢查詢記錄開關

那麼能否通過專業的工具去查看呢？首先我做的第一件事情便是，查看一下mysql的慢查詢是否有打開，好傢夥，還不錯，竟然打開了。如果沒開啟可以開啟一下：

// 查看慢查詢日誌是否開啟  on為開啟  off為關閉 默認是關閉的
show variables like 'slow_query_log';

// 設置是否開啟慢查詢日期記錄
set global slow_query_log = on;    #開啟
set global slow_query_log = off;   #關閉

// 查看慢查詢的閾值（默認是10秒）
show variables like 'long_query_time';

// 如果想修改慢查詢的閾值

// 閾值設置為1秒
set global long_query_time = 1;   

// 查看慢查詢日誌文件路徑
show variables like 'slow_query_log_file';

如果慢查詢記錄log沒有打開，可以參考一下這篇文章：juejin.cn/post/716761…

4、通過mysqldumpslow 查詢慢查詢sql

下面是常用的幾個查詢慢SQL的腳本語句

// 得到返回記錄集最多的10條SQL：
mysqldumpslow -s r -t  10 /var/lib/mysql/slow.log

// 得到訪問次數最多的10條SQL：
mysqldumpslow -s r -t  10 /data/mysql/slow.log

// 得到按照時間排序的前10條裡面含有左連接的SQL：
mysqldumpslow -s t -t 100 -g "left join" /var/lib/mysql/slow.log

// 也支持管道符命令
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log | more //分頁顯示

執行後結果如下所示，一目了然

可以查看到第一個sql 平均耗時2.94s，這個sql不論在哪裡使用都會感覺慢了。所以這個時候查看sql以後，可以使用explain + sql 在mysql客戶端執行，查看執行計劃

可以查看返回結果，我平常觀察最多的幾個欄位便是 type、 rows、Extra、等欄位。

如果你想詳細了解 explain的執行計劃，你可以訪問如下連結來重點閱讀： juejin.cn/post/716359…

5、直接添加索引

我簡單可以總結為如下：

join 後看表關聯的欄位
where 後看查詢條件的欄位
group by order by 後的分組條件和排序條件

在有條件的時候，上述地方能加索引就加索引，但是通常一張表添加五個索引就算比較多的，因為如果一張表索引過多在其他地方，比如存儲、添加、刪除的時候都會重新整理索引，成本消耗會很大。

目前來說這種簡單粗暴的方式，在幾百萬數據的量級完全解決了我的問題，這裡展示了我隨便找的一張表，裡面添加了四個索引，這裡完全可以用四個欄位的普通索引即可，我這裡當時為了驗證聯合索引或者叫複合索引就沒改了，目前來看效果還是嘎嘎的香，隨著數據量的增加我猜測索引會有調整。

6、重置慢查詢日誌

假如我們優化完畢了，正式環境重新部署了，我們想查看一下效果，比如想去查詢一下慢查詢的日誌記錄，但是之前的日誌記錄還在，這個時候我們應該怎麼辦呢？

// 通過rm直接刪除慢查詢日誌記錄文件
rm slow.log

// 然後記得要重置慢查詢才會開啟繼續登錄

// 在 mysql所在的linux伺服器上執行
mysqladmin -uroot -p flush-logs slow

//或者在mysql資料庫中執行
mysql> FLUSH LOGS;

重置後可查看slow.log是否重新生成。

7、注意事項

儘量禁止使用 select * 進行查詢：減少IO和傳遞壓力等
查詢條件的類型儘量與資料庫里的類型一致：不一致可能導致索引失效
group by 後如果不想排序可以在後面添加order by null
查詢計劃中儘量避免全表掃描
每張表都要設置主鍵，因為不設置mysql會自動幫我們設置
主鍵最好不要用GUID，儘量自增ID（GUID插入時時無序的）
明確只返回一條記錄的sql 可以加上limit 1
聯合索引（複合索引）查詢時要注意查詢欄位的順序
如果可以儘量給欄位設置默認值，不要為null空值，null在一定程度上會造成索引失效
like 模糊查詢儘量不要以% 開頭，因為會造成索引失效
一個sql關聯的表不要過多（通常最多三到五個）
多表查詢時一定先以小表查詢，再來查詢大表，也就是小表驅動大表
儘量少用or會造成索引失效，有些時候可以使用union all替換
當然還有其他的，暫時在項目使用就這麼多

8、總結

一種情況時找到具體接口中使用的sql，如果很慢進行優化sql或者添加索引，另外一種時通過mysql工具查找到記錄的慢查詢sql,可以直接根據表結構進行添加索引，如果很複雜，而且簡單的增加索引無法提速，可能要根據具體業務進行分析調整再添加索引。總之索引的使用在大部分情況下是非常有效的。通過explain 查看sql執行計劃，進行優化索引和表設計，因為在某些情況合理的表結構默認值設置、或者表關聯欄位設置，都能有效的避免全表掃描。總之不要慫，加錯了索引，大不了花點時間刪除就好了。

作者：那個曾經的少年回來了
連結：https://juejin.cn/post/7173308940036341791
來源：稀土掘金

項目上線後我是如何通過慢查詢和索引讓系統快起來的