MySQL Storage---InnoDB

Refer to "MySQL Technology insider"

One, MySQL architecture and storage engine

1. Database and database instance

Database: A collection of physical operating system files or other file components;

DB instance: There are database background processes/threads and a shared memory area.

A database is a file, and a database instance is an application. Users to the database of additions and deletions are through the database instance, which is equivalent to a database management software.

2. mysql Architecture

MySQL structure includes: Connection pool component, Management Service tool component, SQL interface component, query parser component, optimizer component, cache component, plug-in table storage engine , physical file '. Pay particular attention to the Insert table storage structure, which is where MySQL is specific to other databases, which are table-based, not database.

Abstract point:

　　

As we can see, the server program handles the query request from the client roughly through three parts, namely,, 连接管理 解析与优化 存储引擎 .

　　1. Connection Management

The client program can use one of the methods described in "link" TCP/IP 命名管道和共享内存 Unix域套接字 to establish a connection with the server program, which caches some threads and assigns a thread to the client program whenever a client connects. To deal with the request it sent over.

　 创建和销毁线程会耗费很多性能，所以在服务器端维护许多线程可以减少这部分的性能损耗。但是服务器端维护的线程数目是有限的，如果在短时间内有超级多的客户端连接进来的话，会有一部分因为获取不到线程而进入等待状态，直到有的客户端断开连接后这些等待的客户端才可以被分配到线程。

When the client program initiates the connection, it needs to carry the host information, the user name, the password, the server program will authenticate the information provided by the client program, and if the authentication fails, the server program will reject the connection. In addition, if the client program and the server program are not running on a single computer, we can also use the SSL (Secure Sockets) network connection to communicate, to ensure the security of data transmission.

　2. Optimization and execution

After the client program has successfully established a connection to the server program, it is possible to send the text command to the server program. This section needs to be 查询缓存 语法解析 查询优化 done in a few steps, and we'll look at it in detail.

　　Query Cache : If I ask you what 9+8×16-3×2×17 the value is, you might use a calculator to calculate, or a little bit of the heart to calculate, and finally get the result 35 , if I ask you again 9+8×16-3×2×17 the value is how much, you still silly again? We've just already counted it, so just say the answer. MySQLthe process of processing query requests by the server program is also the case, it will be processed by the query request and results 缓存 , if the next time there is an identical request to come, directly from the cache to find the result is good, no longer silly to the bottom of the table to find.

Of course, the MySQL server is not smart enough, if the two query requests on any character different (for example: spaces, comments), will cause the cache will not hit. In addition, if a query request contains system functions, stored functions, custom variables, and system tables in the MySQL library, then this request will not be cached, as a function example, two invocations of the same function may produce a different result, such as a function NOW , each call will produce the latest current time, If the function is called in a query request, even if the query request text information is the same, the different time two queries should also get different results, if the first query is cached, then the second query when directly using the first query results are wrong!

But since it's a cache, it's time for the cache to fail. The MySQL cache system will monitor each table involved, as long as the structure or data of the table is modified, the cache associated with that table will be invalidated!

　　parsing : If the cache is not hit, the next step is to enter the formal query phase. First, the client program sent over the request is just a text, the MySQL server program first to analyze the text, determine whether the request syntax is correct, and then from the text to query the table, various query conditions are extracted.

　　Query optimization: based on syntax parsing, the server program gets the information needed, such as what the query list is, what the table is, what the search condition is, and so on, but the light is not enough because the statements we write MySQL may not be very efficient to MySQL execute. Optimizer will do some optimizations to our statements, such as outer joins to inner joins, expression simplification, subqueries to join, use index bar a bunch of things, this part of us will be detailed nagging, now you just need to know in the MySQL process of the Server program processing request is a step.

3. Storage Engine

Until the server program has completed the query optimization, there is no real access to the real data table, the MySQL data storage and extraction operations are encapsulated in a called 存储引擎 module, we know is a row 表 of records composed of, but this is only a logical concept, It is responsible for how to physically represent records, how to read data from a table, and how to write data to specific physical memory 存储引擎 . In order to achieve different functions, a MySQL variety of different 存储引擎 存储引擎 management table structures may be used, and the access algorithms may be different. However, these storage engines provide a unified calling interface to the service layer above, that is, for our users, if we need to use the specific functionality provided by a storage engine, simply switch the table's storage engine.

So after the server program has completed the query optimization, just call the underlying storage engine to provide the call interface, get to the data after the return to the client program is good.

Second, InnoDB storage engine

The core of MySQL is the table storage engine. Engine interface Open source, you can customize the function. InnoDB is a well-known third-party engine.

InnoDB has multiple blocks of memory that make up a large pool of memory, which is responsible for maintaining multiple internal data structures that are accessed by all processes/threads, caching data on disk, facilitating fast reading, and redo log buffering .... The primary role of a background thread is to refresh the data in the memory pool, ensure that the data in the cache pool is up-to-date, and flush the modified data to the disk file. Among them, when the algorithm policy is refreshed, not necessarily the real-time refresh, because sometimes the traffic is too large, real-time refresh difficulty is relatively large. Refer to my previous post------cache.

1. Background thread

2. Memory

Three

MySQL Storage---InnoDB