MySQL database

SQL Execution Process

  • Server layer mainly includes connector, query cache, analyzer, optimizer and executor, which contains many core functions of MySQL, as well as all built-in functions, stored procedures, triggers, views, etc. In fact, all cross-storage engine functions are realized in this layer
  • The storage engine layer, which is mainly responsible for data storage and reading, exists in the form of plug-insInnoDB 、MyISAM,MemoryAnd so on multiple storage engines now default toInnoDB

The query cache

  • When an SQL is executed, the query cache is first entered

  • Check whether the statement has been executed before. If the statement has been executed before, it is saved in the cache in the form of key-value. Key is the query statement, and value is the query result

  • If the cache hits, the result is returned, and if the query statement is not in the cache, the process continues

  • In most cases, we do not recommend using the query cache, because the cache invalidation is very frequent. As long as one update, all the caches in the table will be invalidated

  • Query_cache_type = query_cache_type; DEMAND = query_cache_type; SQL_CACHE = query_cache_type Select SQL_CACHE * from T where ID=1;

  • As of MySQL8.0, the entire query cache function module has been removed and is no longer available

analyzer

  • The analyzer mainly includes lexical analysis and syntax analysis
  • Lexical analysis mainly analyzes what each string in an SQL query represents, such asselectMark out, this is a query, in the specific table name, query fields and so on all analyzed out
  • Syntax analysis is mainly to analyze whether the SQL statement conforms to the MySQL specification, if we write SQL problems, 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
  • Determine which index to use if there are multiple indexes
  • When there is a polygon investigationjoin, query the order of the table
  • Optimization of query conditions and statements

actuator

  • First check if you have access to the table. If you don’t, an error is reported
  • If yes, open the table according to the engine interface for data query filtering

Buffer Pool

  • The default size128MBSmall,
  • For 16-core 32GB machines, 2 gb memory can be allocated, configuration file:my.iniConfiguration:

[server] 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 to be queried is located, and then load the whole data page toBuffer Pool,
  • The default data page size is16KBThat is, a page of data contains16KBThe data of
  • inBufferPoolBy default, cached pages correspond to the size of the data pages on disk
  • There is a description for each cached page
  • Description information includes the tablespace to which the data page belongs, the label of the data page, and the cache page inBuffer PoolMemory address and other information in the
  • inBuffer PoolIn, all description information comes first, followed by the cache pages

  • Representing a data size equal to the size of a cached page5%About, or roughly800Byte, so when we setbuffer poolThe size of128MBBut in factBuffer PoolThe true size of this is going to be a little bit higher, maybe there is130MBThis is the description of each cached page

Table space

  • Normally 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, and the table space created by ourselves is usually oneThe name of the table. IbdData file of

Data area

  • There are too many data pages in a tablespace to manage. This introduces the concept of a data extent

  • A data area has 64 consecutive data pages, each of which is 16KB, so each data area is 1MB in size

  • At the same time, 265 data fields were divided into a group

  • The first three data pages of the first data area of the first group of data Spaces in the table space are fixed, holding some special descriptive information

    • FSP_HDRData page: Holds some table Spaces and the attributes of this set of data areas
    • IBUF - BITMAPData page: Stores all the data pages in this groupinsert bufferSome information about
    • INODEData page: Store some special information

  • The first two data pages of the first data area in each of the other groups of data areas in the tablespace hold special information

This article was published by AnonyStar. It may be reproduced but the original source must be claimed. Welcome to pay attention to wechat public account: cloud habitat Jane code to obtain more quality articles more articles to pay attention to the author’s blog: cloud habitat Jane code i-code.online