In depth understanding of MySQL advanced knowledge points, introduction to startup items, system variables and character sets!

Time:2021-9-9

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.

communication mode

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 includeTCP/IPname pipesShared memoryUNIX domain socket file

  1. TCP/IP: If the server-side process and the client-side process run on different hosts, you can only use theTCP/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 usedTCP/IPTo communicate.
  2. 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

    1. usename 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--pipeperhaps--protocol=pipeParameters.
    2. useShared 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.
  3. 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 isTCP/IP

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:Connection managementAnalysis and optimizationStorage engine

Connection management

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 throughQuery cacheSyntax parsingQuery optimizationEtc.

Query cache

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.

Syntax parsing

The main thing to do in this step is to base the statement onSQL 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.

Query optimization

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 useEXPLAINStatement to view the execution plan of a statement.

Storage engine

MySQL data is saved indata 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 ;

In depth understanding of MySQL advanced knowledge points, introduction to startup items, system variables and character sets!

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 putConnection managementQuery cacheSyntax parsingQuery optimizationThese functions that do not involve real data storage are divided intoMySQL serverThe function of real data access is divided intoStorage 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 exampleskip-networking, they do not need to specify the corresponding value. For startup options that require a specified value, such asdefault-storage-engineWhen specifying this setting item, we need to explicitly specify its value, for exampleInnoDBMyISAM

mysqld --default-storage-engine=MyISAM --skip-networking

For example, the above startup item indicates that the default storage engine isMyISAM, and the use is prohibitedTCP/IPMode communication.

For ease of use, short forms are provided for some common options, such as:

Long form Short form meaning
–host -h host name
–user -u user name
–password -p password
–port -P host name
–host -h port

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

System variable

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 variablemax_connectionsThe default storage engine for tables is controlled by the system variabledefault_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:

  1. GLOBAL: global variable that affects the overall operation of the server.
  2. SESSION: session variable that affects the operation of a client connection. (Note:SESSIONThere are some people namedLOCAL

Obviously, the scope of the system variables set through the startup option isGLOBAL, 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];

state variable

MySQL status variables refer to variables that describe the running status of the server,For exampleThreads_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 haveGLOBALandSESSIONThe 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 calledcode, the process of mapping a binary data to a character is calleddecode

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 actuallyBinary 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

character set

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)
  1. Charset: character set name
  2. Description: character set description
  3. Default collation: default comparison rule
  4. Maxlen: the maximum number of bytes occupied by a character. For adoptionVariable length codingThe number of bytes occupied by a character is not fixed. Like inGB2312 character setIn, a letter occupies only 1 byte, while a Chinese character occupies 2 bytes.

In mysql,utf8andutf8mb4The difference is that the maximum number of bytes occupied by one character is different.utf8One character takes 1-3 bytes, andutf8mb4One character takes 1-4 bytes. In fact, MySQLutf8yesutf8mb3Alias for. If you need to save some special characters that occupy 4 bytes (such as Emoji expression), it is recommended to useutf8mb4Character set.

Comparison rules

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)
  1. Collation: the name of the comparison rule, which basically conforms to:Character set name_ Language_ suffixpattern. Part ICharacter 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 asutf8_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.
  2. Charset: the name of the associated character set.
  3. Default: Yes indicates that it is the default comparison rule of the character set.
suffix transitive verb describe
_ai accent insensitive Accent insensitive
_as accent sensitive Accent sensitive
_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:

  1. Server level
  2. Database level
  3. Table level
  4. Column level

actually,Character sets and comparison rules must finally work inColumn levelOn field。 You can simply think that ifColumn levelIf no character set and comparison rules are specified, theTable levelof IfTable levelIf no character set and comparison rules are specified, theDatabase levelof and so on.

Server level

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 isutf8, the default comparison rule isutf8_general_ci

Database level

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;

For example:

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

Table level

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

For example:

mysql> CREATE TABLE t(
    ->     col VARCHAR(10)
    -> ) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.03 sec)

Column level

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