MySQL 核心知識體系大全：從基礎操作到高級優化（萬字長文）

前言

本文系統梳理 MySQL 核心知識點，涵蓋服務管理、SQL 語法、數據類型、索引優化、事務鎖、備份恢復、權限管理等模塊，適合開發者速查與面試複習。內容基於 MySQL 5.7/8.0 通用特性整理。

一、服務啟動與連接

# 啟動/停止/重啟 (Linux systemd)
systemctl start|stop|restart mysqld

# 客戶端連接
mysql -h 127.0.0.1 -P 3306 -u root -p

# 跳過權限表啟動（緊急重置密碼）
mysqld --skip-grant-tables &

# 修改 root 密碼 (5.7+)
UPDATE mysql.user SET authentication_string=PASSWORD('newpass') WHERE User='root';
FLUSH PRIVILEGES;

二、數據庫/表結構操作 (DDL)

數據庫級

CREATE DATABASE IF NOT EXISTS db_name 
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SHOW DATABASES;
SHOW CREATE DATABASE db_name;
ALTER DATABASE db_name CHARACTER SET utf8mb4;
DROP DATABASE IF EXISTS db_name;

表級

CREATE TABLE IF NOT EXISTS `user` (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用戶名',
    email VARCHAR(128) NOT NULL DEFAULT '' COMMENT '郵箱',
    status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1啟用 0禁用',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_username (username),
    KEY idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

-- 常用 ALTER
ALTER TABLE `user` ADD COLUMN phone VARCHAR(16) NOT NULL DEFAULT '' AFTER email;
ALTER TABLE `user` MODIFY COLUMN status TINYINT UNSIGNED NOT NULL DEFAULT 1;
ALTER TABLE `user` CHANGE COLUMN phone mobile VARCHAR(16) NOT NULL DEFAULT '';
ALTER TABLE `user` DROP COLUMN mobile;
ALTER TABLE `user` RENAME TO `user_new`;

三、數據操作 (DML)

增刪改查核心語法

-- 插入
INSERT INTO `user` (username, email) VALUES ('zhangsan', 'zs@ex.com');
INSERT INTO `user` (username, email) VALUES ('lisi', 'ls@ex.com'), ('wangwu', 'ww@ex.com');

-- 查詢 (完整子句順序)
SELECT DISTINCT username, email 
FROM `user` 
WHERE status = 1 AND created_at > '2024-01-01'
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

-- 更新
UPDATE `user` SET status = 0 WHERE id = 1;

-- 刪除
DELETE FROM `user` WHERE status = 0 LIMIT 100;

多表連接

-- 內連接
SELECT u.username, o.order_no 
FROM `user` u 
INNER JOIN `order` o ON u.id = o.user_id;

-- 左連接 (保留左表所有行)
SELECT u.username, o.order_no 
FROM `user` u 
LEFT JOIN `order` o ON u.id = o.user_id;

-- 子查詢
SELECT * FROM `user` WHERE id IN (SELECT user_id FROM `order` WHERE amount > 1000);

四、數據類型選型指南

五、索引設計與優化

索引類型

• 主鍵索引：聚簇索引，葉子節點存完整行數據。
• 二級索引：葉子節點存主鍵值，回表查詢完整行。
• 覆蓋索引：查詢列全在索引中，無需回表，性能提升數量級。
• 聯合索引：遵循最左前綴原則，(a,b,c) 支持 a/a,b/a,b,c。

索引失效常見場景

-- 1. 隱式類型轉換
WHERE phone = 13800138000  -- phone 是 VARCHAR，需加引號

-- 2. 左側模糊匹配
WHERE name LIKE '%张三'    -- 不走索引

-- 3. 對索引列運算
WHERE YEAR(created_at) = 2024  -- 改用 created_at >= '2024-01-01' AND created_at < '2025-01-01'

-- 4. OR 條件未全命中索引
WHERE a=1 OR b=2  -- a,b 單獨有索引但無聯合索引

-- 5. 隱式字符集轉換
JOIN 兩表字符集不同 (utf8 vs utf8mb4)

EXPLAIN 關鍵列解讀

六、事務與鎖機制

ACID 特性

• 原子性：Undo Log 回滾
• 一致性：約束、觸發器、外鍵保證
• 隔離性：MVCC + 鎖實現
• 持久性：Redo Log 預寫日誌

四大隔離級別

* InnoDB RR 級別通過 Next-Key Lock 解決大部分幻讀，但極端情況仍可能出現。

鎖類型

• 行鎖：Record Lock (單行)、Gap Lock (間隙)、Next-Key Lock (行+間隙)
• 意向鎖：IX/IS，表級，協調行鎖與表鎖衝突
• 死鎖檢測：InnoDB 自動檢測並回滾代價較小的事務

七、主從複制與高可用

複製原理

1. Master 寫入 Binlog (ROW/STATEMENT/MIXED)
2. Slave I/O Thread 拉取 Binlog 寫入 Relay Log
3. Slave SQL Thread 重放 Relay Log

GTID 模式配置 (5.6+)

# Master
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=mysql-bin
server_id=1

# Slave
gtid_mode=ON
enforce_gtid_consistency=ON
server_id=2
CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='repl', MASTER_PASSWORD='xxx', MASTER_AUTO_POSITION=1;
START SLAVE;

常見延遲排查

• 大事務拆分、避免長時間鎖表
• Slave 並行複製：slave_parallel_workers=4 + slave_parallel_type=LOGICAL_CLOCK
• 硬件瓶頸：SSD、網絡帶宽、CPU

八、備份與恢復策略

邏輯備份

# 全庫備份 (含結構+數據)
mysqldump -uroot -p --single-transaction --routines --triggers --events --master-data=2 --databases db1 db2 > backup_$(date +%F).sql

# 僅結構
mysqldump -uroot -p -d db_name > schema.sql

# 僅數據
mysqldump -uroot -p -t db_name > data.sql

物理備份

# Percona XtraBackup (熱備份、增量、流式)
xtrabackup --backup --target-dir=/backup/full --user=root --password=xxx
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full --datadir=/var/lib/mysql

恢復驗證

• 定期演練：每月至少一次全量恢復演練至測試環境
• 校驗和：mysqldump --tab 導出 CSV 對比行數/校驗和
• PITR (Point-In-Time Recovery)：全量備份 + Binlog 重放至指定時間點

九、權限管理最佳實踐

-- 創建業務讀寫用戶
CREATE USER 'app_rw'@'10.0.%' IDENTIFIED BY 'StrongPass#2024';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_rw'@'10.0.%';

-- 創建只讀用戶
CREATE USER 'app_ro'@'10.0.%' IDENTIFIED BY 'StrongPass#2024';
GRANT SELECT ON app_db.* TO 'app_ro'@'10.0.%';

-- 查看權限
SHOW GRANTS FOR 'app_rw'@'10.0.%';

-- 回收權限
REVOKE DELETE ON app_db.* FROM 'app_rw'@'10.0.%';
FLUSH PRIVILEGES;

十、常用維護命令速查

-- 表維護
ANALYZE TABLE tbl_name;        -- 更新統計信息
OPTIMIZE TABLE tbl_name;       -- 整理碎片 (InnoDB 重建表)
CHECK TABLE tbl_name;          -- 檢查錯誤
REPAIR TABLE tbl_name;         -- 修復 (MyISAM)

-- 狀態監控
SHOW PROCESSLIST;              -- 當前線程
SHOW ENGINE INNODB STATUS;     -- InnoDB 內部狀態
SHOW GLOBAL STATUS LIKE 'Innodb_%'; -- 關鍵指標

-- 配置查看
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

十一、性能優化核心清單

1. 慢查詢日誌：slow_query_log=1, long_query_time=1, 分析工具 pt-query-digest
2. 緩衝池：innodb_buffer_pool_size 設為物理內存 60-70%
3. 連接池：應用層配置 HikariCP/Druid，避免頻繁建連
4. 分頁優化：深分頁用延遲關聯或覆蓋索引 SELECT id FROM t WHERE ... LIMIT 100000,10 → SELECT * FROM t WHERE id IN (SELECT id FROM t WHERE ... LIMIT 100000,10)
5. 大表拆分：水平分表 (ShardingSphere/MyCAT) 或分區表
6. 只讀實例：讀寫分離，主庫專寫，從庫專讀

結語

MySQL 知識體系龐大，本文覆蓋了從安裝部署到核心原理、優化實戰的關鍵路徑。建議開發者：

• 夯實 SQL 基礎與執行計劃分析能力
• 深入理解 InnoDB 存儲引擎原理 (B+樹、MVCC、鎖、日誌)
• 建立完善的備份恢復與變更管理流程
• 持續關注官方 Release Notes，跟進 8.0/8.4/9.0 新特性

工欲善其事，必先利其器。願這份筆記成為你 MySQL 進階之路的得力參考。