Mysql数据库设计规范

mysql数据库设计规范

命令规范

  • 所有数据库对象的名称必须使用小写字母并用下划线分割
  • 所有 数据对象的名称禁止使用mysql保留的关键字
  • 关键字: https://dev.mysql.com/doc/refman/5.7/en/keywords.html
  • 数据酷对象的命令要做到见名识义，并且最好不要超过32个字符 如用户数据库：rjl_userdb ；用户账号表 user_account
  • 临时表必须以 tmp 为前缀并以日期为后缀
  • 备份库，备份表必须以 bak 为前缀并以日期作为后缀
  • 所有存储相同数据的列名和列类型必须一致，通常作为关联的列来使用的，对数据的查询性能时很重要的。

基本设计规范

  • 没有特殊的需求，一般必须使用 Innodb 存储引擎，5.6 以后默认引擎，支持事物，行级锁，更好的恢复性，高并发下性能更好。
  • 数据库和表的字符集统一使用 UTF8,兼容性更好，统一字符集可以避免由于字符集转换产生乱码，mysql中utf8字符集汉子占用3字节，ascii占用1个字节
  • 所有的表和字段都必须添加注释，使用 comment添加
  • 尽量控制单表数据量的大小，建议控制在500万以内，可以用历史数据归档，分库分表等手段来控制数据量的大小。
  • 谨慎使用mysql分区表，分表表在物理上表现为多个文件，在逻辑上表现为一个表，谨慎选择分区键，跨分区查询销量可能更低，建议采用物理分表的方式管理大数据
  • 尽量做到冷热数据分离，减少表的宽度，减少磁盘IO,保证热数据的内存命中率；利用更有效的利用缓存，避免读入无用的冷数据；经常使用的放到一张表中。
  • 禁止在表中建立预留字段
  • 禁止在数据库中存储图片，文件，二进制数据
  • 禁止在线上数据库做压力测试
  • 禁止从开发环境，测试环境直连生产环境数据库
  • 索引设计规范
  • 每个Innodb表都要有一个主键
  • 限制每张表上的索引数量，建议单表索引不超过 5 个。，增加查询效率，同样降低插入和更新的效率，禁止给每张表每一列都建立单独的索引。
  • 每个Innodb表都必须有一个主键,不能使用更新频繁的列作为主键，不使用多列主键；不使用 UUID，MD5，HADH，字符串列作为主键；主键建议使用自增ID值。
  • 常见索引列建议：在 select ,update,delete 语句的 where 语句中的列，包含 order by,group by,distinct中的字段；在多表 join的关联列
  • 索引列的顺序：区分度最高的列放在联合索引的最左侧；尽量吧字段长度小的列放在联合索引的最左侧；使用最频繁的列放到联合索引的左侧
  • 避免建立冗余的索引和重复的索引
  • 对于频繁的查询有限考虑使用覆盖索引：就是包含所有查询字段的索引
  • 尽量避免使用外键:不建议使用外键约束，但一定在表与表之间的关联键上建立索引；外键可用于保证数据的参照的完整性，但建议在业务端实现；外键会影响父表和子表的写操作从而降低性能。

字段设计规范

  • 优先选择符合存储需要的最小的数据类型，如将字符串转换为数字类型存储，使用 INET_ATION ,INET_NTOA ，将ip地址转换为整数

    • 对于非负型数据来说，要优先使用无符号整型来存储
    • VARCHAR(N)中的N代表的是字符数，而不是字节数
    • UTF-8存储的汉字 一个字符需要暂用三个字节， Varchar(255) = 765个字节
    • 过大的长度会消耗更多的内存

  • 避免使用 TEXT,BLOG数据类型

    • 建议把 blob或是 text列分离到单独的扩招表中
    • text 或者 blob 类型，只能使用前缀索引

  • 避免使用 ENUM 数据类型

    • 修改ENUM值需要使用 alter语句
    • enum 类型的orderby操作效率低，也需要额外的操作。
    • 禁止使用数据值作为 enum 的枚举值

  • 尽可能把所有的列定义为 Not null

    • 索引null列需要额外的空间来保存，所以要占用更多的空间
    • 进行比较和计算时要对null值做特别的处理

  • 不能用字符串存储日期数据，使用 TIMESTAMP 或 DATETIME 类型存储时间

    • 无法用日期函数进行计算和比较
    • 用字符串存储日期需要占用更多的空间

  • 同财务相关类的金额类数据，必须使用 decimal 类型

    • Decimal 类型为精准浮点数，在计算时不会丢失精度
    • 占用空间由定义的宽度决定
    • 可用于存储比 bigint 更大的整数数据

SQL开发规范

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

  • 避免数据类型的隐式转换

  • 合理利用存在的索引，而不是盲目的增加索引

    • 避免使用双 % 号查询条件，如 a like '%123%'
    • 一个 sql 只能利用到复合索引中的一列进行范围查询
    • 使用 left join 或者 not exists 来优化 not in 操作

  • 程序连接不同的数据使用不同的账号，禁止跨库查询

  • 禁止使用 select * ，必须使用 select <字段列表> 查询

    • 消耗更多的cpu和io以及网络资源
    • 无法使用覆盖索引
    • 减少表结构变更带来的影响

  • 避免使用子查询，可以把子查询优化为 join操作

    • 子查询的结果集无法使用索引
    • 子查询会产生临时表的操作，如果子查询数据量大则严重影响效率
    • 消耗过多的cpu及io资源

  • 避免使用 join 关联太多的表

    • 每join一个表会多占用一部分的内存 join_buffer_size
    • 会产生临时表，影响查询效率
    • mysql 最多允许关联61个表，建议不超过5个

  • 减少同数据库交互的次数

  • 使用 in 代替 or

  • 禁止使用 order by rand() 进行随机排序

  • where 从句中禁止对列进行函数转换和计算

  • 在明显不会有重复值时使用 uniion all 而不是 union

  • 拆分复杂的大 sql 为多个 小 sql

操作行为规范

  • 超过100万行的批量写操作，要分批次多次进行操作
  • 对大表数据结构的修改一定要谨慎，会造成严重的锁表操作尤其时生产环境。
  • 对于大表的使用 pt-online-schema-change修改表结构
  • 禁止为程序使用的账号赋予super权限
  • 对于程序链接数据库账号，遵循权限最小的原则
posted @ 2018-09-26 15:24  NiRao  阅读(193)  评论(0)  编辑  收藏  举报