Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL how to delete data, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

For data cleaning in Oracle, if it is a non-partitioned table, I often deal with the following three ideas.

The first is to keep to the rules, make a good backup, and then start cleaning up, of course, this situation is only part of the description of data cleaning, regardless of the impact of high water level lines. It can be handled with shrink,move tablespace and so on.

In addition, this dump is actually a form, which can be exported in various forms, such as csv for sqlldr, external tables, such as expdp,exp 's exported binary dump, etc.

The second idea is logical backup, which is to back up a copy of the table in the way of ctas. Then clean up the data of the original table. In this case, the data space in the database is occupied.

The third idea is to change the alias of the original table, then create a new table with the same name (there is no data in the table, only the table structure), and then insert the incremental data into the new table.

This kind of thinking is also similar in MySQL, but it is worth mentioning that MySQL's rename is really awesome, because the meaning of database in MySQL is similar to that of user in Oracle. MySQL easily uses rename operations to rename a table in database An and rename database B.

A colleague who developed recently reported that the query of a business is really too slow. After analysis, it is found that one way to improve is to delete the old data. Because it hasn't been cleaned for a long time.

Simply communicate with the developer, in fact, there are several ideas to go, but it depends on the specific requirements. The developer said to keep the data for nearly half a year, and the cleaning sql provided is as follows.

Six months ago, the figure was about 3 million.

Mysql > select count (*) from recharge where occur_time select count (*) from fact_recharge where occur_time > '2015-07-01 00VOV 0000'

+-+

| | count (*) |

+-+

| | 550422 |

+-+

1 row in set (1 min 25.46 sec)

So according to this ratio, it looks better to choose the third method, but it is limited to the local space, and the developer says that the old data deleted by this table needs to be viewed, and the possibility of recovery is very small, so I clean it up at once. Direct physical backup to clean up, using the first way.

The operation will begin after a simple evaluation.

Let's start with the backup.

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

Then I started to delete it according to the usual way of thinking, but it seems that the simple deletion has been reported incorrectly.

Mysql > delete from recharge where occur_time 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)

And what is more entangled is that this environment uses cascading replication, which needs to be modified in one place. The current binlog cache size is about 500m. The deleted data must be larger than this cache_size.

So at this time, we have to use another circuitous tactic, that is, delete it in batches. Consider using datediff as a benchmark for deletion.

Now there is a time difference of 217 days from July 1, 2015, so let's do some articles according to this time difference and delete it in batches.

Mysql > select datediff (now (), '2015-07-01 0000-0015-0012')

+-+

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

+-+

| | 217 |

+-+

1 row in set (0.00 sec)

The current time is:

Mysql > select now ()

+-+

| | now () |

+-+

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

+-+

1 row in set (0.00 sec)

Of course, I always like to test it with oracle sentences.

SQL > SQL > select sysdate-217 from dual

SYSDATE-217

-

2015-07-01 16:02:03

All right, to start deleting the data, you can use the following statement, but it needs to be improved.

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

So how to determine the boundary value of deletion.

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)

This result makes me a little speechless, it should be that there are some data not only old, but also problematic.

SQL > select sysdate-16835 from dual

SYSDATE-16835

-

1969-12-31 16:04:59

The span of the deletion needs to be adjusted.

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) > 500and datediff (now (), occur_time) delete from recharge where datediff (now (), occur_time) > 700and 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

After the modification, about 2G of space has been deleted.

# ll recharge* | du-sh.

33G.

# ll recharge* | du-sh.

31G.

Of course, the deletion just made some reservations, in order to compare, try again, the deletion will be done very quickly.

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.05sec)

Therefore, through this small attempt, we can also see that some of the processing ideas are in common, but there are still a lot of technical details that need to be pondered.

Thank you for reading this article carefully. I hope the article "how to delete data in MySQL" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.