Hefery 的个人网站

Hefery's Personal Website

Contact:hefery@126.com
  menu
73 文章
0 浏览
2 当前访客
ღゝ◡╹)ノ❤️

数据库设计规范

数据库命名规范

  • 库名与应用名称尽量一致,所有数据库对象名称必须使用小写字母并用下划线分割
  • 所有数据库对象名称禁止使用MySQL保留关建字
  • 表的命名好是遵循“业务名称_表的作用”
  • 表名不使用复数名词:表名应该仅仅表示表里面的实体内容,不应该表示实体数量
  • 所有存储相同数据的列名和列类型必须一致

数据库基本设计规范

  • 数据库和表的字符集统一使用UTF-8
  • 所有表和字段都需要添加注释
  • 尽量控制单表数据量的大小,建议控制在500万以内(历史数据归档,分库分表)
  • 禁止在表中建立预留字段
  • 主键列选择:值顺序增长
  • 不使用更新频繁的列作为主键,不使用多列主键
  • 不使用UUID,MD5,HASH,字符串列作为主键
  • 表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是 unsigned tinyint(1是,0否)

数据索引规范

  • 限制每张表上的索引数量,建议单张表索引不超过5个
  • 索引列选择
    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)
  • 对于频繁的查询优先考虑使用覆盖索引
    覆盖索引:就是包含了所有查询字段的索引
  • 尽量避免使用外键
  • 主键索引名为 pk_字段名;唯一索引名为 uk_字段名; 普通索引名则为 idx_字段名
  • 业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引
  • 超过三个表禁止join。需要 join 的字段,数据类型保持绝对一致;多表关联查询时,保证被关联的字段需要有索引
  • 利用覆盖索引来进行查询操作,避免回表。一本书要知道第 11 章的标题,会翻开第 11 章对应的那一页吗?目录浏览一下就好,这个目录就是起到覆盖索引的作用

数据库字段设计规范

  • 将字符串转化为数字类型存储
    INET_ATON('255.255.255.255')=4294967295
    INET_NTOA(4294967295)=′255.255.255.255'
  • 对于非负型的数据来说,要优先使用无符号整型 unsigned 来存储(无符号相对于有符号多出一倍存储空间)
  • 表达是与否概念字段,必须使用 is_xxx 的方式命名,数据类型是 unsigned tinyint(1是,0否)
  • 尽可能把所有列定义为NOT NULL
    索引NULL列需要额外的空间来保存,所以要占用更多的空间
    进行比较和计算时要对NULL值做特别的处理
  • 同财务相关的金额类数据,必须使用 decimal 类型
  • VARCHAR(N)中的N代表的是字符数,而不是字节数
  • 使用UTF-8存储汉字VARCHAR(255)=765个字节
  • varchar 是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长度大于此值,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索引效率
  • 过大的长度会消耗更多的内存
  • 避免使用TEXT、BLOB数据类型
  • 避免使用ENUM数据类型

数据库SQL开发规范

  • 建议使用预编译语句进行数据库操作
  • 避免数据类型的隐式转换(隐式转换会导致索引失效:select * from customer where id='111')
  • 充分利用表上已经存在的索引
    避免使用双%号的查询条件。如a like '%123%’
    使用left join 或not exists来优化not in 操作
  • 禁止使用不含字段列表的 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从句中禁止对列进行函数转换和计算
    对列进行函数转换或计算会导致无法使用索引
    where date(createtime)='20160901' --> where createtime>='20160901' and createtime<'20160902'
  • 在明显不会有重复值时使用UNIONALL而不是UNION
    UNION会把所有数据放到临时表中后再进行去重操作
    UNION ALL 不会再对结果集进行去重操作
  • 拆分复杂的大SQL为多个小SQL
    MySQL一个SQL只能使用一个CPU进行计算
  • 不要使用count(列名)或 count(常量)来替代 count(*),count(*)是 SQL92 定义的标准统 计行数的语法,跟数据库无关,跟NULL 和非 NULL 无关。说明:count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行
  • 当某一列的值全是 NULL 时, count(col)的返回结果为 0,但 sum(col)的返回结果为 NULL,因此使用 sum()时需注意 NPE 问题。可以使用如下方式来避免 sum 的 NPE 问题: SELECT IFNULL(SUM(column), 0) FROM table
  • 使用 ISNULL()来判断是否为 NULL 值(NULL 与任何值的直接比较都为 NULL)
    1.NULL<>NULL 的返回结果是 NULL, 而不是 false
    2.NULL=NULL 的返回结果是 NULL, 而不是 true
    3.NULL<>1 的返回结果是 NULL,而不是 true
  • 在表查询中,一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写明
    1.增加查询分析器解析成本
    2.增减字段容易与 resultMap 配置不一致
    3.无用字段增加网络消耗,尤其是 text 类型的字段

数据库操作行为规范

  • 超100万行的批量写操作,要分批多次进行操作
    大批量操作可能会造成严重的主从延迟
    binlog日志为row格式时会产生大量的日志
    避免产生大事务操作
  • 临时库表必须以tmp为前缀并以日期为后缀
  • 备份库,备份表必须以bak为前缀并以日期为后缀
  • 对于大表使用 pt-online-schema-change 修改表结构

标题:数据库设计规范
作者:Hefery
地址:http://hefery.icu/articles/2022/01/03/1641208646335.html