一:数据设计规范
1.所有数据库对象名称禁止使用MySQL保留关键字
select id,username,from,age from tb_user
属性中有from--表示来源,但是数据库中不会识别,此处应该采用加双引号 ----'from'
2.数据库对象的命名要见名知意,最好不要超过32个字符。
3.临时表必须以tmp为前缀并且以日期为后缀。
4.备份库,备份表必须以bak为前缀并以日期为后缀。
5.所有存储相同数据的列名和列类型必须一致。
6.所有表必须使用Innodb存储引擎-mysql 5.6以后的默认引擎(支持事务,行级锁,更好的恢复性,高并发下性能更好!)
7.数据库和表的字符集统一使用UTF-8
8.所有的表和字段都要添加注释
9.尽量控制单表的数据量的大小,建议控制在500W以内
10.尽量做到热冷数据分离,减小表的宽度。
11.禁止在表中建立预留字段。
12.静止在数据库中存储图片,文件等二进制的数据
二:索引设计规范
1.限制每张表的索引数量,建议单张表索引不超过5个。
2.Innodb是按照主键索引的顺序来组织表的。-----每个Innodb表必须有一个主键--不使用UUID,MD5,HASH字符串列作为主键
3.常见的建立索引的地方:
SELECT UPDATE DELETE 语句的WHERE从句中的列--包含ORDERY BY GROUP BY DISTINCT中的字段。 多表JOIN的关联列
4.索引列的顺序:从左到右的顺序来使用的。
区分度最高的列放在联合索引的最左侧
尽量把字段长度小的列放在联合索引的最左侧
使用最频繁的列放到联合索引的左侧。
5.避免建立冗余索引和重复索引
6.对于频繁的查询优先考虑使用覆盖索引 -- 覆盖索引:就是包含了所有查询字段的缩影
7.尽量避免使用外键约束--不建议使用外键约束,但是一定要在表与表之间的关联键上建立索引
三:数据库字段设计规范
1.字段类型的选择,会直接影响数据库的性能
2.优先选择符合存储需要的最小的数据类型--将字符串转化为数字类型存储
3.对于非负型的数据来说,要优先使用无符号整型来存储--无符号相对于有符号可以多出一倍的存储空间。
4.VARCHAR(N)中的N表示的是字节数,而不是字节数。 使用UFT8存储汉字Varchar(255)=765个字节
5.过大的长度会消耗更多的内存。
6.避免使用TEXT BLOB数据类型,避免使用ENUM数据类型
修改ENUM值需要使用alert语句、ENUM类型的ORDER BY操作效率低,需要额外操作。
7.尽可能把所有列定义为NOT NULL
8.字符串存储日期型的数据(不正确的做法)---使用TIMESTAMP或者DATETIME类型存储时间。
无法使用日期函数进行计算和比较
用字符串存储日期要占用更多的空间
9.同财务相关的金额类数据,必修使用decimal类型--decimal类型为精准浮点数,在计算时不会丢失精度
四:数据库SQL开发规范
1.建议使用预编译语句进行数据库操作。
2.避免数据类型的隐式转换--会导致索引失效
3.充分利用表上已经存在的索引。
--避免使用双%号的查询条件。如 like ‘%123%’
--一个SQL只能利用到符合索引中的一列进行范围查询
--使用left join或not eexist 来优化not in操作
4.禁止使用SELECT * 必须使用SELECT <字段列表> 查询
5.禁止使用不含字段列表的INSERT语句
6.避免使用子查询,可以把子查询优化为JOIN操作。
7.避免使用JOIN关联太多的表--每个Join一个表多占用一部分内存
8.使用in代替or
--in操作可以有效的利用索引
9.禁止使用order by rand()进行随机排序
10.WHERE从句中禁止对列进行函数转换和计算--导致无法使用索引
11.拆分复杂的大SQL为多个小SQL
五:数据库操作行为规范
1.超过100W行的批量写操作,要分批次进行操作
2.大批量操作可能会造成严重的主从延迟,避免产生大事物操作。
3.禁止为程序使用的账号赋予super权限
4.当达到最大连接数限制的时候,只允许一个有super权限的用户连接。
5.super权限只能留给DBA处理问题的账号使用。
6.对于程序连接数据库账号,遵循权限最小原则
7.程序使用数据库账号只能在一个DB下使用,不能跨库。
8.程序使用的账号原则上是不准有drop权限的。