mysql规范

本文主要是介绍mysql规范，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

DBA开发规范
 数据库开发规范

一、命名规范

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

2、库名、表名、字段名支持最多64个字符但禁止超过32个字符。

3、库名、表名、字段名必须见名知意。命名与业务、产品线相关联。

4、库名、表名、字段名禁止使用MySQL保留字(如：add/analyze/check/char/to/and/as/before/between/column...)(MySQL5.5&5.7保留关键字列表；https://dev.mysql.com/doc/refman/5.7/en/keywords.html)。

5、数据库初始化会删除test库 ，库名、表名、字段名禁止使用test开头。

6、创建一rubbish库，存放无效表、垃圾表，线上系统无异常，确认无误后，定期清理。

7、创建一archive库，存放本地归档，确认无使用计划后，定期转移到归档备份服务器中。

8、临时库、表名必须以tmp为前缀，并以日期为后缀。例如 tmp_table_20140401。

9、禁止使用存储过程、触发器、视图、UDF、EVENT等。

10、账号命名规则，本业务访问账号：业务名_wn/业务名_rn；跨业务访问账号：访问组_要访问业务名_w/访问组_要访问业务名_r

二、库表规范

1、默认使用InnoDB存储引擎。

2、库、表、字段、client字符集默认使用UTF8，如遇到EMOJ等表情符号的存储需求，可申请使用UTF8MB4字符集。

3、单库不要超过500个表、单表字段数不要超过40个

4、表、字段添加comment（除主键）

5、禁止在数据库中存储图片、文件等大数据。

6、字符类型居多的表建议控制在3000W以内，整型居多的表建议控制在5000W以内。

7、不建议或禁止在线上做数据库压力测试。

8、禁止测试环境直连生产环境调试，如有数据测试需求，请走特别申请流程。

9、依据数据冷热，分级存储，历史归档

三、库表设计

表设计

1、禁止跨库查询。

2、不建议使用MySQL分区表。

3、将关键字段、使用率频繁的字段拆分到单独表中，将大字段、访问频率低的字段拆分到单独表中存储，冷热分离。

4、推荐使用HASH、RANGE进行散表，表名后缀使用数字，数字必须从0开始。

5、散表或归档分表名称需符合YYYY[MM][DD][HH]格式，例如table_201401、table_20140422

字段设计

表字段少而精，尽量选择最小数据类型，表结构设计时适当冗余，不在数据库上做大量的cpu运算

1、建表默认5字段，主键、创建时间、创建者、修改时间、修改者。id、create_time、create_user、update_time、update_user

2、建议使用UNSIGNED存储非负数值。

3、建议使用INT UNSIGNED存储IPV4。

4、用DECIMAL代替FLOAT和DOUBLE存储精确浮点数，例如支付相关数据。

5、建议整型字段直接使用INT，INT类型固定占4字节存储， 默认是11，最大有效显示宽度是255，例如INT(4)仅代表字符宽度为4位，不代表存储长度。

6、区分使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT数据类型和取值范围(TINYINT>SMALLINT>MEDIUMINT>INT>BIGINT>DECIMAL---存储空间逐渐变大,而性能却逐渐变小)。

7、禁止字段使用enum数据类型。ENUM类型不允许修改默认值，只允许顺序添加。

8、禁止使用TEXT、BLOB类型，如必须使用，建议和主键一起进行拆表处理，可考虑VARCHAR替换。

9、使用VARBINARY类型默认区分大小写，没有字符集概念，可存储大小写敏感的变长字符串或二进制内容。

10、使用尽可能小的VARCHAR字段。VARCHAR(N)中的N表示字符数而非字节数。比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N。

11、区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。 存储时间(精确到秒)建议使用TIMESTAMP类型。

12、所有字段均定义为NOT NULL。尽量避免使用NULL，要是必须用NULL，那也可考虑使用0、特殊值或空串来进行代替。

四、索引规范

1、单张表中索引数量不超过5个，单个索引中的字段数不超过5个。

2、非唯一索引按照“idx_字段名称[_字段名称]”进行命名。

3、唯一索引按照“uk_字段名称[_字段名称]”进行命名。

4、联合索引建议包含所有字段名，过长的字段名可以缩写形式。

5、表必须有主键，推荐使用UNSIGNED自增列作为主键并且该主键为非业务字段。

6、唯一键由3个以下字段组成，并且字段都是整形时，可使用唯一键作为主键。其他情况下，建议使用自增列或全局生成器作主键。

7、禁止冗余、重复索引。

8、禁止索引null列。

9、禁止使用外键，避免产生死锁，通过程序来保证约束。

10、避免数据类型转换，联表查询时，JOIN列的数据类型必须相同，并且要建立索引。

11、选择区分度大的列建立索引。字段的顺序对组合索引有至关重要的作用，组合索引中，区分度大的字段要放在最前面。

12、不在区分度低的上建立索引，例如“性别”。

13、对较长的字符串字段、blob、text使用前缀索引，前缀索引长度不超过8个字符。（使用前缀索引，不仅节省存储空间，提高索引缓存命中率，还可减少disk的IO操作。）

14、不对过长的VARCHAR字段建立索引。建议优先考虑添加CRC32或MD5伪列，并对伪列建立索引，减少索引长度，提高效率。

15、合理创建联合索引，(a，b，c) 相当于 (a) 、(a，b) 、(a，b，c)。

16、合理使用覆盖索引减少IO，避免排序。

17、添加优化的索引之后，再删除多余的索引。

18、单个索引尽可能覆盖更多的sql，更新频率比较高的表要控制索引的数量。

19、大批量变更数据，索引维护成本很高，因此大批量数据更新要拆分出小粒度。


五、SQL设计

1、使用prepared statement，可以提升性能并避免SQL注入。

2、用IN代替OR。SQL语句中IN包含的值不应过多，应少于500个。

3、禁止隐式类型转换。数值类型禁止加引号；字符串类型必须加引号。

4、避免使用JOIN和子查询，禁止使用3表以上的JOIN，必要时推荐用JOIN代替子查询。

5、避免在MySQL中索引列进行数学运算和函数运算。

6、减少与数据库交互次数，尽量采用批量提交SQL语句（INSERT INTO table （column1、column2、column3） VALUES(),(),()），不宜过多。

7、拆分复杂SQL为多个小SQL，避免大事务。

8、获取大量数据时，建议分批次获取数据，每次获取数据少于500条，结果集应小于1M。

9、用UNION ALL替换UNION。

10、禁止在前端业务中使用count(*)，可放在memcache、redis、某一从库或增加统计表来维护。

11、多表关联时禁止使用select *语句，只取需要列。

12、SQL中避免出现rand()、sysdate()、current_user()等不确定结果的函数。

13、INSERT INTO语句必须显示指明字段名称。

14、禁止使用 INSERT INTO B SELECT *  FROM  A。

15、禁止单条SQL语句同时更新多个表。

16、建议使用合理的分页方式以提高分页效率。

17、禁止在生产环境从库进行查询分析统计操作，必要时请申请专用统计服务器，前后台分离。

18、程序应有捕获SQL异常的处理机制，必要时通过rollback显式回滚。

19、程序不要修改数据库中事务隔离机制，控制锁的行为。

19、避免全表扫描，重要SQL必须被索引：update、delete的where条件列、order by、group by、distinct字段、多表join字段。

20、如果只是分组，用group by a order by null替换group by a去除排序。

21、禁止使用%前导查询，例如：like “%abc”，无法利用到索引。

22、禁止使用负向查询，例如 not in、!=、not like、<>。

23、禁止在 where 子句中对字段进行 null 值判断，字段通过增加默认值处理。

24、使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort、Using Temporary。

25、禁止DML语句中出现@num变量替代字段名称。

26、UPDATE或DELETE语句加上WHERE条件索引列更新（如果使用非索引列更新会导致锁全表，造成Lock wait timeout exceeded; try restarting transaction），禁用LIMIT语句。

27、拼接sql语句时，注意where条件，防止sql注入时表记录被清空（如：delete from where a/a=a/1=1/a=0 or 1=1/exist返回布尔值为恒真）。

28、DML：sql语句中不允许有drop、truncate、delelet all data等语句。

29、DDL：添加字段语句禁止使用after，特别是对于mycat或gaea的分布式代理的数据库，如果加上after，会造成数据错误。例如：alter table t add column a after
 b
---------------------------------------------------------------------------------------------------------

新建表参考：

create table  tb_cart_goods_special (

  id int unsigned not null  auto_increment,

  area varchar(10)  not null default 'cn' comment '区域简称, e.g. cn,hk,tw,sg',

 region_name varchar(20) not null default '' comment '区域名称',

 primary key (id)

）engine=innodb  auto_increment=1 default charset=utf8 comment='购物车下面单个商品的推荐调整’;


添加字段参考：

alter table tb_cart_goods_special add goods_id int unsigned not null default  '0'  comment  '商品编号’;



添加索引参考（idx_,uk_）：

alter table tb_cart_goods_special add index idx_area(area),add unique index uk_goods_id(goods_id);

Mysql数据库备份安全

1、备份方式：全备默认xtrabackup为主，mysqldump为辅。

2、备份频率：全备默认每周一次，核心业务每两天一次。

2、历史归档：不同的IDC备份文件上传到异地远端的Hadoop集群中。

3、备份校验：opm校验+手工校验。

4、备份压缩：目前采取lz4，加快压缩速度。

5、备份加密：binlog和全备都需要加密，默认采取openssl 1.0.1i，算法aes，key大小256bit，数据分组方式cbc。

6、如连续两次出现误操作需要dba进行闪回修复，应搭建Delay数据库。

Linux系统及Mysql安全参数规范
1、禁止DB公网访问。
2、禁止开发人员访问DB生产环境，开发通过opm查询，只能查业务的从库，不允许查询主库。
3、Linux系统配置初始化安全选项
      1）别名策略，删除前提示，防止误删除。
           alias rm='rm -i --'，alias cp='cp -i --'，alias mv='mv -i --' 
      2）/etc/sysctl.conf中tcp、syn等相关配置统一
 
4、Mysql初始化安全选项：
     1）数据目录、日志目录，以及目录下文件属组设立单独组work管理
     2）Mysql后台启动方式加入--user=work 。
     3）bind-address绑定内网IP。
     4）修改Mysql默认服务端口号，不同业务设置不同的端口号。
     5）max_user_connections设置每个用户的最大连接数，过载保护。
     6）启用safe-user-create，用户不能用GRANT语句创建新用户，除非用户有mysql.user表的INSERT权限。
     7） 禁止打开load data local file权限。
 1、应用帐号不得拥有File，Grant，Reload，Shutdown，Process等权限。
2、应用帐号不得拥有系统数据库的任何权限。
3、应用帐号只给所属应用的数据库授权，只给其所属应用的IP或代理授权。
4、禁止应用帐号权限设置为all，主库select,insert,update,delete,execute即可满足需求，如有特殊需求，单独处理。
5、mysql从库必须添加read-only，只给select权限，严禁all，导致只读失效。

这篇关于mysql规范的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！