MySQL optimization (5)

Document directory

• 7.3 lock
• 7.3.1 Lock Mechanism
• 7.3.2 lock table

7.3 lock 7.3.1 Lock Mechanism

MySQL currently supportsISAM,MyISAM,MEMORY(HEAP) Table-level locks for Type tables,BDBTable supports page-level locks,InnoDBTable supports row-level locks.
Most of the time, we can use experience to guess what kind of lock is more suitable for the application, but it is often difficult to say that a lock is better than others, it is determined by the application, different locks may be required in different places.
To determine whether to use a row-Level Lock storage engine, you need to check what the application is doing and how the query and update statements are used. For example, many web applications perform a large number of queries and seldom delete them. They only insert records into specific tables based on index updates. Use Basic MySQLMyISAMThe table is suitable.
The storage engine for table-level locks in MySQL releases deadlocks. To avoid deadlock, you can request a lock before any query and lock the table in the order of the request.
Used in MySQLWRITE)The implementation mechanism of the table lock is as follows:

• If the table is not locked, add a write lock.
• Otherwise, put the request in the write lock queue.

Used in MySQLREAD)The implementation mechanism of the table lock is as follows:

• If no write lock is applied to the table, a read lock is applied.
• Otherwise, put the request in the read lock queue.

After the lock is released, the thread in the write lock queue can use this lock resource before it is the turn to read the thread in the lock queue.
This means that if there are many update operations in the tableSelectYou must wait until all updates are complete before starting.
Starting from MySQL 3.23.33, you can use status variablesTable_locks_waitedAndTable_locks_immediateTo analyze the lock table contention in the system:

mysql> SHOW STATUS LIKE 'Table%';+-----------------------+---------+| Variable_name         | Value   |+-----------------------+---------+| Table_locks_immediate | 1151552 || Table_locks_waited    | 15324   |+-----------------------+---------+

After MySQL 3.23.7 (3.23.25 on Windows ),MyISAMAs long as there is no conflict in the tableInsertThe lock table can be freely executed in parallel without having to use the lock table.InsertAndSelectStatement. That is, it can be read from other clients.MyISAMInsert a new record at the same time as the table record. If there are no spare disk blocks in the middle of the data file, there will be no conflict, in this case, all new records are written at the end of the data file (empty may occur when the table is deleted or updated ). When the empty space is filled with new data, the parallel insertion feature is automatically re-enabled.
If you want to do a lotInsertAndSelectOperation, but parallel insertion is not possible, you can insert records into the temporary table, and then regularly update the data in the temporary table to the actual table. You can use the following command:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;mysql> Insert INTO real_table Select * FROM insert_table;mysql> TRUNCATE TABLE insert_table;mysql> UNLOCK TABLES;

InnoDBUse row-level locks,BDBUse a page-Level Lock. ForInnoDBAndBDBThe storage engine may cause deadlocks. This is becauseInnoDBThe row lock is automatically captured,BDBThe page lock will be captured when the SQL statement is executed, rather than at the beginning of the transaction.
Row-level locks have the following advantages:

• Reduce the conflict lock when multiple threads request different records.
• Reduce data changes during transaction rollback.
• It is possible to lock a single row of records for a long time.

Row-level locks have the following Disadvantages:

• It consumes more memory than page-level locks and table-level locks.
• When used in a large number of tables, it is slower than page-level locks and table-level locks because they need to request more resources.
• When most data needs to be frequently processedGROUP BYIf you want to scan the entire table frequently, it is obviously worse than other locks.
• Using Higher-Level locks makes it easier to support different types of applications, because the cost of such locks is much lower than that of Row-level locks.

Table-level locks are superior to page-level locks and row-level locks in the following situations:

• Many operations are read tables.
• Read and update indexes with strict conditions. When updating or deleting indexes, you can use a separate index to read them:

  Update tbl_name SET column=value Where unique_key_col=key_value;    Delete FROM tbl_name Where unique_key_col=key_value;    

• SelectAndInsertConcurrent statement execution, but fewUpdateAndDeleteStatement.
• Many scan tables andGROUP BYOperation, but there is no write table.

Table-level locks differ from row-level locks or page-level locks in the following ways:
Make the version of one write and multiple reads (for example, concurrent inserts in MySQL ). That is to say, databases/Tables support different attempts based on the time points at which data is accessed. Other names include time travel, write replication, or on-demand replication.
Original article: Versioning (such as we use in MySQL for concurrent inserts) where you can have one writer at the same time as your readers. this means that the database/table supports different views for the data depending on when you started to access it. other names for this are time travel, copy on write, or copy on demand.
On-demand replication is much better than page-level or row-level locks in many cases. However, in the worst case, more memory is used than other normal locks.
Application-level locks can be used to replace row-level locks, for exampleGET_LOCK()AndRELEASE_LOCK(). However, they are suggested locks (Original: These are advisory locks), so they can only be used in secure and trusted applications.

7.3.2 lock table

In order to have a quick lock, apart from InnoDBAndBDBBoth storage engines use table-level locks instead of page-level, row-level, and column-level locks ).
ForInnoDBAndBDBTable. MySQL is only usedLOCK TABLESThe table-Level Lock is used only when the table is locked. We recommend that you do not use these two types of tables.LOCK TABLESBecauseInnoDBAutomatic adoption of Row-level locks,BDBUse Page-level locks to ensure transaction isolation.
If the data table is large, table-level locks are much better than Row-level locks in most applications, but there are some traps.
Table-level locks allow many threads to read data from the data table at the same time. However, if another thread wants to write data, it must first obtain exclusive access. When updating data, you must wait until the update is complete before other threads can access the table.
Update operations are generally considered to be more important than reading, so they have a higher priority. However, it is best to check whether the data table has a high value first.SelectBut the update operation is not very 'url '.
The table lock is waiting on a thread because the disk space is full, but the remaining disk space is required for the thread to continue processing. In this case, all the threads that want to access the problematic table will be set to the waiting state until there is any available disk space.
Table locks are unfavorable in the following scenarios:

• A client submitsSelectOperation.
• Other clients have submittedUpdateOperation, the client will waitSelectThe execution can be started only after the execution is completed.
• Other clients have submittedSelectRequest. BecauseUpdateThe priority is higherSelect, SoSelectWill wait Update After the task is completed, the task starts to run. It is also waiting for the first task.SelectOperation.

The following describes how to reduce resource contention caused by table locks:

• LetSelectThe speed should be as fast as possible, which may require the creation of some summary tables.
• StartmysqldUse Parameters--low-priority-updates. This will give the update operation a lower prioritySelect. In this case, in the preceding assumption, the secondSelectInInsertPreviously executed, and there is no need to wait for the firstSelect .
• ExecutableSET LOW_PRIORITY_UpdateS=1Command to specify that all update operations are put in a specified link. For details, see "14.5.3.1SETSyntax ".
• UseLOW_PRIORITYAttribute to reduceInsert,Update,Delete.
• UseHIGH_PRIORITYTo improveSelectStatement priority. For details, see "14.1.7SelectSyntax ".
• Starting from MySQL 3.23.7, you can startmysqldSpecify System Variablesmax_write_lock_countIt is a relatively low value, which can force temporary increase of the number of table inserts to allSelectOperation priority. It allowsWRITEAfter the lock reaches a certain numberREADLock.
• WhenInsert AndSelectYou can useMyISAMTable, which supports concurrentSelectAnd InsertOperation.
• When both insert and delete operations are performed on the same table,Insert DELAYEDIt may be useful. For details, see "14.1.4.2Insert DELAYEDSyntax ".
• When Select AndDeleteWhen a problem occurs during use together,DeleteOfLIMITParameters may be useful. For details, see "14.1.1DeleteSyntax"
• RunSelectUseSQL_BUFFER_RESULTHelps reduce the duration of the lock table. For details, see "14.1.7SelectSyntax ".
• Source code can be modified'Mysys/thr_lock.c'Only one queue is used. In this case, the write lock and read lock have the same priority, which may be helpful to some applications.

The following are some suggestions for MySQL locks:

• As long as a large number of update and query operations are not mixed in the same table, the current user is not a problem.
• RunLOCK TABLESTo improve the speed (a lot of update operations are put in a lock much faster than a lot of updates without a lock ). Splitting data into multiple tables may also be helpful.
• When MySQL encounters a speed problem caused by the lock table, it converts the table typeInnoDBOrBDBMay help improve performance. For more information, see "16InnoDBStorage Engine "and" 15.4BDB(BerkeleyDB) Storage Engine ".