MySQL 是面試中必問的模塊，而 MySQL 中的優化內容又是常見的面試題，所以本文來看“工作中優化MySQL的手段有哪些？”。

圖片

工作中常見的 MySQL 優化手段分為以下五大類：

• 索引優化：確保高頻查詢字段有合適索引。
• SQ優化：減少全表掃描、避免不必要計算。
• 事務與鎖優化：避免長事務、使用批量插入。
• 架構優化：數據量大時進行讀寫分離或分庫分表。
• 硬件和配置優化：升級硬件和 MySQL 參數調優。

1.索引優化

索引優化包含以下內容：

• 高頻字段需要創建索引：對于讀多少寫的場景，一定要創建正確的索引，避免全表掃描，提升查詢效率。
• 避免索引失效：在有索引的前提下，確保索引不會失效，因此需要避免一些導致索引失效的場景，例如以下這些：

1. 聯合索引未遵循最左匹配原則。
2. 使用列運行或內置函數導致索引失效。
3. like 查詢未非前綴模糊查詢。
4. 隱私類型轉換等。

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

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

2.SQL優化

2.1 避免 SELECT *

只查詢需要的字段，減少數據傳輸和內存占用：

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

2.2 分頁優化

大數據量分頁時，避免 LIMIT 100000, 10，而是使用上次查詢 ID 作為起始 ID 進行查詢：

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

2.3 JOIN 優化

• 確保關聯字段有索引，使用小表驅動大表。

圖片

例如以下示例：

-- 小表（emp）驅動大表（dept）
SELECT * FROM emp 
INNER JOIN dept ON emp.dept_id = dept.id;

3. 事務和鎖優化

3.1 減少長事務

長事務會導致鎖競爭和回滾段膨脹：

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

3.2 批量操作優化

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

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

4.架構優化

數據量比較大時，可采取以下措施：

• 讀寫分離：采用數據庫的讀寫分離架構，將讀操作和寫操作分布到不同的數據庫服務器上。這樣可以減輕主數據庫的負載，提高讀操作的性能。在查詢每個年齡段的用戶時，可以將查詢請求發送到從數據庫上，從而提高查詢的并發處理能力和響應速度。

圖片

• 分庫分表：單表行數超過 500 萬行或者單表容量超過 2GB，推薦進行分庫分表。

圖片

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

5.硬件和配置優化

5.1 數據庫配置優化

• 調整緩存參數：增大 innodb_buffer_pool_size（建議分配 50%-80% 可用內存）以提高讀取性能。
• 優化日志參數：增大 innodb_log_file_size 和 innodb_log_buffer_size，減少磁盤寫入。

5.2 提升硬件配置

• 使用 SSD 硬盤：提升 IO 操作性能。
• 增加內存容量：足夠的內存可以減少磁盤 I/O 操作。
• 選擇多核處理器：多核 CPU 可以更好地支持高并發查詢，提升系統的整體吞吐量。
• 升級網絡設備：使用高速網絡接口卡可以提高數據傳輸速度。

小結

MySQL 常見的優化手段包含 5 大類，索引優化、SQL 優化、事務和鎖優化、架構優化和硬件及配置優化。你還知道哪些優化手段呢？