[MYSQL] Pure dry goods! Interview question: InnoDB! Fully resolved! Recommend collection!

InnoDB architecture

The following figure is the InnoDB storage structure given by the official document

Insert picture description here


InnoDB's log-first strategy processes data in the order from memory to disk.

The advantages are as follows

  • After the operation in the memory is completed, it can return to success, which is highly efficient
  • Even if the data falls back to the disk and the power is interrupted, it can be retrieved through the redo log
  • InnoDB's cache pool is implemented through page linked lists, which is highly efficient

The storage in InnoDB's disk has the following characteristics, I simplified it directly to highlight the key points, so that everyone can remember

  • Limitations of InnoDB tables: a table can have up to 64 auxiliary indexes, a row size of up to 65535, and a composite index can have up to 16 fields
  • Table space
  • System tablespace
  • File-Per-Table table space
  • Undo tablespace
  • Temporary table space

index

InnoDB stores indexes through index-organized tables.

The primary key index in InnoDB uses B+ index

Features of B+ tree

  • Each non-leaf node only stores key values, not data
  • The general height is 2-4 layers, so the search speed is very fast

Auxiliary indexes are called non-clustered indexes. The auxiliary index stores the value of the primary key. When the value of the primary key is obtained, the process of looking up data is called returning to the table.

If someone asks you to return to the table, you can follow the logic of returning to the table-auxiliary index-index.

A covering index means that the auxiliary index can find the column you are looking for without returning to the table.

The order of the joint index is very important, so we need to pay attention to the order of the columns when creating the index.

Affairs

ACID

  • Atomicity: Atomicity
  • Consistency: Consistency
  • Isolation: Isolation
  • Durability: Durability

There are 4 transaction isolation levels, and InnoDB supports REPEATABLE READ by default.

InnoDB implements transactions through redo logs, which are physical operation logs stored on disk.

Binlog is a logical log that maintains log consistency in memory.

Dirty read refers to the data inconsistency caused by another transaction to read the data before one transaction is committed.

The problem of phantom reading is that the same SQL is executed multiple times in a transaction, and the result set is different.

lock

Innodb supports row-level locks.

Row-level locks are divided into shared locks and exclusive locks.

  • Shared lock (S lock), can only be read, not modified
  • Exclusive lock (X lock), neither readable nor writable

Intention Locks

  • Intention shared lock (IS), the lock that needs to be obtained before adding row shared lock
  • Intent exclusive lock (IX), you need to get the lock before adding exclusive lock

Row lock storage structure

typedef struct lock_rec_struct        lock_rec_t
struct lock_rec_struct{
    ulint space;    /*space id*/
    ulint page_no;  /*page number*/
    unint n_bits;   /*number of bits in the lock bitmap*/
}

Locking logic: lock-unique index conflict check-judge Gap lock or Next-Key lock-add X lock

Interview focus

  • Basic query syntax
  • index
  • lock
  • The storage structure is recommended to bring out some points when indexing, to create some highlights for yourself