通用分表存储过程
存储 过程 通用 分表
2023-09-14 08:59:48 时间
`syslog_id` int(11) NOT NULL AUTO_INCREMENT,
`create_user` varchar(32) DEFAULT NULL,
PRIMARY KEY (`syslog_id`)
;
call branch_table(test, @field_list, 4, 2);
IN p_table_name VARCHAR(200), IN p_field_list VARCHAR(2048), IN p_branch_size INT, in p_lpad INT BEGIN /*定义变量*/ DECLARE m_begin_row INT DEFAULT 0; WHILE m_begin_row p_branch_size DO /*构造语句*/ SET @MAIN_STRING = CONCAT(CREATE TABLE , p_table_name, _, LPAD(m_begin_row, p_lpad, 0), (, p_field_list ,)ENGINE=InnoDB DEFAULT CHARSET=utf8;); /*预处理*/ PREPARE main_stmt FROM @MAIN_STRING; EXECUTE main_stmt; SET m_begin_row=m_begin_row+1; END WHILE; END;
insert into table_new_0000 select * from table_old where mod(user_id,64)=0; insert into table_new_0001 select * from table_old where mod(user_id,64)=1;
一共64条sql,OK 搞定。但是这个一张表被全表扫描了64次,做的无用功比较多,而且导致停机时间比较长
declare c_ids cursor for select id,user_id from item_records_0000 where gmt_modified 2010-8-25 00:00:00; declare continue handler for not found set v_exit=1; open c_ids; repeat --将需要的值装入变量 fetch c_ids into v_id,v_spid; if v_exit = 0 then set @vv_id = v_id; --根据取模字段获取数据存在的表 select mod(v_spid,64) into c_table; --组装动态sql SET @SQL_CONTEXT = CONCAT(insert into item_record_, LPAD(c_table, 4, 0), select * from item_records_0000 where id = ?); PREPARE STMT FROM @SQL_CONTEXT; --执行sql EXECUTE STMT using @vv_id; DEALLOCATE PREPARE STMT; end if; set ii=i+1; --100条提交一次,以提高效率,记得执行存储过程前设置auto_commit if mod(i,100)=0 then commit; end if; until v_exit=1 end repeat; close c_ids; commit; end; //
declare c_table int; declare c_ids cursor for select id,supplier_id from item_records_0000 where gmt_modified = 2010-8-25 00:00:00; declare continue handler for not found set v_exit=1; open c_ids; repeat fetch c_ids into v_id,v_spid; if v_exit = 0 then set @vv_id = v_id; set @v_row=0; select mod(v_spid,64) into c_table; --判断数据是否已经存在 SET @SQL_C = CONCAT(select count(*) into @v_row from item_record_, LPAD(c_table, 4, 0), where id = ?); PREPARE STMT_C FROM @SQL_C; EXECUTE STMT_C using @vv_id; DEALLOCATE PREPARE STMT_C; SET @SQL_INSERT = CONCAT(insert into bbc_item_record_, LPAD(c_table, 4, 0), select * from item_records_0000 where id = ?); PREPARE STMT_I FROM @SQL_INSERT; SET @SQL_DELETE = CONCAT(DELETE FROM bbc_item_record_, LPAD(c_table, 4, 0), where id = ?); PREPARE STMT_D FROM @SQL_DELETE; --如果数据已经存在,则先delete在insert if @v_row 0 then EXECUTE STMT_D using @vv_id; DEALLOCATE PREPARE STMT_D; end if; EXECUTE STMT_I using @vv_id; DEALLOCATE PREPARE STMT_I; end if; set ii=i+1; if mod(i,100)=0 then commit; end if; until v_exit=1 end repeat; close c_ids; commit; end; //
列表查询的通用优化方案 列表查询是服务端开发中非常高频的诉求,接口的性能往往会跟用户体验强关联。本文通过一个具体的例子,来总结服务端写查询接口时的通用优化方案。 ## 一个例子 ### 功能诉求 给出一个具体的例子,背景是根据内容ID来查询内容信息(如下),目标是通过编码优化使得这个查询效率变快,减少上游(客户端App或外部服务)的等待时间。 ```java public interfa
IN p_table_name VARCHAR(200), IN p_field_list VARCHAR(2048), IN p_branch_size INT, in p_lpad INT BEGIN /*定义变量*/ DECLARE m_begin_row INT DEFAULT 0; WHILE m_begin_row p_branch_size DO /*构造语句*/ SET @MAIN_STRING = CONCAT(CREATE TABLE , p_table_name, _, LPAD(m_begin_row, p_lpad, 0), (, p_field_list ,)ENGINE=InnoDB DEFAULT CHARSET=utf8;); /*预处理*/ PREPARE main_stmt FROM @MAIN_STRING; EXECUTE main_stmt; SET m_begin_row=m_begin_row+1; END WHILE; END;
前期没有分表数据量太大后期拆表用的MySQL存储过程
简单的办法是直接写
--假设根据user_id分表,分成64张
insert into table_new_0000 select * from table_old where mod(user_id,64)=0; insert into table_new_0001 select * from table_old where mod(user_id,64)=1;
一共64条sql,OK 搞定。但是这个一张表被全表扫描了64次,做的无用功比较多,而且导致停机时间比较长
declare c_ids cursor for select id,user_id from item_records_0000 where gmt_modified 2010-8-25 00:00:00; declare continue handler for not found set v_exit=1; open c_ids; repeat --将需要的值装入变量 fetch c_ids into v_id,v_spid; if v_exit = 0 then set @vv_id = v_id; --根据取模字段获取数据存在的表 select mod(v_spid,64) into c_table; --组装动态sql SET @SQL_CONTEXT = CONCAT(insert into item_record_, LPAD(c_table, 4, 0), select * from item_records_0000 where id = ?); PREPARE STMT FROM @SQL_CONTEXT; --执行sql EXECUTE STMT using @vv_id; DEALLOCATE PREPARE STMT; end if; set ii=i+1; --100条提交一次,以提高效率,记得执行存储过程前设置auto_commit if mod(i,100)=0 then commit; end if; until v_exit=1 end repeat; close c_ids; commit; end; //
declare c_table int; declare c_ids cursor for select id,supplier_id from item_records_0000 where gmt_modified = 2010-8-25 00:00:00; declare continue handler for not found set v_exit=1; open c_ids; repeat fetch c_ids into v_id,v_spid; if v_exit = 0 then set @vv_id = v_id; set @v_row=0; select mod(v_spid,64) into c_table; --判断数据是否已经存在 SET @SQL_C = CONCAT(select count(*) into @v_row from item_record_, LPAD(c_table, 4, 0), where id = ?); PREPARE STMT_C FROM @SQL_C; EXECUTE STMT_C using @vv_id; DEALLOCATE PREPARE STMT_C; SET @SQL_INSERT = CONCAT(insert into bbc_item_record_, LPAD(c_table, 4, 0), select * from item_records_0000 where id = ?); PREPARE STMT_I FROM @SQL_INSERT; SET @SQL_DELETE = CONCAT(DELETE FROM bbc_item_record_, LPAD(c_table, 4, 0), where id = ?); PREPARE STMT_D FROM @SQL_DELETE; --如果数据已经存在,则先delete在insert if @v_row 0 then EXECUTE STMT_D using @vv_id; DEALLOCATE PREPARE STMT_D; end if; EXECUTE STMT_I using @vv_id; DEALLOCATE PREPARE STMT_I; end if; set ii=i+1; if mod(i,100)=0 then commit; end if; until v_exit=1 end repeat; close c_ids; commit; end; //
列表查询的通用优化方案 列表查询是服务端开发中非常高频的诉求,接口的性能往往会跟用户体验强关联。本文通过一个具体的例子,来总结服务端写查询接口时的通用优化方案。 ## 一个例子 ### 功能诉求 给出一个具体的例子,背景是根据内容ID来查询内容信息(如下),目标是通过编码优化使得这个查询效率变快,减少上游(客户端App或外部服务)的等待时间。 ```java public interfa
相关文章
- 「mysql优化专题」90%程序员没听过的存储过程和存储函数教学(7)
- Oracle job procedure 存储过程定时任务
- hibernate调用mysql存储过程
- 通用删除查询语句存储过程
- Oracle存储过程
- docker存储结构解析
- 2>存储过程的事务
- postgresql存储过程实例:已审核证书存入临时表
- Java -- JDBC 学习--调用函数&存储过程
- [NHibernate]存储过程的使用(二)
- Qt音视频开发13-mpv录像存储
- MySQL存储过程(二)——存储过程基本使用
- C# 调用存储过程
- [PHP] 自定义session存储机制的两种方案
- word自定义模板的存储位置
- oracle与sql server大批量存储过程可以替换部分
- Atitit mysql 存储过程捕获所有异常,以及日志记录异常信息
- Atitit 数据存储的数据表连接attilax总结
- Mysql存储过程
- [SSD核心技术:FTL 10] 固态存储垃圾回收GC(Garbage Collection)机制 | GC 分类 | GC 过程 | GC 和 Trim 的关系
- 万亿级日志与行为数据存储查询技术剖析(续)——Tindex是改造的lucene和druid
- Spring JDBC调用存储函数
- 用于调用存储过程的对象是。(选择1项)
- K8S 使用 NFS存储 动态创建 PVC/PV