MySQL 是面試中必問(wèn)的模塊，而 MySQL 中的優(yōu)化內(nèi)容又是常見(jiàn)的面試題，所以本文來(lái)看“工作中優(yōu)化MySQL的手段有哪些？”。

圖片

工作中常見(jiàn)的 MySQL 優(yōu)化手段分為以下五大類：

• 索引優(yōu)化：確保高頻查詢字段有合適索引。
• SQ優(yōu)化：減少全表掃描、避免不必要計(jì)算。
• 事務(wù)與鎖優(yōu)化：避免長(zhǎng)事務(wù)、使用批量插入。
• 架構(gòu)優(yōu)化：數(shù)據(jù)量大時(shí)進(jìn)行讀寫分離或分庫(kù)分表。
• 硬件和配置優(yōu)化：升級(jí)硬件和 MySQL 參數(shù)調(diào)優(yōu)。

1.索引優(yōu)化

索引優(yōu)化包含以下內(nèi)容：

• 高頻字段需要?jiǎng)?chuàng)建索引：對(duì)于讀多少寫的場(chǎng)景，一定要?jiǎng)?chuàng)建正確的索引，避免全表掃描，提升查詢效率。
• 避免索引失效：在有索引的前提下，確保索引不會(huì)失效，因此需要避免一些導(dǎo)致索引失效的場(chǎng)景，例如以下這些：

1. 聯(lián)合索引未遵循最左匹配原則。
2. 使用列運(yùn)行或內(nèi)置函數(shù)導(dǎo)致索引失效。
3. like 查詢未非前綴模糊查詢。
4. 隱私類型轉(zhuǎn)換等。

• 避免回表查詢：如果查詢只需要索引字段，避免回表，例如以下示例：

-- 原始查詢（需回表）
SELECT * FROM orders WHERE user_id = 100;
-- 優(yōu)化為覆蓋索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
SELECT user_id, status FROM orders WHERE user_id = 100;

2.SQL優(yōu)化

2.1 避免 SELECT *

只查詢需要的字段，減少數(shù)據(jù)傳輸和內(nèi)存占用：

-- 不推薦
SELECT * FROM products;
-- 推薦
SELECT id, name, price FROM products;

2.2 分頁(yè)優(yōu)化

大數(shù)據(jù)量分頁(yè)時(shí)，避免 LIMIT 100000, 10，而是使用上次查詢 ID 作為起始 ID 進(jìn)行查詢：

-- 原始分頁(yè)（性能差）
SELECT * FROM logs ORDER BY id LIMIT 100000, 10;
-- 優(yōu)化：使用游標(biāo)分頁(yè)（記錄上一頁(yè)最后一條的 id）
SELECT * FROM logs WHERE id>100000 ORDER BY id LIMIT 10;

2.3 JOIN 優(yōu)化

• 確保關(guān)聯(lián)字段有索引，使用小表驅(qū)動(dòng)大表。

圖片

例如以下示例：

-- 小表（emp）驅(qū)動(dòng)大表（dept）
SELECT * FROM emp 
INNER JOIN dept ON emp.dept_id = dept.id;

3. 事務(wù)和鎖優(yōu)化

3.1 減少長(zhǎng)事務(wù)

長(zhǎng)事務(wù)會(huì)導(dǎo)致鎖競(jìng)爭(zhēng)和回滾段膨脹：

-- 不推薦：事務(wù)中包含耗時(shí)操作
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 執(zhí)行其他耗時(shí)操作...
COMMIT;
-- 推薦：盡快提交事務(wù)

3.2 批量操作優(yōu)化

使用批量插入代替逐條插入：

-- 不推薦
INSERT INTO logs (msg) VALUES ('a');
INSERT INTO logs (msg) VALUES ('b');
-- 推薦
INSERT INTO logs (msg) VALUES ('a'), ('b');

4.架構(gòu)優(yōu)化

數(shù)據(jù)量比較大時(shí)，可采取以下措施：

• 讀寫分離：采用數(shù)據(jù)庫(kù)的讀寫分離架構(gòu)，將讀操作和寫操作分布到不同的數(shù)據(jù)庫(kù)服務(wù)器上。這樣可以減輕主數(shù)據(jù)庫(kù)的負(fù)載，提高讀操作的性能。在查詢每個(gè)年齡段的用戶時(shí)，可以將查詢請(qǐng)求發(fā)送到從數(shù)據(jù)庫(kù)上，從而提高查詢的并發(fā)處理能力和響應(yīng)速度。

圖片

• 分庫(kù)分表：?jiǎn)伪硇袛?shù)超過(guò) 500 萬(wàn)行或者單表容量超過(guò) 2GB，推薦進(jìn)行分庫(kù)分表。

圖片

• 分布式數(shù)據(jù)庫(kù)：使用大數(shù)據(jù)下性能更好的分布式數(shù)據(jù)庫(kù)，例如 TiDB、ElasticSearch 等分布式數(shù)據(jù)提升性能。

5.硬件和配置優(yōu)化

5.1 數(shù)據(jù)庫(kù)配置優(yōu)化

• 調(diào)整緩存參數(shù)：增大 innodb_buffer_pool_size（建議分配 50%-80% 可用內(nèi)存）以提高讀取性能。
• 優(yōu)化日志參數(shù)：增大 innodb_log_file_size 和 innodb_log_buffer_size，減少磁盤寫入。

5.2 提升硬件配置

• 使用 SSD 硬盤：提升 IO 操作性能。
• 增加內(nèi)存容量：足夠的內(nèi)存可以減少磁盤 I/O 操作。
• 選擇多核處理器：多核 CPU 可以更好地支持高并發(fā)查詢，提升系統(tǒng)的整體吞吐量。
• 升級(jí)網(wǎng)絡(luò)設(shè)備：使用高速網(wǎng)絡(luò)接口卡可以提高數(shù)據(jù)傳輸速度。

小結(jié)

MySQL 常見(jiàn)的優(yōu)化手段包含 5 大類，索引優(yōu)化、SQL 優(yōu)化、事務(wù)和鎖優(yōu)化、架構(gòu)優(yōu)化和硬件及配置優(yōu)化。你還知道哪些優(yōu)化手段呢？