萬盛學電腦網

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

MySQL 實戰進階指南:分區表、主從原理、日志運維、四捨五入、字段過濾、學習路徑

MySQL 進階五大實戰模塊

模塊一:分區表實戰 —— 百萬級數據架構優化

核心價值: 分區裁剪、歷史數據秒級歸檔、並行聚合、跨磁盤 I/O。
關鍵決策: 分區鍵選擇 (時間/ID/枚舉)、類型選擇 (RANGE/LIST/HASH/KEY/COLUMNS)、分區數量控制 (單分區 1-5千萬行)。
避坑: 分區鍵必須在主鍵/唯一鍵中、非 NULL、無外鍵、WHERE 條件必帶分區鍵否則全分區掃描。

模塊二:主從復制原理深度解析

三線程模型:

  1. Master Binlog Dump 線程: 讀取 Binlog 發送給 Slave IO 線程
  2. Slave IO 線程: 接收 Binlog → 寫入本地 Relay Log → 更新 master.info
  3. Slave SQL 線程: 讀取 Relay Log → 解析為事務 → 重放執行 → 更新 relay-log.info

關鍵文件: master.info (Master 位置)、relay-log.info (執行位置)、relay-bin.xxxxxx (中繼日志)。
演進歷程: 單線程 → 雙線程 (Yahoo Jeremy Zawodny 提議) → 並行復制 (5.7+ LOGICAL_CLOCK) → GTID 無位置復制。

模塊三:日志文件運維與清理

  • 二進制日志 (Binlog): 核心資產,mysql-bin.000001 + mysql-bin.index,嚴禁手動 rm,用 PURGE BINARY LOGS
  • 錯誤日志: hostname.err,記錄啟動/崩潰/嚴重警告,FLUSH LOGS 輪轉
  • 慢查詢日志: long_query_time=1,pt-query-digest 分析 Top SQL
  • InnoDB Redo Log: ib_logfile0/1,WAL 核心,innodb_log_file_size=1G×4
  • 風險: 不及時清理導致磁盤爆滿,建議獨立掛載日志卷、配置自動過期

模塊四:四捨五入與數值計算陷阱

函數行為典型場景
ROUND(x,d)銀行家舍入 (五向偶數)展示層近似值
FLOOR(x)向下取整分桶、分頁總頁數
CEILING(x)向上取整分頁總頁數、資源向上分配
TRUNCATE(x,d)截斷不四捨五入金額計算、精度控制

金鑰律: 金額存分 (INT)、計算用 DECIMAL、展示用 ROUND、關鍵業務應用層 BigDecimal ROUND_HALF_UP。

模塊五:字段過濾與去重技巧


-- 需求: username+password 組合去重,保留 ID 最大的記錄
SELECT * FROM mytable 
WHERE id IN (
    SELECT MAX(id) FROM mytable 
    GROUP BY username, password
);

-- 保留 ID 最小
SELECT * FROM mytable 
WHERE id IN (
    SELECT MIN(id) FROM mytable 
    GROUP BY username, password
);

-- 原理: GROUP BY 分組 + 聚合函數 (MAX/MIN) 定位目標行,外層 IN 回表
-- 注意: GROUP BY 非聚合列必須在 ONLY_FULL_GROUP_BY 模式下兼容

MySQL 學習進階路線圖

  1. 基礎夯實 (1-2 週): SQL 語法、數據類型、索引原理 (B+樹)、事務 ACID、隔離級別、MVCC
  2. 性能調優 (3-4 週): EXPLAIN 深度解讀、慢查詢分析、Schema 設計範式/反範式、配置參數調優
  3. 架構高可用 (2-3 週): 主從/主主/GTID/半同步/並行復制、MHA/Orchestrator 故障切換、ProxySQL 讀寫分離
  4. 存儲引擎內核 (持續): InnoDB 架構、Buffer Pool、Change Buffer、Redo/Undo Log、鎖機制、死鎖分析
  5. 生產運維 (持續): 備份恢復演練、容量規劃、安全合規、遷移上云、故障複盤

推薦資源

  • 官方文檔:dev.mysql.com/doc
  • 經典書籍:《High Performance MySQL 4th》、《MySQL 技術內幕: InnoDB 存儲引擎》
  • 工具箱:pt-query-digest, pt-online-schema-change, mysqlbinlog, mysqldumpslow, sys schema
  • 社區:MySQL 官方論壇、Percona Blog、Planet MySQL、GitHub Awesome MySQL
copyright © 萬盛學電腦網 all rights reserved