Introduction to Basic MySQL database scheduling policies _ MySQL

MySQL allows statements to be scheduled, which makes queries from several clients more cooperative, so that a single client will not be locked for too long. Changing the scheduling feature also ensures that specific queries are processed faster. Let's first take a look at the default scheduling policy of MySQL, and then let's see what options can be used to change this policy. For the purpose of discussion, we assume that

MySQL allows statements to be scheduled, which makes queries from several clients more cooperative, so that a single client will not be locked for too long. Changing the scheduling feature also ensures that specific queries are processed faster. Let's first take a look at the default scheduling policy of MySQL, and then let's see what options can be used to change this policy. For the purpose of discussion, assume that the client program that executes the search (SELECT) is the read program. Another client program that executes the DELETE, INSERT, REPLACE or up date operation is the write program.

The basic scheduling policies of MySQL are summarized as follows:

■ Write requests should be processed in the order they arrive.

■ Writing has a higher priority than reading.

Implement the scheduling policy with the help of table locks. The client must first obtain the table lock whenever it needs to access the table. You can directly use lock tables to do this, but the LOCK manager of the server will automatically obtain the LOCK as needed. When the client ends processing the table, the table lock can be released. The directly obtained lock can be released using the unlock tables, but the server also automatically releases the lock.

The client that executes the write operation must have an exclusive lock on the table. During the write operation, the table is in an inconsistent state because the data record is being deleted, added, or changed, and the indexes on the table may need to be updated accordingly. If the table is constantly changing, allowing other clients to access the table may cause problems. It is obviously not good for two clients to write the same table at the same time, because this will soon make the table unavailable. It is not a good thing to allow the client to read the constantly changing table, because it may be being modified at the moment of reading the table, and the result is incorrect. The client that executes the read operation must have a lock to prevent other clients from writing the table to ensure that the table does not change during the read process. However, this lock does not require exclusive access to read operations. This lock also allows other clients to read tables at the same time. Reading does not change the table. it is unnecessary to prevent other clients from reading the table.

MySQL allows several query modifiers to influence its scheduling policy. One of them is the LOW_PRIORITY keyword of the DELETE, INSERT, load data, REPLACE, and up date statements. The other is the SELECT h_priority keyword of the SELECT statement. The third is the DELAYED keyword of the INSERT and REPLACE statements.

The LOW_PRIORITY keyword affects scheduling as follows. Generally, if the write operation of a table arrives when the table is being read, the write program is blocked until the Read program is completed, because once a query starts, it cannot be interrupted. If another read request arrives when the write program waits, the Read program is blocked because the default scheduling policy has a higher priority than the Read program. When the first read program ends, the write program continues. when the write program ends, the second read program starts.

If the write request is LOW_PRIORITY, the write operation is not considered as having a higher priority than the read operation. In this case, if the second read request arrives when the write program waits, the second read operation is scheduled before the write operation. Write operations are allowed only when there are no other read requests. Theoretically, this type of scheduling change indicates that write with LOW_PRIORITY may be blocked forever. When the previous read request is being processed, the new request can be placed before LOW_PRIORITY write as long as the other read request arrives.

The HIGH_PRIORITY keyword of the SELECT query works similarly. It causes the SELECT statement to be inserted before a waiting write operation, even if the write operation has a normal priority. The ELAYED modifier of INSERT acts as follows. When an insert delayed request arrives in the table, the server puts the corresponding row into a queue and immediately returns a status to the client program, so that the client program can continue to execute, even if these rows have not been inserted into the table. If the reader is reading the table, the row in the queue is suspended. When no data is read, the server starts to insert rows in the delayed row queue. The server stops from time to see if new read requests have arrived and waits. In this case, the delayed row queue is suspended and the read program is allowed to continue. When there are no other read operations, the server inserts the delayed row again. This process continues until the delayed row queue is empty.

This scheduling modifier does not appear in all MySQL versions. The following table lists the modifiers and the MySQL versions that support these modifiers. You can use this table to determine the functions of the MySQL version:

If other clients may execute lengthy SELECT statements and you do not want to wait until the insertion is complete, insert delayed is useful. The client that publishes insert delayed can continue execution more quickly, because the server simply inserts the row to be inserted. However, we should be aware of the differences between normal INSERT and insert delayed performance. If insert delayed has a syntax error, an error is sent to the client. if it is normal, no information is sent. For example, the obtained AUTO_INCREMENT value cannot be believed when this statement is returned. The number of duplicates on the unique index is not counted. This is because the insert operation returns a status before the actual insert is complete. The other statement also indicates that if the row of the insert delayed statement is waiting in queue and the server crashes or is terminated (with kill-9), the row will be lost. This is not the case when a normal TERM is terminated. the server inserts these rows before exiting.