【mysql规范】

飞翔的辉少

已于 2022-07-26 19:19:27 修改

阅读量497

点赞数

文章标签： java 数据库 mysql

于 2022-07-26 18:43:26 首次发布

本文链接：https://blog.csdn.net/qq_29279073/article/details/125945565

版权

MySQL索引规范

建表约束

建表规范

1:表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字，不允许出现复数名词，禁止出现mysql保留字（https://dev.mysql.com/doc/refman/5.7/en/keywords.html）
2:创建表必须包含3个字段 id, gmt_create, gmt_modified。
3:表的命名最好是遵循“业务名称_表的作用”。
4:单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表
5:创建表时候需要对字段和表添加comment注释，字段表示的状态追加时，需要及时更新注释。
6:字段允许适当冗余，以提高查询性能，但必须考虑数据一致。
冗余字段应遵循：
1) 不是频繁修改的字段。
2）不是唯一索引的字段。
3）不是 varchar 超长字段，更不能是 text 字段。
7:创建表的时候必须指定主键,而且推荐最好是自增的，
优点是：
1）整数类型比字符串类型的占用数据量比较小
2）数据顺序递增，不会导致节点的频繁分裂
3）数据类型比字符类型效率更高
8:创建表的sql语句中不要使用drop tables if exists tables,可能会把线上正在使用的表进行删除，推荐使用create tables if not exists tables

数据类型定义

整数类型

在这里插入图片描述
在评估用哪种整数类型的时候，你需要考虑存储空间和可靠性的平衡问题：一方面，用占用字节数少的整数类型可以节省存储空间；另一方面，要是为了节省存储空间，使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起系统错误，影响可靠性。

浮点数类型和定点数类型

浮点数据类型（FLOAT、DOUBLE、REAL）

FLOAT 占据4个字节。范围要小一点
DOUBLE 占据8个字节。范围要大一点
REAL 默认是DOUBLE类型的，但是可以通过设置修改为FOLAT类型的
因为无论是FOLAT或者DOUBLE类型都是以二进制进行存储的，二进制存储就会存在有些位数无法用二进制准确表示出来，所有会导致浮点数有缺陷不精准

定点数据类型（DECIMAL）

它是把十进制数的整数部分和小数部分拆开，分别转换成十六进制数，进行存储。这样，所有的数值，就都可以精准表达了，不会存在因为无法表达而损失精度的问题。

如果选择是浮点数据还是定点数据
浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）；定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如涉及金额计算的场景）。

日期时间类型

主要包括几种类型（year、time、date、dateTime、timeStamp）
在这里插入图片描述
建议：为了确保数据的完整性和系统的稳定性，优先考虑使用 DATETIME 类型。因为虽然 DATETIME 类型占用的存储空间最多，但是它表达的时间最为完整，取值范围也最大。

文本类型

CHAR(M)：固定长度字符串。CHAR(M) 类型必须预先定义字符串长度。如果太短，数据可能会超出范围；如果太长，又浪费存储空间。
VARCHAR(M)：可变长度字符串。VARCHAR(M) 也需要预先知道字符串的最大长度，不过只要不超过这个最大长度，具体存储的时候，是按照实际字符串长度存储的。
TEXT：字符串。系统自动按照实际长度存储，不需要预先定义长度。
不过，需要注意的是，TEXT 也有一个问题：由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用 CHAR(M)，或者 VARCHAR(M)。

索引规范

如何添加索引：
添加表索引时候一般是写完业务逻辑以后，参考相关的sql语句

索引原则：

【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
【推荐】限制每张表上的索引数量,建议单张表索引不超过 5 个
【推荐】比较频繁的查询利用覆盖索引来进行查询操作，避免回表。
【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts
【推荐】建组合索引的时候，区分度最高的在最左边。

SQL原则：

【强制】不要使用 count(列名)或 count(常量)来替代 count()，count()会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
【强制】使用 ISNULL()来判断是否为 NULL 值。
【强制】代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。
【强制】数据订正（特别是删除或修改记录操作）时，要先 select，避免出现误删除，确认无误才能执行更新语句。
【强制】当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为NULL，因此使用 sum()时需注意空指针问题
【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。
【推荐】SQL 语句中表的别名前加 as，并且以 t1、t2、t3、…的顺序依次命名。
【推荐】in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。
【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。