数据库结构设计及性能优化.docxVIP

数据库结构设计及性能优化

1.业务需求与模型抽取

明确业务实体：列出所有核心业务对象（如用户、订单、商品、日志等）。

属性分类：

标识属性：主键（PK）

业务属性：描述业务的字段（如name,price）

时间属性：创建/更新时间、状态变更时间等

派生属性：可以由其他属性计算得到（如total_amount=quantity*unit_price）

确定关联关系：

一对一、一对多、多对多的关系方式

业务规则驱动的外键约束

2.逻辑设计（关系型数据库）

表名

主键

关键外键

重要业务字段

备注

users

user_id(UUID)

—

username,email,phone,created_at,status

用户基本信息

products

product_id(UUID)

—

sku,name,price,stock,category_id

商品目录

orders

order_id(UUID)

user_id(FK)

order_no,order_status,total_amount,created_at

订单头部

order_items

order_item_id(UUID)

order_id(FK),product_id(FK)

quantity,unit_price,subtotal

订单明细

categories

category_id(UUID)

—

category_name,parent_id

商品分类

audit_logs

log_id(UUID)

—

user_id,operation,target_table,target_id,created_at

审计日志

命名统一：使用snake_case，主键统一为_id。

字段类型：

整数→INTUNSIGNED/BIGINTUNSIGNED

字符串→VARCHAR(N)，长度根据业务预估

时间→DATETIME(6)（支持微秒）

金钱→DECIMAL(18,2)

唯一约束：对业务唯一的字段（如email,order_no）添加UNIQUE索引。

3.物理设计与表结构细节

3.1分区与分表

场景

推荐方案

大批量日志（如audit_logs）

按时间范围分区（PARTITIONBYRANGE(created_at)）

用户/订单数据的水平扩展

分库（业务维度）或分表（如按用户IDhash）

只读/写密集型表

只读表使用读取复制，写表使用分区或分表以避免锁竞争

3.2索引策略

索引类型

适用场景

示例

唯一索引

保证字段唯一性

UNIQUEINDEXuq_email(email)

复合索引

多条件过滤、分组、排序

INDEXidx_order_status_user(order_status,user_id)

全文索引

文本搜索、关键字匹配

FULLTEXTINDEXft_product_name(name)

前缀索引

超长字段（如TEXT）需要限定长度

INDEXidx_desc_prefix(desc_content(191))

覆盖索引

只访问索引即可返回结果，避免回表

INDEXidx_order_total(order_status,total_amount)

3.3字符集与排序规则

统一使用UTF8MB4字符集，支持emoji和四字节汉字。

排序规则建议使用utf8mb4_0900_ai_ci（MySQL8.0+）或C排序，以提升比较性能。

3.4物理存储参数（以InnoDB为例）

参数

推荐值（可根据硬件调优）

innodb_buffer_pool_size

70%~80%物理内存（专用于热点数据）

innodb_log_file_size

1G~2G（保证事务提交速度）

innodb_flush_log_at_trx_commit

2（兼顾性能与Durability）

innodb_flush_method

O_DIRECT（避免双写）

max_connections

根据并发用户数+预留10%计算

4.性能优化实战

4.1读性能提升

合理使用缓存

查询缓存（MySQL8.0已废弃），推荐使用Redis或Memcached缓存热点查询（如商品列表、用户信息）。

分页优化

使用keyset分页（基于唯一且递增的字段）代替OFFSET，减少大页offset带来的性能损耗。

只读库与读写分离

将所有SELECT语句路由到只读副本，减轻主库压力。

4.2写性能提升

批量写入

将小颗粒度的INSERT/UPDATE合并为