1. Buffer pool:

  • All operations to the database first modify pages in the buffer pool and then write to disk storage at an appropriate checkpoint.
  • The size of the buffer pool determines database performance.
  • Buffer pools are managed by LRU algorithms. The most used pages are at the front and the least used pages are at the back. When the buffer pool cannot store new pages, the trailing pages are cleaned first. The default page size is 16KB,
  • Innodb optimizes the LRU algorithm by adding midpoint locations to the LRU list. Each newly accessed page is placed into the midpoint location, which is 5/8 by default. The default LRU algorithm inserts directly into the header, but inserts in the middle to increase the longevity of popular pages. Avoid a large number of SQL queries resulting in high – heat pages are brushed out

2. Write twice:

In order to avoid downtime, the database happens to write to a page list, resulting in page contamination, but using redo log and no clean page can be directly rewritten. So mysql updates dirty pages using a double write solution.

  • Two writes have two additional different storage addresses. One is the doublewirte buffer for memory and the other is the shared space on physical disk, both 2MB and exactly 128 pages.
  • When flushing dirty pages from the buffer pool, the dirty pages are first copied to buffer. The contents of the buffer are then written to the shared memory space in two sequential steps. The disk is then synchronized from buffer. The whole process is sequential. If an exception occurs, you can restore shared space data to tablespace files and use redo logs to restore the data.

3. Index :(a separate article will be written at the end of the B+ tree to compare and contrast with other data structures)

InnoDB has 3 indexes: B+ tree index, Hash index, full text index B+ index has high fanout, the height of the tree is usually 2-4 levels

3.1 Adaptive Hash Index:

  • The hash index is created by the system and cannot be manually created
  • Through the cache pool B+ tree structure to build

3.2B+ Tree Index:

3.2.1 Purpose (Why B + Tree is used) :

  • B + tree can effectively reduce the tree level and the NUMBER of I/O times during query under the same amount of data
  • Compared with red-black trees, nodes of B + trees are stored according to the range, which effectively improves the query efficiency
  • Compared with balanced binary trees, B + trees do not need to use a lot of processing to maintain the optimal, while ensuring efficient query

3.2.1 Aggregation Index:

  • The index created by the primary key
  • The data inside the leaf node is the corresponding row data
  • Due to the use of bidirectional chain between leaf nodes. So range lookup is very convenient

3.2.2 Secondary Index:

  • Indexes are built by setting values
  • The reference, which is the corresponding primary key, is saved by bookmark on the leaf node. So the query flow is to go through the secondary index tree and then the aggregate index tree. IO times is the sum of the heights of the two trees

3.2.3 Joint Index:

Lists all key combinations in the same order as the key that created the joint index. For example index(a,b) -> (1,1) (1,2)(2,1)(2,2) (2,2) If you use where A=XXX and B=XXX & where A=XXX, you can use this joint index. But where b= XXX cannot be used. The combination of associative indexes actually affects a, B, C… And so the union key values are sorted. Where a= XXX order by b

3.3 Full-text Index:

To be added

Mysql InnoDB engine (2) lock

Mysql InnoDB engine (3