🏛️ 一、系统架构设计

1.1 MySQL 整体分层架构

MySQL 采用经典的三层架构设计，每一层各司其职，实现了高度的模块化和可扩展性。

┌─────────────────────────────────────────────────────┐ │ 客户端应用层 (Client Layer) │ │ JDBC / ODBC / PHP PDO / Python Connector │ ├─────────────────────────────────────────────────────┤ │ 连接管理层 (Connection Layer) │ │ ┌──────────┐ ┌──────────┐ ┌──────────────┐ │ │ │ 认证模块 │ │ 连接池 │ │ 线程缓存 │ │ │ └──────────┘ └──────────┘ └──────────────┘ │ ├─────────────────────────────────────────────────────┤ │ SQL 接口层 (SQL Interface Layer) │ │ ┌──────────┐ ┌──────────┐ ┌──────────────┐ │ │ │ DDL │ │ DML │ │ 存储过程/视图│ │ │ └──────────┘ └──────────┘ └──────────────┘ │ ├─────────────────────────────────────────────────────┤ │ 解析与优化层 (Parser & Optimizer) │ │ ┌──────────┐ ┌──────────┐ ┌──────────────┐ │ │ │ 语法分析 │ │ 语义分析 │ │ 查询优化器 │ │ │ └──────────┘ └──────────┘ └──────────────┘ │ ├─────────────────────────────────────────────────────┤ │ 缓存层 (Query Cache - 8.0已移除) │ ├─────────────────────────────────────────────────────┤ │ 存储引擎层 (Storage Engine Layer) │ │ ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ │ │ │InnoDB │ │MyISAM │ │Memory │ │Archive │ │ │ └────────┘ └────────┘ └────────┘ └────────┘ │ ├─────────────────────────────────────────────────────┤ │ 文件系统层 (File System Layer) │ │ 数据文件 / 日志文件 / 索引文件 │ └─────────────────────────────────────────────────────┘

各层职责说明：

• 连接管理层：负责客户端连接建立、身份验证、连接池管理和线程缓存
• SQL 接口层：接收客户端的 SQL 命令（DDL/DML/DCL/DQL），分发到下层处理
• 解析与优化层：将 SQL 解析为语法树，进行语义分析，再由查询优化器生成最优执行计划
• 存储引擎层：以插件形式存在，实际负责数据的存储与检索，不同引擎适用于不同场景
• 文件系统层：最终将数据持久化到磁盘文件

💡 核心理念：

MySQL 的 Server 层与存储引擎层通过标准 API 接口通信，这使得存储引擎可以独立开发和替换。InnoDB 是 MySQL 5.5 之后的默认存储引擎。

1.2 连接与线程处理模型

连接生命周期

1. 连接建立：客户端发起 TCP 连接请求到 MySQL 的 3306 端口
2. 身份验证：服务端校验用户名、主机地址、密码（MySQL 8.0 使用 caching_sha2_password 插件）
3. 权限检查：加载用户的权限信息到内存中
4. 命令执行：进入命令循环，解析和执行 SQL 语句
5. 连接关闭：释放资源，归还线程

线程模型对比

-- 查看当前连接状态

SHOW

STATUS

LIKE

'Threads_%'

;

-- 配置最大连接数

SET GLOBAL

max_connections =

500

;

-- 配置线程缓存大小

SET GLOBAL

thread_cache_size =

64

;

⚠️ 注意：

max_connections 设置过高会消耗大量内存（每个连接约占 256KB~10MB），建议根据实际业务需求合理设置，并配合连接池使用。

1.3 InnoDB 存储引擎架构详解

InnoDB 是 MySQL 最重要的存储引擎，支持事务（ACID）、行级锁、外键约束，是 MySQL 5.5 之后的默认引擎。

┌──────────────── InnoDB 存储引擎 ────────────────┐ │ │ │ ┌──────────── 内存结构 ────────────┐ │ │ │ │ │ │ │ ┌─────────────────────────────┐ │ │ │ │ │ Buffer Pool (缓冲池) │ │ │ │ │ │ ┌─────────┐ ┌───────────┐ │ │ │ │ │ │ │数据页缓存│ │索引页缓存 │ │ │ │ │ │ │ └─────────┘ └───────────┘ │ │ │ │ │ │ ┌─────────┐ ┌───────────┐ │ │ │ │ │ │ │Change Bu│ │自适应哈希 │ │ │ │ │ │ │ │ ffer │ │ 索引 │ │ │ │ │ │ │ └─────────┘ └───────────┘ │ │ │ │ │ └─────────────────────────────┘ │ │ │ │ │ │ │ │ ┌──────────┐ ┌──────────────┐ │ │ │ │ │Log Buffer│ │ 自适应哈希 │ │ │ │ │ └──────────┘ └──────────────┘ │ │ │ └────────────────────────────────────┘ │ │ │ │ ┌──────────── 磁盘结构 ────────────┐ │ │ │ │ │ │ │ ┌────────┐ ┌────────┐ ┌────┐ │ │ │ │ │表空间 │ │Redo Log│ │Undo│ │ │ │ │ │(ibdata)│ │(ib_log)│ │ Log│ │ │ │ │ └────────┘ └────────┘ └────┘ │ │ │ │ │ │ │ │ ┌────────────────────────────┐ │ │ │ │ │ Doublewrite Buffer 文件 │ │ │ │ │ └────────────────────────────┘ │ │ │ └────────────────────────────────────┘ │ └────────────────────────────────────────────────────┘

核心组件说明：

• Buffer Pool（缓冲池）：InnoDB 最关键的内存组件，缓存读取的数据页和索引页。默认大小 128MB，生产环境建议设置为物理内存的 60%~80%
• Redo Log（重做日志）：保证事务持久性（Durability），记录物理页面的修改。采用循环写入的 WAL（Write-Ahead Logging）机制
• Undo Log（回滚日志）：记录事务的逆操作，用于事务回滚和 MVCC 多版本并发控制
• Doublewrite Buffer（双写缓冲）：防止写入中断导致部分页写入损坏，先将页写入 Doublewrite 文件，再写入实际表空间
• Change Buffer：缓存非唯一二级索引的变更，减少随机 I/O
• 自适应哈希索引：InnoDB 自动为热点数据建立的哈希索引，加速等值查询

-- 查看 Buffer Pool 配置

SHOW VARIABLES LIKE

'innodb_buffer_pool%'

;

-- 设置 Buffer Pool 大小为 4GB（需重启生效，或 8.0 动态调整）

SET GLOBAL

innodb_buffer_pool_size =

4294967296

;

-- 查看 Redo Log 配置

SHOW VARIABLES LIKE

'innodb_log%'

;

1.4 存储引擎对比与选择策略

✅ 建议：

绝大多数场景请使用 InnoDB 引擎。MyISAM 在 MySQL 8.0 中已被弃用，数据字典表已迁移到 InnoDB。仅在特殊场景下考虑其他引擎。

⚙️ 二、安装与环境配置

2.1 各平台安装部署指南

方式一：Docker 快速部署（推荐）

拉取 MySQL 8.0 官方镜像

docker pull mysql:8.0

启动容器

docker run -d \ --name mysql-server \ -e MYSQL_ROOT_PASSWORD=

YourStrongPass123!

\ -e MYSQL_DATABASE=

myapp

\ -p 3306:3306 \ -v /data/mysql:/var/lib/mysql \ -v /data/mysql/conf:/etc/mysql/conf.d \ mysql:8.0

查看日志

docker logs -f mysql-server

进入容器

docker exec -it mysql-server mysql -uroot -p

方式二：Ubuntu/Debian 安装

添加 MySQL APT 仓库

wget https://dev.mysql.com/get/mysql-apt-config_0.8.32-1_all.deb sudo dpkg -i mysql-apt-config_0.8.32-1_all.deb sudo apt update

安装 MySQL Server

sudo apt install mysql-server

启动并设置开机自启

sudo systemctl start mysql sudo systemctl enable mysql

安全初始化

sudo mysql_secure_installation

方式三：CentOS/RHEL 安装

安装 MySQL Yum 仓库

sudo yum install -y https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm

安装 MySQL Server

sudo yum install -y mysql-community-server

启动服务

sudo systemctl start mysqld sudo systemctl enable mysqld

获取临时密码

sudo grep

'temporary password'

/var/log/mysqld.log

修改 root 密码

mysql -uroot -p

ALTER USER

'root'

@

'localhost'

IDENTIFIED

BY

'NewPass123!'

;

2.2 核心配置文件详解 (my.cnf)

MySQL 的配置文件在不同系统上位置不同：

• Linux： /etc/my.cnf, /etc/mysql/my.cnf, ~/.my.cnf
• Windows： C:\ProgramData\MySQL\MySQL Server X.X\my.ini

[mysqld]

===== 基础配置 =====

port =

3306

datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock pid-file = /var/run/mysqld/mysqld.pid

===== 字符集配置 =====

character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect =

'SET NAMES utf8mb4'

===== 连接配置 =====

max_connections =

500

max_connect_errors =

1000

wait_timeout =

600

interactive_timeout =

600

thread_cache_size =

64

===== InnoDB 配置 =====

innodb_buffer_pool_size = 4G innodb_buffer_pool_instances =

4

innodb_log_file_size = 512M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit =

1

innodb_flush_method = O_DIRECT innodb_io_capacity =

2000

innodb_io_capacity_max =

4000

===== 查询配置 =====

tmp_table_size = 64M max_heap_table_size = 64M join_buffer_size = 4M sort_buffer_size = 4M read_buffer_size = 2M read_rnd_buffer_size = 4M

===== 慢查询日志 =====

slow_query_log =

1

slow_query_log_file = /var/log/mysql/slow.log long_query_time =

2

===== 错误日志 =====

log_error = /var/log/mysql/error.log log_warnings =

2

===== Binlog 配置 =====

log_bin = /var/log/mysql/mysql-bin binlog_format = ROW expire_logs_days =

14

max_binlog_size = 512M server_id =

1

[client] default-character-set = utf8mb4

💡 配置加载优先级：

命令行参数 > 项目录配置 > 用户配置 (/etc/my.cnf) > 默认值。可通过

mysql --help | grep my.cnf

查看加载顺序。

📖 三、SQL 基础教程

3.1 DDL - 数据库与表操作

数据库操作

-- 创建数据库

CREATE DATABASE

IF NOT EXISTS

ecommerce

DEFAULT CHARACTER SET

utf8mb4

DEFAULT COLLATE

utf8mb4_unicode_ci;

-- 查看所有数据库

SHOW DATABASES

;

-- 切换数据库

USE

ecommerce;

-- 删除数据库（慎用！）

DROP DATABASE IF EXISTS

test_db;

创建数据表（完整示例）

CREATE TABLE

users ( id

BIGINT

UNSIGNED NOT NULL

AUTO_INCREMENT

COMMENT

'用户ID'

, username

VARCHAR

(

50

)

NOT NULL

COMMENT

'用户名'

, email

VARCHAR

(

100

)

NOT NULL

COMMENT

'邮箱地址'

, phone

VARCHAR

(

20

)

DEFAULT NULL

COMMENT

'手机号'

, password_hash

VARCHAR

(

255

)

NOT NULL

COMMENT

'密码哈希'

, status

TINYINT

UNSIGNED NOT NULL DEFAULT

1

COMMENT

'状态: 0-禁用 1-正常 2-冻结'

, balance

DECIMAL

(

12

,

2

)

NOT NULL DEFAULT

0.00

COMMENT

'账户余额'

, created_at

TIMESTAMP

NOT NULL DEFAULT

CURRENT_TIMESTAMP

COMMENT

'创建时间'

, updated_at

TIMESTAMP

NOT NULL DEFAULT

CURRENT_TIMESTAMP

ON UPDATE

CURRENT_TIMESTAMP

COMMENT

'更新时间'

,

PRIMARY KEY

(id),

UNIQUE KEY

uk_username (username),

UNIQUE KEY

uk_email (email),

INDEX

idx_phone (phone),

INDEX

idx_status_created (status, created_at) )

ENGINE

=InnoDB

DEFAULT CHARSET

=utf8mb4

COLLATE

=utf8mb4_unicode_ci

COMMENT

=

'用户信息表'

;

约束类型一览

3.2 DML - 增删改查核心操作

插入数据 (INSERT)

-- 单条插入

INSERT INTO

users (username, email, password_hash)

VALUES

(

'zhangsan'

,

'zhang@example.com'

,

'\(2b\)12$...'

);

-- 批量插入（推荐，性能更好）

INSERT INTO

users (username, email, password_hash)

VALUES

(

'lisi'

,

'li@example.com'

,

'\(2b\)12$...'

), (

'wangwu'

,

'wang@example.com'

,

'\(2b\)12$...'

), (

'zhaoliu'

,

'zhao@example.com'

,

'\(2b\)12$...'

);

-- INSERT ... ON DUPLICATE KEY UPDATE (Upsert)

INSERT INTO

user_stats (user_id, login_count, last_login)

VALUES

(

1

,

1

,

NOW

())

ON DUPLICATE KEY UPDATE

login_count = login_count +

1

, last_login =

NOW

();

-- INSERT ... SELECT（从查询结果插入）

INSERT INTO

user_backup

SELECT

*

FROM

users

WHERE

created_at <

'2025-01-01'

;

查询数据 (SELECT)

-- 基础查询

SELECT

id, username, email, status

FROM

users

WHERE

status =

1

AND

created_at >=

'2025-01-01'

ORDER BY

created_at

DESC

LIMIT

20

OFFSET

0

;

-- 聚合查询

SELECT

status,

COUNT

(*)

AS

total,

AVG

(balance)

AS

avg_balance,

SUM

(balance)

AS

sum_balance

FROM

users

GROUP BY

status

HAVING

total >

10

;

-- 多表 JOIN 查询

SELECT

u.username, o.order_no, o.total_amount, p.product_name

FROM

users u

INNER JOIN

orders o

ON

u.id = o.user_id

INNER JOIN

order_items oi

ON

o.id = oi.order_id

INNER JOIN

products p

ON

oi.product_id = p.id

WHERE

o.status =

'completed'

AND

o.created_at >=

'2025-01-01'

;

-- 子查询

SELECT

*

FROM

users

WHERE

id

IN

(

SELECT

user_id

FROM

orders

WHERE

total_amount >

1000

);

-- EXISTS 子查询（推荐，性能更好）

SELECT

*

FROM

users u

WHERE EXISTS

(

SELECT

1

FROM

orders o

WHERE

o.user_id = u.id

AND

o.total_amount >

1000

);

更新与删除

-- 安全更新

UPDATE

users

SET

status =

2

, updated_at =

NOW

()

WHERE

id =

100

AND

status =

1

;

-- 关联更新

UPDATE

users u

INNER JOIN

orders o

ON

u.id = o.user_id

SET

u.status =

3

WHERE

o.total_amount >

10000

;

-- 软删除（推荐，保留数据）

UPDATE

users

SET

is_deleted =

1

, deleted_at =

NOW

()

WHERE

id =

100

;

3.3 索引原理与使用详解

B+Tree 索引结构

B+Tree 索引结构示意 ┌────────────┐ │ [30|60|90] │ ← 非叶子节点 (只存键值) └─────┬──────┘ ┌─────────┬───┴───┬─────────┐ ┌───┴───┐ ┌───┴───┐ ┌┴────┐ ┌───┴───┐ │[10|20]│ │[40|50]│ │[70|80]│ │[100|110]│ ← 非叶子节点 └───┬───┘ └───┬───┘ └───┬───┘ └───┬───┘ │ │ │ │ ┌────┴──┐ ┌────┴──┐ ┌────┴──┐ ┌────┴──┐ │[10][20]│→│[30][40]│→│[50][60]│→│[70][80]│→ ... ← 叶子节点 (存数据行/行指针) └───────┘ └───────┘ └───────┘ └───────┘ ↑ 叶子节点间有双向链表连接 (方便范围查询)

索引类型

• 聚簇索引 (Clustered Index)：叶子节点存储完整数据行，InnoDB 中一个表只能有一个（通常为主键）
• 二级索引 (Secondary Index)：叶子节点存储主键值，查询非索引字段时需要"回表"
• 覆盖索引 (Covering Index)：查询字段都在索引中，无需回表，性能最优
• 联合索引 (Composite Index)：多列组合的索引，遵循最左前缀匹配原则
• 前缀索引：只索引列的前 N 个字符，节省空间
• 唯一索引：值唯一，允许 NULL
• 全文索引：用于全文搜索 (FULLTEXT)

索引操作

-- 创建普通索引

CREATE INDEX

idx_email

ON

users(email);

-- 创建唯一索引

CREATE UNIQUE INDEX

idx_phone

ON

users(phone);

-- 创建联合索引

CREATE INDEX

idx_status_created

ON

users(status, created_at);

-- 创建前缀索引

CREATE INDEX

idx_address

ON

users(address(

20

));

-- 创建全文索引

CREATE FULLTEXT INDEX

idx_content

ON

articles(title, content);

-- 查看索引使用情况

EXPLAIN SELECT

*

FROM

users

WHERE

email =

'test@example.com'

;

⚠️ 索引失效场景：

• 对索引列使用函数或运算：WHERE YEAR(created_at) = 2025
• 使用 LIKE '%前缀模糊'：WHERE name LIKE '%张'
• 隐式类型转换：WHERE phone = 13800138000（应为字符串）
• OR 条件中包含非索引列
• 联合索引不满足最左前缀原则

📊 四、数据类型详解

4.1 常用数据类型完整对照表

数值类型

字符串类型

日期时间类型

💡 DATETIME vs TIMESTAMP：

DATETIME 占用 8 字节，范围更大且不受时区影响；TIMESTAMP 占用 4 字节但受时区影响且有 2038 年问题。现代应用推荐使用 DATETIME。

4.2 JSON 数据类型与操作 (MySQL 5.7+)

MySQL 从 5.7 版本开始原生支持 JSON 数据类型，8.0 版本大幅增强了 JSON 功能。

-- 创建包含 JSON 字段的表

CREATE TABLE

products ( id

BIGINT

AUTO_INCREMENT

PRIMARY KEY

, name

VARCHAR

(

100

)

NOT NULL

, attributes

JSON

NOT NULL

,

INDEX

idx_price ((

CAST

(attributes->>

'$.price'

AS DECIMAL

(

10

,

2

)))) );

-- 插入 JSON 数据

INSERT INTO

products (name, attributes)

VALUES

(

'iPhone 15 Pro'

,

'{"brand":"Apple","price":8999,"colors":["黑色","白色","蓝色"],"specs":{"cpu":"A17 Pro","ram":"8GB"}}'

);

-- 查询 JSON 字段

SELECT

name, attributes->

'$.brand'

AS

brand,

-- JSON 类型结果

attributes->>

'$.price'

AS

price

-- 字符串类型结果

FROM

products;

-- JSON 函数操作

SELECT

JSON_EXTRACT

(attributes,

'$.specs.cpu'

)

AS

cpu,

JSON_CONTAINS

(attributes->

'$.colors'

,

'"黑色"'

)

AS

has_black,

JSON_LENGTH

(attributes->

'$.colors'

)

AS

color_count

FROM

products;

-- 更新 JSON 字段

UPDATE

products

SET

attributes =

JSON_SET

( attributes,

'$.price'

,

7999

,

'$.discount'

,

true

)

WHERE

id =

1

;

✅ MySQL 8.0 JSON 增强：

支持 JSON_TABLE 函数（将 JSON 转为关系表）、JSON 聚合函数（JSON_ARRAYAGG、JSON_OBJECTAGG）、多值索引（Multi-Valued Index）。

🚀 五、高级特性与语法

5.1 事务管理与隔离级别

ACID 四大特性

• Atomicity（原子性）：事务是不可分割的最小单元，要么全部成功，要么全部失败回滚
• Consistency（一致性）：事务执行前后，数据库从一个一致状态变为另一个一致状态
• Isolation（隔离性）：并发事务之间互不干扰
• Durability（持久性）：事务一旦提交，其修改永久保存

事务操作

-- 开启事务

START TRANSACTION

;

-- 或 BEGIN;

-- 执行操作

UPDATE

accounts

SET

balance = balance -

100

WHERE

id =

1

;

UPDATE

accounts

SET

balance = balance +

100

WHERE

id =

2

;

-- 提交事务

COMMIT

;

-- 或回滚事务

ROLLBACK

;

-- 使用 SAVEPOINT

START TRANSACTION

;

UPDATE

accounts

SET

balance = balance -

50

WHERE

id =

1

;

SAVEPOINT

sp1;

UPDATE

accounts

SET

balance = balance +

50

WHERE

id =

3

;

ROLLBACK TO

sp1;

-- 回滚到保存点

COMMIT

;

四种隔离级别

💡 MVCC 原理：

InnoDB 在 RR 级别下通过 Undo Log 版本链 + Read View（快照）实现多版本并发控制，让读操作不阻塞写操作，大幅提升并发性能。

5.2 视图、存储过程与触发器

视图 (View)

-- 创建视图（封装复杂查询）

CREATE OR REPLACE VIEW

v_user_orders

AS

SELECT

u.id, u.username, u.email,

COUNT

(o.id)

AS

order_count,

SUM

(o.total_amount)

AS

total_spent

FROM

users u

LEFT JOIN

orders o

ON

u.id = o.user_id

GROUP BY

u.id, u.username, u.email;

-- 使用视图

SELECT

*

FROM

v_user_orders

WHERE

total_spent >

10000

;

存储过程 (Stored Procedure)

DELIMITER

DELIMITER

;

-- 调用存储过程

CALL

sp_transfer(

1

,

2

,

100.00

, @result);

SELECT

@result;

触发器 (Trigger)

CREATE TRIGGER

trg_user_audit

AFTER INSERT ON

users

FOR EACH ROW

BEGIN

INSERT INTO

user_audit_log (user_id, action, created_at)

VALUES

(NEW.id,

'REGISTER'

,

NOW

());

END

;

⚠️ 注意：

存储过程和触发器在现代架构中应谨慎使用。它们增加了数据库的复杂度，难以版本控制和调试，且不利于水平扩展。建议在应用层处理业务逻辑。

5.3 窗口函数 (MySQL 8.0+)

窗口函数是 MySQL 8.0 引入的强大特性，可以在不改变结果集行数的情况下对数据进行聚合分析。

-- ROW_NUMBER(): 行号

SELECT

name, salary, department,

ROW_NUMBER

()

OVER

(

PARTITION BY

department

ORDER BY

salary

DESC

)

AS

rn

FROM

employees;

-- RANK() vs DENSE_RANK(): 排名

SELECT

name, salary,

RANK

()

OVER

(

ORDER BY

salary

DESC

)

AS

rnk,

-- 1, 2, 2, 4

DENSE_RANK

()

OVER

(

ORDER BY

salary

DESC

)

AS

drnk

-- 1, 2, 2, 3

FROM

employees;

-- 取每个部门薪资 Top 3 的员工

WITH

ranked

AS

(

SELECT

*,

DENSE_RANK

()

OVER

(

PARTITION BY

department

ORDER BY

salary

DESC

)

AS

dr

FROM

employees )

SELECT

*

FROM

ranked

WHERE

dr <=

3

;

-- LAG/LEAD: 前后行值

SELECT

date, sales,

LAG

(sales,

1

)

OVER

(

ORDER BY

date)

AS

prev_day_sales,

LEAD

(sales,

1

)

OVER

(

ORDER BY

date)

AS

next_day_sales, sales -

LAG

(sales,

1

)

OVER

(

ORDER BY

date)

AS

diff

FROM

daily_sales;

-- SUM OVER: 累计求和

SELECT

date, amount,

SUM

(amount)

OVER

(

ORDER BY

date)

AS

cumulative_sum

FROM

transactions;

⚡ 六、性能优化策略

6.1 EXPLAIN 执行计划深度解读

EXPLAIN 是 MySQL 性能调优的利器，它展示了查询优化器选择的执行计划。

EXPLAIN

SELECT

*

FROM

orders o

INNER JOIN

users u

ON

o.user_id = u.id

WHERE

o.status =

'completed'

AND

o.created_at >=

'2025-01-01'

;

EXPLAIN 输出字段解读

💡 type 字段详解（性能从高到低）：

• system：系统表，只有一行
• const：常量查询（主键等值查询），最多一行
• eq_ref：唯一索引等值连接
• ref：非唯一索引等值查询
• range：索引范围扫描
• index：全索引扫描
• ALL：全表扫描（需优化！）

6.2 慢查询分析与优化实战

开启慢查询日志

-- 动态开启慢查询日志

SET GLOBAL

slow_query_log =

ON

;

SET GLOBAL

long_query_time =

1

;

-- 超过1秒记录

SET GLOBAL

log_queries_not_using_indexes =

ON

;

-- 查看慢查询日志位置

SHOW VARIABLES LIKE

'slow_query_log_file'

;

常见慢查询优化案例

案例1：深分页优化

-- 慢！OFFSET 越大越慢

SELECT

*

FROM

orders

ORDER BY

id

LIMIT

1000000

,

20

;

-- 优化：使用游标分页

SELECT

*

FROM

orders

WHERE

id >

1000000

-- 记住上次的最大ID

ORDER BY

id

LIMIT

20

;

-- 或延迟关联

SELECT

o.*

FROM

orders o

INNER JOIN

(

SELECT

id

FROM

orders

ORDER BY

id

LIMIT

1000000

,

20

)

AS

tmp

ON

o.id = tmp.id;

*案例2：COUNT() 大表优化**

-- 大表 COUNT 很慢

SELECT COUNT

(*)

FROM

orders;

-- 优化方案1：使用计数器表

CREATE TABLE

table_counts ( table_name

VARCHAR

(

50

)

PRIMARY KEY

, row_count

BIGINT

NOT NULL

);

-- 优化方案2：使用近似值

SELECT

table_rows

FROM

information_schema.tables

WHERE

table_name =

'orders'

;

6.3 表分区与分库分表策略

MySQL 表分区类型

-- RANGE 分区（最常用）

CREATE TABLE

orders ( id

BIGINT

AUTO_INCREMENT, user_id

BIGINT

NOT NULL

, amount

DECIMAL

(

12

,

2

), order_date

DATE

NOT NULL

,

PRIMARY KEY

(id, order_date) )

PARTITION BY RANGE

(

YEAR

(order_date)) (

PARTITION

p2023

VALUES LESS THAN

(

2024

),

PARTITION

p2024

VALUES LESS THAN

(

2025

),

PARTITION

p2025

VALUES LESS THAN

(

2026

),

PARTITION

p2026

VALUES LESS THAN

(

2027

),

PARTITION

pmax

VALUES LESS THAN MAXVALUE

);

-- HASH 分区

CREATE TABLE

user_logs ( id

BIGINT

, user_id

BIGINT

)

PARTITION BY HASH

(user_id)

PARTITIONS

8

;

-- LIST 分区

CREATE TABLE

sales ( id

BIGINT

, region

VARCHAR

(

20

) )

PARTITION BY LIST

(region) (

PARTITION

p_east

VALUES IN

(

'上海'

,

'杭州'

,

'南京'

),

PARTITION

p_north

VALUES IN

(

'北京'

,

'天津'

),

PARTITION

p_south

VALUES IN

(

'广州'

,

'深圳'

) );

分库分表策略对比

🔒 七、安全设计架构

7.1 权限管理与访问控制

用户与权限管理

-- 创建用户（MySQL 8.0 语法）

CREATE USER

'app_user'

@

'10.0.%'

IDENTIFIED

WITH

caching_sha2_password

BY

'Str0ngP@ssw0rd!'

PASSWORD EXPIRE INTERVAL

90

DAY

FAILED_LOGIN_ATTEMPTS

5

PASSWORD_LOCK_TIME

1

;

-- 授予最小权限

GRANT SELECT

,

INSERT

,

UPDATE

ON

ecommerce.*

TO

'app_user'

@

'10.0.%'

;

-- 授予只读权限（报表用户）

GRANT SELECT ON

ecommerce.*

TO

'readonly_user'

@

'%'

;

-- 创建角色并分配（MySQL 8.0+）

CREATE ROLE

'app_read'

;

GRANT SELECT ON

ecommerce.*

TO

'app_read'

;

CREATE ROLE

'app_write'

;

GRANT INSERT

,

UPDATE ON

ecommerce.*

TO

'app_write'

;

GRANT

'app_read'

,

'app_write'

TO

'app_user'

@

'10.0.%'

;

-- 查看权限

SHOW GRANTS FOR

'app_user'

@

'10.0.%'

;

-- 撤销权限

REVOKE INSERT ON

ecommerce.*

FROM

'app_user'

@

'10.0.%'

;

-- 刷新权限

FLUSH PRIVILEGES

;

✅ 最小权限原则：

应用用户只授予必要的 SELECT/INSERT/UPDATE 权限，禁止授予 DROP/ALTER/FILE/SUPER 等高危权限。不同环境使用不同的用户账号。

7.2 安全防护与 SQL 注入防御

SQL 注入防御

❌ 危险：字符串拼接

query = f

"SELECT * FROM users WHERE id = {user_id}"

✅ 安全：参数化查询（Python + mysql-connector）

cursor.execute(

"SELECT * FROM users WHERE id = %s"

, (user_id,) )

✅ ORM 方式（SQLAlchemy）

user = session.query(User).filter(User.id == user_id).first()

安全防护清单

• ✅ 始终使用参数化查询/预处理语句
• ✅ 配置 MySQL 防火墙（如 MySQL Enterprise Firewall）
• ✅ 启用 SSL/TLS 加密连接
• ✅ 定期更新 MySQL 版本修复安全漏洞
• ✅ 修改默认 3306 端口，限制可访问 IP
• ✅ 关闭不必要的功能（如 LOCAL INFILE）
• ✅ 使用强密码策略
• ✅ 审计日志监控异常操作

my.cnf 安全配置

[mysqld]

禁止 LOCAL INFILE（防止文件读取攻击）

local-infile =

0

禁止符号链接

symbolic-links =

0

启用 SSL

ssl-ca = /etc/mysql/ssl/ca.pem ssl-cert = /etc/mysql/ssl/server-cert.pem ssl-key = /etc/mysql/ssl/server-key.pem require_secure_transport =

ON

隐藏版本号

(在代理层处理，MySQL 本身不支持直接隐藏)

7.3 备份恢复与高可用架构

备份策略

逻辑备份：mysqldump（适合小库）

mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purged=OFF \ --quick \ --lock-tables=false \ ecommerce | gzip > /backup/ecommerce_$(date +%Y%m%d).sql.gz

物理备份：Percona XtraBackup（推荐大库）

xtrabackup --backup \ --target-dir=/backup/full \ --user=root --password=xxx

增量备份

xtrabackup --backup \ --target-dir=/backup/incr1 \ --incremental-basedir=/backup/full \ --user=root --password=xxx

恢复

xtrabackup --prepare --target-dir=/backup/full xtrabackup --copy-back --target-dir=/backup/full

高可用架构

🏆 八、工程最佳实践

8.1 数据库设计规范

命名规范

设计原则

• 三范式 (3NF)：消除冗余数据，但在性能场景下可适当反范式
• 主键策略：建议使用自增 BIGINT UNSIGNED 或雪花算法 ID
• 必备字段：每个表建议包含 id, created_at, updated_at, is_deleted
• 字段 NOT NULL：尽量定义为 NOT NULL，NULL 值会增加存储和索引复杂度
• 字符集：统一使用 utf8mb4，排序规则用 utf8mb4_unicode_ci 或 utf8mb4_0900_ai_ci
• 单表行数：建议不超过 500 万行，超过需考虑分表或归档
• 字段数量：单表字段建议不超过 30-40 个，过多可垂直拆分

8.2 监控与运维实践

关键监控指标

监控工具栈

Prometheus

Grafana

mysqld_exporter

Percona Monitoring

pt-query-digest

PMM (Percona)

Zabbix

8.3 MySQL 8.0 新特性与版本升级

MySQL 8.0 重大新特性

• 窗口函数：ROW_NUMBER, RANK, LAG/LEAD 等
• CTE (公用表表达式)：WITH 递归查询支持
• JSON 增强：JSON_TABLE, 多值索引, 聚合函数
• 角色管理：CREATE ROLE, 批量权限分配
• 原子 DDL：DDL 操作支持原子性，不会半完成
• 隐藏列：支持 INVISIBLE 列
• 降序索引：支持混合排序索引
• 直方图：优化器统计信息更精准
• 不可见索引：方便测试索引效果
• 数据字典：使用事务化的 InnoDB 数据字典，取代 .frm 文件

升级注意事项

升级前检查

mysqlsh --util checkForServerUpgrade root@localhost

使用 MySQL Shell 升级

mysqlsh root@localhost -- util upgradeCheck

⚠️ 升级警告：

MySQL 8.0 默认字符集改为 utf8mb4，排序规则变为 utf8mb4_0900_ai_ci，可能影响排序结果。升级前务必做完整测试和备份。

📝 全文总结

掌握 MySQL 需要从架构设计、SQL 编写、性能优化、安全策略、运维监控多个维度综合提升。核心原则是：

1. 合理设计数据模型，遵循规范
2. 编写高效的 SQL，善用索引和 EXPLAIN
3. 保障数据安全，最小权限原则
4. 建立完善的备份和高可用方案
5. 持续监控和优化，形成闭环