MySQL 資料庫基礎核心知識體系回顧

系統梳理 MySQL 核心基礎知識：資料表操作 (CRUD、約束、索引)、索引分類與優化、儲存引擎選型、字符集配置、常用查詢技巧，構建完整知識地圖，適合面試複習與日常查閱。

一、資料表核心操作

1.1 建表要素

• 欄位定義：名稱、類型、長度、屬性 (NOT NULL、DEFAULT、AUTO_INCREMENT、COMMENT)
• 完整性約束：PRIMARY KEY、UNIQUE、FOREIGN KEY、CHECK、NOT NULL
• 索引：PRIMARY、UNIQUE、INDEX (普通)、FULLTEXT (全文)
• 表選項：ENGINE=InnoDB、CHARSET=utf8mb4、COMMENT='表說明'

1.2 修改表 (ALTER TABLE)

• 欄位增刪改：ADD、DROP、MODIFY、CHANGE
• 重命名：RENAME TO
• 索引/約束增刪：ADD/DROP PRIMARY KEY|UNIQUE|INDEX|FOREIGN KEY|CHECK
• 最佳實踐：多操作合併為單條 ALTER，減少鎖表與複製開銷

1.3 刪除表

• DROP TABLE [IF EXISTS] tbl_name：刪除結構與資料，不可恢復
• TRUNCATE TABLE tbl_name：保留結構，清空資料，重置自增 ID，速度快、不觸發 Trigger

1.4 資料操作 (DML)

操作	語法要點	範例
單條插入	指定欄位順序，VALUES 對應	INSERT INTO user(name,age) VALUES('Tom',25)
批次插入	多組 VALUES，單條語句	INSERT INTO user(name,age) VALUES('A',1),('B',2),('C',3)
更新	必帶 WHERE，否則全表更新	UPDATE user SET age=26 WHERE name='Tom'
刪除	必帶 WHERE，大量刪除考慮分批或 TRUNCATE	DELETE FROM user WHERE age<18

1.5 查詢進階 (DQL)

• 別名： AS 或空格，欄位/表別名簡化輸出
• 去重： DISTINCT 作用於全部選取欄位組合
• 範圍： BETWEEN ... AND ... (含邊界)
• 集合： IN (val1, val2...)、NOT IN
• 模糊： LIKE，% 多字元、_ 單字元，ESCAPE 轉義
• 排序： ORDER BY col [ASC|DESC]，可多欄位
• 分頁： LIMIT offset, count (offset 從 0 起)

二、索引體系

2.1 四大分類

類型	關鍵字	特性	適用場景
主鍵索引	PRIMARY KEY	唯一、非空、聚簇索引、一表僅一個	主鍵欄位
唯一索引	UNIQUE KEY	值唯一、允許 NULL、輔助索引	Email、手機號、身份證
普通索引	INDEX / KEY	允許重複、允許 NULL、輔助索引	查詢/排序/分組高頻欄位
全文索引	FULLTEXT	支援自然語言/布爾模式全文搜尋	文章內容、日誌、商品描述

2.2 索引底層結構

• B+ 樹： InnoDB 預設，支援範圍查詢、排序、最左前綴原則
• Hash： Memory 引擎預設，僅等值查詢 O(1)，不支援範圍/排序
• R-Tree： 空間索引 (GIS)

2.3 覆蓋索引與回表

• 輔助索引葉節點存主鍵值，查詢非索引欄位需「回表」查主鍵索引
• 覆蓋索引：查詢欄位全包含於索引中，避免回表，效能大增
• 範例：INDEX idx_uid_email (uid, email) 可覆蓋 SELECT email FROM user WHERE uid=100

三、儲存引擎與資料儲存

3.1 主流引擎對比

引擎	事務	鎖粒度	外鍵	全文索引	適用場景
InnoDB	✓ (ACID)	行鎖	✓	5.6+ ✓	核心業務、高併發、需事務
MyISAM	✗	表鎖	✗	✓	唯讀/讀多寫少、全文搜尋(舊版)
MEMORY	✗	表鎖	✗	✗	臨時表、快取、查找表、資料可丟失

3.2 查看支援引擎

SHOW ENGINES;
-- 或
SELECT ENGINE, SUPPORT FROM INFORMATION_SCHEMA.ENGINES;

3.3 資料檔案位置

• 預設 datadir (通常 /var/lib/mysql 或安裝目錄 data)
• InnoDB：共享表空間 ibdata1 + 獨立表空間 tbl_name.ibd (開啟 innodb_file_per_table)
• MyISAM：.frm 結構、.MYD 資料、.MYI 索引

四、字符集與排序規則

4.1 多層級設定優先序

伺服器 character-set-server < 資料庫 DEFAULT CHARACTER SET < 資料表 DEFAULT CHARACTER SET < 欄位 CHARACTER SET

4.2 常用字符集

字符集	別名	中文支援	Emoji 支援	Bytes/字符
utf8mb4	UTF-8 完整版	✓	✓	1-4
utf8	utf8mb3	✓	✗ (僅 BMP)	1-3
gbk	中文擴展	✓	✗	1-2
latin1	ISO-8859-1	✗	✗	1

4.3 配置最佳實踐

-- my.cnf / my.ini [mysqld] 區段
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

注意：Windows 客戶端連線若出現中文亂碼，執行 SET NAMES gbk 或確認客戶端編碼設定一致。

五、常用實用技巧匯總

1. 強制索引： FORCE INDEX(idx_name)、USE INDEX、IGNORE INDEX
2. EXPLAIN 分析： 關注 type (system/const/eq_ref/ref/range/index/ALL)、key、rows、Extra (Using index/Using where/Using temporary/Using filesort)
3. 慢查詢日誌： slow_query_log=1、long_query_time=1、log_queries_not_using_indexes=1
4. 資料匯出/匯入： mysqldump -u root -p db > dump.sql、mysql -u root -p db < dump.sql
5. 線上修改大表： pt-online-schema-change / gh-ost

這份知識地圖涵蓋 MySQL 開發與運維 80% 的高頻場景，建議收藏備查，定期複習鞏固核心功力。