MySQL怎么删除数据

53次阅读

共计 4528 个字符，预计需要花费 12 分钟才能阅读完成。

这篇文章主要介绍了 MySQL 怎么删除数据，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

在 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 2015-07-01 00:00:00
+———-+
| 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 2015-07-01 00:00:00
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)
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)
Query OK, 14400 rows affected (1.05 sec)
所以通过这个小的尝试也可以看出来其实有些处理思路还是相通的，但是技术细节上还有很多需要继续琢磨的地方。