MySQL原理之启蒙篇

一、MySQL的CS架构

1.客户端/服务器架构

我们平时都会使用微信，MySQL的使用过程跟这个是一样的，它的服务器程序直接和我们存储的数据打交道，然后可以有好多客户端程序连接到这个服务器程序，发送增删改查的请求，然后服务器就响应这些请求，从而操作它维护的数据。和微信一样，MySQL的每个客户端都需要提供用户名密码才能登录，登录之后才能给服务器发请求来操作某些数据。我们日常使用MySQL的情景一般是这样的：

1. 启动MySQL服务器程序。
2. 启动MySQL客户端程序并连接到服务器程序。
3. 在客户端程序中输入一些命令语句作为请求发送到服务器程序，服务器程序收到这些请求后，会根据请求的内容来操作具体的数据并向客户端返回操作结果。

安装好MySQL后我们需要重点关注MySQL的安装目录和数据目录，在我的本机Windows系统上的安装目录是C:\Program Files\MySQL\MySQL Server 8.0，数据目录是C:\ProgramData\MySQL\MySQL Server 8.0,其中安装目录下的bin目录里就包含了很多有用的客户端程序和服务器程序：

2.客户端与服务器连接的过程

运行着的服务器程序和客户端程序本质上都是计算机上的一个进程，所以客户端进程向服务器进程发送请求并得到回复的过程本质上是一个进程间通信的过程！MySQL支持下边三种客户端进程和服务器进程的通信方式。

2.1TCP/IP

真实环境中，数据库服务器进程和客户端进程可能运行在不同的主机中，它们之间必须通过网络来进行通讯。MySQL采用TCP作为服务器和客户端之间的网络通信协议。在网络环境下，每台计算机都有一个唯一的IP地址，如果某个进程有需要采用TCP协议进行网络通信方面的需求，可以向操作系统申请一个端口号，这是一个整数值，它的取值范围是0~65535。这样在网络中的其他进程就可以通过IP地址 + 端口号的方式来与这个进程连接，这样进程之间就可以通过网络进行通信了。

MySQL服务器启动的时候会默认申请3306端口号，之后就在这个端口号上等待客户端进程进行连接，用书面一点的话来说，MySQL服务器会默认监听3306端口。

如果3306端口号已经被别的进程占用了或者我们单纯的想自定义该数据库实例监听的端口号，那我们可以在启动服务器程序的命令行里添加-P参数来明确指定一下端口号，比如这样：

mysqld -P3307

如果客户端进程想要使用TCP/IP网络来连接到服务器进程，比如我们在使用mysql来启动客户端程序时，在-h参数后必须跟随IP地址来作为需要连接的服务器进程所在主机的主机名，如果客户端进程和服务器进程在一台计算机中的话，我们可以使用127.0.0.1来代表本机的IP地址。另外，如果服务器进程监听的端口号不是默认的3306，我们也可以在使用mysql启动客户端程序时使用-P参数（大写的P，小写的p是用来指定密码的）来指定需要连接到的端口号。比如我们现在已经在本机启动了服务器进程，监听的端口号为3307，那我们启动客户端程序时可以这样写：

mysql -h127.0.0.1 -uroot -P3307 -p

2.2命名管道和共享内存

如果你是一个Windows用户，那么客户端进程和服务器进程之间可以考虑使用命名管道或共享内存进行通信。不过启用这些通信方式的时候需要在启动服务器程序和客户端程序时添加一些参数：

• 使用命名管道来进行进程间通信

需要在启动服务器程序的命令中加上--enable-named-pipe参数，然后在启动客户端程序的命令中加入--pipe或者--protocol=pipe参数。

• 使用共享内存来进行进程间通信

需要在启动服务器程序的命令中加上--shared-memory参数，在成功启动服务器后，共享内存便成为本地客户端程序的默认连接方式，不过我们也可以在启动客户端程序的命令中加入--protocol=memory参数来显式的指定使用共享内存进行通信。

不过需要注意的是，使用共享内存的方式进行通信的服务器进程和客户端进程必须在同一台Windows主机中。

2.3Unix域套接字文件

如果我们的服务器进程和客户端进程都运行在同一台操作系统为类Unix的机器上的话，我们可以使用Unix域套接字文件来进行进程间通信。如果我们在启动客户端程序的时候指定的主机名为localhost，或者指定了--protocol=socket的启动参数，那服务器程序和客户端程序之间就可以通过Unix域套接字文件来进行通信了。MySQL服务器程序默认监听的Unix域套接字文件路径为/tmp/mysql.sock，客户端程序也默认连接到这个Unix域套接字文件。如果我们想改变这个默认路径，可以在启动服务器程序时指定socket参数，就像这样：

mysqld --socket=/tmp/a.txt

这样服务器启动后便会监听/tmp/a.txt。在服务器改变了默认的UNIX域套接字文件后，如果客户端程序想通过UNIX域套接字文件进行通信的话，也需要显式的指定连接到的UNIX域套接字文件路径，就像这样：

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

这样该客户端进程和服务器进程就可以通过路径为/tmp/a.txt的Unix域套接字文件进行通信了。

3.服务器处理客户端请求

其实不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？客户端可以向服务器发送增删改查各类请求，我们这里以比较复杂的查询请求为例来画个图展示一下大致的过程：

3.1连接管理

客户端进程可以采用我们上边介绍的TCP/IP、命名管道或共享内存、Unix域套接字这几种方式之一来与服务器进程建立连接，每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程来专门处理与这个客户端的交互，当该客户端退出时会与服务器断开连接，服务器并不会立即把与该客户端交互的线程销毁掉，而是把它缓存起来，在另一个新的客户端再进行连接时，把这个缓存的线程分配给该新客户端。这样就起到了不频繁创建和销毁线程的效果，从而节省开销。从这一点大家也能看出，MySQL服务器会为每一个连接进来的客户端分配一个线程，但是线程分配的太多了会严重影响系统性能，所以我们也需要限制一下可以同时连接到服务器的客户端数量。

在客户端程序发起连接的时候，需要携带主机信息、用户名、密码，服务器程序会对客户端程序提供的这些信息进行认证，如果认证失败，服务器程序会拒绝连接。另外，如果客户端程序和服务器程序不运行在一台计算机上，我们还可以采用使用了SSL（安全套接字）的网络连接进行通信，来保证数据传输的安全性。

当连接建立后，与该客户端关联的服务器线程会一直等待客户端发送过来的请求，MySQL服务器接收到的请求只是一个文本消息，该文本消息还要经过后续各种处理。

3.2解析与优化

到现在为止，MySQL服务器已经获得了文本形式的请求，接着 还要经过九九八十一难的处理，其中的几个比较重要的部分分别是查询缓存、语法解析和查询优化，下边我们详细来看。

查询缓存

MySQL服务器程序处理查询请求时，会把刚刚处理过的查询请求和结果缓存起来，如果下一次有一模一样的请求过来，直接从缓存中查找结果就好了，就不用再傻呵呵的去底层的表中查找了。这个查询缓存可以在不同客户端之间共享，也就是说如果客户端A刚刚查询了一个语句，而客户端B之后发送了同样的查询请求，那么客户端B的这次查询就可以直接使用查询缓存中的数据。

当然，MySQL服务器并没有人聪明，如果两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。另外，如果查询请求中包含==某些系统函数、用户自定义变量和函数、一些系统表==，如 mysql、information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。以某些系统函数举例，可能同样的函数的两次调用会产生不一样的结果，比如函数NOW，每次调用都会产生最新的当前时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询的结果就是错误的！

不过既然是缓存，那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE 或 DROP DATABASE 语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！

虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。其实除了频繁重建缓存和更新缓存带来的维护开销，我们现在往往都是采用专门的缓存服务如Redis来提升系统查询性能，因此不推荐在MySQL层面再铺设一层缓存，这只会增加系统的复杂度。

语法解析

如果查询缓存没有命中，接下来就需要进入正式的查询阶段了。因为客户端程序发送过来的请求只是一段文本而已，所以MySQL服务器程序首先要对这段文本做分析，判断请求的语法是否正确，然后从文本中将要查询的表、各种查询条件都提取出来放到MySQL服务器内部使用的一些数据结构上来。

这个从指定的文本中提取出我们需要的信息本质上算是一个编译过程，涉及词法解析、语法分析、语义分析等阶段，这些问题不属于我们讨论的范畴。

查询优化(重要)

语法解析之后，服务器程序获得到了需要的信息，比如要查询的列是哪些，表是哪个，搜索条件是什么等等，但光有这些是不够的，因为我们写的MySQL语句执行起来效率可能并不是很高，MySQL的优化程序会对我们的语句做一些优化，如==外连接转换为内连接、表达式简化、子查询转为连接==吧啦吧啦的一堆东西。优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的等等。

3.3存储引擎(重要)

截止到服务器程序完成了查询优化为止，还没有真正的去访问真实的数据表，MySQL服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。我们知道表是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。为了实现不同的功能，MySQL提供了各式各样的存储引擎，不同存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

为什么叫引擎呢？因为这个名字更拉风～ 其实这个存储引擎以前叫做表处理器，后来可能人们觉得太土，就改成了存储引擎的叫法，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。各种不同的存储引擎向上边的MySQL server层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像”读取索引第一条内容”、”读取索引下一条内容”、”插入记录”等等。

所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

4.存储引擎的种类

我们可以用下边这个命令来查看当前服务器程序支持的存储引擎：

SHOW ENGINES;

其中的Support列表示该存储引擎是否可用，DEFAULT值代表是当前服务器程序的默认存储引擎。Comment列是对存储引擎的一个描述，英文的，将就着看吧。Transactions列代表该存储引擎是否支持事务处理。XA列代表着该存储引擎是否支持分布式事务。Savepoints代表着该存储引擎是否支持部分事务回滚。

我们前边说过，存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

# 创建表时指定存储引擎
CREATE TABLE 表名(
    建表语句;
) ENGINE = 存储引擎名称;
# 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称;

二、MySQL的调控按钮

1.启动选项的用处

MySQL的服务器程序和客户端程序有很多设置项，比如对于MySQL服务器程序，我们可以指定诸如允许同时连入的客户端数量、客户端和服务器通信方式、表的默认存储引擎、查询缓存的大小吧啦吧啦的设置项。对于MySQL客户端程序，我们之前已经见识过了，可以指定需要连接的服务器程序所在主机的主机名或IP地址、用户名及密码等信息。

这些设置项一般都有各自的默认值，比方说服务器允许同时连入的客户端的默认数量是151，表的默认存储引擎是InnoDB，我们可以在程序启动的时候去修改这些默认值，对于这种在程序启动时指定的设置项也称之为启动选项（startup options），这些选项控制着程序启动后的行为。

2.命令行上使用选项

如果我们在启动客户端程序时在-h参数后边紧跟服务器的IP地址，这就意味着客户端和服务器之间需要通过TCP/IP网络进行通信。因为我的客户端程序和服务器程序都装在一台计算机上，所以在使用客户端程序连接服务器程序时指定的主机名是127.0.0.1的情况下，客户端进程和服务器进程之间会使用TCP/IP网络进行通信。如果我们在启动服务器程序的时候就禁止各客户端使用TCP/IP网络进行通信，可以在启动服务器程序的命令行里添加skip-networking启动选项，就像这样：

mysqld --skip-networking

再举一个例子，我们前边说过如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用InnoDB作为表的存储引擎。如果我们想改变表的默认存储引擎的话，可以这样写启动服务器的命令行：

mysqld --default-storage-engine=MyISAM

在命令行上使用启动选项有两种形式：长选项和短选项，以长选项为例格式为–启动选项1[=值1] –启动选项2[=值2] … –启动选项n[=值n]

3.配置文件中使用选项

在命令行中设置启动选项只对当次启动生效，也就是说如果下一次重启程序的时候我们还想保留这些启动选项的话，还得重复把这些选项写到启动命令行中，这样真的神烦唉！于是设计MySQL的大叔们提出一种配置文件（也称为选项文件）的概念，我们把需要设置的启动选项都写在这个配置文件中，每次启动服务器的时候都从这个文件里加载相应的启动选项。由于这个配置文件可以长久的保存在计算机的硬盘里，所以只需我们配置一次，以后就都不用显式的把启动选项都写在启动命令行中了，所以我们推荐使用配置文件的方式来设置启动选项。

3.1.配置文件的路径

在Windows操作系统中，MySQL会按照下列路径来寻找配置文件：

在阅读这些Windows操作系统下配置文件路径的时候需要注意一些事情：

• 在给定的前三个路径中，配置文件可以使用.ini的扩展名，也可以使用.cnf的扩展名。
• %WINDIR%指的是你机器上Windows目录的位置，通常是C:\WINDOWS。
• BASEDIR指的是MySQL安装目录的路径。
• 第四个路径指的是我们在启动程序时可以通过在命令行中指定defaults-extra-file参数的值来添加额外的配置文件路径。
• %APPDATA%表示Windows应用程序数据目录的值。
• 列表中最后一个名为.mylogin.cnf配置文件有点儿特殊，它不是一个纯文本文件（其他的配置文件都是纯文本文件），而是使用mysql_config_editor实用程序创建的加密文件。文件中只能包含一些用于启动客户端软件时连接服务器的一些选项，包括 host、user、password、port和 socket。而且它只能被客户端程序所使用。

3.2配置文件的内容

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

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

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

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

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

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

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

像这个配置文件里就定义了许多个组，组名分别是server、mysqld、mysqld_safe、client、mysql、mysqladmin。每个组下边可以定义若干个启动选项，我们以[server]组为例来看一下填写启动选项的形式（其他组中启动选项的形式是一样的）：

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

配置文件中不同的选项组是给不同的启动命令使用的，如果选项组名称与程序名称相同，则组中的选项将专门应用于该程序。例如， [mysqld]和[mysql]组分别应用于mysqld服务器程序和mysql客户端程序。不过有两个选项组比较特别：

• [server]组下边的启动选项将作用于所有的服务器程序。
• [client]组下边的启动选项将作用于所有的客户端程序。

3.3配置文件的优先级

我们前边唠叨过MySQL将在某些固定的路径下搜索配置文件，我们也可以通过在命令行上指定defaults-extra-file启动选项来指定额外的配置文件路径。MySQL将按照我们在上表中给定的顺序依次读取各个配置文件，如果该文件不存在则忽略。值得注意的是，如果我们在多个配置文件中设置了相同的启动选项，那以最后一个配置文件中的为准。

我们说同一个命令可以访问配置文件中的多个组，比如mysqld可以访问[mysqld]、[server]组，如果在同一个配置文件中，在这些组里出现了同样的配置项，比如这样：

[server]
default-storage-engine=InnoDB

[mysqld]
default-storage-engine=MyISAM

那么，将以最后一个出现的组中的启动选项为准，比方说例子中default-storage-engine既出现在[mysqld]组也出现在[server]组，因为[mysqld]组在[server]组后边，就以[mysqld]组中的配置项为准。

如果我们不想让MySQL到默认的路径下搜索配置文件（就是上表中列出的那些），可以在命令行指定defaults-file选项，比如这样（以UNIX系统为例）：

mysqld --defaults-file=/tmp/myconfig.txt

这样，在程序启动的时候将只在/tmp/myconfig.txt路径下搜索配置文件。如果文件不存在或无法访问，则会发生错误。

注意defaults-extra-file和defaults-file的区别，使用defaults-extra-file可以指定额外的配置文件搜索路径（也就是说那些固定的配置文件路径也会被搜索）。

3.4命令行和配置文件中启动选项的区别

在命令行上指定的绝大部分启动选项都可以放到配置文件中，但是有一些选项是专门为命令行设计的，比方说defaults-extra-file、defaults-file这样的选项本身就是为了指定配置文件路径的，再放在配置文件中使用就没啥意义了。

另外有一点需要特别注意，如果同一个启动选项既出现在命令行中，又出现在配置文件中，那么以命令行中的启动选项为准！

总的纲领：

1. 局部大于全局
2. 显式大于隐式

4.系统变量

MySQL服务器程序运行过程中会用到许多影响程序行为的变量，它们被称为MySQL系统变量，比如允许同时连入的客户端数量用系统变量max_connections表示，表的默认存储引擎用系统变量default_storage_engine表示，查询缓存的大小用系统变量query_cache_size表示，MySQL服务器程序的系统变量有好几百条，我们就不一一列举了。每个系统变量都有一个默认值，我们可以使用命令行或者配置文件中的选项在启动服务器时改变一些系统变量的值。大多数的系统变量的值也可以在程序运行过程中修改，而无需停止并重新启动它。

4.1.查看系统变量

mysql> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%default%';
+------------------------------------------+-----------------------+
| Variable_name                            | Value                 |
+------------------------------------------+-----------------------+
| default_authentication_plugin            | caching_sha2_password |
| default_collation_for_utf8mb4            | utf8mb4_0900_ai_ci    |
| default_password_lifetime                | 0                     |
| default_storage_engine                   | InnoDB                |
| default_table_encryption                 | OFF                   |
| default_tmp_storage_engine               | InnoDB                |
| default_week_format                      | 0                     |
| explicit_defaults_for_timestamp          | ON                    |
| innodb_default_row_format                | dynamic               |
| mysqlx_deflate_default_compression_level | 3                     |
| mysqlx_lz4_default_compression_level     | 2                     |
| mysqlx_zstd_default_compression_level    | 3                     |
+------------------------------------------+-----------------------+
12 rows in set, 1 warning (0.00 sec)

4.2设置系统变量

4.2.1通过启动选项设置

# 通过命令行添加启动选项
mysqld --default-storage-engine=MyISAM --max-connections=10
# 通过配置文件添加启动选项
[server]
default-storage-engine=MyISAM
max-connections=10

4.2.2服务器程序运行过程中设置

系统变量比较牛逼的一点就是，对于大部分系统变量来说，它们的值可以在服务器程序运行过程中进行动态修改而无需停止并重启服务器。不过系统变量有作用范围之分，下边详细唠叨下。

我们前边说过，多个客户端程序可以同时连接到一个服务器程序。对于同一个系统变量，我们有时想让不同的客户端有不同的值。比方说狗哥使用客户端A，他想让当前客户端对应的默认存储引擎为InnoDB，所以他可以把系统变量default_storage_engine的值设置为InnoDB；猫爷使用客户端B，他想让当前客户端对应的默认存储引擎为MyISAM，所以他可以把系统变量default_storage_engine的值设置为MyISAM。这样可以使狗哥和猫爷的的客户端拥有不同的默认存储引擎，使用时互不影响，十分方便。但是这样各个客户端都私有一份系统变量会产生这么两个问题：

• 有一些系统变量并不是针对单个客户端的，比如允许同时连接到服务器的客户端数量max_connections，查询缓存的大小query_cache_size，这些公有的系统变量让某个客户端私有显然不合适。
• 一个新连接到服务器的客户端对应的系统变量的值该怎么设置？

---

为了解决这两个问题，设计MySQL的大叔提出了系统变量的作用范围的概念，具体来说作用范围分为这两种：

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

---

在服务器启动时，会将每个全局变量初始化为其默认值（可以通过命令行或选项文件中指定的选项更改这些默认值）。然后服务器还为每个连接的客户端维护一组会话变量，客户端的会话变量在连接时使用相应全局变量的当前值初始化。

这话有点儿绕，还是以default_storage_engine举例，在服务器启动时会初始化一个名为default_storage_engine，作用范围为GLOBAL的系统变量。之后每当有一个客户端连接到该服务器时，服务器都会单独为该客户端分配一个名为default_storage_engine，作用范围为SESSION的系统变量，该作用范围为SESSION的系统变量值按照当前作用范围为GLOBAL的同名系统变量值进行初始化。

很显然，通过启动选项设置的系统变量的作用范围都是GLOBAL的，也就是对所有客户端都有效的，因为在系统启动的时候还没有客户端程序连接进来呢。了解了系统变量的GLOBAL和SESSION作用范围之后，我们再看一下在服务器程序运行期间通过客户端程序设置系统变量的语法：

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

比如我们想在服务器运行过程中把作用范围为GLOBAL的系统变量default_storage_engine的值修改为MyISAM，也就是想让之后新连接到服务器的客户端都用MyISAM作为默认的存储引擎，那我们可以选择下边两条语句中的任意一条来进行设置：

语句一：SET GLOBAL default_storage_engine = MyISAM;
语句二：SET @@GLOBAL.default_storage_engine = MyISAM;

如果只想对本客户端生效，也可以选择下边三条语句中的任意一条来进行设置：

语句一：SET SESSION default_storage_engine = MyISAM;
语句二：SET @@SESSION.default_storage_engine = MyISAM;
语句三：SET default_storage_engine = MyISAM;

从上边的语句三也可以看出，如果在设置系统变量的语句中省略了作用范围，默认的作用范围就是SESSION。也就是说SET 系统变量名 = 值和SET SESSION 系统变量名 = 值是等价的。

既然系统变量有作用范围之分，那我们的SHOW VARIABLES语句查看的是什么作用范围的系统变量呢？

答：默认查看的是SESSION作用范围的系统变量。

当然我们也可以在查看系统变量的语句上加上要查看哪个作用范围的系统变量，就像这样：

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

mysql> SHOW SESSION VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

mysql> SET SESSION default_storage_engine = MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

mysql>

如果某个客户端改变了某个系统变量在GLOBAL作用范围的值，并不会影响该系统变量在当前已经连接的客户端作用范围为SESSION的值，只会影响后续连入的客户端在作用范围为SESSION的值。

注意事项：并不是所有系统变量都具有GLOBAL和SESSION的作用范围。

• 有一些系统变量只具有GLOBAL作用范围，比方说max_connections，表示服务器程序支持同时最多有多少个客户端程序进行连接。

• 有一些系统变量只具有SESSION作用范围，比如insert_id，表示在对某个包含AUTO_INCREMENT列的表进行插入时，该列初始的值。

• 有一些系统变量的值既具有GLOBAL作用范围，也具有SESSION作用范围，比如我们前边用到的default_storage_engine，而且其实大部分的系统变量都是这样的，

• 有些系统变量是只读的，并不能设置值。

  比方说version，表示当前MySQL的版本，我们客户端是不能设置它的值的，只能在SHOW VARIABLES语句里查看。

4.3启动选项和系统变量的区别

启动选项是在程序启动时我们程序员传递的一些参数，而系统变量是影响服务器程序运行行为的变量，它们之间的关系如下：

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

5.状态变量

为了让我们更好的了解服务器程序的运行情况，MySQL服务器程序中维护了好多关于程序运行状态的变量，它们被称为状态变量。比方说Threads_connected表示当前有多少客户端与服务器建立了连接，Handler_update表示已经更新了多少行记录吧啦吧啦。

由于状态变量是用来显示服务器程序运行状况的，所以它们的值只能由服务器程序自己来设置，我们程序员是不能设置的。与系统变量类似，状态变量也有GLOBAL和SESSION两个作用范围的，所以查看状态变量的语句可以这么写：

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

类似的，如果我们不写明作用范围，默认的作用范围是SESSION，比方说这样：

mysql> SHOW STATUS LIKE 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 2     |
| Threads_connected | 1     |
| Threads_created   | 3     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.00 sec)

三、字符集和比较规则

MySQL支持好多好多种字符集，查看当前MySQL中支持的字符集可以用下边这个语句：

SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];

可以看到，我使用的这个MySQL版本一共支持41种字符集，其中的Default collation列表示这种字符集中一种默认的比较规则。大家注意返回结果中的最后一列Maxlen，它代表该种字符集表示一个字符最多需要几个字节。

查看MySQL中支持的比较规则的命令如下：

SHOW COLLATION [LIKE 匹配的模式];

我们前边说过一种字符集可能对应着若干种比较规则，MySQL支持的字符集就已经非常多了，所以支持的比较规则更多，我们先只查看一下utf8mb3字符集下的比较规则：

1.不同级别的字符集和比较规则

MySQL有4个级别的字符集和比较规则，分别是：

• 服务器级别

  • character_set_server表示服务器级别的字符集，collation_server表示服务器级别的比较规则。

• 数据库级别

  • CREATE DATABASE 数据库名
        [[DEFAULT] CHARACTER SET 字符集名称]
        [[DEFAULT] COLLATE 比较规则名称];
    
    ALTER DATABASE 数据库名
        [[DEFAULT] CHARACTER SET 字符集名称]
        [[DEFAULT] COLLATE 比较规则名称];
      - `character_set_database`表示当前数据库的字符集，`collation_database`表示当前默认数据库的比较规则，这两个系统变量是只读的，不能修改。如果没有使用USE指定当前默认数据库，则变量与相应的服务器级系统变量具有相同的值。
    
    - 表级别
    
      - ```shell
        CREATE TABLE 表名 (列的信息)
            [[DEFAULT] CHARACTER SET 字符集名称]
            [COLLATE 比较规则名称]];
        
        ALTER TABLE 表名
            [[DEFAULT] CHARACTER SET 字符集名称]
            [COLLATE 比较规则名称];
    

• 列级别

  • CREATE TABLE 表名(
        列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
        其他列...
    );
    
    ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];
    >在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。
    >
    >修改列时必须小心字符集的兼容性，修改表则没有这个担忧，因为具体列的字符集已经确定了，修改表的字符集只会影响表结构后续新增列的默认字符集。
    
    由于字符集和比较规则是互相有联系的，如果我们只修改了字符集，比较规则也会跟着变化，如果只修改了比较规则，字符集也会跟着变化，具体规则如下：
    
    - `只修改字符集，则比较规则将变为修改后的字符集默认的比较规则`。
    - `只修改比较规则，则字符集将变为修改后的比较规则对应的字符集`。
    
    我们介绍的这4个级别字符集和比较规则的联系如下：
    
    - 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
    - 如果创建表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
    - 如果创建数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则
    
    ### 2.客户端和服务器通信中的字符集
    
    ![image_1d6dfk4orjaj1ra8536aijb99.png-112.4kB](imgs/MySQL原理之启蒙篇/f87857b928f84edca6a6cf127b3fda5atplv-k3u1fbpfcp-jj-mark2268000q75.webp) 
    
    从这个分析中我们可以得出这么几点需要注意的地方：
    
    - 服务器认为客户端发送过来的请求是用`character_set_client`编码的。
    
      假设你的客户端采用的字符集和 ***character_set_client*** 不一样的话，这就会出现意想不到的情况。比如我的客户端使用的是`utf8mb4`字符集，如果把系统变量`character_set_client`的值设置为`ascii`的话，服务器可能无法理解我们发送的请求，更别谈处理这个请求了。
    
    - 服务器将把得到的结果集使用`character_set_results`编码后发送给客户端。
    
      假设你的客户端采用的字符集和 ***character_set_results*** 不一样的话，这就可能会出现客户端无法解码结果集的情况，结果就是在你的屏幕上出现乱码。比如我的客户端使用的是`utf8mb4`字符集，如果把系统变量`character_set_results`的值设置为`ascii`的话，可能会产生乱码。
    
    - `character_set_connection`只是服务器在将请求的字节串从`character_set_client`转换为`character_set_connection`时使用，它是什么其实没多重要，但是一定要注意，该字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用`character_set_connection`代表的字符集进行编码。比如你把`character_set_client`设置为`utf8mb4`，把`character_set_connection`设置成`ascii`，那么此时你如果从客户端发送一个汉字到服务器，那么服务器无法使用`ascii`字符集来编码这个汉字，就会向用户发出一个警告。
    
    知道了在`MySQL`中从发送请求到返回结果过程里发生的各种字符集转换，但是为啥要转来转去的呢？不晕么？
    
    答：是的，很头晕，所以我们通常都把 ***character_set_client*** 、***character_set_connection***、***character_set_results*** 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便我们设置，`MySQL`提供了一条非常简便的语句：
    
    ```shell
    SET NAMES 字符集名;
    

这一条语句产生的效果和我们执行这3条的效果是一样的：

SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

另外，如果你想在启动客户端的时候就把character_set_client、character_set_connection、character_set_results这三个系统变量的值设置成一样的，那我们可以在启动客户端的时候指定一个叫default-character-set的启动选项，比如在配置文件里可以这么写：

[client]
default-character-set=gbk

它起到的效果和执行一遍SET NAMES gbk是一样一样的，都会将那三个系统变量的值设置成gbk。

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

• 客户端发送的请求字节序列是采用哪种字符集进行编码的。
  这一步骤主要取决于操作系统当前使用的字符集；对于Windows操作系统来说，还与客户端启动时设置的default-character-set启动选项有关。
• 服务器接收到请求字节序列后会认为它是采用哪种字符集进行编码的。
  这一步骤取决于系统变量character-set-client的值。
• 服务器在运行过程中会把请求的字节序列转换为以哪种字符集编码的字节序列。
  这一步骤取决于系统变量character-set-connection的值。
• 服务器在向客户端返回字节序列时，是采用哪种字符集进行编码的。
  这一步骤取决于系统变量character-set-results的值。
• 客户端在收到响应字节序列后，是怎么把它们写到命令行终端的。
  这一步骤取决于操作系统当前使用的字符集；对于Windows操作系统来说，还与客户端启动时设置的default-character-set启动选项有关。