mysql merge 引擎分表

淩亂°似流年 2021-12-14 16:04 277阅读 0赞

# 业务场景介绍 #

项目中需要记录全量（新增，编辑，删除，查询都需要记录变更项已经标题ID等）日志，要记录的特别的详细，内容就会有点多，如果我们只用一张表来进行存储的话，要不了多久我们的表就会被撑爆，会影响效率，特别是查询。为了保障效率，我们就需要做优化，那么我们想到了分表来存储我们的日志。

# 分表的实现 #

那么怎么来实现呢，最起码我们要新建一张子表，还有一张主表。  
在实现之前我们先确定好我们的分表策略，如果我们用时间来进行分表，目前业务刚刚开始，没有人知道以后会是什么情况，多少人会使用，使用的频率高不高，每天大概会产生多少数据，我们都没法预知，那么我们干脆一点，不按照时间了，我们的想法是直接按照数据量来进行分表，比如子表的数据量达到了100万（具体值可以调整）就新建一张新的子表。

## 子表的创建 ##

策略定好了我们就开始创建了

CREATE TABLE `sys_full_dose_log_1` (
      `id` bigint(20) NOT NULL COMMENT '主键（雪花串）',
      `user_id` varchar(55) DEFAULT NULL COMMENT '操作用户编号',
      `user_name` varchar(50) DEFAULT NULL COMMENT '操作用户名称',
      `operate_at` datetime DEFAULT NULL COMMENT '操作时间',
      `old_data` text COMMENT '修改之前的json值',
      `new_data` text COMMENT '修改之后的json值',
      `route` varchar(120) DEFAULT NULL COMMENT '路径',
      `operate_type` varchar(100) DEFAULT NULL COMMENT '操作类型',
      `rdescribe` varchar(255) DEFAULT NULL COMMENT '操作描述',
      `ip` varchar(30) DEFAULT NULL COMMENT '操作IP',
      `business` varchar(255) DEFAULT NULL COMMENT '业务名称',
      `num` int(11) DEFAULT NULL COMMENT '数量',
      `entry_key` varchar(50) DEFAULT NULL COMMENT '条目名',
      `entry_value` varchar(50) DEFAULT NULL COMMENT '条目值',
      `entry_id` varchar(55) DEFAULT NULL COMMENT '条目id',
      `filed_type` varchar(30) DEFAULT NULL COMMENT '字段类型',
      `is_success` tinyint(4) DEFAULT '1' COMMENT '操作结果：1成功；0失败',
      `log_type` int(11) DEFAULT NULL COMMENT '日志类型：1登录/退出；2部门日志；3用户日志；4权限日志；5角色日志',
      `reason` text COMMENT '执行失败原因',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='全量日志表';

这里存储引擎一定要选择MyISAM。

## 主表的创建 ##

CREATE TABLE `sys_full_dose_log_body` (
      `id` bigint(20) NOT NULL COMMENT '主键（雪花串）',
      `user_id` varchar(55) DEFAULT NULL COMMENT '操作用户编号',
      `user_name` varchar(50) DEFAULT NULL COMMENT '操作用户名称',
      `operate_at` datetime DEFAULT NULL COMMENT '操作时间',
      `old_data` text COMMENT '修改之前的json值',
      `new_data` text COMMENT '修改之后的json值',
      `route` varchar(120) DEFAULT NULL COMMENT '路径',
      `operate_type` varchar(100) DEFAULT NULL COMMENT '操作类型',
      `rdescribe` varchar(255) DEFAULT NULL COMMENT '操作描述',
      `ip` varchar(30) DEFAULT NULL COMMENT '操作IP',
      `business` varchar(255) DEFAULT NULL COMMENT '业务名称',
      `num` int(11) DEFAULT NULL COMMENT '数量',
      `entry_key` varchar(50) DEFAULT NULL COMMENT '条目名',
      `entry_value` varchar(50) DEFAULT NULL COMMENT '条目值',
      `entry_id` varchar(55) DEFAULT NULL COMMENT '条目id',
      `filed_type` varchar(30) DEFAULT NULL COMMENT '字段类型',
      `is_success` tinyint(4) DEFAULT '1' COMMENT '操作结果：1成功；0失败',
      `log_type` int(11) DEFAULT NULL COMMENT '日志类型：日志类型：1登录/退出；2部门日志；3用户日志；4权限日志；5角色日志',
      `reason` text COMMENT '执行失败原因',
      PRIMARY KEY (`id`)
    ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`sys_full_dose_log_1`) COMMENT='系统全量日志表';

这里主表的存储引擎必须是MRG\_MyISAM，如果你有多张子表，那么你可以在上面新增多张子表然后(`sys_full_dose_log_1`,`sys_full_dose_log_2`)用逗号分割

## 测试 ##

我们可以直接操作主表sys\_full\_dose\_log\_body，直接查询主表时，会自动匹配所有子表的数据。当我们直接插入主表的时候，默认会插入UNION里面的最后一张子表，当然我们也可以直接插入某一张子表。需要注意的是MRG\_MyISAM这个存储引擎是没有事务的，在程序中不要考虑回滚的事情。

### 插入 ###

插入主表

INSERT INTO `sys_full_dose_log_body`(`id`, `user_id`, `user_name`, `operate_at`, `old_data`, `new_data`, `route`, `operate_type`, `rdescribe`, `ip`, `business`, `num`, `entry_key`, `entry_value`, `entry_id`, `filed_type`, `is_success`, `log_type`, `reason`) VALUES (592739202418868224, NULL, NULL, '2019-07-03 16:33:58', NULL, NULL, '', '登录', '登录', '192.168.51.243', 'DEFAULT', 0, '', NULL, NULL, NULL, 0, 1, NULL);

插入子表

INSERT INTO `sys_full_dose_log_1`(`id`, `user_id`, `user_name`, `operate_at`, `old_data`, `new_data`, `route`, `operate_type`, `rdescribe`, `ip`, `business`, `num`, `entry_key`, `entry_value`, `entry_id`, `filed_type`, `is_success`, `log_type`, `reason`) VALUES (593082590901841920, NULL, '小的明', '2019-07-03 17:18:29', NULL, NULL, '安全-用户管理', '查看', '查看用户', '127.0.0.1', 'USER', 0, 'nickName', NULL, NULL, NULL, 1, 3, NULL);

### 查询 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6dzIzMTI_size_16_color_FFFFFF_t_70]  
这里大家可以多创建几张子表来进行测试，也可以在表中多插入一点数据来进行测试，然后在觉得要不要使用这种方法。

# 动态创建表 #

思路上面已经说了，当表的数量达到100万后我们就新增另一张子表来进行存储，下面给出动态创建子表的存储过程

## 动态建表存储过程 ##

CREATE DEFINER=`root`@`%` PROCEDURE `PROC_CREATE_LOG_TABLE`()
    BEGIN
    	/*
    	 * ===================新增日志子表存储过程====================
    	 */
    	 
    	#声明变量
    	
    	-- 当前最后一个子表的表名
    	DECLARE v_last_table_name VARCHAR(30);
    	-- 当前要新增的子表的后缀名
    	DECLARE v_last_table_count INT;
    	-- 当前子表最大id
    	DECLARE v_max_count BIGINT;
    	-- 当前子表最大id查询语句
    	DECLARE v_max_count_sql VARCHAR(300);
    	-- 当前子表集合字符串（用','分割）
    	DECLARE v_union_table TEXT;
    	-- 当前要创建的表名前缀
    	DECLARE v_create_table_name VARCHAR(50) DEFAULT 'sys_full_dose_log_';
    	-- 当前数据库名
    	DECLARE v_schema VARCHAR(50) DEFAULT 'ktwlsoft_framework_basic_pl';
    	
    	SELECT table_name INTO v_last_table_name FROM information_schema.TABLES WHERE table_schema = v_schema AND TABLE_NAME LIKE CONCAT(v_create_table_name,'%') AND ENGINE = 'MyISAM'
    ORDER BY create_time DESC LIMIT 1;
    	SELECT REVERSE(LEFT(REVERSE(v_last_table_name),INSTR(REVERSE(v_last_table_name),'_')-1)) + 1 INTO v_last_table_count; 
    	
    	# 需要声明全局变量(@...)来接受动态SQL的值
    	SET v_max_count_sql = CONCAT(' ', 'select count(id) into @max_count from ',v_last_table_name);
    	SET @sql = v_max_count_sql;
    	PREPARE s1 FROM @sql; 
    	EXECUTE s1; 
    	DEALLOCATE PREPARE s1;
    	SET v_max_count = @max_count;
    	SELECT v_max_count;
    	#当数据大于等于一百万，从新创建子表并将子表union到主表中
    	IF v_max_count >= 1000000 THEN
    		
    		#创建子表
    		SET @sql_create_table = CONCAT(
    		'CREATE TABLE IF NOT EXISTS ',v_create_table_name,v_last_table_count,'(
    			id bigint(20) comment \'主键（雪花串）\',
    			user_id varchar(55) comment \'操作用户编号\',
    			user_name varchar(50) comment \'操作用户名称\',
    			operate_at datetime comment \'操作时间\',
    			old_data text comment \'修改之前的json值\',
    			new_data text comment \'修改之后的json值\',
    			route	varchar(120) comment \'路径\',
    			operate_type varchar(100) comment \'操作类型\',
    			rdescribe varchar(255) comment \'操作描述\',
    			ip varchar(30) comment \'操作IP\',
    			business varchar(255) comment \'业务名称\',
    			num int comment \'数量\',
    			entry_key varchar(50) comment \'条目名\',
    			entry_value varchar(50) comment \'条目值\',
    			entry_id varchar(55) comment \'条目id\',
    			filed_type varchar(30) comment \'字段类型\',
    			is_success tinyint default 1 comment \'操作结果：1成功；0失败\',
    			log_type int comment \'日志类型：1登录/退出；2部门日志；3用户日志；4权限日志；5角色日志\',
    			reason text comment \'执行失败原因\',
    			PRIMARY KEY(id)
    			) comment \'全量日志表\' ENGINE = MYISAM DEFAULT CHARSET = utf8');
    		PREPARE sql_create_table FROM @sql_create_table;
    		EXECUTE sql_create_table;
    		DEALLOCATE PREPARE sql_create_table;
    		
    		#查询子表字符串集合
    		SELECT GROUP_CONCAT(table_name ORDER BY create_time ASC,',') INTO v_union_table FROM information_schema.TABLES WHERE table_schema = v_schema AND TABLE_NAME LIKE CONCAT(v_create_table_name,'%') AND ENGINE = 'MyISAM'; 
    		
    		SELECT v_union_table;
    		
    		#更新主表
    		SET @sql_alter_table = CONCAT('alter table ',v_create_table_name,'body UNION = (',v_union_table,')');
    		PREPARE sql_alter_table FROM @sql_alter_table;
    		EXECUTE sql_alter_table;
    		#释放执行中使用的所有数据库资源
    		DEALLOCATE PREPARE sql_alter_table;
    		
    	END IF;
    	
    END

## Mysql新建定时计划 ##

新建计划定时调用上面创建的存储过程

CREATE DEFINER=`root`@`%` EVENT `PLAN_CHECK_LOG_TABLE` 
    ON SCHEDULE EVERY 30 MINUTE 
    STARTS '开始检查时间（yyyy-MM-dd HH:mm:ss）' 
    ON COMPLETION PRESERVE ENABLE COMMENT '半小时检查一次日志子表是否需要新增' 
    DO 
    call PROC_CREATE_LOG_TABLE()

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6dzIzMTI_size_16_color_FFFFFF_t_70]: /images/20211214/f392f66b4b3146d0ad3ff02bac5eeaf7.png