数据库设计规范

不懂数据库的后台开发工程师不是一个合格的后台开发工程师。前面就一直想着把自己在面试中遇到的数据库相关问题记录下来,但是一直没有一个好的记录顺序,今天意外的发现一个讲数据库的课程,里面所讲的内容就是我想总结的内容,缘分。下面就数据库设计规范进行一些简单的总结,虽然自己还没有开发经验,但是听一听、看一看、写一写,终究还是能记住一些的。

数据库命名规范

  • 所有数据库对象名称必须使用小写字母并用下划线分割
  • 所有数据库对象名称禁止使用MySQL保留关键字
  • 数据库对象命名要能做到见名识义,并且最好不要超过32个字符
  • 临时库表必须以tmp为前缀并以日期为后缀
  • 备份库。备份表必须以bak为前缀并以日期为后缀
  • 所有存储相同数据的列名和列类型必须一致(提高查询性能)

数据库和表的字符集统一使用UFT8,如果存储表情,使用UTF扩展集
MySQL中UTF8字符集占3个字节,ASCII码占用1个字节

  • 所有表和字段都需要添加注释
    使用comment从句添加表和列的备注
    从一开始就进行数据字典的维护

索引设计规范

  • 不使用更新频繁的列作为主键,不使用多列主键
  • 不使用UUID,MD5,HASH,字符串列作为主键(UUID不能保证当前ID和已经插入ID的大小)
  • 在SELECT、UPDATE、DELETE语句的WHERE从句中的列、包含在 ORDER BY、GROUP BY、DISTINCT中的字段、多表JOIN关联的列建立索引

在联合索引中,如何设置索引列的顺序
区分度最高的列放在联合索引的最左侧
尽量把字段长度小的列放在联合索引的最左侧
使用最频繁的列放到联合索引的左侧

数据库字段设计规范

  • 优先选择符合存储需要最小的数据类型
    • 将字符串转化为数字类型:IP地址转换为数字

      INET_ATON(‘255.255.255.255’)=4294967295
      INET_NTOA(4294967295)=255.255.255.255
      存储字符串可能需要15个字节,而存储数字只需要4个字节
      varchar(N)中的N代表的是字符数,而不是字节数
      使用UTF-8存储汉子 varchar(255)=765字节

    • 对于非负数据采用无符号整形进行存储

      SIGNED INT – 2147483648~2147483647
      UNSIGNED INT 0 – 4294967295

  • 避免使用TEXT、BLOB数据类型

  • 避免使用ENUM数据类型

    虽然存的是字符串,但是使用整形存储且只能存储65535个枚举类型
    修改ENUM值需要使用ALTER语句
    ENUM类型的ORDER BY操作效率低,需要额外操作
    进制使用数值作为ENUM的枚举值

  • 尽可能把所有列定义为 NOT NULL

  • 字符串存储日期型的数据(不正确的做法)
    缺点1:无法用日期函数进行计算和比较
    缺点2:用字符串存储日期要占用更多的空间
    使用TIMESTAMP(占四个字节)或DATETIME(占八个字节)类型存储时间
    TIMESTAMP 显示的时间长度:1970-01-01 00:00:01 – 2038-01-19 03:14:07(和INT表示的范围一样)
    DATETIME显示的时间长度:1000-01-01 00:00:00″到”9999-12-31 23:59:59″

数据库SQL开发规范

  • 建议使用预编译语句进行数据库操作

    预编译的好处:
    只传参数,比传递SQL语句更高效
    相同语句一次解析,多次使用,提高处理效率,还可以防止SQL注入

  • 减少同数据库的交互次数
    数据库更适合处理批量操作
    合并多个相同的操作到一个操作

  • 禁止使用 ORDER BY RAND() 进行随机排序
    这种方式会把表中所有符合条件的数据加载到内存中进行排序,会消耗大量CPU和IO的资源

  • WHERE从句中禁止对列进行函数转换和计算
    对列进行函数转换或计算会导致无法使用索引
    where date(createtime)=’20160801’ 改进为
    where createtime >=’20160801’ and createtime <’20160802’,通过这样改进,就可以使用createtime上的索引

  • 拆分复杂的SQL为多个简单的SQL
    MySQL一个SQL只能使用一个CPU进行计算

  • 避免数据类型的隐式转换
    会使索引失效

  • 禁止使用 select* 进行查询及没有子段列表的insert操作

数据库操作规范

  • 超100万行的批量写操作,要分批次多次进行操作
    大批量操作可能会造成严重的主从延迟
    binlog日志为row格式时会产生大量日志
    避免产生大事务操作

  • 对大表数据进行修改一定要慎重
    对于大表使用 pt-online-schema-change 工具(percona公司)修改表结构

  • 禁止为程序使用的账号赋予super权限

  • 对于程序连接数据库账号,遵循权限最小原则
    程序使用数据库账号只能在一个DB下使用,不准夸库
    程序使用的账号原则上不准有drop权限

说点什么

avatar
  Subscribe  
提醒

相关文章

开始在上面输入您的搜索词,然后按回车进行搜索。按ESC取消。

返回顶部