技術文章 » MySQL如何進行優化？從5個維度聊聊效能優化

MySQL如何進行優化？從5個維度聊聊效能優化

2022-06-14 14:02:48
面試官如果問你：你會從哪些維度進行MySQL效能優化？你會怎麼回答？

所謂的效能優化，一般針對的是MySQL查詢的優化。既然是優化查詢，我們自然要先知道查詢操作要經過哪些環節，然後思考可以在哪些環節進行優化。

我用一張圖展示查詢操作需要經歷的基本環節。

1.png

下面從5個角度介紹一下MySQL優化的一些策略。

2.png

1. 連線設定優化

處理連線是MySQL使用者端和MySQL伺服器端親熱的第一步，第一步都邁不好，也就別談後來的故事了。

既然連線是雙方的事情，我們自然從伺服器端和使用者端兩個方面來進行優化嘍。

1.1 伺服器端設定

伺服器端需要做的就是儘可能地多接受使用者端的連線，或許你遇到過error 1040: Too many connections的錯誤？就是伺服器端的胸懷不夠寬廣導致的，格局太小！

3.png

我們可以從兩個方面解決連線數不夠的問題：

1、增加可用連線數，修改環境變數max_connections，預設情況下伺服器端的最大連線數為151個

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

2、及時釋放不活動的連線，系統預設的使用者端超時時間是28800秒（8小時），我們可以把這個值調小一點

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

MySQL有非常多的設定引數，並且大部分引數都提供了預設值，預設值是MySQL作者經過精心設計的，完全可以滿足大部分情況的需求，不建議在不清楚引數含義的情況下貿然修改。

1.2 使用者端優化

使用者端能做的就是儘量減少和伺服器端建立連線的次數，已經建立的連線能湊合用就湊合用，別每次執行個SQL語句都建立個新連線，伺服器端和使用者端的資源都吃不消啊。

解決的方案就是使用連線池來複用連線。

常見的資料庫連線池有DBCP、C3P0、阿里的Druid、Hikari，前兩者用得很少了，後兩者目前如日中天。

但是需要注意的是連線池並不是越大越好，比如Druid的預設最大連線池大小是8，Hikari預設最大連線池大小是10，盲目地加大連線池的大小，系統執行效率反而有可能降低。為什麼？

對於每一個連線，伺服器端會建立一個單獨的執行緒去處理，連線數越多，伺服器端建立的執行緒自然也就越多。而執行緒數超過CPU個數的情況下，CPU勢必要通過分配時間片的方式進行執行緒的上下文切換，頻繁的上下文切換會造成很大的效能開銷。

Hikari官方給出了一個PostgreSQL資料庫連線池大小的建議值公式，CPU核心數*2+1。假設伺服器的CPU核心數是4，把連線池設定成9就可以了。這種公式在一定程度上對其他資料庫也是適用的，大家面試的時候可以吹一吹。

2. 架構優化

2.1 使用快取

系統中難免會出現一些比較慢的查詢，這些查詢要麼是資料量大，要麼是查詢複雜（關聯的表多或者是計算複雜），使得查詢會長時間佔用連線。

如果這種資料的實效性不是特別強（不是每時每刻都會變化，例如每日報表），我們可以把此類資料放入快取系統中，在資料的快取有效期內，直接從快取系統中獲取資料，這樣就可以減輕資料庫的壓力並提升查詢效率。

4.png

2.2 讀寫分離（叢集、主從複製）

專案的初期，資料庫通常都是執行在一臺伺服器上的，使用者的所有讀寫請求會直接作用到這臺資料庫伺服器，單臺伺服器承擔的並行量畢竟是有限的。

針對這個問題，我們可以同時使用多臺資料庫伺服器，將其中一臺設定為為小組長，稱之為master節點，其餘節點作為組員，叫做slave。使用者寫資料只往master節點寫，而讀的請求分攤到各個slave節點上。這個方案叫做讀寫分離。給組長加上組員組成的小團體起個名字，叫叢集。

5.png

注：很多開發者不滿master-slave這種具有侵犯性的詞彙（因為他們認為會聯想到種族歧視、黑人奴隸等），所以發起了一項更名運動。

受此影響MySQL也會逐漸停用master、slave等術語，轉而用source和replica替代，大家碰到的時候明白即可。

使用叢集必然面臨一個問題，就是多個節點之間怎麼保持資料的一致性。畢竟寫請求只往master節點上傳送了，只有master節點的資料是最新資料，怎麼把對master節點的寫操作也同步到各個slave節點上呢？

主從複製技術來了！我在之前的文章中粗淺地介紹了一下binlog紀錄檔，我直接搬過來了。

binlog是實現MySQL主從複製功能的核心元件。master節點會將所有的寫操作記錄到binlog中，slave節點會有專門的I/O執行緒讀取master節點的binlog，將寫操作同步到當前所在的slave節點。

6.png

這種叢集的架構對減輕主資料庫伺服器的壓力有非常好的效果，但是隨著業務資料越來越多，如果某張表的資料量急劇增加，單表的查詢效能就會大幅下降，而這個問題是讀寫分離也無法解決的，畢竟所有節點存放的是一模一樣的資料啊，單表查詢效能差，說的自然也是所有節點效能都差。

這時我們可以把單個節點的資料分散到多個節點上進行儲存，這就是分庫分表。

2.3 分庫分表

分庫分表中的節點的含義比較寬泛，要是把資料庫作為節點，那就是分庫；如果把單張表作為節點，那就是分表。

大家都知道分庫分表分成垂直分庫、垂直分表、水平分庫和水平分表，但是每次都記不住這些概念，我就給大家詳細說一說，幫助大家理解。

2.3.1 垂直分庫

7.png

在單體資料庫的基礎上垂直切幾刀，按照業務邏輯拆分成不同的資料庫，這就是垂直分庫啦。

8.png

2.3.2 垂直分表

9.png

垂直分表就是在單表的基礎上垂直切一刀（或幾刀），將一個表的多個字短拆成若干個小表，這種操作需要根據具體業務來進行判斷，通常會把經常使用的欄位（熱欄位）分成一個表，不經常使用或者不立即使用的欄位（冷欄位）分成一個表，提升查詢速度。

10.png

拿上圖舉例：通常情況下商品的詳情資訊都比較長，而且檢視商品列表時往往不需要立即展示商品詳情（一般都是點選詳情按鈕才會進行顯示），而是會將商品更重要的資訊（價格等）展示出來，按照這個業務邏輯，我們將原來的商品表做了垂直分表。

2.3.3 水平分表

把單張表的資料按照一定的規則（行話叫分片規則）儲存到多個資料表上，橫著給資料表來一刀（或幾刀），就是水平分表了。

11.png

12.png

2.3.4 水平分庫

水平分庫就是對單個資料庫水平切一刀，往往伴隨著水平分表。

13.png

14.png

2.3.5 總結

水平分，主要是為了解決儲存的瓶頸；垂直分，主要是為了減輕並行壓力。

2.4 訊息佇列削峰

通常情況下，使用者的請求會直接存取資料庫，如果同一時刻線上使用者數量非常龐大，極有可能壓垮資料庫（參考明星出軌或公佈戀情時微博的狀態）。

這種情況下可以通過使用訊息佇列降低資料庫的壓力，不管同時有多少個使用者請求，先存入訊息佇列，然後系統有條不紊地從訊息佇列中消費請求。

15.png

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。

16.png

其中，

  • 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 索引優化

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

推薦閱讀：《用好MySQL索引，你必須知道的一些事情》，感興趣的讀者可以看一下。
https://mp.weixin.qq.com/s?__biz=MzI1MDU0MTc2MQ==&mid=2247484338&idx=1&sn=f753421c70f0e436c040af9e969c3331&chksm=e981e01cdef6690ae6e4bec7c92b436019f5f30e02f35524ca8e144a92b8aa743fc2c51d2c27&token=1056536482&lang=zh_CN#rd

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 不要用外來鍵、觸發器和檢視功能

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

  • 降低了可讀性，檢查程式碼的同時還得檢視資料庫的程式碼；

  • 把計算的工作交給程式，資料庫只做好儲存的工作，並把這件事情做好；

  • 資料的完整性校驗的工作應該由開發者完成，而不是依賴於外來鍵，一旦用了外來鍵，你會發現測試的時候隨便刪點垃圾資料都變得異常艱難。

4.2.5 圖片、音訊、視訊儲存

不要直接儲存大檔案，而是要儲存大檔案的存取地址。

4.2.6 大欄位拆分和資料冗餘

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

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

5. 業務優化

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

  • 以往都是雙11當晚開始買買買的模式，最近幾年雙11的預售戰線越拉越長，提前半個多月就開始了，而且各種定金紅包模式叢出不窮，這種方式叫做預售分流。這樣做可以分流客戶的服務請求，不必等到雙十一的凌晨一股腦地集體下單；

  • 雙十一的凌晨你或許想查詢當天之外的訂單，但是卻查詢失敗；甚至支付寶裡的小雞的口糧都被延遲發放了，這是一種降級策略，集結不重要的服務的計算資源，用來保證當前最核心的業務；

  • 雙十一的時候支付寶極力推薦使用花唄支付，而不是銀行卡支付，雖然一部分考量是提高軟體粘性，但是另一方面，使用餘額寶實際使用的阿里內部伺服器，存取速度快，而使用銀行卡，需要呼叫銀行介面，相比之下操作要慢了許多。

MySQL優化的總結寫到此就結束了，其中有不少細節沒有提及，多少讓我感覺這篇文章不完美。但是有些知識點掰開講又太多了，不可能一下子全部寫下，之後再好好寫吧。

【相關推薦：】