Database structure

Databases and instances

MySQL is designed as a single-process multithreaded database

A MySQL database instance is represented as a process on the system

The database

A collection of physical operation file systems or other forms of file types

The instance

The MySQL database consists of background threads and shared memory space

component

  • The connection pool
  • Management service tools
  • SQL interface
  • Query analyzer
  • The optimizer
  • The buffer
  • Storage engine
  • Physical file

structure

process

The connection

TCP/IP

Named pipes and shared memory

UNIX domain socket

Storage engine

InnoDB

architecture

  • memory
    • Multiple memory blocks
    • Jointly maintain internal data structures accessed by processes and threads
    • Caching disk data
    • The redo log buffer
  • A background thread
    • Multiple threads are responsible for different tasks
A background thread
Master Thread

The core background thread is mainly used to asynchronously refresh the data in the buffer pool to the disk to ensure data consistency, including the refresh of dirty pages, merge and insert cache, and UNDO page recycling

IO Thread

Responsible for the callback of IO requests

Purge Thread

Reclaim used and allocated UNDO pages

Page Cleaner Thread

Dirty page refresh

memory
The buffer pool

InnoDB stores on disk and manages records on a page basis. Improve database performance with buffer pools

Changes are now made in the buffer pool and flushed to disk at a regular rate

The Checkpoint mechanism refreshes disks

Data types include

  • Index page
  • Data page UNDO page
  • Insert the buffer
  • Adaptive hash index
  • Lock information,
  • Data dictionary information
redo log

InnoDB’s redo log is fixed in size. For example, it can be configured as a group of 4 1GB redo logs that record 4GB of operations.

The distance between write POS and check point is the space left over by the redo log

bin log

Server logs (archive logs)

  1. Redo logs are unique to InnoDB; Bin log is implemented in the Server layer of MySQL and is available to all engines.
  2. A redo log is a physical log of what was changed on a data page. Bin log is a logical log that records the original logic of this statement, for example, “Add 1 to the C field in the line ID=2”.
  3. Redo logs are written in a redo log cycle. The bin log can be appended to. Appending indicates that the bin log file changes to the next one after being written to a certain size and does not overwrite the previous log.
uodo log
checkpoint

In The InnoDB design, page operations are first done in the buffer pool, and the data pages in the buffer pool are different from the data pages on disk, which are called dirty pages

The rodo log is written first when the transaction commits, write-Ahead Logging. The key point of this Logging is to Write the log first and then to disk

Resolved:

  • Shorten the database recovery time
  • Write dirty pages to disk to reduce buffer pool storage stress
  • Flush dirty pages when redo logs are not available

What might happen

  • Redo log wrote
    • In this case, the database stops executing update statements and the performance decreases to zero
  • Insufficient memory, too many data pages
  • Brush in at leisure
  • The database closes the brush
LRU List, Free List, and Flush List

LRU List

Least recently used algorithm

The list of pages is sorted by number of uses

Remove the last page and insert it in the middle of the list.

Free List

features

  • Insert the buffer
  • The second to write
  • Adaptive hash index
  • The proofs

file

Parameter file

The log file

The socket file

The pid file

MySQL table structure file

Save engine files

table

Index organization table

In InnoDB, tables are organized by primary key order, called indexed organized tables

Data is index and index is data

Primary key Settings:

  • Explicitly define
  • Whether there is a non-empty unique index
  • Automatically creates a pointer of 6 bytes size

The priorities are from top to bottom

Logical storage structure

Logically, all data is stored in a table space

Table Spaces consist of segments, extents, and pages

Table space

The highest level of logical storage structure in InnoDB

Period of

Data segment, index segment, rollback segment

Index segments are the leaf nodes of the B+ tree

area

Continuous page composition is 1MB in any case

To ensure page continuity in extents, InnoDB storage engine requests 4-5 extents from disk at a time.

A typical page size is 16KB, consisting of 64 consecutive pages in a range

page

InnoDB is the smallest unit for managing disks and the basic unit for interacting with memory

The default is 16KB, which can be set to 4KB or 8KB with innodb_page_size

  • Data page (B-tree Node)
  • Undo pp.
  • System also
  • Transaction data page
  • Insert buffer bitmap page
  • Insert buffer free list page
  • Uncompressed binary large object page
  • Compressed binary large object page

line

Data is stored row by row

Line record format

Compact

Redundant

Data page structure

The constraint

view

The partition table

The index

Common indexes

The data structure

The lock

The transaction