MySQL常用命令速查

1. 简介

MySQL 是一个开源的关系型数据库管理系统，它被广泛用于 Web 应用程序的后端数据存储。MySQL 提供了强大的功能和灵活的配置选项，使得它成为了最流行的数据库之一。本文将详细介绍MySQL的常用命令。

2. 连接与退出

2.1 连接MySQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# 连接本地MySQL
mysql -u username -p

# 连接远程MySQL
mysql -h hostname -u username -p

# 指定端口连接
mysql -h hostname -P 3306 -u username -p

# 连接指定数据库
mysql -u username -p database_name

2.2 退出MySQL

1
2
3
4
5
6
-- 退出MySQL
EXIT;
-- 或者
QUIT;
-- 或者使用快捷键
\q

3. 数据库操作

3.1 查看数据库

1
2
3
4
5
6
7
8
-- 显示所有数据库
SHOW DATABASES;

-- 查看当前使用的数据库
SELECT DATABASE();

-- 查看数据库创建语句
SHOW CREATE DATABASE database_name;

3.2 创建数据库

1
2
3
4
5
6
7
8
-- 创建数据库
CREATE DATABASE database_name;

-- 创建数据库并指定字符集
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS database_name;

3.3 使用数据库

1
2
-- 切换到指定数据库
USE database_name;

3.4 删除数据库

1
2
3
4
5
-- 删除数据库
DROP DATABASE database_name;

-- 如果存在则删除
DROP DATABASE IF EXISTS database_name;

4. 表操作

4.1 查看表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 显示当前数据库中的所有表
SHOW TABLES;

-- 显示表结构
DESC table_name;
-- 或者
DESCRIBE table_name;
-- 或者
SHOW COLUMNS FROM table_name;

-- 查看表创建语句
SHOW CREATE TABLE table_name;

-- 查看表状态信息
SHOW TABLE STATUS LIKE 'table_name';

4.2 创建表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- 创建基本表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建表并指定存储引擎和字符集
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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;

4.3 修改表结构

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- 添加列
ALTER TABLE table_name ADD COLUMN column_name VARCHAR(50);

-- 修改列类型
ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(100);

-- 重命名列
ALTER TABLE table_name CHANGE old_column_name new_column_name VARCHAR(50);

-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;

-- 添加索引
ALTER TABLE table_name ADD INDEX index_name (column_name);

-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- 删除索引
ALTER TABLE table_name DROP INDEX index_name;

4.4 删除表

1
2
3
4
5
6
7
8
-- 删除表
DROP TABLE table_name;

-- 如果存在则删除
DROP TABLE IF EXISTS table_name;

-- 清空表数据但保留表结构
TRUNCATE TABLE table_name;

5. 数据操作

5.1 插入数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
-- 插入单条记录
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- 插入多条记录
INSERT INTO users (username, email, password) VALUES 
('user1', 'user1@example.com', 'password1'),
('user2', 'user2@example.com', 'password2'),
('user3', 'user3@example.com', 'password3');

-- 插入或更新（如果主键冲突则更新）
INSERT INTO users (id, username, email) 
VALUES (1, 'updated_user', 'updated@example.com')
ON DUPLICATE KEY UPDATE 
username = VALUES(username), 
email = VALUES(email);

5.2 查询数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 基本查询
SELECT * FROM users;

-- 指定列查询
SELECT username, email FROM users;

-- 条件查询
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE username LIKE '%john%';
SELECT * FROM users WHERE created_at > '2023-01-01';

-- 排序
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC, id DESC;

-- 限制结果数量
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 分组和聚合
SELECT COUNT(*) FROM users;
SELECT category_id, COUNT(*) as product_count 
FROM products 
GROUP BY category_id;

-- 连接查询
SELECT u.username, p.name as product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

5.3 更新数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 更新单条记录
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- 更新多个字段
UPDATE users SET 
    email = 'newemail@example.com',
    updated_at = NOW()
WHERE id = 1;

-- 批量更新
UPDATE products SET price = price * 1.1 WHERE category_id = 1;

5.4 删除数据

1
2
3
4
5
6
7
8
-- 删除指定记录
DELETE FROM users WHERE id = 1;

-- 条件删除
DELETE FROM users WHERE created_at < '2022-01-01';

-- 删除所有记录（保留表结构）
DELETE FROM table_name;

6. 索引操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_name_category ON products(name, category_id);

-- 查看索引
SHOW INDEX FROM table_name;

-- 删除索引
DROP INDEX index_name ON table_name;

-- 分析表性能
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

7. 用户和权限管理

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'password';

-- 授权
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
GRANT SELECT, INSERT, UPDATE ON table_name TO 'username'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'username'@'localhost';

-- 撤销权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 删除用户
DROP USER 'username'@'localhost';

-- 修改密码
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

8. 备份和恢复

8.1 数据备份

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 备份单个数据库
mysqldump -u username -p database_name > backup.sql

# 备份多个数据库
mysqldump -u username -p --databases db1 db2 > backup.sql

# 备份所有数据库
mysqldump -u username -p --all-databases > all_backup.sql

# 只备份表结构
mysqldump -u username -p --no-data database_name > structure.sql

# 只备份数据
mysqldump -u username -p --no-create-info database_name > data.sql

# 备份并压缩
mysqldump -u username -p database_name | gzip > backup.sql.gz

8.2 数据恢复

1
2
3
4
5
# 恢复数据库
mysql -u username -p database_name < backup.sql

# 恢复压缩备份
gunzip < backup.sql.gz | mysql -u username -p database_name

MySQL Docker 容器管理

9.1 运行MySQL容器

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
# 基本运行
docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=rootpassword -d mysql:8.0

# 指定端口和数据卷
docker run --name mysql-container \
  -e MYSQL_ROOT_PASSWORD=rootpassword \
  -e MYSQL_DATABASE=myapp \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD=apppassword \
  -p 3306:3306 \
  -v mysql-data:/var/lib/mysql \
  -d mysql:8.0

# 使用配置文件
docker run --name mysql-container \
  -e MYSQL_ROOT_PASSWORD=rootpassword \
  -p 3306:3306 \
  -v /path/to/my.cnf:/etc/mysql/my.cnf \
  -v mysql-data:/var/lib/mysql \
  -d mysql:8.0

9.2 Docker Compose 配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# docker-compose.yml
version: '3.8'
services:
  mysql:
    image: mysql:8.0
    container_name: mysql-container
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: myapp
      MYSQL_USER: appuser
      MYSQL_PASSWORD: apppassword
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql
      - ./mysql-config:/etc/mysql/conf.d
      - ./init-scripts:/docker-entrypoint-initdb.d
    networks:
      - app-network

volumes:
  mysql-data:

networks:
  app-network:
    driver: bridge

9.3 容器管理命令

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 启动容器
docker start mysql-container

# 停止容器
docker stop mysql-container

# 重启容器
docker restart mysql-container

# 查看容器状态
docker ps
docker ps -a

# 查看容器日志
docker logs mysql-container
docker logs -f mysql-container  # 实时查看

# 进入容器
docker exec -it mysql-container bash
docker exec -it mysql-container mysql -u root -p

# 删除容器
docker rm mysql-container
docker rm -f mysql-container  # 强制删除

9.4 数据备份和恢复（Docker）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# 备份数据库
docker exec mysql-container mysqldump -u root -prootpassword myapp > backup.sql

# 恢复数据库
docker exec -i mysql-container mysql -u root -prootpassword myapp < backup.sql

# 备份数据卷
docker run --rm -v mysql-data:/data -v $(pwd):/backup alpine tar czf /backup/mysql-backup.tar.gz -C /data .

# 恢复数据卷
docker run --rm -v mysql-data:/data -v $(pwd):/backup alpine tar xzf /backup/mysql-backup.tar.gz -C /data

10. 性能优化和监控

10.1 查看系统状态

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
-- 查看MySQL版本
SELECT VERSION();

-- 查看系统变量
SHOW VARIABLES LIKE 'innodb%';
SHOW VARIABLES LIKE 'max_connections';

-- 查看系统状态
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Slow_queries';

-- 查看进程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

10.2 慢查询分析

1
2
3
4
5
6
7
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

10.3 常用配置优化

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# my.cnf 配置示例
[mysqld]
# 基本配置
port = 3306
socket = /var/lib/mysql/mysql.sock

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 连接配置
max_connections = 200
max_connect_errors = 10

# 缓存配置
innodb_buffer_pool_size = 1G
query_cache_size = 64M
query_cache_type = 1

# 日志配置
log-error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# InnoDB配置
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M

11. 常用工具和技巧

11.1 MySQL客户端工具

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# 使用mysql客户端
mysql -u username -p -h hostname -P port database_name

# 执行SQL文件
mysql -u username -p database_name < script.sql

# 执行单条SQL命令
mysql -u username -p -e "SELECT * FROM users LIMIT 5;"

# 输出为CSV格式
mysql -u username -p -e "SELECT * FROM users;" --batch --raw | sed 's/\t/,/g' > output.csv

11.2 实用技巧

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 查看表大小
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema = 'database_name'
ORDER BY (data_length + index_length) DESC;

-- 查看数据库大小
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables 
GROUP BY table_schema;

-- 重置自增ID
ALTER TABLE table_name AUTO_INCREMENT = 1;

-- 查看表的自增值
SHOW TABLE STATUS LIKE 'table_name';

-- 修复表
REPAIR TABLE table_name;

-- 优化表
OPTIMIZE TABLE table_name;

-- 检查表
CHECK TABLE table_name;

12. 注意事项

安全第一：始终使用强密码，合理分配用户权限
定期备份：制定备份策略，确保数据安全
性能监控：定期检查慢查询，优化数据库性能
容器化部署：使用Docker简化MySQL的部署和管理
版本管理：保持MySQL版本更新，关注安全补丁

MySQL 学习笔记

1. MySQL架构

1.1 体系结构

MySQL采用分层架构设计，从上到下分为四层：

graph TD %% 客户端层 Client1[MySQL命令行客户端] --> ConnLayer Client2[JDBC/ODBC] --> ConnLayer Client3[GUI工具
Navicat/Workbench] --> ConnLayer Client4[应用程序] --> ConnLayer %% 连接层 - 最上层 subgraph ConnLayer["🔗 连接层 (Connection Layer)"] Conn1[连接管理
TCP/IP, Socket] Conn2[线程池
线程分配/复用] Conn3[权限认证
用户名/密码验证] Conn4[SSL加密
安全连接] end ConnLayer --> ServiceLayer %% 服务层 - SQL层 subgraph ServiceLayer["⚙️ 服务层 (Service Layer) - SQL处理核心"] direction TB Parser[📝 查询解析器
Parser
词法分析/语法分析] Cache[💾 查询缓存
Query Cache
MySQL 8.0已移除] Optimizer[🧠 查询优化器
Optimizer
执行计划/索引选择] Executor[⚡ 执行器
Executor
调用存储引擎API] Functions[🔧 内置函数
日期/字符串/数学函数] Parser --> Cache Cache --> Optimizer Parser --> Optimizer Optimizer --> Executor Functions --> Executor end ServiceLayer --> EngineLayer %% 存储引擎层 subgraph EngineLayer["🗄️ 存储引擎层 (Storage Engine Layer) - 插件式架构"] direction LR subgraph InnoDB["InnoDB (默认)"] InnoDB1[✅ 事务支持 ACID] InnoDB2[🔒 行级锁] InnoDB3[🔗 外键约束] InnoDB4[📊 MVCC多版本] InnoDB5[💥 崩溃恢复] end subgraph MyISAM["MyISAM (传统)"] MyISAM1[❌ 无事务] MyISAM2[🔒 表级锁] MyISAM3[⚡ 查询速度快] MyISAM4[💾 占用空间小] end subgraph Memory["Memory (内存)"] Memory1[⚡ 数据存内存] Memory2[🚀 极快访问速度] Memory3[💥 重启数据丢失] end subgraph Others["其他引擎"] Archive[Archive
压缩存储] CSV[CSV
CSV格式] Federated[Federated
远程表] end end EngineLayer --> StorageLayer %% 存储层 - 文件系统层 subgraph StorageLayer["💾 存储层 (File System Layer) - 持久化"] direction TB subgraph DataFiles["数据文件"] IBD[.ibd文件
InnoDB表空间] FRM[.frm文件
表结构定义] MYD[.MYD文件
MyISAM数据] MYI[.MYI文件
MyISAM索引] end subgraph LogFiles["日志文件"] RedoLog[Redo Log
重做日志
崩溃恢复] UndoLog[Undo Log
回滚日志
事务回滚] BinLog[Binary Log
二进制日志
主从复制] ErrorLog[Error Log
错误日志] SlowLog[Slow Query Log
慢查询日志] end subgraph TableSpace["表空间管理"] SystemTS[系统表空间
ibdata1] FilePerTable[独立表空间
每表一文件] TempTS[临时表空间
ibtmp1] UndoTS[Undo表空间
undo_001] end end StorageLayer --> FileSystem[🗂️ 操作系统文件系统
Linux/Windows/macOS] %% 样式定义 classDef clientStyle fill:#e1f5fe,stroke:#01579b,stroke-width:2px classDef connStyle fill:#f3e5f5,stroke:#4a148c,stroke-width:2px classDef serviceStyle fill:#e8f5e8,stroke:#1b5e20,stroke-width:2px classDef engineStyle fill:#fff3e0,stroke:#e65100,stroke-width:2px classDef storageStyle fill:#fce4ec,stroke:#880e4f,stroke-width:2px class Client1,Client2,Client3,Client4 clientStyle class ConnLayer connStyle class ServiceLayer serviceStyle class EngineLayer engineStyle class StorageLayer storageStyle

各层详细说明：

连接层（Connection Layer）
- 负责客户端连接管理
- 用户身份验证和权限检查
- 连接池管理，复用连接资源
- 支持多种连接协议（TCP/IP、Unix Socket等）
服务层（Service Layer）
- SQL解析器：词法分析、语法分析，生成解析树
- 查询优化器：基于成本的优化（CBO），选择最优执行计划
- 查询缓存：缓存SELECT语句结果（MySQL 8.0已移除）
- 内置函数：日期函数、字符串函数、数学函数等
引擎层（Storage Engine Layer）
- 可插拔的存储引擎架构
- 不同引擎提供不同的存储机制、索引技巧、锁定水平
- 主要引擎对比：

特性	InnoDB	MyISAM	Memory
事务支持	✓	✗	✗
行级锁	✓	✗	✓
外键约束	✓	✗	✗
崩溃恢复	✓	✗	✗
MVCC	✓	✗	✗

存储层（File System Layer）
- 数据文件：.ibd（InnoDB）、.MYD（MyISAM数据）
- 索引文件：.MYI（MyISAM索引）
- 日志文件：redo log、undo log、binlog、error log

1.2 数据库三范式

数据库设计的三范式是为了减少数据冗余、避免数据异常：

第一范式（1NF）：原子性

表中每个字段都是不可分割的原子数据项
不允许有重复的列

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
-- 违反1NF的例子
CREATE TABLE student_bad (
    id INT,
    name VARCHAR(50),
    subjects VARCHAR(200)  -- 包含多个科目，违反原子性
);

-- 符合1NF的设计
CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE student_subject (
    student_id INT,
    subject VARCHAR(50),
    FOREIGN KEY (student_id) REFERENCES student(id)
);

第二范式（2NF）：完全函数依赖

满足1NF
非主键字段完全依赖于主键，不能只依赖主键的一部分

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
-- 违反2NF的例子（复合主键情况下）
CREATE TABLE order_detail_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(50),  -- 只依赖product_id，违反2NF
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 符合2NF的设计
CREATE TABLE order_detail (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50)
);

第三范式（3NF）：消除传递依赖

满足2NF
非主键字段不能传递依赖于主键

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
-- 违反3NF的例子
CREATE TABLE employee_bad (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    dept_name VARCHAR(50)  -- 传递依赖：emp_id -> dept_id -> dept_name
);

-- 符合3NF的设计
CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT
);

CREATE TABLE department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

1.3 缓存机制

MySQL的缓存机制包括多个层次：

graph LR A[客户端请求] --> B{查询缓存} B -->|命中| C[返回结果] B -->|未命中| D[Buffer Pool] D --> E{页面缓存} E -->|命中| F[返回数据页] E -->|未命中| G[磁盘I/O] G --> H[加载到Buffer Pool] H --> F

1. 查询缓存（Query Cache）

MySQL 8.0已移除，因为在高并发场景下效果不佳
缓存SELECT语句的完整结果
任何表数据变更都会使相关缓存失效

2. InnoDB Buffer Pool

内存中的数据页缓存池
缓存数据页、索引页、插入缓冲、自适应哈希索引等
使用LRU算法管理页面替换

1
2
3
4
5
-- 查看Buffer Pool状态
SHOW ENGINE INNODB STATUS;

-- 配置Buffer Pool大小
SET GLOBAL innodb_buffer_pool_size = 1073741824; -- 1GB

3. 键缓存（Key Cache）

MyISAM引擎专用的索引缓存
只缓存索引页，不缓存数据页

2. 事务

2.1 基本概念

**事务（Transaction）**是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 事务的基本语法
START TRANSACTION;  -- 或 BEGIN
    INSERT INTO account (name, balance) VALUES ('Alice', 1000);
    UPDATE account SET balance = balance - 100 WHERE name = 'Alice';
    UPDATE account SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;  -- 提交事务

-- 回滚事务
START TRANSACTION;
    DELETE FROM important_table WHERE id = 1;
ROLLBACK;  -- 撤销所有操作

2.2 ACID特性

mindmap root((ACID)) Atomicity 原子性全部成功或全部失败通过undo log实现 Consistency 一致性数据完整性约束业务规则约束 Isolation 隔离性并发事务相互隔离通过锁和MVCC实现 Durability 持久性已提交事务永久保存通过redo log实现

原子性（Atomicity）

事务是一个不可分割的工作单位
要么全部完成，要么全部不完成
通过undo log实现回滚

一致性（Consistency）

事务执行前后，数据库从一个一致性状态转换到另一个一致性状态
包括数据完整性约束和业务规则约束

隔离性（Isolation）

并发执行的事务之间不能相互干扰
通过锁机制和MVCC实现

持久性（Durability）

已提交的事务对数据库的修改应该永久保存
通过redo log实现崩溃恢复

2.3 实现A&C

原子性实现：undo log

sequenceDiagram participant Client participant MySQL participant UndoLog participant DataPage Client->>MySQL: BEGIN TRANSACTION Client->>MySQL: UPDATE table SET col=new_val WHERE id=1 MySQL->>UndoLog: 记录旧值 (col=old_val, id=1) MySQL->>DataPage: 更新数据页 alt 提交事务 Client->>MySQL: COMMIT MySQL->>UndoLog: 标记undo log可清理 else 回滚事务 Client->>MySQL: ROLLBACK MySQL->>UndoLog: 读取旧值 MySQL->>DataPage: 恢复数据页 end

一致性实现：约束检查

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 主键约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 0 AND age <= 150)
);

-- 外键约束
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 触发器保证业务一致性
DELIMITER //
CREATE TRIGGER check_balance 
BEFORE UPDATE ON account 
FOR EACH ROW 
BEGIN 
    IF NEW.balance < 0 THEN 
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Balance cannot be negative';
    END IF;
END//
DELIMITER ;

2.4 传播行为&隔离级别

事务隔离级别：

隔离级别	脏读	不可重复读	幻读	实现方式
READ UNCOMMITTED	✓	✓	✓	无锁
READ COMMITTED	✗	✓	✓	读时加共享锁，读完释放
REPEATABLE READ	✗	✗	✓	读时加共享锁，事务结束释放
SERIALIZABLE	✗	✗	✗	读写都加锁

1
2
3
4
5
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 查看当前隔离级别
SELECT @@transaction_isolation;

并发问题演示：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 脏读示例
-- 事务A
BEGIN;
UPDATE account SET balance = 1000 WHERE id = 1;
-- 此时事务B读取到balance=1000（脏读）
ROLLBACK;

-- 不可重复读示例
-- 事务A
BEGIN;
SELECT balance FROM account WHERE id = 1; -- 读取到100
-- 此时事务B修改并提交
SELECT balance FROM account WHERE id = 1; -- 读取到200（不可重复读）
COMMIT;

-- 幻读示例
-- 事务A
BEGIN;
SELECT COUNT(*) FROM account WHERE balance > 100; -- 读取到5条
-- 此时事务B插入新记录并提交
SELECT COUNT(*) FROM account WHERE balance > 100; -- 读取到6条（幻读）
COMMIT;

2.5 MVCC

多版本并发控制（Multi-Version Concurrency Control）

MVCC通过保存数据在某个时间点的快照来实现并发控制：

graph TD A[数据行] --> B[DB_TRX_ID: 事务ID] A --> C[DB_ROLL_PTR: 回滚指针] A --> D[DB_ROW_ID: 行ID] E[Undo Log Chain] --> F[Version 1] F --> G[Version 2] G --> H[Version 3] C --> E

MVCC实现原理：

隐藏字段
- DB_TRX_ID：最后修改该行的事务ID
- DB_ROLL_PTR：指向undo log的指针
- DB_ROW_ID：行ID（如果没有主键）
Read View
- m_ids：当前活跃事务ID列表
- min_trx_id：最小活跃事务ID
- max_trx_id：下一个要分配的事务ID
- creator_trx_id：创建该Read View的事务ID
可见性判断算法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 伪代码
function is_visible(trx_id, read_view):
    if trx_id == read_view.creator_trx_id:
        return true  -- 自己的修改可见
    
    if trx_id < read_view.min_trx_id:
        return true  -- 已提交的历史事务可见
    
    if trx_id >= read_view.max_trx_id:
        return false  -- 未来事务不可见
    
    if trx_id in read_view.m_ids:
        return false  -- 活跃事务不可见
    
    return true  -- 已提交事务可见

3. 锁

3.1 死锁

**死锁定义：**两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象。

graph LR A[事务A] -->|持有锁1| B[资源1] A -->|等待锁2| C[资源2] D[事务B] -->|持有锁2| C D -->|等待锁1| B

死锁示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 会话1
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;  -- 锁定id=1
-- 等待会话2执行
UPDATE account SET balance = balance + 100 WHERE id = 2;  -- 等待id=2的锁

-- 会话2
BEGIN;
UPDATE account SET balance = balance - 50 WHERE id = 2;   -- 锁定id=2
UPDATE account SET balance = balance + 50 WHERE id = 1;   -- 等待id=1的锁，死锁！

死锁检测与处理：

1
2
3
4
5
6
-- 查看死锁信息
SHOW ENGINE INNODB STATUS;

-- 死锁检测配置
SET GLOBAL innodb_deadlock_detect = ON;  -- 开启死锁检测
SET GLOBAL innodb_lock_wait_timeout = 50;  -- 锁等待超时时间

死锁预防策略：

按顺序访问资源

1
2
3
-- 总是按照id顺序更新
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

缩短事务时间
降低隔离级别
使用乐观锁

3.2 读写锁&实现

锁的分类：

graph TD A[MySQL锁] --> B[按锁粒度分类] A --> C[按锁类型分类] A --> D[按加锁方式分类] B --> B1[表级锁] B --> B2[行级锁] B --> B3[页级锁] C --> C1[共享锁 S] C --> C2[排他锁 X] C --> C3[意向锁 IS/IX] D --> D1[显式锁] D --> D2[隐式锁]

行级锁类型：

记录锁（Record Lock）

1
2
-- 锁定具体的行记录
SELECT * FROM users WHERE id = 1 FOR UPDATE;

间隙锁（Gap Lock）

1
2
-- 锁定索引记录之间的间隙，防止幻读
SELECT * FROM users WHERE id BETWEEN 1 AND 10 FOR UPDATE;

临键锁（Next-Key Lock）

1
2
-- 记录锁 + 间隙锁的组合
-- 在REPEATABLE READ级别下默认使用

锁兼容性矩阵：

	S	X	IS	IX
S	✓	✗	✓	✗
X	✗	✗	✗	✗
IS	✓	✗	✓	✓
IX	✗	✗	✓	✓

锁的实现：

1
2
3
4
5
6
7
8
9
-- 查看当前锁信息
SELECT * FROM performance_schema.data_locks;

-- 查看锁等待信息
SELECT * FROM performance_schema.data_lock_waits;

-- 手动加锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;  -- 共享锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;         -- 排他锁

3.3 乐观锁&悲观锁

悲观锁（Pessimistic Locking）

假设并发冲突一定会发生
在读取数据时就加锁

1
2
3
4
5
6
-- 悲观锁示例
BEGIN;
SELECT balance FROM account WHERE id = 1 FOR UPDATE;  -- 加排他锁
-- 执行业务逻辑
UPDATE account SET balance = balance - 100 WHERE id = 1;
COMMIT;

乐观锁（Optimistic Locking）

假设并发冲突很少发生
只在更新时检查数据是否被修改

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 乐观锁实现方式1：版本号
CREATE TABLE account (
    id INT PRIMARY KEY,
    balance DECIMAL(10,2),
    version INT DEFAULT 0
);

-- 读取数据和版本号
SELECT id, balance, version FROM account WHERE id = 1;

-- 更新时检查版本号
UPDATE account 
SET balance = balance - 100, version = version + 1 
WHERE id = 1 AND version = 原版本号;

-- 乐观锁实现方式2：时间戳
CREATE TABLE account (
    id INT PRIMARY KEY,
    balance DECIMAL(10,2),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

UPDATE account 
SET balance = balance - 100 
WHERE id = 1 AND update_time = 原时间戳;

选择策略：

场景	推荐锁类型	原因
读多写少	乐观锁	减少锁竞争，提高并发性能
写多读少	悲观锁	避免大量重试，保证数据一致性
长事务	悲观锁	避免长时间重试
短事务	乐观锁	减少锁持有时间

4. 分库分表

4.1 SQL优化

mindmap root((SQL优化技巧)) 1.索引优化索引设计原则最左前缀原则选择性高的列复合索引优化顺序很重要避免冗余索引索引失效场景函数操作类型转换覆盖索引减少回表提升查询效率 2.查询优化 SQL语句优化避免SELECT * 合理使用WHERE JOIN优化小表驱动大表选择合适JOIN类型子查询优化转换为JOIN 避免相关子查询分页优化延迟关联游标分页 3.表结构优化数据类型选择选择合适长度避免NULL值表设计规范三范式设计适度反范式分区表按时间分区按范围分区垂直拆分冷热数据分离大字段分离 4.执行计划分析 EXPLAIN分析 type字段分析 key字段检查慢查询日志设置阈值定期分析索引使用情况索引命中率冗余索引检查性能监控 QPS/TPS监控响应时间统计 5.配置优化内存配置 innodb_buffer_pool query_cache_size 连接池优化 max_connections 连接超时设置缓存配置查询缓存结果集缓存 IO优化 SSD存储读写分离 6.架构优化分库分表水平分片垂直分片缓存策略 Redis缓存多级缓存读写分离主从复制延迟监控负载均衡轮询策略权重分配 7.事务优化锁优化行锁优于表锁减少锁持有时间隔离级别选择合适级别避免幻读事务大小小事务原则及时提交死锁预防统一加锁顺序超时机制 8.监控调优性能指标 CPU使用率内存使用率工具使用 pt-query-digest MySQL Workbench 报警机制阈值设置及时通知优化流程问题定位持续改进

索引优化

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 创建合适的索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_user_time ON orders(user_id, create_time);

-- 复合索引的最左前缀原则
-- 索引(a,b,c)可以支持的查询：
-- WHERE a = ?
-- WHERE a = ? AND b = ?
-- WHERE a = ? AND b = ? AND c = ?

-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

查询优化

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
-- 避免SELECT *
SELECT id, name, email FROM users WHERE status = 'active';

-- 使用LIMIT限制结果集
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10;

-- 避免在WHERE子句中使用函数
-- 不好的写法
SELECT * FROM orders WHERE YEAR(create_time) = 2023;
-- 好的写法
SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

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

JOIN优化

1
2
3
4
5
6
7
-- 小表驱动大表
SELECT * FROM small_table s 
INNER JOIN large_table l ON s.id = l.small_id;

-- 避免笛卡尔积
SELECT * FROM table1 t1, table2 t2 
WHERE t1.id = t2.ref_id;  -- 必须有连接条件

4.2 MySQL优化

配置优化：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
-- 连接相关
max_connections = 1000              -- 最大连接数
max_connect_errors = 100000         -- 最大连接错误数

-- 缓存相关
innodb_buffer_pool_size = 8G        -- Buffer Pool大小（物理内存的70-80%）
query_cache_size = 0                -- 查询缓存（MySQL 8.0已移除）
table_open_cache = 4000             -- 表缓存

-- 日志相关
innodb_log_file_size = 1G           -- redo log文件大小
innodb_log_buffer_size = 64M        -- redo log缓冲区
sync_binlog = 1                     -- binlog同步策略
innodb_flush_log_at_trx_commit = 1  -- redo log刷盘策略

-- 并发相关
innodb_thread_concurrency = 0       -- InnoDB并发线程数
innodb_read_io_threads = 8          -- 读IO线程数
innodb_write_io_threads = 8         -- 写IO线程数

监控指标：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- 查看连接状态
SHOW PROCESSLIST;
SHOW STATUS LIKE 'Threads_%';

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看表状态
SHOW TABLE STATUS LIKE 'table_name';

4.3 锁优化

锁优化策略：

减少锁持有时间

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 不好的写法：长事务
BEGIN;
SELECT * FROM large_table WHERE condition;  -- 长时间查询
-- ... 复杂业务逻辑 ...
UPDATE other_table SET col = val;
COMMIT;

-- 好的写法：短事务
-- 先查询
SELECT * FROM large_table WHERE condition;
-- 业务逻辑处理
-- 快速更新
BEGIN;
UPDATE other_table SET col = val WHERE id = ?;
COMMIT;

减少锁粒度

1
2
3
4
5
6
-- 使用行级锁而不是表级锁
-- InnoDB默认行级锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 避免全表扫描导致的大量行锁
-- 确保WHERE条件使用索引

锁顺序优化

1
2
3
-- 按照固定顺序获取锁，避免死锁
-- 总是按照主键顺序更新
UPDATE accounts SET balance = balance - 100 WHERE id IN (1, 2, 3) ORDER BY id;

4.4 分表、分库、分区

垂直分库分表：

graph TD A[单体数据库] --> B[用户库] A --> C[订单库] A --> D[商品库] B --> B1[用户基本信息表] B --> B2[用户扩展信息表] C --> C1[订单表] C --> C2[订单详情表]

水平分库分表：

graph TD A[大表] --> B[分片1: user_id % 4 = 0] A --> C[分片2: user_id % 4 = 1] A --> D[分片3: user_id % 4 = 2] A --> E[分片4: user_id % 4 = 3]

分片策略：

范围分片

1
2
3
4
5
-- 按时间范围分片
orders_2023_q1  -- 2023年第一季度
orders_2023_q2  -- 2023年第二季度
orders_2023_q3  -- 2023年第三季度
orders_2023_q4  -- 2023年第四季度

哈希分片

1
2
-- 按用户ID哈希分片
shard_index = user_id % shard_count

一致性哈希

1
2
3
4
5
6
# 一致性哈希算法
import hashlib

def consistent_hash(key, nodes):
    hash_value = int(hashlib.md5(key.encode()).hexdigest(), 16)
    return nodes[hash_value % len(nodes)]

MySQL分区：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 范围分区
CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 哈希分区
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    email VARCHAR(100)
) PARTITION BY HASH(id) PARTITIONS 4;

-- 列表分区
CREATE TABLE sales (
    id INT,
    region VARCHAR(20),
    amount DECIMAL(10,2)
) PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('北京', '天津', '河北'),
    PARTITION p_south VALUES IN ('广东', '广西', '海南'),
    PARTITION p_east VALUES IN ('上海', '江苏', '浙江'),
    PARTITION p_west VALUES IN ('四川', '重庆', '云南')
);

分库分表中间件：

Sharding-JDBC
- 客户端分片
- 轻量级Java框架
- 支持读写分离
MyCat
- 代理层分片
- 支持多种数据库
- 提供SQL路由功能
Atlas
- 360开源的MySQL代理
- 支持读写分离和分库分表

分库分表带来的问题及解决方案：

问题	解决方案
跨分片JOIN	应用层聚合、冗余设计
分布式事务	2PC、TCC、Saga模式
全局唯一ID	雪花算法、UUID、数据库序列
跨分片排序分页	应用层归并排序
数据迁移	双写方案、停机迁移

1
2
3
4
5
6
7
8
9
-- 雪花算法生成全局唯一ID
-- 64位：1位符号位 + 41位时间戳 + 10位机器ID + 12位序列号

-- 分页查询优化
-- 不好的写法：OFFSET性能差
SELECT * FROM large_table ORDER BY id LIMIT 1000000, 10;

-- 好的写法：使用游标
SELECT * FROM large_table WHERE id > last_id ORDER BY id LIMIT 10;

1. 简介#

2. 连接与退出#

2.1 连接MySQL#

2.2 退出MySQL#

3. 数据库操作#

3.1 查看数据库#

3.2 创建数据库#

3.3 使用数据库#

3.4 删除数据库#

4. 表操作#

4.1 查看表#

4.2 创建表#

4.3 修改表结构#

4.4 删除表#

5. 数据操作#

5.1 插入数据#

5.2 查询数据#

5.3 更新数据#

5.4 删除数据#

6. 索引操作#

7. 用户和权限管理#

8. 备份和恢复#

8.1 数据备份#

8.2 数据恢复#

MySQL Docker 容器管理#

9.1 运行MySQL容器#

9.2 Docker Compose 配置#

9.3 容器管理命令#

9.4 数据备份和恢复（Docker）#

10. 性能优化和监控#

10.1 查看系统状态#

10.2 慢查询分析#

10.3 常用配置优化#

11. 常用工具和技巧#

11.1 MySQL客户端工具#

11.2 实用技巧#

12. 注意事项#

MySQL 学习笔记#

1. MySQL架构#

1.1 体系结构#

1.2 数据库三范式#

1.3 缓存机制#

2. 事务#

2.1 基本概念#

2.2 ACID特性#

2.3 实现A&C#

2.4 传播行为&隔离级别#

2.5 MVCC#

3. 锁#

3.1 死锁#

3.2 读写锁&实现#

3.3 乐观锁&悲观锁#

4. 分库分表#

4.1 SQL优化#

4.2 MySQL优化#

4.3 锁优化#

4.4 分表、分库、分区#