关于后端:深入理解mysql进阶知识点启动项系统变量字符集介绍

mysql 数据库是以后利用最为的宽泛的数据库，在理论工作中也常常接触到。真正用好 mysql 也不仅仅是会写 sql 就行，更重要的是真正了解其外部的工作原理。本文先从宏观角度介绍一些 mysql 相干的知识点，目标是为了让大家对 mysql 能有一个大体上的认知，后续再逐个对每个知识点的进行深刻解读。

本文次要内容是依据掘金小册《从根儿上了解 MySQL》整顿而来。如想具体理解，倡议购买掘金小册浏览。

通信形式

mysql 采纳了典型的 客户端 / 服务器架构 (C/ S 架构) 模式。对于计算机而言，数据库客户端程序和服务器程序别离运在不同的过程中。所以客户端过程向服务器过程发送 sql 申请并失去返回后果的过程实质上就是 过程间通信 。mysql 反对的过程间通信形式包含TCP/IP、 命名管道 、 共享内存、unix 域套接字文件。

1. TCP/IP: 如果服务端过程和客户端过程运行在不同的主机中，只能通过 TCP/IP 网络通信协定进行通信 。mysql 服务器启动时监听某个端口(默认 3306)，期待客户端过程来连贯。当然，服务端过程和客户端过程在同一主机中，通过本机回环地址(127.0.0.1) 也是能够应用 TCP/IP 进行通信的。

2. 命名管道或共享内存 : 如果服务端过程和客户端过程都运行在一台 windows 主机上，能够通过命名管道或共享内存形式进行通信。

   1. 应用 命名管道 来进行过程间通信：须要在启动服务器程序的命令中加上 --enable-named-pipe 参数，而后在启动客户端程序的命令中退出 --pipe 或者 --protocol=pipe 参数。
   2. 应用 共享内存 来进行过程间通信: 须要在启动服务器程序的命令中加上 --shared-memory 参数，在胜利启动服务器后，共享内存便成为本地客户端程序的默认连贯形式，不过咱们也能够在启动客户端程序的命令中退出 --protocol=memory 参数来显式的指定应用共享内存进行通信。
3. Unix 域套接字文件: 如果咱们的服务器过程和客户端过程都运行在同一台操作系统为类 Unix 的机器上的话，咱们能够应用 Unix 域套接字文件来进行过程间通信。

实在环境中，服务器和客户端根本都是运行在不同主机中的，它们之间采纳的通信形式就是TCP/IP。

一条查问 sql 的根本处理过程

不管客户端过程和服务器过程是采纳哪种形式进行通信，最初实现的成果都是：客户端向服务器发送一段文本（sql 语句），服务器过程解决后再向客户端过程发送一段文本（处理结果）。上面以查问 sql 为例，简略阐明一下服务器解决客户端申请的大抵处理过程。

从图中咱们能够看出，服务器程序解决来自客户端的查问申请大抵须要通过三个局部，别离是 连贯治理 、 解析与优化 、 存储引擎。

连贯治理

每当有一个客户端连贯到服务器时，服务器都会创立一个线程来专门解决与这个客户端的交互。在客户端程序发动连贯的时候，须要携带主机信息、用户名、明码，服务器程序会对客户端程序提供的这些信息进行认证，如果认证失败，服务器程序会回绝连贯。
当连贯建设后，与该客户端关联的服务器线程会始终期待客户端发送申请，MySQL 服务器接管到的申请只是一个文本音讯，该文本音讯还要通过各种解决能力将最初的处理结果返回客户端。

解析与优化

到当初为止，MySQL 服务器曾经取得了文本模式的申请，接着还须要通过 查问缓存 、 语法解析 、 查问优化 等进行解决。

查问缓存

如果服务器开启了查问缓存，在执行查问的时候会先从查问缓存中获取查问后果。如果命中缓存则间接返回后果，否则接着执行。mysql 不举荐应用查问缓存，并且在 8.0 版本曾经移除此性能。实在环境中也不会应用，因而不必具体理解。

语法解析

这一步次要做的事件是对语句基于 SQL 语法 进行词法和语法分析和语义的解析，将要查问的表、各种查问条件都提取进去放到 MySQL 服务器外部应用的一些数据结构上来。

查问优化

因为咱们写的 MySQL 语句执行起来效率可能并不是很高，MySQL 的优化程序会对咱们的语句做一些优化，如外连贯转换为内连贯、表达式简化、子查问转为连贯等等。优化的后果就是生成一个执行打算，这个执行打算表明了应该应用哪些索引进行查问，表之间的连贯程序等。咱们能够应用 EXPLAIN 语句来查看某个语句的执行打算。

存储引擎

mysql 数据是保留在 数据表 外面，但表只是逻辑上的概念，数据真正是保留在物理磁盘上的。存储引擎负责的就是物理上数据的保留和提取。为了实现不同的性能，MySQL 提供了各式各样的存储引擎，不同存储引擎在物理上的存储构造存在一些差别。然而不同的存储引擎提供了对立的调用接口（也就是存储引擎 API）。

mysql 反对多种存储引擎，能够通过如下命令查看：

show engines ;

尽管反对的存储引擎很多，然而咱们须要重点关注 InnoDB 以及适当理解 MyISAM 存储引擎即可！

为了治理不便，人们把 连贯治理 、 查问缓存 、 语法解析 、 查问优化 这些并不波及实在数据存储的性能划分为 MySQL server 的性能，把实在存取数据的性能划分为 存储引擎 的性能。

启动选项和零碎变量

mysql 程序 (包含服务器相干程序和客户端相干程序) 在启动的时候能够指定启动参数，来控制程序启动后的行为。这些启动参数能够放在命令行中指定，也能够把它们放在配置文件中指定。

在命令行上应用启动选项

启动 mysql 程序的命令行后边指定启动选项的通用格局如下：

-- 启动选项 1[= 值 1] -- 启动选项 2[= 值 2] ... -- 启动选项 n[= 值 n]

各个启动选项之间应用空白字符隔开，在每一个启动选项名称前边增加 --。对于不须要值的启动选项，比方说skip-networking，它们就不须要指定对应的值。对于须要指定值的启动选项，比方default-storage-engine 咱们在指定这个设置项的时候须要显式的指定它的值，比方说InnoDB、MyISAM。

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

比方下面的启动项就示意默认存储引擎为 MyISAM，并且禁止应用TCP/IP 形式通信。

为了应用的不便，对于一些罕用的选项提供了短模式，比方：

配置文件中应用选项

相比于应用命令行的形式设置启动选项，mysql 更举荐应用配置文件来设置启动选项。咱们把须要设置的启动选项都写在这个配置文件中，每次启动服务器的时候都从这个文件里加载相应的启动选项。

MySQL 程序在启动时会寻找多个门路下的配置文件，这些门路有的是固定的，有的是能够在命令行指定的。依据操作系统的不同，配置文件的门路也有所不同，并且越前面门路下的配置优先级越好。总之就是多个门路下都能够存在配置文件，并且有个优先级的关系。这里就不开展了。

配置文件的内容

与在命令行中指定启动选项不同的是，配置文件中的启动选项被划分为若干个组，每个组有一个组名，用中括号 [] 扩起来，像这样：

[server]
(具体的启动选项...)

[mysqld]
(具体的启动选项...)

[mysqld_safe]
(具体的启动选项...)

[client]
(具体的启动选项...)

[mysql]
(具体的启动选项...)

[mysqladmin]
(具体的启动选项...)

启动 mysql 程序时，会应用对应的一个或多个组下的启动选项。每个组下边能够定义若干个启动选项，咱们以 [server] 组为例来看一下填写启动选项的模式（其余组中启动选项的模式是一样的）：

[server]
option1     #这是 option1，该选项不须要选项值
option2 = value2      #这是 option2，该选项须要选项值

零碎变量

mysql 零碎变量是指可能影响服务器程序运行行为的变量 。比方容许同时连入的客户端数量由零碎变量max_connections 管制，表的默认存储引擎由零碎变量 default_storage_engine 管制。每个零碎变量都有一个默认值，咱们能够应用命令行或者配置文件中的选项在启动服务器时扭转一些零碎变量的值，或者在运行时动静批改(大多数零碎变量反对动静批改)。

作用范畴

多个客户端程序能够同时连贯到一个服务器程序。对于同一个零碎变量，咱们有时想让不同的客户端有不同的值，mysql 通过 零碎变量的作用范畴 来解决上述问题。具体来说作用范畴分为上面两种：

1. GLOBAL：全局变量，影响服务器的整体操作。
2. SESSION：会话变量，影响某个客户端连贯的操作。（注：SESSION有个别名叫LOCAL）

很显然，通过启动选项设置的零碎变量的作用范畴都是 GLOBAL 的，也就是对所有客户端都无效的。通过客户端动静批改零碎变量语法如下：

SET [GLOBAL|SESSION] 零碎变量名 = 值;

如果在设置零碎变量的语句中省略了作用范畴，默认的作用范畴就是 SESSION。同理，咱们能够应用下列命令查看 MySQL 服务器程序反对的零碎变量以及它们的以后值：

SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式];

状态变量

mysql 状态变量是指形容服务器运行状态的变量，比方说 Threads_connected 示意以后有多少客户端与服务器建设了连贯。

因为状态变量是用来显示服务器程序运行状况的，所以 它们的值只能由服务器程序本人来设置（对客户端而言是只读的）。与零碎变量相似，状态变量也有 GLOBAL 和SESSION两个作用范畴的，所以查看状态变量的语句能够这么写：

SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];

mysql 反对的字符集和比拟规定

在计算机中，数据最终都是以二进制的模式保留的。因而，如果咱们要保留字符串，首先就先得确定字符串中的每个字符对应的二进制数据是什么，而后再将这些二进制数据保留到计算机中。将一个字符映射成一个二进制数据的过程也叫做 编码 ，将一个二进制数据映射到一个字符的过程叫做 解码。

应用字符集能够解决数据存储的问题，然而无奈齐全解决字符之间互相比拟的问题。简略场景下，咱们能够间接通过比拟字符的二进制数据来判断大小，这种形式其实就是 二进制比拟规定 。而有些场景下， 二进制比拟规定 并不实用，比方疏忽大小写的时候。因而 为了应答不同的场景，同一种字符集能够有多种比拟规定。

字符集

mysql 中反对很多种字符集，能够通过以下语句查看：

SHOW CHARSET [LIKE 匹配的模式];

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: 字符集名称
2. Description: 字符集形容
3. Default collation: 默认的比拟规定
4. Maxlen: 一个字符最大占用的字节数。对于采纳 变长编码方式 的字符集而言，一个字符占用的字节数不是固定的。比方在 GB2312 字符集 中，一个字母只占用 1 个字节，而一个汉字占用了 2 个字节。

在 mysql 中，utf8和 utf8mb4 的区别就在于 1 个字符占用的最大字节数不同。utf8一个字符占用 1 - 3 个字节，而 utf8mb4 一个字符占用 1 - 4 个字节。实际上，mysql 的 utf8 是utf8mb3的别名。如果须要保留一些占用 4 个字节的特殊字符 (比方 emoji 表情)，倡议应用utf8mb4 字符集。

比拟规定

能够通过以下语句查看 mysql 中反对的比拟规定：

SHOW COLLATION [LIKE 匹配的模式];

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: 比拟规定名称，根本合乎 字符集名称_语言_后缀 模式。第一局部 字符集名称 就是与其关联的字符集的名称结尾，第二局部示意该比拟规定作用的语言，比方 utf8_spanish_ci 是以西班牙语的规定比拟，utf8_general_ci是一种通用的比拟规定。第三局部后缀次要用来示意要不要辨别大小写和重音之类的。
2. Charset: 关联的字符集的名称。
3. Default: yes 示意是字符集默认的比拟规定。

字符集和比拟规定作用域级别

mysql 中字符集和比拟规定有 4 种作用域级别：

1. 服务器级别
2. 数据库级别
3. 表级别
4. 列级别

实际上，字符集和比拟较规定最初必定是作用在 列级别 字段上的 。能够简略的认为，如果 列级别 没有指定字符集和比拟较规定，就应用 表级别 的；如果 表级别 没有指定字符集和比拟较规定，就应用 数据库级别 的；以此类推。

服务器级别

MySQL 提供了两个零碎变量来示意服务器级别的字符集和比拟规定：

• character_set_server: 服务器级别的字符集
• collation_server: 服务器级别的比拟规定

服务器级别默认的字符集是utf8，默认的比拟规定是utf8_general_ci。

数据库级别

咱们在创立和批改数据库的时候能够指定该数据库的字符集和比拟规定，具体语法如下：

CREATE DATABASE 数据库名
     CHARACTER SET 字符集名称
     COLLATE 比拟规定名称;

ALTER DATABASE 数据库名
    CHARACTER SET 字符集名称
    COLLATE 比拟规定名称;

比方：

mysql> CREATE DATABASE charset_demo_db
    -> CHARACTER SET gb2312
    -> COLLATE gb2312_chinese_ci;
Query OK, 1 row affected (0.01 sec)

如果想查看以后数据库应用的字符集和比拟规定，能够查看上面两个零碎变量的值:

• character_set_database: 以后数据库的字符集
• collation_database: 以后数据库的比拟规定

表级别

咱们能够在创立和批改表的时候指定表的字符集和比拟规定，语法如下：

CREATE TABLE 表名
    (列的信息)
    CHARACTER SET 字符集名称
    COLLATE 比拟规定名称

ALTER TABLE 表名
    CHARACTER SET 字符集名称
    COLLATE 比拟规定名称

比方：

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

列级别

须要留神的是，对于存储字符串的列，同一个表中的不同的列也能够有不同的字符集和比拟规定。咱们在创立和批改列定义的时候能够指定该列的字符集和比拟规定，语法如下：

CREATE TABLE 表名(
    列名 字符串类型 CHARACTER SET 字符集名称 COLLATE 比拟规定名称,
    其余列...
);

ALTER TABLE 表名 MODIFY 列名 字符串类型 CHARACTER SET 字符集名称 COLLATE 比拟规定名称;

比方咱们批改一下表 t 中列 col 的字符集和比拟规定能够这么写：

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

还须要留神的一点是：因为字符集和比拟规定是互相分割的，如果咱们只批改了字符集和比拟规定，都可能引起关联的字符集和比拟规定发生变化。