八、数据库设计与优化详解

一、数据库设计范式与反范式 1. 数据库设计三大范式 范式级别要求示例问题与解决方案1NF字段原子性（不可再分）将“地址”字段拆分为省、市、详细地址2NF消除部分依赖（所有非主属性完全依赖主键）订单表中不应直接存储商品名称，应通过商品ID关联3NF消除传递依赖（非主属性不依赖其他非主属性）员工表中部门名称应通过部门ID关联，而非直接存储 2. 反范式设计

适用场景：读多写少的高性能需求场景（如数据仓库） 常见手段：

冗余字段：在订单表中直接存储商品名称（避免JOIN）汇总字段：在用户表中增加订单总数字段（避免COUNT查询）

示例：

-- 范式设计（3NF） CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, product_id INT, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (product_id) REFERENCES products(product_id) ); -- 反范式设计（冗余商品名称） CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, product_id INT, product_name VARCHAR(100), -- 冗余字段 FOREIGN KEY (user_id) REFERENCES users(user_id) ); 二、执行计划分析与优化 1. EXPLAIN 关键字段解析 EXPLAIN SELECT * FROM products WHERE price > 100 AND category = '电子'; 字段说明优化关注点type访问类型（性能排序：const > ref > range > index > ALL）尽量避免 ALL（全表扫描）key实际使用的索引检查是否命中预期索引rows预估扫描行数数值越大性能越差Extra附加信息关注 Using filesort、Using temporary 2. 常见执行计划问题

问题 1：全表扫描 现象：type=ALL，key=NULL 解决：为WHERE条件字段添加索引

问题 2：文件排序 现象：Extra=Using filesort 解决：为ORDER BY字段添加索引

---

三、索引深度优化 1. 索引选择策略 场景推荐索引类型示例等值查询B-Tree索引WHERE user_id = 123范围查询B-Tree索引WHERE create_time > '2023-01-01'全文搜索全文索引（FULLTEXT）WHERE MATCH(content) AGAINST('关键词')多维度查询联合索引INDEX (city, age) 2. 索引优化实战技巧

技巧 1：覆盖索引优化

-- 原始查询（需回表） SELECT * FROM products WHERE category = '电子'; -- 优化为覆盖索引查询 SELECT product_id, category FROM products WHERE category = '电子'; -- 创建索引：CREATE INDEX idx_category ON products(category);

技巧 2：索引下推（ICP）

/* MySQL 5.6+ 自动启用，对联合索引 (a,b) 的优化： WHERE a > 10 AND b = 5 旧版本：先按a>10过滤，再逐条检查b=5 ICP：直接按a>10 AND b=5过滤 */

---

四、分库分表策略 1. 分片策略对比 策略优点缺点适用场景水平分表数据均匀分布跨分片查询复杂大数据量表（如订单）垂直分表减少IO需要多次JOIN宽表字段分离哈希分片数据分布均匀扩容困难随机访问型业务范围分片易于范围查询可能产生热点时间序列数据 2. 分库分表示例 -- 原始表 CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), create_time DATETIME ); -- 水平分表（按user_id取模分4张表） CREATE TABLE orders_0 (...); -- user_id % 4 = 0 CREATE TABLE orders_1 (...); -- user_id % 4 = 1 CREATE TABLE orders_2 (...); CREATE TABLE orders_3 (...);

---

五、慢查询优化全流程 1. 优化步骤

定位慢查询

-- MySQL 开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; -- 超过2秒的查询

分析执行计划

EXPLAIN SELECT * FROM large_table WHERE ...;

索引优化

添加缺失索引删除冗余索引调整索引顺序

改写查询语句

避免使用SELECT *将子查询改为JOIN拆分复杂查询

系统级调优

调整innodb_buffer_pool_size升级硬件配置 2. 典型案例分析

场景：用户订单分页查询缓慢 原始SQL：

SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time DESC LIMIT 100000, 20;

优化方案：

索引优化：创建联合索引 (user_id, create_time)

分页优化：改用游标分页

SELECT * FROM orders WHERE user_id = 123 AND create_time < '2023-06-01' ORDER BY create_time DESC LIMIT 20;

---

六、实战项目：电商系统优化 1. 数据库设计优化 -- 商品表（反范式设计） CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(255), category_id INT, category_name VARCHAR(50), -- 冗余分类名称 price DECIMAL(10,2), stock INT, INDEX idx_category (category_id), INDEX idx_price (price) ); -- 订单表（垂直分表） CREATE TABLE orders_base ( order_id BIGINT PRIMARY KEY, user_id INT, status TINYINT, create_time DATETIME ); CREATE TABLE orders_detail ( order_id BIGINT PRIMARY KEY, address TEXT, payment_info JSON ); 2. 慢查询优化案例

问题SQL：

SELECT p.*, COUNT(o.order_id) AS sales FROM products p LEFT JOIN orders o ON p.product_id = o.product_id WHERE p.price BETWEEN 100 AND 500 GROUP BY p.product_id ORDER BY sales DESC LIMIT 100;

优化步骤：

为products.price添加索引

为orders.product_id添加索引

使用覆盖索引优化：

SELECT p.product_id, p.product_name, (SELECT COUNT(*) FROM orders WHERE product_id = p.product_id) AS sales FROM products p WHERE p.price BETWEEN 100 AND 500 ORDER BY sales DESC LIMIT 100;

---

七、进阶学习建议 1. 性能分析工具 pt-query-digest：分析慢查询日志SHOW PROFILE：查看查询详细执行时间Percona Toolkit：数据库高级管理工具集 2. 推荐学习资料 书籍：《数据库索引设计与优化》《高性能MySQL》专栏：极客时间《MySQL实战45讲》工具：MySQL Workbench 性能仪表盘