MySQL数据库开发规范知识点

前言：

设计规范更多的是为了确保数据库设计的合理性、为了项目最终的协调稳定性，而命名规范则更多的是为了确保设计的正式和统一。

约定优先于配置（Convention Over Configuration）。

我们希望团队中所有人看到设计成果，一眼就可以明白这个字段是做什么的、代表的含义是什么，可以但不止于见名知意。再者，当前的开发模式，前后端代码及数据库文档、程序文档、接口文档等等大都是由工具生成，而其最底层的依据就是数据库，表、字段的命名注释同时会影响到工具生成的文档、代码中的类属性方法甚至是前台页面的命名注释，数据库设计命名的规范关系到整个项目的规范。

表名，字段名等是否区分大小写：

我们在基本规范中建议，MySQL数据库、表、字段等名称统一使用小写，单词间用_下划线分隔。同时，我们建议在MySQL数据库中将Character Set设置为utf8、将Collation设置为utf8_bin，并在数据库配置文件中设置lower_case_table_names=1，当然，Windows系统中默认就是此种设置，无需再做更改。

我们建议在SQLServer中将排序规则设置为Chinese_PRC_CS_AS，其默认为Chinese_PRC_CI_AS，因为SQLServer数据库不用考虑部署在不同系统的问题，所以不建议更改除此外的其它编码、字符序相关的默认设置。我们上面也说过SQLServer虽然在执行SQL查询时不区分表名、列名大小写，但在命名及在可视化管理工具中显示时却又区分大小写，为了查看方便所以我们在“基本规范”中要求SQLServer用Pascal的命名方式。

数据库功能块描述：

视图加前缀是为了在执行查询时和表区分开，而存储过程、函数、约束等，我们一眼即可看出它是什么，更何况在可视化管理工具中，这些功能块本来就是各自独立展示的。所以本规范中不强制要求在这些功能上加前缀，但如果要统一加的话，建议使用上图表格中的英文缩写。

 

 

通用字段建议，仅参考：

 

 

 

 

数据库开发规范知识点

目录：

• 命名规范
• 基本设计规范
• 索引设计规范
• 字段设计规范
• SQL开发规范
• 操作行为规范

 

命名规范

• 对象名称使用小写字母并用下划线分割
• 禁止使用MySQL保留关键字
• 见名识义，最好不超过32个字符。例如：用户数据库，mc_userdb（公司+user+db），用户表，user_account
• 临时表，以tmp为前缀，以日期为后缀
• 备份表，以bak为前缀，以日期为后缀

• 存储相同数据的列名和列类型必须一致（关联列类型相同用索引高效）

  
  
   

  

   

基本设计规范

• 所有表用Innodb存储引擎
  • 支持事务
  • 行级锁
  • 更好的恢复性
  • 高并发的性能更好
• 数据库和表的字符集统一使用UTF-8，统一可避免乱码
• 所有表和字段添加注释（COMMENT从句）
• 尽量控制单表数据量，建议控制在500万以内。（不是MySQL的限制，是经验值）
  • 处理方式：历史数据归档、分库分表
• 谨慎使用MySQL分区表
  • 分区表：在物理上表现为多个文件，在逻辑上表现为一个表
  • 问题：谨慎选择分区键，跨分区查询效率可能更低
  • 建议：采用物理分表的方式管理大数据
• 尽量做到冷热数据分离，减小表的宽度
  • 限制：一个表最多4096列
  • 减少磁盘IO，保证热数据的内存缓存命中率
  • 利用更有效的缓存，避免读入无用的冷数据
  • 建议：经常一起用的列放在一个表中
• 禁止在表中建预留字段
  • 无法见名识义
  • 无法确定数据类型
  • MySQL修改预留字段比增加还麻烦，涉及对表的锁定
• 禁止在数据库中存储图片，文件等二进制数据
• 禁止在线上做数据库压力测试
• 禁止从开发或测试环境直接连接生产环境数据库

 

索引设计规范

• 限制每张表的索引数量，建议单表索引不超过5个
  • 索引增加查询效率，但是降低插入和更新效率
  • 禁止给每一列都建立单独的索引
• 每个Innodb表必须有一个主键
  • 不使用更新频繁的列作为主键，不使用多列主键，因为更新后就涉及对索引顺序的修改，频繁更新会导致频繁调整，导致降低性能
  • 不使用UUID，md5，hash字符串作为主键，因为这类哈希不保证插入时递增的特性
  • 建议：使用自增ID值
• 在哪建立索引？
  • 在SELECT, UPDATE, DELETE语句中的WHERE从句中的常出现的列
  • 在ORDER BY, GROUP BY, DISTINCT中的字段
  • 多表JOIN的关联列
• 建立索引的顺序？
  • 区分度最高的列放在联合索引的最左侧。区分度计算：Selectivity = Distinct Values / Total Number Rows，区分度最大的就是主键（区分度为1）
    • 区分度差不多的情况下，尽量把字段长度小的列放在联合索引的最左边
      • 两者还差不多的情况下，使用最频繁的列放在联合索引的左侧
• 避免建立冗余索引和重复索引
  • 重复索引例子：primary key(id), index(id), unique index(id)这三个就重复建立id的索引了
  • 冗余索引例子：index(a,b,c), index(a,b), index(a)对于a来说就重复建立了。
• 对于频繁的查询优先考虑使用覆盖索引。
  • 覆盖索引：包含了所有查询字段的索引
  • 避免Innodb表进行索引的二次查找
  • 可以把随机IO变为顺序IO加快查询效率
• 尽量避免使用外键
  • 外键是用于保证数据的参照完整性，但建议在业务端实现。
  • MySQL外键会建立索引
  • 不建议使用外键约束
  • 表与表之间的关联键建立索引是必须的
  • 外键会影响父表和子表的写操作而降低性能（检查约束导致的）

字段设计规范

• 优先选择符合存储需要的最小的数据类型
  • 将字符串转化为数字类型存储
  • 非负的用无符号整形
  • VARCHAR(N)的N代表是字符数，不是字节数，使用UTF-8存储汉字VARCHAR(255)=765个字节
  • 过大的长度会消耗更多的内存
• 避免使用TEXT、BLOB数据类型
• 避免使用ENUM数据类型
  • 修改ENUM值会导致表结构的修改
  • ENUM的ORDER BY需要额外操作，效率低
  • 禁止使用数值作为ENUM的枚举值
• 尽可能把所有列定义为NOT NULL
  • 索引NULL列需要额外空间，占用更多空间
• 使用TIMESTAMP或DATETIME类型存储时间。不要用字符串存储日期类型（无法利用内置日期函数而且占用更多空间）
• 涉及财务的金额，必须用DECIMAL类型
  • 精确浮点，计算不会丢失精度
  • 占用空间由定义的宽度决定
  • 可用于存储比BIGINT更大的整数数据

SQL开发规范

• 建议使用预编译语句进行数据库操作
  • 只传参数，多次使用，执行更快
  • 可避免动态SQL注入问题
• 避免数据类型的隐式转换
  • 会导致索引失效
• 合理利用存在索引，而不是盲目增加索引
• 充分利用表上已经存在的索引
  • 避免使用双%号的查询条件，如a LIKE '%123%'
  • 一个SQL只能利用到复合索引中的一列进行范围查询
  • 使用LEFT JOIN 或 NOT EXISTS 来优NOT IN操作（可能导致索引失效）
• 禁止跨库查询，程序连接不同的数据库使用不同的账号
  • 为数据库迁移和分库分表留出余地
  • 降低业务耦合度
  • 避免安全风险
• 禁止使用SELECT *，需要用SELECT <字段列表>查询
  • *返回不需要的字段
  • 无法使用覆盖索引
• 禁止使用不含字段列表的INSERT语句
  • 禁止这种INSERT INTO t VALUES('a','b','c')应该带上INSERT INTO t(c1,c2,c3) VALUES('a','b','c')
• 避免使用子查询，可以把子查询优化为JOIN操作
  • 子查询的结果集无法使用索引
  • 子查询会产生临时表操作，如果子查询数据量大则严重影响效率
• 避免使用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
  • 一个SQL只用一个CPU计算
  • 拆分后可以并行执行

操作行为规范

• 超过100万行的批量写操作，要分批多次进行操作
  • 注意之前提到的尽量合并操作是针对查询
  • 大批量的可能会造成主从延迟
  • binlog日志为row格式时会产生大量的日志
  • 避免产生大事务操作，造成锁定和大堵塞。
• 对大表数据结构的修改一定要谨慎，会造成严重的锁表操作。尤其是生产环境，是不能忍受的。
• 对于大表使用pt-online-schema-change（PERCONA公司的工具）修改表结构
  • 复制出一个新表，再修改新表为原表名称
  • 避免主从延迟
  • 避免修改时的锁表
• 禁止程序使用super权限的账号
  • super可以在达到最大连接限制连上用户，但是只能有一个super账号连接，应该交给DBA处理问题用，不应被程序占用
• 数据库账号遵循权限最小原则
  • 只需要查询就别给其他操作权限
  • 数据库账号只能在同一个DB下使用，不允许跨库
  • 程序的账号原则上不允许有drop权限

欢迎fork ：一只阿木木