Understanding the MySQL architecture of MySQL and the mysql Architecture

Understanding the MySQL architecture of MySQL and the mysql Architecture


Collation
Recently, I have been busy with projects, working overtime, and launching projects, so I have no time to study. This is not the case. Just a few days ago, I took some time to read a database book. I believe many of my friends have read it-MySQL technology Insider: InnoDB Storage engine. This book details MySQL's design philosophy, architecture, storage engine, indexing, transactions, and performance tuning. Of course, I have just read a part of it. As for this article, I want to record my learning history and share some new things with you.

Background
As for the background, I will not mention it here. If you are interested, I suggest you take a look at this book. Now, let's take a look at the architecture of MySQL, which is also good. We may also be able to show off it ).

Concept
Before talking about the architecture, let's talk about two terms, which are common in the database field: "Database" and "database instance ".
I think my friends must know these two terms, and sometimes they cannot be distinguished. So what is "Database "? What is "database instance? I will discuss it in detail below.
First, the database is a collection of physical operating system files or other forms of file types. In MySQL, database files can be files ending with frm, myd, myi, and ibd.
Then, the "database instance" is composed of a database background process/thread and a shared memory zone. Shared memory can be shared by running background processes/threads. Note that database instances are actually used to operate database files.
These two words can be used interchangeably, but they have different concepts. In MySQL, the relationship between an instance and a database is usually one-to-one, that is, an instance corresponds to a database, and a database corresponds to an instance. However, in a cluster, a database may be used by multiple instances.

Architecture
MySQL is a portable database that can run on almost all operating systems, such as Unix/Linux, Windows, Mac, and Solaris. Various systems have different underlying implementations, but MySQL can basically ensure the consistency of the physical architecture on each platform.


MySQL consists of the following parts:

• Connectors: interaction with SQL in different languages

Max_connections: the maximum number of connections of the entire MySQL instance. max_user_connections: the maximum number of connections of a single user. This parameter does not indicate which user is a user or any user.

• Management Serveices & Utilities: system Management and control tools

Backup and recovery security, replication, cluster, management, configuration, migration and metadata.

• Connection Pool: Connection Pool

Identity Authentication, thread reuse, connection restrictions, check memory, data cache, management of user connections, thread processing, and other needs to be cached.

• SQL Interface: SQL Interface

DML, DDL, stored procedures, views, triggers, and other operations and management; you can use SQL commands to query the required results.

• Parser: Parser

Query the privileges of a translation object. When an SQL command is passed to the parser, it is verified and parsed by the parser.

• Optimizer: Query Optimizer

Access path statistics;


During the MySQL optimization statement, you can set optimize_switch to control the optimization behavior. In the production environment, the pressure on the MySQL server in a certain period of time was extremely high, and the load reached 100 at a time. The query found that the database had a large number of SQL statements in the state result sorting, result sorting consumes cpu and memory resources. Extract an SQL statement to view the execution plan.

• Cache and Buffer: Query Cache

Global and engine-specific cache and buffer;

• Engine: storage Engine

By default, the storage engine of MySQL for Windows is InnoDB. InnoDB supports transactions and provides row-level locking.

Conclusion
I want to know MySQL. This is just the beginning. I will share it with you from time to time in the future, new ideas can be shared with me.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.