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

面試官如果問你：你會從哪些維度進行MySQL效能優化？你會怎麼回答？

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

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

以下從5個角度介紹一下MySQL最佳化的一些策略。

1. 連線設定最佳化

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

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

1.1 服務端設定

服務端需要做的就是盡可能地接受客戶端的連接，或許你遇過error 1040: Too many connections的錯誤？就是服務端的胸襟不夠寬廣導致的，格局太小！

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

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 使用快取

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

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

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

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

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

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

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

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

主從複製技術來了！我在之前的文章中粗淺地介紹了一下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> 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。

其中，

• 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://www.php.cn/mysql-tutorials-493147.html

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

【相關推薦：mysql影片教學】

#