Mysql数据库设计规范

  我们在项目一开始的设计中,就要忙着考虑数据库的设计,表、字段、索引、sql等等,而在项目比较大型的时候,团队开发中由于多人同时进行,那么尽早的进行设计规范是项目开发非常关键的一步,那么关于数据库设计规范有哪些呢,包括以下六项: 

1
2
3
4
5
6
7
8
9
10
11
1.数据库命名规范

2.数据库基本设计规范

3.数据库索引设计规范

4.数据库字段设计规范  

5.SQL开发规范

6.数据库操作行为规范

  
接下来逐一详细介绍一下各个规范:

1.数据库命名规范:

  1)所有数据库对象名称必须使用小写字母并用下划线分割(MySQL对大小写是敏感的)

  2)禁止使用MySQL的保留关键字(比如:select user,from,age from tb_user 这时候识别不出from关键字,除非使用from,反向单引号来区别)

  3)数据库对象的命名最后能够见名识义,并且最好不要超过32个字符 ,例如:用户数据库 bd_userdb (百度_用户数据库) ,用户账号表 user_account

  4)临时库表必须以tmp前缀并以日期为后缀,tmp_user_20180505 提供更加明确的表名

    备份库,备份表必须以bak前缀并以日期为后缀,bak_userdb_20180504 bak_user_20180505

  5)所有存储相同数据的列名和类型必须一致,例如:不同库表中的user_id(int unsigned not null),那么名称和类型必须是一致的,否则会产生查询效率降低等各种问题

2.数据库基本设计规范:

  1)所有表必须使用InnoDB存储引擎。在MySQL5.6以后,innodb已经成为了默认存储引擎,它支持事务、行级锁,更好的恢复性,高并发下性能更好

  2)数据库和表的字符集统一使用UTF-8。为了避免乱码、性能等问题

  3)所有的表和字段都需要添加注释。使用comment添加备注信息,从一开始就进行数据库字典的维护

  4)尽量控制单表数据量的大小,建议控制在500万行以内。使用历史数据归档、分库分表操作手段来控制数据量的大小

  5)谨慎使用MySQL中的分区表。跨分区查询效率比较低,建议采用物理分表的方式来管理大数据

  6)尽量做到冷数据分离,减小表宽度。字段太多的情况,尽量分表,将常用的放在一块,不常用的字段分到其他表中,有效减少磁盘的IO,保证热数据的缓存命中率

  7)禁止在表中建立预留字段。由于无法预知预留字段的类型,后期对改字段进行修改会耗费很多资源,对表进行锁定等问题

  8)禁止存储图片、文件等二进制文件,造成MySQL的性能影响。这些应该存储到专门的图片、文件服务器中,数据库中只存储对应的信息标识。

    禁止在线上做数据库压力测试

    禁止从开发环境、测试环境直连生成环境数据库

3.索引设计规范:

  1)限制每张表索引的数量,建议单表索引不超过5个 。索引并不是越多越好,能提高查询效率,也能降低效率。应该根据实际情况来建立索引。

  2)每个InnoDB表中必须有一个主键(唯一非空列)。不用使用频繁更新的列为主键,不使用MD5,UUID,HASH,字符串列作为主键。主键建议选择使用自增ID值

  3)常见索引列建议:where从句中的列 order by、group by、distinct 中的字段,多表join的关联列,如果在字句中是单个列,那就单独索引,有多个列,那可以建立联合索引

  4)如何选择索引列的顺序,区分度最高(比如主键列)的列,字段长度小,使用频繁的列放在联合索引的最左侧

  5)避免建立冗余和重复的索引:index(a,b,c),index(a,b),index(a) 对于a就是重复索引

  6)对于频繁的查询优先考虑使用覆盖索引:包含了所有查询字段的索引

  7)尽量避免使用外键约束。外键会影响父表与子表的写操作从而降低性能

4.数据库字段设计规范:

  1)优先选择符合存储需要的最小数据类型。例如:将字符串转化为数字存储

   对于非负数优先选用无符号型来存储。例如:主键id,无符号比有符号多出一倍的存储空间。

   有符号:signed int -2147483648 ~ 2147483647

   无符号:unsigned int 0 ~ 4294967295

   varchar(N) N代表的是字符数,而不是字节数,使用UTF8存储汉字varchar(255)=765个字节

   过大的长度会消耗更多的内存,根据字段长度来分配内存。

  2)避免使用Text、Blob数据类型,若需要使用,尽量分配到专门的扩展表中

  3)避免使用Enum枚举类型。order by操作效率低。禁止使用数值作为ENum枚举值

  4)尽可能把所有列定义为NOT NULL。索引NULL列需要更多的存储空间来保存。索引会失效。

  5)避免使用字符串来存储日期时间,使用TIMESTAMP或DATATIME来存储时间

  6)与财务相关的金额类型数据,必须使用Decimal类型。保证数据精度,计算时不丢失精度。

5.数据库SQL开发规范:

  1)建议使用预编译语句进行数据库操作。只传参数,比传递sql更加高效,相同语句一次解析之后,多次使用,节约sql解析的成本,提高处理效率。

  2)避免数据类型的隐式转换。隐式转换导致索引失效,一般在where字句条件中出现的类型转换,导致了索引失效。

  3)合理利用已存在索引,而不是盲目添加索引。

    避免使用双%的查询条件:like ‘%123%’,只要出现前缀%,索引失效。

    一个SQL只能利用到复合索引的一列进行范围查询,若联合索引 index(a,b,c) 对a进行范围查询,那么b和c将失效,应当将a放到最右侧

    使用left join 或 not exists 来优化 not in 操作,not in会使索引失效

  4)程序连接不同数据库时应该使用不同的账号,禁止跨库查询

  5)禁止使用 select * 必须使用 select <字段列表> 查询,消耗过多的IO和cpu以及网络带宽资源

  6)禁止使用不含字段的insert 语句,为了减少表结构的变更带来的影响:insert into table values(‘a’,’b’,’c’); 应当指明要插入的列,insert into table(c1,c2,c3) values(‘a’,’b’,’c’);

  7)避免使用子查询,可以将子查询优化为join操作:子查询都会创建临时表,占用cpu和io资源,子查询结果集无法使用索引。

  8)避免使用join关联太多的表:

    每关联一张表,多占用一部分内存(join_buffer_size)

    会产生临时表操作,影响查询效率

    MySQL最多允许关联61张表,建议不超过5张表

  9)减少同数据库的交互次数

  10)使用in代替or。in的值不超过500个,in可以有效使用索引,or不行。

  11)禁止使用order by rand() 进行随机排序,这个操作对性能有很大影响,尽量通过程序来得到随机值再从数据库中获取数据。

  12)禁止在where从句中对列进行函数转换和计算,造成索引的失效。where data(createtime) = ‘2018-01-01’ ,尽量在程序中进行计算

  13)在明显不会出现重复值的时候使用union all 而不是union。union会先加载所有数据到临时表中然后去重,而union all不会去重。

  14)拆分复杂的大SQL成多个小SQL。并行执行小SQL来提高处理效率

6.数据库操作行为规范:

  1)超过100万行的批量写操作,要分批多次进行操作:

    大批量操作可能造成严重的主从延迟问题

    binlog日志为row格式时,胡产生大量的日志,造成资源不足

    避免产生大事务的操作

  2)对于大表使用pt-online-schema-change工具来修改表结构。过程是:先创建新表,然后复制旧表数据到新表,将新表名称改成旧表名称,最后删除旧表

  3)禁止为程序使用的账号赋予super超管权限

  4)对于程序连接数据库账号,遵循权限最小的原则。程序使用数据库支行和只能在一个DB下使用,不准跨库,程序使用的账号原则上不准有drop权限

以上就是MySQL的一些设计规范,当然不是说一定要遵循以上的原则,具体视实际应用场景而定,通过DBA指导来指定原则。

-------------本文结束感谢您的阅读-------------
0%