MySQL 效能調優的三個方向-資料庫-CodeUp Hub

MySQL 資料庫是一個廣泛使用的關係型資料庫管理系統，在日常開發和生產環境中，資料庫的效能直接影響到系統的穩定性和響應速度。爲了保證資料庫高效執行，MySQL 效能調優和 SQL 調優是必不可少的。本文將深入探討 MySQL 的效能調優、SQL 調優的步驟，以及 EXPLAIN 的使用技巧及調優方法。

一、MySQL 效能調優概述

1.1 效能調優的重要性

效能調優是爲了提高資料庫的處理速度，減少資源消耗，確保系統在高併發、大資料量的環境下依然能穩定執行。調優不僅可以提高使用者體驗，還能降低硬體成本和維護費用。

1.2 效能調優的主要內容

MySQL 效能調優主要包括以下幾個方面：

硬體調優：包括 CPU、記憶體、磁碟 IO 和網路頻寬的配置與最佳化。
作業系統調優：如檔案系統的選擇、網路引數的調整等。
MySQL 配置調優：包括引數配置、快取設定、日誌設定等。
資料庫結構調優：表結構設計、索引最佳化、分割槽表等。
SQL 調優：分析 SQL 執行計劃，最佳化查詢語句。

二、MySQL 效能調優步驟

2.1 硬體調優

CPU：選擇高主頻的 CPU，核數根據併發量和業務需求進行配置。多核處理器可以提升併發處理能力。
記憶體：充足的記憶體可以提高資料庫快取命中率，減少磁碟 IO 操作。通常建議記憶體大小為資料庫資料量的 2-3 倍。
磁碟：使用 SSD 替代傳統 HDD，可以顯著提高 IO 效能。RAID 配置可以提升磁碟的讀寫效能和資料安全性。
網路：在分佈式環境下，網路頻寬和延遲也是影響效能的重要因素。使用高速網路和最佳化網路配置可以減少網路延遲。

2.2 作業系統調優

檔案系統：推薦使用支援事務日誌的檔案系統，如 EXT4、XFS 等。
核心引數：調整作業系統的核心引數，如 vm.swappiness、fs.file-max 等，確保系統資源高效利用。
網路引數：調整網路引數如 tcp_window_scaling、tcp_max_syn_backlog 等，最佳化網路連線和數據傳輸效能。

2.3 MySQL 配置調優

快取設定：調整 innodb_buffer_pool_size、key_buffer_size 等快取引數，保證足夠的快取空間。
連線管理：調整 max_connections、thread_cache_size 等引數，提高併發處理能力。
日誌設定：合理設定慢查詢日誌、錯誤日誌等，方便後期分析和調優。
查詢快取：根據實際情況開啟或關閉查詢快取，調整 query_cache_size 和 query_cache_type。

2.4 MySQL調優案例

1. 配置檔案位置

MySQL 的配置檔案位置因作業系統和安裝方式的不同而有所差異。常見的配置檔案位置包括：

Linux/Unix: /etc/my.cnf 或 /etc/mysql/my.cnf
Windows: C:\Program Files\MySQL\MySQL Server x.x\my.ini

可以透過以下命令找到配置檔案的位置：

mysql --help | grep my.cnf

2. 調整快取設定

快取設定是 MySQL 配置調優的關鍵部分，合理的快取設定可以顯著提高資料庫的效能。

2.1 innodb_buffer_pool_size

innodb_buffer_pool_size 引數決定了 InnoDB 儲存引擎的緩衝池大小，用於快取資料和索引。通常，建議將其設定為實體記憶體的 70%-80%。

# 將 innodb_buffer_pool_size 設定為 4GB。
[mysqld]
innodb_buffer_pool_size = 4G

2.2 key_buffer_size

key_buffer_size 引數決定了 MyISAM 儲存引擎的鍵快取大小。對於使用 MyISAM 的資料庫，適當調整此引數可以提高查詢速度。通常建議設定為實體記憶體的 25%。

# 將 key_buffer_size 設定為 512MB。
[mysqld]
key_buffer_size = 512M

2.3 query_cache_size 和 query_cache_type

查詢快取可以儲存 SELECT 語句的結果，以提高重複查詢的效能。不過，對於高併發的寫操作場景，查詢快取可能會導致效能下降，因此需要根據實際情況選擇是否開啟。

# 將查詢快取大小設定為 128MB，並啟用查詢快取。
[mysqld]
query_cache_size = 128M
query_cache_type = 1

3. 連線管理

連線管理引數決定了 MySQL 伺服器處理客戶端連線的能力。

3.1 max_connections

max_connections 引數決定了 MySQL 可以同時處理的最大連線數。根據實際的併發需求進行設定。

[mysqld]
# 將 max_connections 設定為 500。
max_connections = 500

3.2 thread_cache_size

thread_cache_size 引數決定了執行緒快取的大小，可以減少建立和銷燬執行緒的開銷。

[mysqld]
# 將 thread_cache_size 設定為 50。
thread_cache_size = 50

4. 日誌設定

合理的日誌設定可以幫助追蹤和分析資料庫的效能問題。

4.1 slow_query_log 慢查詢日誌用於記錄執行時間超過指定閾值的查詢語句，便於最佳化慢查詢。

[mysqld]
# 啟用慢查詢日誌，將日誌檔案儲存到 /var/log/mysql-slow.log，記錄執行時間超過 2 秒的查詢。
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2

4.2 log_error 錯誤日誌記錄 MySQL 伺服器執行過程中遇到的錯誤，便於診斷問題。

[mysqld]
# 將錯誤日誌檔案儲存到 /var/log/mysql-error.log。
log_error = /var/log/mysql-error.log

5. InnoDB 特定引數

InnoDB 是 MySQL 預設的儲存引擎，適用於大多數的應用場景。

5.1 innodb_log_file_size innodb_log_file_size 引數決定了 InnoDB 重做日誌檔案的大小。較大的日誌檔案可以提高寫效能，但會延長崩潰恢復時間。

[mysqld]
# 將 innodb_log_file_size 設定為 512MB。
innodb_log_file_size = 512M

5.2 innodb_flush_log_at_trx_commit innodb_flush_log_at_trx_commit 引數控制事務日誌的重新整理頻率。設定為 1 時，每次事務提交都將日誌寫入磁碟，保證資料的一致性。設定為 2 或 0 可以提高效能，但會在崩潰時丟失部分資料。

[mysqld]
# 將 innodb_flush_log_at_trx_commit 設定為 1，以保證資料一致性。
innodb_flush_log_at_trx_commit = 1

調優示例假設我們有一臺 8GB 記憶體的伺服器，執行 InnoDB 儲存引擎，並且資料庫規模較大，日常的併發連線數在 200 左右。我們可以按照以下方式調整配置檔案：

[mysqld]
# 快取設定
innodb_buffer_pool_size = 6G
key_buffer_size = 512M
query_cache_size = 128M
query_cache_type = 1

# 連線管理
max_connections = 300
thread_cache_size = 50

# 日誌設定
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2
log_error = /var/log/mysql-error.log

# InnoDB 特定引數
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1

將以上配置新增到 MySQL 配置檔案 my.cnf 或 my.ini 中，然後重啟 MySQL 服務以使配置生效：

對於 systemd 管理的系統

sudo systemctl restart mysql

對於使用 service 命令管理的系統

sudo service mysql restart

透過這些設定，可以提高 MySQL 的整體效能，減少查詢時間，並且在高併發場景下保持穩定。實際應用中，調優還需要根據具體的使用場景和負載情況，不斷進行監控和調整，以達到最佳效能。

三、SQL 調優步驟

總結到 SQL 最佳化中，主要有以下三點：

最大化利用索引儘可能避免全表掃描減少無效資料的查詢

3.1 SQL 最佳化方法

SQL 最佳化可以透過以下步驟進行：

使用 SHOW STATUS 瞭解各種 SQL 的執行頻率使用慢日誌定位執行效率低的 SQL 使用 EXPLAIN 分析低效 SQL 的執行計劃，進行鍼對性最佳化

3.2 透過 SHOW STATUS 瞭解各種 SQL 的執行頻率

以下是一些常用的 SHOW STATUS 命令，用來了解 MySQL 啟動後的各種 SQL 執行情況：

-- 檢視 MySQL 本次啟動後的執行時間 (單位：秒)
SHOW STATUS LIKE 'uptime';

-- 檢視 SELECT 語句的執行數
SHOW [GLOBAL] STATUS LIKE 'com_select';

-- 檢視 INSERT 語句的執行數
SHOW [GLOBAL] STATUS LIKE 'com_insert';

-- 檢視 UPDATE 語句的執行數
SHOW [GLOBAL] STATUS LIKE 'com_update';

-- 檢視 DELETE 語句的執行數
SHOW [GLOBAL] STATUS LIKE 'com_delete';

-- 檢視嘗試連線到 MySQL 的連線數 (不管是否連線成功)
SHOW STATUS LIKE 'connections';

-- 檢視執行緒快取內的執行緒數量
SHOW STATUS LIKE 'threads_cached';

-- 檢視當前開啟的連線數量
SHOW STATUS LIKE 'threads_connected';

-- 檢視建立用來處理連線的執行緒數。如果 Threads_created 較大，你可能要增加 thread_cache_size 值。
SHOW STATUS LIKE 'threads_created';

-- 檢視活躍的 (非睡眠狀態) 執行緒數
SHOW STATUS LIKE 'threads_running';

-- 檢視立即獲得的表的鎖的次數
SHOW STATUS LIKE 'table_locks_immediate';

-- 檢視不能立即獲得的表的鎖的次數。如果該值較高，並且有效能問題，你應首先最佳化查詢，然後拆分表或使用複製。
SHOW STATUS LIKE 'table_locks_waited';

-- 檢視建立時間超過 slow_launch_time 秒的執行緒數
SHOW STATUS LIKE 'slow_launch_threads';

-- 檢視查詢時間超過 long_query_time 秒的查詢的個數
SHOW STATUS LIKE 'slow_queries';

3.3 EXPLAIN 的使用技巧

EXPLAIN 命令可以顯示 SQL 語句的執行計劃，包括表的訪問順序、使用的索引、掃描的行數等。以下是 EXPLAIN 的一些關鍵欄位和含義：

id：查詢的序列號，表示查詢中執行 SELECT 子句或操作表的順序。
select_type：查詢的型別，如 SIMPLE（簡單查詢）、PRIMARY（主查詢）、SUBQUERY（子查詢）等。
table：顯示查詢涉及的表名。
type：訪問型別，表示 MySQL 在表中找到所需行的方式，如 ALL（全表掃描）、index（索引掃描）、range（範圍掃描）等。
possible_keys：查詢可能使用的索引。
key：查詢實際使用的索引。
rows：掃描的行數，表示最佳化器估計找到所需記錄需要掃描的行數。
Extra：額外的資訊，如 Using where（使用了 WHERE 子句）、Using index（使用了索引）等。

3.4 SQL 最佳化的 15 種方法

避免使用 SELECT *：只查詢需要的欄位。

示例：

-- 避免使用
SELECT * FROM employees WHERE department = 'Sales';

-- 最佳化後
SELECT employee_id, first_name, last_name FROM employees WHERE department = 'Sales';
用 UNION ALL 代替 UNION：UNION 會合並結果集，並去重，UNION ALL 只合並結果集。

用 UNION ALL 代替 UNION：UNION 會合並結果集，並去重，UNION ALL 只合並結果集。示例：

-- 使用 UNION
SELECT employee_id FROM employees WHERE department = 'Sales'
UNION
SELECT employee_id FROM employees WHERE hire_date > '2022-01-01';

-- 最佳化後使用 UNION ALL
SELECT employee_id FROM employees WHERE department = 'Sales'
UNION ALL
SELECT employee_id FROM employees WHERE hire_date > '2022-01-01';

小表驅動大表：在連線查詢中，將小表放在驅動表的位置。示例：

-- 大表驅動小表
SELECT * FROM large_table t1 INNER JOIN small_table t2 ON t1.id = t2.id;

-- 最佳化後小表驅動大表
SELECT * FROM small_table t2 INNER JOIN large_table t1 ON t1.id = t2.id;

批次操作：批次插入、更新、刪除操作，減少 SQL 執行次數。示例：

-- 單條插入
INSERT INTO orders (order_id, customer_id, amount) VALUES (1, 101, 200.00);

-- 最佳化後批次插入
INSERT INTO orders (order_id, customer_id, amount) VALUES
(1, 101, 200.00),
(2, 102, 150.00),
(3, 103, 300.00);

5.多用 LIMIT：限制返回結果的數量，減少數據傳輸量。示例：

-- 未使用 LIMIT
SELECT * FROM customers;

-- 最佳化後使用 LIMIT
SELECT * FROM customers LIMIT 10;

IN 中值太多時的最佳化：避免在 IN 中包含過多的值，可以分批次進行查詢。

示例：

-- IN 中包含太多值
SELECT * FROM products WHERE product_id IN (1, 2, 3, ..., 1000);

-- 最佳化後分批次查詢
SELECT * FROM products WHERE product_id IN (1, 2, 3, ..., 100);
SELECT * FROM products WHERE product_id IN (101, 102, 103, ..., 200);

7.增量查詢：大資料量時，分批次查詢，避免單次查詢時間過長。示例：

-- 單次大資料量查詢
SELECT * FROM orders WHERE order_date > '2022-01-01';

-- 最佳化後增量查詢
SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-01-10';
SELECT * FROM orders WHERE order_date BETWEEN '2022-01-11' AND '2022-01-20';

8.高效的分頁：使用索引和 LIMIT 進行分頁查詢，避免全表掃描。示例：

-- 使用 OFFSET 分頁
SELECT * FROM products ORDER BY product_id LIMIT 100 OFFSET 1000;

-- 最佳化後使用索引分頁
SELECT * FROM products WHERE product_id > 1000 ORDER BY product_id LIMIT 100;

9.用連線查詢代替子查詢：連線查詢通常比子查詢效率高。示例：

-- 使用子查詢
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

-- 最佳化後使用連線查詢
SELECT e.* FROM employees e INNER JOIN departments d ON e.department_id = d.department_id WHERE d.location = 'New York';

10.JOIN 的表不宜過多：連線的表過多會導致查詢複雜度增加，效能下降。示例：

-- 過多表連線
SELECT * FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id JOIN table3 t3 ON t2.id = t3.id JOIN table4 t4 ON t3.id = t4.id;

-- 最佳化後減少表連線
SELECT * FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id WHERE t1.some_column = 'value';

11.JOIN 時要注意索引：確保連線欄位上有索引。示例：

-- 未加索引的 JOIN
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

-- 最佳化後加索引的 JOIN
ALTER TABLE orders ADD INDEX (customer_id);
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

12.控制索引的數量：索引太多會影響寫操作的效能，合理選擇索引。示例：

-- 未最佳化的索引數量
CREATE INDEX idx1 ON employees(first_name);
CREATE INDEX idx2 ON employees(last_name);
CREATE INDEX idx3 ON employees(department_id);

-- 最佳化後控制索引數量
CREATE INDEX idx_full_name ON employees(first_name, last_name);

13.選擇合理的欄位型別：根據資料型別選擇合適的欄位型別，節省儲存空間，提高查詢效率。示例：

-- 未最佳化的欄位型別
CREATE TABLE orders (
    order_id VARCHAR(255),
    amount VARCHAR(255)
);

-- 最佳化後選擇合理的欄位型別
CREATE TABLE orders (
    order_id INT,
    amount DECIMAL(10, 2)
);

14.提升 GROUP BY 的效率：使用索引欄位進行分組，提高查詢效率。示例：

-- 未使用索引的 GROUP BY
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

-- 最佳化後使用索引欄位的 GROUP BY
CREATE INDEX idx_department_id ON employees(department_id);
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

15.索引最佳化：根據查詢頻率和表的資料量，最佳化索引策略。示例：

-- 未最佳化的索引策略
CREATE INDEX idx_name ON employees(name);

-- 最佳化後索引策略
CREATE INDEX idx_name_department ON employees(name, department_id);

透過這些方法，可以有效地進行 SQL 最佳化，提高資料庫的整體效能和響應速度。實際應用中，還需要根據具體的使用場景和負載情況，不斷進行監控和調整，以達到最佳效能。

四、總結

MySQL 效能調優和 SQL 調優是確保資料庫高效執行的關鍵步驟。透過硬體調優、作業系統調優、MySQL 配置調優和 SQL 調優，可以顯著提高資料庫的效能，減少資源消耗。在實際應用中，需要根據具體的業務需求和環境，不斷調整和最佳化，保證系統的穩定性和高效性。透過 EXPLAIN 等工具，深入分析查詢執行計劃，有針對性地進行最佳化，是實現高效能資料庫的重要方法。

MySQL 效能調優的三個方向