0. Basic knowledge of

1. Relational database ：MySQL
   There is a connection between the data stored in the database （ one-on-one , One to many and many to one ）

1. Indexes （ use B and B+ Trees ）

1.1 Index alternative data structures

Data structure for fast query and retrieval of data , Common are B Trees ,B+ Trees and Hash

1. Hash surface Index ：
   1） advantage ： fast
   2） shortcoming ：Hash The question of conflict ,Hash Index does not support order and range queries .

2. B+ Trees ： Multiway balanced search tree
   1） difference ：B All nodes of the tree store keys , Also store data ;B+ The tree has only leaf nodes to store keys and data , Other nodes only store keys .
   2) The data is stored in the leaf node , Find less data .

3. B- Trees ：

4. AVL Trees ： Balanced binary trees
   1） The height difference of all node subtrees shall not exceed one , If it exceeds the limit, the rotation operation will be carried out
   2） Why not use red and black trees for indexing ？
   The depth of the tree is too large , And cause disk IO Read and write too often .

5. Red and black trees ： Self-balancing binary search tree , Weakly balanced binary tree
   1） Why not use red and black trees for indexing .
   Red and black trees often appear because the depth of the tree is too large , And cause disk IO Read and write too often .

6. Red and black tree AVL The difference between trees ：
   Mangrove Query performance is slightly inferior to AVL Trees , Because he is more than avl The trees will be slightly unbalanced up to one level , That is to say, the query performance of red black tree is only better than that of the same content avl A tree can be compared at most , however , Red and black trees explode on insert and delete avl Trees , avl A lot of balancing is done with each insertion and deletion of the tree , And the cost of the red-black transformation and rotation of the red-black tree in order to maintain the red-black property , Compare with avl Trees are designed to maintain balance The cost is much smaller

1.2 Type of index

1. Index type
   1） primary key （ Clustered index ）： Index structure and data are stored together
   2） Secondary indexes （ Secondary index , Nonclustered indexes ） Index structure and data are not stored together .

2. Business

1. Business ： It's a logical set of operations , Or both , Either not
2. Database transactions ： Ensure that multiple operations on the database form a logical whole , Or it all works , Or none at all

// Start a transaction 
START TRANSACTION
SQL1,SQL2
// Commit transaction 
COMMIT

1. The transaction of relational database has four characteristics ：
   1） Atomicity ： Transactions are the smallest unit of execution , Division is not allowed
   2） Isolation, ： Concurrent access to database , A user's transaction will not be disturbed by other transactions
   3） persistence ： A transaction is committed , Changes to the database are persistent .
   4） Uniformity ： Before and after the execution of the transaction , Data consistency （ Transfer accounts , No less money ） 【 The first three guarantees , The last one can guarantee 】

2. The implementation principle of the four characteristics of transaction ：
   1） Atomicity ：undo log( Rollback log ）
   2） persistence ：redo log( Redo log ）
   3） Isolation, ： Locking mechanism ,MVCC Guarantee .
   4） Uniformity ： The first three meet , Nature guarantees .

3. Problems caused by concurrent transactions ：
   1） Dirty reading ： A transaction to modify the database , Not yet submitted , Access from another transaction , The extra data is dirty data .
   2） Missing changes ： Two transactions modify one data at the same time , Then the data modified by the first transaction will not be saved .
   3） It can't be read repeatedly （ modify ）： A transaction reads a data in the database multiple times , Between reading data , Another transaction has occurred to modify the data , The two data read by the first transaction are inconsistent , Then become unrepeatable .
   4） Fantasy reading （ Add or delete ）： Similar to nonrepeatable reading , When a transaction reads the contents of the database , Another transaction inserts the database , This makes the first transaction read more data for the second time , Like an illusion .

4. The level of transaction isolation ：
   1） Read uncommitted
   2） Read committed
   3） Repeatable
   4） Serializable ：（ optimal ）

5. MySQL The default isolation level for is ： Repeatable .

3. Storage engine

Storage engine ： How to store data 、 How to index and update stored data 、 Query data and other technologies . Determine the functions and performance provided by the database .

There are two mainstream storage engines ：MylSAM and InnoDB（ Transactional database ）.

1. Comparison of two storage engines
   1） Do you support Row-level locks
   2） Do you support Business
   3） Whether it supports foreign keys and database exceptions Safe recovery after crash
   4） Do you support MVCC（ An upgrade of row level lock ）

4. Locking mechanism

1. MylSAM and InnoDB The lock used by the storage engine
   1）MylSAM： Table lock , A lock , Just lock a watch
   2）InnoDB Row level lock and table level lock are supported , The default is row level lock

2. Difference between row level lock and table level lock ：
   1） Table lock ： A lock with the largest granularity , Lock the whole table of current operation .（ No deadlock ）
   2） Row-level locks ： A lock with the smallest granularity , Lock only the row of the current operation （ Deadlock ）

3. Inno There are three lock algorithms for storage engine
   1）Record lock: Record locks
   2）Gap lock: Clearance lock , Lock a range , Not including the record itself
   3）Next-key lock: Temporary lock

Reference article ：

link ：http://www.zhihu.com/question/43744788/answer/98258881