MySQL 数据库学习

第 1 章 MySQL 简介

MySQL 是一个开源的关系型数据库管理系统（RDBMS），以其高性能、可靠性和易用性成为 Web 应用（如 LAMP/LNMP 架构）的首选数据库。它使用 SQL（结构化查询语言）进行数据管理，支持多种存储引擎（如 InnoDB、MyISAM），并提供事务、外键、视图、存储过程等高级功能。

主要特点：

• 跨平台支持（Windows、Linux、macOS）
• 多线程、多用户
• 支持大型数据库
• 提供丰富的 API（C、Java、PHP、Python 等）
• 支持主从复制、分区、分片等高可用方案

---

第 2 章 安装与配置

2.1 在 Linux（CentOS/Ubuntu）上安装 MySQL

CentOS 7 / RHEL

1. # 下载并安装 MySQL 官方 Yum 源
2. wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
3. rpm -ivh mysql80-community-release-el7-3.noarch.rpm
4. # 安装 MySQL 服务器
5. yum install mysql-community-server -y
6. # 启动服务并设置开机自启
7. systemctl start mysqld
8. systemctl enable mysqld
9. # 查看临时密码
10. grep 'temporary password' /var/log/mysqld.log

复制代码

Ubuntu 20.04

1. # 更新包列表
2. apt update
3. # 安装 MySQL 服务器
4. apt install mysql-server -y
5. # 启动服务
6. systemctl start mysql
7. systemctl enable mysql
8. # 安全配置（设置 root 密码等）
9. mysql_secure_installation

复制代码

2.2 配置 MySQL

主配置文件通常位于 /etc/my.cnf（Linux）或 C:\ProgramData\MySQL\MySQL Server X.Y\my.ini（Windows）。常用配置项：

1. [mysqld]
2. port = 3306
3. datadir = /var/lib/mysql
4. socket = /var/lib/mysql/mysql.sock
5. character-set-server = utf8mb4
6. collation-server = utf8mb4_general_ci
7. max_connections = 500
8. innodb_buffer_pool_size = 2G # 根据内存调整

复制代码

修改配置后需重启服务。

2.3 客户端连接

1. mysql -h localhost -P 3306 -u root -p

复制代码

2.4 Windows 平台安装 MySQL

Windows 下安装 MySQL 主要有两种方式：使用 MSI 安装向导（图形化，推荐新手）或使用 ZIP 压缩包手动配置（更灵活，适合需要精细控制的环境）。下面分别介绍。

2.4.1 使用 MSI 安装程序（图形化方式）

1. 下载 MySQL Installer
   访问 MySQL 官方下载页面，选择 Windows (x86, 32-bit), MSI Installer 或 Windows (x86, 64-bit), MSI Installer。通常建议下载体积较大的 mysql-installer-community-.msi（包含所有产品和工具）。

2. 运行安装程序
   双击下载的 .msi 文件，若提示需要 .NET Framework，请按提示安装（MySQL Installer 需要 .NET 4.5.2+）。

3. 选择安装类型

   • Developer Default：安装 MySQL 服务器以及开发所需工具（如 MySQL Workbench、Shell、Router 等），适合开发人员。
   • Server only：仅安装 MySQL 服务器。
   • Client only：仅安装客户端工具（无服务器）。
   • Full：安装所有可用组件。
   • Custom：自定义选择组件。
     对于初学者，建议选择 Developer Default 或 Server only。

4. 检查依赖并执行安装
   安装程序会检查系统缺少的依赖（如 Visual C++ 运行库），点击“Execute”自动安装缺失组件。之后点击“Next”开始安装所选产品。

5. 产品配置
   安装完成后，安装程序自动进入配置向导（也可稍后通过 MySQL Installer 单独配置）。主要配置项包括：

   • High Availability：选择独立服务器（Standalone MySQL Server）或 InnoDB Cluster（集群），通常选独立。
   • Type and Networking：
     • 配置类型：
       • Development Computer：占用较少内存，适合开发机。
       • Server Computer：中等内存占用。
       • Dedicated Computer：使用全部可用内存，适合专用服务器。
     • 端口：默认 3306，可修改。
     • 是否开启 X 协议端口（33060），用于 MySQL 8.0 的文档存储功能，可按需开启。
   • Authentication Method：
     • Use Strong Password Encryption（推荐）：使用 MySQL 8.0 默认的 caching_sha2_password 插件，安全性高。
     • Use Legacy Authentication：兼容旧版客户端（使用 mysql_native_password），如果连接工具较老可选用。
   • Accounts and Roles：设置 root 密码，并可添加其他管理员用户。
   • Windows Service：将 MySQL 配置为 Windows 服务，可指定服务名称（默认 MySQL80），并选择启动类型（自动启动推荐）。还可勾选“Include Bin Directory in Windows PATH”以便在命令行直接使用 mysql 命令。
   • Apply Configuration：点击“Execute”应用配置，完成后点击“Finish”。

6. 验证安装
   打开命令提示符（CMD），输入：

   1. mysql -u root -p

   复制代码

   输入刚才设置的密码，若成功进入 MySQL 提示符 mysql>，则安装成功。也可通过开始菜单中的 MySQL Workbench 连接测试。

2.4.2 使用 ZIP 压缩包手动安装（免安装版）

适合需要定制化或绿色部署的场景。

1. 下载 ZIP 包
   访问 MySQL 社区版下载页，选择 Windows (x86, 64-bit), ZIP Archive 下载。

2. 解压到目标目录
   将 ZIP 包解压到指定路径，例如 C:\mysql-8.0.36-winx64。注意路径中不要包含中文或空格。

3. 创建配置文件 my.ini
   在解压目录下新建文本文件 my.ini，内容参考如下（根据需求调整路径）：

   1. [mysqld]
   2. # 设置 MySQL 安装目录
   3. basedir=C:/mysql-8.0.36-winx64
   4. # 数据存放目录
   5. datadir=C:/mysql-8.0.36-winx64/data
   6. # 端口
   7. port=3306
   8. # 默认存储引擎
   9. default-storage-engine=INNODB
   10. # 字符集
   11. character-set-server=utf8mb4
   12. [client]
   13. default-character-set=utf8mb4
   14. [mysql]
   15. default-character-set=utf8mb4

   复制代码

   注意：路径中使用正斜杠 / 或双反斜杠 \\。

4. 初始化数据目录
   以管理员身份打开命令提示符，进入 MySQL 的 bin 目录：

   1. cd C:\mysql-8.0.36-winx64\bin

   复制代码

   执行初始化命令（生成 root 临时密码）：

   1. mysqld --initialize --console

   复制代码

   记录控制台输出的临时密码（形如 root@localhost: >?

5. 安装 Windows 服务
   继续在 bin 目录下执行：

   1. mysqld --install MySQL80 --defaults-file="C:\mysql-8.0.36-winx64\my.ini"

   复制代码

   其中 MySQL80 是服务名称，可自定义。成功后提示 Service successfully installed.

6. 启动服务
   在命令提示符中执行：

   1. net start MySQL80

   复制代码

   或在“服务”管理器中手动启动。

7. 修改 root 密码
   使用初始密码登录 MySQL：

   1. mysql -u root -p

   复制代码

   输入初始化时生成的临时密码。登录成功后修改密码：

   1. ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
   2. FLUSH PRIVILEGES;

   复制代码

8. 可选：添加环境变量
   将 C:\mysql-8.0.36-winx64\bin 添加到系统 PATH 变量，以便在任何路径下直接使用 mysql 命令。

9. 验证
   重新打开新命令提示符，执行 mysql -u root -p 输入新密码，若能正常进入则安装成功。

以上两种方法任选其一即可在 Windows 上成功安装 MySQL。推荐初学者使用 MSI 安装向导，步骤简单直观；ZIP 手动安装则适合需要批量部署或自定义配置的场景。

---

第 3 章 MySQL 基本操作

3.1 数据库管理

1. -- 查看所有数据库
2. SHOW DATABASES;
4. -- 创建数据库
5. CREATE DATABASE IF NOT EXISTS school
6. CHARACTER SET utf8mb4
7. COLLATE utf8mb4_general_ci;
9. -- 切换数据库
10. USE school;
12. -- 删除数据库
13. DROP DATABASE school;

复制代码

3.2 表管理

1. -- 创建表
2. CREATE TABLE student (
3. id INT AUTO_INCREMENT PRIMARY KEY,
4. name VARCHAR(50) NOT NULL,
5. age TINYINT UNSIGNED,
6. gender ENUM('M','F'),
7. created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
10. -- 查看表结构
11. DESC student;
13. -- 修改表（添加列）
14. ALTER TABLE student ADD email VARCHAR(100) AFTER name;
16. -- 删除表
17. DROP TABLE student;

复制代码

---

第 4 章 数据类型

MySQL 支持多种数据类型，合理选择可优化存储与性能。

类型分类	常见类型	说明
数值	TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE	DECIMAL 用于精确小数，FLOAT/DOUBLE 为近似值
字符串	CHAR, VARCHAR, TEXT, BLOB, ENUM, SET	CHAR 定长，VARCHAR 变长，TEXT 长文本
日期时间	DATE, TIME, DATETIME, TIMESTAMP, YEAR	TIMESTAMP 受时区影响，范围 1970-2038
二进制	BINARY, VARBINARY, BLOB	存储图像、文件等
JSON	JSON	MySQL 5.7+ 支持 JSON 数据类型

示例：

1. CREATE TABLE product (
2. id INT,
3. price DECIMAL(10,2), -- 总位数10，小数位2
4. description TEXT,
5. created DATE,
6. attributes JSON
7. );

复制代码

---

第 5 章 SQL 基础查询（CRUD）

5.1 INSERT 插入数据

1. -- 插入单行
2. INSERT INTO student (name, age, gender) VALUES ('张三', 20, 'M');
4. -- 插入多行
5. INSERT INTO student (name, age, gender) VALUES
6. ('李四', 22, 'F'),
7. ('王五', 21, 'M');
9. -- 从另一表插入
10. INSERT INTO student_archive (id, name) SELECT id, name FROM student WHERE age > 30;

复制代码

5.2 SELECT 查询

1. -- 查询所有列
2. SELECT * FROM student;
4. -- 查询指定列
5. SELECT name, age FROM student;
7. -- 别名
8. SELECT name AS 姓名, age 年龄 FROM student;
10. -- 去重
11. SELECT DISTINCT gender FROM student;

复制代码

5.3 UPDATE 更新数据

1. -- 更新所有行（慎用！）
2. UPDATE student SET age = age + 1;
4. -- 带条件的更新
5. UPDATE student SET email = 'test@example.com' WHERE id = 1;

复制代码

5.4 DELETE 删除数据

1. -- 删除满足条件的行
2. DELETE FROM student WHERE id = 2;
4. -- 删除所有行（保留表结构）
5. DELETE FROM student;
6. -- 或使用 TRUNCATE（更快，但不可回滚）
7. TRUNCATE TABLE student;

复制代码

---

第 6 章 条件查询与运算符

6.1 WHERE 子句

1. SELECT * FROM student WHERE age > 18;
2. SELECT * FROM student WHERE name = '张三';
3. SELECT * FROM student WHERE age BETWEEN 18 AND 25;
4. SELECT * FROM student WHERE name LIKE '张%'; -- % 任意多个字符，_ 单个字符
5. SELECT * FROM student WHERE gender IN ('M', 'F');

复制代码

6.2 逻辑运算符

1. SELECT * FROM student WHERE age > 18 AND gender = 'M';
2. SELECT * FROM student WHERE age < 18 OR gender = 'F';
3. SELECT * FROM student WHERE NOT (age = 20);

复制代码

6.3 空值处理

1. SELECT * FROM student WHERE email IS NULL;
2. SELECT * FROM student WHERE email IS NOT NULL;

复制代码

6.4 排序

1. SELECT * FROM student ORDER BY age DESC, name ASC;

复制代码

6.5 限制结果

1. SELECT * FROM student LIMIT 5; -- 前5行
2. SELECT * FROM student LIMIT 5 OFFSET 10; -- 跳过10行，取5行（分页）

复制代码

---

第 7 章 函数与分组

7.1 常用聚合函数

1. SELECT COUNT(*) FROM student; -- 行数
2. SELECT AVG(age) FROM student; -- 平均年龄
3. SELECT SUM(age) FROM student; -- 年龄总和
4. SELECT MAX(age), MIN(age) FROM student; -- 最大/最小年龄

复制代码

7.2 分组 GROUP BY

1. -- 按性别分组，统计每组人数和平均年龄
2. SELECT gender, COUNT(*) AS count, AVG(age) AS avg_age
3. FROM student
4. GROUP BY gender;

复制代码

7.3 HAVING 过滤分组

1. -- 筛选平均年龄大于20的性别组
2. SELECT gender, AVG(age) AS avg_age
3. FROM student
4. GROUP BY gender
5. HAVING avg_age > 20;

复制代码

7.4 字符串函数

1. SELECT CONCAT(name, '(', age, ')') FROM student;
2. SELECT UPPER(name) FROM student;
3. SELECT SUBSTRING(name, 1, 2) FROM student;
4. SELECT LENGTH(name) FROM student; -- 字节数，utf8mb4 中汉字占3-4字节

复制代码

7.5 日期函数

1. SELECT NOW(); -- 当前日期时间
2. SELECT CURDATE(); -- 当前日期
3. SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY); -- 加一天
4. SELECT DATEDIFF('2025-12-31', CURDATE()); -- 相差天数

复制代码

---

第 8 章 多表连接

8.1 内连接（INNER JOIN）

1. -- 假设有 course 表和 score 表
2. SELECT s.name, c.course_name, sc.score
3. FROM student s
4. INNER JOIN score sc ON s.id = sc.student_id
5. INNER JOIN course c ON sc.course_id = c.id;

复制代码

8.2 左连接（LEFT JOIN）

1. -- 显示所有学生及其成绩（包括无成绩的学生）
2. SELECT s.name, sc.score
3. FROM student s
4. LEFT JOIN score sc ON s.id = sc.student_id;

复制代码

8.3 右连接（RIGHT JOIN）

1. -- 显示所有课程及其成绩（包括无成绩的课程）
2. SELECT c.course_name, sc.score
3. FROM score sc
4. RIGHT JOIN course c ON sc.course_id = c.id;

复制代码

8.4 自连接

1. -- 员工表，查找员工及其经理
2. SELECT e.name AS employee, m.name AS manager
3. FROM employee e
4. LEFT JOIN employee m ON e.manager_id = m.id;

复制代码

---

第 9 章 子查询

9.1 WHERE 中的子查询

1. -- 查询年龄大于平均年龄的学生
2. SELECT name, age FROM student
3. WHERE age > (SELECT AVG(age) FROM student);

复制代码

9.2 FROM 中的子查询（派生表）

1. -- 查询每个性别中年龄最大的学生
2. SELECT gender, name, age
3. FROM student
4. WHERE (gender, age) IN (
5. SELECT gender, MAX(age)
6. FROM student
7. GROUP BY gender
8. );

复制代码

9.3 SELECT 中的子查询（标量子查询）

1. -- 查询学生姓名及其所在班级人数
2. SELECT name,
3. (SELECT COUNT(*) FROM student s2 WHERE s2.class_id = s1.class_id) AS class_count
4. FROM student s1;

复制代码

9.4 EXISTS / NOT EXISTS

1. -- 查询有选课的学生
2. SELECT name FROM student s
3. WHERE EXISTS (SELECT 1 FROM score sc WHERE sc.student_id = s.id);

复制代码

---

第 10 章 索引

索引用于加速数据检索，但会降低写入性能并占用磁盘空间。

10.1 索引类型

• 普通索引（INDEX）：最基本的索引，无唯一性限制。
• 唯一索引（UNIQUE）：索引列的值必须唯一，允许 NULL。
• 主键索引（PRIMARY KEY）：特殊的唯一索引，不允许 NULL，一个表只能有一个。
• 全文索引（FULLTEXT）：用于全文搜索，MyISAM 和 InnoDB 支持。
• 空间索引（SPATIAL）：用于地理空间数据。

10.2 创建索引

1. -- 创建表时定义索引
2. CREATE TABLE student (
3. id INT PRIMARY KEY,
4. name VARCHAR(50),
5. INDEX idx_name (name)
6. );
8. -- 后期添加索引
9. CREATE INDEX idx_age ON student(age);
10. CREATE UNIQUE INDEX idx_email ON student(email);
11. ALTER TABLE student ADD INDEX idx_name_age (name, age); -- 复合索引

复制代码

10.3 查看索引

1. SHOW INDEX FROM student;

复制代码

10.4 删除索引

1. DROP INDEX idx_name ON student;

复制代码

10.5 索引使用原则

• 对频繁查询的列创建索引。
• 选择性高的列（如性别选择性低，不适合单独索引）。
• 避免在索引列上使用函数或计算。
• 复合索引注意最左前缀原则。

---

第 11 章 视图

视图是虚拟表，基于 SQL 查询结果，可简化复杂查询、提高安全性。

11.1 创建视图

1. CREATE VIEW v_student_score AS
2. SELECT s.id, s.name, c.course_name, sc.score
3. FROM student s
4. JOIN score sc ON s.id = sc.student_id
5. JOIN course c ON sc.course_id = c.id;

复制代码

11.2 使用视图

1. SELECT * FROM v_student_score WHERE name = '张三';

复制代码

11.3 修改/删除视图

1. CREATE OR REPLACE VIEW v_student_score AS ...;
2. DROP VIEW v_student_score;

复制代码

注意：对视图的更新（INSERT/UPDATE/DELETE）可能有限制，取决于视图定义。

---

第 12 章 存储过程与函数

存储过程和函数是预编译的 SQL 代码块，可提高复用性和性能。

12.1 存储过程

1. DELIMITER $$
3. CREATE PROCEDURE GetStudentsByAge(IN min_age INT)
4. BEGIN
5. SELECT * FROM student WHERE age >= min_age;
6. END$$
8. DELIMITER ;
10. -- 调用
11. CALL GetStudentsByAge(20);

复制代码

12.2 存储函数

1. DELIMITER $$
3. CREATE FUNCTION GetAgeGroup(age INT) RETURNS VARCHAR(10)
4. DETERMINISTIC
5. BEGIN
6. IF age < 18 THEN RETURN '未成年';
7. ELSE RETURN '成年';
8. END IF;
9. END$$
11. DELIMITER ;
13. -- 使用
14. SELECT name, GetAgeGroup(age) FROM student;

复制代码

12.3 流程控制

1. DELIMITER $$
2. CREATE PROCEDURE example()
3. BEGIN
4. DECLARE i INT DEFAULT 1;
5. WHILE i <= 10 DO
6. INSERT INTO log (message) VALUES (CONCAT('Count: ', i));
7. SET i = i + 1;
8. END WHILE;
9. END$$
10. DELIMITER ;

复制代码

12.4 查看与删除

1. SHOW PROCEDURE STATUS WHERE Db = 'school';
2. DROP PROCEDURE GetStudentsByAge;

复制代码

---

第 13 章 触发器

触发器是自动执行的操作，在 INSERT、UPDATE、DELETE 事件前后触发。

13.1 创建触发器

1. DELIMITER $$
2. CREATE TRIGGER before_student_insert
3. BEFORE INSERT ON student
4. FOR EACH ROW
5. BEGIN
6. SET NEW.created_at = NOW(); -- 自动设置创建时间
7. END$$
8. DELIMITER ;

复制代码

13.2 触发时机与事件

• BEFORE / AFTER
• INSERT / UPDATE / DELETE

示例：记录学生年龄变更日志

1. CREATE TABLE student_age_log (
2. student_id INT,
3. old_age TINYINT,
4. new_age TINYINT,
5. changed_at TIMESTAMP
6. );
8. DELIMITER $$
9. CREATE TRIGGER after_student_update_age
10. AFTER UPDATE ON student
11. FOR EACH ROW
12. BEGIN
13. IF OLD.age != NEW.age THEN
14. INSERT INTO student_age_log (student_id, old_age, new_age, changed_at)
15. VALUES (OLD.id, OLD.age, NEW.age, NOW());
16. END IF;
17. END$$
18. DELIMITER ;

复制代码

13.3 管理触发器

1. SHOW TRIGGERS;
2. DROP TRIGGER before_student_insert;

复制代码

---

第 14 章 事务与锁

事务确保一组操作要么全部成功，要么全部失败。InnoDB 支持事务。

14.1 ACID 特性

• 原子性：事务不可分割。
• 一致性：事务前后数据完整性约束不被破坏。
• 隔离性：并发事务互不干扰。
• 持久性：提交后数据永久保存。

14.2 事务控制语句

1. START TRANSACTION; -- 或 BEGIN
3. UPDATE account SET balance = balance - 100 WHERE user_id = 1;
4. UPDATE account SET balance = balance + 100 WHERE user_id = 2;
6. COMMIT; -- 提交
7. -- 或 ROLLBACK; -- 回滚

复制代码

14.3 保存点

1. SAVEPOINT sp1;
2. ...
3. ROLLBACK TO SAVEPOINT sp1;

复制代码

14.4 事务隔离级别

MySQL 支持四种隔离级别（由低到高）：

• READ UNCOMMITTED：脏读、不可重复读、幻读都可能。
• READ COMMITTED：避免脏读，但可能出现不可重复读、幻读。
• REPEATABLE READ（默认）：避免脏读和不可重复读，但可能出现幻读（InnoDB 通过多版本并发控制 MVCC 基本避免幻读）。
• SERIALIZABLE：最高级别，事务串行化，性能低。

查看/设置隔离级别：

1. SELECT @@transaction_isolation;
2. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

复制代码

14.5 锁机制

• 共享锁（S）：允许其他事务读取，但阻止修改。SELECT ... LOCK IN SHARE MODE
• 排他锁（X）：阻止其他事务读取和修改。SELECT ... FOR UPDATE
• 行锁、表锁、意向锁等由 InnoDB 自动管理。

---

第 15 章 备份与恢复

15.1 逻辑备份（mysqldump）

1. # 备份单个数据库
2. mysqldump -u root -p school > school_backup.sql
4. # 备份多个数据库
5. mysqldump -u root -p --databases db1 db2 > dbs_backup.sql
7. # 备份所有数据库
8. mysqldump -u root -p --all-databases > all.sql
10. # 备份时包含存储过程、事件等
11. mysqldump -u root -p --routines --events school > school_full.sql

复制代码

恢复：

1. mysql -u root -p school < school_backup.sql

复制代码

15.2 物理备份

直接复制数据目录（需停服务或使用文件系统快照），或使用工具如 xtrabackup（Percona）进行热备。

1. # 使用 xtrabackup 全量备份
2. xtrabackup --backup --target-dir=/backup/base --user=root --password=xxx
4. # 准备（应用日志）
5. xtrabackup --prepare --target-dir=/backup/base
7. # 恢复（需清空 datadir）
8. xtrabackup --copy-back --target-dir=/backup/base

复制代码

15.3 二进制日志（binlog）用于时间点恢复

启用 binlog（my.cnf）：

1. server-id = 1
2. log-bin = /var/lib/mysql/mysql-bin
3. expire_logs_days = 7

复制代码

查看 binlog：

1. SHOW BINARY LOGS;
2. SHOW MASTER STATUS;

复制代码

恢复至指定时间点：

1. mysqlbinlog --start-datetime="2025-02-01 00:00:00" --stop-datetime="2025-02-02 00:00:00" /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

复制代码

---

第 16 章 性能优化

16.1 慢查询日志

开启慢查询日志，记录执行时间超过阈值的 SQL。

1. slow_query_log = 1
2. slow_query_log_file = /var/log/mysql/slow.log
3. long_query_time = 2

复制代码

分析工具：mysqldumpslow、pt-query-digest。

16.2 EXPLAIN 分析查询

1. EXPLAIN SELECT * FROM student WHERE age > 20;

复制代码

关注列：

• type：ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（非唯一索引等值）、eq_ref（主键/唯一索引等值）、const（常量）
• key：实际使用的索引
• rows：估计扫描行数
• Extra：Using index（覆盖索引）、Using where、Using filesort（文件排序）等

16.3 索引优化

• 为 WHERE、JOIN、ORDER BY 列创建索引。
• 避免索引失效（如对列使用函数、隐式类型转换）。
• 使用覆盖索引（索引包含查询所需的所有列）减少回表。

16.4 表结构优化

• 选择合适的数据类型，避免过大。
• 尽量使用 NOT NULL。
• 垂直拆分（大字段分离到其他表）。
• 水平分区（如 RANGE 分区）。

1. CREATE TABLE logs (
2. id INT NOT NULL,
3. log_date DATE,
4. message TEXT
5. ) PARTITION BY RANGE (YEAR(log_date)) (
6. PARTITION p2023 VALUES LESS THAN (2024),
7. PARTITION p2024 VALUES LESS THAN (2025),
8. PARTITION p_future VALUES LESS THAN MAXVALUE
9. );

复制代码

16.5 参数调优

• innodb_buffer_pool_size：InnoDB 缓存数据和索引的内存大小，通常设为物理内存的 70%-80%。
• query_cache_type：查询缓存（MySQL 8.0 已废弃）。
• max_connections：最大连接数。
• sort_buffer_size、join_buffer_size：会话级缓冲区，注意不要过大。

16.6 使用缓存

应用层缓存（Redis、Memcached）减轻数据库压力。

---

第 17 章 用户与权限管理

17.1 用户管理

1. -- 创建用户
2. CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
4. -- 修改密码
5. ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_password';
7. -- 删除用户
8. DROP USER 'app_user'@'localhost';

复制代码

17.2 权限管理

1. -- 授予权限
2. GRANT SELECT, INSERT, UPDATE ON school.* TO 'app_user'@'localhost';
4. -- 授予所有权限
5. GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
7. -- 查看权限
8. SHOW GRANTS FOR 'app_user'@'localhost';
10. -- 撤销权限
11. REVOKE INSERT ON school.* FROM 'app_user'@'localhost';
13. -- 刷新权限（使修改立即生效）
14. FLUSH PRIVILEGES;

复制代码

17.3 权限级别

• 全局：*.*
• 数据库：db_name.*
• 表：db_name.table_name
• 列：需在 GRANT 中指定列。

---

第 18 章 高可用与复制简介

18.1 主从复制

主库将更改记录到二进制日志，从库读取日志并重放。

配置步骤（简要）：

主库 my.cnf：

1. server-id = 1
2. log-bin = mysql-bin
3. binlog-do-db = school # 可选，指定复制数据库

复制代码

从库 my.cnf：

1. server-id = 2
2. relay-log = relay-log
3. log-bin = mysql-bin
4. read_only = 1

复制代码

主库创建复制用户：

1. CREATE USER 'repl'@'slave_ip' IDENTIFIED BY 'password';
2. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip';

复制代码

从库设置主库信息并启动：

1. CHANGE MASTER TO
2. MASTER_HOST='master_ip',
3. MASTER_USER='repl',
4. MASTER_PASSWORD='password',
5. MASTER_LOG_FILE='mysql-bin.000001',
6. MASTER_LOG_POS=0;
7. START SLAVE;

复制代码

查看状态：

1. SHOW SLAVE STATUS\G

复制代码

18.2 其他高可用方案

• MHA：Master High Availability，故障自动切换。
• MySQL Group Replication：组复制，多主或单主。
• Orchestrator：复制拓扑管理。
• ProxySQL：数据库中间件，实现读写分离、负载均衡。

---

结语

本教程从 MySQL 的安装、基础 SQL 到高级特性（索引、事务、备份、优化）进行了较为全面的介绍。在实际工作中不断实践，深入理解原理，并关注官方文档和社区动态。

注意：示例基于 MySQL 8.0，部分语法可能与旧版本略有差异，请根据实际版本调整。