MySQL最佳实践

一、MySQL建表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE `t_xxxxx` ( `Fid` bigint(255) NOT NULL AUTO_INCREMENT COMMENT '编号', `Fbuss_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '业务主键', `Fstatus` tinyint(4) DEFAULT '0' COMMENT '状态 //1、基于阶段定义 错位定义 10,20,30，如果后续新增11，12节点容易修改  2、状态机根据业务条件扭转，防止并发 `Ferror_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '错误码', `Ferror_msg` varchar(5000) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '错误信息',<br>`Fdetail` text COMMENT '明细', // 基于领域驱动的值对象设计，内部可定义子流程状态，已执行的子节点禁止重复执行，失败的节点从当前开始重复执行<br>`Fstart_time` datetime COMMENT '任务开始时间',<br>`Fend_time` datetime COMMENT '任务结束时间',<br>`Fretry_times` int COMMENt '任务最大重试次数',  //当子任务执行成功后，修改重试次数为0，便于后续子流程的重试次数从0开始 `Fcreator` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, `Fcreate_time` datetime DEFAULT CURRENT_TIMESTAMP, `Flast_operation_user` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, `Fmodify_time` datetime DEFAULT CURRENT_TIMESTAMP, `Fversion` int(11) DEFAULT NULL COMMENT '数据版本号', PRIMARY KEY (`Fid`), UNIQUE KEY `idx_bussiness_id` (`Fbussiness_id`) ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

1、业务主键 Fbuss_id：并发情况避免重复插入
2、版本号 Fversion: 并发情况基于乐观锁CAS，避免ABA重复更新问题
3、业务错误码 Ferror_code： 预留基于状态机中途特殊异常问题
4、分阶段状态 Fstatus: 流程特别长，记载每个阶段状态，可无限扩展
5、记载最后修改操作 Fmodify_time和Flast_operation_user

 

CREATE TABLE `t_xxxxx_log` (

?

1 2 3 4 5 6 7 8 9 10 11 12 13

<em id="__mceDel">`Fid` bigint(255) NOT NULL AUTO_INCREMENT COMMENT '编号', `Fxxx_id` bitint(255)  NOT NULL COMMENT '业务外键', `Fstatus` tinyint(4) DEFAULT '0' COMMENT '状态 //1、基于阶段定义 错位定义 10,20,30，如果后续新增11，12节点容易修改  2、状态机根据业务条件扭转，防止并发 `Frequest_msg` text COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '请求报文', `Fresponse_msg` text COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '响应报文',<br> `Fcreator` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, `Fcreate_time` datetime DEFAULT CURRENT_TIMESTAMP, `Flast_operation_user` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, `Fmodify_time` datetime DEFAULT CURRENT_TIMESTAMP, `Fversion` int(11) DEFAULT NULL COMMENT '数据版本号', PRIMARY KEY (`Fid`), UNIQUE KEY `idx_bussiness_id` (`Fbussiness_id`) ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;</em>

日志记载规范

1、主表任何状态变更，均记载日志

2、每次修改主表状态，均修改主表Fmodify_time

３、日志记载before和after信息，记载状态流变更

 

二、MySQL规范

1、show table status 'xxx表'
查看avg_row_length是否超过100字节，即为宽表
查看Date_free字段，如果字段>0，代表有磁盘碎片

2、单表记录<5kw条, 单行少于8k 8*1024
日新增<1w 单库单表
1w<日新增<10w db.t_table_yyyy 300*12=3600
10w<日新增<100w db.t_table_yyyymm 3000*12=36000
日新增>100w db.t_table_yyyymmdd
日新增记录数在1000W笔以上，且分布在不同的实例上 db_yyyymm_xx.t_table_dd_y
日新增记录数在1000W笔以上 db_yyyymm.t_table_dd_x
百库10表，总记录数在亿级别 db_xx.t_table_y

3、表规范 t_xxxx, 字段规范 F_xxxx
主键规范 pk_
唯一索引规范 uk_
其他索引 idx_
所有表字段加上Fcreate_time 和Fmodify_time字段
Fmodify_time datetime DEFAULT CURRENT_TIMESTAMP

4、金额字段 bigint
5、单表字段个数<100
6、避免text类型，如果需要放入另外张表
7、状态字段使用 unsigned tinyint
8、int类型主键使用 UNSIGNED
9、禁止字段defualt null, 字符串 DEFAULT ''
10、扩展字段 json类型

 

三、 MySQL防重幂等实践

1、 插入防重：通过获取流水号服务，获取流水号，流水号生成算法 雪花片算法

2、更新ABA问题

页面打开：保留V1版本号

更新事前检测：再次获取实体版本号Vn, 如果Vn=V1继续，否则刷新页面数据有变更

更新事中：update …. Set Fversion=Fversion+1 Where Fbuss_id=’xxxx’ and Fversion=V1

3、支付安全设计

 

a、先获取交易单号

   算法：Snowflake,Twitter分布式ID算法等

b、检验签名：防止入参内容被修改

c、业务有效性检验：from方转账余额后的总数不能<0

d、插入转账记录：

     幂等性保证：通过主键防重，避免重复转账

     对账：记载单笔转账记录的发起方、接收方和转账金额信息

     操作安全：只允许insert和select, 禁止update和delete, 如果要取消流水记录，也是新增一条取消流水记录

e、CAS更新：

    基于版本号，更新发起方和接受方

 

四、海量数据处理方案

冷热数据分离 -》 读流量分片 -》 写流量分片 -》 业务拆分

应用场景	解决方案	应用系统	数据库
高并发	水平扩展（复制）	多机集群，提升并发力	读写分离（将流量打到不同DB) 如：商品读库、商品写库
	垂直拆分（不同业务拆分）	按业务域划分系统 如：商品系统、交易系统	按业务分库 （将流量打到不同DB) 如：商品库、订单库
大数据	业务分片（同业务分片）	按功能点分开部署 如：秒杀系统	分库分表，提高数据容量 如：订单库按ID分库分表
	水平拆分（稳定与易变分离）	服务分层 功能与非功能分开	冷热数据分离、历史数据分离
HA可用性	主备自动选举机制	服务网关汇报心跳、统一nacos地址访问	主从切换、选举机制