Page Page

InnoDB data page structure

record

Record header information

  • deleted_flag
    • Logic to delete
    • Delete operation:
      1. Set deleted_flag=1 (0 is not deleted)
      2. Delete the records, to form a garbage linked list. (Purpose: Reusability of space)
  • min_rec_flag
    • B+ tree identifies the smallest directory entry record in each level of the non-leaf node
  • n_owned
    • Divide a page into groups, and the group leader (the group with the largest primary key) holds the value, which indicates how many records there are in a group
  • heap_no
    • Heap_no =2 infimum heap number is 0, superMun heap number is 1
  • record_type

    • Record type

      1. Ordinary record

      2. B+ tree each layer of non-leaf node directory entry records

      3. Represents a record of Infimun

      4. Represents superMUN records

  • next_record

    • Next record. The distance from the current record’s real data to the next record’s real data

Page Directory

  • Record characteristics at the lowest level
    1. Sort by primary key from smallest to largest
    2. Singly linked list
  • A catalogue of books
  • Rules for grouping
    1. For infimum records, there can only be one record in the group, which is himself
    2. For supermum, only 1 to 8 records are needed in the group
    3. For other records, you only need to have 4-8 records in a group
  • Step of grouping
    1. Initially, there were no records. A new data page had two groups, the Infimum group had one record; The other is Supermum group a record
    2. When inserting data, add one to the supermum group, n_owned
    3. Supermum group is full, split the group into two groups (4+5), apply for a new slot
  • Deleting a record
    • Delete_flag sets the value to 1
    • The next_record pointer to the previous one points to the next one
    • Supermum n_owned – 1
  • What is the process of querying?
    • Find the slot first
    • Start with the smallest next_record in the group

B & B + tree tree

The index

  • Cluster index Primary key index (B+ tree)
  • Secondary index non-primary key (add index to non-primary key), find the corresponding primary key, perform table back operation, and search data by primary key
  • Joint index

Buffer Pool Buffer Pool

  • The cache
  • Index, data –> page –> on disk
  • Look up a record but we want to cache all the pages of that record in memory
  • If it’s not on one page, there’s a lot of IO

Overview of buffer pools

  • Control blocks and buffer pages correspond one to one
  • The free list
    • To allocate space, take pages that need to be cached, get control blocks, and cache pages that need to be cached
  • Flush the list
    • Place the control block corresponding to the modified buffer page in the Flush linked list for all dirty pages
  • When are dirty pages flushed to disk?

    • Flushes a portion of the page from the Flush list to disk

      1. There are threads in the background that determine the refresh rate according to how busy the system is. BUF_FLUSH_LIST
      2. The system is very busy, so it is very slow to refresh dirty pages to disk. If there are no available buffer pages, we will check the end of the LRU list to see if there are unmodified buffer pages that can be directly released. If there are no buffer pages, we will have to synchronously refresh a dirty page at the end of the LRU list to disk. BUF_FLUSH_SINGLE_PAGE

    • Flusher a portion of the page to disk BUF_FLUSH_LRU from the cold data summary of the LRU list

      1. When querying, place used data at the head of the linked list
      2. The page will be placed in cold data when prereading
      3. Full table scan does not flush out hot data areas. You can configure full table scan to query a page multiple times within XXX milliseconds

    • A bufferpool has multiple chunks. When changing the size of a bufferpool, you can create a chunk to replicate the original chunk