文章首發于我的個人博客，歡迎訪問，https://blog.itzhouq.cn/mysql1
基于MySQL 的 SQL 優化總結
在資料庫運維程序中，優化 SQL 是 DBA 團隊的日常任務，例行 SQL 優化，不僅可以提高程式性能，還能減低線上故障的概率，
目前常用的 SQL 優化方式包括但不限于：業務層優化、SQL 邏輯優化、索引優化等，其中索引優化通常通過調整索引或新增索引從而達到 SQL 優化的目的，索引優化往往可以在短時間內產生非常巨大的效果，
--- 來自美團技術團隊
SQL 優化是一個復雜的問題，不同版本和種類的資料庫、不同資料級的資料需要選擇不同的優化策略，
說明：我這里簡單總結一下 SQL 優化，很多的大佬寫過這方面的細節和用法，甚至還有相關的案例，我只是作為一個階段性的總結，肯定是不全面的，如有錯誤和不當之處，歡迎批評指正，不勝感激，
從日常開發寫 SQL 的角度看，需要遵循一些規則，但是這些規則只能解區域分問題，因為隨著開發和資料量的增長，SQL 還是會變慢，這個時候需要一些針對性的措施，比如針對性地添加索引，通過命令或者工具分析變慢的 SQL 等等，
說說 SQL 優化的其中兩個大的原則（肯定還有別的）：
原則一：盡量避免全表掃描，
原則二：通過索引優化，
這兩個涉及的點比較多，他們之間也是有聯系的，下面詳細說說，
1、避免全表掃描
為啥要避免全表掃描呢？因為全表掃描耗費更多的時間，
那么從哪些方法避免全表掃描呢？
對 where 和 order by 涉及的列建立索引可以提高訪問速度，但是要注意，并不是你建立了索引，索引就一定會生效，如果沒有生效查詢時還是全表掃描，速度還是得不到提升，那如何判斷索引沒有生效呢？可以借助 explain + SQL 陳述句的結果判斷，大佬寫的MySQL EXPLAIN 命令: 查看查詢執行計劃中總結了用法，簡單的說，使用該命令分析的結果中很多欄位，其中type 描述了查詢的方式，如果 type 的結果是ALL，那么索引肯定沒起作用，下面總結一下如何避免索引失效，
1、避免在 where 子句中對欄位進行 null 判斷
select id from user where name is null
2、避免在 where 子句使用 != 或者 <>
3、避免在 where 子句中對運算式進行操作
select id from user where age/2 = 20
修改為：
select id from user where age = 20 * 2
4、避免在 where 子句中對欄位進行函式操作
5、避免在 like 查詢中將 %放在開頭
select id from user where username like '%wh'
2、索引優化
適當地添加索引可以提高 SQL 的速度，但也有些注意點，
1、使用聯合索引時，注意索引列的順序，一般遵循最左匹配原則
比如一個索引：
KEY `idx_userid_age` (`userId`, `age`) USING BTREE
符合最左匹配原則的寫法是把userid放在前面
select userid, name from user where userid = 1001 and age = 10
當我們創建的這個聯合索引，就相當于創建了(userid)和(userid, age)兩個索引，聯合索引不滿足最左原則，一般會失效，但是這個還跟 MySQL 優化器有關系，
2、在適當的時候，使用覆寫索引
通常在使用索引檢索資料之后，需要訪問磁盤上資料表檔案讀取所需要的列，這種操作成為“回表”，
若索引中包含查詢的所有列，則不需要回表操作，直接從索引檔案中讀取資料即可，這種索引成為“覆寫索引”，
在查詢時盡量減少select *，只查詢需要的行，條件允許時盡量建立覆寫索引，
3、洗掉冗余索引
索引并不是越多越好，冗余的索引會影響性能，
比如，索引(A, B)相當于創建了索引(A)和索引(A, B)，
4、注意索引的數量
索引不是越多越好，一般不要超過 5 個，索引雖然提高了查詢效率，但是也會降低插入和更新的效率，插入或更新可能會重建索引，索引建立索引也需要慎重考慮，
5、索引不適合建立在有大量重復的欄位上，如性別這類欄位
3、其他
其他原則包括但不限于：
1、查詢 SQL 盡量不要使用 select *，而是 select 某欄位，
2、連表查詢的時候盡量將資料量少的表驅動資料多的表，
3、如果插入的資料較多時，考慮批量插入，
4、原則上不要有超過 5 張以上的表連接
阿里巴巴開發手冊中規定超過三個表禁止 join的，但是這些規范的適用性還是要考慮環境，當連表數量較少時，連表路徑演算法選擇的是動態規劃演算法；但是連表太多的情況下，路徑演算法可能退化成貪心演算法，連表的方案可能不是最優的的，
這種情況下，如何寫 SQL 呢？答案是通過可以通過冗余實作，細節就不展開了，
4、通過工具分析 SQL
說說幾個用到的 SQL 分析工具
4.1 MySQL 自帶的慢查詢日志
MySQL 的慢查詢日志是 MySQL 提供的一種日志，記錄，用于記錄在 MySQL 中回應時間超過設定的閾值的陳述句，在 MySQL 的組態檔 my.ini中開啟后，支持將慢查詢日志寫入檔案或者資料庫，通過explain關鍵詞模擬優化器執行 SQL，分析慢查詢 SQL，
分析相關陳述句使用了哪些表、連接的型別、掃描的行數、使用的索引等，
4.2 日志分析工具 MySQLdumpslow
在生產環境中，手工分析日志、查找 SQL 比較費時間，MySQL 提供的 MySQLdumpslow 工具可以得到一些 SQL 訪問的統計資料，比如訪問次數最多的 10 條 SQL 等，
4.3 第三方工具：美團技術團隊的 SQLAdvisor
由美團技術團隊維護的一個開源的分析 SQL，給出索引優化建議的工具，
只是大概做了個總結，細節都沒有展開，有興趣的同學自行學習吧，
參考文章：
MySQL 快速入門
MySQL 事務機制
MySQL EXPLAIN 命令: 查看查詢執行計劃
MySQL 索引與查詢優化
書寫高質量的 SQL 總結
索引優化的原則總結
美團點評SQL優化工具SQLAdvisor開源
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/40025.html
標籤：MySQL
上一篇：centos7下安裝MySQL 5.7.26 二進制版本（免安裝綠色版）
下一篇：pb如何修改檔案擴展名？