MySQL原理（一）

目录

一、理解MySQL的服务器与客户端关系

1：MySQL服务器与客户端

2：服务器处理客户端请求

3：常见的存储引擎

二、字符集和比较规则

1：字符集和比较规则简介

2：字符集和比较规则应用

3：乱码原因（通用）

4：MySQL处理乱码

5：比较规则

---

一、理解MySQL的服务器与客户端关系

1：MySQL服务器与客户端

​ 运行着的服务器程序和客户端程序本质上都是计算机上的一个进程，所以客户端进程向服务区进程发送请求并得到回复的过程本质上是一个进程间通信的过程

MySQL支持的三种客户端进程和服务器进程的通信方式：

• TCP/IP协议（通常配合SSL安全套接字连接）
• 命名通道（Windows用户）或共享内存（需要同一台Windows）
• Unix域套接字

2：服务器处理客户端请求

效果：客户端进程向服务器进程发送一段文字（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）

过程（可参考下图）：客户端 -> 处理连接 -> 查询缓存 -> 语法解析 -> 查询优化 -> 存储引擎 -> 文件系统

大致三部分：连接管理、解析与优化、存储引擎

连接管理（三种客户端进程与服务器进程通信方式）：通常使用TCP/IP方式，客户端和服务器不再同一台主机上时，采用SSL（安全套接字）连接，期间MySQL服务器会缓存连接线程（不会立即销毁线程）

解析与优化：

• 查询缓存：MySQL服务器在处理完客户端A的查询语句后，客户端B发来相同的查询语句（一定要相同，任何字符上的不同都会导致缓存不被命中，包括空格、注释、大小写，还有内部系统函数调用也不会命中缓存），MySQL8.0被删除，原因是性能提升和维护缓存成本占比太低；性能提升太少，而维护成本太高，一个查询每次都要检索缓存（没有命中），而且查询处理完之后，还要更新缓存，维护这块缓存

• 语法解析：缓存未被命中，那么下一步就是把客户端发送过来的文本进行解析，先检验语法是否正确，然后从文本中抽取出要查询的表、字段以及条件等放入MySQL服务器内部使用的一些数据结构上（词法解析、语法分析、语义分析等阶段）

• 查询优化：光有一些表和条件是不够的，运行效率可能不是很高，MySQL的优化程序可以帮我们做一些优化，比如外连接转为内连接，表达式优化，子查询转为连接等，最后生产一个执行计划，执行计划表面会使用到哪些索引进行查询，表之间的连接顺序是怎么样的等待，关键字EXPLAIN

存储引擎：上述所有过程其实都没有真正的访问真实的数据表，MySQL服务器会把数据的存储和提取操作都封装到一个叫做 “存储引擎” 的模块中（存储引擎负责如何读取数据，如何写入数据到本地中），不同的存储引擎具体表的存储结构不一定相同，采用的算法也不一定相同。

简单理解：（连接管理、查询缓存、语法解析、查询优化）这些不涉及真实数据存储的功能划分为MySQL Server，而真正存取数据的功能划分为 存储引擎 。存储引擎提供统一调用的接口（存储引擎API）包含几十个底层函数，像“读取索引第一条内容”、“插入记录”等，MySQL Server完成查询优化后，只需要按照生成的执行计划调用底层存储引擎提供的API即可获取数据并返回客户端。

3：常见的存储引擎

重点的为红色，次要的为绿色，其它随意

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

show ENGINES;# Engine 	-> 存储引擎
# Support 	-> 是否支持（DEFAULT 默认使用）
# Comment 	-> 描述
# XA 		-> 是否支持分布式事务
# Transactions 	-> 是否支持事务
# Savepoints 	—> 是否支持部分事务回滚

不同的表，可以设置不同的存储引擎

# 格式：
create table 表名(建表字段
) engine = 存储引擎名称;# 实例：
create table tb1 (name varchar(255)
) engine = MyISAM;

# 修改表的存储引擎格式：
alter table 表名 engine = 存储引擎名称;# 实例：
alter table tb1 engine = InnoDB;

MySQL启动配置的选项：长形式和短形式（长形式需要使用双-- 和完整名称 & 短形式需要使用单- 和首字符）

下面列举常见的选项

二、字符集和比较规则

1：字符集和比较规则简介

编码、解码、字符集概念

编码（字符->二进制）：将一个字符映射成一个二进制数据的过程

如：‘a’ -> 00000001 (十六进制：0x01)

解码（二进制->字符）：将一个二进制数据映射到一个字符的过程

如：00000010 (十六进制：0x02) -> ‘b’

字符集：描述某个字符范围的编码规则。

如：‘baB’ -> 000000100000000100000100 (十六进制：0x020104)

MySQL中的 utf8 和 utf8mb4

MySQL中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以MySQL中存在阉割版utf8和正宗的utf8

• utf8mb3：阉割版utf8字符集，只是用1~3个字节表示字符
• utf8mb4：正宗utf8字符集，使用1~4个字符标识字符

MySQL中utf8是utf8mb3的别名，所以MySQL中的utf8意味着使用1~3个字节来表示一个字符

如果需要使用4个字节编码一个字符时，如存储一些emoji表情，那就使用utf8mb4

MySQL中常用的一些字符集，以及对应的最大字节表示一个字符（Maxlen）

# 查看MySQL支持的所有字符集及其Maxlen（下图为常用的字符集）
show character set;

比较规则：每种字符对应若干比较规则，每种字符集都有一种默认的比较规则

后缀	英文释义	描述
_ai	accent insensitive	不区分重音
_as	accent sensitive	区分重
_ci	case insensitive	不区分大小写
_cs	case sensitive	区分大小写
_bin	binary	以二进制

utf8字符集默认的比较规则是 utf8_general_ci ，比较规则的结尾是 ci ，查表可知是不区分大小写比较

2：字符集和比较规则应用

# 查看MySQL支持的utf8所有比较规则
SHOW COLLATION LIKE 'utf8\_%';
# 查看当前字符集
SHOW VARIABLES LIKE 'character_set_server';
# 查看当前字符集的比较规则
SHOW VARIABLES LIKE 'collation_server';

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

服务器级别、数据库级别、表级别、列级别

服务器级别的字符集: character_set_server
服务器级别的比较规则: collation_server当前数据库的字符集: character_set_database
当前数据库的比较规则: collation_database表级别的字符集，在创建表末尾指定: character set 字符集名称
表级别的比较规则，在创建表末尾指定: collate 比较规则名称列级别的字符集和比较规则，在列名末尾指定: character set 字符集名称 collate 比较规则名称

如果下层未指定字符集或比较规则名称，则依次向上取，比如，列级别的没有设置字符集，那会从表级别去找字符集，如果表级别字符集没有设置，则再去找当前数据的字符集，直至找到

补充：由于字符集和比较规则是相互联系的，所以如果我们修改一项，对应的一项会随之改变（取默认的）

3：乱码原因（通用）

字符串实际在计算机中就是一个字节串。通常我们看到的乱码，都是不同解码导致的。

比如：”我“ 字在utf8中编码为 0xE68891，现在你在你的电脑上用utf8进行编码，你发给你的同事，然而你的同事不知道你用的是utf8编码，于是他用gbk进行解码，发现在gbk编码表中 0xE68891 对应的字符是 ”鎴 “

解释 ”鎴“ 字来源：

• 首先第一个字节 0xE6，它的值大于 0x7F（对应十进制127），说明此时是两个字节编码，然后读取下一个字节 0xE688 ，从gbk编码表中查找发现是 ”鎴“
• 然后还有剩下的 0x91 ，它的值也大于 0x7F，但是再往后读字节发现没有了，于是 0x91 是半个字符
• 于是 你同事的电脑中解码得到的字符是 ”鎴“和半个字符（下图为原本utf8编码，但是gb2312解码图片）

由此可知：如果对于同一个字符串编码和解码使用的字符集不一样，那么结果会让你摇头

4：MySQL处理乱码

客户端字符串 -> 服务器处理 -> 返回客户端

• 使用操作系统（比如windows）的字符集（假设为gbk）编码请求字符串
• 从character_set_client（和你操作系统要一致）转化为character_set_connection（中间编码）
• 从character_set_connection转化为具体列（假设为utf8）使用的字符集
• 将查询结果从具体的列使用的字符集转化为character_set_results（gbk）
• 使用操作系统的字符集解码响应字节串

也就是说，客户端的字符集要和 character_set_client & character_set_connection 一致，不然会解码错误；当然，还有一个中间编码character_set_connection，它也是有要求的，字符集所包含的字符必须大于客户端的字符集，不然你客户端一个中文，但是中间编码是ascii，肯定搞不了，对吧

于是：通常我们把 character_set_client & character_set_connection & character_set_results 设置成与客户端字符集一致的情况。

# 一个语句设置三个参数
set names 字符集名;# 当然也可以一个一个设置，但是不建议
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

5：比较规则

比较规则通常是用于比较字符串大小的表达式以及对某个字符列进行排序，有时也被叫做排序规则

tb1表中包含name列，现在对name进行全局排序（order by），列包含 ”我 A a b B“

• 如果比较规则是 gbk_chinese_ci 那么比较的时候，就不会考虑大小写，那么答案是：A a b B 我
• 如果比较规则是 gbk_bin 那么比较的时候，就比较对应字符的二进制，答案变成：A B a b 我