MySQL 核心操作實戰指南：從庫表管理到索引優化

MySQL 基礎操作完全手冊

本文系統梳理 MySQL 最核心、最高頻的實戰操作，涵蓋庫表管理、用戶權限、遠程連接、數據增刪改查、進階查詢、索引優化等生產環境必備技能。

一、 數據庫級操作

1. 查看數據庫

SHOW DATABASES;
-- 默認系統庫：
-- mysql：用戶權限元數據
-- test：測試用庫
-- information_schema：MySQL 內部架構信息

2. 創建數據庫（指定字符集）

-- UTF-8 編碼（推薦）
CREATE DATABASE db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

-- GBK 編碼
CREATE DATABASE db_name DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

3. 切換數據庫

USE db_name;  -- 分號可省略

二、 用戶與權限體系實戰

1. 用戶全生命周期管理

-- 創建用戶（% 表示任意 IP）
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

-- 刪除用戶
DROP USER 'username'@'%';

-- 重命名用戶
RENAME USER 'old_user'@'%' TO 'new_user'@'%';

-- 修改密碼
SET PASSWORD FOR 'username'@'%' = PASSWORD('new_password');

-- 查看當前登錄用戶
SELECT USER();

-- 列出所有用戶（去重顯示）
SELECT DISTINCT CONCAT('User: ''', user, '''@''', host, ''';') AS query FROM mysql.user;

-- 查看指定用戶權限
SHOW GRANTS FOR 'nick'@'%';

2. 權限授予與回收

-- 授權語法
GRANT 權限 ON 數據庫.表 TO '用戶'@'IP';

-- 回收權限
REVOKE 權限 ON 數據庫.表 FROM '用戶'@'IP';

常用權限對照表：

對象範圍：

• db.*：庫下所有表
• db.table：指定表
• db.proc：指定存儲過程
• *.*：所有庫所有表

主機匹配：

• user@'192.168.1.100'：僅該 IP
• user@'192.168.1.%'：IP 段通配
• user@'%'：任意 IP（默認）

三、 表結構與數據操作

1. 表結構 DDL

-- 創建表（含主鍵、自增、非空、默認值、外鍵）
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT DEFAULT 0,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 修改表結構
ALTER TABLE users MODIFY username VARCHAR(60);           -- 修改類型
ALTER TABLE users CHANGE username user_name VARCHAR(60); -- 修改名+類型
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email; -- 增加列
ALTER TABLE users MODIFY phone VARCHAR(20) AFTER username;  -- 調整列順序
ALTER TABLE users DROP PRIMARY KEY, ADD PRIMARY KEY (user_name); -- 重定義主鍵

-- 清空表（保留結構，重置自增 ID）
TRUNCATE TABLE users;

2. 數據增刪改查 (DML)

-- 插入單條
INSERT INTO users (username, email, age) VALUES ('張三', 'zhang@example.com', 25);

-- 批量插入/從查詢結果插入
INSERT INTO users_backup SELECT * FROM users WHERE age > 20;

-- 更新
UPDATE users SET age = 26 WHERE username = '張三';

-- 刪除
DELETE FROM users WHERE age < 18;

-- 條件查詢
SELECT * FROM users WHERE age BETWEEN 20 AND 30 AND username LIKE '張%';

-- 分頁、排序
SELECT * FROM users ORDER BY age DESC LIMIT 10 OFFSET 20;

-- 多表連接
SELECT u.username, d.dept_name 
FROM users u 
LEFT JOIN departments d ON u.dept_id = d.id;

-- 結果集合併
SELECT username FROM users WHERE age > 25
UNION
SELECT username FROM users WHERE dept_id = 1;

3. 表元數據查看

-- 查看建表語句
SHOW CREATE TABLE users;

-- 查看表結構
DESC users;
-- 或
SHOW COLUMNS FROM users;

-- 查看執行計劃（是否走索引）
EXPLAIN SELECT * FROM users WHERE username = '張三';

四、 核心數據類型選擇指南

五、 索引設計與優化精髓

1. 索引類型

• 主鍵索引：唯一、非空、聚簇索引（InnoDB 數據與主鍵共存）
• 唯一索引：值唯一，允許 NULL
• 普通索引：加速查詢
• 全文索引：FULLTEXT，MySQL 5.6+ InnoDB 支持
• 組合索引：多列聯合，遵循最左前綴原則

2. 索引設計黃金法則

1. 最左前綴匹配：INDEX(a,b,c)，查詢 a=1 AND b=2 AND c>3 用到 a,b；c>3 是範圍查詢，d 無法用到索引
2. = 和 IN 可亂序：優化器自動調整順序匹配索引
3. 高區分度優先：COUNT(DISTINCT col)/COUNT(*) > 0.1 適合建索引，性別/狀態等低區分度列不宜
4. 列參與計算失效：FROM_UNIXTIME(create_time) = '2024-01-01' ❌ → create_time = UNIX_TIMESTAMP('2024-01-01') ✅
5. 擴展索引優於新建：已有 INDEX(a)，需 (a,b) 時直接 ALTER TABLE ... DROP INDEX a, ADD INDEX(a,b)

3. 索引類型選擇

• B+Tree：默認，支持精確匹配、範圍查詢、LIKE 'prefix%'
• Hash：Memory 引擎默認，僅支持 =/<=> 精確匹配，不支持範圍
• R-Tree：空間數據類型

4. 避坑指南

• 不在 WHERE 中對索引列使用函數/表達式
• 隱式類型轉換導致索引失效（字符串列查數字）
• 過度索引：寫入性能下降、優化器選擇困難、磁盤空間浪費
• 定期 ANALYZE TABLE 更新統計信息