The lock on the Mysql

The lock in the database is based on the index, hitting the index is a row lock, not hitting is a table lock.

  • By property: Shared lock, exclusive lock
  • According to the granularity: table lock, row lock, gap lock, record lock, key lock
  • As intended: intended shared lock, intended exclusive lock

Share S lock

After a transaction is enabled and a read lock is added to the record, other transactions can only add a read lock to the record, but not a write lock. The write lock can be acquired only after the transaction is completed. Mainly to support high concurrency access.

EXclusive X lock

Add an exclusive lock and neither read nor write is allowed to avoid the problem of dirty read data.

Table level lock

A table lock locks the entire table when it is locked. When the next transaction accesses the table, it can access the table only after the previous transaction releases the lock. Features: large granularity, simple locking, easy to conflict;

Row-level locks

When a row is locked, one or more rows of the table are locked. When other transactions access the same table, only the locked records cannot be accessed, and other records can be accessed normally. Features: small granularity, locking is more troublesome than table lock, not easy to conflict, compared with table lock support concurrency is higher;

Record locks

Record Lock also belongs to a row Lock, but the scope of the Record Lock is only a table of a Record, Record Lock is to say that the transaction is locked after a table of a Record. Trigger condition: accurate condition hit, and hit condition field is unique index; For example, update user_info set name= ‘user_info’ where ID =1, where ID is the unique index. Functions of the record lock: After adding the record lock, the data can avoid the repeated read problem that the data is modified during the query, and avoid the dirty read problem that the data is read by other transactions before the modified transaction is committed.

Clearance lock

To prevent phantom reading problems, a left open and right closed interval is formed if there is a gap between table ids. For example, 1, 5, 6, and 11 will form a gap space of – infinity to 1, 1 to 5, 6 to 11. If the index is queried using this interval, the index within this interval will be locked and cannot be modified to prevent insert phantom problems

Key in the lock

The associated gap is locked with the next interval.

Intent locks

Before adding shared and exclusive locks, you need to try to obtain intentional shared locks and intentional exclusive locks. The intent lock is equivalent to adding a tag to indicate whether a resource is used at a lower level. The execution of Step 2 can be greatly accelerated, that’s all. 1) When transaction A locks upward, it first locks A freehand direction on the table. 2) transaction A writes A lock on row R. Let’s assume A scenario where transaction A is writing A write lock on row R of some table T, and another transaction B is trying to write A lock on the entire table (for example, when modifying the table structure). In this case, you need to perform two steps:

1) Check whether T is locked by read or write. 2) Check whether the table is locked with intent (because intent locks are used before all rows are read or written) to determine whether any row in the table is locked with read or write. Step 2 Check the time complexity of the logic. After the intentional lock is added, the time complexity increases from O(n) to O(1), greatly improving the efficiency. In addition, from this logic, read and write intent lock, the two are compatible with each other, when I read this compatibility relationship between them, I have not quite understood, did not understand the point.

Classification of indexes

MVCC mechanism

MVCC, called multi-version concurrency control, enables concurrent data access in the database.

  • The current reading \

Like select lock in share mode, select for update; Update, INSERT,delete(exclusive lock) operations are a type of current read, why are they called current read? That is, it reads the latest version of the record while ensuring other concurrent transactions

  • The snapshot read \

Snapshot reads are unlocked to resolve read-write conflicts. At the same time, it solves the problem of transaction isolation such as dirty read, unreal read and unrepeatable read, but it cannot solve the problem of data update loss.

Implicit field

  1. DB_TRX_ID
  2. DB_ROLL_PTR

DB_TRX_ID (the transaction ID from which the record was last modified), DB_ROLL_PTR (which points to the value of the record that has not been modified), and DB_ID (the implicit primary key, which automatically creates a 6-byte primary key if no primary key is created).

UndoLog

Update undoLog, generated during an update operation, can be used for transaction rollback and snapshot reads.

If a transaction modifies the record,After modification again

Read View

A snapshot view of a record that is read by a transaction to control which undolog phases of the record can be seen by the transaction.For example, at the moment transaction 2 accesses the record, which is eventually modified by transaction 4. Enter logical judgment.

// Active transaction List<Work> activeList // Minimum active transaction ID Integer minWorkID //ReadView generates the next transaction ID that has not been allocated by the system at the time. +1 Integer nextWorkID Changes_visible (work,workID) {// Work4 workID = 4 if(workID < minWorkID) } else if(workID >= nextWorkID){// Not visible,work after ReadView generated} else If (activelist. contains(work)) {// not visible} else {// visible}}Copy the code

4<nextWorkId, which means that 4 was committed before ReadView was formed. If not in activeList, 2 will see the latest record.

Conclusion MVCC

Tell us what you understand about MVCC

MVCC is a snapshot access strategy based on implicit fields, undoLog, and Read View.

A rollback pointer to the last unmodified record is used for the rollback of the last unmodified record. Undolog: After each transaction updates a record, a record is generated, which records the updated content and all previous record states.

When a transaction accesses a record at a certain time, a ReadView is generated, which records the currently active transaction and the smallest transaction ID in the currently active transaction. The ReadView generates the next transaction ID not allocated by mysql at that time. If 4<minActiveID, it indicates that the transaction was committed after 4. If 4>minActiveID continues to determine whether 4 is greater than the unassigned next transaction ID, if yes, 4 appears after the readView is formed and the record is not visible. 4<minActive Determines whether the transaction is active. If it is active, the transaction has not been committed and the change record is not visible. If it is inactive, the transaction has been committed and is visible.

Create high-performance indexes

An index is equivalent to the table of contents of a book. If you want to find a specific topic in a book, you usually start with the index of the book and find the corresponding page number.

Separate columns

Columns do not contain operands

mysql> 
SELECT 
actor_id 
FROM sakila.actor 
WHERE actor_id + 1 = 5
Copy the code

Prefix index and index selectivity

select 
COUNT(DISTINCT LEFT(city,3)) /COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city,4)) /COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city,5)) /COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city,6)) /COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city,7)) /COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city,8)) /COUNT(*) AS sel3,
From table
Copy the code

When the prefix reaches a value where subsequent SEL changes are no longer apparent, this value can be used as the length of the index

// Create a prefix index
mysql> ALTER TABLE sakila.city_demo ADD KEY (city(7));
Copy the code

Compound indexes

Don’t let the index_merged situation happen

Select the appropriate index order

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584
Copy the code

When creating the index, place the higher resolution staff_id first

mysql> ALTER TABLE payment ADD KEY(customer_id,staff_id);
Copy the code

Considerations for using indexes

1.Speed up indexing by building indexes on frequently searched columns2.Create sequential indexes on columns that frequently use WHERE to speed up conditional judgment3.Create indexes on columns that often need to be sorted to take advantage of index sorting to speed up sorting query times4.Speed up links by creating indexes on frequently connected columns5.Do not add a function to a field in a WHERE clause, as this will cause the index to fail to hitCopy the code

Select indexes and write query principles that leverage those indexes

  1. Read blocks containing as many rows as you need
  2. Sequential access to range data is very fast. First, sequential I/O does not require multiple disk seeks, so it is much faster than random I/O. Second, if the server can read the data in the order it needs it, no additional permutation is required
  3. Try to make index coverage

Left-most prefix rule

An index in Mysql can refer to multiple columns in a certain order, which is called a federated index. For example, the User table name and city with the joint index is (name, city), and the left-most prefix principle refers to the condition when the query matches the left consecutive column or columns of the index, then this column can be used.

Avoid redundant indexes

After Mysql5.7, redundant indexes can be viewed by querying schame_REDUNdANT_INDEXES in the SYS library

The difference between primary keys and unique indexes

  1. There can be only one primary key index and multiple unique indexes
  2. The fields of a primary key index must be NOT NULL, and the values of a unique index can be NULL
  3. Columns in primary key indexes can be used as foreign keys, while unique indexes are not
// Primary key index
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)
// Unique index
ALTER TABLE `table_name` ADD UNIQUE (`column`)
// Add a normal index
ALTER TABLE `table_name` ADD INDEX index_name(`column`)
// Add a multi-column index
ALTER TABLE `table_name` ADD INDEX index_name (`column1`,`column2`,`column3`)
Copy the code

The difference between a unique index and a normal index

  • If a normal index finds an item that matches the conditions, the query continues
  • The unique index is stopped when it finds an item that matches the criteria
  • Query performance similarity
  • Normal indexes can use ChangeBuffer. When updating data, if the data page is not in memory, you can first update the data page to change Buffer. The next time you need to access the data page, the data page is read into memory, and then execute. Merge Change Buffer to reduce disk I/O counts. Merge changes buffer into raw data pages is called merge. Merge is triggered when a database is shut down, a common index is accessed, or over a period of time

How is mysql atomicity implemented

RedoLog (Persistence)

Mysql for performance does not put every time modify real-time synchronization to disk, but the cache to cache first, and then use the thread to do the buffer pool and disk synchronization, if all of a sudden power failure can be a problem, so also will be lost without persisted to disk information, transaction commit all the information stored in the redo Log, The redo Log is persisted to disk and is reexecuted when the computer restarts

UndoLog (atomicity)

Undo Log is called rollback Log. Every change of data generates a record and is persisted to disk. UndoLog records a statement to delete this information

Application scenarios of MySIAM

MyISAM does not require high transaction consistency and is mainly used to perform query operations, such as logging