数据库设计和研发规范

最新推荐文章于 2024-04-18 21:55:15 发布

扁豆的主人

最新推荐文章于 2024-04-18 21:55:15 发布

阅读量405

点赞数 2

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_40213018/article/details/119115347

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一. 基础规范

所有表都需要添加注释

使用comment从句添加表和列的备注，从一开始就做好数据字典维护。

单表数据量建议控制在500万以内

过大会给备份和恢复带来困难，可用历史数据归档（用于日志数据），分库分表（用于业务数据）等手段来控制数据量大小。

不在数据库中存储图、文件等二进制数据

图或文件IO操作耗时，通常存储于文件服务器，数据库只存储文件地址信息。

尽量冷热数据分离，减小表的宽度

把不常用且占宽大的字段从热表中分离可独立存储，每一行数据大小不超过65535字节。

禁止测试、开发环境直连线上数据库

二、命名规范

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

MySQL配置参数lower_case_table_names=1，即库表名以小写存储，大小写不敏感。如果是0，则库表名以实际情况存储，大小写敏感；如果是2，以实际情况存储，但以小写比较。如果大小写混合使用，可能存在abc，Abc，ABC等多个表共存，容易导致混乱。

库名、表名、字段名不要超过32个字符，需见名知意易于辨识

库名、表名、字段名支持最多64个字符，但为了减少传输量，禁止超过32个字符。

库名、表名、字段名禁止使用MySQL保留字

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

备份库、表必须以bak为前缀，并以日期为后缀

形如：bak_user_account_20190313

索引的命名

主键索引名为 pk_字段名；唯一索引名为 uk _字段名；普通索引名则为 idx_字段名。

三、字段设计规范

优先选择符合存储需要的最小的数据类型

列的字段类型越大，建立索引占据的空间就越大，使得一个页中的索引越少，IO次数增加，影响性能，能用int的就不用char或者varchar，能用tinyint的就不用int。状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间；使用 UNSIGNED 存储非负数值；使用tinyint来代替 enum和boolean；存储 ip 最好用 int存储而非 char(15)。

不推荐使用blob，text等类型

blob，text是为了存储极大的字符串而设计的数据类型，采用二进制与字符串方式存储，该数据类型不能设置默认值、不便于排序、不便于建立索引，varchar的性能会比text高很多，如果非要使用，建议将这种数据分离到单独的拓展表中。

禁止使用字符串来存储日期型数据

一是无法使用日期函数计算比较，二是因为字符串存储要占更多的内存空间，datetime占8字节，timestamp是以int存储，占4字节。

用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

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

必须把字段定义为NOT NULL并设默认值

一是NULL需要更多的存储空间，二是NULL的列可能索引实效，三是NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑。

使用varchar(20)存储手机号，不要使用整数

一是牵扯到国家代号，可能出现+/-/()等字符，例如+86，二是varchar可以模糊查询，例如like ‘138%’

根据业务区分使用char/varchar

字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高

字段长度相差较大，或者更新较少的业务场景，适合使用varchar。

禁止在数据库中存储明文密码，把密码加密后存储

尽量不使用外键

建议在应用层实现外键的逻辑，外键与级联更新不适合高并发场景，降低插入性能，大并发下容易产生死锁。

核心表必须有行数据的创建时间和最后更新时间

核心表（如用户表，金钱相关的表）必须有行数据的创建时间字段create_time和最后更新时间字段update_time，便于查问题。

四、索引设计规范

MySQL的查询速度依赖良好的索引设计，因此索引对于高性能至关重要，合理的索引会加快查询速度，不合理的索引会降低速度。

索引其实就是一种数据结构（哈希表、树等），不同类型的索引有着不同的数据结构和功能。

索引的作用是加速查询速度，维护数据的约束性（完整性、一致性）。对于加速查询，使用索引不一定是最好的选择，小表就直接全表扫描，中到大表就建索引，超大表就分区分表，要做好索引带来的好处和维护索引的成本之间的权衡。

单表的索引数建议不超过 5 个，单个索引中的字段数建议不超过 5 个

太多就起不到过滤作用了，索引也占空间，管理也耗资源。

对字符串使用前缀索引，前缀索引长度不超过8个字符

不要索引blob/text等字段，不要索引大型字段，这样做会让索引占用太多的存储空间。

前缀索引就是对文本的前几个字符建立索引，前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)

重要的SQL必须被索引，核心SQL优先考虑覆盖索引

UPDATE、DELETE 语句的 WHERE 条件列；ORDER BY、GROUP BY、DISTINCT 的字段；多表 JOIN 的字段。覆盖索引可以避免Innodb表进行索引的二次查询，把随机IO变成顺序IO，加快查询效率。

联合索引区分度最大的字段放在前面

选择筛选性更优的字段放在最前面，比如userid，carid等，type，status等筛选性一般不建议放在最前面。索引根据左前缀原则，当建立一个联合索引(a,b,c)，则查询条件里面只有包含(a)或(a,b)或(a,b,c)的时候才能走索引，(a,c)作为条件的时候只能使用到a列索引，所以这个时候要确定a的返回列一定不能太多，不然语句设计就不合理，(b,c)则不能走索引。

业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明

显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

InnoDB 和 MyISAM 存储引擎表，索引类型必须为BTREE

MEMORY表可以根据需要选择 HASH 或者 BTREE 类型索引。

MYSQL 中索引的限制

MYISAM 存储引擎索引长度的总和不能超过 1000 字节

BLOB 和 TEXT 类型的列只能创建前缀索引

使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引

过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引

join语句中join条件字段类型不一致的时候MYSQL无法使用索引

使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’)时, MYSQL无法使用索引

使用非等值查询的时候, MYSQL无法使用 Hash 索引。

五、SQL查询规范

按需索取拒绝 select *

无法索引覆盖，回表操作，IO增加；额外的内存负担，大量冷数据灌入innodb_buffer_pool_size，降低查询命中率；额外的网络传输开销。

超过三个表禁止 join

需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段有索引。

涉及到复杂sql时，须用explain检验索引设计

对应同一列进行 or 判断时，使用 in 代替 or

in 的值不要超过 500 个， in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

in 操作能避免则避免

若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。

WHERE从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引。

count(列名)或 count(常量) 替代不了 count(*)

count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无，

count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

不要使用UNION，推荐使用UNION ALL

因为union all不需要去重，节省数据库资源，提高性能，另外子句个数限制在5个以内。

禁止使用 order by rand() 进行随机排序

会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源，推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

不得使用外键与级联，一切外键概念必须在应用层解决

以学生和成绩的关系为例，学生表中的 student_id是主键，那么成绩表中的 student_id则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；而且外键影响数据库的插入速度。