对于一些执行起来十分耗费性能的语句，MySQL 还是依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写
条件化简
查询语句的搜索条件本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，MySQL 的查询优化器会为简化这些表达式
移除不必要的括号
有时候表达式里有许多无用的括号，例如：
((a = 5 AND b = c) OR ((a > c) AND (c < 5)))
优化器会把那些用不到的括号给干掉，优化结果如下：
(a = 5 and b = c) OR (a > c AND c < 5)
常量传递（constant_propagation）
不等值传递
有时候某个表达式是某个列和某个常量做等值匹配，例如：
a = 5
当这个表达式和其他涉及列 a 的表达式使用 AND 连接起来时，可以将其他表达式中的 a 的值替换为 5，例如：
a = 5 AND b > a
将被转换为：
a = 5 AND b > 5
等值传递（equality_propagation）
有时候多个列之间存在等值匹配的关系，例如：
a = b and b = c and c = 5
这个表达式将被转换为：
a = 5 and b = 5 and c = 5
移除没用的条件（trivial_condition_removal）
对于一些明显永远为 TRUE 或者 FALSE 的表达式，优化器会移除掉它们，例如：
(a < 1 and b = b) OR (a = 6 OR 5 != 5)
很明显，b = b 这个表达式永远为 TRUE，5 != 5 这个表达式永远为 FALSE，所以简化后的表达式就是这样的：
(a < 1 and TRUE) OR (a = 6 OR FALSE)
最后将会被转换为：
a < 1 OR a = 6
表达式计算
在查询开始执行之前，如果表达式中只包含常量的话，它的值会被先计算出来，比如这个：
a = 5 + 1
因为 5 + 1 这个表达式只包含常量，所以就会被化简成：
a = 6
但是这里需要注意的是，如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，例如：
ABS(a) > 5 或者 -a < -8
优化器是不会尝试对这些表达式进行化简的。之前提到过只有搜索条件中索引列和常数使用某些运算符连接起来才可能使用到索引，所以如果可以的话，最好让索引列以单独的形式出现在表达式中
常量表检测
MySQL 规定认为 使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表 运行的特别快，因此 MySQL 觉得这两种查询花费的时间特别少，少到可以忽略，所以也把通过这两种方式查询的表称之为常量表（英文名：constant tables）。优化器在分析一 个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本，例如：
SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column2 WHERE table1.primary_key = 1;
很明显，这个查询可以使用主键和常量值的等值匹配来查询 table1 表，也就是在这个查询中 table1 表相当于常量表，在分析对 table2 表的查询成本之前，就会执行对 table1 表的查询，并把查询中涉及 table1 表的条件都替换掉，也就是上边的语句会被转换成这样：
SELECT table1.表记录的各个字段的常量值, table2.* FROM table1 INNER JOIN table2 ON table1.column1 列的常量值 = table2.column2;
外连接消除
内连接的驱动表和被驱动表的位置可以相互转换，而左（外）连接和右（外）连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序
外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录会被舍弃。查询效果就是这样：
SELECT * FROM e1 INNER JOIN e2 ON e1.m1 = e2.m2;
SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2;
对于上边例子中的（左）外连接来说，由于驱动表 e1 中 m1=1, n1='a’的记录无法在被驱动表 e2 中找到符合 ON 子句条件 e1.m1 = e2.m2 的记录，所以就直接把这条记录加入到结果集，对应的 e2 表的 m2 和 n2 列的值都设置为 NULL
因为凡是不符合 WHERE 子句中条件的记录都不会参与连接。只要在搜索条件中指定关于被驱动表相关列的值不为 NULL，那么外连接中在被驱动表中找不到符合ON子句条件的驱动表记录也就被排除出最后的结果集了，也就是说，在这种情况下：外连接和内连接也就没有什么区别了！比方说这个查询：
SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2 WHERE e2.n2 IS NOT NULL;
由于指定了被驱动表 e2 的 n2 列不允许为 NULL，所以上边的 e1 和 e2 表的左（外）连接查询和内连接查询是一样的。当然，也可以不用显式的指定被驱动表的某个列 IS NOT NULL，只要隐含的有这个意思就行了，比方说这样：
SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2 WHERE e2.m2 = 2;
在这个例子中，在 WHERE 子句中指定了被驱动表 e2 的 m2 列等于 2， 也就相当于间接的指定了 m2 列不为 NULL 值，所以上边的这个左（外）连接查询其实和下边这个内连接查询是等价的：
SELECT * FROM e1 INNER JOIN e2 ON e1.m1 = e2.m2 WHERE e2.m2 = 2;
把这种在外连接查询中，指定的 WHERE 子句中包含被驱动表中的列不为 NULL 值的条件称之为空值拒绝（英文名：reject-NULL）。在被驱动表的 WHERE 子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询
子查询优化
子查询语法
在一个查询语句 A 里的某个位置也可以有另一个查询语句 B，这个出现在 A 语句的某个位置中的查询 B 就被称为子查询，A 也被称之为外层查询。子查询可以在一个外层查询的各种位置出现，比如：
- SELECT 子句中
SELECT (SELECT m1 FROM e1 LIMIT 1);
其中的(SELECT m1 FROM e1 LIMIT 1)就是子查询
- FROM 子句中
SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM e2 WHERE m2 > 2) AS t;
这个例子中的子查询是：(SELECT m2 + 1 AS m, n2 AS n FROM e2 WHERE m2 > 2)，这里可以把子查询的查询结果当作是一个表，子查询后边的 AS t 表明这个子查询的结果就相当于一个名称为 t 的表，这个名叫 t 的表的列就是子查询结果中的列，比如例子中表 t 就有两个列：m 列和 n 列。这个放在 FROM 子句中的子查询本质上相当于一个表，但又和平常使用的表有点儿不一样，MySQL 把这种由子查询结果集组成的表称之为派生表
- WHERE 或 ON 子句中
把子查询放在外层查询的 WHERE 子句或者 ON 子句中可能是最常用的一种使用子查询的方式了，例如：
SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);
这个查询表明想要将(SELECT m2 FROM e2)这个子查询的结果作为外层查询的 IN 语句参数，整个查询语句的意思就是想找 e1 表中的某些记录，这些记录的 m1 列的值能在 e2 表的 m2 列找到匹配的值
- ORDER BY 子句、GROUP BY 子句中
虽然语法支持，但不是很常用，在此不做深入
按返回的结果集区分子查询
因为子查询本身也算是一个查询，所以可以按照它们返回的不同结果集类型而把这些子查询分为不同的类型
标量子查询
那些只返回一个单一值的子查询称之为标量子查询，例如：
SELECT (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 * FROM e1 WHERE (m1, n1) = (SELECT m2, n2 FROM e2 LIMIT 1);
其中的(SELECT m2, n2 FROM e2 LIMIT 1)就是一个行子查询，整条语句的含义就是要从 e1 表中找一些记录，这些记录的 m1 和 n1 列分别等于子查询结果中的 m2 和 n2 列
列子查询
列子查询自然就是查询出一个列的数据，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了），例如：
SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);
其中的(SELECT m2 FROM e2)就是一个列子查询，表明查询出 e2 表的 m2 列的值作为外层查询 IN 语句的参数
表子查询
顾名思义，就是子查询的结果既包含很多条记录，又包含很多个列，例如：
SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);
其中的(SELECT m2, n2 FROM e2)就是一个表子查询，这里需要和行子查询对比一下，行子查询中用了 LIMIT 1 来保证子查询的结果只有一条记录，表子查询中不需要这个限制
按与外层查询关系来区分子查询
不相关子查询
如果子查询可以单独运行出结果，而不依赖于外层查询的值，就可以把这个子查询称之为不相关子查询，按返回的结果集区分的子查询中的例子都是不相关子查询
相关子查询
如果子查询的执行需要依赖于外层查询的值，就可以把这个子查询称之 为相关子查询。比如：
SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2 WHERE n1 = n2);
例子中的子查询是(SELECT m2 FROM e2 WHERE n1 = n2)，可是这个查询中有一个搜索条件是 n1 = n2，而 n1 是表 e1 的列，也就是外层查询的列，也就是说子查询的执行需要依赖于外层查询的值，所以这个子查询就是一个相关子查询
[NOT] IN/ANY/SOME/ALL 子查询
对于列子查询和表子查询来说，它们的结果集中包含很多条记录，这些记录相当于是一个集合，所以就不能单纯的和另外一个操作数使用操作符来组成布尔表达式了，MySQL 通过下面的语法来支持某个操作数和一个集合组成一个布尔表达式：
IN 或者 NOT IN
具体的语法形式如下：
操作数 [NOT] IN (子查询)
这个布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成的集合中，比如下边的查询的意思是找出 e1 表中的某些记录，这些记录存在于子查询的结果集中：
SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);
ANY/SOME（ANY 和 SOME 是同义词）
具体的语法形式如下：
操作数 比较符 ANY/SOME(子查询)
这个布尔表达式的意思是只要子查询结果集中存在某个值和给定的操作数做比较操作，比较结果为 TRUE，那么整个表达式的结果就为 TRUE，否则整个表达式的结果就为 FALSE。比方说下边这个查询：
SELECT * FROM e1 WHERE m1 > ANY(SELECT m2 FROM e2);
这个查询的意思就是对于 e1 表的某条记录的 m1 列的值来说，如果子查询 (SELECT m2 FROM e2)的结果集中存在一个小于 m1 列的值，那么整个布尔表达式的值就是 TRUE，否则为 FALSE，也就是说只要 m1 列的值大于子查询结果集中最小的值，整个表达式的结果就是 TRUE，所以上边的查询本质上等价于这个查询：
SELECT * FROM e1 WHERE m1 > (SELECT MIN(m2) FROM e2);
另外，= ANY 相当于判断子查询结果集中是否存在某个值和给定的操作数相等，它的含义和 IN 是相同的
ALL
具体的语法形式如下：
操作数 比较操作 ALL(子查询)
这个布尔表达式的意思是子查询结果集中所有的值和给定的操作数做比较操作比较结果为 TRUE，那么整个表达式的结果就为 TRUE，否则整个表达式的结果就为 FALSE。比方说下边这个查询：
SELECT * FROM e1 WHERE m1 > ALL(SELECT m2 FROM e2);
这个查询的意思就是对于 e1 表的某条记录的 m1 列的值来说，如果子查询 (SELECT m2 FROM e2)的结果集中的所有值都小于 m1 列的值，那么整个布尔表达式的值就是 TRUE，否则为 FALSE，也就是说只要 m1 列的值大于子查询结果集中最大的值，整个表达式的结果就是 TRUE，所以上边的查询本质上等价于这个查询：
SELECT * FROM e1 WHERE m1 > (SELECT MAX(m2) FROM e2);
EXISTS 子查询
有的时候仅仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体是什么，可以使用把 EXISTS 或者 NOT EXISTS 放在子查询语句前边，就像这样：
SELECT * FROM e1 WHERE EXISTS (SELECT 1 FROM e2);
对于子查询(SELECT 1 FROM e2)来说，并不关心这个子查询最后到底查询出的结果是什么，所以查询列表里填*、某个列名，或者其他啥东西都无所谓，真正关心的是子查询的结果集中是否存在记录。也就是说只要(SELECT 1 FROM e2)这个查询中有记录，那么整个 EXISTS 表达式的结果就为 TRUE
子查询语法注意事项
- 子查询必须用小括号扩起来
- 在 SELECT 子句中的子查询必须是标量子查询，如果子查询结果集中有多个列或者多个行，都不允许放在 SELECT 子句中，在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用 LIMIT 1 语句来限制记录数量
- 对于[NOT] IN/ANY/SOME/ALL 子查询来说，子查询中不允许有 LIMIT 语句，而且这类子查询中 ORDER BY 子句、DISTINCT 语句、没有聚集函数以及 HAVING 子句的 GROUP BY 子句没有什么意义。因为子查询的结果其实就相当于一个集合，集合里的值排不排序等没有意义
- 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询
子查询在 MySQL 中的执行方式
推断子查询的执行方式
- 如果该子查询是不相关子查询，比如下边这个查询：
select * from t1 where a in (select a from t2);
首先执行子查询，然后将查询到的结果作为常量集合来执行外层查询
- 如果该子查询是相关子查询，比如下边这个查询：
select * from t1 where a in (select a from t2 where t2.id = t1.id);
该子查询的执行依赖着外层查询的值，先从外层查询中获取一条记录，然后执行子查询，最后根据子查询的查询结果来检测外层查询 WHERE 子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃
MySQL 子查询执行优化
其实 MySQL 用了一系列的办法来优化子查询的执行，并且大部分场景下，优化都是很有效的
标量子查询、行子查询的执行方式
对于不相关标量子查询或者行子查询来说，它的执行方式和上面推断的一样：先单独执行子查询，然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询
对于相关的标量子查询或者行子查询来说，它的执行方式和上面推断的一样：先从外层查询中获取一条记录，然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，然后执行子查询，最后根据子查询的查询结果来检测外层查询 WHERE 子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃，依次类推执行所有的外层记录
对于两种使用标量子查询以及行子查询的场景中，MySQL 优化器的执行方式并没有什么优化方式
MySQL 对 IN 子查询的优化
物化表
对于不相关的 IN 子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率很高，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：
- 结果集太多，可能内存中都放不下
- 对于外层查询来说，如果子查询的结果集太多，那就意味着 IN 子句中的参数特别多，这就导致：无法有效的使用索引，只能对外层查询进行全表扫描
在对外层查询执行全表扫描时，由于 IN 子句中的参数太多，这会导致检测一条记录是否符合和 IN 子句中的参数匹配花费的时间太长
MySQL 的改进是不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：
- 该临时表的列就是子查询结果集中的列
- 写入临时表的记录会被去重，临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引
一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引
如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者 max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+树索引
MySQL 把这个将子查询结果集中的记录保存到临时表的过程称之为物化（英文名：Materialize），正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有 B+树索引），通过索引执行 IN 语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能
物化表转连接
把子查询进行物化之后，假设子查询物化表的名称为 materialized_table，该物化表存储的子查询结果集的列为 m_val，那么这个查询就相当于外层查询表和物化表之间进行内连接，此时，查询优化器可以评估不同连接顺序需要的成本是多少，选取成本最低的那种查询方式执行查询
将子查询转换为 semi-join
由于 IN 子查询与连接查询有相似之处，因此可以借助连接查询的优化方式，它们的区别是 IN 子查询的返回记录可能会有多条，但关注点是是否有匹配，而连接查询是严格匹配，因此 IN 子查询和两表连接之间并不完全等价
将子查询转换为连接又真的可以充分发挥优化器的作用，所以 MySQL 在这里提出了一个新概念 — 半连接（英文名：semi-join）
注意： semi-join 只是在 MySQL 内部采用的一种执行子查询的方式，MySQL 并没有提供面向用户的 semi-join 语法
Table pullout （子查询中的表上拉）
当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的 FROM 子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中
此时 IN 子查询等价于连接查询的依据是，IN子查询返回的数据本身就具有唯一性
DuplicateWeedout execution strategy （重复值消除）
转换为半连接查询后，表 1 中的某条记录可能在表 2 中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中，为了消除重复，可以建立一个临时表，例如：
CREATE TABLE tmp ( id PRIMARY KEY );
这样在执行连接查询的过程中，每当某条表 1 中的记录要加入结果集时，就首先把这条记录的 id 值加入到这个临时表里，如果添加成功，说明之前这条表 1 中的记录并没有加入最终的结果集，现在把该记录添加到最终的结果集；如果添加失败，说明之前这条表 1 中的记录已经加入过最终的结果集，这里直接把它丢弃就好了，这种使用临时表消除 semi-join 结果集中的重复值的方式称之为 DuplicateWeedout
LooseScan execution strategy （松散扫描）
对于子查询返回的数据如果刚好能被索引覆盖，那么就会以子查询的表作为驱动表进行半连接查询，然后在扫描索引的过程中仅仅扫描相同值的第一条记录，这样的扫描方式提高了性能，称之为松散扫描
MySQL 中的半连接方式还有好几种，比如 Semi-join Materializationa 半连接物化、FirstMatch execution strategy （首次匹配） 等等，之后会进行详细补充
semi-join 的适用条件
并不是所有包含 IN 子查询的查询语句都可以转换为 semi-join，只有形如这样的查询才可以被转换为 semi-join：
SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...
或者
SELECT ... FROM outer_tables WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...
- 该子查询必须是和 IN 语句组成的布尔表达式，并且在外层查询的 WHERE 或者 ON 子句中出现
- 外层查询也可以有其他的搜索条件，只不过和 IN 子查询的搜索条件必须使用 AND 连接起来
- 该子查询必须是一个单一的查询，不能是由若干查询由 UNION 连接起来的形式
- 该子查询不能包含 GROUP BY 或者 HAVING 语句或者聚集函数
MySQL 对不能转为 semi-join 查询的子查询优化
- 对于不相关子查询来说，可以尝试把它们物化之后再参与查询，物化之后由于存在索引（基于磁盘的B+树或基于内存的Hash索引），可以加快判断记录是否在物化表中的检索速度
- 不管子查询是相关的还是不相关的，都可以把 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 转换成为 EXISTS 会利用其利用不到的索引，比如：
对于子查询是一个相关子查询，如果条件中没有索引字段，查询的数据列中包含索引字段，在转换为 EXISTS 子查询后便能利用到索引
需要注意的是，如果 IN 子查询不满足转换为 semi-join 的条件，又不能转换为物化表或者转换为物化表的成本太大，那么它就会被转换为 EXISTS 查询
在 MySQL5.5 以及之前的版本没有引进 semi-join 和物化的方式优化子查询时，优化器都会把 IN 子查询转换为 EXISTS 子查询，在之后的版本都会进行自动优化操作
全局考虑性能优化
分析查询速度慢的所涉及的因素
在尝试编写快速的查询之前，需要清楚一点，真正重要是响应时间。如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快
MySQL 查询的生命周期大致可以按照顺序来看:从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等
在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU 计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作,CPU 操作和内存不足时导致的 IO 操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用
优化查询的目的就是减少和消除这些操作所花费的时间
回顾查询执行的流程
当希望 MySQL 能够以更高的性能运行查询时，最好的办法就是弄清楚 MySQL 是如何优化和执行查询的。一旦理解这一点，很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行
根据下图，可以看到当向 MySQL 发送一个请求的时候，MySQL 到底做了些什么:
- 客户端发送一条查询给服务器
- 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段（查询缓存在mysql8中取消）
- 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行计划
- MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询
- 将结果返回给客户端
MySQL 客户端/服务器通信协议
一般来说，不需要去理解 MySQL 通信协议的内部实现细节，只需要大致理解通信协议是如何工作的。MySQL 客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，无法也无须将一条消息切成小块独立来发送
这种协议让 MySQL 通信简单快速，但是也从很多地方限制了 MySQL。一个明显的限制是，这意味着没法进行流量控制。一旦一端开始发生消息，另一端要接收完整个消息才能响应它。这就像来回抛球的游戏﹔在任何时刻，只有一个人能控制球，而且只有控制球的人才能将球抛回去（发送消息)
客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候，参数 max_allowed_packet 就特别重要了。一旦客户端发送了请求，它能做的事情就只是等待结果了
一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果后就“粗暴”地断开连接，都存在相应的问题。这也是在必要的时候一定要在查询中加上 LIMIT 限制的原因
换一种方式解释这种行为:当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是 MySQL 在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来
多数连接 MySQL 的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL 通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源
当使用库函数从 MySQL 获取数据时，其结果看起来都像是从 MySQL 服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集的时候，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集
对于 Java 程序来说，很有可能发生 OOM，所以 MySQL 的 JDBC 里提供了 setFetchSize() 之类的功能，来解决这个问题：
- 当 statement 设置以下属性时，采用的是流数据接收方式，每次只从服务器接收部份数据，直到所有数据处理完毕，不会发生 JVM OOM
setResultSetType(ResultSet.TYPE_FORWARD_ONLY);
setFetchSize(Integer.MIN_VALUE);
- 调用 statement 的 enableStreamingResults 方法，实际上 enableStreamingResults 方法内部封装的就是第 1 种方式
- 设置连接属性 useCursorFetch=true (5.0 版驱动开始支持)，statement 以 TYPE_FORWARD_ONLY 打开，再设置 fetch size 参数，表示采用服务器端游标，每次从服务器取 fetch_size 条数据
con = DriverManager.getConnection(url);
ps = (PreparedStatement) con.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(Integer.MIN_VALUE);
ps.setFetchDirection(ResultSet.FETCH_REVERSE);
rs = ps.executeQuery();
while (rs.next()) {……实际的业务处理}
查询状态
对于一个 MySQL 连接，或者说一个线程，任何时刻都有一个状态，该状态表示了 MySQL 当前正在做什么。在一个查询的生命周期中，状态会变化很多次
查询优化处理
查询的生命周期的下一步是将一个 SQL 转换成一个执行计划，MySQL 再依照这个执行计划和存储引擎进行交互。这包括多个子阶段:解析 SQL、预处理、优化 SQL 执行计划。这个过程中任何错误（例如语法错误）都可能终止查询。在实际执行中，这几部分可能一起执行也可能单独执行
MySQL 的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单地分为两种，一种是静态优化，一种是动态优化。静态优化可以直接对解析树进行分析，并完成优化。例如，优化器可以通过一些简单的代数变换将 WHERE 条件转换成另一种等价形式。静态优化不依赖于特别的数值，如 WHERE 条件中带入的一些常数等。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种“编译时优化”
相反，动态优化则和查询的上下文有关，也可能和很多其他因素有关，例如 WHERE 条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时 候都重新评估，可以认为这是“运行时优化”
优化器是相当复杂和智能的。建议“不要自以为比优化器更聪明”。如果没有必要，不要去干扰优化器的工作，让优化器按照它的方式工作。尽量按照优化器的提示去优化表、索引和 SQL 语句，比如写查询，或者重新设计更优的库表结构，或者添加更合适的索引。但是请尽可能的保持 SQL 语句的简洁
当然，虽然优化器已经很智能了，但是有时候也无法给出最优的结果。有时候你可能比优化器更了解数据，例如，由于应用逻辑使得某些条件总是成立；
还有时，优化器缺少某种功能特性，如哈希索引﹔再比如从优化器的执行成本角度评估出来的最优执行计划，实际运行中可能比其他的执行计划更慢
如果能够确认优化器给出的不是最佳选择，并且清楚优化背后的原理，那么也可以帮助优化器做进一步的优化
查询执行引擎
在解析和优化阶段，MySQL 将生成查询对应的执行计划，MySQL 的查询执行引擎则根据这个执行计划来完成整个查询。相对于查询优化阶段，查询执行阶段不是那么复杂：MySQL 只是简单地根据执行计划给出的指令逐步执行
返回结果给客户端
查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL 仍然会返回这个查询的一些信息，如该查询影响到的行数
如果查询可以被缓存，那么 MySQL 在这个阶段也会将结果存放到查询缓存中
MySQL 将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器开始生成第一条结果时，MySQL 就可以开始向客户端逐步返回结果集了
这样处理有两个好处﹔服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让 MySQL 客户端第一时间获得返回的结果。结果集中的每一行都会以一个满足 MySQL 客户端/服务器通信协议的封包发送，再通过 TCP 协议进行传输，在 TCP 传输的过程中，可能对 MySQL 的封包进行缓存然后批量传输
通过 show profile 分析 SQL
通过应用程序访问 MySQL 服务时，有时候性能不一定全部卡在语句的执行上。当然通过慢查询日志定位那些执行效率较低的 SQL 语句时候我们常用的手段，但是：
- 慢查询日志在查询结束以后才记录，在应用反映执行效率出现问题的时候查询未必执行完成
- 有时候问题的产生不一定是语句的执行，有可能是其他原因导致的。慢查询日志并不能定位问题
show processlist
参考文档：
https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html
show processlist;
通过上面语句查看线程状态非常有用,这可以很快地了解当前 MySQL 在进行的线程,包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。在一个繁忙的服务器上，可能会看到大量的不正常的状态，例如 statistics 正占用大量的时间。这通常表示，某个地方有异常了。线程常见的状态有很多，比如
statistics
The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk-bound performing other work
服务器正在计算统计信息以研究一个查询执行计划。如果线程长时间处于此状态，则服务器可能是磁盘绑定执行其他工作
Creating tmp table
The thread is creating a temporary table in memory or on disk. If the table is created in memory but later is converted to an on-disk table, the state during that operation is Copying to tmp table on disk
该线程正在内存或磁盘上创建临时表。如果表在内存中创建但稍后转换为磁盘表，则该操作期间的状态将为 Copying to tmp table on disk
Sending data
The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query
线程正在读取和处理 SELECT 语句的行 ，并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问（读取），因此它通常是给定查询生命周期中运行时间最长的状态
其他状态参考官方文档
show profile 分析
对于每个线程到底时间花在哪里，可以通过 show profile 来分析
- 首先检查当前 MySQL 是否支持 profile
select @@have_profiling;
- 默认 profiling 是关闭的，可以通过 set 语句在 Session 级别开启 profiling
select @@profiling;
set profiling=1;
- 执行一个 SQL 查询
select count(1) from t1;
- 通过 show profiles 语句，看到当前 SQL 的 Query ID
show profiles;
- 通过 show profile for query 语句能够看到执行过程中线程的每个状态和消耗的时间
show profile for query 17;
通过仔细检查 show profile for query 的输出，能够发现在执行 COUNT(1) 的过程中，时间主要消耗在 executing 这个状态上
- 在获取到最消耗时间的线程状态后，MySQL 支持进一步选择 all、cpu、 block io、contextswitch、page faults 等明细类型来查看 MySQL 在使用什么资源上耗费了过高的时间
show profile all for query 17;
能够发现 executing 状态下，时间主要消耗在 CPU 上了
补充：
如果 MySQL 源码感兴趣，还可以通过 show profile source for query 查看 SQL 解析执行过程中每个步骤对应的源码的文件、函数名以及具体的源文件行数
show profile source for 17;
537
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
