Basic Components of MySQL

SQL execution process

  • The Server layer mainly includes connectors, query caching, analyzers, optimizers, executors, including many of the main core functions of MySQL, as well as all the built-in functions, stored procedures, triggers, views, etc. In fact, all the functions across the storage engine are implemented in this layer
  • The storage engine layer, which is mainly responsible for storing and reading data, exists in the form of plug-ins, with support such asInnoDBMyISAM,MemoryWait for multiple storage engines, now default toInnoDB

The query cache

  • When an SQL is executed, it is first entered into the query cache
  • Check to see if the statement has been executed before. If it has, it will be stored in the cache as key-value, where key is the query statement and value is the result of the query
  • If the cache is hit, the result is returned directly, and if the query is not in the cache, the flow continues
  • In most cases, we do not recommend the use of query cache, because cache invalidation is very frequent, only one update, then all the cache in the table will be invalidated, the update of the data is relatively large, then the efficiency of buffer hit is very low, constantly invalidation
  • Parameter is provided in MySQLquery_cache_typeParameter to set, default isDEMAND, which means that the default SQL does not use the query cache, but can be used if you want to cache queries against specific statementsSQL_CACHETo display the specified, such asselect SQL_CACHE * from T where ID=1;
  • As of MySQL8.0, the entire query cache function module has been removed and is no longer owned


  • The analyzer mainly includes lexical analysis and grammar analysis
  • Lexical analysis focuses on what each string in a SQL represents, such asselectMark out, this is a query, in the specific table name, query fields and so on all the analysis out
  • Syntax analysis is mainly to analyze whether the SQL statement conforms to the specification of MySQL. If we have a problem with SQL writing, then we often see an exception isYou have an error in your SQL syntaxThe prompt

The optimizer

  • Optimize our SQL for a higher execution plan
  • If there are multiple indexes, determine which index to use
  • When there are multiple linksjoinThe order in which the query tables are queried
  • Optimization of query conditions and statements


  • It first verifies that there is access to the table. If there is no access, an error will be reported
  • If so, open the table according to the engine interface for query filtering of the data

Buffer Pool

  • The default size128MBSmall,
  • For a 16-core 32G machine, 2G memory can be allocated. Configuration file:my.iniConfiguration:


innodb_buffer_pool_size = 2147483648

Data page

  • In MySQL, data is abstracted and stored in files in the form of data pages. When querying, first locate the data page where the data is to be queried, and then load the entire data page into the fileBuffer Pool,
  • The default size of the data page is16KB, that is, a page of data contains16KBThe data of
  • inBufferPoolBy default, the cache page corresponds to the size of the data page on disk
  • There is a description for each cached page
  • The description information includes: the tablespace of the data page, the label of the data page, and the cache pageBuffer PoolAnd some other information
  • inBuffer PoolIn, all the description information is at the front, followed by the individual cached pages

  • Describes the size of the data equivalent to the cache page size5%Right or left, which is roughly800Byte, so when we setbuffer poolThe size of128MBBut in factBuffer PoolThe true size of will exceed a little bit, may have130MBAll that is added is the description of each cached page

Table space

  • Usually when we create a table we have one on diskThe name of the table. IbdThis is the concept and physical embodiment of a table space
  • For some system tablespaces, there may be multiple disk files for each tablespace. The tablespace created by us is usually one for each tablespaceThe name of the table. IbdData file of

Data area

  • There are too many data pages in the tablespace to manage. This is introducedData areaThe concept of theextent
  • There is contiguous in a data area64Six data pages, each data page16kbSo the size of each data area is1MB
  • At the same time265The data regions are divided into a group
  • The first three data pages of the first data area in the first set of data areas in a tablespace are fixed and hold some special descriptive information

    • FSP_HDRData pages: Holds some tablespaces and attributes for this set of data extents
    • IBUF - BITMAPData pages: Holds all of this set of data pagesinsert bufferSome information about
    • INODEData pages: hold special information
  • The first two data pages of the first data area of the other groups of data extents in the table space are reserved for specific information

This article was published by Anonystar and can be reproduced without attribution.

Welcome to pay attention to WeChat public account: cloud living simple code for more quality articles

Follow my blog for more posts:
Cloud dwelling simple code