跳转至

9. MySQL逻辑架构和性能优化概述

MySQL 逻辑架构

architecture

MySQL 逻辑架构整体分为三层：

  • 连接层：最上层为客户端层，并非 MySQL 所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。
  • 中间层：MySQL 大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
  • 存储引擎层：最下层为存储引擎，其负责 MySQL 中的数据存储和提取。和 Linux 中的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过 API 与存储引擎通信，这些 API 接口屏蔽了不同存储引擎间的差异。

MySQL Server 是一个单进程多线程的服务程序，在 MySQL Server 上 用 ps -ef | grep mysqld 就能看到其系统进程 ID 了。

MySQL 请求处理流程

  1. 客户端向 MySQL 服务器发送一条查询请求。
  2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
  3. 服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划。
  4. MySQL 根据执行计划，调用存储引擎的 API 来执行查询。
  5. 将结果返回给客户端，同时缓存查询结果。

每一个客户端发起一个新的请求都由服务器端的 连接/线程处理工具 负责接收客户端的请求，并在服务端内存中开辟一个新的内存空间，生成一个新的线程。

当每一个用户连接到服务器端的时候就会在进程地址空间里生成一个新的线程用于响应客户端请求，用户发起的查询请求都在线程空间内运行，结果也在这里面缓存并返回给服务器端。

线程的重用和销毁都是由 连接/线程处理管理器 实现的。

综上所述：用户发起请求，连接/线程处理器开辟内存空间，开始提供查询的机制。

用户总是希望 MySQL 能够获得更高的查询性能，最好的办法是弄清楚 MySQL 是如何优化和执行查询的。

一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让 MySQL 的优化器能够按照预想的合理方式运行而已。

以 MySQL 为例，数据库在执行SQL语句时，需要经历 7 个步骤：

  • 词法分析：将SQL语句分解成一个个token（关键字、标识符、运算符，常量等），然后对token进行分类和解析，生成相应的数据结构。

  • 语法分析：根据SQL语法检测规则检查语法是否正确，并生成语法树。比如检查SELECT关键字是否写错。

  • 语义分析：遍历语法树，确定表和列等信息，同时检查语义的正确性。比如检查表是否存在，列是否存在，是否有权限等。

          - 比如在语法树的投影中遇到"*"，语义分析器就知道需要把它展开，这个展开的过程就需要去系统字典表中找到该业务表所对应的列属性的信息，用这些列属性信息把"*"替换掉。

  • 优化处理：使用优化器对SQL语句进行处理和优化，比如执行计划、索引等。查询重写

  • 执行计划：使用执行计划生成器生成SQL语句的执行计划，比如数据的访问方式，索引的使用方式等。

  • 引擎执行：将执行计划发送给相应的数据库引擎进行处理，执行计划被翻译成底层的操作指令，执行数据扫描、索引查找、排序、分组等操作。

  • 返回数据：将执行结果返回给客户端，比如查询结果集或操作结果。

在这里，我们粗暴的把执行过程理解成两步，即：先编译SQL语法结构（1-3 步），再执行 SQL 语句（4-7 步）。

一个查询语句在不同的阶段，生成的树是不同的，这些树的顺序应该是先生成语法树，然后得到查询树，最终得到计划树，计划树就是我们说的执行计划。

查询树就是查询优化器的输入，经过逻辑优化和物理优化，最终产生一颗最优的计划树，而我们要做的就会看看查询优化器是如何产生这棵最优的计划树的。

正常情况下，用户输入的参数会直接参与 SQL 语法的编译，而预编译则是先构建语法树，确定 SQL 语法结构以后，再拼接用户的参数。

客户端/服务端通信协议

一般来说，不需要去理解 MySQL 通信协议的内部实现细节，只需要大致理解通信协议是如何工作的。

MySQL 客户端/服务端 通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。

这种协议让 MySQL 通信简单快速， 但是也从很多地方限制了 MySQL。一个明显的限制是， 这意味着没法进行流量控制。

一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

客户端提交一个 SQL 请求给服务器时是直接用一个单独的数据包发送的，所以当查询语句很长的时候，比如一次性提交插入大量数据时，需要注意控制 max_allowed_packet 参数。

# max_allowed_packet表示服务器所能处理的请求包的最大值。默认是64MB，最大值是1GB。MySQL 8.0单个packet可以允许的最大值是1GB
# 客户端和服务端都有自己的max_allowed_packet变量，所以要调节此参数时，必须同时增加server端和client端的配置变量。
max_allowed_packet=64MB


# 每个客户端线程都关联了 1 个连接缓冲区（connection buffer）和 1 个结果集缓冲区（result buffer）。
# 这 2 个缓冲区的初始大小都由 net_buffer_length 控制，需要时最大可以自动增长到不超过 max_allowed_packet。
# 每条 SQL 语句执行完成后，结果集缓冲区都会自动恢复到 net_buffer_length 指定的大小。

# 一般情况下，不应该修改 net_buffer_length 的值，如果这个值设置得非常小，你可以把它修改为客户端发送的 SQL 语句预期的长度。
# 如果 SQL 语句长度超过这个值，连接缓冲区还会自动增长。net_buffer_length 的最大值为 1048576（1M）。

MySQL 是 C/S 结构，client 与 server 之间的通信需要遵循约定的规则，C/S 之间通信的最小单元就是 packet，通信约定的规则就是 packet 的结构。

一个传输的包（a communication packet ）代表着：

  • 客户端发送到 mysql 服务端的单个 SQL STATEMENT
  • 服务端发送到客户端的单行数据
  • master 发往 slave 的一个 binary log event

但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。当服务器收到大于 max_allowed_packet 字节的信息包时，将发"信息包过大"错误，并关闭连接。

于某些客户端，如果通信信息包过大，在执行查询期间，可能会遇到"丢失与 MySQL 服务器的连接"错误。

与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。

这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果后就"粗暴"地断开连接，都不是好主意。

因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯。

这也是查询中尽量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一。

引自《高性能 MySQL》。

当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是 MySQL 在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。

InnoDB 的数据是保存在主键索引上的，所以全表扫描实际上是直接扫描表的主键索引，对于单表超过系统内存的情况，查询会不会把数据都读到内存中导致系统内存耗尽呢？

显然不会，实际上服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：

  • 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。

  • 重复获取行，直到 net_buffer 写满，调用网络接口发出去。

  • 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。

  • 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大，并不会达到 200G；

socket send buffer 也不可能达到 200G（默认定义/proc/sys/net/core/wmem_default），如果 socket send buffer 被写满，就会暂停读数据的流程。

也就是说，MySQL 是 边读边发的，这个概念很重要。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。

MySQL 插件体系

MySQL 现在很多模块都是通过plugin的方式连接到 MySQL 核心中的，除了大家熟悉的存储引擎都是Plugin之外，MySQL 还支持其他类型的plugin。

select  * from information_schema.plugins  -- where plugin_name like "%daemon%";

密码强度插件

在 MySQL 官方文档中，The Password Validation Component 应该翻译为组件更为合适，不过实际上都差不多，这里我们就不做过多赘述。

克隆插件

连接器/连接管理

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

客户端使用命令行登陆时连接命令：mysql -h${ip} -P${port} -u${user} -p${password}

在完成 TCP 三次握手之后，连接器就要开始认证身份进行账密校验，校验通过之后，连接器会到权限表里查询拥有的权限之后在这个连接里的权限判断，SQL 执行的权限都依赖于此时读取的权限。

这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

MySQL 客户端，当使用默认参数连接的时候，MySQL 客户端会提供一个本地库名和表名补全的功能。

为了实现这个功能，客户端在连接成功后，需要多做一些操作：

  • 执行 show databases；
  • 切到 db1 库，执行 show tables；
  • 把这两个命令的结果用于构建一个本地的哈希表。

在这些操作中，最花时间的就是第三步在本地构建哈希表的操作。所以，当一个库中的表个数非常多的时候，这一步就会花比较长的时间。也就是说，感知到的连接过程慢，其实并不是连接慢，也不是服务端慢，而是客户端慢。

如果在连接命令中加上 -A，就可以关掉这个自动补全的功能，然后客户端就可以快速返回了。

这里自动补全的效果就是，你在输入库名或者表名的时候，输入前缀，可以使用 Tab 键自动补全表名或者显示提示。实际使用中，如果你自动补全功能用得并不多，建议每次使用的时候都默认加 -A。

MySQL 客户端发送请求后，接收服务端返回结果的方式有两种：

一种是本地缓存，也就是在本地开一片内存，先把结果存起来。如果你用 API 开发，对应的就是 mysql_store_result 方法。 另一种是不缓存，读一个处理一个。如果你用 API 开发，对应的就是 mysql_use_result 方法。 MySQL 客户端默认采用第一种方式，而如果加上–quick 参数，就会使用第二种不缓存的方式。

采用不缓存的方式时，如果本地处理得慢，就会导致服务端发送结果被阻塞，因此会让服务端变慢。

为什么要给这个参数取名叫作 quick 呢？

这是因为使用这个参数可以达到以下三点效果：

第一点，跳过表名自动补全功能。

第二点，mysql_store_result 需要申请本地内存来缓存查询结果，如果查询结果太大，会耗费较多的本地内存，可能会影响客户端本地机器的性能；

第三点，是不会把执行命令记录到本地的命令历史文件。 –quick 参数的意思，是让客户端变得更快。

MySQL 连接线程模型

MySQL 是一个 单进程多线程 的软件，启动一个 MySQL 实例，操作系统中会使用 mysqld 这个可执行文件来启动一个 mysqld 进程。

mysqld通过创建多个线程来服务于不同的用户连接。通常情况下，随着用户连接数的增加，MySQL 内部用于处理用户连接的线程也会同步的增加，在一定范围内，增加用户并发连接，对提高系统的吞吐量有一定的帮助，然而用户并发连接数超过某个阈值，MySQL 的性能反而会降低。

每个线程至少有两个唯一标识符： 一个是操作系统线程 ID， 另一个是 MySQL 内部线程 ID。

  • THREAD_ID: MySQL 内部的线程ID，MySQL数据库内部分配给每个线程的唯一标识符。
  • PROCESSLIST_ID: 连接 ID，每个前台线程都有一个指定的 PROCESSLIST_ID 连接标识符。我们平时 kill processlist_id 就是这个
  • THREAD_OS_ID: 操作系统线程 ID

上述 ID 均可以通过 performance_schema.threads 查询。对于操作系统线程 ID 可以通过系统相关工具查看， 如在Linux系统中可使用ps -eLf命令查看。

我们打开 htop 或 top 时，如果查看以线程方式查看，就可以看到很多 mysqld 线程。这些就是用于处理客户端连接而创建的线程。

MySQL 线程管理

MySQL 是一个单进程多线程的程序，根据 type 可以分为 BACKGROUND 和 FOREGROUND 线程。可通过 performance_schema.threads 表查看。

使用 htop 也可以看到 MySQL 服务的各个线程。

后台线程
thread/innodb/srv_master_thread srv_master_thread 是最重要的后台主线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、UNDO 页的回收等。

前台线程

MySQL 处理用户连接的都是前台线程。可以使用如下语句查看：

select * from `performance_schema`.threads where type='FOREGROUND';

SELECT * FROM `information_schema`.PROCESSLIST;


SELECT 
    THREAD_ID AS 'MySQL线程ID',
    THREAD_OS_ID AS '操作系统线程ID',
    NAME AS  'NAME',
    IF(TYPE = 'BACKGROUND', 'YES', '') AS "是否后台线程" ,
    PROCESSLIST_ID AS  "连接ID"
FROM performance_schema.threads;

MySQL 内部处理用户连接的线程调度方式由 thread_handling 参数控制。严格来说有三种：

  • no-threads，单线程处理所有用户连接，一般在调试时使用。
  • one-thread-per-connection ， 多线程处理用户连接，一个线程对应一个用户连接，也是 MySQL Community Server 默认的连接处理方式。主要由 thread_handling 参数配置。
  • Thread pool, 在 Percona，MariaDB，Oracle MySQL 企业版，阿里云 polarDB，腾讯数据库中提中，提供了 线程池 特性。

在 one-thread-per-connection 情况下，每个连接分配一个线程，当客户端和 MySQL 服务器建立 TCP 连接之后，MySQL 服务器就会给这个连接分配一个线程，当这个连接收到 SQL 时，对应的线程就执行这个 SQL，而当 SQL 执行结束后，这个线程就去 Sleep，等待客户端的新请求。这个线程会一直存活，直到客户端退出登录或线程超时断开（由 wait_timeout 或 interactive_timeout 参数控制，），并关闭连接，这个线程才会退出（或者进入 MySQL 的 ThreadCache）。

超时参数，详见 https://zhuanlan.zhihu.com/p/82554484

MySQL 连接方式有很多种，区分 Unix 系统 和 Windows 系统以及通用的连接方式，在这里仅说两种方式：

  • 一种为 unix domain socket

  • 另外一种为基于 tcp/ip 协议，一般我们如果远程访问数据库肯定是基于 tcp/ip 的，但是如果我们在本机登录就会分为使用 socket 还是 tcp/ip。

Copysocket：mysql -uroot -p
tcp/ip：mysql -h127.0.0.1 -uroot -p

当数据库服务器和应用服务器位于不同的主机时就要使用 tcp/ip 的方式建立连接。每一个连接在操作系统中占用一个线程来维护。

建立连接也分为两类：短连接**和**长连接：

短连接

所谓短连接就是指应用程序和数据库通信完毕之后连接关闭。这种连接每次的操作就是：

发出请求--->建立连接--->操作数据--->释放连接

这样做的问题是：

  1. 频繁的建立 / 释放连接对数据库来说增加了系统负担，频繁创建/销毁线程**增加数据库服务器 CPU 上下文切换开销，影响数据库服务器性能。**；

  2. 应用程序每次操作数据库的过程将会变得很慢，tcp 三次握手四次断开要时间开销的；

  3. 应用系统每次建立连接都要占用一个端口，频繁的建立/释放，每个被释放的连接在发出释放请求之后并不是马上就执行，必须经历一个 FIN 阶段的等待直到确认为止。所以在每秒几千次数据库请求的时候，应用服务器端口很有可能被消耗完。

长连接

长连接即在建立连接后一直打开，直到应用程序关闭才释放。使用长连接的好处是减少每次创建连接带来的开销。

对于客户端来说维持长连接的好处不言自明，但是对于数据库服务器来说，过多的长连接则是灾难。

如果滥用长连接的话，可能会使用过多的 MySQL 服务器连接。现代的操作系统可以拥有几千个 MySQL 连接，但很有可能绝大部分都是睡眠（sleep）状态的，这样的工作方式不够高效，而且连接占据内存，也会导致内存的浪费。

MYSQL 的 TCP 连接支持长连接，所以每次操作完数据库，可以不必直接关掉连接，而是等待下次使用的时候在复用这个连接。

所有的 Socket 长连接都是通过 TCP 自带的 ping 来维持心跳(TCP 保活)，从而保持连接状态，而我们熟悉的websocket,也正是通过 TCP 的心跳来维持连接不被中断。

连接池

长连接的好处这么大，自然大家都用长连接。慢慢就搞出一套长连接维护的工具 - 数据库连接池。

设计连接池也没有多么复杂，大致的步骤就是：

  1. 初始化连接；
  2. 业务取出连接；
  3. 业务发送请求；
  4. 放回连接。

除了上面的基本功能以外，还要处理并发问题，多数据库服务器和多用户，事务处理，连接池的配置与维护。

大概就这些功能。有了连接池（在服务端，有时候叫线程池）之后，连接的建立和释放跟业务就没有关系，交给交接池来维护。

参考

对此，研发工程师、系统运维工程师、DBA 需要保持沟通，确定合理的连接策略，千万不要不假思索就采用长连接。

MySQL 的最大连接数由 max_connections 参数控制，在 5.7 和 8.0 中默认是 151，最大可以达到 16384（2^14）。

对于海量连接的数据库，如果设置的太小，连接满了之后后面的新连接就会报too many connections。

MySQL Server 其实默认允许的最大客户端连接数为 max_connections + 1 ，这其中额外可以登陆的 1 一个连接仅仅允许拥有 super 权限的用户进行连接。

MySQL 如此设计其实是为了当数据库出现连接数打满的情况下，可以使用同时拥有 super、process 权限的高权限数据库账号登陆数据库，将问题会话或者一些空闲连接进行 kill，紧急处理故障。所以通常避免让业务账号具备 super 和 process 的管理权限。

在 MySQL8.0 里，则引入了 admin port 的概念，顾名思义，就是单独开一个端口给管理员用，该特性从 8.0.14 开始引入。这个需要单独设置。 可以说这是个对运维非常有用，关键时候可以救命的特性。这个 feature 由 facebook 贡献给上游。主要包括以下几个参数设置：

# admin_address: 用于指定管理员发起tcp连接的主机地址，可以是ipv4,ipv6, 或者Host name等等，但只能接受一个ip地址
# admin_port: 是管理员用来连接的端口号，默认是33062。注意如果admin_address没有设置的话，这个端口号是无效的
# create_admin_listener_thread: 是否创建一个单独的listener线程来监听admin的链接请求，默认值是关闭的，即会使用已有的监听线程去监听admin连接。该参数同样需要admin_address打开, 否则没有任何影响

# 通常建议这样设置。让mysql内部单独开启一个listener线程来监听127.0.0.0。紧急情况连接用满了，可以在服务器上本地用管理端口连接。
admin_address='localhost'
create_admin_listener_thread=1


mysql -u root -P 33062 --protocol tcp -p'zhtj6668182'

基于此，数据库账号权限一定要做好明确的规划，业务账号仅仅拥有对应业务数据库的读写权限、高权限数据库账号用于运维管理。

MySQL 官网给出了一个最大连接数推荐计算方式，Max_used_connections / max_connections * 100% ≈ 85%

MySQL 的 状态变量 显示 MySQL 服务实例的运行状态信息，这些状态信息是动态的，包括 MySQL 服务器连接的会话状态、变量信息等。默认情况下状态变量都是以大写字母开头。

show status;
show session status;
show global status;
状态变量 含义
Connections 状态变量：MySQL 服务从初始化开始成功建立连接的数量，该值不断累加
Max_used_connections 状态变量：MySQL 服务从启动开始，同一时刻并发连接的最大值，如果该值很大，则有可能系统并发较高，可以考虑调大 max_connections
Connection_errors_max_connections 状态变量：当 MySQL 的最大并发连接数超过设置的 max_connections 变量的值，被拒绝的次数会记录到这个状态值里
Threads_connected 状态变量： MySQL server 当前打开的连接数

控制参数

配置 含义
max_connections 配置参数：MySQL server层面对于连接数的限制。**总的所有账号一起**最大的可连接的数量，默认 151，最大值为 100000
max_user_connections 配置参数：代表允许单个用户的连接数最大值，即并发值。默认为 0，表示不限制
wait_timeout 配置参数：即 MySQL 长连接(非交互式)的最大生命时长，默认是 8 小时，根据业务特点配置
interactive_timeout 配置参数：即 MySQL 长连接长连接(交互式)的最大生命时长，默认是 8 小时，根据业务特点配置
connect_timeout 配置参数：获取 MySQL 连接是多次握手的结果，除了用户名和密码的匹配校验外，还有 IP->HOST->DNS->IP 验证，任何一步都可能因为网络问题导致线程阻塞。为了防止线程浪费在不必要的校验等待上，超过 connect_timeout 的连接请求将会被拒绝。默认是 10 秒

线程缓存

线程缓存实现在MySQL server端。当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户端的连接（而不是直接销毁线程，当然前提是缓存数未达上限）。

缓存的线程数量由 thread_cache_size 大小决定。

当服务器不断有大量连接创建、关闭的场景下，使用线程缓存能够重用缓存起来的线程，避免了大量连接线程的反复创建销毁带来的 CPU 上下文切换性能消耗，但是仍然无法解决高连接数带来的线程数过高的问题。

如果是短连接，适当设置大一点。因为短连接往往需要不停创建，不停销毁，如果大一点，连接线程都处于取用状态，不需要重新创建和销毁，所以对性能肯定是比较大的提升。 对 于长连接，不能保证连接的稳定性，所以设置这参数还是有一定必要，可能连接池的问题，会导致连接数据库的不稳定性，也会出现频繁的创建和销毁，但这个情况比较少，如果是长连接，可以设置成小一点，一般在 50-100 左右。

# 查看线程缓存大小
show global variables like 'thread_cache_size';


# thread_stack: 每个连接线程被创建时，MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时，需要给它分配一定大小的内存堆栈空间，以便存放客户端的请求的Query及自身的各种状态和处理信息。
# Thread Stack 默认值（192KB）足够正常运行。如果线程堆栈大小太小，则会限制服务器可以处理的SQL语句的复杂性，存储过程的递归深度以及其他消耗内存的操作。
# 一般情况下都能正常使用，但是当查询语句或者存储过程复杂时会报Threadstack overrun（超限）错误，此时只要修改增加默认配置就可以了
show VARIABLES like 'thread_stack'

# 系统启动到现在共接受到客户端的连接次数
show status like 'connections'


# 可以通过如下几个MySQL状态值来适当调整线程池的大小
show global status like 'Threads_%';
配置 含义
Threads_cached 状态变量：当前线程池中缓存有多少空闲线程
Threads_connected 状态变量：当前的连接数 ( 因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数 )
Threads_created 状态变量：开启以来累计已经创建过的线程总数
Threads_running 状态变量：当前激活的线程数 ( Threads_connected 中的线程有些可能处于休眠状态 )

主机缓存

MySQL 服务会在内存中维护 host cache ，host cache 包含很多关于**客户端**的信息：IP，地址，主机名，和错误信息。

host cache 的内容是公开的，可以用 select 语句进行查询，以便诊断当前的连接问题.

select * from performance_schema.host_cache ;

提示：

host cache 仅用于非 localhost 的 tcp 连接：对于 loopback 环回地址的 tcp 连接，或者 socket file 连接，命名管道，共享内存等。

执行 flush hosts 可以刷新 host_cache，刷新后会清除内存中的主机缓存

  1. 当有一个新的客户端连接进来时，MySQL Server 会为这个 IP 在 host cache 中建立一个新的记录，包括 IP，主机名和 client lookup validation flag，分别对应 host_cache 表中的 IP，HOST 和 HOST_VALIDATED 这三列。第一次建立连接因为只有 IP，没有主机名，所以 HOST 将设置为 NULL，HOST_VALIDATED 将设置为 FALSE。

  2. MySQL Server 检测 HOST_VALIDATED 的值，如果为 FALSE，它会试图进行**DNS 反向解析**，如果解析成功，它将更新 HOST 的值为主机名，并将 HOST_VALIDATED 值设为 TRUE。如果没有解析成功，判断失败的原因是永久的还是临时的，如果是永久的，则 HOST 的值依旧为 NULL，且将 HOST_VALIDATED 的值设置为 TRUE，后续连接不再进行解析，如果该原因是临时的，则 HOST_VALIDATED 依旧为 FALSE，后续连接会再次进行 DNS 解析。

解析成功的标志并不只是通过 IP，获取到主机名即可，这只是其中一步，还有一步是通过解析后的主机名来反向解析为 IP，判断该 IP 是否与原 IP 相同，如果相同，才判断为解析成功，才能更新 host cache 中的信息。

缺点：当有一个新的客户端连接进来时，MySQL Server 都要建立一个新的记录，如果 DNS 解析很慢，无疑会影响性能。如果被允许访问的主机很多，也会影响性能，这个与 host_cache_size 有关，这个参数是 5.6.5 引入的。5.6.8 之前默认是 128,5.6.8 之后默认是-1,基于 max_connections 的值动态调整。所以如果被允许访问的主机很多，基于 LRU 算法，先前建立的连接可能会被挤掉，这些主机重新进来时，会再次进行 DNS 查询。

优点：通常情况下，主机名是不变的，而 IP 是多变的。如果一个客户端的 IP 经常变化，那基于 IP 的授权将是一个繁琐的过程。因为你很难确定 IP 什么时候变化。而基于主机名，只需一次授权。而且，基于 host cache 中的失败信息，可在一定程度上阻止外界的暴力破解攻击。

参考

MySQL 线程池

在线程池方案下，通常在 MySQL server 服务端实现，MySQL 通过预先创建一定数量的线程，在监听到有新的请求时，线程池直接从现有的线程中分配一个线程来提供服务，服务结束后这个线程不会直接销毁，而是又去处理其他的请求。

这样就避免了线程和内存对象频繁创建和销毁，减少了上下文切换，提高了资源利用率，从而在一定程度上提高了系统的性能和稳定性。

线程池技术限制了并发线程数，相当于限制了 MySQL 的 runing 线程数，无论系统目前有多少连接或者请求，超过最大设置的线程数的都需要排队，让系统保持高性能水平，从而防止 DB 出现雪崩，对底层 DB 起到保护作用。

Thread pool，在 Percona，MariaDB，Oracle MySQL 企业版，以及阿里云 polarDB 中提中，提供了**线程池**特性。

MySQL 连接池

MySQL 连接池，连接池通常实现在 client 端，是指应用(客户端)预先创建一定的连接，利用这些连接服务于客户端所有的 DB 请求。如果某一个时刻，空闲的连接数小于 DB 的请求数，则需要将请求排队，等待空闲连接处理。

通过连接池的连接复用，避免连接的频繁创建和释放，从而减少请求的平均响应时间，并且在请求繁忙时，通过请求排队，可以缓冲应用对 DB 的冲击。常见的 MySQL 连接池 Tomcat、WildFly(JBoss)、 c3p0、 Druid 等。

在很多公司，有不少程序员写代码，懒得用数据库连接池，所以就在每次数据操作时，临时连接数据库，使用完后直接关闭，这显然不好。

MySQL 线程池与连接池

线程处理和线程事务

-- 查看所有连接线程，其中ID为线程ID
SELECT * FROM information_schema.`PROCESSLIST`

-- 在MySQL中有两个kill命令：
-- 一个是kill query +线程id，表示终止这个线程中正在执行的语句；
-- 一个是kill connection +线程id，这里connection可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。

-- 经常遇到使用了kill命令，却没能断开这个连接。再执行show processlist命令，看到这条语句的Command列显示的是Killed


-- 大多数情况下，kill query/connection 命令是有效的。比如，执行一个查询的过程中，发现执行时间太久，要放弃继续查询，这时就可以用 kill query 命令，终止这条查询语句。还有一种情况是，语句处于锁等待的时候，直接使用 kill 命令也是有效的。


-- 1、超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长。


-- 2、DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久。

-- 查看事务执行情况，其中trx_id为事务ID，trx_mysql_thread_id为线程ID
SELECT * FROM information_schema.INNODB_TRX;


-- 生产环境较常见的情况是，由于没有正确配置JDBC等驱动，导致业务代码已经超时退出了与数据库的交互，但是数据库里依旧运行着发起的SQL，如果频繁发起重试，则慢SQL会越来越多，导致数据库负载高，影响稳定性及可用性。因此，建议设置参数max_execution_time来解决这种情况。但是，由于生产环境的复杂性，需要跟开发人员先协商，另外，这个值可能需要设置的相对大一些，避免影响业务。总之来说是谨慎使用。

收到 kill 以后，线程做什么？

在全局锁和表锁中，当对一个表做增删改查操作时，会在表上加 MDL 读锁。所以，session B 虽然处于 blocked 状态，但还是拿着一个 MDL 读锁的。

如果线程被 kill 的时候，就直接终止，那之后这个 MDL 读锁就没机会被释放了。这样看来，kill 并不是马上停止的意思，而是告诉执行线程说，这条语句已经不需要继续执行了，可以开始“执行停止的逻辑了”。

其实，这跟 Linux 的 kill 命令类似，kill -N pid 并不是让进程直接停止，而是给进程发一个信号，然后进程处理这个信号，进入终止逻辑。只是对于 MySQL 的 kill 命令来说，不需要传信号量参数，就只有“停止”这个命令。

  1. 一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是 THD::KILL_QUERY，才开始进入语句终止逻辑；

  2. 如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处；

  3. 语句从开始进入终止逻辑，到终止逻辑完全完成，是有一个过程的。

查询缓存

MySQL 的查询，主要处理过程都是从硬盘中读取数据加载到内存，然后通过网络发给客户端，

MySQL 以前有一个查询缓存 Query Cache，从 MySQL8.0 开始，不再使用这个查询缓存，随着技术的进步，经过时间的考验，MySQL 的工程团队发现启用缓存的好处并不多。所以在 8.0 中移除了这个特性。

在解析一个查询语句前，如果查询缓存是打开的，那么 MySQL 会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。

这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL 将缓存存放在一个引用表（不要理解成 table，可以认为是类似于 HashMap 的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。

所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql 库中的系统表，其查询结果都不会被缓存。

比如函数 NOW() 或者 CURRENT_DATE() 会因为不同的查询时间，返回不同的查询结果。再比如包含 CURRENT_USER 或者 CONNECION_ID() 的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

既然是缓存，就会失效，那查询缓存何时失效呢？

MySQL 的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

正因为如此，在任何的写操作时，MySQL 必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。

而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

  1. 任何的查询语句在开始之前都必须经过检查，即使这条 SQL 语句永远不会命中缓存
  2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

首先，查询缓存的效果取决于缓存的命中率，只有命中缓存的查询效果才能有改善，因此无法预测其性能。

其次，查询缓存的另一个大问题是它受到单个互斥锁的保护。在具有多个内核的服务器上，大量查询会导致大量的互斥锁争用。

通过基准测试发现，大多数工作负载最好禁用查询缓存（5.6 的默认设置）：query_cache_type = 0

如果你认为会从查询缓存中获得好处，请按照实际情况进行测试。

  • 数据写的越多，好处越少
  • 缓冲池中容纳的数据越多，好处越少
  • 查询越复杂，扫描范围越大，则越受益

最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。

如果你实在是忍不住，可以将 query_cache_type 设置为 DEMAND，这时只有加入 SQL_CACHE 的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

对于一些热点数据，现在比较流行的做法是引入外部的缓存中间件，比如 redis 等，这个以后展开再讲。

语法解析和查询处理

MySQL 通过关键字将 SQL 语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。

比如 SQL 中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据 MySQL 规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

SQL 解析与优化是属于编译器编译原理方面的知识，与 C 语言这类编程语言的解析上是类似的。SQL 解析主要包含：词法分析、语义语法分析、优化和执行代码生成。

  • 词法分析 Lexical Analysis：将用户输入的 SQL 语句拆解成单词(Token)序列，并识别出关键字、标识、常量等。

  • 语法分析 Syntax Analysis：分析器对词法分析器解析出来的单词(Token)序列在语法上是否满足 SQL 语法规则。构建一颗语法分析树。

  • 语义分析 Semantic Analysis：语义分析是 SQL 解析过程的一个逻辑阶段，主要任务是在语法正确的基础上进行上下文有关性质的审查，在 SQL 解析过程中该阶段完成表名、操作符、类型等元素的合法性判断，同时检测语义上的二义性。

SQL 解析由词法分析和语法、语义分析两个部分组成。词法分析主要是把输入转化成若干个 Token，其中 Token 包含 key 和非 key。比如，一个简单的 SQL 如下所示：

SELECT age FROM user

查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。

优化器概述

为什么数据库要进行查询优化？”

不止是数据库要进行优化，基本上所有的编程语言在编译的时候都要优化。

比如在编译 C 语言的时候，可以通过编译选项 -o 来指定进行哪个级别的优化，只是查询数据库的查询优化和 C 语言的优化还有些区别。

C 语言是过程化语言，已经指定好了需要执行的每一个步骤；但 SQL 是描述性语言，只指定了 WHAT，而没有指定 HOW 。这样它的优化空间就大了。

查询优化的动机？

同一条查询可以转换为多个逻辑等价的执行计划，首先这些不同的逻辑执行计划在执行效率上存在差异，其次同一个逻辑算子在数据库中有多种不同的物理实现方式，而不同场景下不同的物理实现方式的效率也存在差异。所以查询优化的核心目的其实就是 找到性能更好的执行计划

查询优化的输入为查询树，对查询树依次从 逻辑 和 物理 两个层面进行优化，最终输出是查询的物理执行计划。

查询优化主要有三个重要的过程：

  • 逻辑优化 逻辑优化的输入是查询树，输出是经过逻辑优化后的查询树。逻辑优化是找出与查询等价但执行效率更高的关系代数表达式。 实现逻辑优化的方法主要是 查询重写 ，根据特定的重写规则对查询树做逻辑等价变化。

  • 代价估计 经过逻辑优化过程，可以得到基于等价规则重写过的逻辑执行计划。将逻辑执行执行转换为实际执行的物理计划前，还需要确认各种具体实现方式： 比如，两表连接时应该具体采用何种连接算法，多表连接时还需要确定表之间的连接顺序。

  • 物理优化 物理层面的优化则是枚举各种物理执行计划，根据代价估计模型择出代价最小的物理执行计划.

关系演算是纯描述性的语言，关系代数包含了一些基本的关系操作，SQL 主要借鉴的是关系演算，也包含了关系代数的一部分特点。

关系代数有投影、选择、连接、并集、差集，重命名，一共 6 个基本操作。另外，结合实际应用在这些基本操作之上又扩展出了外连接、半连接、聚集操作、分组操作等。

SQL 语句虽然是描述性的，但是我们可以把它转化成一个关系代数表达式。而关系代数中又有一些等价的规则，这样我们就能结合这些等价规则对关系代数表达式进行等价的转换。进行等价转换的目的是找到性能更好的代数表达式。

数据库有哪些方法来实现一个左外连接呢？ 它可以用嵌套循环连接、哈希连接、归并连接等。

  • 内连接、外连接是连接操作。这些就是 逻辑操作符
  • 嵌套循环连接、归并连接，哈希连接等。这些就是 物理操作符。

优化器作为数据库的大脑，也需要建立代价模型，对物理操作符计算代价，然后筛选出最优的物理操作符来。

因此，基于代价的优化是建立在物理操作符上的优化，所以也叫物理优化。物理优化就是建立在物理操作符上的优化。

查询算子

SQL 查询的执行过程，就像工厂的加工流水线，加工过程中的每一种工序都对应一种运算。

这些运算可以被抽象为关系代数运算，查询算子 是指这些关系代数运算。每种查询算子可能存在不同的物理实现方式，不同实现方式适合不同的场景。

查询算子 实现方式
排序算子 内存排序、外部归并排序
选择算子 线性扫描、索引扫描
连接算子 嵌套循环连接、块嵌套循环连接、索引嵌套循环连接、排序归并连接、哈希连接
去重算子、聚集算子、集合算子 排序、哈希

多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL 采用了基于开销成本的优化器，以确定处理查询的最解方式，也就是说执行查询之前，都会先选择一条自以为最优的方案，然后执行这个方案来获取结果。

在很多情况下，MySQL 能够计算最佳的可能查询计划，但在某些情况下，MySQL 没有关于数据的足够信息，或者是提供太多的相关数据信息，估测就不那么友好了。

对于一些执行起来十分耗费性能的语句，MySQL 还是依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写。

它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

基于成本的优化器将枚举可能的执行计划，并为每个计划分配成本，成本是执行该计划所需的时间和资源的估计值。

一旦这些可能性被列举出来，优化器就会选择成本最低的计划并将其交付执行。虽然成本模型通常被设计为最大化吞吐量（即每秒查询），但它也可以被设计为支持其他期望指标的查询行为，例如最小化延迟（即检索第一行的时间）或最小化内存使用。

CBO(Cost-Based Optimization)

CBO: Cost-Based Optimization 也即"基于代价的优化器"，该优化器通过根据优化规则对关系表达式进行转换，生成多个执行计划，然后 CBO 会通过根据统计信息(Statistics)和代价模型(Cost Model)计算各种可能“执行计划”的“代价”，即 COST，从中选用 COST 最低的执行方案，作为实际运行方案。

CBO 依赖数据库对象的统计信息，统计信息的准确与否会影响 CBO 做出最优的选择。

以 Oracle 数据库为例，统计信息包括 SQL 执行路径的 I/O、网络资源、CPU 的使用情况。

目前各大数据库和大数据计算引擎都倾向于使用 CBO，例如从 Oracle 10g 开始，Oracle 已经彻底放弃 RBO，转而使用 CBO；而 Hive 在 0.14 版本中也引入了 CBO。

优化器和查询成本

一般来说一个 sql 查询可以有不同的执行方案，可以选择走某个索引进行查询，也可以选择全表扫描。

SQL 优化器，其中最重要的一个组件是查询优化器，是数据库系统的重要组成部分。特别是对于现代大数据系统，执行计划的搜索空间异常庞大，研究人员研究了许多方法对执行计划空间进行裁剪，以减少搜索空间的代价。

在当今数据库系统领域，查询优化器可以说是必备组件，不管是关系型数据库系统 Oracle、MySQL，流处理领域的 Flink、Storm，批处理领域的 Hive、Spark SQL，还是文本搜索领域的 Elasticsearch 等，都会内嵌一个查询优化器。

有的数据库系统会采用自研的优化器，而有的则会采用开源的查询优化器插件，比如 Apache Calcite 就是一个优秀的开源查询优化器插件。而像 Oracle 数据库的查询优化器，则是 Oracle 公司自研的一个核心组件，负责解析 SQL，其目的是按照一定的原则来获取目标 SQL 在当前情形下执行的最高效执行路径。

参考

查询优化器 则会比较并选择其中成本最低的方案去执行查询。

查询处理的代价主要是指查询对各种计算资源的消耗，包括磁盘 I/O 占用、执行查询所用的 CPU 时间，如果是分布式数据库的话还需要考虑数据通信代价

在大型数据库系统中，磁盘 I/O 代价是最主要的代价，可以**使用磁盘 I/O**的块数作为代价度量的指标。

查询成本分大体为两种：

  • I/O 成本：磁盘读写的开销。一个查询或一个写入，都要从磁盘中读写数据，要一定的 IO 开销。

  • CPU 成本：关联查询，条件查找，都要 CPU 来进行计算判断，一定的计算开销。

MySQL 使用的InnoDB引擎会把数据和索引都存储到磁盘上，当查询的时候需要先把数据先加载到内存中在进行下一步操作，这个加载的时间就是I/O成本。

当数据被加载到内存中后，CPU 会计算查询条件匹配，对数据排序等等操作，这一步所消耗的时间就是 CPU 成本。

但是查询优化器并不会真正的去执行 sql，只会去根据优化的结果去预估一个成本。

InnoDB 引擎规定读取一个页面花费的成本默认约是 0.25，读取以及检测一条记录是否符合搜索条件的成本默认约是 0.1。

为什么都是约呢，因为 MySQL 内部的计算成本比较复杂这里提取了两个主要的计算参数。

## MySQL server 层面的各种开销
mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2021-09-24 14:47:20 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2021-09-24 14:47:20 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2021-09-24 14:47:20 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2021-09-24 14:47:20 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2021-09-24 14:47:20 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2021-09-24 14:47:20 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)

mysql>

## MySQL 存储引擎层面的各种开销
mysql> select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2021-09-24 14:47:20 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2021-09-24 14:47:20 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.00 sec)

mysql>

在 MySQL 可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

 mysql> select * from t_message limit 10;
 ...省略结果集
 
 mysql> show status like 'last_query_cost';
 +-----------------+-------------+
 | Variable_name   | Value       |
 +-----------------+-------------+
 | Last_query_cost | 6391.799000 |
 +-----------------+-------------+
 ————————————————

示例中的结果表示优化器认为大概需要做 6391 个数据页的随机查找才能完成上面的查询。

这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）。

MySQL 认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但 MySQL 选择它认为成本小的，但成本小并不意味着执行时间短）等等。

优化器追踪

MySQL 优化器可以生成explain执行计划，我们可以通过执行计划查看是否使用了索引，使用了哪种索引？

但是它只能展示SQL语句的执行计划，无法展示为什么一些其他的执行计划未被选择，比如说明明有索引，但是为什么查询时未使用索引等。

好在 MySQL 提供了一个好用的工具optimizer trace（优化器追踪），可以帮助我们查看优化器生成执行计划的整个过程，以及做出的各种决策，包括访问表的方法、各种开销计算、各种转换等。

众所周知，MySQL 是基于成本的优化器（CBO），每个执行计划的成本大致反应了该计划查询所需要的资源。

CBO 选择目标 SQL 执行计划的判断原则是成本，从目标 SQL 的诸多执行计划中选取成本值最小的执行路径为其执行计划，各执行路径的成本值是根据目标 SQL 中涉及到的表、索引、列等相关对象的统计信息计算出来的，实际反应执行目标 SQL 所要消耗的 I/O、CPU 和网络资源的一个估计值。

优化器会为每个操作标上成本，这些成本的基准单位或最小值是从磁盘读取随机数据页的成本，其他操作的成本都是它的倍数。所以优化器可以根据每个执行计划的所有操作为其计算出总的成本，然后从众多执行计划中，选取成本最小的来最终执行。

CREATE TABLE `user` (
    `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
    `name` varchar(100) NOT NULL COMMENT '姓名',
    `gender` tinyint NOT NULL COMMENT '性别',
    PRIMARY KEY (`id`),
    KEY `idx_name` (`name`),
    KEY `idx_gender_name` (`gender`,`name`)
) ENGINE=InnoDB COMMENT='用户表';

INSERT INTO  user(id,name,gender)  VALUES
    (1,'小明',1),
    (2,'小黄',1),
    (3,'关羽',1),
    (4,'张飞',0),
    (5,'曹操',1),
    (6,'刘备',0),
    (7,'孙悟空',1),
    (8,'林冲',1),
    (9,'李白',0),
    (19,'李四',1),
    (10,'小强',1),
    (11,'刘婵',1),
    (12,'赵芸',1),
    (13,'惠月',1),
    (14,'景逸',1),
    (15,'小黄',1),
    (16,'思峰',1),
    (17,'伏尔泰',1),
    (18,'雨果',1),
    (20,'贝多芬',1),
    (21,'小明',1),
    (22,'小明',1),
    (23,'小明',1),
    (24,'小明',1),
    (25,'小明',1),
    (26,'小明',1),
    (27,'小明',1),
    (28,'小明',1);

--这条语句用的是`idx_gender_name`这个联合索引
explain select * from user  where gender=1 and name='小明';

--这条语句用的是`idx_name`这个索引
explain select * from user  where gender=0 and name='张三';


-- 同一条SQL因为传参不同，而使用了不同的索引。
-- 到这里，使用现有工具，我们已经无法排查分析，MySQL优化器为什么使用了(name)上的索引，而没有使用(gender,name)上的联合索引。

相关变量

mysql> show variables like '%optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name                | Value                                                                      |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace              | enabled=off,one_line=off                                                   |
| optimizer_trace_features     | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit        | 1                                                                          |
| optimizer_trace_max_mem_size | 1048576                                                                    |
| optimizer_trace_offset       | -1                                                                         |
+------------------------------+----------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql>

-- 可用SET语句操作，用如下命令即可打开OPTIMIZER TRACE
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
-- 即使全局开启OPTIMIZER_TRACE，每个Session也只能跟踪它自己执行的语句
SET GLOBAL OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
-- 默认情况下，由于optimizer_trace_offset=-1，optimizer_trace_limit=1，记录最近的一条SQL语句，展示时，每次展示1条数
SET optimizer_trace_offset=<OFFSET>, optimizer_trace_limit=<LIMIT>


-- 开启OPTIMIZER_TRACE功能，并设置要展示的数据条目数
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET optimizer_trace_offset=-30, optimizer_trace_limit=30;


-- 发送你想要分析的SQL语句
-- SELECT * FROM users;

-- 查看分析结果
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30;

optimizer_trace

  • enabled：启用/禁用 optimizer_trace 功能。默认关闭，也建议关闭，因为它会产生额外的性能开销。根据相关评测，当打开optimizer trace时，约有不到 10%的性能下降。

  • one_line：决定了跟踪信息的存储方式，为 on 表示使用单行存储，否则以 JSON 树的标准展示形式存储。

在 Optimizer Trace 的输出中，主要分为三个部分:

  • join_preparation SQL 准备阶段

完成 SQL 的准备工作，在这个阶段，SQL 语句会被格式化输出，通配符*会被具体字段代替，但不会进行等价改写动作。

  • join_optimization SQL 优化阶段

完成 SQL 语句的逻辑与物理优化的过程，这其中的优化步骤比较多。在展开具体内容之前，先解释下”select #”的问题。在输出中经常会看到有”select#:N”的字样，它表示当前跟踪的结构体是属于第几个 SELECT。如果语句中使用多个 SELECT 语句拼接（如 UNION）或者有嵌套子查询中有 SELECT，会产生多个序号。

  • join_execution SQL 执行阶段

统计数据

既然是基于统计数据来进行标记成本，就总会有样本无法正确反映整体的情况，这也是 MySQL 优化器有时做出错误优化的重要原因之一。

MySQL 统计信息是指数据库通过采样、统计出来的表、索引的相关信息，例如，表的记录数、聚集索引 page 个数、字段的 Cardinality....。

MySQL 在生成执行计划时，需要根据索引的统计信息进行估算，计算出最低代价（或者说是最小开销）的执行计划，MySQL 支持有限的索引统计信息，因存储引擎不同而统计信息收集的方式也不同。

根据统计数据是否可以持久化，MySQL 提供了两种统计方式：

  • 统计数据存储在磁盘上。

  • 统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了。

MySQL 给我们提供了系统变量 innodb_stats_persistent 来控制到底采用哪种方式去存储统计数据。

# 在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，InnoDB的统计数据默认是存储到内存的
innodb_stats_persistent=OFF
# 在MySQL 5.6.6之后，innodb_stats_persistent的值默认是ON，InnoDB的统计数据默认是存储到磁盘中
innodb_stats_persistent=ON
  • mysql.innodb_table_stats 存储了表的总行数，主键clustered index上 page 数，非主键索引的 page 总数。

InnoDB 默认是以表为单位来收集和存储统计数据的，也就是说我们可以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中。

我们可以在创建和修改表的时候通过指定(STATS_PERSISTENT,STATS_AUTO_RECALC,STATS_SAMPLE_PAGES)属性来指明该表的统计数据存储方式，以及其他属性。

CREATE TABLE table1 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);
ALTER TABLE table2 Engine=InnoDB, STATS_PERSISTENT = (1|0);

持久化的统计数据存储在 mysql.innodb_index_stats 和 mysql.innodb_table_stats 中：

  • database_name 数据库名
  • table_name 表名
  • last_update 本条记录最后更新时间
  • n_rows 表中记录的条数
  • clustered_index_size 表的聚簇索引占用的页面数量
  • sum_of_other_index_sizes 表的其他索引占用的页面数量

在 InnoDB 存储引擎下，其对表中记录数量的统计值 n_rows 是不准确的。其统计方法是先通过算法选取若干个(聚簇索引的)叶子节点页面，然后计算叶子节点页面中记录数量的均值，最后将均值乘以(聚簇索引的)叶子节点的数量得到 n_rows 值。故其不是一个精确值，而是一个估计值。

在计算均值过程中，如果选取的叶子节点越多，则 n_rows 值越准确。故在 MySQL 中，可通过系统变量innodb_stats_persistent_sample_pages来设置在计算永久性的统计数据时统计过程所需的页面采样数量。显然innodb_stats_persistent_sample_pages值越大，统计过程所需耗时也就越多。

更新统计数据

当系统变量 innodb_stats_auto_recalc 值为 ON，即可实现统计数据的自动更新。具体地，一般当表中变化的记录数超过一定阈值，MySQL 会自动开始重新进行统计。只不过该统计是异步的，所以即使满足重新统计的条件也不会立即开始计算，有可能会延迟几秒才开始

前面我们说了，统计数据是以表为单位进行统计的，故我们还可以通过 STATS_AUTO_RECALC 属性来显式地设置表是否自动更新统计数据。具体地，当值为 0 意为不会进行自动更新；当值为 1 意为会进行自动更新。更多地，我们一般很少会在建表时指定该属性，则该表就默认使用我们上面提到的系统变量 innodb_stats_auto_recalc 的配置

优化器配置

  1. 不改变语义的情况下，重写 sql。重写后的 sql 更简单，更方便制定执行计划。
  2. 根据成本分析，制定执行计划。
SELECT @@optimizer_switch;

index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on
index_condition_pushdown=on,
mrr=on,
mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on,
use_invisible_indexes=off,
skip_scan=on,
hash_join=on


-- 修改优化器
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

-- command语法如下：
-- default          --重置为默认
-- opt_name=default --选项默认
-- opt_name=off     --关掉某项优化
-- opt_name=on      --开启某项优化

MySQL优化器流程

前面一大堆是数据库优化器理论，

  • 逻辑优化：对查询做逻辑等价变换，例如semi-join、子查询解关联、谓词下推、视图合并、左连接转内连接、等值推导等。对查询做等价变换后，查询生成更优的执行计划，有更多的计划选择。

  • 初始的优化分析

对表各个可能的访问路径做一个扫描行数和代价分析。扫描行数和代机的分析，可以帮助后续选择合适的基表路径，有初始的Join表排序帮助能够快速剪枝，后续在Join条件关联索引和常量索引之间做选择。同时会分析出Const Table。Const Table是指在表条件上有primary key或者unique key等值访问的表，从约束条件上确认最多只会获取一行数据。Const Table在优化期间就会执行，获取要访问的数据，相关的列在后续优化中即可作为常量，使后续优化可以更加准确。同时如果Const Table上不存在满足条件的数据行，在优化期间就可以被发现，从而跳过后续的优化和执行过程。

Optimizer Hints

优化器是关系数据库的重要模块，它决定 SQL 执行计划的好坏。但是，优化器的影响因素很多，由于数据变化和估计准确性等因素，它不能总是产出最优的执行计划 。选择了不同的执行计划，执行效果差异可能非常大，甚至达到数量级差异，可能对生产系统产生严重影响。虽然学术和业界长期致力于优化器的改进，但对于业务系统而言，在优化器犯错的时候，需要有一些直接有效的干预办法。

Optimizer Hints (下文简称 Hints ) 是一套干预优化器的实用机制，不同数据库厂商都有各自的实现方式。Oracle 可能是将 Hints 机制发挥到极致的数据库大厂。而即使像 PostgreSQL 这样拒绝 hints 的学院派数据库，“民间”也自发搞了个 pg_hint_plan 插件 ，让大家能够尽快地解决执行计划走错的问题。

Hints 的干预方式是向优化器提供现成的优化决策，从而缩小执行计划的选择范围。通常在人为干预优化器时，只需要在关键决策点提供具体决策，就可以规避错误的执行计划；当然也可以提供所有决策，这样可以产生确定的执行计划。

在使用 hints 的时候，有一个非常重要的概念，就是标定被干预对象，也就是说优化决策是如何匹配的。然后才是施以具体动作，影响优化器的行为。从这个视角来看， hints 也是一套支持“匹配-动作”的规则系统。

下推

计算下推是数据库优化器优化查询性能的一种常见手段，早期的数据库系统提及的计算下推一般是指谓词下推，其理论源自关系代数理论。

2000 年以后，随着 Oracle RAC 的盛行以及一众开源分布式数据库的崛起，存算分离的概念逐步流行，计算下推的涵盖范围由此从基本的谓词+投影下推延伸到了数据库所支持的一切可能计算的下推（JOIN、聚合、完整 query、部分 query 等）。

对于单机数据库来说，尤其是 MySQL 这种采用经典火山模型的关系型数据库，最常见的就是谓词下推、投影下推，通常在查询优化的 RBO 阶段完成（有的下推在 CBO 阶段），通过将 Filter 和 Project 算子在抽象语法树（AST）中向下移动，提前对行/列进行裁剪，减少后续计算的数据量。

当然，MySQL 中不仅仅是谓词下推、投影下推，还有条件下推、ICP 等。

下推是查询改写的一项重要优化。查询下推不仅可以降低数据的 IO，而且能够节省内存，减少 CPU 的计算量等，特别是对于分布式的数据处理情境中，查询下推能够对查询性能有一个显著的提升。

查询下推可以从两个两个方面来理解：从逻辑优化的角度来看，查询下推属于是将逻辑查询树中的一些节点下推到叶子结点或接近叶子结点，即更接近数据源的地方，从而使得上层节点的操作所涉及到的数据量大大减少，提高数据处理效率

派生条件下推

MySQL 8.0.22 及更高版本支持符合条件的子查询的派生条件下推。

对于如

SELECT * FROM (SELECT i，j FROM t1) AS dt WHERE i > constant

在许多情况下，可以将外部WHERE条件下推到派生表，相当于改写成如下 SQL

SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt

这减少了派⽣表返回的⾏数，从⽽加快查询的速度。

派生条件下推一句话理解即为：外查询与派生表相关的条件会被推入到派生表中作为条件，以减少处理的数据行数，加速查询速度

https://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html

当派生表无法合并到外部查询中时（例如：如果派生表使用聚合），将外部 WHERE 条件下推到派生表应该会减少需要处理的行数，从而加快查询的执行。

索引下推

ICP

谓词下推

数据库中有个概念叫**谓词下推**

如果一个谓词在执行计划中即使处在不同的位置也不改变执行结果，那么我们就尽量把它保持在下层，因为它有"过滤"的作用。在下层结点把数据过滤掉，有助于降低上层结点的计算量。当然对于一些比较执着的谓词SQL的书写者把它安排在了上层，我们在生成执行计划的时候就可以考虑是否能把它推下去。这就需要进行甄别，哪些谓词是可以推下去的，而哪些谓词是无法推下去的。

  • 过滤条件：处在 WHERE 关键字后面的约束条件是过滤条件。
  • 连接条件：处在 ON 关键字后面的约束条件是连接条件。

如果两个表要做的是内连接，实际上它是无需区分连接条件还是过滤条件的。因为即使是处在 ON 关键字后面的连接条件，也只有过滤的作用，内连接不会去补 NULL 值，所以在内连接的时候过滤条件和连接条件是等价的。

谓词下推的基本思想是将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。用在 SQL 优化上来说，就是先过滤再做聚合等操作。

predicate push down 翻译为谓词下推，这个翻译很准确，明确的告诉了我们这个操作是一个什么动作。

predicate（谓词）即条件表达式，在 SQL 中，谓词就是返回boolean值即true或false的函数，或是隐式转换为bool的函数。

SQL中的谓词主要有 LIKE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS。其结果为布尔值，即true或false。

predicate pushdown是将 SQL 语句中的部分语句( predicates 谓词部分) 可以被 "pushed" 下推到数据源或者靠近数据源的部分。

通过尽早过滤掉数据，这种处理方式能大大减少数据处理的量，降低资源消耗，在同样的服务器环境下，极大地减少了查询/处理时间。

在Hive SQL和Spark SQL等一系列SQL ON Hadoop的解析语法树时都在谓词下推方面作出了优化，其实在使用SQL的过程，我们心中记住这是一种将过滤尽可能在靠近数据源（取数据）的时候完成的一种操作，是数据库的一种经典的优化手段。

在传统数据库的查询系统中谓词下推作为优化手段很早就出现了，谓词下推的目的就是通过将一些过滤条件尽可能的在最底层执行可以减少每一层交互的数据量，从而提升性能。例如下面这个例子：

select count(1) from A Join B on A.id = B.id where A.a > 10 and B.b < 100;

-- 对于这个SQL语句，在MySQL内部，大概可以有两种方式执行，一种是分别对A和B全表扫描并关联查询出结果集，再对结果集分别进行按照 where 条件过滤，最终得出结果。显然这个效率不高，表关联的开销是很大的。

-- 第二种方式，先分别对两个表按照where查找后，分别生成临时的结果集，然后再关联查询，可以大大降低Join操作的输入数据量。优化后的SQL如下：

select count(1) from (select *  from A  where a>10)A1 Join (select *  from B  where b<100)B1 on A1.id = B1.id;

-- 无论是行式存储还是列式存储，都可以在将过滤条件在读取一条记录之后执行以判断该记录是否需要返回给调用者，在Parquet做了更进一步的优化，优化的方法时对每一个Row Group的每一个Column Chunk在存储的时候都计算对应的统计信息

查询重写 REWRITE

查询重写(query rewrite)：按照一系列关系代数表达式的等价规则，对查询的关系代数表达式进行等价转换，从而提高查询执行效率。

关系表达式的等价（equivalent）：对于两个关系代数表达式，如果使用相同的关系对表达式中的关系进行替换，总能得到完全相同的结果，则称这两个关系表达式等价

基于关系代数等价规则做等价变换的优化，就是基于规则的优化。

在内核中要实现这些改写算法是极具挑战性的。在业务层改写 SQL 很简单，因为我们的任务只是改对一条已知的 SQL。在内核层改写非常困难，因为我们的任务是给定任意一条 SQL，如果可以改写要尽可能的改写，同时改写必须是正确的。这其实对改写算法提出了两点要求：

  • 正确：改写后的SQL语义不能改变。
  • 完备：可以改写的SQL要能够改写。

正确性非常容易理解，如果一个改写的结果是错误的，这对业务的价值完全是负面的。完备性同样重要，它要求一个改写算法具有较好的通用性，不能只处理一些简单的场景，而不处理复杂的场景。通用性差对业务的价值是有限的。当然，我们很难把一个改写算法做到完全的完备。因为总有一些非常复杂的情形是很难处理的，强行改写可能会引入正确性问题。在实现一个改写算法的过程中，我们会在确保正确的前提下，尽可能的做到完备。

外连接消除

外连接操作可分为左外连接，右外连接和全外连接。连接过程中，外连接左右顺序不能变换，这限制了优化器对连接顺序的选择。

考虑 L 与 R 的左外链接L LEFT JOIN R ON L.ID = R.ID。如果 L 中的一行没有与 R 中的任意一行连接成功，那么连接结果会对 R 的列补空输出。

考虑L LEFT JOIN R的结果，假如存在过滤条件R.C2 = 'XXX'，那么补空产生的行会被过滤掉，外连接和内连接产生的结果集是完全相同的。

SELECT L.ID, L.C1, R.C2 FROM L LEFT JOIN R ON L.ID = R.ID WHERE R.C2 = 'XXX';
/*外连接消除*/
SELECT L.ID, L.C1, R.C2 FROM L, R WHERE L.ID = R.ID AND R.C2 = 'XXX';

-- 此时，优化器的查询重写功能可以将外连接改写为内连接。这有助于优化器考虑更多的join顺序和算法。

WHERE cno IS NOT NULL这样的条件可以让外连接和内连接的结果相同，因为这个约束条件是严格 strict 的。"严格"的精确定义是，对于一个表达式，如果输入参数是 NULL 值，那么输出也一定是 NULL 值，就可以说这个表达式是严格的。

从 SQL 语义的角度出发，对于一个表达式，如果输入参数是 NULL 值，输出结果是 NULL 值或者 FALSE，那么就可以认为这个表达式是严格的。如果在约束条件里有这种严格的表达式，由于输入是 NULL 值，输出是 NULL 值或者 FALSE，那么含有 NULL 值的元组就会被过滤掉。

在外连接消除中，我们称 R.C2 = 'XXX' 这样的条件为==空值拒绝条件==。

实现外连接消除最大的挑战在于：给定任意一个表达式，我们可以准确地判定出它是否是一个==空值拒绝条件==

  • R.C2 出现 >, <, ==, IS NOT NULL 等判断表达式的一侧。这类表达式一侧参数为NULL时，判断结果必然为unknown/false，所以它们是空值拒绝条件。

外连接消除就是将一个outer join转换成inner join

因为这个查询的结果中不可能出现 R 为 NULL 的行，假设有这样的行，它也一定会被 where R.C2 = 'XXX' 过滤掉。这使得使用 outer join 和使用 inner join 的效果等价。

外连接消除最终目的是给优化器带来更多的灵活性，inner join 可以选择的优化路径比 outer join 多得多。

优化器在选择多表连接顺序时，可以有更多更灵活的选择，从而可以选择更好的表连接顺序，加快查询执行的速度；表的一些连接算法（如块嵌套连接和索引循环连接等）。可以减少不必要的 I/O 开销，能加快算法执行的速度。

外连接消除总结:

  • 注意外连接与内连接的语义差别；
  • 外连接优化的条件：空值拒绝；
  • 外连接优化的本质：语义上是外连接，但 WHER 条件使得外连接可以蜕化为内连接。

连接消除

连接消除，去掉不必要的连接对象，则减少了连接操作。

连接消除的条件，无固定模式，具体问题具体处理。

CREATE TABLE t1(a1 INT UNIQUE, b1 VARCHAR(255) );
CREATE TABLE t1(a2 INT UNIQUE, b2 VARCHAR(255) );
CREATE TABLE t1(a3 INT UNIQUE, b3 VARCHAR(255) );


-- 唯一键/主键作为连接条件，三表内连接可以去掉中间表（mysql优化器不支持，需人工去掉）

--可以看到这个语句中的t2表仅仅作为连接条件，查询目标列没有t2的列，它属于多余的对象，但是优化器依然去连接它。
select t1.*,t3.* from t1 join t2 on (a1=a2) join t3 on (a2=a3) limit 1;

-- 可以人工改写成如下
select t1.*,t3.* from t1 join t3 on (a1=a3) limit 1;

连接顺序交换

SQL 语句中会指定表的具体顺序，有一些简单的情况，通过观察就能看出交换顺序之后仍然等价，例如：

  • 内连接 交换内连接的两个表的连接顺序，通常而言不会影响执行结果
  • 左外连接 交换左外连接的两个表的连接顺序而且把左外连接改变成右外连接，那么也不会影响执行结果

查看优化器重写后的 SQL

SQL 语句在被服务器执行时，并不一定就会按照我们写的顺序执行，MySQL 优化器会重写 SQL，如何才能看到优化器重写后的 SQL 呢？这就要用到 explain extended 和 show warnings 了。

explain extended sql 语句，然后 show warnings 查看。

explain extended 会输出 sql 的执行计划，查询记录的方式(全表扫描、全索引扫描、索引范围扫描等)、是否用上索引

show warnings 会看到优化器重写后的 sql

条件化简

我们编写的查询语句的 where 搜索条件本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，MySQL 的查询优化器会为我们简化这些表达式。

移除不必要的括号

有时候表达式里有许多无用的括号，比如这样一条 sql 条件：

 ((a = 5 AND b = c) OR ((a > c) AND (c < 5)))
 
 # 优化器就会对其进行优化成下面这样
 
 (a = 5 and b = c) OR (a > c AND c < 5)

常量传递

 a = 5 AND b > a 就可以被转换为： a = 5 AND b > 5

子查询优化

我们查询中的 select 列 from 表 中，有时候，列和表可能是我们其他查询中出来的。这种列和表是用 select 语句表现出来的就叫子查询。外层 select 就叫外层查询。

  • SELECT 子句
 SELECT (SELECT m1 FROM e1 LIMIT 1);
  • FROM 子句
 -- 子查询后边的 AS t 表明这个子 查询的结果就相当于一个名称为 t 的表，这个名叫 t 的表的列就是子查询结果中的列（m和n）。
 -- 这个放在 FROM 子句中的子查询本质上相当于一个表，但又和我们平常使用的表有点儿不一样，MySQL 把这种由子查询结果集组成的表称之为派生表。
 SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM e2 WHERE m2 > 2) AS t;
  • WHERE 或 ON 子句
 -- 最常见的查询：整个查询语句的意思就是我们想找 e1 表中的某些记录，这 些记录的 m1 列的值能在 e2 表的 m2 列找到匹配的值。
 SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);

子查询分类

  • 标量子查询 （一行一列）
 -- 那些只返回一个单一值的子查询称之为标量子查询：子查询里面的查询结果只返回一行一列一个值的情况。
 SELECT * FROM (SELECT m1 FROM e1 LIMIT 1);
 SELECT * FROM e1 WHERE m1 = (SELECT MIN(m2) FROM e2);
 SELECT * FROM e1 WHERE m1 < (SELECT MIN(m2) FROM e2);
 


-- 查询出销售单价高于平均销售单价的商品:子查询只返回一个值(平均单价)
SELECT product_id, product_name, sale_price
 FROM Product
WHERE sale_price > (SELECT AVG(sale_price)FROM Product);



-- 标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。
-- 也就是说，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用。
  • 行子查询（一行多列）
 -- 顾名思义，就是返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。
 -- 其中的(SELECT m2, n2 FROM e2 LIMIT 1)就是一个行子查询
 -- 整条语句的含义就是要从 e1 表中找一些记录，这些记录的 m1 和 n1 列分别等于子查询结果中的 m2 和 n2 列
 SELECT * FROM e1 WHERE (m1, n1) = (SELECT m2, n2 FROM e2 LIMIT 1);
  • 列子查询
 -- 列子查询自然就是查询出一个列的数据，不过这个列的数据需要包含多条记录
 -- 其中的(SELECT m2 FROM e2)就是一个列子查询，表明查询出 e2 表的 m2 列 的所有值作为外层查询 IN 语句的参数。
 SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);
  • 表子查询（二维多行多列）
 -- 顾名思义，就是子查询的结果既包含很多条记录，又包含很多个列
 -- 其中的(SELECT m2, n2 FROM e2)就是一个表子查询、此sql必须要在m1，n1都满足的条件下方可成立
 SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);
  • 不相关子查询

如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。我们前边介绍的那些子查询全部都可以看作不相关子查询。

  • 相关子查询

如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为相关子查询。

-- 在普通子查询中，执行顺序是由内到外，先执行内部查询再执行外部查询。内部查询的执行不依赖于外部查询，且内部查询只处理一次，外部查询基于内部查询返回值再进行查询，就查询完毕了。


-- 对于外部查询返回的每一行数据，内部查询都要执行一次。
-- 在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策。
-- 而在关联子查询中，信息传播是双向而不是单向的。内部查询利用关联子查询涉及外部查询提供的信息，外部查询也会根据内部查询返回的记录进行决策。内部查询的执行依赖于外部查询，不能单独执行。


-- 检索工资大于同职位的平均工资的员工信息。

-- 员工多，而相应的职位（如销售员、经理、部门经理等）少，因此首先想到的思路是对职位分组，这样就能分别得到各个职位的平均工资，再比较每个人的工资和他对应职位的平均工资，大于则被筛选出来。

参考

子查询在 MySQL 中是怎么执行的

常规想象思维中子查询的执行方式（非实际）

 -- 不相关子查询
 SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = 'a');

-- 对于这样一个查询
SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);


-- 我们可能会认为这样执行的
--SELECT t2.b FROM t2 WHERE id < 10;
-- 结果:1,2,3,4,5,6,7,8,9
-- select * from t1 where t1.a in(1,2,3,4,5,6,7,8,9);

-- 实际上在MySQL内部，优化器可能会改写成如下SQL
select * from t1 where exists(select b from t2 where id < 10 and t1.a=t2.b);

-- 不相关子查询变成了关联子查询（select_type:DEPENDENT SUBQUERY），子查询需要根据 b 来关联外表 t1，因为需要外表的 t1 字段，所以子查询是没法先执行的。执行流程如下：

-- 1、扫描 t1，从 t1 取出一行数据 R；
-- 2、从数据行 R 中，取出字段 a 执行子查询，如果得到结果为 TRUE，则把这行数据 R 放到结果集；
-- 3、重复 1、2 直到结束。

-- 总的扫描行数为 100+100*9=1000（这是理论值，实际值为 964，怎么来的一直没想明白，看规律是子查询结果集每多一行，总扫描行数就会少几行）。
 
 -- 我们想象中的可能是把子查询里面的这个结果集查出来放到内存中，然后做为外层查询的条件进行查询。可能会导致两个问题：
 -- 1.子查询的结果集太多，可能内存中都放不下。
 -- 2.对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，由于order_note不是索引列，每个IN语句的条件都会全表扫描进行遍历。
 
 -- 结果集过多的处理方案
 -- IN子句中的结果集可能存在着大量的重复字段。这些字段对于获取最后的查询结果而言，都是浪费资源的无用功，因此，结果集过多的第一个处理方案，就是考虑去重。
 -- 如果结果集中确实过大，导致即使结果去重后，内存存放仍然有压力，因此转存到磁盘当中。
 
 -- order_note不是索引，你怎么滴还能让他不进行全表扫描不成？当然，直接加索引是不成的。但是我们可以通过物化表的方式对sql进行改造，由优化器再次判断是否使用全表扫描。
 -- 当IN的结果集过大时，我们会将IN子句升级为物化表。升级流程如下：
 
 -- 1.该临时表的列就是子查询结果集中的列。
 -- 2.写入临时表的记录会被去重，临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引。
 
 -- 物化表是基于磁盘的么？不，这个表在不是特别大的时候是基于内存的。
 -- 一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。
 -- 如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者 max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+树索引。



--物化表转连接
-- 当我们把子查询进行物化之后，假设子查询物化表的名称为 materialized_table，该物化表存储的子查询结果集的列为 m_val，那么这个查询就相当于表 s1 和子查询物化表 materialized_table 进行内连接。
 SELECT s1.* FROM s1 INNER JOIN materialized_table ON order_note = m_val

 -- 驱动表算法 

ANY/ALL 子查询优化

如果 ANY/ALL 子查询是不相关子查询的话， 它们在很多场合都能转换成我们熟悉的方式去执行：

< ANY (SELECT inner_expr ...)   < (SELECT MAX(inner_expr) ...)  --小于任何一个，等价于小于最大的
> ANY (SELECT inner_expr ...)   > (SELECT MIN(inner_expr) ...)  --大于任何一个，等价于大于最小的
< ALL (SELECT inner_expr ...)   < (SELECT MIN(inner_expr) ...)  --小于所有的，等价于小于最小的
> ALL (SELECT inner_expr ...)   > (SELECT MAX(inner_expr) ...)  --大于所有的，等价于大于最大的

IN 和 EXISTS 区别

SQL 的保留字中，有很多都被归为谓词一类。例如，=,<,>,<> 等比较谓词，以及 BETWEEN 、LIKE 、IN 、IS NULL 等。

谓词是一种特殊的函数，返回值是真值。 前面提到的每个谓词，返回值都是 true 、 false 或者 unknown （一般的谓词逻辑里没有 unknown ，但是 SQL 采用的是三值逻辑，因此具有三种真值）。 谓词逻辑提供谓词是为了判断命题（可以理解成陈述句）的真假，而在关系数据库里，表中的一行数据可以看作是一个命题。

表常常被认为是行的集合，但从谓词逻辑的观点看，也可以认为是命题的集合（也就是描述每一行记录陈述句的集合）。

一言以蔽之，谓词的作用就是"判断是否存在满足某种条件的记录"。如果存在这样的记录就返回真TRUE ，如果不存在就返回假FALSE

也就是说，我们平时使用的 WHERE 子句，其实也可以看成是由多个谓词组合而成的新谓词。只有能让 WHERE 子句的返回值为真的命题，才能从表（命题的集合）中查询到。

不管子查询是相关的还是不相关的， 都可以把 IN 子查询尝试转为 EXISTS 子查询其实对于任意一个 IN 子查询来说， 都可以被转为 EXISTS 子查询

通用的例子如下：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
-- 可以被转换为：
EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr=inner_expr)


--需要注意的是， 如果 IN 子查询不满足转换为 semi-join 的条件， 又不能转换为物化表或者转换为物化表的成本太大， 那么它就会被转换为 EXISTS 查询。

在 MySQL5.5 以及之前的版本没有引进 semi-join 和物化的方式优化子查询时，优化器都会把 IN 子查询转换为 EXISTS 子查询，所以当时好多声音都是建议大家把子查询转为连接，不过随着 MySQL 的发展，最近的版本中引入了非常多的子查询优化策略，内部的转换工作优化器会为大家自动实现。

https://blog.csdn.net/weixin_47184173/article/details/117411011

为什么 300 的并发能把支持最大连接数 4000 数据库压死？

如何处理查询？

有时候，各种查询语句经常会直接查询超大表，甚至单表就超过整个服务器的所有物理内存了。那会不会一下子就打挂 MySQL 呢？

显然不会。客户端执行查询要求返回的结果集是很普遍的情况。

MySQL 线程状态

状态 含义
After create 当线程在创建表的函数末尾创建表（包括内部临时表）时，会发生这种情况。即使由于某些错误而无法创建表，也会使用此状态
altering table 服务器正在执行就地 ALTER TABLE
Analyzing 线程正在计算 MyISAM 表键分布（例如，for ANALYZE TABLE）。
checking permissions 线程正在检查服务器是否具有执行语句所需的权限
Checking table 该线程正在执行表检查操作
cleaning up 该线程已经处理了一个命令，并准备释放内存并重置某些状态变量
closing tables
committing alter table to storage engine
converting HEAP to MyISAM
copy to tmp table
Copying to group table
Copying to tmp table
Copying to tmp table on disk
Creating index
Creating sort index
creating table
Creating tmp table

存储引擎

为了方便管理，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为 MySQL Server 的功能，把真实存取数据的功能划分为存储引擎的功能。所以在 MySQL server 完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的 API，获取到数据后返回给客户端就好了。

MySQL 中提到了存储引擎的概念，简而言之，存储引擎就是指表的类型。其实存储引擎以前叫做表处理器，后来改名为存储引擎，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。