数据库命名规范
- 所有数据库对象名称必须使用小写字母并用下划线分隔
- 数据库对象是以文件形式存储的。在Linux系统中字母的大小写是敏感的。
- 禁止使用mysql保留关键字(https://dev.mysql.com/doc/refman/5.7/en/keywords.html)
- 数据库对象的命名要见名识义,并且最好不要超过32个字符
- 临时库表必须以tmp为前缀并以日期为后缀
- 备份库表必须以bak为前缀并以日期为后缀
- 所有存储相同数据的列名和列类型必须一致
数据库基本设计规范
- 所有表必须使用InnoDB存储引擎
- 5.6以后的默认引擎
- 支持事务,行级锁,更好的恢复性,高并发下性能更好
- 数据库和表的字符集统一使用utf-8。
- 兼容性好
- 统一字符集可以避免由于字符集转换产生的乱码
- MySQL中utf8字符集汉字占3个字节,ASCII码占1个字节
- 所有表和字段都要添加注释
- 使用comment从句添加表和列的备注
CREATE TABLE `t_building` (
`id` varchar(255) NOT NULL DEFAULT '' COMMENT '主键_id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='建筑表';
- 尽量控制单表数据量的大小,建议控制在500万以内。
- 500万并不是MySQL数据库的限制,这种限制取决于存储设置和文件系统
- 数据太大的时候修改表结构,备份,恢复都会有很大问题
- 可以用历史数据归档,分库分表等手段来控制数据量大小
- 谨慎使用mysql分区表
- 分区表在物理上表现为多个文件,在逻辑上表现为一个表
- 谨慎选择分区键,跨分区查询效率可能更低
- 建议采用物理分表的方式管理大数据
- 尽量做到冷热数据分离,减少表的宽度
- 减少磁盘IO,保证热数据的内存缓存命中率
- 利用更有效的利用缓存,避免读入无用的冷数据
- 经常一起使用的列放到一个表中
- 禁止在表中建立预留字段
- 预留字段的命名很难做到见名识义
- 预留字段无法确认存储的数据类型,所以无法选择合适的类型
- 对预留字段类型的修改,会对表进行锁定
- 禁止在数据库中存储图片,文件等二进制数据
- 禁止在线上做数据库压力测试
- 禁止从开发环境,测试环境直连生产环境数据库
数据库索引设计规范
- 不要滥用索引,限制每张表上的索引数量,建议单张表索引不超过5个
- 索引并不是越多越好,索引可以提高效率同样可以降低效率
- 索引可以增加查询效率,但同样也会降低插入和更新的效率
- 禁止给表中的每一列都建立单独的索引
- 每个InnoDB表必须有一个主键
- InnoDB是按照主键的顺序来组织表的
- 不使用更新频繁的列作为主键,不使用多列主键
- 不使用uuid,md5,hash,字符串列作为主键
- 主键建议选择使用自增id值
- 在哪些列上建立索引
- select,update,delete语句的where从句中的列
- 包含在order by,group by,distinct中的字段
- 多表join的关联列
- 如何选择索引列的顺序
- 索引是从左到右的顺序来使用的
- 把区分度最高的列放在联合索引的最左侧
- 尽量把字段长度小的列放在联合索引的最左侧
- 使用最频繁的列放到联合索引的左侧
- 避免建立冗余索引和重复索引
- 比如重复索引:primary key(id),index(id),unique index(id)
- 比如冗余索引:index(a,b,c),index(a,b),index(a)
- 对于频繁的查询优先考虑使用覆盖索引
- 覆盖索引:就是包含了所有查询字段的索引
- 使用覆盖索引可以避免InnoDB表进行索引的二次查找
- 可以把随机IO变为顺序IO加快查询效率
- 避免使用外键
- 不建议使用外键约束,但一定在表与表之间的关联键上建立索引
- 外键可用于保证数据的参照完整性,但建议在业务端实现
- 外键会影响父表和子表的写操作从而降低性能
数据库字段设计规范
优先选择符合存储需要的最小的数据类型
将字符串转化为数字类型存储(ip地址应存储为数字类型)
INET_ATON(‘255.255.255.255’)=4294967295
INET_NTOA(4294967295)=’255.255.255.255’
对于非负型的数据来说,要优先使用无符号整型来存储。无符号相对有符号可以多出一倍的存储空间
signed int -2147483648~2147483647
- unsigned int 0~4294967295
varchar(N)中的N代表是字符数,而不是字节数
- 使用utf8存储汉字varchar(255)=765个字节
- 过大的长度会消耗更多的内存
避免使用text,blog数据类型
- text分为四种tinyText,text,midumtext,longtext
- 建议把blob或是text列分离到单独的扩展表中
- text或blob类型只能使用前缀索引
避免使用enum数据类型
- 修改enum值需要使用alter语句
- enum类型的order by操作效率低,需要额外操作
- 禁止使用数值作为enum的枚举值
尽可能把所有列定义为not null
- 索引null的列需要额外的空间来保存,所以要占用更多的空间
- 进行比较和计算时要对null值做特别的处理
用字符串存储日期型的数据是不正确的
- 无法使用日期函数进行计算和比较
- 用字符串存储日期要占用更多的空间
- 使用timestamp(4个字节)或datetime(8个字节)类型存储时间
- timestamp占用4字节和int相同,但比int可读性高
- 超出timestamp取值范围的使用datetime类型
同财务相关的金额类数据,必须使用decimal类型
- decimal类型为精准浮点数,在计算时不会丢失精度
- 占用空间由定义的宽度决定
- 可以存储比bigint更大的整数数据
数据库SQL开发规范
建议使用预编译语句进行数据库操作
- 只传递参数,比传递SQL语句更高效
- 相同语句可以一次解析,多次使用,提高处理效率
避免数据类型的隐式转换,如果定义的为字符串类型一定要加单引号
- 隐式转换会导致索引失效
合理利用存在索引,而不是盲目增加索引。充分利用表上已经存在的索引
- 避免使用双%号的查询。比如a like ‘%123%’
- 一个SQL只能利用到复合索引中的一列进行范围查询
- 使用left join或not exists来优化not in操作
程序连接不同的数据库使用不同的账号,禁止跨库查询
- 为数据库迁移和分库分表留出余地
- 降低业务耦合度
- 避免权限过大而产生的安全风险
禁止使用select *必须使用select 字段列表查询
- 消耗更多的CPU和IO以及网络宽带资源
- 无法使用覆盖索引
- 可以减少表结构变更带来的影响
- 禁止使用不含字段列表的insert语句
- 可以减少表结构变更带来的影响
insert into t values('a','b','c');
insert into t(c1,c2,c3) values('a','b','c');
- 避免使用子查询,可以把子查询优化为join操作
- 子查询的结果集无法使用索引
- 子查询会产生临时表操作,如果子查询数据量大则严重影响效率
- 消耗过多的CPU及io资源
- 避免使用join关联太多的表
- 每join一个表会多占用一部分内存(join_buffer_size)
- 会产生临时表操作,影响查询效率
- mysql最多允许关联61个表,建议不超过5个
- 减少同数据库交互次数
- 数据库更适合处理批量操作
- 合并多个相同的操作到一起,可以提高处理效率
- 使用in代替or
- in的值不要超过500个
- in操作可以有效的利用索引
- 禁止使用order by rand()进行随机排序
- 会把表中所有符合条件的数据装载到内存中进行排序
- 会消耗大量的CPU和io及内存资源
- 推荐在程序中获取一个随机值,然后从数据库中获取数据的方式
- where从句中禁止对列进行函数转换和计算
- 对列进行函数转换或计算会导致无法使用索引
- 在明显不会有重复值时使用union all而不是union
- union会把所有数据放到临时表中后再进行去重操作
- union all不会再对结果集进行去重操作
- 拆分复杂的大SQL为多个小SQL
- mysql一个SQL只能使用一个CPU进行计算
- SQL拆分后可以通过并行执行来提供处理效率
数据库操作行为规范
- 超过100万行的批量写操作,要分批多次进行操作
- 大批量操作可能会造成严重的主从延迟
- binlog日志为row格式时会产生大量的日志
- 避免产生大事务操作
- 对大表数据结构的修改一定要谨慎,会造成严重的锁表操作。尤其是生产环境,是不能忍受的。
- 对大表使用pt-online-schema-change修改表结构
- 避免大表修改产生的主从延迟
- 避免在对表字段进行修改时进行锁表
- 禁止为程序使用的账号赋予super权限
- 当达到最大连接数限制时,还允许1个有super权限的用户连接
- super权限只能留给DBA处理问题的账号使用
- 对于程序连接数据库账号,遵循权限最小原则
- 程序使用数据库账号只能在一个DB下使用,不准跨库
- 程序使用的账号原则上不准有drop权限