萬盛學電腦網

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

MySQL 數據庫優化方法論:配置、索引、SQL、架構全鏈路指南

優化方法論:由內而外、由點到面

MySQL 優化不是調幾個參數,而是系統工程。遵循「測量 → 分析 → 優化 → 驗證 → 固化」閉環。

第一層:Schema 與數據類型 (基石)

1. 最小化數據類型

  • TINYINT(1) 代替 INT 存布爾/狀態 (1 byte vs 4 bytes)
  • MEDIUMINT 代替 INT 存百萬級 ID (3 bytes vs 4 bytes)
  • CHAR(6) 存郵編,CHAR(32) 存 MD5,CHAR(64) 存 SHA256
  • VARCHAR(N) 只存可變長文本,N 為業務最大長度
  • DECIMAL(12,2) 或 BIGINT(分) 存金額,禁用 FLOAT/DOUBLE
  • DATETIME(3) 存毫秒時間,TIMESTAMP 存自動更新時間戳

2. NOT NULL + DEFAULT

-- ✅ 優化器友好,統計信息準確,節省 NULL 位圖空間
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

3. 短索引 / 前綴索引

-- 長字符串列只索引前綴 (區分度足夠時)
ALTER TABLE users ADD INDEX idx_email_prefix (email(19));  -- email 區分度前 19 字符足夠
-- 驗證區分度
SELECT COUNT(DISTINCT LEFT(email, 19)) / COUNT(*) FROM users;  -- > 0.9 可接受

第二層:索引設計 (加速器)

1. 索引創建原則

  • WHERE/JOIN/ORDER BY/GROUP BY 涉及的列 → 候選索引列
  • 高區分度列放前 (性別/狀態低區分度放後或不建單獨索引)
  • 聯合索引遵循「最左前綴」+「等值在前、範圍在後」
  • 覆蓋索引避免回表:INDEX idx_a_b_c (a, b, c) 覆蓋 SELECT a,b,c FROM t WHERE a=1 AND b=2
  • 單表索引數 ≤ 5 (寫入開銷、優化器選擇成本)

2. 索引失效場景避坑

失效模式 錯誤示例 修正
函數/運算包裹列 WHERE YEAR(dt) = 2024 WHERE dt >= '2024-01-01' AND dt < '2025-01-01'
隱式類型轉換 WHERE varchar_col = 123 WHERE varchar_col = '123'
LIKE 以 % 開頭 WHERE name LIKE '%abc' 全文索引 / 反轉存儲 / 搜索引擎
OR 條件跨列無索引 WHERE a=1 OR b=2 UNION ALL 分開查詢
IS NULL/IS NOT NULL WHERE col IS NULL 給默認值 0/'',查 WHERE col = 0
聯合索引非最左 INDEX(a,b,c) WHERE b=1 建 INDEX(b) 或調整順序

第三層:SQL 重寫與執行計劃

1. EXPLAIN 核心列解讀

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1 ORDER BY created_at DESC LIMIT 20;

+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_uid_status| idx_uid_status| 5      | const |  100 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------------+

關鍵列:
type:  system > const > eq_ref > ref > range > index > ALL (由好到壞)
key:   實際使用的索引
rows:  預估掃描行數 (越小越好)
filtered: 過濾後剩餘比例 (越高越好)
Extra: Using index(覆蓋) / Using where / Using filesort(排序) / Using temporary(臨時表)

2. 優化器提示 (MySQL 8.0+)

-- 強制使用索引
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM orders 
WHERE user_id = 100 /*+ INDEX(orders idx_uid_status) */;

-- 禁用索引
SELECT * FROM orders IGNORE INDEX (idx_uid_status) WHERE user_id = 100;

-- 指定連接順序
SELECT /*+ JOIN_ORDER(t1, t2) */ * FROM t1 JOIN t2 ON t1.id = t2.t1_id;

3. 典型慢 SQL 改寫模板

-- 1. 大表分頁
-- ❌ LIMIT 100000, 20
-- ✅ 延遲關聯
SELECT * FROM t WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 100000, 1) ORDER BY id LIMIT 20;

-- 2. COUNT(*) 優化
-- ❌ SELECT COUNT(*) FROM huge_table WHERE status = 1
-- ✅ 計數表 / Redis 緩存 / 近似值 SHOW TABLE STATUS

-- 3. 隨機取樣
-- ❌ ORDER BY RAND() LIMIT 10
-- ✅ WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t))) LIMIT 10

-- 4. 多表關聯排序
-- ❌ SELECT * FROM a JOIN b ON a.id=b.a_id ORDER BY a.created_at LIMIT 10
-- ✅ 先排序主表再 JOIN
SELECT * FROM (SELECT * FROM a ORDER BY created_at DESC LIMIT 10) a JOIN b ON a.id=b.a_id;

第四層:服務器配置 (引擎室)

內存分配公式 (專用 DB 服務器)

Total RAM = 64GB
OS Reserve = 4GB
MySQL Reserve = 60GB

innodb_buffer_pool_size = 48GB      (75%)
innodb_log_file_size = 4GB
innodb_log_buffer_size = 256M
key_buffer_size = 128M              (MyISAM 系統表)
sort_buffer_size = 4M               (per thread)
read_buffer_size = 2M
join_buffer_size = 4M
tmp_table_size = 512M
max_heap_table_size = 512M
thread_cache_size = 200
max_connections = 2000

關鍵參數影響對照表

參數 調大收益 調大風險 建議值
innodb_buffer_pool_size 緩存命中率↑、磁盤 IO↓ OS OOM、Swap 狂飆 物理內存 60-80%
innodb_log_file_size 檢查點衝刷頻率↓、寫入性能↑ 崩潰恢復時間↑ 1-4GB
innodb_flush_log_at_trx_commit 1→2 寫入性能↑ 10x 掉電丟 1 秒數據 核心業務=1,日志/統計=2
max_connections 支撐更多並發 線程上下文切換、內存暴增 配合連接池,峰值*1.2
sort_buffer_size 大排序避免磁盤臨時表 每連接分配,連接多時 OOM 2-8MB,會話級調大

第五層:架構擴展 (終極方案)

1. 讀寫分離

# ProxySQL 配置片段
mysql_servers:
  - { hostgroup: 10, hostname: "master", port: 3306, weight: 100 }
  - { hostgroup: 20, hostname: "slave1", port: 3306, weight: 100 }
  - { hostgroup: 20, hostname: "slave2", port: 3306, weight: 100 }

mysql_query_rules:
  - { rule_id: 1, active: 1, match_pattern: "^SELECT .* FOR UPDATE", destination_hostgroup: 10, apply: 1 }
  - { rule_id: 2, active: 1, match_pattern: "^SELECT ", destination_hostgroup: 20, apply: 1 }
  - { rule_id: 3, active: 1, match_pattern: "^(INSERT|UPDATE|DELETE|REPLACE)", destination_hostgroup: 10, apply: 1 }

2. 分庫分表 (Sharding)

  • 垂直拆分:業務模塊解耦 (用戶庫、訂單庫、商品庫)
  • 水平分表:按 user_id % 1024 或範圍分片,中間件路由
  • 分布式事務:Seata AT 模式 / 本地消息表 / 事務性外發

3. 異構存儲分流

數據類型 推薦存儲 同步方式
核心交易 (OLTP) MySQL InnoDB 主從 / Group Replication
日志/審計/點擊流 ClickHouse / Apache Doris Canal + Kafka → Flink → OLAP
全文搜索/日志分析 Elasticsearch / OpenSearch Canal 同步 Binlog
熱點 Key-Value / 計數器 Redis Cluster 應用雙寫 / Redis 源碼讀取
文件/圖片/視頻 S3 / MinIO / OSS 應用直傳,DB 存 URL

優化檢查清單 (上線前必驗)

  • ☐ 所有表有主鍵 (最好是自增 BIGINT)
  • ☐ 所有外鍵列有索引
  • ☐ 無冗餘/未使用索引 (sys.schema_unused_indexes)
  • ☐ 慢查詢日志開啟,long_query_time=0.5
  • ☐ 核心業務 SQL 經過 EXPLAIN 驗證走索引
  • ☐ 連接池配置:maxLifetime < wait_timeout、validationQuery
  • ☐ 備份恢復演練近 30 天內通過
  • ☐ 監控覆蓋:QPS、TPS、延遲 P99、連接數、緩存命中率、磁盤 IOPS、CPU/內存

總結:MySQL 優化是「千層餅」,Schema 是地基,索引是骨架,SQL 是肌肉,配置是氣血,架構是神經。不要為了優化而優化,要以業務 SLA (可用性、延遲、吞吐) 為導向,投入產出比最高的優化先做。

copyright © 萬盛學電腦網 all rights reserved