Order
Recently has been busy projects, various overtime, project on-line, gradually did not have the time to study. This is not, just these days to spare some time, sneak in, is reading a database of books, I believe many small partners have also seen-"MySQL Technology Insider: InnoDB Storage engine." This book covers MySQL's design ideas, architecture, storage engines, indexes, transactions, and tuning of performance. Of course, now I have just read a part, as for now writing this article, mainly to record my study history, and to share with you some new things.
Background
As for the background, there is not much to say, if interested, or recommend you look at this book. Well, now, first of all, the MySQL architecture is also good, may also be able to show off (purely flicker).
Concept
Before architecture, say two nouns, and it is a common two words in the database domain: "database" and "database instance".
I think, the little friends must know these two nouns, and sometimes often can not distinguish. So, what is a "database"? What is a "db instance"? Let me tell you more about it.
First say "database", the database is a collection of physical operating system files or other forms of file types. In MySQL, the database file can be a file ending with frm, myd, Myi, IBD.
Then say "db instance", which consists of a database background process/thread and a shared memory area. Shared memory can be shared by running background processes/threads.It is important to note that the database instance is really used to manipulate the database files.
These two words can sometimes be used interchangeably, but the concepts of the two are completely different. In MySQL, the instance and database typically have a relationship of one by one, i.e. one instance corresponds to a database, and one database corresponds to one instance. However, in a cluster scenario, there may be situations where a database can be used by multiple instances.
System structure
MySQL is a portable database that runs on almost all of the current operating systems, such as Unix/linux, Windows, Mac, and Solaris. Various systems vary in the underlying implementation, but MySQL basically guarantees the consistency of the physical architecture on each platform.
MySQL consists of the following parts:
- Connectors: Interaction with SQL in different languages
Max_connections: Is the maximum number of connections for the entire MySQL instance limit max_user_connections: is the maximum number of connections for a single user, and there is no indication of which user is a user.
- Management serveices & Utilities: System Management and control tools
Backup and restore security, replication, clustering, management, configuration, migration, and metadata.
- Connection Pool: Connection pooling
Authentication, thread reuse, connection restrictions, checking memory, data caching, managing user connections, threading, and so on need for caching.
- SQL Interface:sql Interface
Perform operations and management of DML, DDL, stored procedures, views, triggers, etc., and the user queries the desired results with SQL commands.
Query the privileges of the translation object, and the SQL command is validated and parsed by the parser when it is passed to the parser.
- Optimizer: Query optimizer
Access to the statistical data of the path;
In the MySQL optimization statement process, you can set Optimize_switch control to optimize behavior. In the production environment, a time period MySQL server pressure is particularly large, load once reached 100, the query found that the database has a large number of SQL statements state result sorting, result sorting this sort of special consumption of CPU and memory resources. Extract one of the SQL to view the execution plan.
- Cache and buffer: query caching
Global and engine-specific caches and buffers;
MySQL's Windows version of the default storage engine supports transactions for INNODB,INNODB and provides row-level locking.
Conclusion
Want to know MySQL, here is just a beginning, in the follow-up study, I will also not regularly share with you, at the same time, the small partners have new ideas can also with me a communication.
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Get you to know MySQL MySQL architecture
