【趣学SQL】第七章：SQL 实战案例 7.1 电商系统中的 SQL 优化——从“双十一崩库“到“丝滑秒杀“的逆袭之路

第七章：SQL 实战案例

7.1 电商系统中的 SQL 优化——从"双十一崩库"到"丝滑秒杀"的逆袭之路

欢迎来到「电商数据库急诊室」！今天我们将化身"SQL手术专家"，用一家日活千万的虚拟电商平台"猫了个猫"的崩库案例，手把手教你如何把数据库从ICU抢救回来。🩺💻

---

7.1.1 电商系统的数据模型——"猫了个猫"的解剖图

核心表结构：

-- 用户表（每天被薅羊毛党狂刷）  
CREATE TABLE users (  
  user_id BIGINT PRIMARY KEY,  
  username VARCHAR(50) UNIQUE,  -- 唯一索引  
  password CHAR(60),            -- bcrypt加密  
  last_login DATETIME  
) ENGINE=InnoDB;  

-- 商品表（SKU多到能开超市）  
CREATE TABLE products (  
  product_id BIGINT PRIMARY KEY,  
  title VARCHAR(200),           -- 商品标题（需要全文索引）  
  price DECIMAL(10,2),  
  stock INT,                    -- 实时库存（并发重灾区）  
  INDEX idx_title (title(20))   -- 前缀索引  
);  

-- 订单表（双十一每秒插入1万条）  
CREATE TABLE orders (  
  order_id VARCHAR(32) PRIMARY KEY,  -- 订单号带时间戳  
  user_id BIGINT,  
  total_amount DECIMAL(12,2),  
  status ENUM('pending','paid','shipped'),  
  create_time DATETIME,  
  KEY idx_user_time (user_id, create_time)  -- 联合索引  
);  

典型问题：

• 商品搜索LIKE %...%导致全表扫描
• 订单分页查询LIMIT 1000000,10耗时5秒
• 库存更新出现超卖（并发控制不当）

---

7.1.2 常见的查询瓶颈——"猫了个猫"的十大酷刑

酷刑1：用户登录慢如树懒

SELECT * FROM users  
WHERE username = '羊毛党007'   
  AND password = '加密后的密码';  
-- 问题：username是唯一索引但password未加密比对  

酷刑2：商品搜索卡成PPT

SELECT * FROM products  
WHERE title LIKE '%智能蓝牙耳机%'  
ORDER BY price DESC  
LIMIT 100;  
-- 问题：LIKE左模糊+无合适索引  

酷刑3：订单分页加载失败

SELECT * FROM orders  
WHERE user_id = 10086  
ORDER BY create_time DESC  
LIMIT 100000, 10;  
-- 问题：深度分页导致大量无效扫描  

---

7.1.3 索引优化案例——给数据库装上"涡轮增压"

案例1：商品搜索提速100倍

优化前：

+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+  
| id | select_type | table    | type | possible_keys | key  | key_len | rows | Extra   |  
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+  
| 1  | SIMPLE      | products | ALL  | idx_title     | NULL | NULL    | 2M   | Using where; Using filesort |  
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+  

手术方案：

-- 添加全文索引  
ALTER TABLE products  
ADD FULLTEXT INDEX ft_title (title);  

-- 改写查询  
SELECT * FROM products  
WHERE MATCH(title) AGAINST('+智能 +蓝牙 +耳机' IN BOOLEAN MODE)  
ORDER BY price DESC  
LIMIT 100;  

术后效果：

• 执行时间从3.2秒 → 0.03秒
• 索引大小仅增加15%（倒排索引YYDS）

---

7.1.4 查询优化案例——让SQL学会"凌波微步"

案例2：订单分页起死回生

优化前（深度分页灾难）：

SELECT * FROM orders  
WHERE user_id = 10086  
ORDER BY create_time DESC  
LIMIT 100000, 10; -- 需要扫描10万+10行  

手术方案（游标分页术）：

SELECT * FROM orders  
WHERE user_id = 10086  
  AND create_time < '2023-12-01 00:00:00' -- 上一页最后的时间  
ORDER BY create_time DESC  
LIMIT 10;  

术后效果：

• 执行时间从5.1秒 → 0.02秒
• 前端需要记录最后一条的create_time

---

7.1.5 系统调优案例——给数据库注入"肾上腺素"

案例3：库存超卖急救

症状：

-- 并发下单时的超卖  
UPDATE products SET stock = stock - 1 WHERE product_id = 666;  

手术方案（CAS乐观锁）：

UPDATE products  
SET stock = stock - 1  
WHERE product_id = 666  
  AND stock > 0; -- 原子操作检查  

-- 检查影响行数  
IF ROW_COUNT() = 0 THEN  
  ROLLBACK;  
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';  
END IF;  

调优配置：

# my.cnf  
innodb_buffer_pool_size = 64G      # 内存的70%  
innodb_flush_log_at_trx_commit = 2 # 适当牺牲持久性换性能  
max_connections = 2000             # 双十一特供  

术后效果：

• 库存更新TPS从500 → 8500
• 超卖投诉减少99.9%

---

7.1.6 安全管理案例——防住"羊毛党"的黑手

案例4：防止SQL注入

漏洞代码：

# 错误示范（拼接SQL）  
query = f"SELECT * FROM users WHERE username = '{user_input}'"  

安全方案：

# 使用预编译语句  
cursor.execute("""  
  SELECT * FROM users  
  WHERE username = %s  
""", (user_input,))  

案例5：敏感数据加密

-- 手机号加密存储  
ALTER TABLE users  
ADD COLUMN phone_encrypted VARBINARY(256);  

-- AES加密写入  
UPDATE users  
SET phone_encrypted = AES_ENCRYPT('13812345678', 'secret_key');  

-- 解密查询  
SELECT AES_DECRYPT(phone_encrypted, 'secret_key')  
FROM users WHERE user_id = 10086;  

---

术后护理指南

监控工具套餐：

# 实时监控利器  
pt-query-digest /var/log/mysql/slow.log  

# 压力测试神器  
sysbench oltp_read_write --threads=50 run  

备份恢复方案：

# XtraBackup全量+binlog增量  
xtrabackup --backup --target-dir=/backups/full  
mysqlbinlog --start-datetime="2024-01-01 00:00:00" /var/log/mysql/binlog.0000* > inc.sql  

---

本章冷知识

• 某电商曾因VARCHAR(255)存储IP地址，导致索引失效（改用VARBINARY(16)存储IPv6）
• 双十一期间MySQL的QPS峰值可达百万级（需要分库分表+读写分离）
• 最奇葩的慢查询案例：某促销活动WHERE条件写成price = '100元'（字段类型是DECIMAL）

现在你已经成为"电商数据库神医"！下一站我们将挑战《社交媒体应用中的 SQL 优化——从“卡成PPT”到“纵享丝滑”的逆袭之路》的玄学领域，记得给你的服务器准备速效救心丸——电商大促的刺激永不停歇！ 🚀💸