mysql数据库开发工具 mysql开发规范

转载

mob64ca140b82e3 2023-08-11 17:47:26

文章标签 mysql数据库开发工具 mysqk 规范字段数据库 文章分类 MySQL 数据库

一、基础规范

二、表设计规范

三、字段设计规范

四、索引设计规范

五、SQL使用规范

六、SQL设计审核

七、读写分离使用

一、基础规范

1. 必须使用InnoDB存储引擎

解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高，备份可以不涉及表级别的锁。

2. 必须使用UTF8字符集

解读：避免乱码带来的展示问题字符集推荐使用utf8mb4字符集,utf8mb4是utf8的超集，emoji表情以及部分不常见汉字在utf8下会表现为乱码，故需要升级至utf8mb4，可根据情况指定具体字段的字符集。

3. 数据表、数据字段必须加入中文注释

解读：后面维护业务的同学清楚的了解数据表的业务范围

4. 禁止使用存储过程、视图、触发器、Event

解读：并发量大的情况下，这些功能很可能将数据库搞垮，业务逻辑放到服务层具备更好的扩展性，能够容易实现“增机器就加性能”。

5. 禁止存储大文件或者照片类的数据

解读：数据库要做他擅长的事情，文件类型的存储要放在存储服务上

二、表设计规范

1. 表名与实际存储对象贴切,一般采用单数形式，全部采用小写形式,表名加模块(服务)前缀例:auth_rbac_role

2. 多对多关系表必须命名为 table1_table2_mapping 的形式

3. 时间数据类型必须使用datetime、ip地址必须使用init类型

IP转换函数：ip地址转换数字inet_aton()，数字转换IP地址：inet_ntoa()

4. 字段名称与实际业务场景贴切，尽量不使用超过业务范畴的通用名，例如admin_id应确切区分业务场景为sale_admin_id或者其他

5. 表必须有主键，例如自增主键

解读： a) 主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用 b) 主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率 c) 无主键的表删除，在row模式的主从架构下，会导致备库夯住

6. 应禁止使用外键，如果有外键完整性约束，需要应用程序控制

解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql的性能，甚至会造成死锁。高并发情况下容易造成数据库性能问题

7. 重要：创建新的表必须显示的指定存储引擎engine=InnoDB

解读：因mysql版本的问题低于5.5的版本默认的存储引擎是MyISAM

三、字段设计规范

1. 字段名必须为英文小写，单词间用 "_" 分割

2. 必须把字段定义为NOT NULL并且提供默认值，默认值根据业务的实际情况来决定，应该用0、一个特殊的值或者一个空串代替空值

解读： a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化 b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多 c）null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识 d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’wby’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

3. 尽量不要使用TEXT、BLOB类型，如果必须要用到，需单独将此类数据单独建表

解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能，变更表结构的时候也会有大量的binlog产生，可能会使复制导致复制崩溃

4. 必须使用varchar(20)存储手机号

解读： a）涉及到区号或者国家代号，可能出现+-()

5. 禁止使用ENUM，可使用TINYINT代替

解读： a）增加新的ENUM值要做DDL操作，重建整个表，这在资源和时间方面是昂贵的 b）获取完整的枚举列表很复杂，因为需要访问information_schema数据库

6. 表主键必须为定义为 id int unsigned NOT NULL auto_increment primary key

7. 外键字段名必须为 table_id 的形式

8. 非 mapping 表必须有如下三个默认字段

created_at datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '创建时间',（必有） modified_at datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '更新时间',（必有） is_deleted tinyint NOT NULL DEFAULT 2 COMMENT '是否删除，1 - 是，2 - 否'（必有）精确到秒的时间：xxx_time（具体业务添加）精确到天的时间：xxx_date（具体业务添加）创建人id：created_by（具体业务添加）修改人id：modified_by（具体业务添加）

9. 所有判断类型的字段必须命名为 is_xxx、has_xxx 形式，字段类型使用 tinyint，取值 1 是 2 否

10. 表示状态的字段必须命名为 xxx_status，字段类型 tinyint，注释中说明取值含义

11. 时间字段必须按如下方式命名

12. 禁止使用关键字作为字段命名

四、索引设计规范

1. 单表索引建议控制在5个以内

解读：索引不是越多越好，太多insert、update等设计更多的IO操作

2. 单索引字段数不允许超过5个

解读：字段超过5个时，实际已经起不到有效过滤数据的作用了

3. 禁止在更新十分频繁、区分度不高的属性上建立索引

解读： a）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能 b）“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似

4. 建立组合索引，必须把区分度高的字段放在前面

解读：能够更加有效的过滤数据索引命名规则： a）普通索引： idx列名列名例如：idx_account_id_status b）唯一索引：uidx列名列名例如：uidx_account_id_status

五、SQL使用规范

1. 禁止使用SELECT * ，只获取必要的字段，需要显示说明列属性

解读： a）读取不需要的列会增加CPU、IO、NET消耗 b）不能有效的利用覆盖索引 c）使用SELECT * 容易在增加或者删除字段后出现程序BUG

2. 禁止使用INSERT INTO table VALUES(xxx)，必须显示指定插入的列属性。

解读：容易在增加或者删除字段后出现程序BUG。

3. 禁止使用属性隐式转换

解读：SELECT uid FROM babysitter_user WHERE phone=13812345678。会导致全表扫描，而不能命中phone索引，这里电话号码mysql识别成了数字而不是字符串类型。

4. 禁止在WHERE条件的属性上使用函数或者表达式

解读：SELECT uid FROM t_user WHERE from_unixtime(day)>=’2017-02-15’ 会导致全表扫描。正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp(‘2017-02-15 00:00:00’)

5. 禁止负向查询，以及%开头的模糊查询

解读： a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描。 b）%开头的模糊查询，会导致全表扫描, 可以使用find_in_set()、locate()、position()等内置函数替代

6. 禁止大表使用JOIN查询，禁止大表使用子查询

解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能

7. update修改字段值的时候必须显示的使用** limit** 关键字限制返回行数【适用发布系统发布的sql】

注：在开发测试环节中首先要自行打印框架生成的SQL语句，并通过explain来查看执行计划是否最优，如不确定可寻求DBA的帮助解决