MySQL 启动选项和字符集

1. 客户端和服务器

1.1 服务器程序

1. 数据库实例：代表 MySQL 服务器程序的进程（ mysqld 可执行文件）

2. mysqld_safe：启动脚本，会间接调用 mysqld 并监控服务器运行状态。出现错误时可以帮助重启服务器程序，输出错误日志。

3. mysql.server：启动脚本，会间接调用 mysqld_safe 。
   启动服务器程序 mysql.server start
   关闭服务器程序 mysql.server stop

4. mysqld_multi：启动多个服务器实例。

1.2 客户端程序

mysql -h主机名 -u 用户名 -p密码 （mysql 是可执行文件）
-h 服务器进程所在计算机的域名或IP地址。运行在本机时可省略
-p 后面最好不要跟着密码，如果非要写注意 -p 和密码之间不要有空格（其他短形式 -P -V -h 和 -u可以有）
关闭客户端的方式：quit、exit、\q

1.3 客户端和服务器的连接

MySQL 的服务器程序和客户端本质上都是计算机上的一个进程，因此它们之间的交互本质上是一个进程间通信的过程。

（1）TCP/IP

MySQL 服务器默认监听 3306 端口
指定端口号启动服务器进程

mysqld -P3307

TCP/IP方式下，-h参数后必须跟随 IP 地址，本机的为127.0.0.1。-P指定需要连接的端口号

mysql -h127.0.0.1 -u root -P3307 -p

（2）命名管道和共享内存

Windows 系统可以使用。

命名管道：启动服务器时加上 --enable-named-pipe ，启动客户端时加上 --pipe 或 --protocol=pipe
共享内存：启动服务器时加上 --shared-memory ，启动客户端时加上 --protocol=memory；使用共享内存进行通信的客户端进程和服务器进程必须在同一台 Windows 主机中。

（3）UNIX 域套接字

客户端和服务器都在类 UNIX 的同一台机器上，启动客户端时没有指定主机名，或主机名为localhost，或者指定了 --protocol=socket

MySQL 服务器程序默认监听的 UNIX 域套接字文件为 /tmp/mysql.sock（8.0 版本的为 /var/run/mysqld/mysqld.sock）；

如果想更改

mysqld --socket=/tmp/a.txt

客户端为

mysql -hlocalhost -uroot --socket=/tmp/a.txt -p

1.4 服务器处理客户端的请求

（1）连接管理
MySQL 服务器采用线程池的方式，为每个连接进来的客户端分配一个线程。（客户端的数量会有限制，默认为 max_connections = 151，严格来讲还有额外一个是为超级用户准备的）
客户端发起连接时，需要携带主机信息、用户名、密码等信息，服务器会进行验证。

MySQL 服务器接收到的请求只是一个文本消息。

（2）Server 层和存储引擎层交互，一般是以记录为单位。

Server 层包含所有的内置函数

• 连接器：权限管理、维持和管理连接，客户端太长时间没有交互会自动断开（wait_timeout控制）
  • 有长短连接之分
  • 使用长连接会导致内存增长（运行过程中的内存是管理在连接对象中的，资源只有连接断开时才释放）：
    • 定期断开长连接，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连
    • 在每次执行一个大的操作后，执行 mysql_reset_connection 来重新初始化连接资源，这个过程不需要重连和重新做权限验证；
• 查询缓存：静态表适用（MySQL 8.0之后删除了此模块）
• 分析器
  • 词法分析：识别关键字、表名列名替换
  • 语法分析：是否满足语法要求，生成一颗解析树
  • 预处理器：检查解析树的合法性，如表、列是否存在、是否歧义等
• 优化器
• 执行器
  • 先判断是否对表有执行权限
  • rows_examined统计执行器调用几次（因为索引下推等特性，与存储引擎扫描行数可能不一致）

（3）存储引擎

InnoDB 支持事务、行级锁、外键（默认存储引擎）
MyISAM 主要的非事务处理存储引擎
MEMORY 数据只存储在内存中；多用于临时表

XA 代表是否支持分布式事务。

ALTER TABLE 表名 ENGINE=存储引擎名；// 修改表的存储引擎

2. 启动选项和系统变量

（1）设置都有各自的默认值。

启动选项：在程序启动时指定的设置项，可在启动时修改这些默认值；各单词之间可用 - 或 _ 连接；

系统变量：运行过程中用到的变量；各单词只能用 _ 连接；

（2）启动选项和系统变量的关系

大部分系统变量都可以当作启动选项传入；
有些系统变量是在程序运行过程中自动生成的，不可以当作启动选项设置，例如 character_set_client；
有些启动变量也不是系统变量，如 defaults-file。

2.1 命令行上使用选项

特点：只对当次启动有效。
各启动选项之间使用空格分隔。选项名、=、选项值之间不可以有空格。

–启动选项1[=值1] --启动选项2[=值2]
mysqld --skip-networking //---------------------------------- 禁止各客户端使用TCP/IP进行通信
mysqld --default-storage-engine=MyISAM //------------ 修改默认存储引擎
mysql --help // ---------------------------------------------------- mysqld_safe等都是这种方式
mysqld --verbose --help // ------------------------------------ 比较特殊

2.2 配置文件中使用选项

配置文件中的启动选项被划分为若干个组，每个组都有一个组名。（把服务器的启动选项写在客户端组下，不会生效）
在多个配置文件中设置相同的启动选项，以最后一个文件中的为准

在同一个配置文件的不同组中设置相同的启动选项，以最后一个出现的组中的启动选项为准

mysqld --defaults-file=/tmp/myconfig.txt //---------------------------------- 忽略默认路径，只在 /tmp/myconfig.txt 搜索配置文件
defaults-extra-file 指定额外的配置文件路径，这两个只能在命令行中指定

同一个启动选项即出现在命令行中，又出现在配置文件中，以命令行中的为准。

2.3 系统变量

（1）作用范围

GLOBAL：影响服务器的整体操作；
SESSION：只影响某个客户端连接的操作（会话变量）；服务器会为每个连接的客户端维护一组会话变量，大部分的会话变量在客户端连接时使用响应的全局变量的当前值进行初始化。

（2）设置和查看系统变量

通过启动选项设置的都为 GLOBAL；

在运行期间设置系统变量，省略作用范围的话默认为 SESSION；

SET [GLOBAL|SESSION] 系统变量名 = 值；
SET [@@(GLOBAL|SESSION) . ]系统变量名 = 值；

查看系统变量；如果某个系统变量没有 SESSION 作用范围，即使使用了 SESSION 修饰符，显示的是 GLOBAL 作用范围的值。

SHOW [GLOBAL|SESSION] VARIABLES LIKE ‘系统变量名’；

（3）注意事项

有些系统变量是只读的，如 version；
有些只有 GLOBAL 作用范围，如 max_connections
有些只有 SESSION 作用范围，如insert_id，表示对某个包含 AUTO_INCREMENT 列的表插入时，该列的初始值。

2.4 状态变量

服务器程序运行状态的变量，只能由服务器程序自己设置。

SHOW [GLOBAL|SESSION] STATUS LIKE ‘状态变量名’；

3. MySQL 字符集和比较规则

utf8mb3：删减过的 UTF-8 字符集，只使用 1 ~ 3 字节表示字符。可以表示大部分常用的字符
utf8mb4：正宗的 UTF-8 字符集，使用 1 ~ 4 字节表示字符，包含 emoji 表情。（8.0 后设置为默认的字符集）

MySQL 中表示字符集的名称使用小写形式。

（1）比较规则后缀含义

_as/ai 表示是否区分重音
_cs/ci 表示是否区分大小写

3.1 应用

MySQL 有 4 个级别的字符集和比较规则；

（1）服务器级别；使用系统变量character_set_server和collation_server描述

在本人电脑下，分别为 utf8mb4 和 utf8mb4_0900_ai_ci ，说明不区分重音和大小写。

（2）数据库级别；

创建数据库时指定该数据库的字符集和比较规则（默认和服务器级别一致）

CREATE/ALTER DATABASE 数据库名
【 CHARACTER SET 字符集名】
【 COLLATE 比较规则名】

USE 数据库名；后可以查看character_set_database和collation_database描述系统变量值。

（3）表级别

和数据库类似，默认采用表所在的数据库的字符集和比较规则。

（4）列级别；同一个表的不同列可以有不同的字符集和比较规则

CREATE TABLE 表名(
列名 字符串类型 【CHARACTER SET 字符集名】【COLLATE 比较规则名】，
其他列…
);
ALTER TABLE 表名 MODIFY 列名 字符串类型 【CHARACTER SET 字符集名】【COLLATE 比较规则名】；

修改列的字符集时，如果列中存储的数据不能使用修改后的字符集表示，会发生错误。

（5）字符集和比较规则之间相互关联。只修改其中一个，另一个也会改变

3.2 通信过程中的字符集

下述 3 个系统变量，均是 SESSION 级别。服务器会为每个客户端维护这 3 个变量。

（0）MySQL 启动客户端，确定客户端的默认字符集；

1. 自动检测操作系统的字符集；并按照一定规则映射为 MySQL 支持的字符集（一般直接采用操作系统的字符集，特例 ascii 会映射为 latin1）
   在类 UNIX 系统中，由 LC_ALL > LC_CTYPE > LANG 这 3 个系统变量表示（ locale 命令查看 ）。 如果这 3 个都没有设置，操作系统当前使用的字符集就是默认字符集。设置为客户端的默认字符集。
   一般要把终端展示字符时采用的编码和操作系统当前使用的编码保持一致，否则写入命令会乱码；

2. 不支持 \ 不允许自动检测时，MySQL会使用它自己的内建的默认字符集作为客户端默认字符集，MySQL 8.0 中为 utf8mb4；

3. 如果设置了default-character-set启动选项，会将该值作为客户端的默认字符集，并且在连接服务器时会将character_set_client\connecton\results这 3 个系统变量的值初始化为客户端的默认字符集；
   也可以使用 SET NAMES charset_name一次性修改上述 3 个系统变量的值，但不会改变客户端发送请求时使用的字符集。

服务器知道客户端的默认字符集后，就会把 character_set_client\connecton\results 这 3 个系统变量的值初始化为客户端的默认字符集；

（1）客户端发送请求

1. 在 UNIX 系统中，使用输入软件在终端输入命令时，使用的是操作系统当前使用字符集；
   键入命令后，涉及应用程序和操作系统交互，会使用 read 系统调用，之后再发送给服务器；

2. 在 WIN 系统中，会涉及将操作系统当前使用字符集转换为客户端默认字符集，再发送到服务器

（2）服务器接收请求（对接收的请求进行解码）

服务器会将收到的请求当作是使用系统变量 character_set_client进行编码的字节序列。

（3）服务器处理请求（内部表达方式和比较规则）

通过第 2 步我们已经知道了接收的都是哪些字符，但这些字符的表达方式和比较规则还不知道。

因此真正处理时，会将其转换为使用 SESSION 级别的系统变量 character_set_connection 对应字符集编码的字节序列

如果这里的编码和列采用字符集冲突，以列的字符集和比较规则优先。

（4）服务器生成响应（对结果进行编码）

表中的字符可能是采用用户指定的不同字符集进行编码的，这里会将结果使用 character_set_results 字符集进行编码，再发送给客户端。

（5）客户端收到响应

直接就把接收到的字节序列通过系统调用 write 写到终端中，因此要与终端展示字符集一致。

对类 UNIX 系统会使用操作系统当前使用的字符集解码
对 Win 系统会使用客户端默认的字符集解码

（6）不同字符集之间的转换

采用 Unicode 作为中间编码

3.3 通信过程中的字符集注意事项

（1）这里要特别注意选择的字符集是否支持需要

如果客户端使用不同的字符集，比如 C1 使用 default-character-set = latin1，C2 使用 utf8mb4;

其中 C1 创建一个表，设置该表的字符集为 uft8，并且插入了一些中文字符，那么在 C1 处访问这些中文字符是正常的，C2 访问该表数据时就会乱码；

虽然这里插入的字符会转换为表的字符集latin1，但latin1是不支持中文的，因此在转换时就会出现问题，所以 C2 访问时就会乱码；

4. 临时增加数据库性能

（1）可以断开事务外（不处于事务中的）空闲太久连接

查看 information_schema 库的 innodb_trx 表中的 trx_mysql_thread_id 展示了哪些线程还在事务中；

参考声明

《MySQL 是怎样运行的》