MySQL 容易被忽略的小知识

本篇文章主要介绍一些我感觉容易被忽略的小细节，主要从「查询请求执行过程」「启动选项」「系统变量」「字符集和比较规则」四个角度展开

查询请求执行过程

连接管理

首先，查询请求之前，客户端肯定需要和服务器连接，所以第一部分是连接管理。连接方式有三种：「TCP/IP」「命名管道或共享内存」「UNIX 域套接字」

注意：命令mysql -h127.0.0.1 -uroot -p使用的是「TCP/IP」连接方式；而命令mysql -hlocalhost -uroot -p使用的是「UNIX 域套接字」连接方式

• localhot (local) 不经网卡传输！这点很重要，它不受网络防火墙和网卡相关的的限制

• 127.0.0.1 是通过网卡传输，依赖网卡，并受到网络防火墙和网卡相关的限制

• 一般设置程序时本地服务用 localhost 是最好的，localhost 不会解析成 ip，也不会占用网卡、网络资源

当每个客户端连接到服务器时，服务器都会为该客户端创建一个线程专门和该客户端交互。如果客户端断开连接，服务器并不会立马销毁该线程，而是缓存起来分配给其他客户端使用，避免了频繁的创建销毁线程

解析与优化

查询缓存：服务器会把每次的查询和结果缓存起来，以供之后相同的查询可直接使用该结果。但是每条查询请求必须任何字符都相同 (如：空格、注释、大小写) 才可以命中缓存。如果某个表发生了修改，那么与该表相关的所有缓存都会被删除

语法解析：对查询语句的语法进行解析，类似于编译原理中的词法解析，语法分析，语义分析

查询优化：可能人写的查询求情效率没有那么高，所以 MySQL 会对语句进行优化处理 (还挺聪明的！！)

存储引擎

MySQL 把数据的存储和提取操作封装到了一个名为存储引擎的模块中，各种不同的存储引擎为上层提供统一的调用接口

启动与连接

注意：以下命令都是基于「MacOS」系统

启动服务端

• mysqld：该命令不常用

• mysqld_safe：底层调用命令mysqld，优点在于当服务器经常出现错误停止时，可以帮助重启，且可以记录错误日志

• mysql.server start：底层调用命令mysqld_safe

• mysql.server stop：关闭服务

• mysqld_multi：可以在一个服务器上运行多个服务器实例

连接服务端

• mysql -h主机名 -u用户名 -p密码

• exit或quit或\q

关于存储引擎的操作

查看当前服务器支持的存储引擎：show engines

查看表的创建结构：show create table engine_demo_table\G

创建表时指定存储引擎：

xxxxxxxxxx
create table if not exists `engine_demo_table`(
   `id` int
)engine=InnoDB;

修改表的存储引擎：

xxxxxxxxxx
alter table engine_demo_table engine=MyISAM;

启动选项

命令行上使用选项

刚刚提到了启动服务端有：mysqld，如果我们现在使用mysqld --skip-networking启动表示禁止「TCP/IP」方式的连接。如：mysql -h127.0.0.1 -uroot -p

刚刚我们用命令show engines看到了服务器的默认存储引擎，如果我们现在使用mysqld --default-storage-engine=MyISAM启动表示服务器默认引擎改为「MyISAM」

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

注意：等号两边不能有空格。错误例子：mysqld --default-storage-engine = MyISAM ❌

小提示：如果有些选项存在短形式，可以使用-短形式，且启动选项和值之间可以有空格

• 例子 ( 三者等价)：mysqld --port=3307、mysqld -p3307、mysqld -p 3307

配置文件中使用选项

注意：命令行中的启动项优先级最高！！！

除了在命令行上使用选项，还可以在配置文件中使用选项，下面给出基于「MacOS」系统的配置文件路径 (优先级从上到下依次递增)

如果下述路径中没有对应文件，可自己创建一个

配置文件格式 (优先级从上到下依次递增)

xxxxxxxxxx
[server]
option1
option2 = value2

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

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

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

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

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

程序对应类别和能读的组

系统变量

查看系统变量：show variables [like 匹配模式]

通过启动选项设置系统变量有两种方式：

• 通过命令行添加启动选择：mysqld --default-storage-engine=MyISAM --max-connections=10

• 通过配置文件添加启动选项：

  default-storage-engine = MyISAM
  [server]
  default-storage-engine = MyISAM
  max-connections = 10
  

除了在启动 MySQL 服务时设置系统变量，还可以在服务运行过程中设置系统变量。在服务器启动时会初始化一个系统变量，作用范围为GLOBAL，之后每当有一个客户端连接到该服务器时，服务器会单独为该客户端分配一个作用范围为SESSION的系统变量。下面给出三种常用命令：

• 设置不同作用范围的系统变量：set [GLOBAL|SESSION] 系统变量名 = 值或set [@@(GLOBAL|SESSION).] 系统变量名 = 值

• 只对本客户端生效 (SESSION 级别)：set 系统变量名 = 值

• 查看不同作用范围的系统变量：show [GLOBAL|SESSION] variables [like 匹配模式]

注意：

• 并不是所有的系统变量都有GLOBAL和SESSION的作用范围

• 有些系统变量只读不可写

状态变量

与系统变量相对的还有状态变量，表示服务器运行的状态，不可修改

查看不同作用范围的状态变量：show [GLOBAL|SESSION] status [like 匹配模式]

字符集和比较规则

计算机中只有 0 和 1，那么怎么表示一个字符呢？所以就需要设定一种规则用不同的 01 组合来表示不同的字符，这就叫「编码」

不同字符都是不同的 01 组合，那么如何来比较不同字符的大小呢？所有就需要一种「比较规则」

这里给出三种常用的编码：

• ASCII：一共 128 个，用 1 字节 (8 bit) 表示

• GBK：用 1-2 字节表示

• UTF-8：用 1-4 字节表示

MySQL 中的 utf8 和 utf8mb4

utf8mb3：阉割版的 UTF-8，只使用 1-3 个字符表示

utf8mb4：正宗的 UTF-8，使用 1-4 个字符表示

注意：MySQL 中，utf8 是 utf8mb3 的别名

查看字符集：show charset

查看比较规则：show collation [like 匹配的模式]

• _ci表示不区分大小写 (case insensitive)

各级别的字符集和比较规则

服务器级别

可以在配置文件中设置这两个变量的值

show variables like 'character_set_server'

show variables like 'collation_server'

数据库级别

xxxxxxxxxx
# 创建数据库时设置字符集和比较规则
create database 数据库名
    [[default] character set 字符集名称]
    [[default] collate 比较规则名称];
# 修改数据库的字符集和比较规则
alter database 数据库名
    [[default] character set 字符集名称]
    [[default] collate 比较规则名称];
# 查看数据库的字符集和比较规则
use 数据库名;
show variables like 'character_set_database';

表级别

xxxxxxxxxx
# 创建表时设置字符集和比较规则
create table 表名
    [[default] character set 字符集名称]
    [[default] collate 比较规则名称];
# 修改表的字符集和比较规则
use 数据库名;
alter table 表名
    [[default] character set 字符集名称]
    [[default] collate 比较规则名称];
# 查看表的字符集和比较规则
show create table 表名\G;

列级别

xxxxxxxxxx
# 创建表时设置字符集和比较规则
create table 表名 (
    列名 字符串类型 [character set 字符集名称] [collate 比较规则名称],
    其他列...
);
# 修改列的字符集和比较规则
alter table 表名 modify 列名 字符串类型 [character set 字符集名称] [collate 比较规则名称];
# 查看列的字符集和比较规则
show create table 表名\G;

客户端和服务端通信过程中使用的字符集

下表为通信相关的系统变量及其含义

客户端在初次连接服务端的时候，客户端会将默认的字符集信息与用户名、密码等信息一起发送给服务器，服务器在收到后会将 character_set_client、character_set_connection、character_set_result 这三个系统变量的值初始化成客户端默认字符集

从发送请求到接受响应的过程中发生的字符集转换如下图所示：

如果 OS character 和 character_set_result 不一致的话，终端界面将会显示乱码！！！