MySQL最佳实践

一、MySQL建表

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 '错误信息',
`Fdetail` text COMMENT '明细', // 基于领域驱动的值对象设计，内部可定义子流程状态，已执行的子节点禁止重复执行，失败的节点从当前开始重复执行
`Fstart_time` datetime COMMENT '任务开始时间',
`Fend_time` datetime COMMENT '任务结束时间',
`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` (

`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 '响应报文',

`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、主表任何状态变更，均记载日志

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地址访问	主从切换、选举机制