Mysql Database is the most widely used database at present , I often come into contact with . Really use it well mysql It's not just about writing sql Just go , What's more important is to really understand its internal working principle . This article first introduces some from the macro point of view mysql Related knowledge , The purpose is to make people understand mysql Can have a general cognition , Next, we will make an in-depth interpretation of each knowledge point one by one .
Mysql A typical client / Server architecture (C/S framework ) Pattern . For computers , Database client program and server program run in different processes respectively . So the client process sends... To the server process sql The process of requesting and getting the returned result is essentially interprocess communication .mysql The supported interprocess communication methods include TCP/IP、 name pipes 、 Shared memory 、unix Domain socket file .
If the server process and the client process are running on different hosts , Only through TCP/IP Network communication protocol .mysql Listen on a port when the server starts ( Default 3306), Wait for the client process to connect . Of course , The server process and the client process are in the same host , Through local loopback address (127.0.0.1) It can also be used TCP/IP Communicating .
2. Named pipes or shared memory
If both the server process and the client process are running on the same machine windows On a host , You can communicate through named pipes or shared memory .
（1） Use named pipes for interprocess communication
You need to add... To the command to start the server program --enable-named-pipe Parameters , Then add... To the command to start the client program --pipe perhaps --protocol=pipe Parameters .
（2） Use shared memory for interprocess communication
You need to add... To the command to start the server program --shared-memory Parameters , After successfully starting the server , Shared memory becomes the default connection mode for local client programs , But we can also add... To the command to start the client program --protocol=memory Parameter to explicitly specify the use of shared memory for communication .
（3）Unix Domain socket file
If our server process and client process are running on the same operating system, it is a class Unix If it's on your machine , We can use Unix Domain socket file for interprocess communication .
In real environment , Servers and clients are basically running on different hosts , The way they communicate with each other is TCP/IP.
A query sql The basic treatment process of
No matter which way the client process and the server process communicate , The final effect is ： The client sends a piece of text to the server （sql sentence ）, The server process sends a piece of text to the client process after processing （ Processing results ）. Here is a query sql For example , Briefly describe the general process of the server processing client requests .
As we can see from the picture , The server program needs to go through three parts to process the query request from the client , Connection management 、 Analysis and optimization 、 Storage engine .
Whenever a client connects to the server , The server will create a thread to specifically handle the interaction with the client . When the client program initiates the connection , Need to carry host information 、 user name 、 password , The server program authenticates the information provided by the client program , If authentication fails , The server program will reject the connection .
When the connection is established , The server thread associated with the client will always wait for the client to send a request ,MySQL The request received by the server is just a text message , The text message has to go through various processing to return the final processing result to the client .
Analysis and optimization
Up to now ,MySQL The server has obtained the request in text form , Then you need to go through the query cache 、 Syntax parsing 、 Query optimization and so on .
The query cache
If the server turns on the query cache , When the query is executed, the query results will be obtained from the query cache first . If it hits the cache, it returns the result directly , Otherwise, go ahead with .mysql Query caching is not recommended , And in 8.0 Version has removed this feature . Not in the real world , So you don't have to go into details .
The main thing to do in this step is based on SQL Grammar carries out lexical and grammatical analysis and semantic analysis , The table to be queried 、 All kinds of query conditions are extracted MySQL Some data structures used inside the server .
Because we wrote MySQL Statement execution may not be very efficient ,MySQL Our optimization program will optimize our statements , If external connection is converted to internal connection 、 Expression simplification 、 Subquery to join, etc . The result of optimization is to generate an execution plan , This execution plan shows which indexes should be used for query , Connection order between tables, etc . We can use EXPLAIN Statement to view the execution plan of a statement .
mysql The data is stored in the data table , But tables are just logical concepts , The data is really stored on the physical disk . The storage engine is responsible for the storage and extraction of physical data . In order to achieve different functions ,MySQL Provides a wide range of storage engines , There are some differences in the physical storage structure of different storage engines . But different storage engines provide a unified call interface （ Storage engine API）.
mysql Support for multiple storage engines , You can view the following commands ：
Although many storage engines are supported , But we need to focus on InnoDB And a proper understanding of MyISAM The storage engine can ！
For management convenience , People put connection management 、 The query cache 、 Syntax parsing 、 Query optimization these functions that do not involve real data storage are divided into MySQL server The function of , Divide the function of real access data into the function of storage engine .
Startup options and system variables
mysql Program ( Including server related programs and client related programs ) You can specify startup parameters when starting , To control the behavior of the program after it starts . These startup parameters can be specified on the command line , You can also specify them in the configuration file .
Use the startup option on the command line
start-up mysql The general format of the startup options specified after the command line of the program is as follows ：
-- Startup options 1[= value 1] -- Startup options 2[= value 2] ... -- Startup options n[= value n]
The startup options are separated by white space characters , Add... Before each startup option name --. For startup options that don't need a value , For example skip-networking, They don't have to specify a corresponding value . For startup options that require a specified value , such as default-storage-engine When we specify this setting item, we need to explicitly specify its value , For example InnoDB、MyISAM.
mysqld --default-storage-engine=MyISAM --skip-networking
For example, the startup item above indicates that the default storage engine is MyISAM, And the use of TCP/IP Means of communication .
For the convenience of use , For some common options, short forms are provided , such as ：
Use options in configuration files
Compared to setting startup options using the command line ,mysql It is more recommended to use configuration files to set startup options . We write all the startup options we need to set in this configuration file , Load the corresponding startup options from this file every time you start the server .
MySQL When the program starts, it will look for configuration files in multiple paths , Some of these paths are fixed , Some can be specified on the command line . Depending on the operating system , The path to the configuration file is also different , And the higher the configuration priority is, the better . In short, configuration files can exist in multiple paths , And there's a priority relationship . It's not going to unfold here .
Content of profile
Unlike specifying startup options on the command line , The startup options in the configuration file are divided into groups , Each group has a group name , Use 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 ...)
start-up mysql The program , The startup options under the corresponding group or groups will be used . Several startup options can be defined under each group , We use [server] Group as an example, let's take a look at the form of filling in startup options （ The form of the startup options in the other groups is the same ）：
[server] option1 # This is a option1, This option does not require an option value option2 = value2 # This is a option2, This option requires an option value
Mysql System variables are 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_connections control , The default storage engine for the table is determined by the system variable default_storage_engine control . 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 dynamically modify 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 ,mysql This problem can be solved by the scope of system variables . Specifically, the scope of action is divided into the following two types ：
1.GLOBAL： Global variables , Affect the overall operation of the server .
2.SESSION： Session variables , Affect the operation of a client connection .（ notes ：SESSION There is a person named LOCAL）
Obviously , The scope of the system variables set through the startup options is GLOBAL Of , That is, it works for all clients . The syntax of dynamically modifying system variables through the client is as follows ：
SET [GLOBAL|SESSION] System variable name = value ;
If you omit the scope in the statement that sets the system variable , The default scope is SESSION. Empathy , We can use the following command to view MySQL The system variables supported by the server program and their current values ：
SHOW [GLOBAL|SESSION] VARIABLES [LIKE Matching patterns ];
Mysql State variables are variables that describe the running state of a server , For example Threads_connected Indicates how many clients are currently connected to the server .
Because state variables are used to show the health of the server program , So their values can only be set by the server program itself （ Read only to the client ）. Similar to system variables , State variables also have GLOBAL and SESSION Two areas of action , So the statement to view the state variable can be written like this ：
SHOW [GLOBAL|SESSION] STATUS [LIKE Matching patterns ];
mysql Supported character sets and comparison rules
In the computer , Data is ultimately stored in binary form . therefore , If we want to save strings , The first step is to determine what the binary data is for each character in the string , And then save the binary data to the computer . The process of mapping a character to a binary data is also called encoding , The process of mapping a binary data to a character is called decoding .
Using character set can solve the problem of data storage , But it can't solve the problem of character comparison . In a simple scenario , We can judge the size directly by comparing the binary data of characters , This way is actually binary comparison rule . And in some cases , The binary comparison rule doesn't apply , For example, when ignoring case . So in order to cope with different scenarios , The same character set can have multiple comparison rules .
Mysql Many character sets are supported in , You can view ：
SHOW CHARSET [LIKE Matching patterns ];
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)
1.Charset: Character set name
2.Description: Character set description
3.Default collation: The default comparison rule
4.Maxlen: The maximum number of bytes a character takes . For character sets with variable length encoding , The number of bytes a character takes is not fixed . For example GB2312 Character set , One letter only takes 1 Bytes , And a Chinese character takes up 2 Bytes .
stay Mysql in ,utf8 and utf8mb4 The difference is 1 The maximum number of bytes per character is different .utf8 One character takes up 1-3 Bytes , and utf8mb4 One character takes up 1-4 Bytes . actually ,Mysql Of utf8 yes utf8mb3 Another name for . If you need to save some 4 Special characters in bytes ( such as emoji expression ), It is recommended to use utf8mb4 Character set .
Compare the rules
You can view mysql Comparison rules supported in ：
SHOW COLLATION [LIKE Matching patterns ];
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)
1.Collation: Comparison rule name , Basically in line with the character set name _ Language _ The suffix mode . The first part of the character set name is the beginning of the name of the character set associated with it , The second part shows the language of the comparison rule , such as utf8_spanish_ci It's a comparison of Spanish rules ,utf8_general_ci It's a general rule of comparison . The third part of the suffix is mainly used to indicate whether or not to distinguish case and accent and so on .
2.Charset: The name of the associated character set .
3.Default: yes Represents the default comparison rule for character sets .
Character set and comparison rule scope level
Mysql The character set and comparison rules in are 4 Two scope levels ：
1. Server level
2. Database level
3. Table level
4. Column level
actually , Character sets and comparisons are more regular. In the end, they must be applied to column level fields . We can simply think that , If the column level does not specify a character set and the comparison is more regular , Use table level ; If the table level does not specify a character set and the comparison is more regular , Just use database level ; And so on .
MySQL Two system variables are provided to represent the server level character set and comparison rules ：
1.character_set_server: Server level character set
2.collation_server: Server level comparison rules
The server level default character set is utf8, The default comparison rule is utf8_general_ci.
When we create and modify 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 ;
such as ：
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 see the character set and comparison rules used by the current database , You can see the values of the following two system variables :
1.character_set_database: The character set of the current database
2.collation_database: Current database comparison rules
We can specify the character set and comparison rules when creating and modifying tables , The grammar 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
such as ：
mysql> CREATE TABLE t( -> col VARCHAR(10) -> ) CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 0 rows affected (0.03 sec)
It should be noted that , For columns that store strings , Different columns in the same table can have different character sets and comparison rules .
When we create and modify a column definition, we can specify the character set and comparison rules of the column , The grammar is as follows ：
CREATE TABLE Table name ( Name String type CHARACTER SET Character set name COLLATE Comparison rule name , Other columns ... ); ALTER TABLE Table name MODIFY Name String type CHARACTER SET Character set name COLLATE Comparison rule name ;
For example, let's modify the table t Middle column col The character set and comparison rules of can be written like this ：
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
One more thing to note is ： Because character sets and comparison rules are interrelated , If we only modify the character set and comparison rules , May cause the associated character set and comparison rules to change .
I've arranged a copy here ：Mysql Related documents and knowledge map 、Java The systematic information of ,（ Include Java Core knowledge points 、 Interview topics and 20 The latest Internet real topic in 、 E-books, etc ） Friends in need can pay attention to the official account. 【 Cheng Xuyuan, Xiao Wan 】 Can get .