MySQL 基礎與常用命令總結：從啟動到索引優化的速查手冊

一、服務與連接

# Windows
net start mysql
net stop mysql

# 登入
mysql -u root -p
mysql -h localhost -u root -p dbname

二、庫/表基礎操作

SHOW DATABASES;
USE dbname;
SHOW TABLES;
SHOW COLUMNS FROM tbl;
DESC tbl;

CREATE DATABASE dbname;
CREATE TABLE tbl (id INT PRIMARY KEY, name VARCHAR(20));
DROP DATABASE dbname;
DROP TABLE tbl;
TRUNCATE TABLE tbl;

三、備份與還原

# 導出
mysqldump -u root -p dbname > backup.sql
mysqldump -u root -p dbname tbl > tbl.sql
mysqldump -u root -p -d --add-drop-table dbname > struct.sql

# 導入
mysql -u root -p dbname < backup.sql
# 或進入 mysql 後
SOURCE /path/backup.sql;

四、表結構變更

# 重命名表
ALTER TABLE old RENAME new;

# 增加列
ALTER TABLE tbl ADD COLUMN col VARCHAR(30);
ALTER TABLE tbl ADD COLUMN c1 INT, ADD COLUMN c2 VARCHAR(10);

# 刪除列
ALTER TABLE tbl DROP COLUMN col;

# 修改列名/類型
ALTER TABLE tbl CHANGE old_col new_col INT;
ALTER TABLE tbl MODIFY col VARCHAR(50);

# 索引
ALTER TABLE tbl ADD INDEX idx_col (col);
CREATE UNIQUE INDEX uk_col ON tbl (col);
DROP INDEX idx_col ON tbl;
ALTER TABLE tbl DROP INDEX idx_col;

五、查詢技巧

• 字符串拼接：SELECT CONCAT(id, ':', name) FROM tbl;
• 分頁：SELECT * FROM tbl ORDER BY id LIMIT 9,10;（偏移量從 0 開始）
• 建表去重結構：CREATE TABLE t2 SELECT * FROM t1 WHERE 1<>1;
• 複製表含數據：CREATE TABLE t2 SELECT * FROM t1;

六、MySQL 不支援的特性（舊版本）

事務、視圖、外鍵、存儲過程、觸發器（5.0+ 已支援，視版本而定）。

七、索引使用規則

• 會用到索引的操作符：< <= >= > = BETWEEN IN LIKE 'abc%'（不以萬用字元開頭）。
• 索引缺點：寫入變慢、佔磁盤、優化器負擔大。
• EXPLAIN 分析關鍵列：type（ref 最佳）、key、rows、Extra。

八、表設計優化口訣

1. 短小定長：能用 CHAR 別用 VARCHAR，能用 SMALLINT 別用 INT。
2. NOT NULL：預設非空，減少 NULL 判斷開銷。
3. ENUM/SET：枚舉值固定時優先使用，存儲極小且快。
4. 拆大字段：TEXT/BLOB 或長 VARCHAR 低頻訪問則垂直拆表。
5. 適度冗餘：高頻 JOIN 大表取小字段，冗餘換查詢速度。
6. 定期 OPTIMIZE：碎片整理，OPTIMIZE TABLE tbl;
7. PROCEDURE ANALYSE()：SELECT * FROM tbl PROCEDURE ANALYSE(); 獲取類型建議。
8. 查詢緩存：query_cache_type=1、query_cache_size=256M，適配讀多寫少場景。

九、硬件調優方向

1. 加內存（最直接）。
2. 上 SSD/NVMe，降低隨機讀寫延遲。
3. 繁忙庫分離到不同物理磁盤（非同盤不同分區）。