MySQL 性能優化是系統工程,需自上而下:
-- ❌ 郵編用 VARCHAR(255) 或 CHAR(255)
-- ✅ 郵編固定 6 位數字
`postal_code` CHAR(6) NOT NULL DEFAULT '' COMMENT '郵編'
-- ❌ 狀態用 VARCHAR(20) 存 'active'/'inactive'
-- ✅ 狀態用 TINYINT/UNSIGNED + 字典表/ENUM
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=啟用 0=禁用'
-- ❌ 金額用 FLOAT/DOUBLE (精度丟失)
-- ✅ 金額用 DECIMAL(12,2) 或 BIGINT 存分
`amount` BIGINT NOT NULL DEFAULT 0 COMMENT '金額(分)'
-- IP 地址
`ip` INT UNSIGNED NOT NULL DEFAULT 0 -- INET_ATON()/INET_NTOA() 轉換
NOT NULL,NULL 會佔用額外位圖、導致統計信息不準、復雜化優化器判斷DEFAULT (0、''、'1970-01-01')-- 低基數、固定集合、極少變更 → ENUM
`gender` ENUM('M','F','U') NOT NULL DEFAULT 'U'
`status` ENUM('pending','paid','shipped','completed','cancelled')
-- 高基數、頻繁變更、需關聯查詢 → 查找表 + 外鍵
CREATE TABLE order_status (id TINYINT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE orders (..., status_id TINYINT NOT NULL, FOREIGN KEY(status_id) REFERENCES order_status(id));
-- 訂單表冗存用戶名、商品名,避免 JOIN users/products
ALTER TABLE orders ADD COLUMN buyer_name VARCHAR(50) NOT NULL DEFAULT '',
ADD COLUMN product_name VARCHAR(100) NOT NULL DEFAULT '';
-- 由應用層/觸發器/異步任務維護一致性
INDEX idx_a_b_c (a, b, c)
-- ✅ 能用到索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 ORDER BY b, c
WHERE a = 1 AND b > 2 ORDER BY b
-- ❌ 無法用到索引 (非最左列)
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3
WHERE a = 1 ORDER BY c -- 跳過 b
-- 查詢只需覆蓋索引列,無需回表
EXPLAIN SELECT id, name FROM users WHERE email = 'test@example.com';
-- type=ref, key=idx_email, Extra=Using index
-- 強制覆蓋
CREATE INDEX idx_email_name ON users(email, name); -- 包含 SELECT 列
-- WHERE a=1 AND b LIKE 'abc%' AND c=3
-- 聯合索引 (a,b,c),MySQL 在存儲引擎層過濾 b LIKE 'abc%',減少回表次數
-- ❌ 子查詢 (MySQL 5.6 前物化差,甚至 8.0 仍有優化限制)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);
-- ✅ JOIN (可用索引、可優化連接順序)
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.vip = 1;
-- ✅ UNION ALL 不去重,快;UNION 去重,慢
(SELECT id, name, 'type1' AS src FROM table1 WHERE cond1)
UNION ALL
(SELECT id, name, 'type2' AS src FROM table2 WHERE cond2)
ORDER BY id LIMIT 100;
-- ❌ OR 導致索引失效
SELECT * FROM orders WHERE user_id = 100 OR seller_id = 100;
-- ✅ UNION ALL 分開走索引
SELECT * FROM orders WHERE user_id = 100
UNION ALL
SELECT * FROM orders WHERE seller_id = 100 AND user_id <> 100;
-- ❌ 大偏移 LIMIT 100000, 20 全表掃描
-- ✅ 子查詢定位主鍵
SELECT * FROM orders
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 20;
-- ❌ 索引失效
WHERE DATE(create_time) = '2024-01-15'
WHERE YEAR(create_time) = 2024
WHERE UPPER(name) = 'JOHN'
-- ✅ 範圍查詢走索引
WHERE create_time >= '2024-01-15' AND create_time < '2024-01-16'
WHERE name = 'John' -- 列定義 COLLATE utf8mb4_general_ci 不區分大小寫
READ COMMITTED + binlog_format=ROW 兼顧性能與一致性LOCK TABLES inventory WRITE, orders WRITE;
SELECT quantity FROM inventory WHERE item = 'book' FOR UPDATE;
UPDATE inventory SET quantity = quantity - 1 WHERE item = 'book';
INSERT INTO orders ...;
UNLOCK TABLES;
[mysqld]
# === 內存分配 (核心) ===
innodb_buffer_pool_size = 70%_RAM # 專用 DB 服務器 70-80%
innodb_buffer_pool_instances = 8 # 1GB 每實例,減少鎖競爭
innodb_log_file_size = 2G # 大 Redo Log 減少檢查點衝刷
innodb_log_buffer_size = 64M # 大事務緩存
innodb_flush_log_at_trx_commit = 1 # 雙1 保證零丟失
sync_binlog = 1
# === 連接與並發 ===
max_connections = 2000
thread_cache_size = 100
max_connect_errors = 100000
back_log = 1000
# === 查詢緩存 (5.7 關閉,8.0 移除) ===
query_cache_type = 0
query_cache_size = 0
# === 臨時表與排序 ===
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M # 每連接,慎調大
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 4M
# === IO 優化 ===
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000 # SSD IOPS
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# === 安全與日志 ===
expire_logs_days = 7
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
slow_query_log = 1
long_query_time = 0.5
log_queries_not_using_indexes = 0
log_error = /var/log/mysql/error.log
log_error_verbosity = 3
-- 1. 慢查詢分析
pt-query-digest /var/log/mysql/slow.log --limit 20 --filter '$event->{Query_time} > 1'
-- 2. 索引使用情況
SELECT * FROM sys.schema_unused_indexes; -- 未用索引
SELECT * FROM sys.schema_redundant_indexes; -- 冗餘索引
-- 3. 表碎片
SELECT TABLE_NAME, ROUND(DATA_FREE/1024/1024,2) AS free_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND DATA_FREE > 100*1024*1024
ORDER BY DATA_FREE DESC;
-- 4. InnoDB 狀態
SHOW ENGINE INNODB STATUS\G
-- 關注:History list length、Log sequence number、Buffer pool hit rate、Row lock waits
-- 5. 實時會話
SELECT * FROM performance_schema.events_statements_current
WHERE TIMER_WAIT > 1000000000000 -- >1秒
ORDER BY TIMER_WAIT DESC;
優化鐵律:
1. 測量優先:無數據不優化,建立基線 → 識別瓶頸 → 驗證效果
2. 從高收益入手:慢查詢 TOP 10、無索引表、大表碎片、配置明顯不合理
3. 小步快跑:單次變更單一參數/索引/SQL,灰度驗證
4. 全鏈路視角:應用連接池 → 網絡 → MySQL 線程 → 優化器 → 存儲引擎 → IO → 磁盤