Thresh

MySQL Server architecture can be roughly divided into network connection layer, service layer, storage engine layer and system file layer from top to bottom.

  • Network connection layer

    Client Connectors: Provides support for setting up with the MySQL server. At present, almost all mainstream server programming technologies are supported, such as common Java, C, Python,.NET, etc., which connect to MySQL through their respective API technologies.

  • Service Layer (MySQL Server)

The service layer is the core of MySQL Server, which mainly contains six parts: system management and control tools, connection pool, SQL interface, parser, query optimizer and cache.

1. Connection Pool: Stores and manages the Connection between the client and the database. One thread manages one Connection. [official website performance test report: introduce thread pool, performance stability and performance will be greatly improved,128 concurrent, read/write mode, mysql 60 times higher, read-only 18 times, if not reference thread pool, thread creation and closing performance consumption is large] 2. System Management Services and Utilities: such as backup and restoration, security Management, and cluster Management. 3. SQL Interface: accepts SQL commands sent by clients and returns query results. Such as DML, DDL, stored procedures, views, triggers, and so on. 4. Parser: Parses the requested SQL into a "parse tree". Then check further to see if the parse tree is valid according to some MySQL rules. 5. Query Optimizer: When the "parse tree" passes the parser syntax check, it is turned into an execution plan by the Optimizer, which then interacts with the storage engine. 6. Cache&Buffer: the cache mechanism is composed of a series of small caches. Such as table cache, record cache, permission cache, engine cache, etc. If the query cache has a matching query result, the query statement can directly fetch data from the query cache. > select uid,name from user where gender=1; 1) select according to WHERE statement first, not query all data and then filter 2) select query according to UID and name attribute projection, not extract all fields 3) join the previous selection and projection finally generate query resultsCopy the code
  • Pluggable Storage Engines

Storage engine is responsible for storage and extraction of data in MySQL and interaction with the underlying system files. The MySQL storage engine is plug-in. The query execution engine in the server communicates with the storage engine through interfaces that mask the differences between storage engines. There are a variety of storage engines, each with its own characteristics, the most common being MyISAM and InnoDB.

  • File System

This layer is responsible for storing database data and logs on top of the file system and interacting with the storage engine. It is the physical storage layer for files. It mainly contains log files, data files, configuration files, PID files, socket files and so on.

1. Log files a. Error log is enabled by default. Show variables like '%log_error%' b. General Query log records General query statements, show variables like '% General %'; C. The binary log records the change operations performed on the MySQL database, the occurrence time and execution duration of the statement. But it does not record SQL for select, show, and so on that does not modify the database. It is used for database recovery and primary/secondary replication. show variables like '%log_bin%'; // Show variables like '%binlog%'; // Show binary logs; D. Slow Query log Records all SQL queries whose execution time times out. The default value is 10 seconds. show variables like '%slow_query%'; // Show variables like '%long_query_time%'; // Duration 2. The configuration file is used to store all MySQL configuration files, such as my.cnf and my.ini. 3. Data file A. db.opt: Record the default character set and validation rules for this library. B. FRM file: Stores meta information related to tables, including the definition of table structures. Each table has an FRM file. C. MYD file: The MyISAM storage engine is dedicated to storing the data of MyISAM tables. Each table has one. MYD file. D. MYI file: MyISAM is dedicated to storage engines and stores index information about MyISAM tables. Each MyISAM table corresponds to a.myi file. E. IBD files and IBDATA files: Store InnoDB data files (including indexes). InnoDB storage engine has two types of tablespaces: exclusive tablespaces and shared tablespaces. 1. Exclusive tablespaces use.ibd files to store data, and each InnoDB table corresponds to an.ibd file. 2. The shared tablespace uses an. Ibdata file. All tables use one or more. F. Ibdata1 file: system tablespace data file that stores table metadata and Undo logs. G. ib_logfile0 and ib_logfile1: Redo log files. Pid file PID file is a process file of mysqld application in Unix/Linux environment. Like many other Unix/Linux server programs, it holds its own process ID. Socket files are available only in Unix/Linux environments. In Unix/Linux environments, users can directly use Unix sockets to connect to MySQL without using TCP/IP networks.Copy the code