Mysql database is the most widely used database at present, and it is often contacted in practical work. To really use MySQL well is not only to be able to write SQL, but also to really understand its internal working principle. This paper first introduces some MySQL related knowledge points from a macro perspective, in order to let everyone have a general understanding of MySQL, and then deeply interpret each knowledge point one by one.
The main content of this article is based on the Nuggets brochure “understanding MySQL from the root”. If you want to learn more, it is recommended to buy Nuggets brochure to read.
MySQL uses a typicalClient / server architecture (C / S architecture)pattern. For the computer, the database client program and server program are run in different processes. Therefore, the process that the client process sends SQL requests to the server process and gets the returned results is essentiallyInterprocess communication。 The interprocess communication methods supported by MySQL include
UNIX domain socket file。
TCP/IP: If the server-side process and the client-side process run on different hosts, you can only use the
TCP/IPNetwork communication protocol。 When the MySQL server starts, it listens to a port (3306 by default) and waits for the client process to connect. Of course, the server process and the client process are on the same host, and the local loopback address (127.0.0.1) can also be used
Named pipe or shared memory: If the server process and the client process are running on a Windows host, they can communicate through named pipes or shared memory。
name pipesFor interprocess communication: you need to add in the command to start the server program
--enable-named-pipeParameter, and then add it to the command to start the client program
Shared memoryFor interprocess communication: you need to add in the command to start the server program
--shared-memoryParameter. After the server is successfully started, the shared memory will become the default connection method of the local client program, but we can also add it in the command to start the client program
--protocol=memoryParameter to explicitly specify the use of shared memory for communication.
UNIX domain socket file: if our server process and client process are running on the same UNIX like machine, we can use UNIX domain socket file for inter process communication.
In the real environment, the server and client are basically running in different hosts, and the communication mode between them is
A basic process for querying SQL
No matter which way the client process and server process communicate, the final effect is:The client sends a text (SQL statement) to the server, and the server process sends a text (processing result) to the client process after processing。 Let’s take query SQL as an example to briefly explain the general processing process of the server processing client requests.
From the figure, we can see that the server program needs to go through three parts to process the query request from the client:
Analysis and optimization、
Whenever a client connects to the server, the server will create a thread to deal with the interaction with the client. When the client program initiates the connection, it needs to carry the host information, user name and password. The server program will authenticate the information provided by the client program. If the authentication fails, the server program will refuse the connection.
After the connection is established, the server thread associated with the client will always wait for the client to send a request. The request received by the MySQL server is only a text message. The text message needs to be processed before the final processing result can be returned to the client.
Analysis and optimization
So far, the MySQL server has obtained the request in text form, and then it needs to go through
If the server has enabled the query cache, the query results will be obtained from the query cache when executing the query. If the cache is hit, the result is returned directly, otherwise it is executed. MySQL does not recommend using query caching, and this feature has been removed in version 8.0. It will not be used in the real environment, so there is no need to know in detail.
The main thing to do in this step is to base the statement on
SQL syntaxLexical and syntactic analysis and semantic analysis are carried out. The tables to be queried and various query conditions are extracted and put into some data structures used inside the MySQL server.
Because the execution efficiency of the MySQL statements we write may not be very high, the MySQL optimizer will optimize our statements, such as converting external connections to internal connections, simplifying expressions, converting subqueries to connections, and so on. The result of optimization is to generate an execution plan, which indicates which indexes should be used for query, the connection order between tables, etc. We can use
EXPLAINStatement to view the execution plan of a statement.
MySQL data is saved in
data sheetInside, but the table is only a logical concept, and the data is really saved on the physical disk. The storage engine is responsible for the storage and extraction of physical data. In order to realize different functions, MySQL provides a variety of storage engines. There are some differences in the physical storage structure of different storage engines. However, different storage engines provide a unified calling interface (that is, storage engine API).
MySQL supports multiple storage engines, which can be viewed through the following commands:
show engines ;
Although there are many supported storage engines, we need to focus on InnoDB and properly understand MyISAM storage engine!
For the convenience of management, people put
Query optimizationThese functions that do not involve real data storage are divided into
MySQL serverThe function of real data access is divided into
Storage engineThe function of.
Startup options and system variables
MySQL programs (including server related programs and client related programs) can specify startup parameters to control the behavior of the program after startup. These startup parameters can be specified on the command line or in the configuration file.
Use the startup option on the command line
The general format of the startup options specified after the command line for starting the MySQL program is as follows:
--Startup option 1 [= value 1] - startup option 2 [= value 2]... -- startup option n [= value n]
Each startup option is separated by a blank character, and the name of each startup option is added before it
--。 For startup options that do not require a value, for example
skip-networking, they do not need to specify the corresponding value. For startup options that require a specified value, such as
default-storage-engineWhen specifying this setting item, we need to explicitly specify its value, for example
mysqld --default-storage-engine=MyISAM --skip-networking
For example, the above startup item indicates that the default storage engine is
MyISAM, and the use is prohibited
For ease of use, short forms are provided for some common options, such as:
|Long form||Short form||meaning|
Use options in configuration files
Compared with setting startup options using the command line, MySQL recommends using the configuration file to set startup options. We write all the startup options that need to be set in this configuration file. Each time we start the server, we load the corresponding startup options from this file.
When MySQL program starts, it will look for configuration files under multiple paths. Some of these paths are fixed or can be specified on the command line. Depending on the operating system, the path of the configuration file is also different, and the later the path, the better the configuration priority. In short, configuration files can exist under multiple paths, and there is a priority relationship. Not here.
Contents of the configuration file
Unlike specifying startup options on the command line, startup options in the configuration file are divided into groups, each with a group name in brackets
Expand, like this:
[server] (specific startup options...) [mysqld] (specific startup options...) [mysqld_safe] (specific startup options...) [client] (specific startup options...) [mysql] (specific startup options...) [mysqladmin] (specific startup options...)
When starting the MySQL program, the startup options under one or more corresponding groups will be used. Several startup options can be defined under each group
[server]Take group as an example to see the form of filling in startup options (the form of startup options in other groups is the same):
[server] Option1 # this is option1, which does not require an option value Option2 = Value2 # this is option2, which requires an option value
MySQL system variables refer to variables that can affect the running behavior of server programs。 For example, the number of clients allowed to connect at the same time is determined by the system variable
max_connectionsThe default storage engine for tables is controlled by the system variable
default_storage_engineControl. Each system variable has a default value. We can use the options in the command line or configuration file to change the values of some system variables when starting the server, or modify them dynamically at run time (most system variables support dynamic modification).
Scope of action
Multiple client programs can connect to a server program at the same time. For the same system variable, we sometimes want different clients to have different values through mysqlScope of system variableTo solve the above problems. Specifically, the scope of action is divided into the following two types:
GLOBAL: global variable that affects the overall operation of the server.
SESSION: session variable that affects the operation of a client connection. (Note:
SESSIONThere are some people named
Obviously, the scope of the system variables set through the startup option is
GLOBAL, which is valid for all clients. The syntax of dynamically modifying system variables through the client is as follows:
Set [global session] system variable name = value;
If the scope is omitted from the statement setting the system variable, the default scope is session.Similarly, we can use the following commands to view the system variables supported by the MySQL server program and their current values:
Show [global|session] variables [like matching patterns];
MySQL status variables refer to variables that describe the running status of the server,For example
Threads_connectedIndicates how many clients are currently connected to the server.
Since the status variable is used to display the running status of the server program, theTheir values can only be set by the server program itself (read-only to the client)。 Like system variables, state variables have
SESSIONThe two scopes are, so the statement to view the status variable can be written as follows:
Show [global|session] status [like matching mode];
Character sets and comparison rules supported by MySQL
In a computer, data is ultimately stored in binary form. Therefore, if we want to save the string, we must first determine what the binary data corresponding to each character in the string is, and then save these binary data to the computer. The process of mapping a character to binary data is also called
code, the process of mapping a binary data to a character is called
Using character set can solve the problem of data storage, but it can not completely solve the problem of comparing characters. In a simple scenario, we can directly judge the size by comparing the binary data of characters. This method is actually
Binary comparison rule。 And in some scenarios,
Binary comparison ruleNot applicable, such as ignoring case. thereforeIn order to deal with different scenarios, the same character set can have multiple comparison rules。
MySQL supports many character sets, which can be viewed through the following statement:
Show charset [like matching pattern];
mysql> SHOW CHARSET; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | ... | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | ... +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.01 sec)
Charset: character set name
Description: character set description
Default collation: default comparison rule
Maxlen: the maximum number of bytes occupied by a character. For adoption
Variable length codingThe number of bytes occupied by a character is not fixed. Like in
GB2312 character setIn, a letter occupies only 1 byte, while a Chinese character occupies 2 bytes.
utf8mb4The difference is that the maximum number of bytes occupied by one character is different.
utf8One character takes 1-3 bytes, and
utf8mb4One character takes 1-4 bytes. In fact, MySQL
utf8mb3Alias for. If you need to save some special characters that occupy 4 bytes (such as Emoji expression), it is recommended to use
You can view the comparison rules supported in MySQL through the following statement:
Show collection [like matching pattern];
mysql> SHOW COLLATION LIKE 'utf8\_%'; +--------------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------------+---------+-----+---------+----------+---------+ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | | Yes | 1 | ... +--------------------------+---------+-----+---------+----------+---------+ 27 rows in set (0.00 sec)
Collation: the name of the comparison rule, which basically conforms to:
Character set name_ Language_ suffixpattern. Part I
Character set nameIs the beginning of the name of the character set associated with it. The second part represents the language of the comparison rule, such as
utf8_spanish_ciCompared with the rules of Spanish,
utf8_general_ciIs a general comparison rule. The third part of the suffix is mainly used to indicate whether to be case sensitive and accent sensitive.
Charset: the name of the associated character set.
Default: Yes indicates that it is the default comparison rule of the character set.
|_ai||accent insensitive||Accent insensitive|
|_ci||case insensitive||Case insensitive|
|_cs||case sensitive||Case sensitive|
|_bin||binary||Compare in binary|
Character set and comparison rule scope level
There are four scope levels for character sets and comparison rules in MySQL:
- Server level
- Database level
- Table level
- Column level
actually,Character sets and comparison rules must finally work in
Column levelOn field。 You can simply think that if
Column levelIf no character set and comparison rules are specified, the
Table levelof If
Table levelIf no character set and comparison rules are specified, the
Database levelof and so on.
MySQL provides two system variables to represent the character set and comparison rules at the server level:
character_set_server: server level character set
collation_server: server level comparison rules
The default character set at the server level is
utf8, the default comparison rule is
When creating and modifying a database, we can specify the character set and comparison rules of the database. The specific syntax is as follows:
Create database database name Character set character set name Collate comparison rule name; Alter database database name Character set character set name Collate comparison rule name;
mysql> CREATE DATABASE charset_demo_db -> CHARACTER SET gb2312 -> COLLATE gb2312_chinese_ci; Query OK, 1 row affected (0.01 sec)
If you want to view the character set and comparison rules used by the current database, you can view the values of the following two system variables:
character_set_database: the character set of the current database
collation_database: comparison rules for the current database
We can specify the character set and comparison rules of the table when creating and modifying the table. The syntax is as follows:
Create table table name (column information) Character set character set name Collate comparison rule name Alter table table name Character set character set name Collate comparison rule name
mysql> CREATE TABLE t( -> col VARCHAR(10) -> ) CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 0 rows affected (0.03 sec)
Note that for columns that store strings,Different columns in the same table can also have different character sets and comparison rules。 When creating and modifying a column definition, we can specify the character set and comparison rules of the column. The syntax is as follows:
Create table table name（ Column name string type character set character set name collate comparison rule name, More columns ); Alter table table name modify column name string type character set character set name collate comparison rule name;
For example, we can modify the character set and comparison rules of col in table t as follows:
mysql> ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
It should also be noted that:Because character sets and comparison rules are interrelated, if we only modify character sets and comparison rules, the associated character sets and comparison rules may change。