您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 效能優化八大妙招：字段屬性、JOIN、UNION、事務、索引、分頁、配置、監控

編輯：MySQL 資料庫相關知識

前言

MySQL 優化無銀彈，但有「八大妙招」覆蓋 90% 場景。本文從字段設計、查詢重寫、索引策略、事務控制、分頁優化、配置調優、架構分層、監控閉環八個維度給出可落地的優化清單。

妙招一：選取最適用的字段屬性 (「越小越好、定長優變長、枚舉慎用」)

整數：TINYINT(1) 存布爾、SMALLINT 存狀態碼、INT 存 ID、BIGINT 存雪花 ID。
字符串：固定長度 (MD5、UUID、國標代碼) 用 CHAR；變長用 VARCHAR，**長度剛好夠用** (如手機號 VARCHAR(11))。
枚舉/集合：ENUM 內部存數值，比 VARCHAR 快，但修改結構需鎖表重建，擴展性差；建議改用 TINYINT + 查找表 或 CHECK 约束 (8.0.16+)。
NOT NULL：默認加 NOT NULL，避免 NULL 比較開銷、統計函數陷阱、索引失效風險。
時間：TIMESTAMP (4 字節、時區感知、2038 年問題) vs DATETIME (8 字節、無時區、範圍大)；5.6.4+ 支援微秒精度。

妙招二：用 JOIN 替換子查詢 (Subquery → Join)

-- 子查詢 (MySQL 5.6+ 優化器可轉換，但不穩定)
SELECT * FROM customer WHERE id NOT IN (SELECT customer_id FROM orders);

-- LEFT JOIN + IS NULL (確定性高、可用索引)
SELECT c.* FROM customer c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

原理：JOIN 避免臨時表創建，直接利用索引關聯；子查詢在舊版本易產生 DEPENDENT SUBQUERY 全表掃描。

妙招三：用 UNION 替換手動臨時表

-- 多源彙總：手動建臨時表 INSERT → 查詢 → DROP
-- UNION 一條龍 (4.0+ 支援)
SELECT name, phone FROM client
UNION ALL
SELECT name, birth_date FROM author
UNION ALL
SELECT name, supplier FROM product;

注意：UNION 去重 (排序開銷大)；UNION ALL 不去重，**無特殊去重需求必用 UNION ALL**。所有 SELECT 列數、類型須一致。

妙招四：事務保證數據一致性 (ACID 落地)

BEGIN;
INSERT INTO orders (...) VALUES (...);
SET @order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, ...) VALUES (@order_id, ...);
UPDATE products SET stock = stock - 1 WHERE id = ...;
DELETE FROM cart WHERE user_id = ...;
COMMIT;  -- 或 ROLLBACK

原則：事務要短 (避免長鎖)、要快 (少查詢)、要少 (少表)、死鎖捕獲重試 (應用層或 ProxySQL)。

妙招五：索引策略 (覆蓋、最左、區分度、避免回表)

覆蓋索引：SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b)，Using index 避免回表。
最左前綴：索引 (a,b,c) 支援 a / a,b / a,b,c，不支援 b / c / b,c。
區分度：COUNT(DISTINCT col)/COUNT(*) > 0.1 才值得建索引；性別、狀態、是否刪除區分度極低，**單列索引無效**，需組合索引。
避免索引失效：函數操作、隱式轉換、LIKE '%xxx'、OR 跨欄位無組合索引、!=/<>、IS NULL/IS NOT NULL。

妙招六：分頁優化 (深分頁殺手鐧)

-- 深分頁 LIMIT 1000000, 20 掃描 100 萬行再丟棄
-- 延遲關聯 (覆蓋索引定位 ID 再回表)
SELECT t.* FROM `table` t
JOIN (SELECT id FROM `table` WHERE cond ORDER BY id LIMIT 1000000, 20) tmp USING (id);

-- 游標式 / Keyset Pagination (無總頁數、適合無限滾動)
SELECT * FROM `table` WHERE id > 1000000 ORDER BY id LIMIT 20;

妙招七：核心配置參數基線 (InnoDB 專用)

innodb_buffer_pool_size = 0.7 * RAM
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 20000 (NVMe)
max_connections = 3000
table_open_cache = 10000
sort_buffer_size = 4M
join_buffer_size = 4M
read_rnd_buffer_size = 8M