mysql 整体

1 字符集与排序规则

1.1

  • 1 字符集是一组字符及其编码方式的集合。

MySQL 支持多种字符集，如 latin1、utf8mb3、utf8mb4 等。
utf8mb3 是 MySQL 中对 UTF-8 的一种实现，最大支持 3 字节编码，无法存储如表情符号等需要 4 字节的字符。
utf8mb4 是真正的 UTF-8 实现，支持最多 4 字节编码，能完整支持 Unicode 字符，包括 emoji 和其他扩展字符。

  • 2 排序规则定义了字符的比较规则，包括大小写敏感性、重音符号处理等。

例如：
utf8mb4_general_ci：不区分大小写，适用于多语言排序。
utf8mb4_unicode_ci：基于 Unicode 标准，更精确地处理多语言字符。
utf8mb4_0900_ai_ci：MySQL 8.0 引入的新排序规则，支持更准确的 Unicode 排序。
utf8mb4_bin：二进制排序，区分大小写。

1.2 各级别字符集与排序规则

在 MySQL 中，“级别”指的是字符集和排序规则可以设置的层级范围，从大到小分为：‌服务器级别 → 数据库级别 → 表级别 → 列级别‌。每一级都可以单独设置，如果未显式指定，则会继承上一级的设置。

  • 服务器级别
#在 my.cnf 或 my.ini 配置文件中添加
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci
  • 数据库级别‌

在创建或修改数据库时，可以指定该数据库的默认字符集和排序规则。‌数据库中的所有表默认会继承此设置。


-- 创建数据库时指定
CREATE DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_0900_ai_ci;

-- 修改现有数据库
ALTER DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;
  • 表级别‌

在创建或修改表时，可以指定该表的默认字符集和排序规则。‌表中的所有列默认会继承此设置。‌

-- 创建表时指定
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 修改现有表
ALTER TABLE users 
  DEFAULT CHARACTER SET utf8mb4 
  COLLATE utf8mb4_bin;

1.3JDBC 连接字符串配置

jdbc:mysql://localhost:3306/dbname?useUnicode=true&characterEncoding=utf8mb4

1.4 MySQL 字符集在 SQL 请求与响应中的变化详解

1.4.1 character_set_client‌：‌客户端字符集‌。

服务器使用此变量来‌解读‌客户端发送来的 SQL 语句的原始字节流。它告诉服务器：“客户端发送给我的数据是用什么编码的。”

1.4.2 character_set_connection‌：‌连接字符集‌。

服务器将接收到的 SQL 语句（已从 character_set_client 解码）‌转换‌为此字符集，用于后续的字符串字面量处理、比较和内部运算。

1.4.3 character_set_results‌：‌结果集字符集‌。

服务器在向客户端返回‌结果‌（包括数据行和元数据，如列名）时，使用此字符集进行编码。它告诉服务器：“我应该用什么编码把数据打包发回给客户端。”

1.4.4 字符集转换流程

1.4.4.1示例一

  1. 客户端发送 SQL 请求（INSERT）， INSERT INTO users (name) VALUES ('张三');
    在程序内存中，字符串 '张三' 以其内部编码（如 Java 的 UTF-16）存在。通过 JDBC 驱动，它会被转换为连接时指定的字符集（例如 UTF-8）的字节流。

  2. 服务器接收与解码‌：字节流通过网络到达 MySQL 服务器。服务器查看当前会话的 character_set_client 设置（例如 utf8mb4）。‌服务器使用 character_set_client 的编码规则来解码接收到的字节流‌，将其还原为正确的字符。

  3. 连接层转换‌：解码后的语句文本，会被服务器从 character_set_client 的编码‌转换‌为character_set_connection 的编码。

  4. 存储转换‌：当执行 INSERT 时，服务器需要将值 '张三' 存入 users.name 字段。此时会发生又一次转换：值从 character_set_connection 编码‌转换‌为‌目标字段的字符集‌（users.name 列的字符集，例如 utf8mb4）。

此阶段关键点‌：character_set_client 是‌解码输入‌的钥匙；character_set_connection 是‌内部处理‌的沙盒；最终存储依赖于‌字段本身的字符集‌。

1.4.4.2 示例二

  1. 客户端执行 SELECT name FROM users WHERE id = 1; 时，服务器从磁盘读取数据。数据以字段字符集（utf8mb4）的形式存储。在将数据（'张三'）发送回客户端之前，服务器会查看当前会话的 character_set_results 设置。‌服务器将数据从存储的字符集转换到 character_set_results 指定的字符集‌，并编码为字节流。

  2. 客户端接收与解码‌：字节流发送到客户端。客户端‌必须‌使用与 character_set_results ‌相匹配的字符集‌来解码这个字节流，才能正确显示“张三”。

此阶段关键点‌：character_set_results 是‌编码输出‌的规则。客户端必须用它来“解包”服务器发回的数据。

1.5 MySQL 5.7 与 8.0 的主要区别

特性 MySQL 5.7 MySQL 8.0
默认字符集 latin1 utf8mb4
默认排序规则 utf8_general_ci utf8mb4_0900_ai_ci
支持的字符集 utf8mb3、latin1 等 utf8mb4、utf8mb3（已废弃）
排序规则 utf8_general_ci、utf8_unicode_ci 等 utf8mb4_0900_ai_ci、utf8mb4_unicode_ci 等

2 逻辑架构

2.1图示

图1
image
图2
image

2.2

2.2.1 连接层

  • 客户端访问 MySQL 服务器，建立 TCP 连接。

  • 经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。

    • 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行。
    • 用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依
      赖于此时读到的权限。

  • mysql有个连接池，TCP 连接收到请求后，分配给一个线程专门与这个客户端的交互。去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

  • 查看 MySQL 服务被多少个客户端连接，命令：show processlist
    image

    • 比如上图的显示结果，共有两个用户名为 root 的用户连接了 MySQL 服务，其中 id 为 6 的用户的 Command 列的状态为 Sleep ，这意味着该用户连接完 MySQL 服务就没有再执行过任何命令，也就是说这是一个空闲的连接。

  • MySQL 服务支持的最大连接数由 max_connections 参数控制，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

  • 长短连接

// 短连接
连接 mysql 服务（TCP 三次握手）
执行sql
断开 mysql 服务（TCP 四次挥手）

// 长连接
连接 mysql 服务（TCP 三次握手）
执行sql
执行sql
执行sql
....
断开 mysql 服务（TCP 四次挥手）

一般是推荐使用长连接

2.2.2 服务层

1.按顺序执行下面

  • SQL Interface: SQL接口
    接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT ... FROM就是调用SQL Interface

  • Caches & Buffers： 查询缓存组件

    • Caches，用Key-Value形式存，key是sql语句，value是结果。比如第一次“select name from employee”，就会缓存起来，但是第二次查询时，必须与第一次的语句一样，多一个空格都不行，比如“select name from employee”，“name from”中间比第一次的语句多了个空格。
      • 查询缓存挺鸡肋的。对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。
      • 从MySQL 5.7.20开始，不推荐使用查询缓存。

  • Parser: 解析器
    在解析器中对 SQL 语句进行语法分析、语义分析，生成语法树

  • Optimizer: 查询优化器SQL语句
    在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划。
    这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连
    接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将
    查询结果返回给用户。

2.2.3 引擎层

插件式存储引擎层（ Storage Engines），与存储层进行交互

2.2.4 存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在 文件系统上，以文件的方式存
在的，并完成与存储引擎的交互。

2.3 查询流程

image

2.3.1 连接通信

2.3.2 查询缓存

用Key-Value形式存，key是sql语句，value是结果，大部分情况下作用不大。

  • 如果表经常更新数据，缓存经常失效，只适用于很少更新数据的表，比如配置表
  • 而且很严格，比如第一次“select name from employee”查询，第二次“select name from employee”，就查不到，注意“select name”这里，多了个空格，就查不到了。
  • 8.0开始，该功能已经没了
  • 5.7中设置缓存的开启，2表示按需使用
    image
  • 按需使用，看下面的例子。默认情况（没有加SQL_CACHE）不使用
    • 告诉mysql要使用
      image

2.3.3 解析

  1. 词法分析，例如，SQL语句 select username from userinfo
关键字 非关键字 关键字 非关键字
select username from userinfo

  1. 语法分析，构建出 SQL 语法树（解析树），如果我们输入的 SQL 语句语法不对，就会在解析器这个阶段报错
    image

  2. 预处理

  • 版本8.0 开始会检查 SQL 查询语句中的表或者字段是否存在；

    • 对于 MySQL 5.7 判断表或字段是否存在的工作，是在语法分析之后，预处理阶段之前做的。

  • 将 select * 中的 * 符号，扩展为表上的所有列；

2.3.4 优化器

优化器主要负责将 SQL 查询语句的执行计划确定下来。比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。比如怎么连接查询比较好。
这个执行计划表明应该使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何

2.3.5 执行

经历完优化器后，就确定了执行方案，接下来 MySQL 就真正开始执行语句了，这个工作是由「执行器」完成的。在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。

2.3.6 缓存和响应

响应查询结果，如果开了缓存就缓存

2.4 查看SQL执行计划

Profiling‌ 是一个内置的性能分析工具，用于‌逐语句记录 SQL 执行的详细时间消耗‌，包括每个阶段（如解析、优化、执行、排序、IO 等）所花费的时间。它能帮你回答：“我的这条 SQL，到底慢在哪儿？”

2.4.1 MySQL8

select @@profiling，0 代表关闭
image

set profiling=1 设置为打开自动记录当前会话中所有执行的 SQL 语句的性能剖面数据‌。
image

  1. profile就是一条执行计划，profiles是全部的执行计划
    image

  2. 查看最近的一条
    image
    也可以指定查看那一条
    image
    image

2.4.2 MySQL5.7

2.5 sql执行顺序

posted @ 2026-04-11 22:20  dvdhellohaha  阅读(18)  评论(0)    收藏  举报