1.数据库设计

数据库设计是构建高效、可维护数据库的关键步骤，而范式是数据库设计中的规范化理论，用于减少数据冗余和提高数据一致性。

数据库设计通常包括以下步骤：

需求分析：了解业务需求，确定数据存储和访问需求。
概念设计：设计概念模型（如ER图），描述实体及其关系。
逻辑设计：将概念模型转换为逻辑模型（如表结构）。
物理设计：确定数据库的物理存储结构（如索引、分区等）。
实施与维护：创建数据库并持续优化。

1.1 需求分析

目标：明确数据库的用途、数据范围和用户需求。
关键行动：
访谈利益相关者：
- 确定核心业务需求（如电商系统需管理商品、订单、用户）。
识别数据实体：
- 列出需存储的对象（如用户、商品、订单、库存）。
定义数据操作：
- 高频操作（如“查询用户订单”）、数据量预估（日增1000订单）。
交付物：需求文档（含数据清单和使用场景）。

1.2 实体-关系模型（ER模型）

目标：抽象出实体、属性和关系，构建ER图。

ER模型是数据库设计的重要工具，用于描述实体及其关系。

关键行动：
绘制ER图：
- 实体：用户、商品、订单。
- 属性：用户包含用户ID、姓名、邮箱。
- 关系：用户与订单是“1对多”关系（一个用户可下多个订单）。
验证完整性：
- 确保所有业务场景在ER图中体现（如退货需关联订单和库存）。

示例：

实体：用户、订单
属性：用户（id, username, email），订单（order_id, user_id, amount）
关系：用户与订单是一对多关系。

1.3 逻辑设计（关系模型）

目标：将ER图转化为表结构，并范式化。
关键行动：
实体转表：
- 用户表、商品表、订单表。
处理关系：
- 多对多关系通过关联表实现（如订单商品表）。
范式化：
- 3NF示例：
- 商品表不应直接存分类名称，应通过分类ID关联分类表。

1.4 物理设计

目标：优化存储结构，提升性能。
关键行动：
选择存储引擎：
- OLTP用InnoDB（支持事务），OLAP用列式存储（如ClickHouse）。
设计索引：
- 订单表按user_id和order_date建组合索引：

CREATE INDEX idx_user_order ON orders(user_id, order_date);

分区策略：按时间分区订单表（如按月分表 orders_2023_01）。

1.5 实施与部署

目标：创建数据库并导入数据。
关键行动：
执行DDL语句：
- 使用建表语句创建所有表。
数据迁移：
- 从旧系统导出数据，用ETL工具（如Apache NiFi）清洗导入。
权限配置：
- 创建应用账号并限制权限：

GRANT SELECT, INSERT ON orders TO 'app_user'@'%';

2.范式

范式是数据库设计中的规范化理论，用于减少数据冗余和提高数据一致性。常见的范式包括第一范式（1NF）、第二范式（2NF）、第三范式（3NF）和BCNF。

2.1 第一范式（1NF）

定义：表中的每一列都是不可分割的原子值。
要求：每一列都是单一值，不能包含数组、集合等。每一行都是唯一的，通常通过主键实现。

2.2 第二范式（2NF）

定义：在1NF的基础上，非主键列必须完全依赖于主键。
要求：表必须有主键。非主键列不能部分依赖于主键。

2.3 第三范式（3NF）

定义：在2NF的基础上，非主键列不能传递依赖于主键。
要求：非主键列必须直接依赖于主键，不能依赖于其他非主键列。

2.4 BCNF（Boyce-Codd范式）

定义：在3NF的基础上，进一步消除函数依赖。
要求：对于每一个函数依赖X -> Y，X必须是超键。

3.反范式化

反范式化是为了提高查询性能，故意违反范式规则，引入冗余数据。

3.1 反范式化的场景

频繁的复杂查询：通过冗余数据减少连接操作。
读多写少的场景：冗余数据对写操作影响较小。

4.注意事项

4.1 数据模型设计

避免过度冗余
问题：重复数据浪费存储且易导致不一致（如用户地址在多表中重复）。
解决：通过范式化拆分表，外键关联（如用 UserID 关联用户表与订单表）。
范式化与反范式化的平衡
范式化（减少冗余）：
优点：数据一致性高。
缺点：多表关联查询性能低。
反范式化（适当冗余）：
场景：高频查询字段（如订单表冗余用户名）。
风险：需通过触发器或程序保证冗余数据一致性。
合理设计主键
原则：使用无意义自增整数（如 INT AUTO_INCREMENT）。避免业务字段作为主键（如身份证号可能暴露隐私）。
联合主键慎用：仅用于多对多关系表（如选课表 (StudentID, CourseID)）。

4.2 性能优化

索引设计
必建索引：主键、外键字段。高频查询的 WHERE、ORDER BY、JOIN 字段。
避免过度索引：单表索引不超过5个（写入时需维护索引，降低性能）。区分度低的字段不建索引（如性别字段只有男/女）。
数据类型选择
数值类型：优先 INT、BIGINT（定长，查询快）。金额用 DECIMAL（避免浮点精度丢失）。
字符串类型：定长字段用 CHAR（如性别 CHAR(1)）。变长字段用 VARCHAR（如地址 VARCHAR(255)）。
时间类型：精确到秒用 DATETIME。自动更新用 TIMESTAMP（支持时区）。
分区策略
场景：单表数据量超千万行时。
方式：
范围分区：按时间（如按年分订单表）。
哈希分区：分散数据到不同分区（如 HASH(UserID)）。

4.3 安全性与完整性

权限控制最小权限原则：
应用账号仅授予必要权限（如只读账号用 GRANT SELECT）。禁止直接使用 root 账号连接应用。
示例：

CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app_user'@'%';

防止SQL注入

方法：
使用参数化查询（Prepared Statements）。避免动态拼接SQL（如"SELECT * FROM users WHERE id = " + userInput）。
外键约束
启用外键：确保数据关联完整性。
注意：外键可能影响性能（需检查约束）。删除时级联操作需谨慎（如 ON DELETE CASCADE）。

4.4 可维护性

命名规范
表名：复数形式（如 users）、小写加下划线（如 order_details）。
字段名：避免保留字（如 desc → 改用 description）。
索引名：idx_表名_字段（如 idx_users_email）。
注释与文档
字段注释：

CREATE TABLE users (
    is_deleted TINYINT DEFAULT 0 COMMENT '0-未删除, 1-已删除'
);

维护数据字典：记录表结构、关系、业务含义。
版本控制
工具：使用数据库迁移工具（如Flyway、Liquibase）。
示例：

-- V1__create_users_table.sql
CREATE TABLE users (...);

4.5 扩展性设计

预留扩展字段添加 extra_data JSON 字段存储灵活扩展数据。示例：

ALTER TABLE orders ADD COLUMN extra_data JSON DEFAULT NULL;

分库分表预判标识分片键：高频查询字段（如 UserID）。
数据冷热分离：历史数据归档到独立表（如 orders_2023）。

4.6 测试与验证

模拟数据填充
工具：使用脚本或工具生成测试数据（如 Mockaroo）。
验证：数据完整性（外键关联正确）。约束生效（如唯一性约束阻止重复插入）。
压力测试
工具：JMeter、sysbench。
指标：QPS（每秒查询数）。并发连接数下的响应时间。

mariadb数据库设计基础概念