设计数据库表的思路

设计数据库表实际上可理解为如何对实体对象进行管理，实体对象之间又有怎样的关联关系；话又说回来，实体对象从哪里来？这就要看具体的业务，业务又从哪里来？从项目最开始的阶段，需求分析；

注意表字段的类型

  1. 唯一主键随着系统越做越大，id 会越来越多，所以它的类型应设置为bigint，也可以示long，长度设置为 20
  2. 像标识一些类型、状态等字段，类型应设置为tinyint，长度设置为 1，默认值 0
  3. 注意一些时间字段的类型，根据具体的需求设置，一般设置为datetime，精确到时分秒
  4. 普通varchar类型的字段建议设置默认值为 null（empty string）
  5. 手机号等定长字段可设置为char类型，节省空间

  6. 对于内容比较多的字段应设置为text类型

    表与表的关联关系

  7. 一对多：一般上来说都是主从表，从表关联主表的主键即可；对于一些特殊的需求，必要的时候可加一些冗余字段

  8. 多对多：必须建立关联表，把两张甚至多张表关联起来
  9. 对于一些核心的业务来说，es：订单、支付、第三方对接…必要时候加一些日志表，方便问题追溯

    大数据量表设计

    再以上两点的基础上考虑，随着系统运行时间的增长，数据量也在不断的增长，所导致原本很快的一些 sql 语句在现阶段执行就会很慢，除开 sql 优化，还可以从哪些地方着手优化？

    主从复制读写分离

    写操作走主库，读操作走从库；目的降低主库的压力；

    但还是无法解决单表数据量过大的问题

分库分表

垂直切分

垂直分库

根据业务耦合性，将关联度低的不同表存储不同的数据库；类似于微服务治理，一个微服务单独使用一个数据库；

垂直分表

把一个表的多个字段分别拆分成多个表，一般按字段的冷热拆分，热字段一个表，冷字段一个表，从而提升数据库的性能；

可酌情根据业务需求来拆分表字段

优缺点

  1. 优点：
    1. 解决业务层面的耦合
    2. 可对不同业务的数据进行分级管理
    3. 高并发下一定程度上提升 IO、数据库连接数等资源的瓶颈
  2. 缺点：
    1. 分库后无法连接查询，只能通过接口聚合方式解决
    2. 分布式事务处理复杂
    3. 依然存在单表数据量较大的问题

      水平切分

      水平分库

      一般 mysql 单机也就 1000 左右的 QPS，若超过 1000则需要考虑分库；

      水平分表

      一般一张表的数据超过1000w，就需要考虑分表；

常见数据分片规则：

  • Hash取模分表：类似 Hash 定位，很好理解
    • 优点：数据分片比较均匀，不容易出现热点和并发访问的瓶颈
    • 缺点：集群扩容需要迁移旧数据很难；面临分片查询的复杂问题
  • 数值Range分表：按照时间区间或 ID 区间来切分
    • 优点：单表大小可控；天然便于水平扩展；使用分片字段进行范围查询时，连续分片可快速定位分片进行快速查询；
    • 缺点：热点数据成为性能瓶颈
  • 一致性 Hash 算法分表

    优缺点

  1. 优点：
    1. 不存在但数据量较大，提升系统稳定性和负载能力
    2. 应用改造小，不需要拆分业务模块
  2. 缺点：
    1. 跨分片的事务一致性难以保证
    2. 跨库的联合查询性能较差
    3. 数据多次扩展难度和维护量极大