Mysql 设计规范

公司要开发一个新项目,之前是.NET Framework + Sql Server,这次要用.NET Core + Mysql,没怎么接触过mysql,所以做数据库设计之前,结合网上的资料,以及项目的使用,总结了一下Mysql设计规范。按照规范设计了数据库,目前项目开始正常开发,没遇到因为数据库设计产生的问题,记录一下。ps.随着开发,应该还会记录下mysql与SQL server的差异。

命名规范

基本命名原则

  • 使用有意义的英文词汇,不要使用拼音或拼音缩写。
  • 只能使用英文字母,数字,下划线,并以英文字母开头。
  • 表名库名全部小写用_分隔,列名Pascal(避免代码中太多attribute)
  • 避免用 MySQL 的保留字,如 desc,关键字如 index
  • 命名禁止超过32个字符,须见名之意,建议使用名词不是动词
  • 数据库,数据表一律使用前缀
    • 临时库、表名必须以tmp为前缀,并以日期为后缀
    • 备份库、表必须以bak为前缀,并以日期为后缀

表命名

  • 同一个模块的表尽可能使用相同的前缀,表名称尽可能表达含义。所有日志表均以 log_ 开头

字段命名

  • 表达其实际含义的英文单词或简写。布尔意义的字段以“Is”作为前缀,后接动词过去分词。
  • 各表之间相同意义的字段应同名。各表之间相同意义的字段,以去掉模块前缀的表名+字段名命名。
  • 外键字段用表名+字段名表示其关联关系。
  • 表的主键一般都约定成为Id,自增类型,是别的表的外键均使用xxxId的方式来表明。

索引命名

  • 非唯一索引必须按照idx_字段名称_字段名称[_字段名]进行命名
  • 唯一索引必须按照 uniq_字段名称_字段名称[_字段名]进行命名

约束命名

  • 主键约束:pk_表名称
  • 唯一约束:uk_表名称_字段名 。(应用中需要同时有唯一性检查逻辑。)
  • 外键约束:fk_从表名称_主表名称

触发器命名

  • trg_表名_操作

函数过程命名

  • 采用动词+名词的形式表达其含义。

序列命名

  • seq_表名

表设计规范

  1. 表引擎取决于实际应用场景;日志及报表类表建议用myisam,与交易,审核,金额相关的表建议用innodb引擎。如无说明,建表时一律采用innodb引擎
  2. 默认使用utf8mb4字符集,数据库排序规则使用utf8mb4_general_ci,(由于数据库定义使用了默认,数据表可以不再定义,但为保险起见,建议都写上)。
  3. 所有表、字段均应用 comment 列属性来描述此表、字段所代表的真正含义,如枚举值则建议将该字段中使用的内容都定义出来。
  4. 如无说明,表中的第一个id字段一定是主键且为自动增长,禁止在非事务内作为上下文作为条件进行数据传递。禁止使用varchar类型作为主键语句设计。
  5. 如无说明,表必须包含CreateTimeModifyTime字段,即表必须包含记录创建时间和修改时间的字段
  6. 如无说明,表必须包含IsDel,用来标示数据是否被删除,原则上数据库数据不允许物理删除。
  7. 用尽量少的存储空间来存数一个字段的数据
    • 能用int的就不用char或者varchar
    • 能用tinyint的就不用int
    • 使用UNSIGNED存储非负数值。
    • 不建议使用ENUM、SET类型,使用TINYINT来代替
    • 使用短数据类型,比如取值范围为0-80时,使用TINYINT UNSIGNED
    • 存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE
    • 时间字段
      • 存储年使用YEAR类型。
      • 存储日期使用DATE类型。
      • 存储时间(精确到秒) 统一使用 DATETIME
    • 尽可能不使用TEXT(mysql磁盘读取检索)、BLOB类型
    • 禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。建议使用其他方式存储(TFS/SFS),MySQL只保存指针信息。
  8. 如无备注,所有字段都设置NOT NULL,并设置默认值;
  9. 禁止在数据库中存储明文密码
  10. 如无备注,所有的布尔值字段,如IsHotIsDel,都必须设置一个默认值,并设为0;
  11. 整形定义中不添加长度,比如使用INT,而不是INT[4]

索引设计规范

MySQL的查询速度依赖良好的索引设计,因此索引对于高性能至关重要。合理的索引会加快查询速度(包括UPDATE和DELETE的速度,MySQL会将包含该行的page加载到内存中,然后进行UPDATE或者DELETE操作),不合理的索引会降低速度。MySQL索引查找类似于新华字典的拼音和部首查找,当拼音和部首索引不存在时,只能通过一页一页的翻页来查找。当MySQL查询不能使用索引时,MySQL会进行全表扫描,会消耗大量的IO。索引的用途:去重、加速定位、避免排序、覆盖索引。

索引的基本规范

  1. 索引数量控制,单张表中索引数量不超过5个,单个索引中的字段数不超过5个。

    • 综合评估数据密度和分布
    • 考虑查询和更新比例
  2. 对字符串使用前缀索引,前缀索引长度不超过8个字符,建议优先考虑前缀索引,必要时可添加伪列并建立索引。

    • 不要索引blob/text等字段,不要索引大型字段,这样做会让索引占用太多的存储空间

      什么是前缀索引?

      前缀索引说白了就是对文本的前几个字符(具体是几个字符在建立索引时指定)建立索引,这样建立起来的索引更小,所以查询更快。 前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。

      建立前缀索引的语法:ALTER TABLE table_name ADD KEY(column_name(prefix_length));

  3. 主键准则

    • 表必须有主键
    • 不使用更新频繁的列
    • 尽量不选择字符串列
    • 不使用UUID MD5 HASH
    • 默认使用非空的唯一键
    • 建议选择自增或发号器
  4. 索引禁忌

    • 不在低基数列上建立索引,例如“性别”
    • 不在索引列进行数学运算和函数运算
    • 不要索引常用的小型表
  5. 尽量不使用外键

    • 外键用来保护参照完整性,可在业务端实现
    • 对父表和子表的操作会相互影响,降低可用性
    • INNODB本身对online DDL的限制

MYSQL 中索引的限制

  • MYISAM 存储引擎索引长度的总和不能超过 1000 字节
  • BLOB 和 TEXT 类型的列只能创建前缀索引
  • MYSQL 目前不支持函数索引
  • 使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引。
  • 过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引。
  • join语句中join条件字段类型不一致的时候MYSQL无法使用索引
  • 使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’)时, MYSQL无法使用索引。
  • 使用非等值查询的时候, MYSQL 无法使用 Hash 索引。

语句设计规范

  1. 使用预编译语句
    • 只传参数,比传递SQL语句更高效
    • 一次解析,多次使用
    • 降低SQL注入概率
  2. 避免隐式转换
    • 会导致索引失效
  3. 充分利用前缀索引
    • 必须是最左前缀
    • 不可能同时用到两个范围条件
    • 不使用%前导的查询,如like “%ab”
  4. 不使用负向查询,如not in/like
    • 无法使用索引,导致全表扫描
    • 全表扫描导致buffer pool利用率降低
  5. 避免使用存储过程、触发器、UDF、events等
    • 让数据库做最擅长的事
    • 降低业务耦合度,为sacle out、sharding留有余地
    • 避开BUG
  6. 避免使用大表的JOIN
    • MySQL最擅长的是单表的主键/二级索引查询
    • JOIN消耗较多内存,产生临时表
  7. 避免在数据库中进行数学运算
    • MySQL不擅长数学运算和逻辑判断
    • 无法使用索引
  8. 视情况使用以下语句,减少与数据库的交互次数
    • INSERT … ON DUPLICATE KEY UPDATE
    • REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()
    • UPDATE … WHERE ID IN(10,20,50,…)
  9. 合理的使用分页
    • 限制分页展示的页数
    • 只能点击上一页、下一页
    • 采用延迟关联
  10. 拒绝大SQL,拆分成小SQL
    • 充分利用QUERY CACHE
    • 充分利用多核CPU
  11. 使用in代替or,in的值不超过1000个
  12. 禁止使用order by rand()
  13. 使用EXPLAIN诊断,避免生成临时表
  14. 果可以确认合并的两个结果集中不包含重复的数据,用 union all 而不是 union
  15. 程序应有捕获 SQL 异常的处理机制
  16. 不使用select * ,SELECT语句只获取需要的字段
  17. UPDATE、DELETE语句不使用LIMIT
  18. INSERT语句必须显式的指明字段名称,不使用INSERT INTO table()
  19. INSERT语句使用batch提交(INSERT INTO table VALUES(),(),()……),values的个数不超过500
  20. 统计表中记录数时使用COUNT(*),而不是COUNT(primary_key)和COUNT(1) 备注:仅针对Myisam
  21. 数据更新建议使用二级索引先查询出主键,再根据主键进行数据更新
  22. 禁止使用跨库查询
  23. 禁止使用子查询,建议将子查询转换成关联查询
  24. 针对varchar类型字段的程序处理,请验证用户输入,不要超出其预设的长度;

REF

评论