wwz_ henu 2022-02-13 05:26:29 阅读数:23
One . Logical architecture
The storage engine does not parse SQL(InnoDB With the exception of , Can parse foreign key definitions ,Mysql The server itself does not have this function ), Different storage engines will not communicate before , The upper server is just a simple request .
Mysql Will optimize queries , Create internal data structures ( The parse tree ), Then optimize it , Including rewriting queries 、 Determine the read order of the table 、 Choose the right index, etc . You can use special keywords (hint) Influence the optimization process , have access to (explain) See how the server is optimized .
about SELECT sentence , The query cache is checked before parsing the query , If there is a cache, directly return the result set .
Two . concurrency control
Read the lock : share , Don't block
Write lock : exclusive , Block other read and write locks
Table locks :Mysql The most basic lock strategy in , The cost is minimal .
Row lock : Only in InnoDB and XtraDB Wait for Engine Implementation ,MySQL The server layer is not implemented . The server layer has no idea about the lock implementation in the storage engine .
3、 ... and . Business
A transaction is a set of atomic SQL Inquire about , Or a separate unit of work .
Transactions need to be implemented by the system ACID.
A: Atomicity
A transaction must be treated as an indivisible minimum unit of work .
C: Uniformity
Database consistency , That is, before the transaction is finally committed , Data will not be modified .
I: Isolation,
Before a transaction is finally committed , Is invisible to other transactions .
D: Continuity
Once submitted , The changes you make will be permanently saved to the database , Even if the system crashes , And the data won't be lost .
Isolation level :
Changes in transactions , Even if it's not submitted , It's also visible to other things .
Dirty reading : Transactions can be read as committed data .
Solved the problem of dirty reading .
It ensures that the same record is read multiple times in the same transaction, and the result is the same . Can't solve unreal reading .
Fantasy reading : When a transaction is reading a range of records , Another transaction inserts a new record in this scope , When the previous transaction reads again , There will be illusions .
InnoDB and XtraDB adopt MVCC This problem has been solved .
This level is MySQL Default transaction isolation level .
The highest isolation level . By forcing transactions to execute serially , Avoid unreal reading . May cause timeout and lock contention problems .
Deadlock : Two or more transactions occupy each other on the same resource , And ask to lock the other party's resources , Which leads to a vicious circle .
MySQL In the affairs :
Automatic submission , If not shown, start a transaction , Each query is submitted as a transaction .
SHOW VARIABLES LIKE 'AUTOCOMMIT'; # View the current auto submit status 1/ON For opening ;0/OFF To close
SET AUTOCOMMIT=1; # Set auto submit status
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; # Set the transaction isolation level , The next transaction takes effect
When AUOTCOMMIT by 0 when , All queries are in one transaction , Until explicit COMMIT or ROLLBACK, The transaction ends , At the same time, a new transaction is opened . modify AUTOCOMMIT No impact on non transactional tables .
There are some commands that are enforced before they are executed COMMIT Current active transaction , Such as ALTER TABLE,LOCK TABLE etc.
The data tables of different storage engines in the same transaction are unreliable .
InnoDB Adopt two-stage locking protocol : Locking can be performed at any time in a transaction ; Release all locks when the transaction is committed or rolled back .
Four .MVCC
By saving a snapshot of the data at a certain point in time . No matter how long it takes , Each transaction sees the same data . Only in REPEATABLE READ and READ COMMITTED Two isolation levels are in effect .
InnoDB Of MVCC It is realized by two hidden columns after each row of records . Creation time of a saved row , Expiration time of a save line ( Or delete the time ). It's not the time value that's stored , It's the system version number . Didn't start a new business , The system version number will be incremented . The system version number at the start of the transaction will be the version number of the transaction , Used to compare with the version number of each row of records found . stay REPETABLE READ Under isolation level ,MVCC Do this :
InnoDB Check each line of records according to the following two conditions :
5、 ... and . Storage engine
Conversion table engine :
ALTER TABLE table1 ENGINE=InnoDB;
copyright:author[wwz_ henu],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130526256552.html