MySQL query is optimized by scheduling and locking

The following articles mainly introduce how to use scheduling and locking to optimize MySQL queries. In actual operations, we often encounter faster query execution for individual MySQL databases. The MySQL database also allows you to change the statement scheduling priority, which enables better collaboration between queries from multiple clients.

In this way, a single client will not wait for a long time due to locking.

Database Administrators often encounter fast execution of separate MySQL queries. MySQL also allows you to change the statement scheduling priority so that MySQL queries from multiple clients can better collaborate, so that a single client will not wait for a long time due to locking.

Changing the priority also ensures that queries of specific types are processed faster. The following describes the default Scheduling Policies of MySQL and the options that can be used to influence these policies. There are also the use of concurrent insert operations and the impact of the storage engine lock level on the concurrency of the client. For the convenience of the discussion, we call the client that executes the search (SELECT) as the "Reader" and perform the modification operations (DELETE, INSERT, REPLACE, or UPDATE) the client is called "Writer ".

The default Scheduling Policies available for MySQL queries are summarized as follows:

Write operations take precedence over read operations.

Write operations on a data table can only occur once at a certain time point. Write requests are processed in the order they arrive.

Multiple read operations on a data table can be performed simultaneously.

The MyISAM and MEMORY storage engines use data table locks to implement such scheduling policies. When the client accesses a table, it must first obtain its lock. When the client completes the table operation, the lock is released. It is feasible to explicitly obtain or release the LOCK using the lock tables and unlock tables statements, but in general, the server's LOCK manager will automatically obtain the LOCK as needed, release the lock when it is no longer needed.

The type of the obtained lock depends on whether the client writes or reads the lock.

The client that writes data to a table must have exclusive (exclusive) Access lock. During the operation, the data table is in an inconsistent (inconsistent) state, because when the data records are deleted, added, or modified, indexes on a data table may also need to be updated to match each other.

If other clients are allowed to access this data table during the change process, problems may occur. Obviously, it is unfavorable to allow two clients to write data to a table at the same time, because such an operation will quickly make the information in the table a pile of useless garbage. However, it is not good to allow the client to read the changed data table, because the data in the reading location may be changing (modifying), and the read result may not be real.

The client that reads a table must obtain a lock to prevent other clients from writing or changing the table during the reading process. However, this lock does not require exclusive access. The read operation does not change the data, so there is no reason for a reader to prevent other readers from accessing the table. Therefore, the read lock allows other clients to read the table at the same time.

MySQL query provides several statement modifiers that allow you to modify its scheduling policy:

The LOW_PRIORITY keyword is applied to DELETE, INSERT, load data, REPLACE, and UPDATE.

The HIGH_PRIORITY keyword is applied to SELECT and INSERT statements.

The DELAYED keyword is applied to INSERT and REPLACE statements.

LOW_PRIORITY and HIGH_PRIORITY regulators affect storage engines that use data table locks (such as MyISAM and MEMORY ). The DELAYED modifier acts on the MyISAM and MEMORY data tables.