《MySQL 是怎样运行的：从根儿上理解 MySQL》笔记

关注

原创 2022-06-30 18:00:10 5.1k 阅读 AI 写同款· GEO 优化 ›

MySQL 是怎样运行的：从根儿上理解 MySQL（下）.pdf 立即下载

首先，8折优惠码了解一下，比公众号的9折给力。

![8折优惠](https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xMzU3MjYzMy04ZjFlY2JhODMwYWFmM2E0LnBuZw?x-oss-process=image/format,png

重新认识MySQL

前面主要是一些安装使用MySQL的过程，没必要记笔记
客户端与服务器连接过程，MySQL支持一下三种客户端进程与服务端进程通讯方式：
1. TCP/IP
2. 命名管道和共享内存（Windows操作系统中的两种进程间通信方式）
3. Unix域套接字文件
服务器处理客户端请求过程
1. 连接管理
  服务器进程会为每个连接进来的客户端分配一个线程，当客户端断开连接时候，不会马上销毁该线程，而是缓存起来，这样节省开销。线程分配过多会影响性能，所以可以限制同时连接到服务器的客户端数量。
2. 解析与优化
  - 查询缓存
    - 命中缓存：一样的请求
    - 不命中
      - 两次请求有任何字符的不同，都不命中
      - 请求中包含系统函数，用户自定义变量与函数，一些系统表，比如使用now()函数
    - 缓存失效：缓存系统检测涉及到的每张表，只要表的结构或者数据修改，缓存就失效并删除。
  - 语法优化
    - 判断语法是否正确，将文本中要查询的表、各种查询条件提取出来放到MySQL服务器内部使用的一些数据结构上来。本质上是一个编译过程。
  - 查询优化
    - 优化查询效率，优化的结果就是生成一个执行计划。可以使用EXPLAIN语句查看执行计划。
3. 存储引擎
  - 负责数据的存储和提取。
  - 为MySQL曾提供统一接口。
  - 常用InnoDB、MyISAM。
  - 查看支持的存储引擎。
    - Transactions：是否支持事物
    - XA：是否支持分布式事务
    - Savepoints：是否支持部分事务回滚

启动选项和配置文件

可以通过启动选项或者配置文件设置
系统变量中的SESSION和GLOBAL
- SET [GLOBAL|SESSION] 系统变量名 = 值;
- SET [@@(GLOBAL|SESSION).]var_name = XXX;
- GLOBAL：全局变量，影响服务器整体
- SESSION：回话（local）变量，影响某个客户端的操作
- 设置变量时候默认作用范围是SESSION，设置时候需要注意。
- 有的系统变量没有作用范围，有的系统变量只读不可设置
状态变量
- 显示程序运行状态，不可设置
- 有GLOBAL和 SESSION
- SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];
- 同样默认是SESSION

字符集和比较规则

一种字符集可以有很多种规则
常用字符集
- ASCII
- ISO 8859-1：latin1
- GB2312
- GBK
- UTF-8
MySQL支持的字符集和排序规则
- utf8mb3：阉割版utf8，使用1-3个字节表示字符。
- utf8mb4：正式版，使用1-4个字节表示字符。
- 查看支持的字符集
  SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
查看MySQL支持的比较规则
SHOW COLLATION [LIKE 匹配的模式];
- 比较规则的命名
  - 字符集_语言_后缀
  - 后缀的含义
    - _ai：不区分重音
    - _as：区分重音
    - _ci：不区分大小写
    - cs：区分大小写
    - _bin：以二进制比较
字符集和比较规则的应用
1. MySQL有四个级别的字符集和比较规则
  - 服务器级别：
    - 查看：character_set_server 、collation_server
    - 配置：启动时，配置文件或者启动项设置character_set_server=gbk collation_server=gbk_chinese_ci两个变量。运行时通过set语句修改以上两个变量的值来修改。
  - 数据库级别：
    - 查看：character_set_database 、collation_database ，注意这两个变量是只读的。
    - 配置：建库时候配置，若未配置使用服务器级别的。也可以使用ALTER DATABASE语句修改。
  - 表级别
    - 查看：查看建表语句。
    - 配置：建表时候设置，若未设置使用数据库级别默认的。已存在的表通过ALTER TABLE语句修改。
- 列级别
  - 查看：查看建表语句。
  - 配置：建表时未配置使用表默认的。存在的使用ALTER TABLE table_name MODIFY column ...语句修改。
1. 只修改字符集，比较规则变为字符集默认的。
  只修改比较规则，字符集变成比较规则对应的。
客户端和服务器通信的字符集
1. MySQL中字符集的转换
  - 转换过程中使用的3个系统变量
    - character_set_client服务器解码请求时使用的字符集，服务器会认为客户端发送过来的请求是此变量对应的字符集编码的，若不是则会出错。
    - character_set_connection 服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
    - character_set_results 服务器向客户端返回数据时使用的字符集，客户端需要使用这个字符集来解码。
2. SET NAMES 字符集名，可以将以上三个系统变量设置为同一个值。

InnoDB记录存储结构

由于磁盘相对于内存等比较慢，一条条读取代价太高。所以InnoDB将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
- SHOW GLOBAL STATUS like 'Innodb_page_size';查看页大小

InnoDB数据页结构

B+树索引

B+树索引的使用

MySQL的数据目录

InnoDB的表空间

单表访问方法

连接的原理

MySQL给予成本的优化

InnoDB统计数据的收集

MySQL基于规则的优化

Explain

id
1. 查询优化器可能对设计子查询的查询语句进行重写，从而转换为连接查询。所以如果想知道查询优化器是否对某个包含子查询的语句进行了重写，查看执行计划中各个表的id值就行。
select_type
- SIMPLE
  查询语句中不包含UNION或子查询，连接查询也是
- PRIMARY
  包含UNION、UNION ALL或者子查询的大查询，最左边的查询
- UNION
  UNION或者UNION AL，除了最左边的查询，其余的查询
- UNION RESULT
  MySQL使用临时表完成UNION的去重，该临时表的查询
- SUBQUERY
  如果包含子查询的查询语句不能转为semi-join，且该子查询是不相关子查询，并且查询优化器决定将该子查询物化来执行，该子查询的第一个SELECT关键字代表的查询就是SUBQUERY（SUBQUERY的子查询只会执行一次）
- DEPENDENT SUBQUERY
  如果包含子查询的查询语句不能转为semi-join，且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询就是DEPENDENT SUBQUERY（DEPENDENT SUBQUERY的子查询可能执行多次）
- DEPENDENT UNION
  在包含UNION或UNION ALL的大查询中，如果各个小查询都依赖外层查询，拿出了最左边的那个小查询外，其余的小查询就是DEPENDENT UNION
- DERIVED
  采用物化的方式执行的包含派生表的查询，该派生表对应的子查询就是DERIVED
- MATERIALIZED
  查询优化器选择将包含子查询的语句的子查询物化之后与外层查询进行连接查询时，该子查询就是MATERIALIZED
- UNCACHEABLE SUBQUERY
- UNCACHEABLE UNION
partitions
分区
type