Detailed explanation of MySQL — knowledge sorting

Time:2021-12-31

[continuously update, organize and use by yourself]
B-tree and B + tree

  • The most easy to understand introduction of b-tree index – xiaohouye – blog Garden (cnblogs. Com)
  • B-tree non leaf nodes are composed ofKeyword + data, so data can be obtained during traversal.And B+All tree nodes will appear in leaf nodes, and non leaf nodes only have keywords,The data is all in the leaf node. And there are pointer connections between leaf nodes
  • stayThe smallest unit of data stored on a computer disk is a sectorThe size of a sector is 512 bytes, andfile system(e.g. XFS / ext4) his smallest unit isblock, the size of a block is4k, and for ourInnoDBThe storage engine also has its own minimum storage unit——page(page), the size of a page is16K
  • Why does relational database B + tree store data[1. Each node of B-tree needs to store (keyword + data), while B + tree only stores keywords; the size of each page is limited, so in the same page,B + tree can store more data。 2. B + tree can find keyword data throughTraverse the linked list to easily find the specified rangeKeywords]
  • InnoDB also has the concept of pages. The default page size is 16K, that is, 4 * 4K each time data is read.。 databasePage splittingAs follows: the data of 1-10 is originally stored in page1,Only one pageLater, because 11 was added, one page could not fit and needed to be split.Splitting is to copy the original page to a new page, and then set the original page as the root node to become a page that does not store data, but only stores keywords, and then create a new page to put the extra 11. There are three pages after division.
  • Copy the data of the original root node to a new page instead of directly creating a new page as the root node, because if the root node is re created, the physical address stored in the root node may often change, which is not conducive to searching. And inThe root node in InnoDB will be pre read into memoryYes, so it’s better to fix the physical address of the node!