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

This article mainly explains "what are the knowledge points of MYSQL database optimization?" interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what are the knowledge points of MYSQL database optimization?"

Usually when we do database operations, generally speaking, when we want to access a table in the database, the reader first has to acquire the lock of the table. If a write operation for this table arrives at this time, then the writer will wait all the time. When the reader completes the read operation, the reader will release the lock on the table, if the writer is still waiting for the queue. The table is locked by the writer and will not be unlocked until the writer completes the write operation to the table.

Here is a question for us to think about: what is the scheduling policy of MYSQL if both a write operation and a read operation are waiting in the waiting queue to access this table?

In MYSQL, the default scheduling strategy is that the writer takes precedence over the reader, that is, if there is both a write operation and a read operation in the waiting queue, then the writer first waits for it to complete the operation and liberates the lock, and the reader starts the operation. Therefore, in order to improve the update data efficiency of MYSQL, we should focus on how to reduce the lock competition and reduce the waiting time of write operation.

1.INSERT statement

Our mysql insert statement is shown in the figure above, but when we need to insert a large amount of data, such statements can have performance problems. For example, when we need to insert 10, 000 pieces of data at once, we need 10, 000 insert statements, each of which needs to be submitted to the relational engine for parsing and optimization before the real insertion work is done. This will greatly reduce performance, so what strategies can we take to improve the performance of insert statements?

The main results are as follows: (1) when inserting data, several pieces of data are inserted at the same time, and after testing, the efficiency is the highest when inserting 10 pieces of data at the same time.

(2) use sequential primary keys, such as self-increasing primary keys, so that the order of insertion is as sequential as possible.

(3) replace INSERT statement with replace statement as far as possible.

2.UPDATE statement

The above figure is the basic statement of our update, in fact, we can easily understand the update statement, which is to modify the data already in the table, so what kind of optimization can we make in the update operation?

(1) the same principle as our insert statement, when we need to update a large amount of data, such statements will have performance problems. So you can update multiple records at the same time.

(2) try not to modify the primary key field, in fact, this has also been explained, the primary key we try to use sequential primary key.

(3) minimize the update operation containing UPDATE triggers. Some readers may not understand what triggers are. It is recommended that Baidu supplement the source of knowledge first.

(4) avoid updating columns with many indexes as far as possible.

(5) avoid updating columns in the condition of WHERE sentences as far as possible.

3.DELETE statement

Delete statement is used to delete table data in the database, which is generally divided into two types: directly delete all data in the table and delete data in the table that meet certain conditions.

In MYSQL, both truncate table and delete statements can delete all data in the table, but truncate table deletes faster, and no mysql log is recorded after deletion, so the data can not be recovered, so it must be used with caution. And if there is no foreign key association, the execution of truncate table is to drop table first and then create a new empty table, which is of course much faster than the delete statement that deletes row-by-row data. When a table is associated with a foreign key, truncate table deletes data one by one. If the foreign key specifies cascading deletion, the associated child table data will also be deleted. If no cascade deletion is specified, the deletion will report an error if it encounters the parent row associated with the child table.

Like the INSERT statement, the DELETE statement locks the table that is about to operate, and if the table is locked, other operations cannot be performed, so a relatively simple strategy is to leave it to the database to split and formulate an execution strategy, such as limiting the processing of 1000 pieces of data at a time. For order of magnitude data, delete data can first delete the index, and then delete the useless data, delete and recreate the index, which is definitely a qualitative change compared to the efficiency of deleting one by one.

4.REPLACE statement

Just now in the INSERT statement I mentioned that one of the optimization strategies is to use the replace statement to replace the insert statement, for example, a table has established a unique index in a field, and when inserting a record into the table using the existing key value, there will be an error, then we can use the replace statement instead of the insert statement, so our execution result is to replace the original record value with the new record value.

The difference between 5.REPLACE statements and INSERT statements:

When using replace to insert data, if the records are not duplicated, the function is exactly the same as the insert statement, and the obvious point of the replace statement is that the delete statement and the insert statement are combined into an atomic operation, so the complexity of the transaction operation is greatly reduced, which is why we advocate using the replace statement instead of the insert statement.

When using the replace statement, there must be a unique primary key or unique index in the table, otherwise the replace statement will be meaningless.

The difference between 6.REPLACE statements and UPDATE statements:

The update statement does nothing when there is no matching record, while the replace statement updates when there are duplicate records and inserts when there are no duplicate records. The update statement can update some fields of a record according to the clause condition, while the replace statement deletes the record completely when a duplicate record is found, and then inserts a new record. So the replace statement can actually be understood as the combination of insert statement and update statement.

At this point, I believe that everyone on the "MYSQL database optimization knowledge points what" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.