前面我们学习知识解决的问题索引如何快速查询EXPLAIN如何分析 SQLSQL优化如何减少查询成本事务如何保证业务一致锁如何处理并发修改MVCC如何提高读性能InnoDBMySQL底层原理现在把这些知识组合起来模拟企业开发中的数据库优化流程。一、实际项目场景假设我们开发一个电商订单系统业务用户购买商品。涉及用户商品订单订单详情数据库设计用户表CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50), phone VARCHAR(20), create_time DATETIME );商品表CREATE TABLE product ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), price DECIMAL(10,2), stock INT, create_time DATETIME );订单表CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT, total_price DECIMAL(10,2), status INT, create_time DATETIME );订单详情表CREATE TABLE order_item ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT, product_id BIGINT, count INT );二、问题1用户查询慢业务查看用户SELECT * FROM user WHERE phone13800138000;分析执行EXPLAIN SELECT * FROM user WHERE phone13800138000;结果可能type: ALL key: NULL rows: 5000000说明全表扫描。优化手机号具有查询频繁唯一创建唯一索引CREATE UNIQUE INDEX idx_phone ON user(phone);再次EXPLAIN结果type: const key: idx_phone rows: 1优化完成。三、问题2订单列表查询慢接口用户查看订单SELECT * FROM orders WHERE user_id100 ORDER BY create_time DESC;数据订单5000万。分析EXPLAIN发现type: ALL Using filesort问题两个user_id 没索引create_time 排序慢优化建立联合索引CREATE INDEX idx_user_time ON orders(user_id, create_time);为什么因为查询WHERE user_id ORDER BY create_time符合最左匹配原则。优化后type: ref key: idx_user_time四、问题3分页越来越慢订单列表第一页SELECT * FROM orders LIMIT 0,20;很快。第100万页SELECT * FROM orders LIMIT 10000000,20;慢。原因MySQL扫描10000020条。然后丢弃前10000000条。优化方案使用游标分页。例如上一页最后id100000下一页SELECT * FROM orders WHERE id 100000 ORDER BY id LIMIT 20;效率直接定位。五、问题4库存超卖商品stock1两个用户同时购买。错误代码SELECT stock FROM product WHERE id1;返回1两个请求都认为库存足够。导致超卖。方案1悲观锁BEGIN; SELECT stock FROM product WHERE id1 FOR UPDATE; UPDATE product SET stockstock-1 WHERE id1; COMMIT;执行流程用户A锁住商品 修改库存 提交用户B等待方案2乐观锁增加字段ALTER TABLE product ADD version INT;更新UPDATE product SET stockstock-1, versionversion1 WHERE id1 AND version5;如果影响行数1成功。如果0说明冲突。六、问题5订单创建如何保证一致创建订单需要步骤创建订单减库存保存订单详情如果库存失败。订单怎么办必须使用事务BEGIN; INSERT INTO orders; UPDATE product SET stockstock-1; INSERT INTO order_item; COMMIT;失败ROLLBACK;保证订单和库存一致。七、问题6为什么查询不用加锁例如后台查看订单SELECT * FROM orders WHERE user_id100;是否需要FOR UPDATE答案通常不需要。原因普通查询使用MVCC读取快照。只有需要修改前确认最新数据例如库存才使用FOR UPDATE八、索引设计实战原则1. 高频查询字段建立索引例如WHERE phone建立idx_phone2. 查询条件顺序设计联合索引例如SQLWHERE user_id1 ORDER BY create_time索引(user_id,create_time)3. 不要给所有字段加索引错误name age phone address time status ... 全部建立索引问题占空间降低写性能九、慢 SQL 排查流程 ⭐⭐⭐⭐⭐企业流程第一步发现接口慢。↓查看 SQL。第二步执行EXPLAIN SQL;第三步重点看type避免ALLkey确认有没有使用索引。rows扫描多少数据。Extra避免Using filesort Using temporary第四步优化可能添加索引修改 SQL调整分页减少字段十、MySQL 高级阶段完整知识体系到这里第四阶段完成。你已经学习MySQL高级 ├── 索引 │ ├── EXPLAIN │ ├── SQL优化 │ ├── 事务 │ ├── 锁 │ ├── MVCC │ ├── InnoDB │ └── 综合优化第四阶段最终总结SQL慢的本质通常是扫描数据太多 ↓ 索引设计不好 ↓ SQL执行成本高 ↓ 数据库压力大企业数据库优化核心记住这五句话1.查询慢先 EXPLAIN。2.大部分查询问题本质是索引问题。3.索引不是越多越好要平衡读写。4.修改数据考虑事务和锁。5.高并发读取依靠 MVCC。 第四阶段一句话总结MySQL高级的核心就是理解数据如何存储、如何查询、如何并发访问并通过索引、事务、锁和优化让数据库稳定运行。