MYSQL数据库设计与应用 MySQL数据库设计与应用PDF

转载

mob64ca14048514 2023-11-25 15:56:30

文章标签 MYSQL数据库设计与应用 mysql数据库设计pdf 表名字段名数据库 文章分类 MySQL 数据库

主要内容：

一、基础规范

1、【高危】使用InnoDB 存储引擎

没有特殊要求(即Innodb无法满足的功能如：列存储，存储空间数据等)的情况下，所有表必须使用Innodb存储引擎(mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb)Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好

2、【强制】表字符集使用utf8mb4

默认使用 utf8mb4 字符集，数据库排序规则使用 utf8mb4_general_ci，采用 utf8 编码的 MySQL 无法保存占位是 4 个字节的 Emoji 表情。为了使后端的项目全面支持客户端输入的 Emoji 表情，升级编码为 utf8mb4 是最佳解决方案；

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效

3、【强制】所有表所有字段都需要添加注释

使用comment从句添加表和列的备注从一开始就进行数据字典的维护

4、【强制】单表数据量建议控制在500万以内

500万并不是MySQL数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题，可以用历史数据归档(应用于日志数据)，分库分表(应用于业务数据)等手段来控制数据量大小

5、【高危】不在数据库中存储图、文件等大的二进制数据

通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时通常存储于文件服务器，数据库只存储文件地址信息

6、【高危】尽量做到冷热数据分离，减小表的宽度

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节减少磁盘IO,保证热数据的内存缓存命中率(表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO) 更有效的利用缓存，避免读入无用的冷数据经常一起使用的列放到一个表中(避免更多的关联操作)

7、【强制】禁止在线上做数据库压力测试

8、【强制】禁止测试、开发环境直连数据库

二、命名规范

1、【强制】库名、表名、字段名必须使用小写字母，并采用下划线分割

MySQL有配置参数lower_case_table_names=1，即库表名以小写存储，大小写不敏感。如果是0，则库表名以实际情况存储，大小写敏感；如果是2，以实际情况存储，但以小写比较

如果大小写混合使用，可能存在abc，Abc，ABC等多个表共存，容易导致混乱

字段名显示区分大小写，但实际使用时不区分，即不可以建立两个名字一样但大小写不一样的字段

为了统一规范，库名、表名、字段名使用小写字母，不允许 - 号

2、【强制】库名、表名、字段名禁止超过32个字符，需见名知意，建议使用名词不是动词

库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符

3、【强制】库名、表名、字段名禁止使用MySQL保留字

当库名、表名、字段名等属性含有保留字时，SQL语句必须用反引号引用属性名称，这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂

4、【强制】临时库、表名必须以tmp为前缀，并以日期为后缀

形如：tmp_user_account_20190313

5、【强制】备份库、表必须以bak为前缀，并以日期为后缀

形如：bak_user_account_20190313

6、【强制】主键索引名为 pk_ 字段名；唯一索引名为 uk _ 字段名；普通索引名则为 idx_ 字段名

pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称

7、【强制】在不同的库或表中，要保证所有存储相同数据的列名和列类型必须一致

一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低

8、【强制】表的命名最好是加上“业务名称_表的作用”

正例：user_task / force_project / trade_config

9、【强制】表名要求模块名强相关，如师资系统采用”sz”作为前缀，渠道系统采用”qd”作为前缀等。

10、【建议】表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。

因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题

三、字段设计规范

1、【强制】优先选择符合存储需要的最小的数据类型

列的字段类型越大，建立索引占据的空间就越大，导致一个页中的索引越少，造成IO次数增加，影响性能

业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间

能用int的就不用char或者varchar

能用tinyint的就不用int

使用 UNSIGNED 存储非负数值

使用tinyint来代替 enum和boolean

存储 ip 最好用 int存储而非 char(15)

通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数，需要使用DECIMAL或两个BIGINT来存储SELECT INET_ATON('209.207.224.40'); 3520061480SELECT INET_NTOA(3520061480); 209.207.224.40

表中的自增列(auto_increment属性)，推荐使用bigint类型

2、【强制】不推荐使用blob，text等类型

blob，text是为了存储极大的字符串而设计的数据类型，采用二进制与字符串方式存储，该数据类型不能设置默认值、不便于排序、不便于建立索引， varchar 的性能会比 text 高很多，如果非要使用，建议将这种数据分离到单独的拓展表中，垂直拆分到其他表里，仅在需要读这些对象的时候才去select。

3、【强制】禁止使用字符串来存储日期型数据

无法使用日期函数计算比较

字符串存储要占更多的内存空间，datetime(8字节)和timestamp(本身是以int存储，占4字节,范围:1970-01-01 00:00:01到2038-01-19 03:14:07)

TIMESTAMP 记录经常变化的更新/创建/发布/日志时间等，并且是近来的时间，够用，可免时区处理

DATETIME 记录生日、纪念事件、超出 TIMESTAMP 的时间，记得时区处理

4、【强制】用 DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数

Decimal类型为精准浮点数，float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。

5、【强制】必须把字段定义为NOT NULL并设默认值

NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化

NULL需要更多的存储空间

NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑

6、【强制】使用varchar(20)存储手机号，不要使用整数