MySQL 基礎操作、SQL 技巧、優化實戰與服務器調優完整筆記

MySQL 從基礎到調優：系統化筆記整理

本文匯總 MySQL 表操作、索引、視圖、函數、預處理、事務、存儲過程、觸發器、常用 SQL 技巧、優化步驟、大數據導入、服務器參數調優等核心知識點，適合作為速查手冊。

第一部分：基礎操作

1. 表復制

CREATE TABLE t3 LIKE t1;           -- 復制結構
INSERT INTO t3 SELECT * FROM t1;   -- 復制數據

2. 索引管理

-- 普通索引
ALTER TABLE t1 ADD INDEX idx_name (name);
-- 唯一索引
ALTER TABLE t1 ADD UNIQUE uk_name (name);
-- 主鍵索引（需自增）
ALTER TABLE t1 ADD PRIMARY KEY (id);
ALTER TABLE t1 MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT;

-- 查看/刪除
SHOW INDEX FROM t1;
ALTER TABLE t1 DROP INDEX idx_name;
-- 刪除主鍵需先去自增
ALTER TABLE t1 MODIFY id INT UNSIGNED NOT NULL;
ALTER TABLE t1 DROP PRIMARY KEY;

3. 視圖

CREATE VIEW v_t1 AS SELECT * FROM t1 WHERE id > 4 AND id < 11;
DROP VIEW v_t1;

4. 內置函數速查

• 字符串：CONCAT, LCASE, UCASE, LENGTH, TRIM, REPLACE, SUBSTRING, REPEAT, SPACE
• 數學：BIN, CEILING, FLOOR, MAX, MIN, SQRT, RAND
• 日期：CURDATE, CURTIME, NOW, UNIX_TIMESTAMP, FROM_UNIXTIME, WEEK, YEAR, DATEDIFF

5. 預處理語句

PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE id > ?';
SET @i = 5;
EXECUTE stmt1 USING @i;
DEALLOCATE PREPARE stmt1;

6. 事務（InnoDB）

SET autocommit = 0;
DELETE FROM t1 WHERE id > 5;
ROLLBACK;  -- 或 COMMIT;
-- 保存點
SAVEPOINT p1;
INSERT ...; SAVEPOINT p2;
ROLLBACK TO p1;

7. 存儲過程/觸發器

見「MySQL 視圖、存儲過程、觸發器、游標入門實戰指南」。

8. 重置 AUTO_INCREMENT

TRUNCATE TABLE t;  -- 歸 1
-- 或
ALTER TABLE t AUTO_INCREMENT = 1;

第二部分：常用 SQL 技巧

• 正則：REGEXP '^linux'、REGEXP '@163[.,]com$'（比 LIKE 耗資源）。
• 隨機抽樣：ORDER BY RAND() LIMIT 3。
• GROUP BY WITH ROLLUP：額外輸出超級聚合行（NULL 組）。
• 位聚合：BIT_OR/BIT_AND 對二進制位做 OR/AND 統計。
• 外鍵：僅 InnoDB 支持，生產慎用，應用層維護完整性更靈活。
• HELP 系統：? contents, ? functions, ? regexp。

第三部分：SQL 優化通用步驟

1. SHOW GLOBAL STATUS LIKE 'Com_%' 統計增刪改查頻率；Innodb_rows_% 看行級操作量。
2. EXPLAIN / DESC 定位慢查詢：關注 type、key、rows、Extra（Using temporary/filesort）。
3. 索引優化：最左前綴、覆蓋索引、避免函數/運算破壞索引、LIKE 'abc%' 用索引、OR 兩側都需索引。
4. 定期 ANALYZE TABLE 更新統計、OPTIMIZE TABLE 整理碎片。

第四部分：常用 SQL 改寫優化

• 大批量導入：MyISAM DISABLE/ENABLE KEYS + LOAD DATA；InnoDB 按主鍵序 + autocommit=0 + unique_checks=0。
• 多值 INSERT：INSERT ... VALUES (),(),() 減少往返。
• GROUP BY 禁排序：ORDER BY NULL。
• 子查詢改 JOIN：NOT IN (sub) -> LEFT JOIN ... IS NULL，雙表都走索引。

第五部分：表級優化

• 垂直拆分（熱/冷列分表）、水平拆分（分庫分表/分區）。
• 中間表/視圖預聚合高頻統計查詢。

第六部分：MyISAM 表鎖

• LOCK TABLE t READ：共享鎖，可讀不可寫。
• LOCK TABLE t WRITE：排他鎖，僅持有者可讀寫。
• UNLOCK TABLES 釋放。備份用 mysqldump --single-transaction（InnoDB）避免鎖表。

第七部分：服務器參數調優

總結

MySQL 掌握路徑：基礎語法 -> 索引原理 -> EXPLAIN 分析 -> 改寫優化 -> 架構拆分 -> 參數調優。將本筆記作為工具書，遇到問題查對應章節，持續精進。