MySQL 使用大全

本文档基于 MySQL 8.0 官方参考手册整理，涵盖 MySQL 的核心功能和使用方法。

---

目录

1. 基础查询 (SELECT)
2. 数据操作 (INSERT/UPDATE/DELETE)
3. [JOIN 连接操作](#3-join 连接操作)
4. 索引 (Indexes)
5. 事务 (Transactions)
6. 存储过程和函数
7. 视图 (Views)
8. 触发器 (Triggers)
9. 数据库设计最佳实践
10. 性能优化

---

1. 基础查询 (SELECT)

1.1 基本 SELECT 语法

-- 基本查询
SELECT column1, column2 FROM table_name;

-- 使用别名
SELECT column1 AS alias_name FROM table_name;

-- 使用 WHERE 条件
SELECT * FROM citizen
WHERE income/dependents > 10000 AND age > 30;

-- 使用函数
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM citizen
WHERE age > 30;

1.2 ORDER BY 排序

-- 单列排序
SELECT * FROM employees ORDER BY salary DESC;

-- 多列排序
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

1.3 LIMIT 限制结果

-- 限制返回行数
SELECT * FROM employees LIMIT 10;

-- 分页查询
SELECT * FROM employees LIMIT 10 OFFSET 20;

1.4 GROUP BY 分组

-- 基本分组
SELECT student_name, COUNT(*) AS course_count
FROM student
GROUP BY student_name;

-- 多列分组
SELECT country, year, SUM(profit) AS total_profit
FROM sales
GROUP BY country, year;

1.5 HAVING 过滤分组

-- HAVING 与聚合函数配合使用
SELECT user, MAX(salary) AS max_salary
FROM users
GROUP BY user 
HAVING MAX(salary) > 10;

-- 多个 HAVING 条件
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000 AND COUNT(*) > 5;

1.6 聚合函数

-- COUNT: 计数
SELECT COUNT(*) FROM employees;
SELECT COUNT(column_name) FROM table_name;

-- SUM: 求和
SELECT SUM(profit) AS total_profit FROM sales;

-- AVG: 平均值
SELECT student_name, AVG(test_score) AS avg_score
FROM student
GROUP BY student_name;

-- MIN/MAX: 最小值/最大值
SELECT MIN(salary), MAX(salary) FROM employees;

-- 组合使用
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department;

1.7 子查询

-- WHERE 子句中的子查询
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- SELECT 中的子查询
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) AS order_count
FROM customers;

-- EXISTS 子查询
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

---

2. 数据操作 (INSERT/UPDATE/DELETE)

2.1 INSERT 语句

-- 基本插入
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- 使用 SET 语法
INSERT INTO table_name SET column1 = value1, column2 = value2;

-- 多行插入
INSERT INTO yourtable (a, b, c)
VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);

-- 从其他表插入
INSERT INTO tbl_temp2 (fld_id)
SELECT fld_order_id FROM tbl_temp1
WHERE fld_order_id > 100;

-- ON DUPLICATE KEY UPDATE
INSERT INTO users (id, email, name)
VALUES (1, 'test@example.com', 'Test')
ON DUPLICATE KEY UPDATE name = VALUES(name);

2.2 REPLACE 语句

-- REPLACE 语法（先删除再插入）
REPLACE INTO table_name (column1, column2)
VALUES (value1, value2);

-- REPLACE ... SELECT
REPLACE INTO table_name (column1, column2)
SELECT columnA, columnB FROM another_table;

2.3 UPDATE 语句

-- 基本更新
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';

-- 多列更新
UPDATE employees
SET salary = 50000, title = 'Manager'
WHERE id = 123;

-- 使用子查询更新
UPDATE employees e
SET salary = (
    SELECT AVG(salary) FROM employees WHERE department = e.department
)
WHERE department = 'Engineering';

2.4 DELETE 语句

-- 基本删除
DELETE FROM employees WHERE id = 123;

-- 带条件的删除
DELETE FROM orders
WHERE order_date < '2023-01-01' AND status = 'cancelled';

-- 限制删除数量
DELETE FROM logs LIMIT 1000;

-- JOIN 删除
DELETE t1 FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.table1_id
WHERE t2.status = 'deleted';

---

3. JOIN 连接操作

3.1 INNER JOIN

-- 基本 INNER JOIN
SELECT * FROM t1
JOIN t2 ON t1.c1 = t2.c1;

-- 多表连接
SELECT o.order_id, c.name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id;

-- 跨数据库连接
SELECT c1, c2, t1.c 
FROM db1.t AS t1 
INNER JOIN db2.t AS t2
WHERE t2.c > 100;

3.2 LEFT/RIGHT JOIN

-- LEFT JOIN
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

-- RIGHT JOIN
SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

3.3 JOIN 语法规范

-- 完整语法
_table_references_:
    _escaped_table_reference_ [, _escaped_table_reference_] ...

_table_reference_: {
    _table_factor_
  | _joined_table_
}

_joined_table_: {
    _table_reference_ {[INNER | CROSS] JOIN | STRAIGHT_JOIN} _table_factor_ [_join_specification_]
  | _table_reference_ {LEFT|RIGHT} [OUTER] JOIN _table_reference_ _join_specification_
  | _table_reference_ NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN _table_factor_
}

_join_specification_: {
    ON _search_condition_
  | USING (_join_column_list_)
}

3.4 索引提示

-- USE INDEX 提示
SELECT * FROM t1 USE INDEX (idx_name) WHERE col = 1;

-- FORCE INDEX 强制使用
SELECT * FROM t1 FORCE INDEX (idx_name) WHERE col = 1;

-- IGNORE INDEX 忽略索引
SELECT * FROM t1 IGNORE INDEX (idx_name) WHERE col = 1;

-- 指定用途的索引提示
SELECT * FROM t1 USE INDEX (idx_name) FOR ORDER BY
WHERE col = 1 ORDER BY col;

---

4. 索引 (Indexes)

4.1 索引类型

MySQL 支持以下索引类型：

• B-Tree 索引: 默认索引类型，适用于大多数场景
• Fulltext 索引: 用于全文搜索
• Spatial 索引: 用于空间数据（R-Tree）
• Hash 索引: MEMORY 引擎支持

4.2 CREATE INDEX 语法

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option:
  KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

4.3 创建索引示例

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);

-- 复合索引
CREATE INDEX idx_lastname_firstname 
ON employees (lastname, firstname DESC);

-- 前缀索引
CREATE INDEX idx_name_prefix ON users (name(10));

-- 表达式索引 (MySQL 8.0.13+)
CREATE INDEX idx_lower_email ON users (LOWER(email));

-- Fulltext 索引
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
) ENGINE=InnoDB;

-- Spatial 索引
CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);
CREATE SPATIAL INDEX g ON geom (g);

4.4 在 CREATE TABLE 中定义索引

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    department_id INT,
    salary DECIMAL(10,2),
    INDEX idx_department (department_id),
    INDEX idx_salary (salary),
    FULLTEXT INDEX idx_name (name)
) ENGINE=InnoDB;

4.5 JSON 索引

-- 为 JSON 字段创建生成列索引
CREATE TABLE jemp (
    c JSON,
    g INT GENERATED ALWAYS AS (c->"$.id"),
    INDEX i (g)
);

INSERT INTO jemp (c) VALUES
    ('{"id": "1", "name": "Fred"}'),
    ('{"id": "2", "name": "Wilma"}');

-- 查询时自动使用索引
SELECT c->>"$.name" AS name FROM jemp WHERE g > 2;

4.6 使用 EXPLAIN 分析查询

-- 基本 EXPLAIN
EXPLAIN SELECT * FROM employees WHERE department_id = 1;

-- 格式化输出
EXPLAIN SELECT * FROM employees WHERE department_id = 1\G

-- 查看实际执行计划
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 1;

4.7 索引优化技巧

-- 利用索引进行 MIN/MAX 查询
SELECT MIN(key_part2), MAX(key_part2)
FROM tbl_name WHERE key_part1 = 10;

-- 覆盖索引查询（只查询索引列）
SELECT id, name FROM users WHERE id = 1;

-- 避免索引失效
-- 不要对索引列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 失效
SELECT * FROM users WHERE created_at >= '2023-01-01' 
                     AND created_at < '2024-01-01'; -- 有效

---

5. 事务 (Transactions)

5.1 ACID 特性

MySQL InnoDB 引擎支持 ACID 特性：

• 原子性 (Atomicity): 通过 autocommit、COMMIT、ROLLBACK 实现
• 一致性 (Consistency): 通过约束、触发器等保证
• 隔离性 (Isolation): 通过事务隔离级别实现
• 持久性 (Durability): 通过 redo log 保证

5.2 基本事务控制

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 自动提交设置
SET autocommit = 0;  -- 关闭自动提交
SET autocommit = 1;  -- 开启自动提交

5.3 Savepoint 保存点

START TRANSACTION;

INSERT INTO accounts VALUES (1, 1000);
SAVEPOINT sp1;

INSERT INTO accounts VALUES (2, 2000);
SAVEPOINT sp2;

-- 回滚到 sp1，保留第一个插入
ROLLBACK TO SAVEPOINT sp1;

-- 释放保存点
RELEASE SAVEPOINT sp1;

COMMIT;

5.4 事务隔离级别

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 四种隔离级别（从低到高）
-- READ UNCOMMITTED: 读未提交
-- READ COMMITTED: 读已提交
-- REPEATABLE READ: 可重复读（MySQL 默认）
-- SERIALIZABLE: 串行化

-- 为单个事务设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ... 事务操作
COMMIT;

5.5 读写模式

-- 只读事务
SET TRANSACTION READ ONLY;
START TRANSACTION;
-- 只能执行 SELECT 操作

-- 读写事务
SET TRANSACTION READ WRITE;
START TRANSACTION;
-- 可以执行所有操作

5.6 锁机制

-- 共享锁（读锁）
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- 或
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（写锁）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- 跳过锁等待
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;

-- 等待锁超时
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

5.7 死锁检测

-- 查询阻塞信息
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

-- 查看所有活跃事务
SELECT * FROM information_schema.innodb_trx;

---

6. 存储过程和函数

6.1 CREATE PROCEDURE 语法

CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...]
    routine_body

proc_parameter:
    [IN | OUT | INOUT] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
  | SQL SECURITY {DEFINER | INVOKER}

6.2 存储过程示例

DELIMITER //

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  
  -- 声明游标
  DECLARE cur1 CURSOR FOR SELECT id, data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  
  -- 异常处理器
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    
    IF done THEN
      LEAVE read_loop;
    END IF;
    
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a, b);
    ELSE
      INSERT INTO test.t3 VALUES (a, c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END //

DELIMITER ;

6.3 存储函数示例

DELIMITER //

CREATE FUNCTION SimpleCompare(n INT, m INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE s VARCHAR(20);

    IF n > m THEN 
        SET s = '>';
    ELSEIF n = m THEN 
        SET s = '=';
    ELSE 
        SET s = '<';
    END IF;

    SET s = CONCAT(n, ' ', s, ' ', m);
    RETURN s;
END //

DELIMITER ;

-- 调用函数
SELECT SimpleCompare(5, 10);  -- 返回 '5 < 10'

6.4 控制流语句

-- IF 语句
IF condition THEN
    -- statements
ELSEIF condition THEN
    -- statements
ELSE
    -- statements
END IF;

-- CASE 语句
CASE expression
    WHEN value THEN statement
    WHEN value THEN statement
    ELSE statement
END CASE;

-- 或
CASE
    WHEN condition THEN statement
    WHEN condition THEN statement
    ELSE statement
END CASE;

-- LOOP
loop_label: LOOP
    -- statements
    IF condition THEN
        LEAVE loop_label;
    END IF;
END LOOP;

-- WHILE
WHILE condition DO
    -- statements
END WHILE;

-- REPEAT
REPEAT
    -- statements
UNTIL condition
END REPEAT;

6.5 变量和游标

-- 用户变量
SET @my_var = 100;
SELECT @my_var;

-- 会话变量
SET @total := (SELECT SUM(salary) FROM employees);

-- 局部变量（存储过程中）
DECLARE counter INT DEFAULT 0;
DECLARE message VARCHAR(100);

-- 游标操作
DECLARE cur CURSOR FOR SELECT name FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
FETCH cur INTO var_name;
CLOSE cur;

---

7. 视图 (Views)

7.1 CREATE VIEW 语法

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

7.2 创建视图示例

-- 基本视图
CREATE VIEW v AS SELECT * FROM t;

-- 带计算列的视图
CREATE TABLE t (qty INT, price INT);
INSERT INTO t VALUES(3, 50), (5, 60);

CREATE VIEW v AS 
SELECT qty, price, qty * price AS value 
FROM t;

SELECT * FROM v;
-- 结果:
-- +------+-------+-------+
-- | qty  | price | value |
-- +------+-------+-------+
-- |    3 |    50 |   150 |
-- |    5 |    60 |   300 |
-- +------+-------+-------+

-- 带列名的视图
CREATE VIEW employee_summary (emp_name, dept, annual_salary)
AS 
SELECT CONCAT(first_name, ' ', last_name), department, salary * 12
FROM employees;

-- 安全上下文视图
CREATE VIEW sensitive_data
SQL SECURITY INVOKER
AS SELECT * FROM salaries;

7.3 修改和删除视图

-- 修改视图
ALTER VIEW v 
AS SELECT qty, price, qty * price * 1.1 AS value_with_tax FROM t;

-- 删除视图
DROP VIEW v;

-- 条件删除
DROP VIEW IF EXISTS v;

-- 删除多个视图
DROP VIEW v1, v2, v3;

7.4 可更新视图规则

-- 可更新的视图
CREATE VIEW vup AS SELECT * FROM t2;
UPDATE vup SET c = 10 WHERE c = 5;

-- 不可更新的视图（包含聚合函数）
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
-- UPDATE vmat ... 会失败

-- 使用 WITH CHECK OPTION
CREATE VIEW active_users AS 
SELECT * FROM users WHERE status = 'active'
WITH CHECK OPTION;

-- 插入不符合条件的行会失败
INSERT INTO active_users (id, status) VALUES (1, 'inactive'); -- 错误

---

8. 触发器 (Triggers)

8.1 触发器语法

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
trigger_body

trigger_time: BEFORE | AFTER
trigger_event: INSERT | UPDATE | DELETE

8.2 触发器示例

DELIMITER |

-- INSERT 触发器
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|

DELIMITER ;

-- 审计触发器（记录修改历史）
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(50),
    operation VARCHAR(10),
    old_value TEXT,
    new_value TEXT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER |

CREATE TRIGGER audit_update AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, old_value, new_value)
    VALUES (
        'employees',
        'UPDATE',
        CONCAT('salary:', OLD.salary),
        CONCAT('salary:', NEW.salary)
    );
END;
|

DELIMITER ;

-- 防止删除触发器
DELIMITER |

CREATE TRIGGER prevent_delete BEFORE DELETE ON important_table
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Deletion is not allowed on this table';
END;
|

DELIMITER ;

8.3 NEW 和 OLD 限定词

• NEW: 引用即将插入或更新后的新行（仅可用于 BEFORE/AFTER INSERT 和 UPDATE 触发器）
• OLD: 引用即将删除或更新前的旧行（仅可用于 BEFORE/AFTER DELETE 和 UPDATE 触发器）

-- 在 BEFORE 触发器中可以修改 NEW 值
CREATE TRIGGER set_default_before BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.hire_date IS NULL THEN
        SET NEW.hire_date = CURDATE();
    END IF;
END;

8.4 删除触发器

DROP TRIGGER IF EXISTS testref;

---

9. 数据库设计最佳实践

9.1 外键约束

-- 定义外键
CONSTRAINT [symbol] FOREIGN KEY [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

9.2 创建父子表示例

-- 父表
CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

-- 子表
CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=INNODB;

-- 使用 ALTER TABLE 添加外键
ALTER TABLE child
ADD CONSTRAINT fk_parent
FOREIGN KEY (parent_id) REFERENCES parent(id);

9.3 约束类型

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,           -- 主键约束
    email VARCHAR(100) UNIQUE,                   -- 唯一约束
    name VARCHAR(100) NOT NULL,                  -- 非空约束
    age INT CHECK (age >= 18 AND age <= 65),     -- 检查约束 (MySQL 8.0.16+)
    department_id INT,
    status VARCHAR(20) DEFAULT 'active',         -- 默认值
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_department 
        FOREIGN KEY (department_id) REFERENCES departments(id)
);

9.4 数据类型选择

数据类型	用途	说明
TINYINT	小整数	-128 到 127
SMALLINT	中等整数	-32768 到 32767
MEDIUMINT	较大整数	-8388608 到 8388607
INT	标准整数	-2147483648 到 2147483647
BIGINT	大整数	64 位整数
DECIMAL	精确小数	金融计算
FLOAT/DOUBLE	浮点数	科学计算
DATE	日期	YYYY-MM-DD
DATETIME	日期时间	YYYY-MM-DD HH:MM:SS
TIMESTAMP	时间戳	自动更新选项
VARCHAR(n)	变长字符串	最大 n 字符
TEXT	大文本	最多 65535 字节
LONGTEXT	超大文本	最多 4GB
JSON	JSON 数据	MySQL 5.7+

9.5 表分区

-- 范围分区
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- 列表分区
CREATE TABLE stores (
    id INT,
    city VARCHAR(50)
) PARTITION BY LIST COLUMNS(city) (
    PARTITION p_ny VALUES IN ('New York'),
    PARTITION p_ca VALUES IN ('Los Angeles', 'San Francisco'),
    PARTITION p_other VALUES IN (NULL)
);

-- 哈希分区
CREATE TABLE logs (
    id INT,
    created_at TIMESTAMP
) PARTITION BY HASH(id) PARTITIONS 8;

---

10. 性能优化

10.1 批量插入优化

-- 减少网络往返
INSERT INTO yourtable VALUES (1,2), (5,5), (10,15);

-- 临时禁用索引
ALTER TABLE yourtable DISABLE KEYS;
-- 批量插入
ALTER TABLE yourtable ENABLE KEYS;

-- 调整批量大小
SET autocommit = 0;
-- 批量插入操作
COMMIT;

10.2 查询优化

-- 使用 EXPLAIN 分析
EXPLAIN SELECT * FROM employees WHERE department_id = 1;

-- 避免 SELECT *
SELECT id, name, email FROM users;  -- 明确指定列

-- 使用 EXISTS 代替 IN（大数据集）
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- 优化 OR 条件
-- 不推荐：SELECT * FROM t WHERE a = 1 OR b = 2;
-- 推荐：
SELECT * FROM t WHERE a = 1
UNION ALL
SELECT * FROM t WHERE b = 2;

10.3 窗口函数

-- 排名函数
SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) as rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
FROM employees;

-- 聚合窗口
SELECT country, profit,
       SUM(profit) OVER (PARTITION BY country) as country_total
FROM sales;

-- 移动平均
SELECT date, revenue,
       AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
       as moving_avg_7day
FROM daily_sales;

10.4 性能监控

-- 慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- 查看当前进程
SHOW PROCESSLIST;

-- 性能模式查询
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

---

参考资料

本文档基于 MySQL 8.0 Reference Manual 整理。

Source: https://dev.mysql.com/doc/refman/8.0/en/