3. 優化器——SQL分析與優化

處理完連接、優化完緩存等架構的事情，SQL查詢語句來到了解析器和優化器的地盤了。在這一步如果出了任何問題，那就只能是SQL語句的問題了。

只要你的語法不出問題，解析器就不會有問題。此外，為了防止你寫的SQL運行效率低，優化器會自動做一些優化，但如果實在是太爛，優化器也救不了你了，只能眼睜睜地看著你的SQL查詢淪為 慢查詢 。

3.1 慢查詢

慢查詢就是執行地很慢的查詢（這句話說得跟廢話似的。。。），只有知道MySQL中有哪些慢查詢我們才能針對性地進行優化。

因為開啟慢查詢日志是有性能代價的，因此MySQL默認是關閉慢查詢日志功能，使用以下命令查看當前慢查詢狀態

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

slow_query_log表示當前慢查詢日志是否開啟，slow_query_log_file表示慢查詢日志的保存位置。

除了上面兩個變量，我們還需要確定“慢”的指標是什么，即執行超過多長時間才算是慢查詢，默認是10S，如果改成0的話就是記錄所有的SQL。

mysql> show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

3.1.1 打開慢日志

有兩種打開慢日志的方式

1. 修改配置文件my.cnf

此種修改方式系統重啟后依然有效

# 是否開啟慢查詢日志
slow_query_log=ON
# 
long_query_time=2
slow_query_log_file=/var/lib/mysql/slow.log

2. 動態修改參數（重啟后失效）

mysql> set @@global.slow_query_log=1;
Query OK, 0 rows affected (0.06 sec)

mysql> set @@global.long_query_time=2;
Query OK, 0 rows affected (0.00 sec)

3.1.2 慢日志分析

MySQL不僅為我們保存了慢日志文件，還為我們提供了慢日志查詢的工具mysqldumpslow，為了演示這個工具，我們先構造一條慢查詢：

mysql> SELECT sleep(5);

然后我們查詢用時最多的1條慢查詢：

[root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log

Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  SELECT sleep(N)

其中，

• Count ：表示這個SQL執行的次數
• Time ：表示執行的時間，括號中的是累積時間
• Locks ：表示鎖定的時間，括號中的是累積時間
• Rows ：表示返回的記錄數，括號中的是累積數

更多關于mysqldumpslow的使用方式，可以查閱官方文檔，或者執行mysqldumpslow --help尋求幫助。

3.2 查看運行中的線程

我們可以運行show full processlist查看MySQL中運行的所有線程，查看其狀態和運行時間，找到不順眼的，直接kill。

image-20220405182328247

其中，

• Id ：線程的唯一標志，可以使用Id殺死指定線程
• User ：啟動這個線程的用戶，普通賬戶只能查看自己的線程
• Host ：哪個ip和端口發起的連接
• db ：線程操作的數據庫
• Command ：線程的命令
• Time ：操作持續時間，單位秒
• State ：線程的狀態
• Info ：SQL語句的前100個字符

3.3 查看服務器運行狀態

使用SHOW STATUS查看MySQL服務器的運行狀態，有session和global兩種作用域，一般使用like+通配符進行過濾。

-- 查看select的次數
mysql> SHOW GLOBAL STATUS LIKE 'com_select';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 168241 |
+---------------+--------+
1 row in set (0.05 sec)

3.4 查看存儲引擎運行信息

SHOW ENGINE用來展示存儲引擎的當前運行信息，包括事務持有的表鎖、行鎖信息；事務的鎖等待情況；線程信號量等待；文件IO請求；Buffer pool統計信息等等數據。

例如：

SHOW ENGINE INNODB STATUS;

上面這條語句可以展示innodb存儲引擎的當前運行的各種信息，大家可以據此找到MySQL當前的問題，限于篇幅不在此意義說明其中信息的含義，大家只要知道MySQL提供了這樣一個監控工具就行了，等到需要的時候再來用就好。

3.5 EXPLAIN執行計劃

通過慢查詢日志我們可以知道哪些SQL語句執行慢了，可是為什么慢？慢在哪里呢？

MySQL提供了一個執行計劃的查詢命令EXPLAIN，通過此命令我們可以查看SQL執行的計劃，所謂執行計劃就是：優化器會不會優化我們自己書寫的SQL語句（比如外連接改內連接查詢，子查詢優化為連接查詢...）、優化器針對此條SQL的執行對哪些索引進行了成本估算，并最終決定采用哪個索引（或者最終選擇不用索引，而是全表掃描）、優化器對單表執行的策略是什么，等等等等。

EXPLAIN在MySQL5.6.3之后也可以針對UPDATE、DELETE和INSERT語句進行分析，但是通常情況下我們還是用在SELECT查詢上。

這篇文章主要是從宏觀上多個角度介紹MySQL的優化策略，因此這里不詳細說明EXPLAIN的細節，之后單獨成篇。

3.6 SQL與索引優化

3.6.1 SQL優化

SQL優化指的是SQL本身語法沒有問題，但是有實現相同目的的更好的寫法。比如：

• 使用小表驅動大表；用join改寫子查詢；or改成union
• 連接查詢中，盡量減少驅動表的扇出（記錄數），訪問被驅動表的成本要盡量低，盡量在被驅動表的連接列上建立索引，降低訪問成本；被驅動表的連接列最好是該表的主鍵或者是唯一二級索引列，這樣被驅動表的成本會降到更低
• 大偏移量的limit，先過濾再排序

針對最后一條舉個簡單的例子，下面兩條語句能實現同樣的目的，但是第二條的執行效率比第一條執行效率要高得多（存儲引擎使用的是InnoDB），大家感受一下：

-- 1. 大偏移量的查詢
mysql> SELECT * FROM user_innodb LIMIT 9000000,10;
Empty set (8.18 sec)

-- 2.先過濾ID（因為ID使用的是索引），再limit
mysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;
Empty set (0.02 sec)

3.6.2 索引優化

為慢查詢創建適當的索引是個非常常見并且非常有效的方法，但是索引是否會被高效使用又是另一門學問了。

4. 存儲引擎與表結構

4.1 選擇存儲引擎

一般情況下，我們會選擇MySQL默認的存儲引擎存儲引擎InnoDB，但是當對數據庫性能要求精益求精的時候，存儲引擎的選擇也成為一個關鍵的影響因素。

建議根據不同的業務選擇不同的存儲引擎，例如：

• 查詢操作、插入操作多的業務表，推薦使用MyISAM；
• 臨時表使用Memory；
• 并發數量大、更新多的業務選擇使用InnoDB；
• 不知道選啥直接默認。

4.2 優化字段

字段優化的最終原則是： 使用可以正確存儲數據的最小的數據類型 。

4.2.1 整數類型

MySQL提供了6種整數類型，分別是

• tinyint
• smallint
• mediumint
• int
• integer
• bigint

不同的存儲類型的最大存儲范圍不同，占用的存儲的空間自然也不同。

例如，是否被刪除的標識，建議選用tinyint，而不是bigint。

4.2.2 字符類型

你是不是直接把所有字符串的字段都設置為varchar格式了？甚至怕不夠，還會直接設置成varchar(1024)的長度？

如果不確定字段的長度，肯定是要選擇varchar，但是varchar需要額外的空間來記錄該字段目前占用的長度；因此如果字段的長度是固定的，盡量選用char，這會給你節約不少的內存空間。

4.2.3 非空

非空字段盡量設置成NOT NULL，并提供默認值，或者使用特殊值代替NULL。

因為NULL類型的存儲和優化都會存在性能不佳的問題，具體原因在這里就不展開了。

4.2.4 不要用外鍵、觸發器和視圖功能

這也是「阿里巴巴開發手冊」中提到的原則。原因有三個：

1. 降低了可讀性，檢查代碼的同時還得查看數據庫的代碼；
2. 把計算的工作交給程序，數據庫只做好存儲的工作，并把這件事情做好；
3. 數據的完整性校驗的工作應該由開發者完成，而不是依賴于外鍵，一旦用了外鍵，你會發現測試的時候隨便刪點垃圾數據都變得異常艱難。

4.2.5 圖片、音頻、視頻存儲

不要直接存儲大文件，而是要存儲大文件的訪問地址。

4.2.6 大字段拆分和數據冗余

大字段拆分其實就是前面說過的垂直分表，把不常用的字段或者數據量較大的字段拆分出去，避免列數過多和數據量過大，尤其是習慣編寫SELECT *的情況下，列數多和數據量大導致的問題會被嚴重放大！

字段冗余原則上不符合數據庫設計范式，但是卻非常有利于快速檢索。比如，合同表中存儲客戶id的同時可以冗余存儲客戶姓名，這樣查詢時就不需要再根據客戶id獲取用戶姓名了。因此針對業務邏輯適當做一定程度的冗余也是一種比較好的優化技巧。

5. 業務優化

嚴格來說，業務方面的優化已經不算是MySQL調優的手段了，但是業務的優化卻能非常有效地減輕數據庫訪問壓力，這方面一個典型例子就是淘寶，下面舉幾個簡單例子給大家提供一下思路：

1. 以往都是雙11當晚開始買買買的模式，最近幾年雙11的預售戰線越拉越長，提前半個多月就開始了，而且各種定金紅包模式叢出不窮，這種方式叫做 預售分流 。這樣做可以分流客戶的服務請求，不必等到雙十一的凌晨一股腦地集體下單；
2. 雙十一的凌晨你或許想查詢當天之外的訂單，但是卻查詢失敗；甚至支付寶里的小雞的口糧都被延遲發放了，這是一種 降級策略 ，集結不重要的服務的計算資源，用來保證當前最核心的業務；
3. 雙十一的時候支付寶極力推薦使用花唄支付，而不是銀行卡支付，雖然一部分考量是提高軟件粘性，但是另一方面，使用余額寶實際使用的阿里內部服務器，訪問速度快，而使用銀行卡，需要調用銀行接口，相比之下操作要慢了許多。