Scheduling and locking for query optimization _ MySQL

Scheduling and locking of query optimization
When many customers query data tables together, scheduling and locking are required if the customer can query the data as quickly as possible. In MySQL, the select operation is called Read, and the operation (INSERT, UPDATE, REPLACE...) added to the modification of the data table is called write. The basic scheduling policies of MySQL can be summarized as follows:

Write requests are processed in the order they arrive at the server;

Write operations take precedence over read operations.

The scheduling policies for MyISAM and ISAM data tables are implemented with the help of data table locks. before a client program accesses a data table, it needs to obtain the corresponding locks. after completing the operations on the data table, then release the lock. LOCK management is usually managed by the server. you can also manually use the lock tables and unlock tables commands to apply for and release the LOCK. During the write operation, you need to apply for an exclusive lock. that is to say, during the write operation, the table can only be used by the write operation. During read operations, the customer must apply for a lock that allows other customers to write data tables to ensure that the data tables remain unchanged during reading. The read operation lock is not exclusive. Multiple read operations can simultaneously act on the same data table.

Some modifiers can affect the scheduling policy, such as LOW_PRIORITY (used for DELETE, INSERT, load data, REPLACE, UPDATE statements), HIGH_PRIORITY (used for SELECT statements), DELAYED (used for INSERT and REPLACE statements ). Their functions are as follows:

LOW_PRIORITY will lower the write operation priority to below the read operation level, that is, the read operation will block this level of write operations, and the SELECT HIGH_PRIORITY has a similar effect.

The DELAYED modification in the INSERT statement puts the INSERT operation into a delayed insert queue. Return the status information to the customer so that the customer program can continue the subsequent operations before the new data row is inserted into the data table. If a customer keeps reading the data table, the new data row will remain in the queue until the data table is not read, and the server will insert the data row in the queue into the data table. This statement can be used in the following scenarios to INSERT data into a data table with lengthy queries, and you do not want to be blocked, you can issue the insert delayed statement, put the insert operation into the "delayed insert" queue on the server. you can perform the following operations immediately without waiting.

When a data table has never been deleted or has just been fragled, the data rows inserted with the INSERT statement will only be added to the end of the data table, instead of inserting data into the middle of the data table. In this way, for MyISAM tables, MySQL allows write operations when other customers are performing read operations. We call this concurrent insert. To use this technique, pay attention to the following two questions:

Do not use the LOW_PRIORITY modifier in the INSERT statement.

The READ operation applies the lock tables... read local statement instead of the lock tables... READ statement. The LOCAL keyword only locks existing rows in the data table and does not block adding new rows to the end of the data table.

BDB data tables use page-level operation locks, while InnoDB data tables use row-level operation locks. Therefore, the concurrency of these two types of tables is much better than that of table-level locks such as MyISAM and ISAM data tables. Among them, InnoDB has the best concurrency. In conclusion, we can draw the following conclusions:

MyISAM and ISAM data tables have the fastest retrieval speed. However, when there are many retrieval and modification operations, the lock competition problem may occur, resulting in a longer wait time.

BDB and InnoDB data tables provide good concurrency in environments with a large number of modifications to provide better performance.

The MyISAM and ISAM data tables are locked at the table level, so no deadlock occurs. the BDB and InnoDB data tables are prone to deadlocks.