preface

In a high-demand database scenario, it is always a good practice to add limit 1 after delete and update for single delete and update operations.

For example, in a delete execution, the first row hits the delete row, if limit 1 is present in SQL; Return, otherwise a full table scan will be performed. Efficiency speaks for itself.

Do we need to get in the habit of adding limit to delete on a daily basis? Is it a good habit?

In your daily SQL writing, do you use the following SQL when writing delete statements?

delete from t where sex = 1 limit 100; 
Copy the code

You may not have used it before. In general, we are unfamiliar with the question of whether to add limit after DELETE, and we don’t know how different it is. Today, I will take you to understand it, remember Mark!

Truncate is much more efficient than DELETE. Therefore, TRUNCate does not move transactions, lock the table, and write a large number of logs into the log file.

Truncate table table_NAME Immediately releases the disk space and resets the auto_INCREMENT value. Delete Deletes do not free up disk space, but subsequent inserts overwrite previously deleted data.

The delete scenario is discussed below. First, delete is followed by the limit keyword, but only by a single parameter, [limit row_count], which tells the server the maximum number of rows to be deleted before the control command is returned to the client.

Delete limit syntax is as follows:

(It is worth noting that when ordering by, order by + limit must be used, otherwise the order by will be optimized by the optimizer and will be considered meaningless.)

delete[low_priority] [quick] [ignore] from tbl_name [where ...]  [order by ...]  [limit row_count]Copy the code

The advantages of adding limit:

Take the following SQL as an example:

delete from t where sex = 1; 
Copy the code
  • 1. Reduce the cost of writing wrong SQL, even if you delete the wrong SQL, such as limit 500, it will lose 500 data, not fatal, using binlog can quickly restore data.
  • 2. Long transactions are avoided. When delete is executed, MySQL will add write locks and Gap locks (Gap locks) to all rows involved.
  • 3. If you do not add limit to delete a large amount of data, the CPU will be full and the deletion will be slow.

Select * from primary key where sex = 1; select * from primary key where sex = 1; select * from primary key where sex = 1;

MySQL MySQL: delete limit

If you want to delete the first 10,000 rows of a table, there are three ways to do it:

Delete from T limit 10000;

The second option is to loop delete from T limit 500 20 times in one connection;

Third, delete from T limit 500 is performed on all 20 connections simultaneously.

Think about it for a moment and read some of the answers:

Tony Du:

  • In scheme 1, if the transaction is relatively long, the lock is occupied for a long time. As a result, other clients wait for resources for a long time.
  • Scheme 2, serial execution, the relatively long transaction is divided into several relatively short transactions, each transaction occupies a relatively short lock time, other clients wait for corresponding resources in a relatively short time. Such an operation, which also means that resources are sharded (different fragments of resources are used for each execution), improves concurrency.
  • Scheme three, artificial lock competition, increase concurrency.
  • Scheme 2 is relatively better, and it should be combined with actual business scenarios.

Meat Mountain: Not considering the amount of concurrent access to the data table, just from the three schemes to compare.

  • In the first scenario, the lock is held for a long time at a time, which may cause other clients to wait for resources.
  • In the second scheme, it is divided into multiple occupancy locks and executed in serial mode without occupying the gap between the locks. Other clients can work, which is similar to the time fragmentation scheduling of the current multi-task operating system. In this way, resources are divided and not directly affected.
  • The third option, creating lock competition, increases concurrency.

As for which solution to choose, consider various factors such as table size, concurrency, and how much the business depends on the table.

~ hum:

  • 1. Direct delete 10000 May take too long to execute a transaction
  • 2. Slow efficiency Each cycle is a new short transaction, and the same record will not be locked. Repeat DELETE until the impact behavior is 0
  • 3. Although the efficiency is high, it is easy to lock the same record, and the possibility of deadlock is relatively high

Delete first 10000 rows from table Most of my friends chose the second option, which is to loop delete from T limit 500 20 times in one connection. Indeed, the second way is better.

In delete from T limit 10000, a single statement takes a long time and the lock takes a long time. Large transactions also cause master-slave delays.

The third option (delete from T limit 500 on all 20 connections) creates an artificial lock conflict.

The practical lesson of this example is to limit when deleting data. This not only controls the number of deleted data items, making the operation more secure, but also reduces the scope of locking. Therefore, adding limit after DELETE is a good habit to develop.

Well, that’s all for this article. If you have any questions or ideas, please leave a comment below so you can discuss them with your friends.

The story behind a photo

This photo was taken in 2009 in Beishan Township, Xide County. A girl raised her hand to answer questions in class. Her ragged clothes and classroom can only highlight her desire for learning in her eyes

From: chensj.blog.csdn.net/article/det…