mysql調優從書寫sql開始

Coding測試發表於2020-11-19

理論知識

MySQL 的執行機制
Mysql 的SQL關鍵字執行順序

公眾號:Coding測試

1、MySQL 的優化方案有哪些？

MySQL 資料庫常見的優化手段分為三個層面：SQL 和索引優化、資料庫結構優化、系統硬體優化等。
前兩個可以通過日常的書寫sql來實現，養成好的習慣可以事半功倍。
SQL 和索引優化習慣準則：
- 習慣1：我們應該儘可能的使用主鍵查詢，而非其他索引查詢，因為主鍵查詢不會觸發回表查詢，因此節省了一部分時間，變相的提高了查詢的效能。
- 習慣2：避免在 where 查詢條件中使用 != 或者 <> 操作符，因為這些操作符會導致查詢引擎放棄索引而進行全表掃描。
- 習慣3：（查詢具體的欄位而非全部欄位）
  要儘量避免使用 select *，而是查詢需要的欄位，這樣可以提升速度，以及減少網路傳輸的頻寬壓力。
- 習慣4：（優化子查詢）
  儘量使用 Join 語句來替代子查詢，因為子查詢是巢狀查詢，而巢狀查詢會新建立一張臨時表，而臨時表的建立與銷燬會佔用一定的系統資源以及花費一定的時間，但 Join 語句並不會建立臨時表，因此效能會更高。
- 習慣5：（注意查詢結果集）
  我們要儘量使用小表驅動大表的方式進行查詢，也就是如果 B 表的資料小於 A 表的資料，那執行的順序就是先查 B 表再查 A 表。
- 習慣6：（不要在列上進行運算操作）
  不要在列欄位上進行算術運算或其他表示式運算，否則可能會導致查詢引擎無法正確使用索引，從而影響了查詢的效率。
- 習慣7：（適當增加冗餘欄位）
  增加冗餘欄位可以減少大量的連表查詢，因為多張表的連表查詢效能很低，所有可以適當的增加冗餘欄位，以減少多張表的關聯查詢，這是以空間換時間的優化策略。
資料庫結構優化習慣準則
- 習慣1：（最小資料長度）
  一般說來資料庫的表越小，那麼它的查詢速度就越快，因此為了提高表的效率，應該將表的欄位設定的儘可能小，比如身份證號，可以設定為 char(18) 就不要設定為 varchar(18)。
- 習慣2：（使用最簡單資料型別）
  能使用 int 型別就不要使用 varchar 型別，因為 int 型別比 varchar 型別的查詢效率更高。
- 習慣3：（儘量少定義 text 型別）
  text 型別的查詢效率很低，如果必須要使用 text 定義欄位，可以把此欄位分離成子表，需要查詢此欄位時使用聯合查詢，這樣可以提高主表的查詢效率。
- 習慣4：（適當分表、分庫策略）
  分表和分庫方案也是我們經常說的垂直分隔（分表）和水平分隔（分庫）。

2、正確使用聯合索引

使用了 B+ 樹的 MySQL 資料庫引擎，比如 InnoDB 引擎，在每次查詢複合欄位時是從左往右匹配資料的，因此在建立聯合索引的時候需要注意索引建立的順序。例如，我們建立了一個聯合索引是 idx(name,age,sex)，那麼當我們使用，姓名+年齡+性別、姓名+年齡、姓名等這種最左字首查詢條件時，就會觸發聯合索引進行查詢；然而如果非最左匹配的查詢條件，例如，性別+姓名這種查詢條件就不會觸發聯合索引。

慢查詢排查
慢查詢通常的排查手段是先使用慢查詢日誌功能，1.查詢出比較慢的 SQL 語句，2.然後再通過 explain 來查詢 SQL 語句的執行計劃，最後分析並定位出問題的根源，再進行處理。
配置開啟說明（需要注意的是，在開啟慢日誌功能之後，會對 MySQL 的效能造成一定的影響，因此在生產環境中要慎用此功能）
我們需要配置 my.cnf 中的“slow_query_log=1”即可，並且可以通過設定“slow_query_log_file=/tmp/mysql_slow.log”來配置慢查詢日誌的儲存目錄，但這種方式配置完成之後需要重啟 MySQL 伺服器才可生效。

具體查詢可以通過mysql的Explain執行計劃來排查

具體使用詳情見該文章