MySQL 实战案例
真实场景下的 MySQL 应用和最佳实践
1. 电商系统设计
1.1 表结构设计
用户表
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
phone VARCHAR(20) UNIQUE COMMENT '手机号',
password VARCHAR(255) NOT NULL COMMENT '密码',
avatar VARCHAR(200) COMMENT '头像URL',
status TINYINT DEFAULT 1 COMMENT '状态：0-禁用，1-正常',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_phone (phone),
INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
商品表
CREATE TABLE products (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
category_id INT UNSIGNED NOT NULL COMMENT '分类ID',
name VARCHAR(200) NOT NULL COMMENT '商品名称',
price DECIMAL(10,2) NOT NULL COMMENT '价格',
stock INT UNSIGNED DEFAULT 0 COMMENT '库存',
sales INT UNSIGNED DEFAULT 0 COMMENT '销量',
status TINYINT DEFAULT 1 COMMENT '状态：0-下架，1-上架',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_category (category_id),
INDEX idx_status_sales (status, sales DESC),
FULLTEXT INDEX ft_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';
订单表
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
total_amount DECIMAL(10,2) NOT NULL COMMENT '总金额',
status TINYINT DEFAULT 0 COMMENT '状态：0-待支付，1-已支付，2-已发货，3-已完成，4-已取消',
pay_time DATETIME COMMENT '支付时间',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
INDEX idx_user_id (user_id),
INDEX idx_order_no (order_no),
INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
CREATE TABLE order_items (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
product_id INT UNSIGNED NOT NULL COMMENT '商品ID',
product_name VARCHAR(200) NOT NULL COMMENT '商品名称（冗余）',
price DECIMAL(10,2) NOT NULL COMMENT '单价',
quantity INT UNSIGNED NOT NULL COMMENT '数量',
amount DECIMAL(10,2) NOT NULL COMMENT '小计',
INDEX idx_order_id (order_id),
INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';
1.2 秒杀场景
库存扣减（乐观锁）
-- ❌ 不安全（并发问题）
UPDATE products SET stock = stock - 1 WHERE id = 1;
-- ✅ 乐观锁（基于版本号）
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND stock > 0 AND version = 10;
-- 检查影响行数
affected_rows == 1 ? "成功" : "失败（库存不足或版本冲突）"
超卖问题
-- ✅ 检查库存
UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock >= 1;
-- 检查 affected_rows
if (affected_rows > 0) {
// 扣减成功
} else {
// 库存不足
}
2. 权限系统设计（RBAC）
2.1 表结构
-- 用户表
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;
-- 角色表
CREATE TABLE roles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名',
description VARCHAR(200) COMMENT '描述'
) ENGINE=InnoDB;
-- 权限表
CREATE TABLE permissions (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE COMMENT '权限名',
resource VARCHAR(100) NOT NULL COMMENT '资源',
action VARCHAR(50) NOT NULL COMMENT '操作'
) ENGINE=InnoDB;
-- 用户角色关联表
CREATE TABLE user_roles (
user_id INT UNSIGNED NOT NULL,
role_id INT UNSIGNED NOT NULL,
PRIMARY KEY (user_id, role_id),
INDEX idx_role_id (role_id)
) ENGINE=InnoDB;
-- 角色权限关联表
CREATE TABLE role_permissions (
role_id INT UNSIGNED NOT NULL,
permission_id INT UNSIGNED NOT NULL,
PRIMARY KEY (role_id, permission_id),
INDEX idx_permission_id (permission_id)
) ENGINE=InnoDB;
2.2 查询用户权限
-- 查询用户的所有权限
SELECT DISTINCT p.*
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN role_permissions rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.id = 1;
3. 统计分析场景
3.1 订单统计
-- 每日订单统计
SELECT
DATE(created_at) AS order_date,
COUNT(*) AS order_count,
SUM(total_amount) AS total_amount
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at)
ORDER BY order_date;
-- 每月销售额
SELECT
DATE_FORMAT(created_at, '%Y-%m') AS month,
SUM(total_amount) AS total_amount
FROM orders
WHERE status >= 1
GROUP BY month;
-- 用户消费排行
SELECT
u.id,
u.username,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status >= 1
GROUP BY u.id
ORDER BY total_amount DESC
LIMIT 100;
3.2 商品销量排行
-- 销量排行
SELECT
p.id,
p.name,
SUM(oi.quantity) AS total_sales,
SUM(oi.amount) AS total_amount
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status >= 1
AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY p.id
ORDER BY total_sales DESC
LIMIT 50;
4. 高并发优化案例
4.1 缓存 + 数据库
class ProductService {
public function getProduct($id) {
// 1. 查询缓存
$cacheKey = "product:{$id}";
$cached = $this->redis->get($cacheKey);
if ($cached) {
return json_decode($cached, true);
}
// 2. 查询数据库
$product = $this->db->query(
"SELECT * FROM products WHERE id = ?",
[$id]
);
// 3. 写入缓存
if ($product) {
$this->redis->setex($cacheKey, 3600, json_encode($product));
}
return $product;
}
}
4.2 计数器优化（异步更新）
// 问题：浏览量每次 +1，频繁更新数据库
// 方案 1：Redis 计数，定期同步
class ViewCounter {
public function incr($productId) {
// Redis 递增
$this->redis->incr("views:product:{$productId}");
}
public function syncToDatabase() {
// 定时任务：每分钟同步一次
$keys = $this->redis->keys("views:product:*");
foreach ($keys as $key) {
$productId = str_replace('views:product:', '', $key);
$views = $this->redis->get($key);
$this->db->query(
"UPDATE products SET views = views + ? WHERE id = ?",
[$views, $productId]
);
$this->redis->del($key);
}
}
}
4.3 库存扣减优化
-- 方案 1：数据库层面（行锁）
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE; -- 加锁
-- 检查库存
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;
-- 方案 2：Redis 预扣减
-- 1. 预热库存到 Redis
SET stock:product:1 1000
-- 2. Redis 扣减（原子操作）
DECR stock:product:1
-- 3. 异步更新数据库
-- 定时任务同步 Redis 库存到数据库
5. 数据迁移和备份
5.1 mysqldump 备份
# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql
# 备份多个数据库
mysqldump -u root -p --databases db1 db2 > backup.sql
# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql
# 只备份结构
mysqldump -u root -p --no-data mydb > structure.sql
# 只备份数据
mysqldump -u root -p --no-create-info mydb > data.sql
# 压缩备份
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz
5.2 恢复数据
# 恢复数据库
mysql -u root -p mydb < mydb_backup.sql
# 恢复压缩备份
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb
5.3 在线备份（不锁表）
# 使用 mysqldump --single-transaction
mysqldump -u root -p --single-transaction --master-data=2 mydb > backup.sql
# 使用 Percona XtraBackup（推荐）
xtrabackup --backup --target-dir=/backup/
xtrabackup --prepare --target-dir=/backup/
xtrabackup --copy-back --target-dir=/backup/
6. 数据清理策略
6.1 历史数据归档
-- 创建归档表
CREATE TABLE orders_archive LIKE orders;
-- 按月归档
INSERT INTO orders_archive
SELECT * FROM orders
WHERE created_at < '2023-01-01';
-- 删除已归档数据
DELETE FROM orders
WHERE created_at < '2023-01-01'
LIMIT 10000; -- 分批删除
6.2 定期清理
-- 清理过期数据
DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
LIMIT 10000;
-- 使用事件调度器自动执行
CREATE EVENT clean_old_logs
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
LIMIT 10000;
7. 常见问题和解决方案
7.1 1062 Duplicate entry（重复键）
-- 问题：插入重复的唯一键值
-- 解决方案 1：INSERT IGNORE
INSERT IGNORE INTO users (email) VALUES ('alice@example.com');
-- 解决方案 2：ON DUPLICATE KEY UPDATE
INSERT INTO users (email, username)
VALUES ('alice@example.com', 'alice')
ON DUPLICATE KEY UPDATE username = 'alice';
-- 解决方案 3：REPLACE（删除旧行，插入新行）
REPLACE INTO users (email, username)
VALUES ('alice@example.com', 'alice');
7.2 1205 Lock wait timeout exceeded
-- 问题：等待锁超时
-- 解决方案
1. 优化 SQL，减少锁持有时间
2. 使用索引，减少锁定行数
3. 调整超时时间
SET GLOBAL innodb_lock_wait_timeout = 50; -- 默认 50 秒
4. 检查是否有死锁
SHOW ENGINE INNODB STATUS;
7.3 1213 Deadlock found
-- 问题：检测到死锁
-- 解决方案
1. 固定加锁顺序
2. 缩小事务范围
3. 使用索引
4. 降低隔离级别
8. 性能测试
8.1 使用 sysbench
# 安装 sysbench
apt-get install sysbench
# 准备测试数据
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 \
--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-user=root \
--mysql-password=password \
--mysql-db=test \
--tables=10 \
--table-size=100000 \
--threads=16 \
--time=60 \
run
# 清理数据
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--mysql-password=password \
--mysql-db=test \
cleanup
8.2 慢查询分析
# 查看慢查询配置
mysql> SHOW VARIABLES LIKE 'slow_query%';
# 开启慢查询
mysql> SET GLOBAL slow_query_log = ON;
mysql> SET GLOBAL long_query_time = 2;
# 分析慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 使用 pt-query-digest
pt-query-digest slow.log > report.txt
9. 监控和维护
9.1 监控指标
-- 连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
-- QPS/TPS
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';
-- InnoDB 缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = (Reads - Read Requests) / Reads
-- 慢查询数量
SHOW STATUS LIKE 'Slow_queries';
-- 表锁等待
SHOW STATUS LIKE 'Table_locks_waited';
9.2 定期维护
-- 分析表（更新统计信息）
ANALYZE TABLE users;
-- 优化表（整理碎片）
OPTIMIZE TABLE users;
-- 检查表
CHECK TABLE users;
-- 修复表
REPAIR TABLE users;
10. 最佳实践
10.1 表设计
- ✅ 主键使用自增 INT/BIGINT
- ✅ 字段设置 NOT NULL + DEFAULT
- ✅ 合理选择数据类型
- ✅ 添加注释
- ✅ 使用 InnoDB 引擎
- ✅ 字符集使用 utf8mb4
10.2 索引设计
- ✅ 为 WHERE、ORDER BY、GROUP BY 字段建索引
- ✅ 联合索引遵循最左前缀
- ✅ 避免过多索引（建议 < 5 个）
- ✅ 定期检查无用索引
10.3 SQL 编写
- ✅ 避免 SELECT *
- ✅ 使用 LIMIT 限制返回数量
- ✅ 避免在 WHERE 中使用函数
- ✅ 使用 EXPLAIN 分析
- ✅ 使用 JOIN 代替子查询
10.4 事务使用
- ✅ 事务尽可能短
- ✅ 避免在事务中执行慢 SQL
- ✅ 合理选择隔离级别
- ✅ 避免大事务
10.5 配置优化
- ✅ innodb_buffer_pool_size = 物理内存的 50-70%
- ✅ 合理设置连接数
- ✅ 启用慢查询日志
- ✅ 定期备份
总结
MySQL 实战核心：
- ✅ 表结构设计（范式、字段类型、索引）
- ✅ 秒杀场景（库存扣减、防超卖）
- ✅ RBAC 权限系统
- ✅ 统计分析（订单、销量）
- ✅ 高并发优化（缓存、异步）
- ✅ 数据备份和恢复
- ✅ 监控和维护
- ✅ 最佳实践
推荐工具：
- 监控：Prometheus + Grafana
- 备份：Percona XtraBackup
- 分析：pt-query-digest
- 测试：sysbench
下一步：刷题，巩固知识！
