MySQL 慢查詢優化
theme: juejin highlight: a11y-light
前言
優化一個 Web 專案的方法還有很多，比如合理的在後端給返回資料加快取、充分利用瀏覽器的前端快取、CDN，實在不行甚至需要「勞師動眾」的部署多臺機器做叢集和負載均衡等等。該部落格將放在資料庫慢查詢的優化上，這個話題對於 Web 專案而言更具有普適的意義。
一方面它不侷限於任何一種技術棧，另一方面它更是直接在業務邏輯層面從程式碼本質上來優化你的專案效能，這往往也是單例項服務下效能的瓶頸所在。
一個起步就不簡單的原因是，我們如何才能定位到那些真正形成瓶頸的慢查詢。一個普通專案中的 SQL 可能就有大幾十甚至上百個，而「凶手們」就藏匿其中。
一個樸素的想法是在專案中每一個 SQL 執行前後打上時間戳來估計執行時間，暫且不論由於各種因素的影響這種估算可能不準確，更讓人不可接受的是這對原始程式碼造成的極大的侵入。
檢查慢日誌是否開啟
SHOW VARIABLES LIKE '%slow%';
開啟慢查詢日誌
方法一：
修改配置檔案 在 my.ini 增加幾行: 主要是慢查詢的定義時間（超過2秒就是慢查詢），以及慢查詢log日誌記錄（ slow_query_log）
[mysqld]
//其他配置項
slow_query_log=1
slow_query_log_file=/var/log/mysql/log-slow-queries.log
long_query_time=2
方法二：
通過MySQL資料庫開啟慢查詢:
``` //開啟慢日誌 mysql> set global slow_query_log=on; Query OK, 0 rows affected (0.08 sec)
//設定慢查詢時間閾值 mysql> set global long_query_time=3600; Query OK, 0 rows affected (0.08 sec)
//設定控制是否記錄未走索引的 SQL 查詢 mysql> set global log_queries_not_using_indexes=on; Query OK, 0 rows affected (0.00 sec) ```
測試SQL
小夥伴可參考 MySQL生成百萬級測試資料
執行SQL，超過閾值則記錄慢日誌中
檢查對應慢日誌
使用Explain工具對慢SQL進行分析
Explain引數詳情可參考 explain引數詳解
執行後引數分析
explain select * from users;
SQL優化分析
SQL優化可參考 MySQL 常用SQL語句優化
select * form users;
從資料庫裡讀出越多的資料，那麼查詢就會變得越慢。並且，如果你的資料庫伺服器和WEB伺服器是兩臺獨立的伺服器的話，這還會增加網路傳輸的負載。
所以，你應該養成一個需要什麼就取什麼的好的習慣。
以上SQL可優化成：
//需要什麼就取什麼
select id,username form users;//優化後
select id,username from users where username like "%test%";
索引並不一定就是給主鍵或是唯一的欄位。如果在你的表中，有某個欄位你總要會經常用來做搜尋，那麼，請為其建立索引吧。
例如，當你需要在一篇大的文章中搜索一個詞時，如： “WHERE post_content LIKE ‘%apple%’”，索引可能是沒有意義的。你可能需要使用MySQL全文索引或是自己做一個索引（比如說：搜尋關鍵詞或是Tag什麼的）
以上SQL加索引後：
ALTER TABLE `users` ADD INDEX index_name ( `username` );
以上SQL優化後：
//like 優化 去掉前面的% 例：like "%test%" => like "test%";
select username from users where username like "test%";;//優化後