Take you to know MySQL-MySQL architecture-MySQL

Source: Internet
Author: User
Recently, I have been busy with projects, working overtime, and launching projects, so I have no time to study. This is not the case. I just took some time in the past few days. I am busy reading a database book. I believe many of my friends have read MySQL technology: InnoDB has been busy with projects recently, various overtime work, project launch, and no learning time. 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.
As for the background, I will not talk about 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 ).
Before talking about the concept of architecture, let's start with 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.