mysql删除数据报错sec_MySQL删除数据的简单尝试

在Oracle里面对于数据清理，如果是非分区表，目前我经常的处理思路是下面三个。

第一种是中规中矩，做好备份，然后开始清理，当然这种情况只是说明数据清理的部分，不考虑高水位线的影响。可以使用shrink,move tablespace等来处理。

补充一句，其实这个dump这是一种形式，可以采用各种形式的数据导出格式，比如sqlldr适用的csv,比如外部表，比如expdp,exp的导出二进制dump等。

第二种思路是逻辑备份，就是把表采用ctas的方式备份一份。然后对原来的表进行数据清理。这种情况下，占用的是数据库内的数据空间。

第三种思路是迂回战术，就是把原表改一个别名，然后新建一个同名的表(表里没有数据，只有表结构)，然后把需要的增量数据插入到新表中.

这种思路在MySQL里面也是类似，不过值得一提的是MySQL的rename着实比较牛，因为MySQL中的database和Oracle中的user的含义有些类似，MySQL里面很轻松的使用rename操作把一个数据库A中的表TEST很轻松的rename倒数据库B里面。

最近开发的同事反馈有一个业务的查询着实太慢，结果分析下来发现一种改善思路就是删除旧数据。因为确实很长时间没有清理了。

简单和开发沟通了一下，其实有几种思路可以走，不过就看具体的需求了。开发说保留近半年的数据，提供的清理sql如下。

半年以前的数据有大概300万。

mysql> select count(*)from recharge where occur_time

+----------+

| count(*) |

+----------+

|  2945974 |

+----------+

1 row in set (1 min 20.13 sec)

需要保留的数据有50多万。

mysql> select count(*)from fact_recharge where occur_time > '2015-07-01 00:00:00';

+----------+

| count(*) |

+----------+

|   550422 |

+----------+

1 row in set (1 min 25.46 sec)

所以按照这个比例，其实选用第三种方法看起来要好些，不过限于本地的空间，而且开发说这个表删除的旧数据需要查看，恢复的可能性极小，所以我就一次弄干净点，直接物理备份出来清理，采用了第一种方式。

简单评估之后就开始操作。

先开始做备份。

mysqldump --default-character-set=UTF8 --single-transaction -q -R --triggers --tables test_ad xxxx_regok  |gzip > /data2/dba/databak/tab_bak/full_20150203_us_test_ad_xxxx_regok.sql.gz

然后就按照常规思路开始删除，不过看起来很简单的删除竟然还报错了。

mysql> delete from recharge where occur_time

ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

这个错误看来和binlog的cache size有很大的关系，目前的binlog设置如下

mysql> show variables like '%binlog%';

+-----------------------------------------+----------------------+

| Variable_name                           | Value                |

+-----------------------------------------+----------------------+

| binlog_cache_size                       | 4194304              |

| binlog_direct_non_transactional_updates | OFF                  |

| binlog_format                           | ROW                  |

| binlog_stmt_cache_size                  | 32768                |

| innodb_locks_unsafe_for_binlog          | OFF                  |

| max_binlog_cache_size                   | 536870912            |

| max_binlog_size                         | 1073741824           |

| max_binlog_stmt_cache_size              | 18446744073709547520 |

| sync_binlog                             | 0                    |

+-----------------------------------------+----------------------+

9 rows in set (0.00 sec)

而且比较纠结的是这个环境是采用了级联复制，动一处需要联动修改多处。目前的binlog cache size是500M左右。删除的数据肯定要大于这个cache_size.

所以这个时候还得使用另外一种迂回战术，那就是分批删了。可以考虑使用datediff来作为一个基准删除。

现在距离2015年7月1日有217天的时间差，那么我们就按照这个时间差来做点文章，分批删除。

mysql> select datediff(now(),'2015-07-01 00:00:00') ;

+---------------------------------------+

| datediff(now(),'2015-07-01 00:00:00') |

+---------------------------------------+

|                                   217 |

+---------------------------------------+

1 row in set (0.00 sec)

当前时间为：

mysql> select now();

+---------------------+

| now()               |

+---------------------+

| 2016-02-03 00:01:28 |

+---------------------+

1 row in set (0.00 sec)

当然老是喜欢用oracle的语句检验一下。

SQL> SQL> select sysdate-217 from dual;

SYSDATE-217

-------------------

2015-07-01 16:02:03

好了，开始删除数据，可以使用下面的语句，不过还需要改进一下。

delete from fact_recharge where datediff(now(),occur_time) >217

那么删除的边界值怎么确定呢。

mysql> select max(datediff(now(),occur_time)) from fact_recharge where datediff(now(),occur_time) >217 ；

+---------------------------------+

| max(datediff(now(),occur_time)) |

+---------------------------------+

|                           16835 |

+---------------------------------+

1 row in set (3.69 sec)

这个结果让我有些无语，应该是里面有一些数据不光旧，而且还有问题。

SQL>select sysdate-16835 from dual

SYSDATE-16835

-------------------

1969-12-31 16:04:59

需要调节删除的跨度。

mysql> delete from recharge where datediff(now(),occur_time)>218 and  datediff(now(),occur_time) < 800;

ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

mysql> delete from recharge where datediff(now(),occur_time)>218 and  datediff(now(),occur_time) < 300;

Query OK, 310067 rows affected (36.78 sec)

mysql> delete from recharge where datediff(now(),occur_time)>300 and  datediff(now(),occur_time) < 500;

Query OK, 1065870 rows affected (1 min 50.08 sec)

mysql> delete from recharge where datediff(now(),occur_time)>500 and  datediff(now(),occur_time) <700;

Query OK, 1021640 rows affected (1 min 59.31 sec)

mysql> delete from recharge where datediff(now(),occur_time)>700 and datediff(now(),occur_time) < 1000;

Query OK, 505048 rows affected (2 min 29.91 sec)

数据已经大体删除，我们可以使用修改存储引擎达到释放碎片的目的了。

mysql> alter table recharge  engine=InnoDB;

Query OK, 594253 rows affected (4 min 19.94 sec)

Records: 594253  Duplicates: 0  Warnings: 0

修改之后，删除了大概2G左右的空间。

# ll recharge*|du -sh .

33G     .

# ll recharge*|du -sh .

31G     .

当然刚刚的删除还做了一些保留，为了对比，再次尝试，删除的工作就很快了。

mysql> delete from recharge where datediff(now(),occur_time)>1000;

Query OK, 25712 rows affected (2.03 sec)

mysql> delete from recharge where datediff(now(),occur_time)>218;

Query OK, 14400 rows affected (1.05 sec)

所以通过这个小的尝试也可以看出来其实有些处理思路还是相通的，但是技术细节上还有很多需要继续琢磨的地方。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1986193/，如需转载，请注明出处，否则将追究法律责任。