One, MySQL server logical architecture diagram each dashed box is a layer: the first layer: the topmost server is not unique to MySQL, most network-based client/server tools or services have similar systems. such as link processing, authorization authentication, security and so on. Second tier: Most of MySQL's core service capabilities are in this layer, including query parsing, parsing, optimization, caching, and all of the built-in functions (e.g. date, time, math, cryptographic functions, etc.). All features across the storage engine are implemented at this level: stored procedures, triggers, views. Third layer: Contains the storage engine. The storage engine is responsible for data storage and extraction in MySQL. The server communicates with the API and the storage engine, which masks the differences between different storage engines, making these differences transparent to the upper-level query process. The Storage Engine API contains dozens of underlying functions for performing operations such as "Start a transaction" or "fetch a row of records based on a primary key". But the storage engine does not parse SQL (InnoDB is an exception, it resolves the foreign key definition, because the MySQL server itself does not implement the feature) Two, connection management and security each client connection has a thread in the server process, This linked query executes only in this separate thread, which can only be run on a CPU core or CPU in turn. The server is responsible for caching threads, so there is no need to create or destroy threads for each new one. When a client (app) connects to a MySQL server, the server needs to authenticate it. Authentication is based on user name, original host information and password. If you connect using Secure Sockets (SSL), you can also use the integer authentication of the number of integers. Once the client connection succeeds, the server continues to verify that the client has permission to execute a particular query. Optimization and execution MySQL parses the query and creates an internal data structure (parse tree) and then optimizes it, including rewriting the query, determining the table's reading order, and selecting the appropriate index. The user can influence his decision-making process through a special keyword hint (hint) optimizer. You can also request the optimizer to explain (explain) the various factors of the optimization process, so that users can know how the server makes optimization decisions, and provide a reference base for users to reconstruct the query and schema (for MySQL schema, can be understood as the place to store data), Modify the configuration to make your app run as efficiently as possible. The optimizer does not care what storage engine the table uses, but the storage engine has an impact on optimizing the query. The optimizer will request the storage engine to provide the cost information for the capacity or a specific operation, as well as statistics on the table data, and so on. For a SELECT statement, the server checks the cache (query cache) before parsing the query, such asTo find the corresponding query, the server does not have to perform the entire process of query parsing, optimization, and execution, but instead directly returns the result set in the query cache. Iv. concurrency control is divided into two aspects: Server layer control and Storage engine layer control. are controlled using read-write locks. Locks are divided into table locks and row-level locks. The server tier uses row-level locks to synchronize, and row-level locks exist only at the storage engine level
MySQL server logical schema
