MySQL怎么刪除數(shù)據(jù)

這篇文章主要介紹了MySQL怎么刪除數(shù)據(jù)，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

在Oracle里面對于數(shù)據(jù)清理，如果是非分區(qū)表，目前我經(jīng)常的處理思路是下面三個。
第一種是中規(guī)中矩，做好備份，然后開始清理，當(dāng)然這種情況只是說明數(shù)據(jù)清理的部分，不考慮高水位線的影響?？梢允褂胹hrink,move tablespace等來處理。
補充一句，其實這個dump這是一種形式，可以采用各種形式的數(shù)據(jù)導(dǎo)出格式，比如sqlldr適用的csv,比如外部表，比如expdp,exp的導(dǎo)出二進制dump等。

第二種思路是邏輯備份，就是把表采用ctas的方式備份一份。然后對原來的表進行數(shù)據(jù)清理。這種情況下，占用的是數(shù)據(jù)庫內(nèi)的數(shù)據(jù)空間。

第三種思路是迂回戰(zhàn)術(shù)，就是把原表改一個別名，然后新建一個同名的表（表里沒有數(shù)據(jù)，只有表結(jié)構(gòu))，然后把需要的增量數(shù)據(jù)插入到新表中.

這種思路在MySQL里面也是類似，不過值得一提的是MySQL的rename著實比較牛，因為MySQL中的database和Oracle中的user的含義有些類似，MySQL里面很輕松的使用rename操作把一個數(shù)據(jù)庫A中的表TEST很輕松的rename倒數(shù)據(jù)庫B里面。
最近開發(fā)的同事反饋有一個業(yè)務(wù)的查詢著實太慢，結(jié)果分析下來發(fā)現(xiàn)一種改善思路就是刪除舊數(shù)據(jù)。因為確實很長時間沒有清理了。
簡單和開發(fā)溝通了一下，其實有幾種思路可以走，不過就看具體的需求了。開發(fā)說保留近半年的數(shù)據(jù)，提供的清理sql如下。
半年以前的數(shù)據(jù)有大概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)
需要保留的數(shù)據(jù)有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)
所以按照這個比例，其實選用第三種方法看起來要好些，不過限于本地的空間，而且開發(fā)說這個表刪除的舊數(shù)據(jù)需要查看，恢復(fù)的可能性極小，所以我就一次弄干凈點，直接物理備份出來清理，采用了第一種方式。
簡單評估之后就開始操作。
先開始做備份。
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
然后就按照常規(guī)思路開始刪除，不過看起來很簡單的刪除竟然還報錯了。
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有很大的關(guān)系，目前的binlog設(shè)置如下
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)
而且比較糾結(jié)的是這個環(huán)境是采用了級聯(lián)復(fù)制，動一處需要聯(lián)動修改多處。目前的binlog cache size是500M左右。刪除的數(shù)據(jù)肯定要大于這個cache_size.
所以這個時候還得使用另外一種迂回戰(zhàn)術(shù)，那就是分批刪了。可以考慮使用datediff來作為一個基準(zhǔn)刪除。
現(xiàn)在距離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)
當(dāng)前時間為：
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2016-02-03 00:01:28 |
+---------------------+
1 row in set (0.00 sec)
當(dāng)然老是喜歡用oracle的語句檢驗一下。
SQL> SQL> select sysdate-217 from dual;
SYSDATE-217
-------------------
2015-07-01 16:02:03
好了，開始刪除數(shù)據(jù)，可以使用下面的語句，不過還需要改進一下。
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)
這個結(jié)果讓我有些無語，應(yīng)該是里面有一些數(shù)據(jù)不光舊，而且還有問題。
SQL>select sysdate-16835 from dual
SYSDATE-16835
-------------------
1969-12-31 16:04:59
需要調(diào)節(jié)刪除的跨度。
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)
數(shù)據(jù)已經(jīng)大體刪除，我們可以使用修改存儲引擎達到釋放碎片的目的了。
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     .
當(dāng)然剛剛的刪除還做了一些保留，為了對比，再次嘗試，刪除的工作就很快了。
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)
所以通過這個小的嘗試也可以看出來其實有些處理思路還是相通的，但是技術(shù)細節(jié)上還有很多需要繼續(xù)琢磨的地方。

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL怎么刪除數(shù)據(jù)”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!