High performance MySQL reading notes--mysql logical architecture

I. MySQL logical architectureMySQL's most important and distinct feature is its storage engine architecture, which is designed to separate query processing from other system tasks and data storage/extraction.

The first layer, the service layer, the top level of service is not unique to MySQL, most web-based client/server-side tools or services have a similar architecture. Mainly for the request to do connection processing, authorization authentication, security and so on.

The second, core, and most MySQL core service functions are in this layer, including query parsing, parsing, optimization, caching, and all of the built-in functions (such as date, time, math, and cryptographic functions), all of which are implemented at this level: stored procedures, triggers, views, and so on.

Third tier, storage engine, storage engine is responsible for the storage and extraction of data in MySQL. The server communicates with the storage engine through the API. These interfaces mask the differences between different storage engines, making these differences transparent to the upper-level query process. The storage engine does not parse SQL, and the storage engine does not communicate with each other, but simply responds to requests from the upper service.

Connection management and security (first tier service tier) > Connection Process

Δ each connected query is completed in a thread in a process.

Δ the server is responsible for caching threads, so the service layer does not need to create or destroy threads for each new connection (you can use the thread pool).

> Certification Process

1. When the client (app) connects to the MySQL server, the server needs to authenticate it. Authentication is based on user name, original host information, and password. If you are connecting using a secure socket, you can also use the certificate authentication for the certificates.

2. Once the client connection succeeds, the server continues to verify that the customer has permission to execute a query (for example, to execute a query statement on a library table)

Optimization and execution

1. mysql interprets queries, creates internal data structures (parse trees), and then optimizes them, including rewriting queries, determining the order in which tables are read, and selecting the appropriate indexes.

2, for the SELECT statement, before parsing the query, the server will first check the query cache, if it can hit, the server will not have to perform query parsing, optimization and execution of the entire process, directly return to the query cache result set.

MySQL Focus: "High performance MySQL" Reading notes-index : http://blog.csdn.net/xifeijian/article/details/20312557

High performance MySQL reading notes--mysql logical architecture