MySQL-数据库规范

命名规范

1. 库名、表名、字段名

• 禁止超过32个字符
• 使用小写的见名知意的英文单词，尽量使用名词而不是动词
• 采用下划线分割
• 数据库只存表对象，没有视图、存储过程等其他对象，故无需加入t_、v_等标识

2. 新建表具备统一前缀，对相关功能的实体应当使用相同前缀，如acl_xxx，house_xxx；其中前缀通常为这个实体的模块或依赖主实体对象的名字，通常来讲表名为：业务动作类型，或是业务类型

3. 临时表具备统一后缀,以tmp或tmp加日期为后缀，如record_tmp, record_tmp20201227

4. 备份表具备统一后缀,以bak或bak加日期为后缀，如record_bak, record_bak20201227

库表字段设计规范

1. 拆分大字段和访问频率低的字段，分离冷热数据。

2. 预估数据量超过100W时采用合适的分库分表策略。

3. 避免使用TEXT、BLOB类型，varchar字段性能比text高很多，实在避免不了blob，请拆表。

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

5. 将字符转化为数字、使用TINYINT 来代替ENUM、SET类型。tinyint(1)

6. 所有必填字段均定义为NOT NULL，并设置默认值。

7. MySQL5.6 及以后的版本中建议优先使用datetime存储时间。

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

9. 各数值类型存储所需字节数及取值范围如下

   
   

10. 通用字段参考

字段名称	字段	字段类型	备注
创建人	create_user	varchar(32)	user id
创建时间	create_time	datetime
修改人	update_user	varchar(32)	user id
修改时间	update_time	datetime
删除人	delete_user	varchar(32)	user id
删除标记	delete_flag	tinyint(1)
删除时间	delete_time	datetime
启用标记	enable_flag	tinyint(1)

索引规范

1. 索引的数量

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

2. 主键准则

• 表必须有主键，没有合适的字段的话，写可以使用自增列作为主键；
• 不使用更新频繁的列作为主键；
• 尽量选择数值类型字段，不选择字符串字段；
• 不使用UUID、MD5、HASH这些作为主键。
• 建议使用bigint(20)做主键对应为long类型

3. 重要的SQL尽量被索引

• 重要业务中的select、update、delete语句的where条件列
• 重要业务中的order by、group by、distinct字段

4. 多表JOIN的字段注意下以下几方面

• 区分度最大的字段放在最前面
• 核心SQL优先考虑复合索引,多字段关联
• 避免冗余和重复索引
• 索引要综合评估数据密度和分布以及考虑查询和更新比例
• JOIN应避免产生笛卡尔及

5. 禁止使用外键

6. 索引禁忌

• 不在低基数列上建立索引，例如：“性别”
• 不在索引列进行数学运算和函数运算
• 不在TEXT、BLOB字段上建立索引
• 索引字段必须设置为NOT NULL

9. 命名:

• 非唯一索引以idx_字段 1_字段 2 命名
• 唯一索引以uniq_字段 1_字段 2 命名
• 索引名称全部小写

10. 无法使用索引

• MYSQL 目前不支持函数索引
• 使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引。
• 过滤字段使用单行函数 (如 abs (column)) 后, MYSQL无法使用索引。
• join语句中join条件字段类型不一致的时候MYSQL 无法使用索引
• 使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’)时, MYSQL无法使用索引。
• 使用非等值查询的时候, MYSQL 无法使用 Hash 索引。
• BLOB 和 TEXT 类型的列只能创建前缀索引

SQL规范

• 降低业务耦合度,SQL语句尽可能简单 大SQL语句尽可能拆成小SQL语句，MySQL对复杂SQL支持不好。
• 禁止使用触发器、函数、存储过程。
• 避免在数据库中进行数学运算
• 避免使用select *，需要查询哪几个字段就select这几个字段
• IN()里面的数据个数建议控制在 500 以内，可以用exist代替in，exist在某些场景比in效率高，尽量不使用not in
• limit分页注意效率。limit越大，效率越低。可以改写limit，例如： select id from test - limit 10000,10 可以改写为 select id from test where id > 10000 limit 10
• 当只要一行数据时使用LIMIT 1 。
• 获取大量数据时，建议分批次获取数据，每次获取数据少于 10000 条，结果集应小于1M
• 避免使用大表做 JOIN，使用group by分组、自动排序
• SQL语句禁止出现隐式转换，例如：select id from test where id=’1’，其中 id 列为 int 等数字类型。
• 避免核心业务流程SQL包含：计算操作、多表关联、表遍历case when等复杂查询，建议拆分成单表简单查询
• OR 条件： f_phone=’10000’ or f_mobile=’10000’，两个字段各自有索引，但只能用到其中一个。可以拆分成2个sql，或者union all
• 用UNION ALL代替UNION：UNION ALL不需要对结果集再进行排序
• 任何新的select,update,delete上线，都要先explain，看索引使用情况。尽量避免extra列出现：Using File Sort，Using Temporary；rows超过1000的要谨慎上线
• 杜绝危险SQL
   比如:update/delete禁止使用where 1=1 这样无意义或恒真的条件，以防遇到sql注入
   比如:SQL中不允许出现DDL语句，一般也不给予create/alter这类权限
• DDL语句中尽量避免drop表,应先备份再删除
• 数据更新时

  1. 对同一个表的多次alter操作必须合并为一次操作
     Mysql对表的修改绝大部分操作都需要锁表并重建表，而锁表则会对线上业务造成影响。

  2. INSERT语句使用batch提交，values的个数不超过500

  3. 减少与数据库交互次数，尽量采用批量SQL语句

数据库表容量规范

• 1. 单表半年年内数据量超过500w考虑分表，可等量均衡分表或根据业务规则分表，且需提前考虑历史数据迁移或应用自行删除历史数据
• 2. 单条记录大小避免超过8k（列长度(中文)3(utf8) + 列长度(英文)1）
• 3. varchar (N)，N表示字符数而非字节数，N尽可能小，例如status使用varchar(128)进行存储就不妥。因为MySQL进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存varchar
• 4. varchar(M)，如果M<256时会使用一个字节来存储长度，如果M>=256则使用两个字节来存储长度