Mysql (3)

数据库表设计规范

数据库命名规范

• 所有的数据库对象名称必须使用小写字母并用下划线表示，因为默认情况下，mysql 对大小写敏感，mysql 数据库本质上是 linux 系统下的一个文件，而 linux 系统是大小写敏感的
• 所有数据库对象名称禁止使用 mysql 保留关键字
• 数据库对象的命名要能做到见名知意，并且最好不要超过 32 个字符。太长不方便使用，并且会在传输时增加网络开销
• 临时表必须以 tmp_ 为前缀并以 日期 为后缀
• 备份表必须以 bak_ 为前缀并以 日期 为后缀
• 所有存储相同数据的列名和列类型必须一致，比如 user 表中的 user_id 和 order 表中的 user_id

数据库基本设计规范

• 所有表必须使用 Innodb存储引擎
Innodb引擎 是 5.6 之后的默认存储引擎； mysql5.5 之前使用 Myisam(默认存储引擎)
  Innodb引擎 优点：支持事务，行级锁，更好的恢复性，高并发下性能更好

• 数据库 和 表 的字符集统一使用 utf8mb4

• 数据库，表，字段 字符集一定要统一，统一字符集可以避免由于字符集转换产生的乱码

• 所有 表 和 字段 都需要添加 注释

• 禁止在数据库中存储图片，文件等二级制数据

索引设计规范(Innodb中主键实质上是一个索引)

• 限制每张表上索引数量，建议单表 不超过5个 索引。索引并不是越多越好，可以提高查询效率，但是会降低插入和更新的效率。甚至在一些情况下，还会降低查询效率，因为 mysql 优化器在选择如何优化查询时，会根据统计信息，对每一个可用索引来进行评估，以生成一个最好的执行计划，如果同时有很多索引都可以用于查询，就会增加 mysql 查询优化器生成查询计划的时间

• 每个 Innodb 表都必须有一个主键。Innodb 是一种索引索引组织表，是指数据存储的逻辑顺序和索引的顺序是相同，Innodb 是按照主键索引的顺序来组织表的，因此，每个 Innodb 表都必须要有一个主键，如果我们没有指定主键，那么 Innodb 会优先选择表中第一个非空唯一索引来作为主键，如果没有这个索引，那么 Innodb 会自动生成一个占6字节的主键，而这个主键的性能并不是最好

• 不使用更新频繁的列作为主键，不使用多列联合主键。因为Innodb是一种索引组织表，如果主键上的值频繁更新，就意味着数据存储的逻辑顺序频繁变动，必然会带来大量的IO操作，降低数据库性能

• 不要使用 uuid，md5，hash，字符串 列作为主键。因为这种主键不能保证主键的值是顺序增长的，如果后来的主键值在已有主键值的中间段，那么这个主键插入的时候，会将所有主键值大于它的列都向后移

• 最好选择能保证值的顺序为顺序增长的列为主键。并且数据不能重复，建议用mysql自增id建立主键
  在 select，delete，update 的 where 从句中的列
  包含在 order by，group by，distinct 字段中的列

• 多表 join的关联列：mysql 对关联操作的处理方式只有一种，那就是嵌套循环的关联方式，所以这种操作的性能对关联列上的索引的依赖性很大

• 复合索引：从左到右的顺序来使用
  区分度(列中 group by 的数目和此列总行数的比值趋近于1)最高的列放在联合索引的最左侧
  在区分度差不多的情况下，尽量吧字段长度小的放在联合索引的最左侧，因为同样的行数，字段小的文件也小，读取时IO性能更优
  使用最频繁的列放在联合索引的左侧，这样的话，可以较少地建立索引就能满足需求

• 避免建立冗余索引和重复索引

• 对于频繁的查询优先使用覆盖索引，就是包含了所有查询字段的索引，这样可以避免Innodb表进行索引的二次查找，并可以把随机IO变为顺序IO提高查询效率

• 尽量避免使用外键，mysql和别的数据库不同，会自动在外键上建立索引，会降低数据库的写性能

• 建议不使用外键约束，但是一定要在表与表之间的关联键上建立索引，虽然外键是为了保证数据的完整性，但是最好在代码中去保证

字段设计规范

• 优先选择符合存储需要的最小的数据类型

• 尽量将字符串转化为数字类型存储：如将 ip 存储为数字：inet_aton('255.255.255.255') = 4294967295 ,反之， inet_ntoa(4294967295) = '255.255.255.255'

• 对于非负整型数据，优先使用无符号整型来存储，如：id,age,无符号相对于有符号，可以多出一倍的存储空间

• mysql中，varchar(n) 中n表示字符数而不是字节数

• 避免使用 text，blog 来存储字段，这种类型只能使用前缀索引，如果非要使用，建议将这种数据分离到单独的拓展表中

• 避免使用 enum 类型。枚举本身是一个字符串类型，但是内部确是用正数类型来存储的，所以最多可存储 65535种不同的值，修改的话必须使用alter语句，直接修改元数据，有操作风险；order by 效率低，必须转换并无法使用索引，禁止使用数值作为 enum 值，因为 enum 本身是索引顺序存储的，会造成逻辑混淆

• 尽可能把所有列定义为 not null ，使用默认值，索引null 列需要额外的空间来保存，占更多空间，进行比较和计算时，对 null 值作特别的处理，可能造成索引失效

• 禁止使用字符串来存储日期型数据，可以使用 datetime 或者 bigint 类型，无法使用日期函数计算，比较字符串存储要占更多的内存空间，datetime(8字节)和timestamp(本身是以int存储)

• 财务相关数据，使用decimal类型 (精准浮点类型，在计算时不丢失精度)。

SQL开发规范

• 避免数据类型的隐式转换，一般出现在where从句中，会导致索引失效，如：select id,name from user where id = 12 禁止 select id,name from user where id = '12'

• 充分利用已存在的索引，避免使用 双% 的查询条件，不走索引， 可以使用 select id,name from user where name = '测试%' 作为查询

• 一个 SQL 只能利用到 复合索引 中的一列进行范围查询

• 使用 left join 或 not exists 来优化 not in 操作

• 禁止使用 select * 来查询，必须用字段名

• 数据库更适合处理批量操作，合并多个相同的操作到一起，提高处理效率

• 使用 in 代替 or，in 的值不要超过 500个， in 操作可以有效利用索引

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

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

本文由 feng 创作，采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为:2021-02-20 00:00:00