mysql相关问题

[qq_30345479]

我有一张表table里面大概有900w条数据，对应的idb大小为900m左右
接着我执行一个存储结构，是对这张表的其中一个字段进行update，
在update期间，发现其idb不断增到，增大到50多G，而且还会一直增大，导致硬盘满了
（加如我是直接执行update的话，idb几乎是不会增大的）
这是什么问题

[ACMAIN_CHM]

你的语句是什么？建议详细描述以供分析。 贴出 show create table , 及存储过程。

[zhu19774279]

同意版主的。 另外，把数据换成9w条，看看存储过程能否正确执行。或者idb到底会变大多少

[qq_30345479]

-- 创建表
drop table if exists tb_hist_item;
CREATE TABLE `tb_hist_item` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `p_item_id` bigint(20) unsigned NOT NULL,
  `p_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `oper_type` int(10) unsigned NOT NULL,
  `count` int(11) NOT NULL,
  `r_count` int(11) NOT NULL DEFAULT '0',
  `action_id` int(10) unsigned NOT NULL,
  `shop_id` int(11) unsigned NOT NULL,
  `created_date` datetime NOT NULL,
  `last_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Index_1` (`oper_type`,`last_date`,`item_id`) USING BTREE,
  KEY `Index_2` (`p_id`,`p_item_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

-- 模拟插入数据
drop PROCEDURE if EXISTS p_insert;
delimiter ;;
create PROCEDURE p_insert()
BEGIN
set @ii=1;
while @ii < 10000  do
 INSERT INTO `tb_hist_item` (`id`, `p_item_id`, `p_id`, `item_id`, `oper_type`, `count`, `r_count`, `action_id`, `shop_id`, `created_date`, `last_date`) VALUES (0, @ii, @ii, @ii, @ii, @ii, 0, 0, @ii, now(), now());
 set @ii=@ii+1;
end while ;
end;;
delimiter ;

-- 处理数据的存储过程
DROP PROCEDURE IF EXISTS `p_B`;
DELIMITER ;;
CREATE  PROCEDURE `p_B`()
begin
DECLARE done INT DEFAULT 0;
DECLARE my_table_name varchar(100);
DECLARE cur2 CURSOR FOR select "tb_hist_item" ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
declare exit handler for sqlexception select 'ERROR';

SET done = 0;
OPEN cur2;
	FETCH cur2 INTO my_table_name;
	if done = 0 then
	REPEAT
			SET @l_sql=concat('update ',my_table_name,' set id=id+100000 order by id desc');		
		PREPARE mysql FROM @l_sql;
		EXECUTE mysql;
		DEALLOCATE PREPARE mysql;		
	FETCH cur2 INTO my_table_name;
	UNTIL done END REPEAT;
	end if;
CLOSE cur2;
end
;;
DELIMITER ;

[qq_30345479]

还有之前写错了 增大的文件是一个以ibd为格式的文件

[qq_30345479]

引用 1 楼 ACMAIN_CHM 的回复:

你的语句是什么？建议详细描述以供分析。 贴出 show create table , 及存储过程。

已经贴出来了，麻烦看看

[ACMAIN_CHM]

应该是你的存储过程中没有做任何COMMIT，导致一个单独事务中更新大量记录，而需要大量的磁盘空间来存储这些事务日志

[qq_30345479]

引用 6 楼 ACMAIN_CHM 的回复:

应该是你的存储过程中没有做任何COMMIT，导致一个单独事务中更新大量记录，而需要大量的磁盘空间来存储这些事务日志

我没有试过在存储过程中做commit 是要如何加的，能举个例子吗

[qq_30345479]

我对事务接触的比较少，弱弱的问一下 这个存储过程里面也只有一条update的语句，加上commit，是如何减少事务日志的

[ACMAIN_CHM]

那就百度一下 数据库 事务 日志 undo log