1130 lines
24 KiB
Markdown
1130 lines
24 KiB
Markdown
# MySQL 实战指南
|
||
|
||
> 最流行的开源关系型数据库。
|
||
|
||
---
|
||
|
||
## 1. 关系型数据库基础
|
||
|
||
### 1.1 数据库类型
|
||
|
||
| 类型 | 说明 | 代表产品 |
|
||
|------|------|---------|
|
||
| 关系型数据库（RDBMS） | 基于关系模型，表与表之间有关联 | MySQL、PostgreSQL、Oracle |
|
||
| 非关系型数据库（NoSQL） | 非关系模型，文档/键值/列式 | MongoDB、Redis、HBase |
|
||
| NewSQL | 分布式关系型数据库 | TiDB、CockroachDB |
|
||
|
||
### 1.2 关系型数据库核心概念
|
||
|
||
```
|
||
表（Table）→ 行（Row）→ 记录
|
||
→ 列（Column）→ 字段
|
||
→ 主键（Primary Key）→ 唯一标识
|
||
→ 外键（Foreign Key）→ 表间关联
|
||
→ 索引（Index）→ 加速查询
|
||
```
|
||
|
||
### 1.3 范式
|
||
|
||
| 范式 | 要求 |
|
||
|------|------|
|
||
| 1NF | 字段原子性，不可再分 |
|
||
| 2NF | 消除部分依赖，非主键字段完全依赖于主键 |
|
||
| 3NF | 消除传递依赖，非主键字段不依赖于其他非主键字段 |
|
||
|
||
### 1.4 SQL 分类
|
||
|
||
| 分类 | 命令 | 说明 |
|
||
|------|------|------|
|
||
| DDL | CREATE/ALTER/DROP | 定义数据结构 |
|
||
| DML | INSERT/UPDATE/DELETE | 操作数据 |
|
||
| DQL | SELECT | 查询数据 |
|
||
| DCL | GRANT/REVOKE | 权限控制 |
|
||
| TCL | COMMIT/ROLLBACK | 事务控制 |
|
||
|
||
---
|
||
|
||
## 2. 安装 MySQL
|
||
|
||
### 2.1 YUM/RPM 安装（CentOS/RHEL）
|
||
|
||
```bash
|
||
# 1. 下载 Yum Repository
|
||
wget https://repo.mysql.com/mysql80-community-release-el8-4.noarch.rpm
|
||
|
||
# 2. 安装 Repository
|
||
rpm -Uvh mysql80-community-release-el8-4.noarch.rpm
|
||
|
||
# 3. 安装 MySQL Server
|
||
yum install -y mysql-community-server
|
||
|
||
# 4. 启动服务
|
||
systemctl start mysqld
|
||
systemctl enable mysqld
|
||
|
||
# 5. 获取临时密码
|
||
grep 'temporary password' /var/log/mysqld.log
|
||
|
||
# 6. 安全初始化
|
||
mysql_secure_installation
|
||
```
|
||
|
||
### 2.2 Dockerfile 安装
|
||
|
||
```dockerfile
|
||
FROM mysql:8.0
|
||
ENV MYSQL_ROOT_PASSWORD=your_password
|
||
VOLUME /var/lib/mysql
|
||
EXPOSE 3306
|
||
```
|
||
|
||
### 2.3 二进制安装
|
||
|
||
```bash
|
||
# 1. 下载二进制包
|
||
wget https://downloads.mysql.com/archives/get/mysql-8.0.35-linux-glibc2.17-x86_64.tar.gz
|
||
|
||
# 2. 解压并创建用户
|
||
tar xf mysql-8.0.35-linux-glibc2.17-x86_64.tar.gz -C /usr/local/
|
||
ln -s /usr/local/mysql-8.0.35-linux-glibc2.17-x86_64 /usr/local/mysql
|
||
useradd -r -s /sbin/nologin mysql
|
||
|
||
# 3. 初始化
|
||
cd /usr/local/mysql
|
||
mysqld --initialize-insecure --user=mysql
|
||
|
||
# 4. 启动
|
||
mysqld_safe --user=mysql &
|
||
```
|
||
|
||
### 2.4 常见安装问题
|
||
|
||
```bash
|
||
# 解决依赖
|
||
yum install -y libaio numactl
|
||
|
||
# 解决 SSL 问题
|
||
yum update openssl -y
|
||
```
|
||
|
||
---
|
||
|
||
## 3. 管理数据库和表
|
||
|
||
### 3.1 数据库操作
|
||
|
||
```sql
|
||
-- 创建数据库
|
||
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
-- 查看数据库
|
||
SHOW DATABASES;
|
||
SHOW CREATE DATABASE db_name;
|
||
|
||
-- 删除数据库
|
||
DROP DATABASE db_name;
|
||
|
||
-- 使用数据库
|
||
USE db_name;
|
||
```
|
||
|
||
### 3.2 表操作
|
||
|
||
```sql
|
||
-- 创建表
|
||
CREATE TABLE users (
|
||
id INT PRIMARY KEY AUTO_INCREMENT,
|
||
name VARCHAR(50) NOT NULL,
|
||
email VARCHAR(100) UNIQUE,
|
||
password VARCHAR(255) NOT NULL,
|
||
status TINYINT DEFAULT 1,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- 查看表
|
||
SHOW TABLES;
|
||
DESC users;
|
||
SHOW CREATE TABLE users;
|
||
|
||
-- 修改表结构
|
||
-- 添加列
|
||
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
|
||
|
||
-- 修改列
|
||
ALTER TABLE users MODIFY COLUMN name VARCHAR(100);
|
||
|
||
-- 删除列
|
||
ALTER TABLE users DROP COLUMN phone;
|
||
|
||
-- 重命名表
|
||
RENAME TABLE users TO app_users;
|
||
|
||
-- 删除表
|
||
DROP TABLE users;
|
||
```
|
||
|
||
### 3.3 数据操作
|
||
|
||
```sql
|
||
-- 插入数据
|
||
INSERT INTO users (name, email, password) VALUES ('张三', 'zhangsan@example.com', 'pass123');
|
||
|
||
-- 批量插入
|
||
INSERT INTO users (name, email, password) VALUES
|
||
('李四', 'lisi@example.com', 'pass123'),
|
||
('王五', 'wangwu@example.com', 'pass123');
|
||
|
||
-- 更新数据
|
||
UPDATE users SET name = '新名字' WHERE id = 1;
|
||
|
||
-- 删除数据
|
||
DELETE FROM users WHERE id = 1;
|
||
|
||
-- 条件插入或更新
|
||
INSERT INTO users (id, name) VALUES (1, '张三') ON DUPLICATE KEY UPDATE name = '张三';
|
||
|
||
-- 替换
|
||
REPLACE INTO users (id, name) VALUES (1, '张三');
|
||
```
|
||
|
||
### 3.4 查询
|
||
|
||
```sql
|
||
-- 基础查询
|
||
SELECT * FROM users;
|
||
SELECT id, name FROM users WHERE status = 1;
|
||
|
||
-- 排序
|
||
SELECT * FROM users ORDER BY created_at DESC;
|
||
|
||
-- 分页
|
||
SELECT * FROM users LIMIT 10 OFFSET 20;
|
||
|
||
-- 聚合
|
||
SELECT COUNT(*) FROM users;
|
||
SELECT SUM(amount) FROM orders;
|
||
SELECT AVG(age) FROM users;
|
||
SELECT MAX(score) FROM users;
|
||
|
||
-- 分组
|
||
SELECT status, COUNT(*) FROM users GROUP BY status;
|
||
|
||
-- HAVING
|
||
SELECT status, COUNT(*) as cnt FROM users GROUP BY status HAVING cnt > 10;
|
||
|
||
-- 多表查询
|
||
-- 内连接
|
||
SELECT u.name, o.order_no FROM users u INNER JOIN orders o ON u.id = o.user_id;
|
||
|
||
-- 左连接
|
||
SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id = o.user_id;
|
||
|
||
-- 子查询
|
||
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
|
||
|
||
-- 联合查询
|
||
SELECT name FROM users WHERE status = 1 UNION ALL SELECT name FROM admins;
|
||
```
|
||
|
||
---
|
||
|
||
## 4. 用户和权限管理
|
||
|
||
### 4.1 用户操作
|
||
|
||
```sql
|
||
-- 创建用户
|
||
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
|
||
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
|
||
|
||
-- 查看用户
|
||
SELECT user, host FROM mysql.user;
|
||
|
||
-- 重命名用户
|
||
RENAME USER 'old'@'localhost' TO 'new'@'localhost';
|
||
|
||
-- 删除用户
|
||
DROP USER 'username'@'localhost';
|
||
```
|
||
|
||
### 4.2 权限管理
|
||
|
||
```sql
|
||
-- 授予权限
|
||
GRANT SELECT, INSERT ON db_name.* TO 'username'@'localhost';
|
||
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';
|
||
|
||
-- 撤销权限
|
||
REVOKE INSERT ON db_name.* FROM 'username'@'localhost';
|
||
|
||
-- 查看权限
|
||
SHOW GRANTS FOR 'username'@'localhost';
|
||
|
||
-- 刷新权限
|
||
FLUSH PRIVILEGES;
|
||
```
|
||
|
||
### 4.3 角色管理（MySQL 8.0）
|
||
|
||
```sql
|
||
-- 创建角色
|
||
CREATE ROLE app_read, app_write;
|
||
|
||
-- 授予角色权限
|
||
GRANT SELECT ON db_name.* TO app_read;
|
||
GRANT SELECT, INSERT, UPDATE, DELETE ON db_name.* TO app_write;
|
||
|
||
-- 授予用户角色
|
||
GRANT app_read TO 'username'@'localhost';
|
||
|
||
-- 查看角色
|
||
SELECT * FROM mysql.user WHERE account_type = 'role';
|
||
```
|
||
|
||
### 4.4 密码策略（MySQL 8.0）
|
||
|
||
```sql
|
||
-- 查看密码策略
|
||
SHOW VARIABLES LIKE 'validate_password%';
|
||
|
||
-- 设置密码策略
|
||
SET GLOBAL validate_password.policy = 'LOW';
|
||
SET GLOBAL validate_password.length = 8;
|
||
|
||
-- 锁定账户
|
||
ALTER USER 'username'@'localhost' ACCOUNT LOCK;
|
||
ALTER USER 'username'@'localhost' ACCOUNT UNLOCK;
|
||
```
|
||
|
||
---
|
||
|
||
## 5. 函数、存储过程、触发器和事件
|
||
|
||
### 5.1 内置函数
|
||
|
||
```sql
|
||
-- 字符串函数
|
||
SELECT CONCAT('Hello', ' ', 'World');
|
||
SELECT UPPER(name), LOWER(email);
|
||
SELECT SUBSTRING('Hello', 1, 3);
|
||
SELECT TRIM(' abc ');
|
||
SELECT LPAD('5', 5, '0');
|
||
|
||
-- 数值函数
|
||
SELECT ABS(-5);
|
||
SELECT ROUND(3.14159, 2);
|
||
SELECT CEIL(3.1), FLOOR(3.9);
|
||
SELECT MOD(10, 3);
|
||
|
||
-- 日期函数
|
||
SELECT NOW(), CURDATE(), CURTIME();
|
||
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
|
||
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
|
||
SELECT DATEDIFF('2024-01-01', '2023-01-01');
|
||
|
||
-- 条件函数
|
||
SELECT IF(status = 1, '启用', '禁用');
|
||
SELECT CASE status WHEN 1 THEN '启用' ELSE '禁用' END;
|
||
SELECT COALESCE(NULL, 'default');
|
||
```
|
||
|
||
### 5.2 自定义函数
|
||
|
||
```sql
|
||
-- 创建函数
|
||
DELIMITER //
|
||
CREATE FUNCTION get_user_name(uid INT)
|
||
RETURNS VARCHAR(100)
|
||
DETERMINISTIC
|
||
BEGIN
|
||
DECLARE result VARCHAR(100);
|
||
SELECT name INTO result FROM users WHERE id = uid;
|
||
RETURN result;
|
||
END //
|
||
DELIMITER ;
|
||
|
||
-- 调用函数
|
||
SELECT get_user_name(1);
|
||
|
||
-- 删除函数
|
||
DROP FUNCTION get_user_name;
|
||
```
|
||
|
||
### 5.3 存储过程
|
||
|
||
```sql
|
||
-- 创建存储过程
|
||
DELIMITER //
|
||
CREATE PROCEDURE get_user_by_id(IN uid INT)
|
||
BEGIN
|
||
SELECT * FROM users WHERE id = uid;
|
||
END //
|
||
|
||
CREATE PROCEDURE insert_user(IN name VARCHAR(50), IN email VARCHAR(100))
|
||
BEGIN
|
||
INSERT INTO users (name, email) VALUES (name, email);
|
||
SELECT LAST_INSERT_ID() AS user_id;
|
||
END //
|
||
|
||
CREATE PROCEDURE update_users_status()
|
||
BEGIN
|
||
UPDATE users SET status = 1 WHERE status = 0;
|
||
END //
|
||
DELIMITER ;
|
||
|
||
-- 调用
|
||
CALL get_user_by_id(1);
|
||
|
||
-- 查看
|
||
SHOW PROCEDURE STATUS;
|
||
|
||
-- 删除
|
||
DROP PROCEDURE update_users_status;
|
||
```
|
||
|
||
### 5.4 触发器
|
||
|
||
```sql
|
||
-- 创建触发器（插入后更新）
|
||
DELIMITER //
|
||
CREATE TRIGGER after_user_insert
|
||
AFTER INSERT ON users FOR EACH ROW
|
||
BEGIN
|
||
INSERT INTO user_logs (user_id, action) VALUES (NEW.id, 'insert');
|
||
END //
|
||
|
||
-- 删除后记录
|
||
CREATE TRIGGER after_user_delete
|
||
AFTER DELETE ON users FOR EACH ROW
|
||
BEGIN
|
||
INSERT INTO user_logs (user_id, action, created_at) VALUES (OLD.id, 'delete', NOW());
|
||
END //
|
||
|
||
-- 更新前记录
|
||
CREATE TRIGGER before_user_update
|
||
BEFORE UPDATE ON users FOR EACH ROW
|
||
BEGIN
|
||
SET NEW.updated_at = NOW();
|
||
END //
|
||
DELIMITER ;
|
||
|
||
-- 查看
|
||
SHOW TRIGGERS;
|
||
|
||
-- 删除
|
||
DROP TRIGGER after_user_insert;
|
||
```
|
||
|
||
### 5.5 事件（定时任务）
|
||
|
||
```sql
|
||
-- 开启事件调度器
|
||
SET GLOBAL event_scheduler = ON;
|
||
|
||
-- 创建事件
|
||
DELIMITER //
|
||
CREATE EVENT clean_old_logs
|
||
ON SCHEDULE EVERY 1 DAY
|
||
DO BEGIN
|
||
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
|
||
END //
|
||
DELIMITER ;
|
||
|
||
-- 查看
|
||
SHOW EVENTS;
|
||
SHOW EVENTS FROM db_name;
|
||
|
||
-- 开启/关闭事件
|
||
ALTER EVENT clean_old_logs ENABLE;
|
||
ALTER EVENT clean_old_logs DISABLE;
|
||
|
||
-- 删除
|
||
DROP EVENT clean_old_logs;
|
||
```
|
||
|
||
---
|
||
|
||
## 6. MySQL 架构
|
||
|
||
### 6.1 架构图
|
||
|
||
```
|
||
┌─────────────────────────────────────────┐
|
||
│ 连接层（Connection Layer） │
|
||
│ 连接管理器 │ 认证 │ 线程池 │ 连接缓冲 │
|
||
└──────────────────┬──────────────────────┘
|
||
│
|
||
┌──────────────────▼──────────────────────┐
|
||
│ SQL 层（SQL Layer） │
|
||
│ 解析器 │ 查询优化器 │ 预处理器 │ 执行器 │
|
||
└──────────────────┬──────────────────────┘
|
||
│
|
||
┌──────────────────▼──────────────────────┐
|
||
│ 存储引擎层（Storage Engine） │
|
||
│ InnoDB │ MyISAM │ Memory │ CSV │
|
||
└──────────────────┬──────────────────────┘
|
||
│
|
||
┌──────────────────▼──────────────────────┐
|
||
│ 物理层（Physical Layer） │
|
||
│ 数据文件 │ 索引文件 │ 日志文件 │ redo │
|
||
└─────────────────────────────────────────┘
|
||
```
|
||
|
||
### 6.2 解析流程
|
||
|
||
```
|
||
SQL 语句
|
||
↓
|
||
词法分析（分词）→ 语法分析（解析）→ 语义分析（理解）→ 逻辑计划→ 物理执行计划→ 执行
|
||
```
|
||
|
||
### 6.3 缓冲池
|
||
|
||
| 缓冲池 | 说明 |
|
||
|--------|------|
|
||
| Buffer Pool | 缓存数据页和索引页 |
|
||
| Log Buffer | Redo 日志缓冲 |
|
||
| Change Buffer | 变更缓冲（二级索引） |
|
||
| Adaptive Hash Index | 自适应哈希索引 |
|
||
|
||
---
|
||
|
||
## 7. 存储引擎
|
||
|
||
### 7.1 引擎对比
|
||
|
||
| 引擎 | 事务 | 行锁 | 外键 | 全文索引 | 使用场景 |
|
||
|------|------|------|------|----------|----------|
|
||
| **InnoDB** | 支持 | 支持 | 支持 | 支持 | 默认，事务应用 |
|
||
| MyISAM | 不支持 | 表锁 | 不支持 | 支持 | 只读/静态表 |
|
||
| Memory | 不支持 | 表锁 | 不支持 | 不支持 | 临时表/缓存 |
|
||
| CSV | 不支持 | 表锁 | 不支持 | 不支持 | 数据交换 |
|
||
| Archive | 不支持 | 行锁 | 不支持 | 不支持 | 日志/归档 |
|
||
| Blackhole | 不支持 | 不支持 | 不支持 | 不支持 | 复制中继 |
|
||
|
||
### 7.2 InnoDB
|
||
|
||
```sql
|
||
-- 查看表引擎
|
||
SHOW CREATE TABLE users;
|
||
|
||
-- 修改表引擎
|
||
ALTER TABLE users ENGINE = InnoDB;
|
||
|
||
-- 查看 InnoDB 状态
|
||
SHOW ENGINE InnoDB STATUS;
|
||
|
||
-- InnoDB 配置
|
||
SHOW VARIABLES LIKE 'innodb_%';
|
||
```
|
||
|
||
### 7.3 表空间
|
||
|
||
```sql
|
||
-- 独立表空间
|
||
innodb_file_per_table = 1
|
||
|
||
-- 查看表空间文件
|
||
SHOW VARIABLES LIKE 'innodb_data_file_path';
|
||
```
|
||
|
||
---
|
||
|
||
## 8. 服务器选项和变量
|
||
|
||
### 8.1 重要系统变量
|
||
|
||
```sql
|
||
-- 查看
|
||
SHOW VARIABLES;
|
||
SHOW VARIABLES LIKE 'max_connections';
|
||
SHOW GLOBAL VARIABLES;
|
||
|
||
-- 永久修改（配置文件）
|
||
max_connections = 500
|
||
|
||
-- 临时修改（会话）
|
||
SET GLOBAL max_connections = 500;
|
||
SET SESSION sort_buffer_size = 256*1024;
|
||
```
|
||
|
||
### 8.2 常用配置
|
||
|
||
| 变量 | 说明 | 推荐值 |
|
||
|------|------|--------|
|
||
| max_connections | 最大连接数 | 500-1000 |
|
||
| query_cache_size | 查询缓存（8.0 已移除） | - |
|
||
| innodb_buffer_pool_size | 缓冲池大小 | 70-80% 内存 |
|
||
| innodb_log_file_size | 日志文件大小 | 1-2GB |
|
||
| max_allowed_packet | 最大包大小 | 64M |
|
||
| character_set_server | 默认字符集 | utf8mb4 |
|
||
|
||
### 8.3 状态变量
|
||
|
||
```sql
|
||
-- 查看
|
||
SHOW STATUS;
|
||
SHOW GLOBAL STATUS LIKE 'Threads_connected';
|
||
SHOW GLOBAL STATUS LIKE 'Qcache%';
|
||
|
||
-- 关键状态
|
||
Threads_connected -- 当前<E5BD93><E5898D><EFBFBD>接数
|
||
Threads_running -- 活跃连接数
|
||
Questions -- 总查询数
|
||
Slow_queries -- 慢查询数
|
||
```
|
||
|
||
### 8.4 配置文件示例
|
||
|
||
```ini
|
||
[mysqld]
|
||
port = 3306
|
||
datadir = /var/lib/mysql
|
||
socket = /var/lib/mysql/mysql.sock
|
||
user = mysql
|
||
character-set-server = utf8mb4
|
||
collation-server = utf8mb4_unicode_ci
|
||
max_connections = 500
|
||
innodb_buffer_pool_size = 2G
|
||
innodb_log_file_size = 1G
|
||
slow_query_log = 1
|
||
slow_query_log_file = /var/log/mysql/slow.log
|
||
long_query_time = 2
|
||
```
|
||
|
||
---
|
||
|
||
## 9. 优化查询和索引管理
|
||
|
||
### 9.1 索引类型
|
||
|
||
| 类型 | 说明 | 示例 |
|
||
|------|------|------|
|
||
| 主键索引 | 唯一，非空 | PRIMARY KEY |
|
||
| 唯一索引 | 唯一，可空 | UNIQUE |
|
||
| 普通索引 | 可重复 | INDEX |
|
||
| 全文索引 | 文本搜索 | FULLTEXT |
|
||
| 组合索引 | 多列复合 | INDEX(a, b, c) |
|
||
|
||
### 9.2 索引操作
|
||
|
||
```sql
|
||
-- 创建索引
|
||
CREATE INDEX idx_name ON users(name);
|
||
CREATE UNIQUE INDEX idx_email ON users(email);
|
||
ALTER TABLE users ADD INDEX idx_status_created(status, created_at);
|
||
|
||
-- 查看索引
|
||
SHOW INDEX FROM users;
|
||
|
||
-- 删除索引
|
||
DROP INDEX idx_name ON users;
|
||
ALTER TABLE users DROP INDEX idx_email;
|
||
```
|
||
|
||
### 9.3 慢查询分析
|
||
|
||
```sql
|
||
-- 开启慢查询日志
|
||
SET GLOBAL slow_query_log = 'ON';
|
||
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
|
||
SET GLOBAL long_query_time = 2;
|
||
|
||
-- 查看慢查询
|
||
SELECT * FROM mysql.slow_log ORDER BY start_time DESC;
|
||
|
||
-- 使用 EXPLAIN
|
||
EXPLAIN SELECT * FROM users WHERE name = '张三';
|
||
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';
|
||
```
|
||
|
||
### 9.4 查询优化技巧
|
||
|
||
```sql
|
||
-- 避免 SELECT *
|
||
SELECT id, name FROM users WHERE name = '张三';
|
||
|
||
-- 使用 LIMIT
|
||
SELECT * FROM users LIMIT 10;
|
||
|
||
-- 避免函数作用于索引列
|
||
-- 低效：WHERE YEAR(created_at) = 2024
|
||
-- 高效：WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
|
||
|
||
-- 使用覆盖索引
|
||
SELECT id, name FROM users WHERE name = '张三';
|
||
|
||
-- 避免隐式转换
|
||
-- 低效：WHERE phone = 13800138000
|
||
-- 高效：WHERE phone = '13800138000'
|
||
|
||
-- 合理使用 UNION
|
||
-- 使用 UNION ALL 避免重复检查
|
||
SELECT name FROM users WHERE status = 1
|
||
UNION ALL
|
||
SELECT name FROM admins WHERE status = 1;
|
||
```
|
||
|
||
### 9.5 索引失效场景
|
||
|
||
- 使用 `!=`、`<>`、NOT IN
|
||
- 使用 `IS NULL`、`IS NOT NULL`
|
||
- 使用函数或运算
|
||
- 类型转换
|
||
- LIKE 开头使用通配符
|
||
- 多列索引不遵循最左前缀
|
||
|
||
---
|
||
|
||
## 10. 锁和事务管理
|
||
|
||
### 10.1 事务特性（ACID）
|
||
|
||
| 特性 | 说明 |
|
||
|------|------|
|
||
| Atomicity（原子性） | 事务要么全成功，要么全失败 |
|
||
| Consistency（一致性） | 事务前后数据库状态一致 |
|
||
| Isolation（隔离性） | 并发事务互不干扰 |
|
||
| Durability（持久性） | 事务提交后永久保存 |
|
||
|
||
### 10.2 事务控制
|
||
|
||
```sql
|
||
-- 开启事务
|
||
START TRANSACTION;
|
||
BEGIN;
|
||
|
||
-- 提交
|
||
COMMIT;
|
||
|
||
-- 回滚
|
||
ROLLBACK;
|
||
|
||
-- 设置保存点
|
||
SAVEPOINT sp1;
|
||
|
||
-- 回滚到保存点
|
||
ROLLBACK TO SAVEPOINT sp1;
|
||
```
|
||
|
||
### 10.3 隔离级别
|
||
|
||
| 级别 | 脏读 | 不可重复读 | 幻读 |
|
||
|------|------|-----------|------|
|
||
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
|
||
| READ COMMITTED | 不可能 | 可能 | 可能 |
|
||
| REPEATABLE READ | 不可能 | 不可能 | 可能（InnoDB 不可能） |
|
||
| SERIALIZABLE | 不可能 | 不可能 | 不可能 |
|
||
|
||
```sql
|
||
-- 查看/设置隔离级别
|
||
SELECT @@transaction_isolation;
|
||
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
||
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
|
||
```
|
||
|
||
### 10.4 锁
|
||
|
||
```sql
|
||
-- 行锁
|
||
SELECT * FROM users WHERE id = 1 FOR UPDATE;
|
||
|
||
-- 读锁
|
||
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
|
||
|
||
-- 表锁
|
||
LOCK TABLES users READ;
|
||
LOCK TABLES users WRITE;
|
||
UNLOCK TABLES;
|
||
|
||
-- 查看锁
|
||
SHOW ENGINE InnoDB STATUS;
|
||
SELECT * FROM information_schema. innodb_lock_waits;
|
||
```
|
||
|
||
### 10.5 死锁
|
||
|
||
```sql
|
||
-- 查看死锁信息
|
||
SHOW ENGINE InnoDB STATUS;
|
||
|
||
-- 避免死锁
|
||
-- 1. 按相同顺序操作数据
|
||
-- 2. 缩短事务时间
|
||
-- 3. 小批量更新
|
||
-- 4. 使用低隔离级别
|
||
```
|
||
|
||
---
|
||
|
||
## 11. 日志管理
|
||
|
||
### 11.1 日志类型
|
||
|
||
| 日志 | 说明 | 位置 |
|
||
|------|------|------|
|
||
| Error Log | 错误日志 | log_error |
|
||
| Slow Query Log | 慢查询日志 | slow_query_log_file |
|
||
| General Log | 通用查询日志 | general_log_file |
|
||
| Binary Log | 二进制日志（复制/恢复） | log_bin |
|
||
| Relay Log | 中继日志 | relay_log |
|
||
| Redo Log | 重做日志 | innodb_log_file |
|
||
|
||
### 11.2 二进制日志
|
||
|
||
```sql
|
||
-- 开启
|
||
log_bin = /var/log/mysql/mysql-bin
|
||
server_id = 1
|
||
expire_logs_days = 7
|
||
|
||
-- 查看
|
||
SHOW MASTER STATUS;
|
||
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
|
||
|
||
-- 使用 binlog 恢复
|
||
mysqlbinlog mysql-bin.000001 | mysql -u root -p
|
||
|
||
-- 基于位置恢复
|
||
mysqlbinlog --stop-position=1234 mysql-bin.000001 | mysql
|
||
|
||
-- 基于时间恢复
|
||
mysqlbinlog --stop-datetime="2024-01-01 12:00:00" mysql-bin.000001 | mysql
|
||
```
|
||
|
||
### 11.3 慢查询日志
|
||
|
||
```sql
|
||
SET GLOBAL slow_query_log = 'ON';
|
||
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
|
||
SET GLOBAL long_query_time = 2;
|
||
SET GLOBAL log_queries_not_using_indexes = 'ON';
|
||
```
|
||
|
||
### 11.4 通用查询日志
|
||
|
||
```sql
|
||
SET GLOBAL general_log = 'ON';
|
||
SET GLOBAL general_log_file = '/var/log/mysql/query.log';
|
||
```
|
||
|
||
---
|
||
|
||
## 12. 备份还原
|
||
|
||
### 12.1 逻辑备份（mysqldump）
|
||
|
||
```bash
|
||
# 备份单个库
|
||
mysqldump -u root -p db_name > backup.sql
|
||
|
||
# 备份所有库
|
||
mysqldump -u root -p --all-databases > backup.sql
|
||
|
||
# 备份指定表
|
||
mysqldump -u root -p db_name table1 table2 > backup.sql
|
||
|
||
# 备份结构（不备份数据）
|
||
mysqldump -u root -p -d db_name > structure.sql
|
||
|
||
# 备份数据（不备份结构）
|
||
mysqldump -u root -p -t db_name > data.sql
|
||
|
||
# 远程备份
|
||
mysqldump -h remote_host -u root -p db_name > backup.sql
|
||
|
||
# 压缩备份
|
||
mysqldump -u root -p db_name | gzip > backup.sql.gz
|
||
```
|
||
|
||
### 12.2 逻辑还原
|
||
|
||
```bash
|
||
# 还原
|
||
mysql -u root -p db_name < backup.sql
|
||
|
||
# 压缩还原
|
||
gunzip < backup.sql.gz | mysql -u root -p db_name
|
||
```
|
||
|
||
### 12.3 物理备份（xtrabackup）
|
||
|
||
```bash
|
||
# 安装
|
||
yum install percona-xtrabackup
|
||
|
||
# 全量备份
|
||
xtrabackup --backup --target-dir=/backup/full/
|
||
|
||
# 增量备份
|
||
xtrabackup --backup --target-dir=/backup/inc1/ --incremental-basedir=/backup/full/
|
||
|
||
# 准备还原
|
||
xtrabackup --prepare --target-dir=/backup/full/
|
||
|
||
# 还原
|
||
xtrabackup --copy-back --target-dir=/backup/full/
|
||
|
||
# 停止 MySQL
|
||
systemctl stop mysqld
|
||
|
||
# 删除数据文件
|
||
rm -rf /var/lib/mysql/*
|
||
|
||
# 复制还原
|
||
xtrabackup --copy-back --target-dir=/backup/full/
|
||
|
||
# 修改权限
|
||
chown -R mysql:mysql /var/lib/mysql
|
||
|
||
# 启动
|
||
systemctl start mysqld
|
||
```
|
||
|
||
### 12.4 定时备份脚本
|
||
|
||
```bash
|
||
#!/bin/bash
|
||
BACKUP_DIR="/backup"
|
||
DATE=$(date +%Y%m%d_%H%M%S)
|
||
mysqldump -u root -p'your_password' --all-databases | gzip > $BACKUP_DIR/mysql_$DATE.sql.gz
|
||
find $BACKUP_DIR -mtime +7 -name "*.gz" -delete
|
||
```
|
||
|
||
crontab：
|
||
```
|
||
0 2 * * * /path/to/backup.sh
|
||
```
|
||
|
||
---
|
||
|
||
## 13. MySQL 集群
|
||
|
||
### 13.1 主从复制
|
||
|
||
```
|
||
主库（Master） → 从库（Slave）
|
||
↓
|
||
写入 Binary Log → 读取 Relay Log → 重放
|
||
```
|
||
|
||
**主库配置**：
|
||
```ini
|
||
[mysqld]
|
||
server-id = 1
|
||
log_bin = /var/log/mysql/mysql-bin
|
||
```
|
||
|
||
**从库配置**：
|
||
```ini
|
||
[mysqld]
|
||
server-id = 2
|
||
relay_log = /var/log/mysql/mysql-relay-bin
|
||
read_only = 1
|
||
```
|
||
|
||
**配置复制**：
|
||
```sql
|
||
-- 主库创建复制用户
|
||
CREATE USER 'repl'@'%' IDENTIFIED BY 'replpass';
|
||
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
|
||
|
||
-- 从库配置主库
|
||
CHANGE MASTER TO
|
||
MASTER_HOST='master_host',
|
||
MASTER_USER='repl',
|
||
MASTER_PASSWORD='replpass',
|
||
MASTER_LOG_FILE='mysql-bin.000001',
|
||
MASTER_LOG_POS=1234;
|
||
|
||
-- 启动复制
|
||
START SLAVE;
|
||
|
||
-- 查看状态
|
||
SHOW SLAVE STATUS;
|
||
```
|
||
|
||
### 13.2 主主复制
|
||
|
||
```
|
||
库A ←→ 库B
|
||
```
|
||
|
||
配置双向复制即可，注意自增 ID 冲突：
|
||
```ini
|
||
auto_increment_increment = 2
|
||
auto_increment_offset = 1 # 节点1
|
||
auto_increment_offset = 2 # 节点2
|
||
```
|
||
|
||
### 13.3 读写分离（ProxySQL）
|
||
|
||
```bash
|
||
# 安装
|
||
yum install proxysql2
|
||
|
||
# 配置
|
||
SELECT * FROM mysql_servers;
|
||
SELECT * FROM mysql_users;
|
||
SELECT * FROM mysql_query_rules;
|
||
LOAD MYSQL SERVER TO RUNTIME;
|
||
LOAD MYSQL USER TO RUNTIME;
|
||
```
|
||
|
||
### 13.4 MySQL Router
|
||
|
||
```bash
|
||
# 安装
|
||
yum install mysql-router
|
||
|
||
# 配置
|
||
[logger]
|
||
level = INFO
|
||
|
||
[metadata_cache]
|
||
cluster_type = gr
|
||
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
|
||
metadata_cluster = mycluster
|
||
router_uuid = cluster_router
|
||
|
||
[bind_address]
|
||
address = 0.0.0.0
|
||
force_primary_connection = true
|
||
```
|
||
|
||
---
|
||
|
||
## 14. 压力测试
|
||
|
||
### 14.1 sysbench
|
||
|
||
```bash
|
||
# 安装
|
||
yum install sysbench
|
||
|
||
# 准备测试
|
||
sysbench /usr/share/sysbench/oltp_common.lua \
|
||
--mysql-host=127.0.0.1 \
|
||
--mysql-port=3306 \
|
||
--mysql-user=root \
|
||
--mysql-password=password \
|
||
--mysql-db=test \
|
||
--tables=10 \
|
||
--table-size=100000 \
|
||
prepare
|
||
|
||
# 运行测试
|
||
sysbench /usr/share/sysbench/oltp_read_write.lua \
|
||
--mysql-host=127.0.0.1 \
|
||
--mysql-port=3306 \
|
||
--mysql-user=root \
|
||
--mysql-password=password \
|
||
--mysql-db=test \
|
||
--tables=10 \
|
||
--table-size=100000 \
|
||
--threads=100 \
|
||
--time=60 \
|
||
run
|
||
|
||
# 清理
|
||
sysbench /usr/share/sysbench/oltp_common.lua \
|
||
--mysql-db=test \
|
||
--tables=10 \
|
||
cleanup
|
||
```
|
||
|
||
### 14.2 mysqlslap
|
||
|
||
```bash
|
||
# 单轮测试
|
||
mysqlslap --create-schema=testdb --query="SELECT * FROM users" --concurrency=50 --iterations=10
|
||
|
||
# 完整测试
|
||
mysqlslap --user=root --password=password \
|
||
--create-schema=testdb \
|
||
--query="SELECT COUNT(*) FROM orders WHERE created_at > NOW()" \
|
||
--concurrency=10,50,100 \
|
||
--iterations=5 \
|
||
--no-drop
|
||
```
|
||
|
||
### 14.3 tpcc-mysql
|
||
|
||
```bash
|
||
# 安装
|
||
wget https://github.com/PerconaLab/tpcc-mysql/archive/refs/heads/master.zip
|
||
unzip master.zip
|
||
|
||
# 编译
|
||
cd tpcc-mysql-master/src
|
||
make
|
||
|
||
# 创建表
|
||
mysql -u root -p -e "CREATE DATABASE tpcc;"
|
||
mysql -u root -p tpcc < create_table.sql
|
||
mysql -u root -p tpcc < add_fkeys.sql
|
||
|
||
# 导入数据
|
||
tpcc_load -h localhost -P 3306 -d tpcc -u root -p "" -w 10
|
||
|
||
# 运行测试
|
||
tpcc_start -h localhost -P 3306 -d tpcc -u root -p "" -w 10 -c 10 -r 120 -l 1800
|
||
```
|
||
|
||
### 14.4 测试报告解读
|
||
|
||
| 指标 | 含义 | 基准 |
|
||
|------|------|------|
|
||
| TPS | 每秒事务数 | > 1000 |
|
||
| QPS | 每秒查询数 | > 5000 |
|
||
| 响应时间 | 平均延迟 | < 50ms |
|
||
| 95%延迟 | P95 延迟 | < 100ms |
|
||
| 连接数 | 并发连接 | 可用连接 80% |
|
||
|
||
---
|
||
|
||
## 15. 常用命令速查
|
||
|
||
### 15.1 连接管理
|
||
|
||
| 场景 | 命令 |
|
||
|------|------|
|
||
| 连接本地 | `mysql -u root -p` |
|
||
| 连接远程 | `mysql -h host -P 3306 -u root -p` |
|
||
| 导入 SQL | `mysql -u root -p db < file.sql` |
|
||
| 导出 SQL | `mysqldump -u root -p db > file.sql` |
|
||
|
||
### 15.2 状态查看
|
||
|
||
| 场景 | 命令 |
|
||
|------|------|
|
||
| 查看进程 | `SHOW PROCESSLIST;` |
|
||
| 查看状态 | `SHOW STATUS;` |
|
||
| 查看变量 | `SHOW VARIABLES;` |
|
||
| 查看引擎 | `SHOW ENGINES;` |
|
||
| 查看连接 | `SHOW STATUS LIKE 'Threads%';` |
|
||
|
||
### 15.3 性能分析
|
||
|
||
| 场景 | 命令 |
|
||
|------|------|
|
||
| 分析查询 | `EXPLAIN SELECT...` |
|
||
| 分析成本 | `EXPLAIN ANALYZE SELECT...` |
|
||
| 查看慢查询 | `SELECT * FROM mysql.slow_log;` |
|
||
| 查看索引 | `SHOW INDEX FROM table;` |
|
||
|
||
---
|
||
|
||
## 16. 小结
|
||
|
||
| 类别 | 关键点 |
|
||
|------|--------|
|
||
| 存储引擎 | InnoDB（默认，事务安全） |
|
||
| 字符集 | utf8mb4（支持 emoji） |
|
||
| 隔离级别 | READ COMMITTED |
|
||
| 备份 | mysqldump（逻辑）/xtrabackup（物理） |
|
||
| 优化 | 索引 + EXPLAIN + 慢查询日志 |
|
||
| 集群 | 主从复制/读写分离 |
|
||
|
||
> 建议：生产环境使用 MySQL 8.0 + InnoDB，配置主从复制，定期备份。 |