萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 數據庫程序優化實戰:從架構到 SQL 的系統性能調優

優化維度全景圖

MySQL 性能優化是系統工程,需自上而下:

  1. 架構層:分庫分表、讀寫分離、緩存層、異步化
  2. Schema 層:範式/反範式、數據類型、索引策略、分區
  3. SQL 層:查詢重寫、執行計劃分析、優化器提示
  4. 配置層:內存分配、並發控制、IO 優化、日志策略
  5. 硬件/OS 層:CPU/NUMA、內存、SSD/RAID、網絡、內核參數

一、Schema 設計優化

1. 選擇最小足夠的數據類型

-- ❌ 郵編用 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() 轉換

2. NOT NULL 與 DEFAULT

  • 所有列默認 NOT NULL,NULL 會佔用額外位圖、導致統計信息不準、復雜化優化器判斷
  • 業務無義義時給合理 DEFAULT (0、''、'1970-01-01')

3. ENUM vs 查找表

-- 低基數、固定集合、極少變更 → 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));

4. 反範式化:冗餘列換查詢速度

-- 訂單表冗存用戶名、商品名,避免 JOIN users/products
ALTER TABLE orders ADD COLUMN buyer_name VARCHAR(50) NOT NULL DEFAULT '',
                       ADD COLUMN product_name VARCHAR(100) NOT NULL DEFAULT '';
-- 由應用層/觸發器/異步任務維護一致性

二、索引策略

1. 索引類型選擇

  • B+Tree (默認):全值匹配、範圍查詢、排序、前綴匹配
  • Hash (Memory/Heap):精確查找,不支持範圍/排序
  • Fulltext:全文搜索 (ngram/MeCab)
  • Spatial (R-Tree):GIS 地理空間

2. 聯合索引最左前綴原則

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

3. 覆蓋索引

-- 查詢只需覆蓋索引列,無需回表
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 列

4. 索引下推 (ICP, MySQL 5.6+)

-- WHERE a=1 AND b LIKE 'abc%' AND c=3
-- 聯合索引 (a,b,c),MySQL 在存儲引擎層過濾 b LIKE 'abc%',減少回表次數

三、SQL 重寫優化

1. 子查詢 → JOIN

-- ❌ 子查詢 (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;

2. UNION 替代臨時表

-- ✅ 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;

3. OR 條件優化

-- ❌ 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;

4. 分頁優化 (延遲關聯)

-- ❌ 大偏移 LIMIT 100000, 20 全表掃描
-- ✅ 子查詢定位主鍵
SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 20;

5. 避免函數包裹列

-- ❌ 索引失效
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 不區分大小寫

四、事務與鎖優化

1. 事務原則

  • 盡量縮短事務持有時間:業務邏輯移出事務,只保留必要的寫操作
  • 按固定順序訪問表/行,避免死鎖
  • 合理設置隔離級別:READ COMMITTED + binlog_format=ROW 兼顧性能與一致性

2. 顯式鎖表 (極少用,替代長事務)

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;

3. 外鍵權衡

  • 優點:數據完整性由數據庫保證,級聯操作方便
  • 缺點:鎖競爭、死鎖風險、導入/遷移麻煩、性能開銷
  • 建議:核心業務 (訂單-用戶、訂單-商品) 用外鍵;非核心/高寫入表應用層維護

五、服務器配置優化 (my.cnf 核心參數)

[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;

七、架構級優化 (超越單機)

  • 讀寫分離:ProxySQL / MyCat / ShardingSphere 路由 SELECT 到從庫
  • 分庫分表:ShardingSphere / Vitess / 自研中間件,按 tenant_id / user_id 分片
  • 緩存層:Redis 熱點數據、計數器、Session;多級緩存 (Local + Remote)
  • 異步化:Kafka/RocketMQ 削峰填谷,讀寫分離最終一致性
  • HTAP:TiDB / StarRocks / ClickHouse 承擔 OLAP,MySQL 專注 OLTP

優化鐵律:
1. 測量優先:無數據不優化,建立基線 → 識別瓶頸 → 驗證效果
2. 從高收益入手:慢查詢 TOP 10、無索引表、大表碎片、配置明顯不合理
3. 小步快跑:單次變更單一參數/索引/SQL,灰度驗證
4. 全鏈路視角:應用連接池 → 網絡 → MySQL 線程 → 優化器 → 存儲引擎 → IO → 磁盤

copyright © 萬盛學電腦網 all rights reserved