MySQL超全優(yōu)化清單執(zhí)行指南

作者：一安 2024-09-13 09:38:43

使用合適的數(shù)據(jù)類型可以減少存儲(chǔ)空間和提高查詢速度。這個(gè)可不能小看，數(shù)據(jù)量到達(dá)一個(gè)量級(jí)，這個(gè)就能看出明顯差異。

一般語(yǔ)句優(yōu)化

先從一般的語(yǔ)句優(yōu)化開始，其實(shí)對(duì)于很多規(guī)范大家并不陌生，可就是在用的時(shí)候，無(wú)法遵從，希望今天大家再過(guò)一遍，可以養(yǎng)成一種良好的數(shù)據(jù)庫(kù)編碼習(xí)慣。

選擇合適的數(shù)據(jù)類型及字符集

例子：對(duì)于布爾值使用 TINYINT(1) 而不是 CHAR(1) 比如你有一個(gè)字段是表示業(yè)務(wù)狀態(tài)或者是類型。

CREATE TABLE users (
    is_active TINYINT(1)
);

對(duì)于僅存儲(chǔ)英文的表，使用 latin1 而不是 utf8mb4。

CREATE TABLE messages (
    content VARCHAR(255) CHARACTER SET latin1
);

避免使用SELECT *

僅選擇必要的列，減少數(shù)據(jù)傳輸量。

例子：避免 SELECT *，改用具體列名。

SELECT id, name, email FROM users;

合理使用JOIN、避免子查詢

避免過(guò)多的 JOIN 操作，盡量減少數(shù)據(jù)集的大小。

例子：優(yōu)化連接條件，確保連接列上有索引。

SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

盡量使用 JOIN 或者 EXISTS 代替子查詢。

例子：避免使用子查詢，改用 JOIN。

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

使用UNION代替OR、優(yōu)化ORDER BY和GROUP BY

確保 ORDER BY 和 GROUP BY 的列上有索引。

例子：在排序和分組列上添加索引。

CREATE INDEX idx_order_date ON orders (order_date);
SELECT * FROM orders ORDER BY order_date;

在業(yè)務(wù)允許的情況下，使用 UNION 代替 OR 條件。

例子：用兩個(gè)查詢的 UNION 代替一個(gè)帶 OR 的查詢。

SELECT id, name FROM users WHERE status = 'active'
UNION
SELECT id, name FROM users WHERE status = 'pending';

避免使用%開頭的LIKE查詢

避免使用 % 開頭的 LIKE 查詢，因?yàn)椴荒苁褂盟饕?/p>

例子：使用全文本搜索代替 LIKE '%keyword%'。也就是讓%在最后面

SELECT * FROM products WHERE description LIKE 'keyword%';

這個(gè)尤其重要，相信各位在各大平臺(tái)網(wǎng)站上。很多搜索只有輸入前面的字才能有結(jié)果，你輸入中間的字，會(huì)查詢不到，其實(shí)就是這個(gè)原理。

使用批量插入、優(yōu)化INSERT操作

使用批量插入減少插入操作的開銷。

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), 
('Bob', 'bob@example.com');

在批量插入時(shí)，關(guān)閉唯一性檢查和索引更新，插入完成后再開啟（此種情況大家可根據(jù)業(yè)務(wù)來(lái)，比如當(dāng)查詢很頻繁的時(shí)候，這樣操作會(huì)影響查詢效率）。

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

-- 批量插入操作

SET unique_checks=1;
SET foreign_key_checks=1;
COMMIT;

使用查詢緩存

使用查詢緩存，減少重復(fù)查詢的開銷。

SET GLOBAL query_cache_size = 1048576;
SET GLOBAL query_cache_type = ON;

避免使用HAVING代替WHERE

在可能的情況下，使用 WHERE 代替 HAVING 進(jìn)行過(guò)濾。

例子：避免使用 HAVING 過(guò)濾。

SELECT user_id, COUNT(*) FROM orders
WHERE order_date > '2020-01-01'
GROUP BY user_id
HAVING COUNT(*) > 1;

配置參數(shù)調(diào)優(yōu)

該部分主要針對(duì)Mysql的配置做一些操作，這塊還是相當(dāng)重要的，雖然是運(yùn)維領(lǐng)域，但熟悉Mysql的配置是我們研發(fā)的不可不會(huì)的領(lǐng)域。

調(diào)整innodb_buffer_pool_size

innodb_buffer_pool_size 是 InnoDB 存儲(chǔ)引擎最重要的配置參數(shù)之一，用于指定 InnoDB 緩沖池的大小。緩沖池用于緩存數(shù)據(jù)頁(yè)、索引頁(yè)和 InnoDB 表的其它信息。合理設(shè)置這個(gè)參數(shù)對(duì)數(shù)據(jù)庫(kù)性能有很大影響。

增大 InnoDB 緩沖池大小，提高緩存命中率。

SET GLOBAL innodb_buffer_pool_size = 2G;

但是這里要注意該值并不是越大越好。innodb_buffer_pool_size 應(yīng)該設(shè)置要盡可能大，但要確保為操作系統(tǒng)和其他應(yīng)用程序留出足夠的內(nèi)存。

一般建議在數(shù)據(jù)庫(kù)專用服務(wù)器上設(shè)置為物理內(nèi)存的 60% 到 80%。通過(guò)監(jiān)控?cái)?shù)據(jù)庫(kù)性能和內(nèi)存使用情況，可以進(jìn)一步調(diào)整這個(gè)參數(shù)以優(yōu)化數(shù)據(jù)庫(kù)性能。

調(diào)整query_cache_size

query_cache_size 是用于指定查詢緩存的大小。查詢緩存可以緩存 SELECT 查詢的結(jié)果，避免重復(fù)執(zhí)行相同的查詢，從而提高性能。

然而，在 MySQL 8.0 及更高版本中，查詢緩存已經(jīng)被完全移除。如果你使用的是 MySQL 8.0 及以上版本，可以忽略 query_cache_size 參數(shù)。

調(diào)整thread_cache_size

增大線程緩存大小，減少線程創(chuàng)建開銷。

SET GLOBAL thread_cache_size = 100;

調(diào)整table_open_cache

增大表緩存大小，減少表打開的開銷。

SET GLOBAL table_open_cache = 4000;

調(diào)整tmp_table_size和max_heap_table_size

增大臨時(shí)表和堆表的最大大小，減少磁盤 I/O。

SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;

調(diào)整innodb_flush_log_at_trx_commit

根據(jù)需求調(diào)整日志刷新策略，權(quán)衡性能和數(shù)據(jù)安全性。

SET GLOBAL innodb_flush_log_at_trx_commit = 2;

調(diào)整innodb_log_file_size

增大日志文件大小，減少日志文件切換的開銷。

SET GLOBAL innodb_log_file_size = 256M;

調(diào)整innodb_log_buffer_size

增大日志緩沖區(qū)大小，提高寫入性能。

SET GLOBAL innodb_log_buffer_size = 16M;

調(diào)整innodb_io_capacity

根據(jù)磁盤 I/O 性能調(diào)整 InnoDB I/O 容量。

SET GLOBAL innodb_io_capacity = 2000;

調(diào)整max_connections

增大最大連接數(shù)，支持更多并發(fā)連接。

SET GLOBAL max_connections = 500;

調(diào)整sort_buffer_size

增大排序緩沖區(qū)大小，提高排序操作的性能。

SET GLOBAL sort_buffer_size = 4M;

調(diào)整read_buffer_size

增大讀緩沖區(qū)大小，提高順序掃描性能。

SET GLOBAL read_buffer_size = 2M;

正確使用索引

這塊是最重要的，因?yàn)榧偃缡褂貌划?dāng)，那么創(chuàng)建索引不但沒(méi)有效果，反而還會(huì)成為負(fù)擔(dān)。

在常用查詢條件和連接條件的列上建立索引

這塊很清楚，反正只要發(fā)現(xiàn)查詢較慢，優(yōu)先檢查where條件后面，有沒(méi)有被創(chuàng)建索引。

遵循最左前綴原則

這個(gè)是針對(duì)復(fù)合索引時(shí)的要求，遵循最左前綴原則。

例子：對(duì)于索引 (a, b, c)，可以用于 (a)，(a, b)，(a, b, c) 的查詢。

CREATE INDEX idx_abc ON table_name (a, b, c);
SELECT * FROM table_name WHERE a = 1 AND b = 2;

避免在索引列上進(jìn)行計(jì)算

例子：避免 WHERE YEAR(date) = 2020，改用范圍查詢。

SELECT * FROM orders WHERE date BETWEEN '2024-06-01' AND '2024-06-30';

避免重復(fù)索引

檢查并刪除重復(fù)的索引，減少維護(hù)開銷。了解mysql底層的都知道，創(chuàng)建索引，就會(huì)增加一個(gè)頁(yè)，重復(fù)索引無(wú)疑是給增加負(fù)擔(dān)。

更新頻繁的列慎用索引

對(duì)于更新頻繁的列，索引會(huì)增加寫操作的開銷，需要慎重使用。

CREATE INDEX idx_update_col ON table_name (update_col);  
-- 如果 update_col 更新頻繁，需慎用

避免過(guò)多的列使用復(fù)合索引

復(fù)合索引的列數(shù)不要太多，列數(shù)過(guò)多會(huì)增加索引的維護(hù)開銷，并且可能導(dǎo)致索引文件過(guò)大。對(duì)此可以拆分為較少?gòu)?fù)合索引和單個(gè)索引

CREATE INDEX idx_columns ON table_name (col1, col2, col3, col4, col5);  
-- 列數(shù)太多

使用覆蓋索引

這個(gè)什么意思呢，如果查詢的所有列都在索引中，那么可以避免回表，提高性能。

CREATE INDEX idx_covering ON orders (order_id, order_date, customer_id);

-- 查詢只涉及索引中的列
SELECT order_id, order_date, customer_id FROM orders WHERE customer_id = 123;

其他避坑

避免使用SELECT DISTINCT

在沒(méi)有必要的情況下避免使用 SELECT DISTINCT，因?yàn)樗鼤?huì)導(dǎo)致額外的排序操作，增加查詢的開銷。

-- 如果可以確定結(jié)果集不會(huì)有重復(fù)值，避免使用 DISTINCT
SELECT DISTINCT name FROM users WHERE status = 'active';

使用LIMIT 1優(yōu)化查詢

在只需要一條結(jié)果的查詢中使用 LIMIT 1 可以提高性能。

SELECT * FROM users WHERE email = 'user@example.com' LIMIT 1;

合理使用HAVING

在可能的情況下，使用 WHERE 代替 HAVING 進(jìn)行過(guò)濾，因?yàn)?HAVING 是在聚合之后進(jìn)行過(guò)濾，性能較差。

SELECT user_id, COUNT(*) FROM orders
WHERE order_date > '2020-01-01'
GROUP BY user_id
HAVING COUNT(*) > 1;

-- 改為使用 WHERE
SELECT user_id, COUNT(*) AS order_count FROM orders
WHERE order_date > '2020-01-01'
GROUP BY user_id
WHERE order_count > 1;

避免在WHERE子句中使用函數(shù)

避免在 WHERE 子句中使用函數(shù)，因?yàn)闀?huì)導(dǎo)致索引失效（這個(gè)剛才講索引的時(shí)候提到了）。

-- 避免
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 改為
SELECT * FROM users WHERE created_at BETWEEN '2024-06-01' AND '2024-06-01';

合理使用UNION ALL

在可能的情況下，使用 UNION ALL 代替 UNION，因?yàn)?nbsp;UNION 會(huì)去重，增加開銷。

SELECT name FROM employees WHERE department = 'Sales'
UNION ALL
SELECT name FROM contractors WHERE department = 'Sales';

避免在索引列上使用IS NULL或IS NOT NULL

盡量避免在索引列上使用 IS NULL 或 IS NOT NULL，因?yàn)橛行┐鎯?chǔ)引擎對(duì)這類查詢不使用索引。

-- 避免
SELECT * FROM users WHERE email IS NULL;

-- 如果業(yè)務(wù)允許，考慮使用默認(rèn)值替代 NULL
SELECT * FROM users WHERE email = '';

避免使用負(fù)條件

避免使用 NOT IN、!=、<> 等負(fù)條件，因?yàn)檫@些條件不能有效使用索引。

-- 避免
SELECT * FROM orders WHERE status != 'completed';

-- 改為使用正條件
SELECT * FROM orders WHERE status IN ('pending', 'processing');

合理使用分頁(yè)

在大數(shù)據(jù)集分頁(yè)時(shí)，避免使用 OFFSET 大量偏移，而是使用更高效的方式，如基于唯一鍵的范圍查詢。

-- 避免
SELECT * FROM orders ORDER BY order_id LIMIT 1000000, 10;

-- 改為使用范圍查詢
SELECT * FROM orders WHERE order_id > (SELECT order_id FROM orders ORDER BY order_id LIMIT 999999, 1) LIMIT 10;

使用適當(dāng)?shù)逆i

在需要鎖定的情況下，合理選擇鎖的類型（行鎖、表鎖）以避免性能問(wèn)題和死鎖（死鎖、行鎖、表鎖等問(wèn)題會(huì)開專欄討論，今天不討論）。

-- 行級(jí)鎖
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;

-- 表級(jí)鎖
LOCK TABLES orders WRITE;

冷熱數(shù)據(jù)備份

這個(gè)什么意思呢，簡(jiǎn)單來(lái)講，什么是目前業(yè)務(wù)經(jīng)常需要的數(shù)據(jù)，比如5、8年前的數(shù)據(jù) 是否業(yè)務(wù)不再進(jìn)行訪問(wèn)，或者對(duì)數(shù)據(jù)按照（時(shí)間、某一業(yè)務(wù)）維度拆分，把數(shù)據(jù)一拆為多，減輕當(dāng)表的壓力。總之一個(gè)原則，訪問(wèn)5千萬(wàn)的數(shù)據(jù)量要比訪問(wèn)5百萬(wàn)的數(shù)據(jù)速度要慢很多。那就拆。

注意：這個(gè)和分庫(kù)分表還不是一個(gè)概念，這個(gè)是把冷數(shù)據(jù)給清理出去，把最新的熱數(shù)據(jù)放進(jìn)來(lái)。

詳解Explain

最后說(shuō)一下這個(gè)，應(yīng)該有有一部分人，對(duì)這個(gè)還不是很熟悉。

當(dāng)一條查詢語(yǔ)句在經(jīng)過(guò)MySQL查詢優(yōu)化器的各種基于成本和規(guī)則的優(yōu)化會(huì)后生成一個(gè)所謂的執(zhí)行計(jì)劃，這個(gè)執(zhí)行計(jì)劃展示了接下來(lái)具體執(zhí)行查詢的方式，比如多表連接的順序是什么，對(duì)于每個(gè)表采用什么訪問(wèn)方法來(lái)具體執(zhí)行查詢等等。設(shè)計(jì)MySQL的大叔貼心的為我們提供了EXPLAIN語(yǔ)句來(lái)幫助我們查看某個(gè)查詢語(yǔ)句的具體執(zhí)行計(jì)劃。

我們只用這個(gè)為我們服務(wù)一個(gè)點(diǎn)，那就是看有沒(méi)有走索引，比如你加上索引了可是沒(méi)有效果，那就看看執(zhí)行計(jì)劃，把你的sql執(zhí)行前面加一個(gè)Explain。

編寫查詢語(yǔ)句

首先，編寫你想要優(yōu)化的查詢語(yǔ)句。例如：

SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

使用 EXPLAIN

在查詢語(yǔ)句前加上 EXPLAIN 關(guān)鍵字：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

執(zhí)行上述 EXPLAIN 語(yǔ)句，查看輸出結(jié)果。MySQL 會(huì)返回一個(gè)包含查詢執(zhí)行計(jì)劃的表格（例如下圖）。

+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | d     | const  | PRIMARY       | PRIMARY | 4       | const             |    1 |                          |
|  1 | SIMPLE      | e     | ref    | department_id | department_id | 4 | const             |   10 | Using where              |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+

具體解釋：

EXPLAIN 輸出表格包含多個(gè)列，每列提供不同的查詢計(jì)劃信息。常見(jiàn)列包括：

id：查詢的標(biāo)識(shí)符，表示查詢的執(zhí)行順序。
select_type：查詢類型，如 SIMPLE（簡(jiǎn)單查詢），PRIMARY（主查詢），UNION（聯(lián)合查詢的一部分），SUBQUERY（子查詢）。
table：查詢涉及的表。
type：連接類型，表示MySQL如何查找行。常見(jiàn)類型按效率從高到低排列為：

system：表只有一行（常見(jiàn)于系統(tǒng)表）。

const：表最多有一個(gè)匹配行（索引為主鍵或唯一索引）。

eq_ref：對(duì)于每個(gè)來(lái)自前一個(gè)表的行，表中最多有一個(gè)匹配行。

ref：對(duì)于每個(gè)來(lái)自前一個(gè)表的行，表中可能有多個(gè)匹配行。

range：使用索引查找給定范圍的行。

index：全表掃描索引。

ALL：全表掃描。

possible_keys：查詢中可能使用的索引。
key：實(shí)際使用的索引。
key_len：使用的索引鍵長(zhǎng)度。
ref：使用的列或常量，與索引比較。
rows： MySQL 估計(jì)的要讀取的行數(shù)。
filtered：經(jīng)過(guò)表?xiàng)l件過(guò)濾后的行百分比。
Extra：額外的信息，如 Using index（覆蓋索引），Using where（使用 WHERE 子句過(guò)濾），Using filesort（文件排序），Using temporary（使用臨時(shí)表）。

優(yōu)化查詢路徑

根據(jù) EXPLAIN 輸出，采取以下措施優(yōu)化查詢路徑：

確保使用索引

如果 type 列顯示為 ALL 或 index，說(shuō)明表進(jìn)行了全表掃描。可以通過(guò)創(chuàng)建適當(dāng)?shù)乃饕齺?lái)優(yōu)化查詢。例如：

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

優(yōu)化查詢條件

避免在索引列上使用函數(shù)或進(jìn)行計(jì)算。改寫查詢條件以利用索引。例如：

-- 避免
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- 改為
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

使用覆蓋索引

如果查詢只涉及索引中的列，可以避免回表，提高性能。例如：

CREATE INDEX idx_covering ON orders (customer_id, order_date, order_id);

-- 查詢只涉及索引中的列
SELECT customer_id, order_date, order_id FROM orders WHERE customer_id = 123;

分解復(fù)雜查詢

將復(fù)雜查詢分解為多個(gè)簡(jiǎn)單查詢，可以提高性能。例如：

-- 復(fù)雜查詢
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.name = 'John Doe';

-- 分解為兩個(gè)簡(jiǎn)單查詢
SELECT id FROM customers WHERE name = 'John Doe';
-- 假設(shè)查詢結(jié)果為 123
SELECT * FROM orders WHERE customer_id = 123;

實(shí)際示例

假設(shè)有一個(gè) employees 表和一個(gè) departments 表：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    INDEX (department_id),
    INDEX (hire_date)
);

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

查詢所有在某個(gè)日期后加入某部門的員工：

EXPLAIN
SELECT e.id, e.first_name, e.last_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Sales' AND e.hire_date > '2023-01-01';

示例 EXPLAIN 輸出：

+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | d     | const  | PRIMARY       | PRIMARY | 4       | const             |    1 |                          |
|  1 | SIMPLE      | e     | ref    | department_id | department_id | 4 | const             |   10 | Using where              |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+

從 EXPLAIN 輸出可以看出：

d 表使用了 PRIMARY 索引，類型為 const，表示是一個(gè)常量查找。
e 表使用了 department_id 索引，類型為 ref，表示引用查找。

進(jìn)一步優(yōu)化：

如果查詢頻繁，可以為 departments.name 創(chuàng)建索引。
確保 hire_date 上有索引。

優(yōu)化后的索引創(chuàng)建：

CREATE INDEX idx_department_name ON departments (name);

再次執(zhí)行 EXPLAIN：

EXPLAIN
SELECT e.id, e.first_name, e.last_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Sales' AND e.hire_date > '2023-01-01';

優(yōu)化后的輸出可能顯示更好的執(zhí)行計(jì)劃，減少查詢時(shí)間。