Shulou(Shulou.com)06/01 Report--

This article mainly introduces whether it is good to add limit after delete in mysql. What is introduced in this article is very detailed and has certain reference value. Interested friends must finish reading it!

In databases where business scenarios are demanding, it is definitely a good habit to add limit 1 after delete and update for single delete and update operations. For example, in the delete execution, the first line hits the delete row, if there is limit 1 in the SQL; then it will be return, otherwise a full table scan will be performed before return. Efficiency is self-evident.

So, in the daily implementation of delete, do we need to get into the habit of adding limit? Is it a good habit?

In your daily SQL writing, have you ever used the following SQL when writing delete statements?

Delete from t where sex = 1 limit 100

You may not have used it, but in general scenarios, we are very strange to the question of whether we need to add limit after delete, and we don't know how different it is!

First of all, the limit keyword is supported after delete, but only a single parameter, [limit row_count], is supported, which is used to tell the server the maximum value of the row deleted before the control command is returned to the client.

The delete limit syntax is as follows, and it is worth noting that order by must be used in conjunction with limit, otherwise it will be optimized.

Delete\ [low\ _ priority\]\ [quick\]\ [ignore\] from tbl\ _ name

\ [where...\]

\ [order by...\]

\ [limit row\ _ count\]

Advantages of adding limit:

"take the following SQL as an example:"

Delete from t where sex = 1

1. Reduce the cost of miswriting SQL, even if you delete it wrong, such as limit 500, then you will lose 500 pieces of data, which is not fatal, and you can recover data quickly through binlog. two。 To avoid long transactions, MySQL will add write locks and Gap locks (gap locks) to all rows involved during delete execution, and all rows related to the execution of DML statements will be locked. If the number of deletions is large, it will directly affect the unavailability of related businesses. 3. When the amount of delete data is large, it is easy to fill up the cpu without adding limit, resulting in slower and slower deletions.

For the second point above, the premise is that sex adds an index, as we all know, "locking is based on the index. If the sex field does not have an index, it will be scanned to the primary key index, so even if there is only one record in sex = 1, the table will be locked." "

"MySQL boss Ding Qi has a question about the use of delete limit:"

❝

If you want to delete the first 10000 rows of data in a table, there are three ways to do this: first, execute delete from T limit 10000 directly; second, execute delete from T limit 500 20 times in a join loop; and third, execute delete from T limit 500 in 20 joins at the same time.

❞

Think about it first, and then take a look at the answers of some old buddies:

"Tony Du:"

In scenario 1, if the transaction is relatively long, the lock will take longer, which will cause other clients to wait for resources for a long time. In the second scheme, serial execution divides a relatively long transaction into several relatively short transactions, the time for each transaction to occupy the lock is relatively short, and the time for other clients to wait for the corresponding resources is also shorter. This operation, which also means that resources are used in pieces (using different fragments of resources for each execution), can improve concurrency. The third plan is to create self-made lock competition to aggravate the concurrency. Plan 2 is relatively good, and it should also be combined with the actual business scenario.

Meat Mountain:

Do not consider the access concurrency of the data table, simply from the comparison of these three schemes.

In the first scenario, the lock time is long at a time, which may cause other clients to wait for resources all the time. The second scheme, divided into multiple occupancy locks, serial execution, does not occupy the lock gap other clients can work, similar to the current multitasking operating system time slicing scheduling, everyone slicing the use of resources, does not directly affect the use. The third scheme creates lock competition and aggravates concurrency.

As for which solution to choose should be combined with the actual scenario, take into account various factors, such as the size of the table, the amount of concurrency, the degree of dependence of the business on this table, and so on.

"~ buzzing:"

1. Direct delete 10000 may take too long to execute a transaction

two。 Slow down. Each cycle is a new short transaction, and the same record is not locked. Repeat DELETE to know that it affects behavior 0.

3. Although the efficiency is high, it is easy to lock the same record, and the possibility of deadlock is high.

How to delete the first 10000 rows of a table. More friends choose the second way, that is, to cycle through delete from T limit 500s 20 times in one connection. This is true, and the second way is relatively good.

In the first way (that is, executing delete from T limit 10000 directly), a single statement takes a long time and locks for a long time, and large transactions can lead to master-slave delays.

The third method (that is, executing delete from T limit 500s simultaneously in 20 connections) can artificially cause lock conflicts.

The guiding significance of this example for our practice is to add limit as much as possible when deleting data. This can not only control the number of deleted data, make the operation more secure, but also reduce the scope of locking. Therefore, adding limit after delete is a good habit worth forming.

The above is all the content of this article "how about adding limit after delete in mysql?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.