Catalog

Infrastructure ： One SQL How query statements are executed

Log system ： One SQL How update statements are executed

The transaction isolation ： Why do you change? I can't see

Index in simple terms

Global lock and table lock ： How can adding a field to a table be so much of a hindrance

The merits and demerits of line lock ： How to reduce the impact of row locks on performance

---

Infrastructure ： One SQL How query statements are executed

First step , The connector , Connect to database

It can be divided into long connection and short connection , Long connections are recommended , However, long connections will take up too much memory with the execution process , The solution is as follows ：

• Regularly disconnect long connections
• stay 5.7 Use after version mysql_reset_connection To reinitialize the connection resources

The second step , The query cache , Direct return if there is a cache

Caching is not recommended （ take query_cache_type Set to DEMAND）, Because as soon as the table is updated , Will empty the cache , Hit ratio is low

The third step , analyzer , Do lexical analysis and grammatical analysis

Used to judge SQL Is the statement correct

Step four , Optimizer , Choose the right index

Step five , actuator

Judge whether you have permission to execute query , Loop fetch , Up to the last row of the table

---

Log system ： One SQL How update statements are executed

redo log（ Redo log ）	binlog（ Archive log ）
InnoDB Engine specific , Ensure that the records submitted before the abnormal restart of the database will not be lost （crash-safe）	Server Layer , All engines can be used
Physical log ,“ What changes have been made on a data page ”	Logic log ,“ to ID=2 Field of a+1”
Write in cycles , When you run out of space, update to disk	Additional writing , You can switch to the next file and continue writing , Will not cover

 

Binlog There are two patterns ,statement The format is to remember sql sentence , row The format records the contents of the line , Make two notes , Before and after the update .

Two stage submission ：redo log branch prepare and commit state , branch 2 Times completed

innodb_flush_log_at_trx_commit= 1 , For each transaction redo log All persist directly to disk

sync_binlog = 1 , For each transaction binlog All persistent to disk

---

The transaction isolation ： Why do you change? I can't see

ACID： Atomicity 、 Uniformity 、 Isolation, 、 persistence

There may be problems when multiple transactions are performed at the same time ： Dirty reading 、 It can't be read repeatedly 、 Fantasy reading

Isolation level ： Read uncommitted 、 Read the submission 、 Repeatable 、 Serialization

Consistency view ： Repeatable reads are created at the beginning of a transaction , The reading submission will be in SQL Create... At execution time

transaction-isolation The value of is set to READ-COMMITTED, Set the isolation level to read commit

When each record is rolled back, one record is updated at the same time , When no transactions are used in the rollback log , Delete

Do not use long transactions , There will be a lot of rollback logs taking up space

set autocommit=1, Automatic submission , Start a transaction by displaying a statement

---

Index in simple terms

InnoDB Use B+ Tree index model , Each index corresponds to one B+ Trees

primary key （ Cluster index ） The leaf node stores data

Philippine primary key index （ Secondary indexes ） The leaf node of stores the primary key value

The smaller the primary key , The smaller the leaf node of a normal index is , The smaller the space occupied by ordinary indexes

Back to the table ： Query statement based on non primary key index , You need to obtain the primary key through the non primary key index tree first , Then use the primary key to get data from the primary key index tree

Page splitting and merging ： In data insertion 、 During deletion , You need to move the data after the operation data line , This may require applying for a new or merged page

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

For the upper table , Execute the following query statement

select * from T where k between 3 and 5

The execution process is as follows ：

1. stay k Found on index tree k=3 The record of , obtain ID = 300;
2. Until then ID Index tree found ID=300 Corresponding R3;
3. stay k Index tree takes next value k=5, obtain ID=500;
4. Back to ID Index tree found ID=500 Corresponding R4;
5. stay k Index tree takes next value k=6, Not meeting the conditions , The loop ends .

If the executed statement is

select ID from T where k between 3 and 5

because ID Already in k On index , No need to go again ID Find data on the index , So you can return data directly , You don't have to go back to the table , This is called Overlay index

Leftmost prefix principle ： It can be the leftmost of the union index N A field , It can also be the leftmost of a string index M Characters

Index push down optimization ：MySQL5.6, During index traversal , Judge the fields contained in the index first , See if the conditions are met , So as to reduce back to the table

select * from tuser where name like ' Zhang %' and age=10 and ismale=1;

 

---

Global lock and table lock ： How can adding a field to a table be so much of a hindrance

MySQL The locks in the can be roughly divided into ： Global lock 、 Table lock （ Table locks 、 Metadata lock meta data lock,MDL）、 Row lock

Global lock , For data backup ：

Flush tables with read lock; (FTWRL)

Table locks ：

lock tables … read/write

unlock tables

Metadata lock ：MDL You don't need to explicitly use , When accessing a table, it will be automatically added , Generally, it is only used when row lock is not supported

When adding, deleting, modifying and querying a table , Add MDL Read the lock

When you change the structure of a table , Add MDL Write lock

Read locks are not mutually exclusive , So you can have multiple threads to add, delete, modify and query a table at the same time .

Read-write lock 、 Write locks are mutually exclusive , To ensure the security of the operation to change the structure of the table . therefore , If two threads want to add fields to a table at the same time , One of them can't be executed until the other has finished .

How to add fields to a small table safely

• Solve long affairs . The transaction does not commit , Will always occupy MDL lock , adopt information_schema Library innodb_trx The table allows you to view long transactions in progress
• stay alter table Statement to set the wait time

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

---

The merits and demerits of line lock ： How to reduce the impact of row locks on performance

Two stage lock protocol ： stay InnoDB Transaction , Row locks are added when needed , But it's not about releasing them immediately when they don't need to , It's about waiting until the end of the transaction .

If you need to lock multiple rows in your transaction , The most likely cause of lock conflict 、 The lock most likely to affect concurrency should be put back as far as possible

Deadlock ：

Business A Waiting for business B Release id=2 The row lock , Business B Waiting for business A Release id=1 The row lock . Business A And transaction B Waiting for each other's resources to be released

Two strategies deal with deadlocks ：

1. Waiting for timeout , Through parameters innodb_lock_wait_timeout Set up （ Default 50s）
2. Turn on Deadlock Detection , The parameter innodb_deadlock_detect Set to on（ Default on）

Deadlock detection ： Whenever a transaction is locked , Check whether the thread it depends on is locked by others , loop , Finally, judge whether there is circular waiting

Whenever a transaction joins , We should judge whether the deadlock is caused by our participation , This is a time complexity O（n） The operation of , If there is 1000 One transaction operates on one row at the same time , So deadlock detection is 100 Ten thousand , It can lead to CPU High utilization , But the number of transactions executed is very low

You can reduce concurrency by changing one row of data into multiple rows , For example, the total amount of company accounts , Can be placed in 10 In row data , this 10 The sum of the line data is the total amount of the company account , During operation, a row is randomly assigned for operation

---

Is the transaction isolated or not

begin/start transaction Command is not the starting point of a transaction , The first operation after they are executed InnoDB Statement of table , The business really starts .

If you want to start a transaction immediately , have access to start transaction with consistent snapshot This command

Business B Checked up k The value of is 3

Business A Checked up k The value of is 1

The updated data is read first and then written , And this read , Can only read the current value , be called “ The current reading ”（current read）

stay MySQL in , There are two “ View ” The concept of ：

One is view. It is a virtual table defined by a query statement , Execute the query statement at the time of the call and generate the result . The syntax for creating a view is create view … , And its query method is the same as the table .

The other is InnoDB In the realization of MVCC Consistent read view used in , namely consistent read view, Used to support RC（Read Committed, Read the submission ） and RR（Repeatable Read, Repeatable ） Implementation of isolation level

“ snapshot ” stay MVCC How does Li work

InnoDB Each transaction has a unique transaction ID, called transaction id. It is at the beginning of the transaction to InnoDB Of the transaction system , It is strictly increasing in the order of application

And each row of data has multiple versions . Every time a transaction updates data , Will generate a new data version , And the transaction id The transaction assigned to this data version ID, Write it down as row trx_id. meanwhile , Keep the old data version , And in the new data version , You can get information directly .