This is the sixth day of my participation in the August More text Challenge. For details, see:August is more challenging

Mysql architecture

In general, MySQL can be divided into two parts: the Server tier and the storage engine.

Server layer includes: connector, query cache, analyzer, the optimizer, actuators, etc., covering most of the core MySQL service function, and all of the built-in functions (such as date, time, mathematics, and encryption function, etc.), all across the storage engine functions are realized in this layer, such as: stored procedures, triggers, views, and so on.

The storage engine layer is responsible for: data storage and retrieval. The architecture is plug-in and supports multiple storage engines such as InnoDB and MyISAM. InnoDB is the default from MySQL5.5.5, but you can specify the storage engine as engine = MyISAM when building tables. Different storage engines provide different table data access methods and support different functions.

As you can see from the figure above, the different storage engines share a Server layer, which runs from the connector to the actuator.

The process of executing an SQL statement

  1. The application sends the query SQL statement to the server for execution;
  2. Query cache. If the query cache is enabled, the server will not directly query the database after receiving the query request, but will look for the corresponding query data in the query cache of the database. If the query data exists, it will directly return it to the client. Perform the following operations only if the cache does not exist.
  3. Query optimization process to generate an execution plan. This stage mainly includes parsing SQL, preprocessing, optimizing SQL execution plan;
  4. MySQL completes the entire query according to the corresponding execution plan;
  5. The query results are returned to the client.

Three paradigm

Design patterns (patterns, database design patterns, design patterns for databases) are collections of relational patterns that conform to a certain level.

First normal form (1NF) : The fields in a database table are single-attribute and non-divisible. This single attribute is made up of basic types, including integer, real, character, logical, date, and so on. Second normal form (2NF) : there is no partial functional dependence of non-key fields on any candidate key field in the database table (partial functional dependence refers to the presence of some fields in the combination of keywords to determine the non-key field), that is, all non-key fields are completely dependent on any set of candidate keywords. Third normal form (3NF) : On the basis of the second normal form, the data table conforms to the third normal form if there is no transfer function dependence of non-key fields on any candidate key fields. By transfer function dependence, we mean that the transfer function C depends on A if there is A deterministic relationship “A → B → C”. Therefore, a database table that satisfies the third normal form should not have the following dependencies: key field → non-key field X → non-key field Y

A database design that meets the requirements of the paradigm has a clear structure and avoids data redundancy and operational exceptions. This does not mean that a design that does not conform to the requirements of the paradigm is necessarily wrong. In the special case where there is a 1:1 or 1: N relationship in the database table, the non-conformities caused by the merge are justified.