Archery项目中的MySQL数据库设计规范详解-优快云博客

本文链接：https://blog.youkuaiyun.com/gitblog_00923/article/details/148467767

Archery项目中的MySQL数据库设计规范详解

Archery hhyo/Archery: 这是一个用于辅助MySQL数据库管理和开发的Web工具。适合用于需要管理和开发MySQL数据库的场景。特点：易于使用，具有多种数据库管理功能，包括查询构建、数据库结构管理、数据导入导出等。项目地址: https://gitcode.com/gh_mirrors/ar/Archery

前言

在数据库管理系统中，良好的设计规范是保证系统稳定性和性能的关键。本文将深入解析Archery项目中提出的MySQL数据库设计规范，帮助开发者和DBA构建高效、可靠的数据库系统。

一、规范背景与核心目标

MySQL作为开源关系型数据库的代表，在企业级应用中扮演着重要角色。Archery项目提出的设计规范主要基于以下考虑：

性能优化：针对MySQL的存储引擎特性进行优化
可维护性：统一设计标准，降低维护成本
高可用性：确保系统在各类业务场景下的稳定性
扩展性：为未来业务增长预留扩展空间

二、数据库设计规范详解

2.1 命名规范

库名设计原则

长度控制在32字符内，采用业务系统_子系统格式
分库命名采用库通配名_编号或库通配名_时间格式
必须显式指定字符集（utf8/utf8mb4）

表名设计原则

32字符内，字母数字下划线，全小写
采用模块前缀（如师资系统用"sz"）
中间表以tmp_开头，备份表以bak_开头

2.2 表结构设计

基础规范

显式指定存储引擎（默认InnoDB）和字符集
必须包含表注释（comment）
核心表需包含create_time和update_time

主键设计

主键字段名为id，类型为int/bigint auto_increment
业务主体字段（如user_id）设为唯一索引而非主键

字段设计

所有字段NOT NULL并设置DEFAULT值
大字段（blob/text）垂直拆分到其他表
适当反范式设计，冗余高频查询字段

2.3 数据类型优化

数值类型

自增列推荐使用bigint
状态字段使用tinyint/smallint
IP地址存储使用int而非char(15)
金额存储使用int（程序端处理小数点）

文本类型

避免使用enum/set类型
文本数据优先使用varchar（≤2700字符）
时间类型优先使用timestamp（4字节）

2.4 索引设计规范

索引命名

主键：pk_前缀
唯一键：uk_或uq_前缀
普通索引：idx_前缀

索引策略

单表索引不超过7个
优先使用联合索引，高区分度字段在前
确保join查询的被驱动表连接列有索引
避免冗余索引

2.5 分库分表规范

分区表

分区字段必须有索引
分区数不超过1024个
单个分区≤2G，总大小≤50G

分库分表

分库不超过1024个
分表不超过4096个
单表≤500万行，ibd文件≤2G
水平分表优先取模，日志类按日期分

2.6 SQL编写规范

DML语句

禁止SELECT *，必须指定字段
INSERT指定字段名
IN列表值不超过500个
事务批量操作控制在2000行内

查询优化

WHERE条件使用索引列
避免全模糊LIKE
分页查询先过滤再LIMIT
减少OR使用，改用UNION

多表连接

禁止跨DB的join
更新语句禁止使用join
单次join不超过3表
小结果集表作为驱动表

事务控制

事务隔离级别推荐repeatable-read
单个事务SQL不超过5个（支付除外）
更新基于主键或唯一键
外部调用移出事务

三、禁止使用的SQL模式

带LIMIT的UPDATE/DELETE（导致主从不一致）
关联子查询（性能极差）
存储过程/函数/触发器/视图
INSERT...ON DUPLICATE KEY UPDATE（高并发问题）
联表更新语句

四、最佳实践示例

CREATE TABLE user (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(11) NOT NULL COMMENT '用户id',
  `username` varchar(45) NOT NULL COMMENT '真实姓名',
  `email` varchar(30) NOT NULL COMMENT '用户邮箱',
  `nickname` varchar(45) NOT NULL COMMENT '昵称',
  `avatar` int(11) NOT NULL COMMENT '头像',
  `birthday` date NOT NULL COMMENT '生日',
  `sex` tinyint(4) DEFAULT '0' COMMENT '性别',
  `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍',
  `user_resume` varchar(300) NOT NULL COMMENT '简历地址',
  `user_register_ip` int NOT NULL COMMENT '注册IP',
  `create_time` timestamp NOT NULL COMMENT '创建时间',
  `update_time` timestamp NOT NULL COMMENT '更新时间',
  `user_review_status` tinyint NOT NULL COMMENT '审核状态',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_username`(`username`),
  KEY `idx_create_time_status`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户基本信息';