Original author, public number [programmer reading], welcome to pay attention to the public number, reprint the article please indicate the source oh.
This is the first article in my relearning MySQL series.
The reason for writing this series of articles is that as a Web backend programmer, I need to deal with MySQL a lot, but mostly stay in the application level of CURD. However, as the amount of data in charge of the project increases and the performance of the server hardware starts to bottleneck. Let me feel that it is necessary to learn and understand MySQL more comprehensively and deeply, in order to better play the performance of MySQL and improve the ability to use MySQL.
MySQL Architecture
Like all Server software, MySQL uses C/S architecture, namely Client and Server architecture. When we use MySQL, we send requests to connect to the MySQL daemon running the Server as the Client. The MySQL server will process the request and return the result to us. This process can be simply abstracted as the following diagram:
The Client (Client)
After installing MySQL, there is a MySQL command in the bin directory of the MySQL installation directory. This is what we call the MySQL client, but this is the MySQL official command line client.
To connect to the mysql server, enter the mysql command in the shell as follows:
$ mysql -u root -p
Enter password:*******
Copy the codeAfter entering the password, the MySQL client interface is displayed as follows:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 5.7.29-standard
Type 'help; ' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Copy the codeThe arrow above indicates waiting for input, which means we can send various statements to the MySQL server.
To exit the client, enter \q,exit,quit, for example:
mysql>exit
Bye
Copy the codeIn addition to the MySQL native command line client, there are some GUI clients more commonly used, such as Navicat, and there are various programming languages that provide access to MySQL class libraries, in fact, are connected to MySQL client.
The Server (Server)
The diagram above may be too abstract and simple, but if we look inside the MySQL server, we can use the following diagram to describe the logical architecture of MySQL in more detail:
MySQL architecture logic diagram
When we say MySQL, we mean the MySQL server more often. The MySQL server is the daemon that responds to client requests.
The MySQL server daemon can be started by running the following command in the MySQL installation directory, but it is not usually started in this way. Instead, the MySQL server daemon is started as a system service.
$ bin/mysqld
Copy the codeAs can be seen from the above schematic diagram, MySQL Server can be roughly divided into Server layer and storage engine layer, while Server layer is connection management, resolution and optimization, query optimizer and executor respectively, and the interaction between Server layer and storage engine is called through a unified API. Let’s take a detailed look at the functions and functions of each component of MySQL Server.
Connection manager
The connection manager manages and maintains all connections requested by MySQL clients. When we make a request to MySQL, the connection manager is responsible for creating a connection and verifying user permissions.
The connection manager will automatically disconnect an established connection if it has not been too long before a request has been sent, and we can determine how long to disconnect an inactive connection by setting the variable wait_TIMEOUT.
The query cache
When we establish a connection with the connector, if we execute a SELECT statement, the connector will first check the query cache to see if the statement has been executed before, and if not, it will determine the corresponding permission if it has, and then return the result directly.
The query cache is a key-value cache structure that uses the query statement as a key and the query result as a value.
However, when the data in the table changes, the corresponding query cache will fail. Therefore, the query cache will often fail to be hit. Therefore, it is generally recommended not to use the query cache.
mysql> select SQL_CACHE * from users whereUid = 1000;Copy the codeThe MySQL team may have realized that the query cache function is not very useful and has removed the entire query cache function in MySQL 8.0, so if you are using MySQL 8.0, the query cache function does not exist.
The parser
When there is no command to query in the query cache, then you need to actually perform statement, then hand over to the parser to lexical analysis, the statement of our input and dismantling, folding solution and then parse, figure out whether we input statements in accordance with MySQL grammar rules, if the input statements do not conform to the MySQL grammar rules, The command stops execution and an error message is displayed.
For example, we enter the following error statement:
select * users
Copy the codeBecause the above statement is missing from, the following error message is returned:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'users' at line 1
Copy the codeQuery optimizer
The MySQL server already knows what we want to query after lexical and syntax analysis of the parser, but it must be optimized by the query optimizer before starting the query.
In the process of optimization, the optimizer will decide which index to use according to the query conditions of SQL statements. If there is join, the query order of the table will be determined. Finally, an execution plan will be generated according to the optimization results and sent to the following executor to execute.
actuator
After the SQL statement is optimized by the query optimizer, it is then handed over to the executor to start execution, but the executor determines whether the user has permissions on the corresponding data table before starting execution.
If the user has permissions, began to call the data, and its database, MySQL data storage and calls to a store, when we call, actuators through storage engine API to send corresponding instructions at the bottom of the storage, the storage engine is responsible for the specific implementation, and tell the execution result to actuators, and then returned to the client.
The storage engine
Storage engines, also known as table types, determine how a table is processed and stored. MySQL supports many different storage engines, and storage engines are designed to be pluggable. Different tables in the same database can use different storage engines.
Innodb is the default storage engine for MySQL. It is also a common storage engine. Other common storage engines include MyISAM and Momery.
Query supported storage engines
After connecting to the MySQL server, we can use the following command to query the storage engine supported by the current MySQL server:
mysql > show engines
Copy the codeThe following table shows the result of the above statement:
+--------------------+---------+----------------------------------------------------------------+--------------+------+- -----------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+- -----------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, storedin memory, useful fortemporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+- -----------+ 9 rowsin set (0.00 sec)
Copy the codeIn the above table, the Engine column shows the name of the storage Engine, the Support column indicates whether the Engine is available, the DEFAULT column indicates that the Engine is the DEFAULT Engine, and the Comment column indicates the description of the Engine. The Transactions column indicates whether the storage engine supports Transactions, the XA column indicates whether the storage engine supports distributed Transactions, and the Savepoints column indicates whether the storage engine supports partial transaction rollback.
Specifying a Storage engine
Innodb is the default storage engine for MySQL, which means that if you do not specify a storage engine when creating a table, Innodb is used by default. The following statement shows how to specify a storage engine when creating a table: MyISAM is used for the Users table.
mysql> CREATE TABLE users(
-> uid int not null,
-> username varchar(32) not null,
-> email varchar(64) not null,
-> gender tinyint not null,
-> primary key(uid)
-> )engine=MyISAM
-> ;
Copy the codeModify the storage engine of the table
For already created data, you can also modify its storage engine by using the following statement:
ALTER TALBE table name ENGINE= Name of the storage ENGINECopy the codeFor example, we changed the storage engine of the Users table from MyISAM to InnoDB
ALTER TABLE users ENGINE=InnoDB;
Copy the codesummary
The learning and understanding of MySQL infrastructure allows us to stand at the top, which helps us to have a bird’s eye view of MySQL, understand the overall operation of MySQL, and think about how to solve problems from a global perspective, rather than being limited to a certain aspect.
Welcome to scan code attention, common learning progress
