MYSQL-生产环境中的数据库的表和规范

01、目标

了解和掌握生产环境中的数据库的表和规范

02、分析

基础规范

• 表必须要有主键，建议使用整型作为主键
• 禁止使用外键，表之间的关联性和完整性通过应用程序代码去维护和控制
• 表在设计之初、硬考虑到大致的数量级。若表记录数低于1000W、尽量使用单表，不建议分表。（千万和亿级别可以考虑分区即可。更大才考虑分库分表）。
• 建议将大字段，访问频率低及不需要筛选的字段拆分到拓展表中（做好垂直拆分）（比如：商品的详情）
• 控制单实例中表分区的总数，单个表分表的个数控制在1024以内。

列设计规则

• 合理使用字段类型，比如：
• 正确区分tinyint（byte,boolean）、int、bigint的范围
• 使用varchar(20)来存储手机号码，不要使用整数
• 设计金额使用decimal或者varchar、并指定精度
• 如果价格不作为查询字段，可以考虑使用varchar存储
• 使用字段应该如果你设定的时候能不为null就尽量设置成not null。(表设计是：null 但是：程序一定要给以默认值：int -1 String “”)
• 因为，null需要更多的空间存储，并且使得索引和统计变得更加复杂，可能会造成索引失效的问题。

索引规范

• 唯一索引使用uniq_索引名称来命名
• 非唯一索引使用idx_索引名称来命名
• 不建议在频繁更新的字段上建立索引
• 非必要不要进行join查询，如果要进行join查询，被join的字段必须类型相同，并建立索引
• 单张表索引数量建议控制在5个以内，索引过多，不仅会导致插入，更新的额性能下降，还可能导致MYSQL使用错误的索引，可在语句中加上force index来强制使用某个索引。
• 组合索引字段数不建议超过5个，理解组合索引最左前缀原则，避免重复建设索引，如果建立了（x,y,z），相当于建立了（x)，(x,y)，(x,y,z)。

SQL规范

• 禁止 select * ，只获取必要字段，select *会增加cpu/io/内存/带宽的消耗。指定字段能有效利用索引覆盖指定字段查询，也可以达到，在表结构变更时，能保证对应用程序无影响。
• insert必须指定字段，禁止使用insert into T values（），指定字段插入，在表结构变更时，能保证对应用程序无影响。
• 隐式类型转换会是索引失效，导致回表3扫描（当然在不同MYSQL版本可能做出了优化，但是按照规范去开发还是非常必要的）
• 禁止在where条件列使用函数或者表达式，导致不能命中索引，全表扫描。
• 禁止负向查询（不等于）以及%开头的模糊查询，导致不能命中索引而造成全表查询。
• 避免直接返回大结果集造成内存溢出。可采用分段和游标方式。（就是采用分页来处理或者分段来查询）
• 返回结果集时，尽量使用limit分页显示
• 避免出现较大的limit和offset的值
• 使用group by或者order by的语句，即使用了limit offset。如果没有合适的索引做排序操作，也会遍历所有满足 where条件的结果。
• 大表扫码操作尽量房到镜像库上做。
• 禁止大表join和子查询
• 尽量避免用or而用in去替换操作
• 应用程序必须捕获SQL异常，方便定位线上问题。

对表进行垂直拆分&水平拆分

垂直拆分：按照业务模块拆分：商品库、用户库、订单库。
水平拆分：对表进行拆分，分表，分区，比如：用户表1 用户表2
表垂直拆分：将一个属性较多的表，一行数据较大的表，将不同的属性拆分到不同的数据库表中，以降低单库中表的大小。

• 每个表的结构不一致
• 表之间有至少一列的关联数据，一般是主键
• 所有表的并集是全量数据
• 表的字段很多、字段的使用频率不一，考虑拆表（主表和明细表）

原则

• 将长度较短，访问频率较高的放入一张表，比如：主表
• 将长度较长、访问频率较低的字段放入拓展表中。
• 将经常一起访问的字段放入一张表中