Perform process analysis:
Start the
Run the net start mysql(Windows)/service mysql start(Linux) command to start the initial module of mysql service invocation. Initialization module is when the database is started, do some initialization operations to the entire database, such as initialization of various system environment variables, various caches, storage engine initialization Settings, etc. Core API: the core API of MySQL database mainly realizes the optimization function of the bottom operation of database, including IO operation, formatting output, optimization of high-performance storage data result algorithm, string processing, among which the most important is memory management.
The connection
When a user sends an SQL, the network interaction module will monitor the user’s operation request. Passed to forward the ‘connection management module receives a request to the’ into/thread connection modules’ call ‘user modules for permissions detection (permission to access the database) by detecting will go after’ connection into/thread module from the ‘thread connection pool to find free cached connection thread and the client request, If the connection fails, a new connection request will be created and returned to the connection thread network interaction module: External API interfaces can be provided to receive and send data. When other modules need to interact, connection management module, feed/thread connection module and thread connection pool can be called through the API interface: The connection management module is responsible for monitoring all kinds of requests from MySQL Server and forwarding them to the thread management module according to different requests. Each customer request will be automatically assigned by the database to an independent thread for its independent service, and the main work of the connection thread is responsible for the communication between MySQL Server and the client. The thread management module is responsible for managing these generated threads.
To deal with
After the user permissions are verified successfully, and the new connection pool is obtained, it will go to the ‘command dispenser’. If the type of the command is select, it will go to the ‘query cache’, if not, it will go down; If it is select, and the ‘query cache’ is enabled, the cache will be queried to see if there is a matching SQL, if there is, the user access to the data will be checked, and if not, an error message will be returned. If the data is not available, the SQL operation process in the process will be recorded in the log file. When the corresponding conditions are not met in step 8 and 9, the SQL operation process will be recorded in the log file. After lexical analysis and syntax analysis, the parse tree will be generated and the corresponding module will be processed according to the operation (pre-processing stage). After receiving the request, the module checks whether the connected user has the permission to access the target table and target fields through the ‘Access Control module’. Yes the ‘table management module’ checks whether the table cache exists. If yes, the table directly corresponds to the table and obtains the lock. Responsible for re-opening the table file according to the ENGINE data of the table, obtain the storage ENGINE type of the table and other information through the interface to call the corresponding storage ENGINE processing after returning to query data content user module: the main function is used to control the user login access and user authorization management.
Access control module: mainly used to monitor each operation of users. The function of access control module is to control users’ access to data according to different user authorizations in the user module and various constraints of its database. The user module and access control module combine to form the permission management function of MySQL database.
Query optimizer: this module is mainly about the query request sent by the client, on the basis of the analysis of the previous algorithm, calculate an optimal query strategy, after optimization will improve the speed of query access, and finally return the query statement according to its optimal strategy.
Table change management module: mainly responsible for the completion of DML and DDl query, column such as, INSERT, UPDATE, DELETE, CREATE table, ALTER table and other statements processing.
Table maintenance module: mainly used to detect the status of the table, analyze, optimize the structure of the table, and repair the table.
Replication module: The replication module consists of the Master module and Slave module. The Master module is mainly responsible for reading binary logs on the Master side and I/O thread interaction on the Slave side in the replication environment.
Status module: when the client requests the system status, the system status module is mainly responsible for returning the data of various states to the user. Some of the most commonly used query status commands including show status, show variable is and so on are returned through this module.
Table management module: the main maintenance system generated table files. Columns such as MyISAM storage engine generate FRM, MYD, myI files, maintain these files, cache information about each table structure, and manage table level locks.
Storage engine interface module: MySQL implements the plug-in manager management of its database underlying storage engine, and processes all kinds of data in highly abstract paintings.
After executing the result command, the result set is returned to the Comprehending/threading module for subsequent cleanup, and either to wait for the request or to disconnect from the storage engine
View the storage engines supported by the database
show engines; Advantages of storage engines (MyISAM, InnoDB)
myisam
MyISAM clusters three files on disk with the same file name and corresponding table name
FRM file: Stores the definition data of the table. Myd file: store table specific record data. Myi file: Stores indexes. MyISAM storage engine does not support transactions, nor does it support primary keys, so the speed of data storage and batch query is relatively fast.
In practical applications, myISAM storage engine (logging) is often used for applications that do not need complete transactions and mainly focus on querying and adding records.
innodb
Innodb is developed by a third party company, and innoDB is the most widely used data storage engine except for satisfaction 3. Innodb’s write processing is less efficient than MyISAM, InnoDB sacrifices the efficiency of storage and query, supports transaction security, supports automatic column growth, and supports the safety of liquid transfer. This is an important reason why InnoDB is one of myISam’s most popular memory engines.
Foreign key constraints
Innodb implements foreign key, an important database function. From the perspective of database performance, foreign key reduces the efficiency of database query, and the coupling degree between database tables is closer. However, for many users, foreign key constraint may be a low-cost choice
Java, PHP, Golang, operation and maintenance interview questions