数据库和索引设计

一. 表结构设计规范

1. 基础规范

* 表必须有主键, 建议使用整形作为主键
* 禁止使用外接, 表之间的关联性和完整性通过应用层来控制
* 表在设计之初, 应该考虑到大致的数据级,若表记录低于1000W，尽量使用单表，不建议分表
* 建议将大字段, 访问频率低, 或者不需要作为筛选条件的字段拆分到拓展表中, 做好垂直拆分
* 控制单实例表的总数, 单个表分表数控制在1024以内

2. 列设计规范

* 正确区分 tinyint, int , bigint的范围
* 使用 varchar(20) 存储手机号, 不要使用整数
* 涉及金额使用 decimal, 并制定精度
* 不要设计为null的字段, 而是空字符, 因为null需要更多的空间, 并且使得索引和统计变得更复杂

3. 索引规范

* 唯一索引使用uniq_[字段名]来命名
* 非唯一索引使用idx_[字段名]来命名
* 不建议在频繁更新的字段上建立索引
* 非必要 不要进行JOIN(联表操作), 如果要进行join查询,被join的字段必须类型相同, 并建立索引
* 单张表的索引数量建议控制在5个以内, 索引过多, 不仅会导致插入更新性能下降, 还可能导致MySQL的索引出错和性能下降
* 组合索引字段数量不建议超过5个, 理解组合索引的最左匹配原则, 避免重复建设索引. 比如建立(x,y,z) 相当于建立了(x),(x,y),(x,y,z)

二. SQL编写要求规范

* 禁止使用 select *  , 只获取必要字段, select 会增加 cpu/i0/内存,带宽的消耗
* insert必须知道字段, 禁止使用 insert into Table values(). 指定字段插入, 在表结果变更时, 能保证对应应用程序无影响.
* 隐式类型转换会使索引失效, 导致全表扫描. (如果存储手机号时为字符串类型,搜索时传的数字类型,则会进行隐式类型转换)
* 禁止在where后调用的列使用函数或者表达式, 导致不能命中索引, 导致全表扫描
* 禁止负向查询 (!=, not like, no in等) 以及% 开头的模糊查询, 熬制不能命中索引, 导致全表扫描
* 避免直接返回大结果集造成内存溢出, 采用分段和游标方式. (可利用子查询 优先去除一部分 不在范围内的数据)
* 返回结果集时, 尽量使用limit分页显示
* 尽量在order by/group by 的列上创建索引
* 大表扫描尽量放在镜像库上去做
* 禁止大表join查询和子查询
* 尽量避免数据库内置函数作为查询条件
* 应用程序尽量捕获SQL异常

三. 分库分表

1.为什么要分库分表
一般的机器(4C16G),单库的MySQL并发(QPS+TPS) 超过了2K,系统基本就完蛋了, 最好是并发量控制在1K左右

分库分表的目的: 解决高并发, 和数据量大的问题

高并发情况下, 会造成IO读写频繁, 自然就会造成读写缓慢, 甚至是宕机, 一般单库不要超过2K并发, NB的机器除外.
数据量大的问题, 主要由于底层索引实现导致, MySQL的索引实现为B+TREE, 数据量其他, 会导致索引树十分庞大, 造成查询缓慢. 第二, innodb的最大存储限制64TB.

要解决上述问题, 最常见做法,就是分库分表.
分库分表的目的, 是一个表拆成N个表, 就是让每个表的数据量控制在一定范围内, 保证SQL的性能, 一个表数据建议不超过500W.

分库分表方式

水平拆分: 就是分表,统一表的数据拆到不同的库不同的表中, 可以根据时间,地区,或某个业务键维度, 也可以通过hash进行拆分, 最后通过路由访问到具体的数据. 拆分后的每个表结构保持一致.
垂直拆分: 就是业务模块拆分,把一个有很多字段的表给拆分成多个表, 或者是多个库上去. 每个库表的结构都不一样, 每个库表都包含部分字段. 一般来说, 可以根据业务维度进行拆分, 如订单表可以拆分为订单, 订单支持, 订单商品, 订单扩展等表; 也可以根据冷热程度拆分, 20%的热点字段拆到一个表, 80%的冷字段拆到另外一个表.

将一个属性过多的表, 一行数据较大的表, 将不同的属性分割到不同的数据库表中, 以降低单库表的大小
特点:
- 每个表的结构不一致
- 每个表的数据都是全量
- 表和表之间一定会有一列进行关联, 一般都是主键
原则:
- 将当杜较短, 访问频率较高的字段放在一个表中, 主表
- 将长度较长, 访问频率比较低的字段放一个表中
- 将经常访问字段放一个表中
- 所有表的并集是全量数据

四. 如何平滑添加字段

场景: 开发时, 有时需要给表添加字段, 在大数据量且分表的情况下, 怎么平滑添加.

1. 直接alter Table add column ,数据量大时 不建议 (会产生死锁)
2. 提前预留字段(不优雅, 造成空间浪费, 预留多少很难控制, 拓展性差)
3. 新增一张表, 增加字段, 迁移原表数据, 在重新命名新表作为原表
4. 放入extinfo (扩展属性, 无法使用索引)
5. 提前设计, 使用 key/value 方式存储(比较JSON字符串), 新增字段时, 直接加一个key就好了(优雅)

五. 数据库事务

1.数据库事务需满足ACID 四个特性

原子性(Atomicity) 指事务作为整体来执行, 要么全执行, 要么全不执行
一致性(Consistency) 指事务应确保数据从一个一致的状态转变为另一个一致的状态
隔离性(Isolation) 指多个事务并发执行时,一个事务的执行不应影响其他事务的执行
持久性(Durability) 指已提交的事务修改数据会被持久保存

本地事务
不开启任何分布式事务管理的前提下,让每个数据节点各自管理自己的事务. 它们之间没有协调以及通信的能力, 也并不互相知晓其他数据节点事务成功与否.
本地事务在性能方面无任何损耗, 但在强一致性以及最终一致性方面则力不从心.

ShardingJDBC中添加注解:

@Transactional(rollbackFor = Exception.class)
@ShardingTransactionType(TransactionType.LOCAL)

两阶段提交
XA协议最早的分布式事务模型是由X/OPen国际联盟提出的X/Open Distributed Transaction Processing (DTP) 模型, 简称XA协议.
基于 XA 协议实现的分布式事务对业务侵入很小。它最大的优势就是对使用方透明，用户可以像使用本地事务一样使用基于 XA 协议的分布式事务。XA 协议能够严格保障事务 ACID 特性。

严格保障事务 ACID 特性是一把双刃剑。事务执行在过程中需要将所需资源全部锁定，它更加适用于执行时间确定的短事务。对于长事务来说，整个事务进行期间对数据的独占，将导致对热点数据依赖的业务系统并发性能衰退明显。因此，在高并发的性能至上场景中，基于 XA 协议两阶段提交类型的分布式事务并不是最佳选择。

ShardingJDBC中添加注解:

@Transactional(rollbackFor = Exception.class)
@ShardingTransactionType(TransactionType.XA)

柔性事务
如果将实现了 ACID 的事务要素的事务称为刚性事务的话，那么基于 BASE 事务要素的事务则称为柔性事务。BASE 是基本可用、柔性状态和最终一致性这 3 个要素的缩写。

在 ACID 事务中对一致性和隔离性的要求很高，在事务执行过程中，必须将所有的资源占用。柔性事务的理念则是通过业务逻辑将互斥锁操作从资源层面上移至业务层面。通过放宽对强一致性和隔离性的要求，只要求当整个事务最终结束的时候，数据是一致的。而在事务执行期间，任何读取操作得到的数据都有可能被改变。这种弱一致性的设计可以用来换取系统吞吐量的提升。Saga 和 TCC 都是典型的柔性事务实现方案。

基于 ACID 的两阶段事务和基于 BASE 的最终一致性事务都不是银弹，可通过下表详细对比它们之间的区别。

-	两阶段提交	柔性事务
业务改造	无	实现相关接口
一致性	支持	最终一致
隔离性	支持	业务方保证
并发性能	严重衰退	略微衰退
适合场景	短事务 & 低并发	长事务 & 高并发

六. ShardingJDBC 分表分库读写分离

ShardingJDBC 配置 yml:

spring:
  shardingsphere:
    # 参数配置,显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源起别名, 下面的ds1,ds2,ds3. 任意取名
      names: ds1,ds2,ds3
      # 给 ds1(master) 每个数据源配置数据库连接信息
      ds1:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1/test?characterEncoding=utf8&allowMultiQueries=true&useSSL=true&serverTimezone=GMT%2B8
        username: root
        password: 123456
      ds2:
        # 配置ds2(slave)
        type: com.alibaba.druid.pool.DruidDataSource
        .... (省略)
      ds3:
        # 配置ds3(slave)
        type: com.alibaba.druid.pool.DruidDataSource
        .... (省略)
      # 配置默认数据源ds1
      sharding:
        # 默认数据源, 主要用于写, 注意一定要配置读写分离, 注意: 如果不配置, 那么 永远只读写操作 ds1 ,就没有读写分离了
        default-data-source-name: ds1
      # 配置数据源的 读写分离, 但是数据库 一定要做主从复制
      masterslave:
        # 配置主从名称, 可以任意取名字
        name: ms
        # 配置主库master, 负责数据的写入
        master-data-source-name: ds1
        # 配置从库slave节点
        slave-data-source-names: ds2,ds3

参考学习视频: https://www.bilibili.com/video/BV1ei4y1K7dn?p=1

posted @ 2021-12-17 11:13 栋_RevoL 阅读(143) 评论(1) 编辑收藏举报

刷新页面返回顶部

栋_RevoL