MySQL learning notes -- Architecture and history

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 ：

• READ UNCOMMITTED Uncommitted read

       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 .

• READ COMMITTED Submit to read

       When a transaction begins , You can only see the changes made by the submitted firm . It can also be called non repeatable , Because the same query is executed twice , May get different results .

• REPEATABLE READ Repeated reading

      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 .

• SERIALIZABLE Serializable

     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 ：

• SELECT

     InnoDB Check each line of records according to the following two conditions ：

1. Only find data rows older than the current transaction version , That is, the system version number of the row is less than or equal to the transaction version number , Ensure that the rows read by the transaction , Or it already exists before the transaction starts , Either the transaction itself is modified or inserted ;
2. The deleted version of the line is either undefined , Or greater than the current transaction version , Make sure that the read rows are not deleted before the transaction starts .
   

• INSERT

      Save the current system version number as the line version number for the newly inserted line .

• UPDATE

      Insert a new line , Save the current system version number as the line version number ; At the same time, save the current system version number to the old line as the deletion ID .

• DELETE

      Save the current system version number for the deleted line as the deletion ID .

5、 ... and . Storage engine

• InnoDB

     Support transactions , Row-level locks ; Designed to handle a large number of short-term transactions ; Automatic crash recovery ; use MVCC Handle high concurrency ; The default isolation level is REPEATABLE READ, Prevent unreal reading through gap lock ; Cluster based indexing , High performance for primary key query ; Secondary indexes ( Non primary key index ) Must contain primary key index , If the primary key index is large , Other indexes will be big ; Storage format platform independent ; Support hot backup ;

• MyISAM

     Unsupported transaction ; Table lock ; Full text index support 、 Compress 、 Space function, etc ; You can't fully recover after a crash ;MYD Store the data ,MYI Storage index ; Use myisampack Pack the table , If you need to modify, you need to unzip ; After compression, the occupied space is reduced , Support the index ; Delay write , Write input to memory , Wait for the system to write to disk ;

• Built in other engines

1. Archive  Only support INSERT and SELECT, Use zlib Compress insert row , Than MyISAM surface IO less ;SELECT Will scan the whole table ; Suitable for log and data collection applications ; Support row level locks and private buffers , Achieve high concurrency insertion ; In a SELECT After start , Other queries are blocked ; Unsupported transaction ;
2. Blackhole No storage mechanism is implemented ; Discard all inserted data , No preservation ; The server will record Blcakhole journal , It can be used to copy the database or just log ;
3. CSV Support CSV The file is treated as a data table ; Index not supported ; When the database is running CSV Copy files in or out ; take CSV Copy files to database directory , That is, available MySQL Handle , Yes CSV Handling of documents , Other software can also immediately read ;
4. Federated  Access to other MySQL A proxy for the server , A client link to the remote server will be created , Pass the query to remote execution , And get the result set ; Default disabled
5. Memory  All data is stored in memory ; Database crash , Data loss ; Support hash Indexes , Fast query speed ; Table lock , Low concurrent write performance ; I won't support it BLOG/TEXT Column , The length of each line is fixed ; Even if you specify VARCHAR It's going to be char Waste of space ; The temporary table used internally is Memory, If it exceeds the limit or contains BLOB/TEXT Column , Will turn into MyISAM
6. Merge  MyISAM Variants ;Merge The table consists of multiple MyISAM Virtual table from table merging ; Used for log or data warehouse applications ; After the introduction of partition function , This engine was abandoned ;
7. NDB  Cluster storage engine

• Engine selection Business ; Backup ; Crash recovery ; Unique characteristics
  

Conversion table engine ：

ALTER TABLE table1 ENGINE=InnoDB;