深入MySQL的組成結構與結構化查詢語言詳解

MySQL整體架構概述

MySQL作為世界上最流行的開源關係型資料庫管理系統，採用了分層架構設計。從整體上看，MySQL主要分為以下幾個層次：

1. 連線層（Connection Layer）

連線層負責處理客戶端的連線請求，包括：

• 連線管理：建立、維護和銷燬客戶端連線
• 身份驗證：使用者名稱、密碼驗證，許可權檢查
• 執行緒管理：為每個連線分配獨立的執行緒
• 連線池管理：複用連線，減少連線開銷

2. 服務層（Service Layer）

服務層是MySQL的核心，包含了大多數MySQL功能：

• SQL介面：接收SQL語句並返回查詢結果
• 解析器：詞法分析和語法分析
• 最佳化器：查詢最佳化和執行計劃生成
• 快取：查詢快取機制
• 內建函式：日期、時間、數學、加密等函式

3. 引擎層（Engine Layer）

儲存引擎層負責資料的儲存和提取：

• 可插拔設計：支援多種儲存引擎
• 事務處理：ACID特性支援
• 鎖機制：併發控制
• 索引管理：B+樹、雜湊等索引結構

4. 儲存層（Storage Layer）

物理儲存層負責資料的持久化：

• 資料檔案：表資料儲存
• 日誌檔案：事務日誌、錯誤日誌等
• 索引檔案：索引資料儲存
• 配置檔案：系統配置資訊

MySQL核心元件詳解

聯結器（Connector）

聯結器是客戶端與MySQL伺服器通訊的橋樑：

-- 檢視當前連線狀態SHOW PROCESSLIST;-- 檢視連線相關引數SHOW VARIABLES LIKE'%connect%';-- 設定最大連線數SETGLOBAL max_connections =1000;

關鍵配置引數：

• max_connections：最大連線數
• connect_timeout：連線超時時間
• wait_timeout：空閒連線超時時間
• interactive_timeout：互動式連線超時時間

查詢快取（Query Cache）

查詢快取用於快取SELECT語句的結果：

-- 檢視查詢快取狀態SHOW VARIABLES LIKE'query_cache%';-- 檢視查詢快取統計資訊SHOW STATUS LIKE'Qcache%';-- 清理查詢快取FLUSH QUERY CACHE;RESET QUERY CACHE;

注意事項：

• MySQL 8.0已移除查詢快取功能
• 查詢快取在高併發場景下可能成為效能瓶頸
• 適用於讀多寫少的應用場景

解析器（Parser）

解析器負責SQL語句的詞法和語法分析：

詞法分析階段：

• 識別SQL關鍵字、識別符號、運算子
• 檢查語法錯誤
• 生成詞法單元序列

語法分析階段：

• 構建抽象語法樹（AST）
• 驗證SQL語句的語法正確性
• 準備後續最佳化處理

最佳化器（Optimizer）

最佳化器是MySQL的核心元件，負責生成最優執行計劃：

-- 檢視執行計劃EXPLAIN SELECT*FROM users WHERE age >25;-- 檢視詳細執行計劃EXPLAIN FORMAT=JSON SELECT*FROM users WHERE age >25;-- 檢視最佳化器追蹤資訊SET optimizer_trace='enabled=on';SELECT*FROM users WHERE age >25;SELECT*FROM information_schema.optimizer_trace;

最佳化器型別：

• 基於規則的最佳化（RBO）：根據預定義規則最佳化
• 基於成本的最佳化（CBO）：根據統計資訊估算成本

執行器（Executor）

執行器負責執行最佳化後的SQL語句：

-- 檢視執行統計資訊SHOW STATUS LIKE'Handler%';-- 檢視慢查詢日誌SHOW VARIABLES LIKE'slow_query_log%';SETGLOBAL slow_query_log ='ON';SETGLOBAL long_query_time =2;

儲存引擎體系

InnoDB儲存引擎

InnoDB是MySQL的預設儲存引擎，具有以下特點：

事務支援：

-- 檢視事務隔離級別SELECT @@transaction_isolation;-- 設定事務隔離級別SET SESSION transaction_isolation ='READ-COMMITTED';-- 事務操作示例START TRANSACTION;UPDATE accounts SET balance = balance -100WHERE id =1;UPDATE accounts SET balance = balance +100WHERE id =2;COMMIT;

行級鎖定：

-- 檢視鎖狀態SHOW ENGINE INNODB STATUS;-- 檢視鎖等待情況SELECT*FROM information_schema.innodb_locks;SELECT*FROM information_schema.innodb_lock_waits;

外部索引鍵約束：

-- 建立外部索引鍵約束CREATE TABLE orders (    id INTPRIMARY KEY,    customer_id INT,FOREIGN KEY (customer_id) REFERENCES customers(id));-- 檢視外部索引鍵約束SELECT*FROM information_schema.key_column_usage WHERE referenced_table_name ISNOT NULL;

MyISAM儲存引擎

MyISAM是MySQL早期的預設儲存引擎：

特點：

• 表級鎖定
• 不支援事務
• 支援全文索引
• 儲存空間小

-- 建立MyISAM表CREATE TABLE logs (    id INTPRIMARY KEY,    message TEXT,    created_at TIMESTAMP) ENGINE=MyISAM;-- 最佳化MyISAM表OPTIMIZE TABLE logs;-- 修復MyISAM表REPAIR TABLE logs;

Memory儲存引擎

Memory引擎將資料儲存在記憶體中：

-- 建立Memory表CREATE TABLE sessions (    session_id VARCHAR(32) PRIMARY KEY,    user_id INT,    last_activity TIMESTAMP) ENGINE=MEMORY;-- 檢視Memory引擎配置SHOW VARIABLES LIKE'max_heap_table_size';

其他儲存引擎

• Archive：用於資料歸檔
• CSV：以CSV格式儲存資料
• Federated：訪問遠端MySQL伺服器
• NDB：用於MySQL Cluster

記憶體結構深入分析

全域性記憶體區域

1. InnoDB緩衝池（Buffer Pool）

緩衝池是InnoDB最重要的記憶體結構：

-- 檢視緩衝池狀態SHOW ENGINE INNODB STATUS\G-- 檢視緩衝池配置SHOW VARIABLES LIKE'innodb_buffer_pool%';-- 配置緩衝池大小SETGLOBAL innodb_buffer_pool_size =1073741824; -- 1GB

緩衝池管理：

• LRU連結串列：最近最少使用演算法
• Free連結串列：空閒頁面管理
• Flush連結串列：髒頁管理

2. 重做日誌緩衝（Redo Log Buffer）

-- 檢視重做日誌緩衝配置SHOW VARIABLES LIKE'innodb_log_buffer_size';-- 設定重做日誌緩衝大小SETGLOBAL innodb_log_buffer_size =16777216; -- 16MB

3. 額外記憶體池（Additional Memory Pool）

-- 檢視額外記憶體池配置SHOW VARIABLES LIKE'innodb_additional_mem_pool_size';

執行緒記憶體區域

1. 排序緩衝區（Sort Buffer）

-- 檢視排序緩衝區配置SHOW VARIABLES LIKE'sort_buffer_size';-- 設定排序緩衝區大小SET SESSION sort_buffer_size =2097152; -- 2MB

2. 連線緩衝區（Join Buffer）

-- 檢視連線緩衝區配置SHOW VARIABLES LIKE'join_buffer_size';-- 設定連線緩衝區大小SET SESSION join_buffer_size =262144; -- 256KB

3. 讀緩衝區（Read Buffer）

-- 檢視讀緩衝區配置SHOW VARIABLES LIKE'read_buffer_size';SHOW VARIABLES LIKE'read_rnd_buffer_size';

磁碟結構詳解

系統表空間

系統表空間包含InnoDB資料字典和撤銷日誌：

-- 檢視系統表空間資訊SELECT*FROM information_schema.innodb_sys_tablespaces WHERE name ='innodb_system';-- 檢視系統表空間檔案SHOW VARIABLES LIKE'innodb_data_file_path';

獨立表空間

每個InnoDB表都有自己的表空間檔案：

-- 啟用獨立表空間SHOW VARIABLES LIKE'innodb_file_per_table';-- 查看錶空間資訊SELECT*FROM information_schema.innodb_sys_tablespaces;-- 查看錶空間檔案SELECT*FROM information_schema.files;

重做日誌檔案

重做日誌用於事務恢復：

-- 檢視重做日誌配置SHOW VARIABLES LIKE'innodb_log%';-- 重做日誌檔案位置和大小SHOW VARIABLES LIKE'innodb_log_group_home_dir';SHOW VARIABLES LIKE'innodb_log_file_size';SHOW VARIABLES LIKE'innodb_log_files_in_group';

撤銷日誌

撤銷日誌用於事務回滾：

-- 檢視撤銷日誌配置SHOW VARIABLES LIKE'innodb_undo%';-- 檢視撤銷日誌統計SHOW ENGINE INNODB STATUS\G

二進位制日誌

二進位制日誌用於主從複製和資料恢復：

-- 啟用二進位制日誌SHOW VARIABLES LIKE'log_bin';-- 檢視二進位制日誌檔案SHOWBINARY LOGS;-- 檢視二進位制日誌事件SHOW BINLOG EVENTS IN'mysql-bin.000001';-- 重新整理二進位制日誌FLUSH BINARY LOGS;

SQL語言體系概述

結構化查詢語言（SQL）是操作關係型資料庫的標準語言，分為四個主要部分：

SQL語言分類

1. DDL（Data Definition Language）：資料定義語言
2. DML（Data Manipulation Language）：資料操作語言
3. DQL（Data Query Language）：資料查詢語言
4. DCL（Data Control Language）：資料控制語言

SQL標準演進

• SQL-86：第一個SQL標準
• SQL-89：添加了外連線
• SQL-92：添加了新的資料型別和語法
• SQL-99：添加了正則表示式和麵向物件特性
• SQL-2003：添加了XML功能
• SQL-2008：添加了MERGE語句和視窗函式

DDL資料定義語言

DDL用於定義和管理資料庫物件的結構。

資料庫操作

建立資料庫

-- 基本建立資料庫CREATE DATABASE mydb;-- 指定字元集和排序規則CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;-- 建立資料庫時檢查是否存在CREATE DATABASE IF NOTEXISTS mydb;

修改資料庫

-- 修改資料庫字元集ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;-- 修改資料庫讀寫許可權ALTER DATABASE mydb READ ONLY=1;

刪除資料庫

-- 刪除資料庫DROP DATABASE mydb;-- 安全刪除資料庫DROP DATABASE IF EXISTS mydb;

檢視資料庫

-- 檢視所有資料庫SHOW DATABASES;-- 檢視資料庫建立語句SHOWCREATE DATABASE mydb;-- 檢視當前資料庫SELECT DATABASE();

表操作

建立表

-- 基本表建立CREATE TABLE users (    id INTPRIMARY KEY AUTO_INCREMENT,    username VARCHAR(50) NOT NULLUNIQUE,    email VARCHAR(100) NOT NULL,    password VARCHAR(255) NOT NULL,    created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,    updated_at TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP);-- 建立表時指定儲存引擎和字元集CREATE TABLE products (    id INTPRIMARY KEY AUTO_INCREMENT,    name VARCHAR(100) NOT NULL,    price DECIMAL(10,2) NOT NULL,    description TEXT,    category_id INT,    INDEX idx_category (category_id),FOREIGN KEY (category_id) REFERENCES categories(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 複製表結構CREATE TABLE users_backup LIKE users;-- 複製表結構和資料CREATE TABLE users_backup ASSELECT*FROM users;

修改表結構

-- 新增列ALTER TABLE users ADDCOLUMN phone VARCHAR(20);ALTER TABLE users ADDCOLUMN address TEXT AFTER email;-- 修改列ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30);-- 刪除列ALTER TABLE users DROPCOLUMN address;-- 新增索引ALTER TABLE users ADD INDEX idx_username (username);ALTER TABLE users ADDUNIQUE KEY uk_email (email);-- 刪除索引ALTER TABLE users DROP INDEX idx_username;-- 新增主鍵ALTER TABLE users ADDPRIMARY KEY (id);-- 刪除主鍵ALTER TABLE users DROPPRIMARY KEY;-- 新增外部索引鍵ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);-- 刪除外部索引鍵ALTER TABLE orders DROPFOREIGN KEY fk_customer;-- 修改表名ALTER TABLE users RENAME TO user_accounts;RENAME TABLE users TO user_accounts;-- 修改表引擎ALTER TABLE users ENGINE=MyISAM;-- 修改表字符集ALTER TABLE users CONVERTTOCHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

刪除表

-- 刪除表DROPTABLE users;-- 安全刪除表DROPTABLE IF EXISTS users;-- 刪除多個表DROPTABLE users, products, orders;-- 清空表資料但保留結構TRUNCATETABLE users;

查看錶資訊

-- 檢視所有表SHOW TABLES;-- 查看錶結構DESCRIBE users;DESC users;SHOW COLUMNS FROM users;-- 查看錶建立語句SHOWCREATE TABLE users;-- 查看錶狀態SHOWTABLE STATUS LIKE'users';-- 查看錶索引SHOW INDEX FROM users;

索引操作

建立索引

-- 普通索引CREATE INDEX idx_username ON users(username);-- 唯一索引CREATEUNIQUE INDEX uk_email ON users(email);-- 複合索引CREATE INDEX idx_name_age ON users(name, age);-- 字首索引CREATE INDEX idx_email_prefix ON users(email(10));-- 全文索引CREATE FULLTEXT INDEX ft_content ON articles(content);-- 空間索引CREATE SPATIAL INDEX sp_location ON places(location);

刪除索引

-- 刪除索引DROP INDEX idx_username ON users;-- 刪除主鍵索引ALTER TABLE users DROPPRIMARY KEY;-- 刪除外部索引鍵索引ALTER TABLE users DROPFOREIGN KEY fk_constraint_name;

檢視操作

建立檢視

-- 基本檢視CREATEVIEW user_summary ASSELECT id, username, email, created_atFROM usersWHERE status ='active';-- 複雜檢視CREATEVIEW order_details ASSELECT    o.id,    o.order_date,    u.username,    p.name AS product_name,    oi.quantity,    oi.priceFROM orders oJOIN users u ON o.user_id = u.idJOIN order_items oi ON o.id = oi.order_idJOIN products p ON oi.product_id = p.id;-- 可更新檢視CREATEVIEW active_users ASSELECT id, username, emailFROM usersWHERE status ='active'WITHCHECK OPTION;

修改檢視

-- 修改檢視ALTERVIEW user_summary ASSELECT id, username, email, created_at, last_loginFROM usersWHERE status ='active';-- 或者使用CREATE OR REPLACECREATEOR REPLACE VIEW user_summary ASSELECT id, username, email, created_at, last_loginFROM usersWHERE status ='active';

刪除檢視

-- 刪除檢視DROPVIEW user_summary;-- 安全刪除檢視DROPVIEW IF EXISTS user_summary;

儲存過程和函式

建立儲存過程

DELIMITER //CREATEPROCEDURE GetUserById(IN user_id INT)BEGINSELECT*FROM users WHERE id = user_id;END//DELIMITER ;-- 複雜儲存過程DELIMITER //CREATEPROCEDURE UpdateUserStatus(IN p_user_id INT,IN p_status VARCHAR(20),OUT p_result VARCHAR(50))BEGINDECLARE v_count INTDEFAULT0;SELECTCOUNT(*) INTO v_count FROM users WHERE id = p_user_id;    IF v_count >0THENUPDATE users SET status = p_status WHERE id = p_user_id;SET p_result ='Success';ELSESET p_result ='User not found';END IF;END//DELIMITER ;

建立函式

DELIMITER //CREATEFUNCTION GetUserCount() RETURNSINTREADSSQL DATADETERMINISTICBEGINDECLARE user_count INTDEFAULT0;SELECTCOUNT(*) INTO user_count FROM users;RETURN user_count;END//DELIMITER ;-- 呼叫函式SELECT GetUserCount();

觸發器

建立觸發器

-- BEFORE INSERT觸發器DELIMITER //CREATETRIGGER before_user_insertBEFORE INSERTON usersFOREACHROWBEGINSET NEW.created_at = NOW();SET NEW.updated_at = NOW();END//DELIMITER ;-- AFTER UPDATE觸發器DELIMITER //CREATETRIGGER after_user_updateAFTER UPDATEON usersFOREACHROWBEGININSERT INTO user_audit (user_id, action, old_value, new_value, changed_at)VALUES (NEW.id, 'UPDATE', OLD.username, NEW.username, NOW());END//DELIMITER ;-- BEFORE DELETE觸發器DELIMITER //CREATETRIGGER before_user_deleteBEFORE DELETEON usersFOREACHROWBEGININSERT INTO deleted_users SELECT*FROM users WHERE id = OLD.id;END//DELIMITER ;

檢視和刪除觸發器

-- 檢視觸發器SHOW TRIGGERS;-- 刪除觸發器DROPTRIGGER before_user_insert;

DML資料操作語言

DML用於對資料庫中的資料進行增刪改操作。

INSERT語句

基本插入

-- 插入單條記錄INSERT INTO users (username, email, password) VALUES ('john_doe', '[email protected]', 'password123');-- 插入多條記錄INSERT INTO users (username, email, password) VALUES('alice', '[email protected]', 'pass123'),('bob', '[email protected]', 'pass456'),('charlie', '[email protected]', 'pass789');-- 插入所有欄位INSERT INTO users VALUES(NULL, 'david', '[email protected]', 'pass000', NOW(), NOW());

高階插入

-- 插入並忽略重複記錄INSERT IGNORE INTO users (username, email, password) VALUES ('john_doe', '[email protected]', 'password123');-- 插入或更新（ON DUPLICATE KEY UPDATE）INSERT INTO users (username, email, password) VALUES ('john_doe', '[email protected]', 'new_password')ON DUPLICATE KEY UPDATE    email =VALUES(email),    password =VALUES(password),    updated_at = NOW();-- 從其他表插入INSERT INTO users_backup (username, email, password)SELECT username, email, password FROM users WHERE created_at >'2024-01-01';-- 替換插入REPLACE INTO users (id, username, email, password) VALUES (1, 'john_doe', '[email protected]', 'new_password');

UPDATE語句

基本更新

-- 更新單條記錄UPDATE users SET email ='[email protected]', updated_at = NOW() WHERE id =1;-- 更新多條記錄UPDATE users SET status ='inactive'WHERE last_login <'2024-01-01';-- 更新所有記錄UPDATE users SET updated_at = NOW();

高階更新

-- 多表更新UPDATE users uJOIN orders o ON u.id = o.user_idSET u.total_orders = u.total_orders +1WHERE o.order_date >'2024-01-01';-- 條件更新UPDATE users SET status =CASEWHEN last_login >'2024-06-01'THEN'active'WHEN last_login >'2024-01-01'THEN'inactive'ELSE'dormant'END;-- 限制更新記錄數UPDATE users SET status ='processed'WHERE status ='pending'ORDERBY created_at LIMIT 100;

DELETE語句

基本刪除

-- 刪除單條記錄DELETEFROM users WHERE id =1;-- 刪除多條記錄DELETEFROM users WHERE status ='inactive';-- 刪除所有記錄DELETEFROM users;

高階刪除

-- 多表刪除DELETE u FROM users uJOIN orders o ON u.id = o.user_idWHERE o.order_date <'2020-01-01';-- 限制刪除記錄數DELETEFROM users WHERE status ='spam'ORDERBY created_at LIMIT 1000;-- 安全刪除（使用事務）START TRANSACTION;DELETEFROM users WHERE id =1;-- 檢查結果SELECT ROW_COUNT();COMMIT; -- 或 ROLLBACK;

DQL資料查詢語言

DQL是SQL中最複雜也是最常用的部分，用於從資料庫中檢索資料。

基本SELECT語句

簡單查詢

-- 查詢所有欄位SELECT*FROM users;-- 查詢指定欄位SELECT id, username, email FROM users;-- 使用別名SELECT    id AS user_id,    username AS login_name,    email AS email_addressFROM users;-- 去重查詢SELECTDISTINCT status FROM users;-- 限制結果數量SELECT*FROM users LIMIT 10;SELECT*FROM users LIMIT 10, 20; -- 跳過10條，取20條

WHERE條件查詢

基本條件

-- 等值查詢SELECT*FROM users WHERE status ='active';-- 不等值查詢SELECT*FROM users WHERE status !='inactive';SELECT*FROM users WHERE status <>'inactive';-- 數值比較SELECT*FROM users WHERE age >25;SELECT*FROM users WHERE age BETWEEN18AND65;-- 模糊查詢SELECT*FROM users WHERE username LIKE'john%';SELECT*FROM users WHERE email LIKE'%@gmail.com';-- 空值查詢SELECT*FROM users WHERE last_login ISNULL;SELECT*FROM users WHERE last_login ISNOT NULL;-- 列表查詢SELECT*FROM users WHERE status IN ('active', 'pending');SELECT*FROM users WHERE id NOTIN (1, 2, 3);

複合條件

-- 邏輯ANDSELECT*FROM users WHERE status ='active'AND age >25;-- 邏輯ORSELECT*FROM users WHERE status ='active'OR status ='pending';-- 複雜條件組合SELECT*FROM users WHERE (status ='active'OR status ='pending') AND age BETWEEN18AND65AND email LIKE'%@gmail.com';

排序和分組

ORDER BY排序

-- 升序排序SELECT*FROM users ORDERBY created_at ASC;-- 降序排序SELECT*FROM users ORDERBY created_at DESC;-- 多欄位排序SELECT*FROM users ORDERBY status ASC, created_at DESC;-- 使用表示式排序SELECT*FROM users ORDERBYCHAR_LENGTH(username) DESC;

GROUP BY分組

-- 基本分組SELECT status, COUNT(*) as user_countFROM users GROUPBY status;-- 多欄位分組SELECT status, DATE(created_at) asdate, COUNT(*) as countFROM users GROUPBY status, DATE(created_at);-- 分組後過濾SELECT status, COUNT(*) as user_countFROM users GROUPBY statusHAVINGCOUNT(*) >10;-- 分組與排序SELECT status, COUNT(*) as user_countFROM users GROUPBY statusORDERBY user_count DESC;

聚合函式

常用聚合函式

-- 計數SELECTCOUNT(*) FROM users;SELECTCOUNT(DISTINCT status) FROM users;-- 求和SELECTSUM(order_amount) FROM orders;-- 平均值SELECTAVG(age) FROM users;-- 最大值和最小值SELECTMAX(created_at), MIN(created_at) FROM users;-- 字串連線SELECT GROUP_CONCAT(username) FROM users;SELECT GROUP_CONCAT(username SEPARATOR ', ') FROM users;

視窗函式（MySQL 8.0+）

-- 行號SELECT    username,    email,ROW_NUMBER() OVER (ORDERBY created_at) as rnFROM users;-- 排名SELECT    username,    age,RANK() OVER (ORDERBY age DESC) as rank,DENSE_RANK() OVER (ORDERBY age DESC) as dense_rankFROM users;-- 分組排名SELECT    username,    department,    salary,RANK() OVER (PARTITIONBY department ORDERBY salary DESC) as dept_rankFROM employees;-- 累計求和SELECT    order_date,    amount,SUM(amount) OVER (ORDERBY order_date) as running_totalFROM orders;-- 移動平均SELECT    order_date,    amount,AVG(amount) OVER (ORDERBY order_date ROWSBETWEEN2 PRECEDING ANDCURRENTROW) as ma3FROM orders;

表連線

內連線（INNER JOIN）

-- 基本內連線SELECT u.username, p.titleFROM users uINNERJOIN posts p ON u.id = p.user_id;-- 多表內連線SELECT    u.username,    p.title,    c.name as category_nameFROM users uINNERJOIN posts p ON u.id = p.user_idINNERJOIN categories c ON p.category_id = c.id;

外連線（OUTER JOIN）

-- 左外連線SELECT u.username, p.titleFROM users uLEFTJOIN posts p ON u.id = p.user_id;-- 右外連線SELECT u.username, p.titleFROM users uRIGHTJOIN posts p ON u.id = p.user_id;-- 全外連線（MySQL不直接支援，需要用UNION）SELECT u.username, p.titleFROM users uLEFTJOIN posts p ON u.id = p.user_idUNIONSELECT u.username, p.titleFROM users uRIGHTJOIN posts p ON u.id = p.user_id;

自連線

-- 查詢同部門的員工SELECT    e1.name as employee,    e2.name as colleagueFROM employees e1JOIN employees e2 ON e1.department_id = e2.department_idWHERE e1.id != e2.id;-- 查詢員工及其直接上級SELECT    e.name as employee,    m.name as managerFROM employees eLEFTJOIN employees m ON e.manager_id = m.id;

子查詢

標量子查詢

-- 查詢年齡大於平均年齡的使用者SELECT*FROM users WHERE age > (SELECTAVG(age) FROM users);-- 查詢最新訂單的使用者SELECT*FROM users WHERE id = (SELECT user_id FROM orders ORDERBY created_at DESC LIMIT 1);

列子查詢

-- 查詢有訂單的使用者SELECT*FROM users WHERE id IN (SELECTDISTINCT user_id FROM orders);-- 查詢沒有訂單的使用者SELECT*FROM users WHERE id NOTIN (SELECT user_id FROM orders WHERE user_id ISNOT NULL);

行子查詢

-- 查詢特定使用者的訂單資訊SELECT*FROM orders WHERE (user_id, order_date) IN (SELECT user_id, MAX(order_date) FROM orders GROUPBY user_id);

表子查詢

-- 使用子查詢作為臨時表SELECT user_stats.username, user_stats.order_countFROM (SELECT        u.username,COUNT(o.id) as order_countFROM users uLEFTJOIN orders o ON u.id = o.user_idGROUPBY u.id, u.username) as user_statsWHERE user_stats.order_count >5;

高階查詢技巧

公用表表達式（CTE）- MySQL 8.0+

-- 基本CTEWITH active_users AS (SELECT id, username, email FROM users WHERE status ='active')SELECT*FROM active_users WHERE email LIKE'%@gmail.com';-- 遞迴CTEWITHRECURSIVE employee_hierarchy AS (-- 基礎查詢：頂級員工SELECT id, name, manager_id, 0as levelFROM employeesWHERE manager_id ISNULLUNIONALL-- 遞迴查詢：下級員工SELECT e.id, e.name, e.manager_id, eh.level +1FROM employees eJOIN employee_hierarchy eh ON e.manager_id = eh.id)SELECT*FROM employee_hierarchy ORDERBY level, name;

案例表示式

-- CASE WHENSELECT    username,    age,CASEWHEN age <18THEN'未成年'WHEN age BETWEEN18AND65THEN'成年'ELSE'老年'ENDas age_groupFROM users;-- 簡單CASESELECT    username,    status,CASE statusWHEN'active'THEN'活躍'WHEN'inactive'THEN'不活躍'ELSE'未知'ENDas status_descFROM users;

DCL資料控制語言

DCL用於控制資料庫的訪問許可權和安全性。

使用者管理

建立使用者

-- 建立使用者CREATEUSER'newuser'@'localhost' IDENTIFIED BY'password123';-- 建立使用者並指定主機CREATEUSER'webuser'@'192.168.1.%' IDENTIFIED BY'webpass';CREATEUSER'appuser'@'%' IDENTIFIED BY'apppass';-- 建立使用者時指定密碼策略CREATEUSER'secureuser'@'localhost'IDENTIFIED BY'SecurePass123!'PASSWORD EXPIRE INTERVAL90DAY;

修改使用者

-- 修改使用者密碼ALTERUSER'newuser'@'localhost' IDENTIFIED BY'newpassword';-- 修改當前使用者密碼ALTERUSERUSER() IDENTIFIED BY'newpassword';-- 設定密碼過期ALTERUSER'newuser'@'localhost' PASSWORD EXPIRE;-- 鎖定使用者ALTERUSER'newuser'@'localhost' ACCOUNT LOCK;-- 解鎖使用者ALTERUSER'newuser'@'localhost' ACCOUNT UNLOCK;

刪除使用者

-- 刪除使用者DROPUSER'newuser'@'localhost';-- 刪除多個使用者DROPUSER'user1'@'localhost', 'user2'@'localhost';

檢視使用者

-- 檢視所有使用者SELECTuser, host FROM mysql.user;-- 檢視當前使用者SELECTUSER(), CURRENT_USER();-- 檢視使用者許可權SHOW GRANTS FOR'newuser'@'localhost';SHOW GRANTS FORCURRENT_USER();

許可權管理

授予許可權

-- 授予資料庫所有許可權GRANTALL PRIVILEGES ON mydb.*TO'newuser'@'localhost';-- 授予特定表的許可權GRANTSELECT, INSERT, UPDATEON mydb.users TO'newuser'@'localhost';-- 授予特定列的許可權GRANTSELECT (id, username), UPDATE (email) ON mydb.users TO'newuser'@'localhost';-- 授予儲存過程許可權GRANTEXECUTEONPROCEDURE mydb.GetUserById TO'newuser'@'localhost';-- 授予全域性許可權GRANT REPLICATION SLAVE ON*.*TO'repl_user'@'%';-- 授予許可權並允許授權給其他使用者GRANTSELECTON mydb.*TO'newuser'@'localhost'WITHGRANT OPTION;

許可權型別詳解

-- 資料操作許可權GRANTSELECTON mydb.*TO'readonly'@'localhost';GRANTINSERTON mydb.*TO'insert_user'@'localhost';GRANTUPDATEON mydb.*TO'update_user'@'localhost';GRANTDELETEON mydb.*TO'delete_user'@'localhost';-- 結構操作許可權GRANTCREATEON mydb.*TO'dev_user'@'localhost';GRANTALTERON mydb.*TO'admin_user'@'localhost';GRANTDROPON mydb.*TO'admin_user'@'localhost';GRANT INDEX ON mydb.*TO'dba_user'@'localhost';-- 管理許可權GRANT PROCESS ON*.*TO'monitor_user'@'localhost';GRANT RELOAD ON*.*TO'backup_user'@'localhost';GRANT REPLICATION CLIENT ON*.*TO'repl_monitor'@'localhost';

回收許可權

-- 回收特定許可權REVOKESELECTON mydb.users FROM'newuser'@'localhost';-- 回收所有許可權REVOKEALL PRIVILEGES ON mydb.*FROM'newuser'@'localhost';-- 回收授權許可權REVOKEGRANT OPTION ON mydb.*FROM'newuser'@'localhost';

重新整理許可權

-- 重新整理許可權快取FLUSH PRIVILEGES;

角色管理（MySQL 8.0+）

建立角色

-- 建立角色CREATE ROLE 'app_developer', 'app_read', 'app_write';-- 授予角色許可權GRANTSELECT, INSERT, UPDATEON mydb.*TO'app_developer';GRANTSELECTON mydb.*TO'app_read';GRANTINSERT, UPDATEON mydb.*TO'app_write';-- 將角色授予使用者GRANT'app_developer'TO'dev_user'@'localhost';GRANT'app_read'TO'readonly_user'@'localhost';-- 設定預設角色SETDEFAULT ROLE 'app_developer'TO'dev_user'@'localhost';

角色繼承

-- 建立角色層次結構CREATE ROLE 'base_role', 'advanced_role', 'admin_role';-- 基礎角色許可權GRANTSELECTON mydb.*TO'base_role';-- 高階角色繼承基礎角色GRANT'base_role'TO'advanced_role';GRANTINSERT, UPDATEON mydb.*TO'advanced_role';-- 管理員角色繼承高階角色GRANT'advanced_role'TO'admin_role';GRANTDELETE, CREATE, ALTERON mydb.*TO'admin_role';

安全配置

密碼策略

-- 檢視密碼策略SHOW VARIABLES LIKE'validate_password%';-- 設定密碼策略SETGLOBAL validate_password.policy ='STRONG';SETGLOBAL validate_password.length =12;SETGLOBAL validate_password.mixed_case_count =2;SETGLOBAL validate_password.number_count =2;SETGLOBAL validate_password.special_char_count =2;

連線限制

-- 建立使用者時設定連線限制CREATEUSER'limited_user'@'localhost'IDENTIFIED BY'password'WITH MAX_CONNECTIONS_PER_HOUR 100MAX_QUERIES_PER_HOUR 1000MAX_UPDATES_PER_HOUR 100MAX_USER_CONNECTIONS 5;-- 修改使用者連線限制ALTERUSER'limited_user'@'localhost'WITH MAX_CONNECTIONS_PER_HOUR 50;

SSL配置

-- 要求SSL連線CREATEUSER'secure_user'@'%'IDENTIFIED BY'password'REQUIRE SSL;-- 要求特定的SSL證書CREATEUSER'cert_user'@'%'IDENTIFIED BY'password'REQUIRE X509;-- 要求特定的SSL金鑰CREATEUSER'key_user'@'%'IDENTIFIED BY'password'REQUIRE SUBJECT '/C=US/ST=CA/L=San Francisco/O=MyOrg/CN=MyName';

效能最佳化與運維實踐

查詢最佳化

執行計劃分析

-- 基本執行計劃EXPLAIN SELECT*FROM users WHERE age >25;-- 詳細執行計劃EXPLAIN FORMAT=JSON SELECT*FROM users WHERE age >25;-- 檢視實際執行統計EXPLAIN ANALYZE SELECT*FROM users WHERE age >25;

索引最佳化策略

-- 建立合適的索引CREATE INDEX idx_age ON users(age);CREATE INDEX idx_status_created ON users(status, created_at);-- 檢視索引使用情況SHOW INDEX FROM users;-- 分析索引效率SELECT    table_name,    index_name,cardinality,    nullableFROM information_schema.statistics WHERE table_schema ='mydb';

監控與維護

效能監控

-- 檢視慢查詢SHOW VARIABLES LIKE'slow_query_log%';SHOW STATUS LIKE'Slow_queries';-- 檢視連線狀態SHOW STATUS LIKE'Connections';SHOW STATUS LIKE'Threads_%';-- 檢視快取命中率SHOW STATUS LIKE'Key_read%';SHOW STATUS LIKE'Innodb_buffer_pool_read%';-- 檢視鎖等待SHOW STATUS LIKE'Innodb_row_lock_%';

表維護

-- 分析表ANALYZE TABLE users;-- 最佳化表OPTIMIZE TABLE users;-- 檢查表CHECKTABLE users;-- 修復表REPAIR TABLE users;

備份與恢復

邏輯備份

# 備份單個數據庫mysqldump -u root -p mydb > mydb_backup.sql# 備份所有資料庫mysqldump -u root -p --all-databases > all_databases_backup.sql# 備份表結構mysqldump -u root -p --no-data mydb > mydb_structure.sql# 恢復資料庫mysql -u root -p mydb < mydb_backup.sql

物理備份

-- 建立備份目錄SETGLOBAL local_infile =1;-- 匯出資料到檔案SELECT*INTO OUTFILE '/tmp/users_backup.csv'FIELDS TERMINATED BY','LINES TERMINATED BY'\n'FROM users;-- 從檔案匯入資料LOAD DATA INFILE '/tmp/users_backup.csv'INTOTABLE usersFIELDS TERMINATED BY','LINES TERMINATED BY'\n';

主從複製配置

主庫配置

-- 啟用二進位制日誌SETGLOBAL log_bin ='mysql-bin';-- 建立複製使用者CREATEUSER'repl'@'slave_host' IDENTIFIED BY'repl_password';GRANT REPLICATION SLAVE ON*.*TO'repl'@'slave_host';-- 檢視主庫狀態SHOW MASTER STATUS;

從庫配置

-- 配置主庫資訊CHANGE MASTER TO    MASTER_HOST='master_host',    MASTER_USER='repl',    MASTER_PASSWORD='repl_password',    MASTER_LOG_FILE='mysql-bin.000001',    MASTER_LOG_POS=154;-- 啟動複製START SLAVE;-- 檢視從庫狀態SHOW SLAVE STATUS\G;