preface

Mysql database related flow chart/schematic diagram, make notes, we learn together.

1. Master/slave replication diagram of mysql

Mysql master-slave replication principle is a frequent interview question for dachang backend. It is necessary to understand the principle of mysql master-slave replication.

The master-slave replication principle, in short, consists of three steps, as follows:

  • The main database has a bin-log binary file that records all Sql statements. (Binlog thread)
  • Copy the SQL statements from the bin-log file of the primary database from the secondary database. (IO thread)
  • Execute these SQL statements again from the database relay-log redo log file. (Sql execution thread)

As shown below:

The master-slave replication above is performed in five steps:

Step 1: Update events (UPDATE, INSERT, DELETE) of the master library are written to the binlog

Step 2: Initiate a connection from the library to connect to the master library.

Step 3: The master library creates a binlog dump thread and sends the contents of the binlog to the slave library.

Step 4: After starting from the slave library, create an I/O thread to read the binlog content from the master library and write it to the relay log

Step 5: An SQL thread is also created to read from the relay log, execute the read update event from Exec_Master_Log_Pos, and write the update to the SLAVE DB

2. Logical architecture diagram of Mysql

If you can build an architectural picture in your mind of how the MySql components work together, it helps to understand the MySql server

Mysql logical architecture diagram is divided into three layers:

1) The first layer is responsible for connection processing, authorization, security and so on

  • Each client connection has a thread in the server process, and the server maintains a thread pool, so there is no need to create or destroy a thread for each new connection.
  • When the client connects to the Mysql server, the server authenticates the client using the user name, password, or SSL certificate.
  • Once the client is connected, the server continues to verify that the client has permission to execute a particular query.

2) The second layer is responsible for compiling and optimizing SQL

  • This layer includes query parsing, analysis, optimization, caching, and all of the built-in functions.
  • For the SELECT statement, the server checks the query cache before parsing the query. If the query result can be found in the cache, the server directly returns the query result without performing query parsing or optimization.
  • All functions across storage engines are implemented in this layer: stored procedures, triggers, views.

3) The third layer is the storage engine.

  • Storage engine is responsible for storing and extracting data in MySQL.
  • Storage engines communicate with the upper layer through apis that mask differences between storage engines and make those differences transparent to the upper layer query process.
  • The storage engines do not parse SQL, and the different storage engines do not communicate with each other, but simply respond to requests from the upper-layer server.

3.InnoDb logical storage structure diagram

From the logical storage structure of InnoDb storage engine, all data are stored in a logical space, called tablespace. A tablespace consists of segments, extents, and pages. Pages are sometimes called blocks in some documents. InnoDb logical storage structure diagram is as follows:

Tablespace (tablespace)

  • Table Spaces are the highest level of Innodb storage engine logic, where all data is stored.
  • By default, the Innodb storage engine has a shared tablespace, IBDatA1, in which all data is stored.
  • If innodb_file_per_table is enabled, note that only data, indexes, and insert buffer Bitmap are stored in the tablespace of each table. Other classes of data, such as rollback information, insert buffer retrieval page, system transaction information, secondary write buffer, etc. are stored in the original shared table.

Segment (segment)

  • A table space consists of segments, such as data segments, index segments, and rollback segments.
  • InnoDB storage engine tables are organized by index, so data is index and index is data. The data segment is the leaf node of B+ tree, and the index segment is the non-index node of B+ tree.
  • InnoDB storage engine segment management is done by the engine itself, DBA can not and does not need to control it.

Area (among)

  • Extents are Spaces made up of contiguous pages, each of which is 1MB in size in any case.
  • To ensure page continuity in extents, the InnoDB storage engine requests 4-5 extents from disk at a time.
  • By default, InnoDB storage engine pages are 16KB in size, with a total of 64 contiguous extents in a single extents.

Page (page)

  • Pages are the smallest unit of InnoDB disk management.
  • In the InnoDB storage engine, the default page size is 16KB.
  • Starting with innodb1.2. x, page sizes can be set to 4K, 8K, 16K with the innodb_page_size parameter.
  • InnoDB storage engine, common page types are: data page, undo page, system page, transaction data page, insert buffer bitmap page, insert buffer free list page and so on.

4. Schematic diagram of Innodb page structure

Innodb page structure monomer diagram

The InnoDB data page consists of the following 7 sections, as shown in the figure below:

The size of File Header, Page Header and File Trailer are fixed, which are 38, 56 and 8 bytes respectively. These Spaces are used to mark some information of the Page, such as Checksum, layer number of B+ tree index where the data Page is located, etc. The User Records, Free Space, and Page Directory sections are the actual row record storage Space, so the size is dynamic.

Below we use the form to describe the seven parts roughly:

Storage flow diagrams recorded in a page

Whenever we insert a record, we will apply for a Space of record size from the Free Space part, that is, the unused storage Space, and divide it into the User Records part. When all the Space in the Free Space part is replaced by the User Records part, This means that the page is used up. If a new record is inserted, it needs to apply for a new page. This process is illustrated as follows:

Data structure diagram of different Innodb pages

A table can have thousands of records, and a page is only 16KB, so it may take many pages to hold the data. The File Header is the first part of the InnoDB page. Its FIL_PAGE_PREV and FIL_PAGE_NEXT represent the page number of the previous and next page respectively, that is, the pointer to the last and next nodes in the linked list.

5.Innodb index structure diagram

Let’s first look at a sample data table, assuming Col1 is the primary key, as follows:

B+ tree clustering index structure diagram

  • A clustered index is an index created with a primary key
  • Clustered indexes store data in tables on leaf nodes

Non-clustered index structure diagram

Assuming that the index is Col3, the index structure diagram is as follows:

  • A non-clustered index is an index created with a non-primary key
  • Non-clustered indexes store primary keys and index columns on leaf nodes
  • When querying data using a non-clustered index, the primary key on the leaf is retrieved and the desired data is retrieved. (The process of getting the primary key and looking it up is called back table)
  • If all columns queried are the same as those of the index, the index column is called an overwrite index.

InnoDB lock type mind map

Locking mechanism

Optimistic locking and pessimistic locking are two concurrency control ideas that can be used to solve the lost update problem.

Optimistic locking

  • Every time I went to fetch data, I was optimistic that there would be no concurrency problems.
  • Therefore, data is accessed and processed without locking each time.
  • However, during the update, the system determines whether there is a conflict according to the version number or timestamp. If there is a conflict, the transaction is processed; if there is no conflict, the transaction is committed.

Pessimistic locking

  • Every time I go to fetch data, I am very pessimistic. I think it will be modified by others and there will be concurrency problems.
  • Therefore, exclusive locks are placed before data is accessed and processed.
  • Data is locked throughout data processing, and the lock is released only after the transaction commits or rolls back.

Locking granularity

  • Table lock: low overhead, fast lock; The locking force is large, the probability of lock conflict is high, and the concurrency is low. No deadlocks.
  • Line lock: expensive, slow lock; Deadlocks occur; The lock granularity is small, the probability of lock conflict is low, and the concurrency is high.
  • Page locks: the overhead and speed of locking is between table and row locks. Deadlocks occur; The locking granularity is between table and row locks, and the concurrency is average

compatibility

A Shared lock:

  • Also known as read lock (S lock).
  • If one transaction acquires the shared lock, other transactions can acquire the shared lock but cannot acquire the exclusive lock. Other transactions can read but cannot write.
  • SELECT … LOCK IN SHARE MODE Indicates adding a SHARE LOCK.

Exclusive locks:

  • Also known as write lock (X lock).
  • If transaction T places an exclusive lock on data A, other transactions cannot place any kind of lock on data A. Transactions that are granted exclusive locks can both read and modify data.
  • SELECT … FOR UPDATE displays adding exclusive locks.

The lock mode

  • Record lock: A lock on the corresponding index record for a row that locks a row record
  • Gap lock: a lock that locks an interval between indexed records
  • Next-key lock: a combination of the record lock and the lock on the gap before this index record, locking the row record + interval.
  • Intent locks are designed to support the simultaneous existence of multiple granularity locks.

Reference and thanks

  • MySQL Tech Insider
  • High Performance MySql
  • MySQL InnoDB locking mechanism in parsing share segmentfault.com/a/119000001…
  • Database two great artifact [index and lock] juejin.cn/post/684490…
  • InnoDB logical storage structure learning blog.csdn.net/m0_37752084…
  • MySQL index the data structure and algorithm principle behind blog.codinglabs.org/articles/th…

Personal public account

Welcome to pay attention to, we study together, discuss together ha.