MySQL怎么刪除數據

126次閱讀

共計 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)
所以通過這個小的嘗試也可以看出來其實有些處理思路還是相通的，但是技術細節上還有很多需要繼續琢磨的地方。