昆明猫咪科技网站建设,wordpress插件开发,莱芜做网站优化,做ppt的图片素材网站MySQL 数据库开发规范
目录
背景与目标规范列表 1. 库表设计 1.1 必须字段1.2 命名规范 2. 定义规范 2.1 约束规范2.2 类型规范 2.2.1 字段类型与长度2.2.2 状态字段数据类型2.2.3 布尔型2.2.4 varchar和text, json2.2.5 decimal(m,d) 3. 索引规范4. 其他规范5. SQL 使用 5.…MySQL 数据库开发规范
目录
背景与目标规范列表 1. 库表设计 1.1 必须字段1.2 命名规范 2. 定义规范 2.1 约束规范2.2 类型规范 2.2.1 字段类型与长度2.2.2 状态字段数据类型2.2.3 布尔型2.2.4 varchar和text, json2.2.5 decimal(m,d) 3. 索引规范4. 其他规范5. SQL 使用 5.1 索引5.2 查询5.3 操作 6. SQL 版本控制 6.1 审核 补充说明
前言
在开发中虽然性能大多决定于架构设计但是合理的使用sql语句是开发人员的必修课今天基于mysql官方文档给大家整理一些mysql的规定。
规范列表
规范依据约束力强弱及故障敏感性依次分为【强制】、【推荐】、【参考】三大类。
1. 库表设计
1.1 必须字段 约束度【强制】 规范描述 无特殊需求默认使用 InnoDB 存储引擎。基本约束表设计必须有主键 id、创建时间 create_time、修改时间 update_time。主键无特殊需求使用 bigint 和 auto_increment。数据库默认选取 utf8mb4 作为字符集只有 utf8mb4 才能存放 emoji 表情符。每个表的字段数不要超过 50 个无特殊需求情况。根据更新的频繁程度决定字段的顺序。为提高数据库效率将更新频繁程度高的字段排在表中靠前的位置越靠后的字段效率越低。 CREATE TABLE table_exp (id bigint unsigned AUTO_INCREMENT NOT NULL COMMENT 主键ID,package_id int unsigned NOT NULL DEFAULT 0 COMMENT 套系id,module_id int unsigned NOT NULL DEFAULT 0 COMMENT 模块id,module_name varchar(64) NOT NULL DEFAULT COMMENT 模块名称,is_delete tinyint unsigned NOT NULL DEFAULT 0 COMMENT 是否删除0-未删除1-删除默认为0,create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 修改时间,PRIMARY KEY (id),KEY idx_package_id(package_id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 ROW_FORMATDYNAMIC COMMENT模块示例表;1.2 命名规范
约束度【强制】规范描述 库名、表名、字段名索引名别名必须使用小写字母开头使用_分割不超过 30 个字符禁止使用 MySQL 保留字禁止使用字母、下划线和数字以外的其他字符。临时库、临时表必须以 _tmp_8位日期 结尾如order_tmp_20160712备份库、备份表必须以 _bak_8位日期 结尾如order_bak_20160712【create|alter】table 语句不指定字符集统一由库定义。
2. 库表设计
2.1 定义规范
约束度【强制】规范描述 同一项目产品中存储相同数据的列类型必须一致列名必须一致。同业务字段在不同项目数据表需要使用同一字段名。使用 DTS 同步的数据表和原表保持一致的字段定义表名可根据业务不同。同一个业务线采用统一字符集避免隐式转换。控制单库表个数单库表个数不超过 4096 个。创建数据库的语句必须包含字符集字句和默认的校验规则。 CREATE DATABASE IF NOT EXISTS my_database DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_bin;2.2 约束规范
约束度【强制】规范描述 所有表和字段都需要添加注释。字段设置 not null 非空约束。默认值 0 或 ‘’。
2.3 类型规范
2.2.1 字段类型与长度 约束度【强制** 规范描述 合理分配字段类型和长度字段值与类型一致避免用字符串存数字等。 price DECIMAL(10,2) NOT NULL;2.2.2 状态字段数据类型 约束度【强制】【推荐】 规范描述 表示状态字段使用 TINYINT UNSIGNED禁止使用枚举类型定义注释必须清晰地说明每个状态的含义以及是否多选等。 status TINYINT UNSIGNED NOT NULL COMMENT 1: 启用0: 禁用;2.2.3 布尔型 约束度【强制】【推荐】 规范描述 注释必须清晰地说明每个值的含义。表达是否概念或有限 list 的应该用 unsigned tinyint。 is_active TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 1: 启用0: 禁用;2.2.4 varchar和text, json
约束度【强制**规范描述 可变长度 varchar 类型长度不建议超过 1000。如果超过 4000必须分离到单表以主键和主表关联避免影响其他字段的数据效率。text 字段类型必须分离到单表以主键和主表关联避免影响其他字段的数据效率。
2.2.5 decimal(m,d)
约束度【强制**规范描述 价格或需要精确数值的字段使用 decimal避免使用 float 或 double。
3. 索引规范
约束度【强制**规范描述 一两个的查询字段和关联字段、where 字句字段可以考虑建立覆盖索引。唯一索引使用 uk_[字段名] 来命名非唯一索引使用 idx_[字段名] 来命名。长字符串采用前缀索引长度通过 count(distinct left(col_name,n))/count(1) 来计算达到 90% 即可。使用组合索引字段顺序按区分度高低排列满足最左匹配原则为优先。索引必须创建在索引选择性较高的列上。联合索引的第一个字段必须在 where 子句中。联合索引中将索引选择性高的字段靠前放。禁止使用外键。容易产生死锁且影响性能。TEXT 类型字段必须使用前缀索引。单表的索引数量控制在 7 个以内把索引建在 SELECT 操作比较频繁且数据量大的表经常有大批量插入、更新操作的表尽量少建索引。组合索引的字段数不超过 5 个。禁止对过长的 VARCHAR 类型字段建立索引。MySQL 的 VARCHAR 索引只支持不超过 768 个字节utf8 一个字符三字节即768/3256所以最长支持 255 个字符的字段创建索引。除了前缀索引外超过 32 字符的 VARCHAR 列加索引需要 DBA 评估。
4. 其他规范
约束度【强制**规范描述 禁止使用存储过程、触发器、视图、Event、自定义函数、外键约束。无特殊需求严禁使用分区表。进行大批量操作时必须分批提交每次数据量操作不能超过 10 万条。LAST_INSERT_ID() 函数只能返回当前 SESSION 最近一次 INSERT 操作之后所使用到的 AUTO_INCREMENT 类型字段的值。用 IN() / UNION 替代 OR并注意 IN 的个数不要超过 300IN 的性能高于 OR而 EXISTS / NOT EXISTS 比 IN / NOT IN 性能更优。使用 LOCATE()、POSITION()、INSTR()、FIND_IN_SET() 的性能稍微优于 LIKE。使用 PREPARED STATEMENT 可以提高性能并避免 SQL 注入。严禁开发使用 LOCK TABLE 进行人为锁表仅允许使用 SELECT ... FOR UPDATE 语句。WHERE 条件尽可能避免非等值条件IN、BETWEEN 会导致后面的条件使用不了索引。使用 UNION ALL 代替 UNION。UPDATE、DELETE 语句不要使用 LIMIT。INSERT 语句必须指明字段名称避免后期因为字段扩展而影响原有应用程序。INSERT 使用 BULK 提交VALUES 的个数不宜过多。BULK 提交可以提高写的效率。拆分复杂的 SQL 为多个小 SQL避免大事务。尽量采用批量 SQL 语句 INSERT ... ON DUPLICATE KEY UPDATEINSERT IGNOREINSERT INTO VALUES()REPLACE INTO 对同一个表的多次 ALTER 操作必须合并为一次操作开发使用 ALTER 需要 DBA 进行严格审核。
5. SQL 使用
5.1 索引
约束度【强制**规范描述 WHERE 字段列禁止使用表达式或函数它们不会使用该列上的索引如WHERE month(create_time)1 或 where num1100。禁止使用 IS NULL 或 IS NOT NULL。OR 两边字段都应该有索引。两百万以上大表禁止使用全模糊查询如 LIKE %keywork%。此类业务推荐使用搜索引擎或者全文索引。对于电话等末位匹配查询推荐反向存储数值查询时采用“前缀”。WHERE 字段列和字段值类型应该一致避免隐式转换。区间查询应该使用封闭区间避免 [-∞,n] 和 [n,∞] 不可控范围。WHERE 字段应该根据统一补充条件避免索引过度。
5.2 查询
约束度【强制**规范描述 只允许 2 表关联禁止使用 3 个表及以上的关联查询。关联查询时被关联的字段需要有索引多字段关联应该使用联合索引关联字段数据类型和字符集必须一致避免索引失效。ORDER BY 语句必须跟 LIMIT n 限制条件。统计查询禁止使用 ORDER BY。
5.3 操作
约束度【强制**规范描述 代码中禁用 SELECT *必须指定列名。 SELECT column1, column2 FROM table_name;所有内连接的 SQL 语句必须使用 INNER JOINJOIN ... ON ..外连接必须使用 LEFT JOINLEFT OUTER JOIN ... ON。不使用 RIGHT JOIN。代码中 INSERT INTO table 必须指定列名与值的对应关系。
6. SQL 版本控制
6.1 审核
约束度【强制**规范描述 版本上线更新必须提供 SQL 原型文件进行审核。SQL 脚本变更和初始化必须存储在代码仓库版本的同一级目录建立单独的目录。lshm_admin_v1.0.0.0_liaozr_20201121_001.sql 为初始全量 SQL 脚本下一个 SQL 脚本为差异 SQL 脚本按版本号顺序依次迭代。
补充说明
rowid 生成器举例 雪花算法。数据库设计示例建议覆盖所有规约项。集中业务字段将常用的基础业务字段集中管控起来字段名、字段标题、字段类型、字段长度方便业务统一。
附录 1: 字段定义长度与数据页及效率的计算
因为 MySQL 是索引组织表所以常规情况下操作 MySQL 的表都是根据索引进行的即使全表扫描也是如此。索引通常用 B树 来实现。
数据只保存在绿色的叶子结点非叶子结点都是用来索引叶子结点的。
假设索引高度为 h那么每次索引查询都要查询 h 个索引页面才能找到叶子结点的索引数据。
假设每行记录大小为 1KB则每个叶子页面可以容纳 16 行则总共可索引的行数为100W*161600W
在高度 h4 时总行数1000^3*16160亿条对于 bigint 的主键表来说通常索引树的高度在 2~4 个。
索引字段的数据类型越简单效率越好。例如int 或 tinyint索引效率会更好而 varchar(40) 等类型的扇出系数就低一些所以索引效率也会低些。
