Detailed explanation of the architecture and concepts of high-performance MySQL

1.Logical architecture of MySQL

The topmost part is not unique to MySQL, all network-based C/S All network applications should include connection handling, authentication, security management, etc.

The middle layer is the core of MySQL, including queryparsing, analysis, optimization and caching. At the same time, it also provides cross-storage engine functions, including stored procedures, triggers and views, etc.

The bottom is the storage engine, which is responsible for accessing data. The server can interact with various storage engines through storage engine API.

1.1. Query Optimization and Execution (Optimization and Execution)

MySQL parses the user's query statement and creates an internal data structure - the analysis tree, and then performs various optimizations. For example, rewriting the query, choosing the order in which to read the tables, and which index to use, etc. The query optimizer does not care about the storage engine used by a table, but the storage engine affects how the server optimizes queries. The optimizer obtains some parameters, the execution cost of an operation, and statistical information through the storage engine. Before parsing a query, the server accesses the query cache - which stores the SELECT statement and the corresponding query result set. If a query result is already in the cache, the server will no longer parse, optimize, and execute the query. It only returns the results in the cache to the user, which will greatly improve the performance of the system.

1.2. Concurrency control

MySQL provides two levels of concurrency control: the server level (the server level) and the storage engine level (the storage engine level). Locking is the basic method to achieve concurrency control. The granularity of locks in MySQL:

(1) Table-level locks: MySQL provides table locks independently of the storage engine. For example, for the ALTER TABLE statement, the server provides table locks ( table-level lock).

(2) Row-level locks: InnoDB and Falcon storage engines provide row-level locks. In addition, BDB supports page-level locks. InnoDB’s concurrency control mechanism is discussed in detail in the next section.

The above is the detailed content of Detailed explanation of the architecture and concepts of high-performance MySQL. For more information, please follow other related articles on the PHP Chinese website!