MySQL性能優化的5個維度

語言: CN / TW / HK

面試官如果問你:你會從哪些維度進行MySQL性能優化?你會怎麼回答?

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

我之前寫過一條SQL查詢語句是如何執行的?,感興趣的朋友可以閲讀一下,我用其中的一張圖展示查詢操作需要經歷的基本環節。

SQL查詢的環節

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

image-20220405204100602

1. 連接配置優化

處理連接是MySQL客户端和MySQL服務端親熱的第一步,第一步都邁不好,也就別談後來的故事了。

既然連接是雙方的事情,我們自然從服務端和客户端兩個方面來進行優化嘍。

1.1 服務端配置

服務端需要做的就是儘可能地多接受客户端的連接,或許你遇到過error 1040: Too many connections的錯誤?就是服務端的胸懷不夠寬廣導致的,格局太小!

感謝媳婦兒給畫的圖

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

  1. 增加可用連接數,修改環境變量max_connections,默認情況下服務端的最大連接數為151

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

  1. 及時釋放不活動的連接,系統默認的客户端超時時間是28800秒(8小時),我們可以把這個值調小一點

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

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

1.2 客户端優化

客户端能做的就是儘量減少和服務端建立連接的次數,已經建立的連接能湊合用就湊合用,別每次執行個SQL語句都創建個新連接,服務端和客户端的資源都吃不消啊。

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

常見的數據庫連接池有DBCPC3P0、阿里的DruidHikari,前兩者用得很少了,後兩者目前如日中天。

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

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

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

2. 架構優化

2.1 使用緩存

系統中難免會出現一些比較慢的查詢,這些查詢要麼是數據量大,要麼是查詢複雜(關聯的表多或者是計算複雜),使得查詢會長時間佔用連接。

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

緩存的使用

2.2 讀寫分離(集羣、主從複製)

項目的初期,數據庫通常都是運行在一台服務器上的,用户的所有讀寫請求會直接作用到這台數據庫服務器,單台服務器承擔的併發量畢竟是有限的。

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

這就是集羣

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

受此影響MySQL也會逐漸停用masterslave等術語,轉而用sourcereplica替代,大家碰到的時候明白即可。

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

主從複製技術來了!我在一條SQL更新語句是如何執行的?中粗淺地介紹了一下binlog日誌,我直接搬過來了。

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

主從複製

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

這時我們可以把單個節點的數據分散到多個節點上進行存儲,這就是分庫分表

2.3 分庫分表

分庫分表中的節點的含義比較寬泛,要是把數據庫作為節點,那就是分庫;如果把單張表作為節點,那就是分表。

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

2.3.1 垂直分庫

垂直分庫

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

垂直分庫

2.3.2 垂直分表

垂直分表

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

垂直分表

拿上圖舉例:通常情況下商品的詳情信息都比較長,而且查看商品列表時往往不需要立即展示商品詳情(一般都是點擊詳情按鈕才會進行顯示),而是會將商品更重要的信息(價格等)展示出來,按照這個業務邏輯,我們將原來的商品表做了垂直分表。

2.3.3 水平分表

把單張表的數據按照一定的規則(行話叫分片規則)保存到多個數據表上,橫着給數據表來一刀(或幾刀),就是水平分表了。

水平分表

水平分表

2.3.4 水平分庫

水平分庫就是對單個數據庫水平切一刀,往往伴隨着水平分表。

水平分庫

水平分庫

2.3.5 總結

水平分,主要是為了解決存儲的瓶頸;垂直分,主要是為了減輕併發壓力。

2.4 消息隊列削峯

通常情況下,用户的請求會直接訪問數據庫,如果同一時刻在線用户數量非常龐大,極有可能壓垮數據庫(參考明星出軌或公佈戀情時微博的狀態)。

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

隊列削峯

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

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

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

3.1 慢查詢

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

因為開啟慢查詢日誌是有性能代價的,因此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 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 ```

  1. 動態修改參數(重啟後失效)

```mysql 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 mysql> SELECT sleep(5);

然後我們查詢用時最多的1條慢查詢:

```bash [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服務器的運行狀態,有sessionglobal兩種作用域,一般使用like+通配符進行過濾。

mysql -- 查看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統計信息等等數據。

例如:

mysql 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),大家感受一下:

```mysql -- 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索引,你必須知道的一些事情 ,感興趣的讀者可以看一下。

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. 雙十一的時候支付寶極力推薦使用花唄支付,而不是銀行卡支付,雖然一部分考量是提高軟件粘性,但是另一方面,使用餘額寶實際使用的阿里內部服務器,訪問速度快,而使用銀行卡,需要調用銀行接口,相比之下操作要慢了許多。

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

我是蟬沐風,公眾號「蟬沐風」,一個認真寫文章的技術人,下期見!