MySQL 學習筆記大全：千行核心知識點匯總 (安裝、SQL、索引、事務、鎖、備份、權限、優化)

MySQL 核心知識體系千行匯總：從入門到架構師必備速查手冊

整理自實戰項目與官方文檔，涵蓋安裝部署、SQL 標準、存儲引擎、索引設計、事務鎖、備份恢複、權限管理、效能優化八大模組，適合離線檢索與面試複習。

一、安裝部署與連線

# Ubuntu 安裝
apt-get install mysql-server mysql-workbench

# 連線
mysql -h host -P port -u user -p

# 跳過權限 (重置密碼用)
mysqld --skip-grant-tables

# 顯示執行緒、變數
SHOW PROCESSLIST;
SHOW VARIABLES;

二、資料庫與表操作 (DDL)

-- 庫操作
CREATE DATABASE [IF NOT EXISTS] db_name CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
SHOW CREATE DATABASE db_name;
ALTER DATABASE db_name CHARSET utf8mb4;
DROP DATABASE [IF EXISTS] db_name;

-- 表操作
CREATE TABLE [IF NOT EXISTS] tbl_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL DEFAULT '' COMMENT '名稱',
    age TINYINT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8mb4;

SHOW TABLES;
SHOW CREATE TABLE tbl_name;
DESC tbl_name;

-- 修改表
ALTER TABLE tbl_name ENGINE=InnoDB;
RENAME TABLE old TO new;
ALTER TABLE tbl_name ADD COLUMN email VARCHAR(100) AFTER name;
ALTER TABLE tbl_name MODIFY COLUMN name VARCHAR(100);
ALTER TABLE tbl_name CHANGE old_name new_name VARCHAR(100);
ALTER TABLE tbl_name DROP COLUMN age;
ALTER TABLE tbl_name ADD PRIMARY KEY (id);
ALTER TABLE tbl_name ADD UNIQUE KEY uk_email (email);
ALTER TABLE tbl_name ADD INDEX idx_name (name);
ALTER TABLE tbl_name DROP INDEX idx_name;

-- 刪除/清空/複製
DROP TABLE [IF EXISTS] tbl_name;
TRUNCATE TABLE tbl_name;
CREATE TABLE new_tbl LIKE old_tbl;              -- 僅結構
CREATE TABLE new_tbl AS SELECT * FROM old_tbl;  -- 結構+資料

-- 表維護
ANALYZE TABLE tbl_name;
CHECK TABLE tbl_name;
OPTIMIZE TABLE tbl_name;
REPAIR TABLE tbl_name;  -- MyISAM

三、資料操作 (DML) 與查詢進階

-- 增
INSERT INTO tbl (name, age) VALUES ('Tom', 20), ('Jerry', 18);
INSERT INTO tbl SET name='Tom', age=20;
INSERT INTO tbl (name, age) SELECT name, age FROM other_tbl;
INSERT INTO tbl (id, name) VALUES (1, 'A') ON DUPLICATE KEY UPDATE name='A_new';

-- 刪
DELETE FROM tbl WHERE id=1 LIMIT 1;
DELETE t1, t2 FROM t1 JOIN t2 ON t1.id=t2.id WHERE t1.status=0;

-- 改
UPDATE tbl SET age=21 WHERE name='Tom';

-- 查核心語法
SELECT [ALL|DISTINCT] select_expr
FROM table_references
[WHERE where_condition]
[GROUP BY {col|expr|position} [ASC|DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col|expr|position} [ASC|DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

-- 聚合函數
COUNT(*), COUNT(col), SUM(col), MAX(col), MIN(col), AVG(col), GROUP_CONCAT(col)

-- 連接查詢
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id;
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id;
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.t1_id;
SELECT * FROM t1 CROSS JOIN t2;

-- 子查詢
SELECT * FROM tbl WHERE id IN (SELECT id FROM other WHERE status=1);
SELECT * FROM tbl WHERE EXISTS (SELECT 1 FROM other WHERE other.tbl_id = tbl.id);

-- UNION
(SELECT id FROM t1) UNION (SELECT id FROM t2);  -- 去重
(SELECT id FROM t1) UNION ALL (SELECT id FROM t2); -- 不去重

四、資料類型精選指南

五、索引設計六大原則 (詳見文章 175454)

1. WHERE/JOIN 列優先，非 SELECT 列
2. 高基數列優先 (唯一值多)
3. 短索引/前綴索引 (VARCHAR(200) → idx(name(10)))
4. 最左前綴法則 (複合索引 = 多索引)
5. 拒絕過度索引 (寫入殺手)
6. 匹配支援運算子 (=, >, >=, <, <=, BETWEEN, IN, LIKE 'prefix%')

六、事務與鎖

-- 事務控制
START TRANSACTION;  -- 或 BEGIN
COMMIT;
ROLLBACK;
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;

-- 自動提交
SET autocommit = 0|1;

-- 鎖表 (MyISAM)
LOCK TABLES t1 READ, t2 WRITE;
UNLOCK TABLES;

-- InnoDB 行鎖 (需事務中)
SELECT * FROM t WHERE id=1 FOR UPDATE;        -- 排他鎖 (X)
SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE; -- 共享鎖 (S)

-- 死鎖處理
SHOW ENGINE INNODB STATUS;  -- 查看最近死鎖詳情
-- 應用層：固定訪問順序、縮短事務、避免長事務

七、備份與還原

# 邏輯備份
mysqldump -u user -p --single-transaction --routines --triggers --events db_name > backup.sql
mysqldump -u user -p --all-databases > all.sql

# 物理備份 (需停庫或快照)
xtrabackup --backup --target-dir=/backup --user=root --password

# 還原
mysql -u user -p db_name < backup.sql
gunzip < backup.sql.gz | mysql -u user -p db_name

八、用戶與權限管理

-- 建用戶
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass123';

-- 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON db_name.* TO 'app'@'%';
GRANT ALL PRIVILEGES ON db_name.* TO 'admin'@'%' WITH GRANT OPTION;

-- 查看權限
SHOW GRANTS FOR 'app'@'%';

-- 回收權限
REVOKE INSERT ON db_name.* FROM 'app'@'%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';

-- 改密碼
ALTER USER 'app'@'%' IDENTIFIED BY 'NewPass456';
SET PASSWORD FOR 'app'@'%' = PASSWORD('NewPass456');

-- 刪用戶
DROP USER 'app'@'%';

-- 權限層級：全局 > 庫 > 表 > 列
-- 權限表：mysql.user, mysql.db, mysql.tables_priv, mysql.columns_priv

九、效能優化關鍵參數 (InnoDB 為主)

十、常用函數速查

-- 字串
CONCAT(s1,s2...), SUBSTR(str,pos,len), REPLACE(str,from,to), TRIM(str), LEFT/RIGHT(str,len)

-- 時間
NOW(), CURDATE(), CURTIME(), DATE_FORMAT(dt,'%Y-%m-%d'), UNIX_TIMESTAMP(), FROM_UNIXTIME(ts)
DATE_ADD(dt, INTERVAL 1 DAY), DATEDIFF(dt1,dt2), TIMESTAMPDIFF(SECOND, dt1, dt2)

-- 數值
ROUND(x,d), CEIL(x), FLOOR(x), RAND(), ABS(x), MOD(m,n), POW(m,n)

-- 流程
IF(cond, true_val, false_val), CASE WHEN cond THEN val ELSE val END, COALESCE(v1,v2...), NULLIF(v1,v2)

-- 聚合
COUNT, SUM, AVG, MAX, MIN, GROUP_CONCAT, STDDEV, VARIANCE

常見面試題 / 避坑指南

1. COUNT(*) vs COUNT(col) vs COUNT(1)：InnoDB 無 COUNT(*) 優化，全掃描；COUNT(col) 忽略 NULL。
2. LIMIT 1000000, 10 深分頁優化：WHERE id > last_id LIMIT 10 (游標分頁)。
3. NULL 比較：IS NULL/IS NOT NULL，= NULL 永遠為假。
4. utf8 vs utf8mb4：前者僅支援 BMP (3 字節)，後者完整 Unicode (4 字節)，生產必用 utf8mb4。
5. 外鍵：InnoDB 支援，MyISAM 不支援；高並發建議應用層維護關聯，減少鎖開銷。