嵌套连接优化
表连接允许嵌套。
table_factor
table_factor 语法对标准 SQL 进行扩展，标准 SQL 只接收 table_reference，而 table_factor 额外支持列表。
SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
-- 等价于
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
在 MySQL 中，CROSS JOIN在语法上等同于INNER JOIN；它们可以相互替换。在标准 SQL 中，它们并不等效。INNER JOIN与ON子句一起使用；CROSS JOIN在其他情况下使用。
一般来说，在仅包含内连接操作的连接表达式中，可以忽略括号。考虑这个连接表达式：
t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
ON t1.a=t2.a
删除括号并向左进行分组操作后，该连接表达式将转换为以下表达式：
(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3
ON t2.b=t3.b OR t2.b IS NULL
然而，这两个表达式并不等价。要看到这一点，假设表t1、t2和t3具有以下状态：
- 表
t1包含行（1）、``（2） - 表
t2包含行（1,101） - 表
t3包含行（101）
在这种情况下，第一个表达式返回一个结果集，其中包括行(1,1,101,101)、(2,NULL,NULL,NULL)，而第二个表达式返回行(1,1,101,101)、(2,NULL,NULL,101)。
mysql> SELECT *
FROM t1
LEFT JOIN
(t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
ON t1.a=t2.a;
+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | NULL |
+------+------+------+------+
mysql> SELECT *
FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
LEFT JOIN t3
ON t2.b=t3.b OR t2.b IS NULL;
+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | 101 |
+------+------+------+------+
在以下示例中，外部连接操作与内部连接操作一起使用：
t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
该表达式不能转换为以下表达式：
t1 LEFT JOIN t2 ON t1.a=t2.a, t3
对于给定的表状态，这两个表达式返回不同的行集：
mysql> SELECT *
FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;
+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | NULL |
+------+------+------+------+
mysql> SELECT *
FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | 101 |
+------+------+------+------+
因此，如果我们在带有外连接运算符的连接表达式中省略括号，我们可能会改变原始表达式的结果集。
更确切地说，我们不能忽略左外连接操作的右操作数以及右连接操作的左操作数中的括号。换句话说，我们不能忽略外连接操作的内部表表达式中的括号。外部表的另一个操作数（外部表的操作数）的括号可以忽略。
(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)
等效于任何表的此表达式 t1、t2、t3 和任何条件 P 对属性 t2.b 和 t3.b 的响应：
t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)
每当连接表达式（joined_table）中的连接操作执行顺序不是从左到右时，我们就称之为嵌套连接。考虑以下查询：
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a
WHERE t1.a > 1
SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1
这些查询被认为包含这些嵌套连接：
t2 LEFT JOIN t3 ON t2.b=t3.b
t2, t3
在第一个查询中，嵌套连接是通过左连接操作形成的。在第二个查询中，它是通过内连接操作形成的。
在第一个查询中，可以省略括号：连接表达式的语法结构决定了连接操作的执行顺序相同。对于第二个查询，括号不能省略，尽管这里的连接表达式在没有括号的情况下也可以明确解释。在我们的扩展语法中，第二个查询中的(t2,t3)括号是必需的，尽管理论上可以在没有括号的情况下解析查询：我们仍然会为查询提供明确的语法结构，因为LEFT JOIN和ON扮演表达式(t2,t3)的左右分隔符的角色。
前面的示例演示了这些要点：
- 对于仅涉及内连接（而非外连接）的连接表达式，可以删除括号并从左到右评估连接。实际上，表可以以任何顺序进行评估。
- 一般来说，对于外连接或外连接与内连接混合的情况并非如此。删除括号可能会改变结果。
带有嵌套外连接的查询与带有内连接的查询以相同的流水线方式执行。更确切地说，利用了嵌套循环连接算法的一种变体。回想一下嵌套循环连接执行查询的算法（请参见10.2.1.7 节“嵌套循环连接算法”）。假设对三个表T1,T2,T3的连接查询具有以下形式：
SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2) INNER JOIN T3 ON P2(T2,T3) WHERE P(T1,T2,T3)
在这里， P1(T1,T2)和 P2(T3,T3)是一些连接条件（on 表达式），而 P(T1,T2,T3)是对表 T1,T2,T3列的一个条件。
嵌套循环连接算法将以以下方式执行此查询：
FOR each row t1 in T1 {
FOR each row t2 in T2 such that P1(t1,t2) {
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
t1||t2||t3的表示法表示通过连接行t1、t2和t3的列构造的行。在下面的一些示例中，当表名出现的位置为NULL时，表示在该行中，该表的每一列都使用NULL。例如，t1||t2||NULL表示通过连接行t1和t2的列以及NULL
现在考虑一个带有嵌套外连接的查询：
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON P2(T2,T3))
ON P1(T1,T2)
WHERE P(T1,T2,T3)
对于此查询，修改嵌套循环模式以获得：
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
BOOL f2:=FALSE;
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF P(t1,t2,NULL) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
一般来说，对于外连接操作中第一个内部表的任何嵌套循环，都会引入一个标志，该标志在循环之前被关闭，并在循环之后进行检查。当从外部表的当前行中找到代表内部操作数的表中的匹配项时，该标志被打开。如果在循环周期结束时标志仍然关闭，则表示外部表的当前行未找到匹配项。在这种情况下，内部表的列将用NULL补充该行。结果行将传递到最终输出检查或进入下一个嵌套循环，但仅当该行满足所有嵌入式外连接的连接条件时。
在该示例中，嵌入了由以下表达式表示的外连接表：
(T2 LEFT JOIN T3 ON P2(T2,T3))
对于带有内连接的查询，优化器可以选择不同的嵌套循环顺序，例如下面这个：
FOR each row t3 in T3 {
FOR each row t2 in T2 such that P2(t2,t3) {
FOR each row t1 in T1 such that P1(t1,t2) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
对于带有外连接的查询，优化器只能选择外表的循环先于内表循环的这样一种顺序。因此，对于我们带有外连接的查询，只有一种嵌套顺序是可能的。对于下面的查询，优化器评估两种不同的嵌套。在这两种嵌套中，T1必须在外层循环中处理，因为它用于外连接。T2和T3用于内连接，所以该连接必须在内层循环中处理。然而，因为连接是内连接，T2和T3可以以任意顺序处理。
在数据库查询的外连接操作中，外部表和内部表是相对的概念，它们的区别体现在连接顺序、数据处理方式和优化器的处理策略上。
- 定义：在使用外连接（如左外连接
LEFT JOIN、右外连接RIGHT JOIN或全外连接FULL JOIN）时，以左外连接为例，LEFT JOIN左边的表就是外部表，右边的表是内部表。例如在T1 LEFT JOIN (T2 LEFT JOIN T3 ON P2(T2,T3)) ON P1(T1,T2)中，T1是外部表，T2和T3在嵌套的外连接中，对于T1来说它们整体可看作内部表，同时T2对于T3又是外部表12。- 区别
- 连接顺序：在查询执行时，优化器会先处理外部表的循环，再处理内部表的循环。如
SELECT * FROM T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)，T1作为外部表，其循环会先执行，之后才是T2和T3所在的内部循环34。- 数据处理方式：外部表中的所有行都会出现在最终结果集中，即使在内部表中没有匹配的行，对于这些不匹配的情况，内部表对应的列会填充为
NULL。例如T1 LEFT JOIN T2 ON T1.a = T2.a，若T1中有行在T2中找不到匹配，结果集中对应T2的列值为NULL。而内部表只有满足连接条件的行才会参与结果集的生成，若不满足连接条件，这些行不会直接进入结果集1。- 优化器处理策略：对于外连接查询，优化器只能选择外部表循环在前、内部表循环在后的顺序。如上述查询中，
T1必须在外部循环处理，因为它用于外连接；而内部表T2和T3如果是内连接，它们的处理顺序可以互换 ，因为内连接的表顺序调整在某些情况下不影响结果集3。举例说明外部表和内部表的概念
外连接操作中，优化器是如何选择连接顺序的？
如何优化带有外连接的查询性能？
SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)
WHERE P(T1,T2,T3)
一个嵌套先计算T2，然后计算T3：
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
FOR each row t3 in T3 such that P2(t1,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f1:=TRUE
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
另一个嵌套先计算T3，然后计算T2：
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t3 in T3 such that P2(t1,t3) {
FOR each row t2 in T2 such that P1(t1,t2) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f1:=TRUE
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
在讨论内连接的嵌套循环算法时，我们省略了一些细节，这些细节对查询执行性能的影响可能很大。我们没有提到所谓的“下推”条件。假设我们的WHERE条件P(T1,T2,T3)可以用合取公式表示：
P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).
在这种情况下，MySQL 实际上使用以下嵌套循环算法来执行带有内连接的查询：
FOR each row t1 in T1 such that C1(t1) {
FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) {
FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
你会看到，每个合取项C1(T1)、C2(T2)、C3(T3)都从最内层循环推到最外层循环，在那里可以进行评估。如果C1(T1)是一个非常严格的条件，这种条件下推可能会大大减少从表T1传递到内层循环的行数。因此，查询的执行时间可能会大大缩短。
对于带有外连接的查询，只有在确定外表中的当前行在内部表中有匹配项后，才检查WHERE条件。因此，将条件从内部嵌套循环中推出的优化不能直接应用于带有外连接的查询。在这里，我们必须引入由标志保护的条件下推谓词，当遇到匹配项时，这些标志会被打开。
回忆这个带有外连接的示例：
P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)
对于那个示例，使用受保护的下推条件的嵌套循环算法如下：
FOR each row t1 in T1 such that C1(t1) {
BOOL f1:=FALSE;
FOR each row t2 in T2
such that P1(t1,t2) AND (f1?C2(t2):TRUE) {
BOOL f2:=FALSE;
FOR each row t3 in T3
such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {
IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1 && P(t1,NULL,NULL)) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
一般来说，下推谓词可以从连接条件中提取出来，例如P1(T1,T2)和P(T2,T3)。在这种情况下，下推谓词也由一个标志保护，该标志防止为相应的外连接操作生成的NULL补充行检查谓词。
如果由WHERE条件中的谓词引起，则禁止在相同嵌套连接中从一个内部表通过键访问另一个内部表。
表连接优化
哈希连接优化（Hash Join Optimization）
哈希连接是表连接的一种方式，其核心思路是：选择一个参与表连接的表，对其连接条件涉及的列值建立哈希表，遍历另外的表，计算连接条件涉及列值的哈希值并与哈希表中进行查找，查找到即一条数据连接完成直至所有数据连接完成。
哈希连接适用条件：对于任何查询，其中每个连接都有一个等值连接条件，并且没有可应用于任何连接条件的索引。
默认情况下，MySQL 会尽可能使用哈希连接。可以使用 BNL 和 NO_BNL 优化器提示之一来控制是否使用哈希连接，或者通过在 optimizer_switch 服务器系统变量的设置中设置 block_nested_loop=on 或 block_nested_loop=off 来控制。
哈希连接的内存使用量可以通过join_buffer_size系统变量进行控制；哈希连接使用的内存不能超过这个量。当哈希连接所需的内存超过可用内存时，MySQL 通过使用磁盘上的文件来处理这种情况。如果发生这种情况，你应该意识到，如果哈希连接无法装入内存并且创建的文件数量超过为open_files_limit设置的数量，连接可能不会成功。为避免此类问题，请进行以下任一更改：
- 增加
join_buffer_size，以便哈希连接不会溢出到磁盘。 - 增加
open_files_limit。
外部连接时会分配整个缓冲区，这意味着若缓冲区较大，小查询和大查询都是用的整个连接缓冲区，就是说小查询会浪费连接缓冲区。不过，哈希连接算法会用于外部连接，而哈希连接算法中缓冲区是增量分配的（连接操作一点一点地增量申请缓冲区），因此可以将join_buffer_size设置得更高，而无需担心小查询分配非常大量的内存。
CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);
内部等值连接
mysql> EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using join buffer (hash join)
哈希连接也用于涉及多个连接的查询，只要每对表至少有一个连接条件是等值连接
SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) JOIN t3 ON (t2.c1 = t3.c1);
mysql> EXPLAIN FORMAT=TREE
-> SELECT *
-> FROM t1
-> JOIN t2
-> ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
-> JOIN t3
-> ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t3.c1 = t1.c1) (cost=1.05 rows=1)
-> Table scan on t3 (cost=0.35 rows=1)
-> Hash
-> Filter: (t1.c2 < t2.c2) (cost=0.70 rows=1)
-> Inner hash join (t2.c1 = t1.c1) (cost=0.70 rows=1)
-> Table scan on t2 (cost=0.35 rows=1)
-> Hash
-> Table scan on t1 (cost=0.35 rows=1)
- 整体查询是一个多表连接查询，使用
EXPLAIN FORMAT=TREE来查看查询执行计划的树形结构。 2. 最外层是一个内连接（Inner hash join），连接条件是t3.c1 = t1.c1，预计成本cost=1.05，返回行数rows=1。 3. 这个内连接的左子树是对t3表进行全表扫描（Table scan on t3），成本cost=0.35，预计返回行数rows=1。 4. 右子树是一个Hash结构，Hash结构内部是一个过滤条件（Filter）：(t1.c2 < t2.c2)，成本cost=0.70，预计返回行数rows=1。 5. 这个过滤条件的左子树又是一个内连接（Inner hash join），连接条件是t2.c1 = t1.c1，成本cost=0.70，预计返回行数rows=1。 6. 这个内连接的左子树是对t2表进行全表扫描（Table scan on t2），成本cost=0.35，预计返回行数rows=1。 7. 右子树是另一个Hash结构，其内部是对t1表进行全表扫描（Table scan on t1），成本cost=0.35，预计返回行数rows=1。 简单来说，MySQL 先扫描t1表并构建哈希表，接着扫描t2表与t1表连接，再应用过滤条件，最后扫描t3表与前面的结果进行连接 。
内部非等值连接
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t2.c1) (cost=4.70 rows=12)
-> Inner hash join (no condition) (cost=4.70 rows=12)
-> Table scan on t2 (cost=0.08 rows=6)
-> Hash
-> Table scan on t1 (cost=0.85 rows=6)
内部等值非等值混合连接
mysql> EXPLAIN FORMAT=TREE
-> SELECT * FROM t1
-> JOIN t2 ON (t1.c1 = t2.c1)
-> JOIN t3 ON (t2.c1 < t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t3.c1) (cost=1.05 rows=1)
-> Inner hash join (no condition) (cost=1.05 rows=1)
-> Table scan on t3 (cost=0.35 rows=1)
-> Hash
-> Inner hash join (t2.c1 = t1.c1) (cost=0.70 rows=1)
-> Table scan on t2 (cost=0.35 rows=1)
-> Hash
-> Table scan on t1 (cost=0.35 rows=1)
内部笛卡尔积连接
mysql> EXPLAIN FORMAT=TREE
-> SELECT *
-> FROM t1
-> JOIN t2
-> WHERE t1.c2 > 50\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (cost=0.70 rows=1)
-> Table scan on t2 (cost=0.35 rows=1)
-> Hash
-> Filter: (t1.c2 > 50) (cost=0.35 rows=1)
-> Table scan on t1 (cost=0.35 rows=1)
左外连接
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Left hash join (t2.c1 = t1.c1) (cost=0.70 rows=1)
-> Table scan on t1 (cost=0.35 rows=1)
-> Hash
-> Table scan on t2 (cost=0.35 rows=1)
右外连接
注意：MySQL 会将所有右外连接重写为左外连接
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Left hash join (t1.c1 = t2.c1) (cost=0.70 rows=1)
-> Table scan on t2 (cost=0.35 rows=1)
-> Hash
-> Table scan on t1 (cost=0.35 rows=1)
半连接
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1
-> WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\G
*************************** 1. row ***************************
EXPLAIN: -> Hash semijoin (t2.c2 = t1.c1) (cost=0.70 rows=1)
-> Table scan on t1 (cost=0.35 rows=1)
-> Hash
-> Table scan on t2 (cost=0.35 rows=1)
反连接
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2
-> WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Hash antijoin (t1.c1 = t2.c1) (cost=0.70 rows=1)
-> Table scan on t2 (cost=0.35 rows=1)
-> Hash
-> Table scan on t1 (cost=0.35 rows=1)
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 't3.t2.c1' of SELECT #2 was resolved in SELECT #1
常量折叠优化（Constant-Folding Optimization）
列值与常量值比较时，常量值超出列类型取值范围或者常量值类型与列类型不匹配，两种问题在查询优化阶段一次性处理（而不是在执行阶段一行一行处理）。以这种方式处理的比较操作有大于（>）、大于等于（>=）、小于（<）、小于等于（<=）、不等于（<>/!=）、等于（=）和严格等于（<=>）。
CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);
SELECT * FROM t WHERE c < 256;
c 为 TINYINT UNSIGNED，其取值范围为 0-255，一定小于 256。
若无常量折叠优化，MySQL 将 c 列值和 256 视为更大的类型然后进行比较。使用常量折叠优化，由于 c < 256 始终为真，则查询语句被优化为：
SELECT * FROM t WHERE 1;
若 c 列允许为 NULL，则查询语句被优化为：
SELECT * FROM t WHERE c is not NULL;
常量折叠优化适用于如下MySQL列类型到常量类型的比较：
整数列类型
与整数常量比较
若常量超出列类型范围，将比较优化为 1 或者 is not null
若常量为范围边界，将比较优化为 =。
mysql> EXPLAIN SELECT * FROM t WHERE c >= 255;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 20.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t`.`ti` AS `ti` from `test`.`t` where (`test`.`t`.`ti` = 255)
1 row in set (0.00 sec)
与浮点数常量或者定点数常量对比
若常量是十进制类型之一（如DECIMAL、REAL、DOUBLE或FLOAT）且有非零小数部分，则不能相等；相应地进行处理。对于其他比较，根据符号向上或向下舍入为整数值，然后执行范围检查，并按照整数与整数比较的方式进行处理。
如果REAL值太小而无法表示为DECIMAL，则根据符号四舍五入到 0.01 或 -0.01，然后作为DECIMAL处理。
与字符串类型比较
尝试将字符串值解释为整数类型，然后按照整数之间的比较来处理。如果这一尝试失败，就尝试将该值作为实数（REAL）来处理。
DECIMAL 或 REAL 列
与整数常量比较
对列值的整数部分执行范围检查。如果没有折叠结果，则将常量转换为与列值具有相同小数位数的DECIMAL，然后将其作为DECIMAL进行检查（见下文）。
如果常量的小数部分有效数字多于列的类型，则截断常量。如果比较运算符是=或<>，则进行折叠。如果运算符是>=或<=，则由于截断而调整运算符。例如，如果列的类型是DECIMAL(3,1)，SELECT * FROM t WHERE f >= 10.13变为SELECT * FROM t WHERE f >10.1。
如果常量的小数位数少于列的类型，则将其转换为具有相同小数位数的常量。对于一个REAL值的下溢（即小数位数太少无法表示它），将常量转换为十进制的 0。
与字符串常量比较
如果该值可以解释为整数类型，则按整数类型处理。否则，尝试将其作为REAL处理。
FLOAT 或 DOUBLE 列
FLOAT （*m，n*）*``* 或 双精度 （*m，n*）*``* 与常量比较的值的处理方式如下：
如果该值超出列的范围，则折叠。
如果值有超过n位小数，则截断，在折叠过程中进行补偿。对于=和<>比较，按照前面所述折叠为TRUE、FALSE或IS [NOT]NULL；对于其他运算符，调整运算符。
如果该值的整数位数超过m位，则折叠。
限制
常量折叠优化无法适用如下场景：
- 使用 BETWEEN 和 IN 的比较
- BIT 列或者日期时间类型的比较
- 在预处理语句的优化阶段才能执行该优化，预处理语句的准备阶段不行，因为准备阶段常量值还未知。
SELECT 语句优化
WHERE 子句优化
行构造函数表达式优化
行构造函数表达式允许同时比较多个值。
SELECT * FROM t1 WHERE (column1,column2) = (1,1);
其与如下语句语义等效：
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
避免将行构造函数与 AND/OR 表达式混合使用以尽可能避免无法覆盖索引。
若行构造函数未覆盖索引的前缀，则优化器不会使用索引。
CREATE TABLE t1 (
c1 INT, c2 INT, c3 INT, c4 CHAR(100),
PRIMARY KEY(c1,c2,c3)
);
WHERE 子句使用了索引中的所有列，但是行构造函数本身并未覆盖索引前缀，导致优化器仅使用 c1（key_len=4，c1 的大小）。
mysql> EXPLAIN SELECT * FROM t1
WHERE c1 = 1 AND (c2,c3) > (1,1)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 3
filtered: 100.00
Extra: Using where
使用等效的非行构造函数表达式利用索引。(c2,c3) > (1,1)等价于c2 > 1 OR ((c2 = 1) AND (c3 > 1))，优化器使用索引中的所有三列（key_len=12）：
mysql> EXPLAIN SELECT * FROM t1
WHERE c1 = 1 AND (c2 > 1 OR ((c2 = 1) AND (c3 > 1)))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 12
ref: NULL
rows: 3
filtered: 100.00
Extra: Using where
在某些情况下，优化器可以将范围访问方法应用于具有行构造函数参数的 IN() 表达式。请参阅 行构造函数表达式的范围优化。
范围优化
range 使用单个索引检索包含在一个或多个索引区间内的表数据的子集。
仅检索给定范围内的行，使用索引来选择行。输出行中的 key 列指示使用了哪个索引。key_len 包含使用的最长键部分。ref 列对于此类型为 NULL。
当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN() 运算符将键列与常量进行比较时，可以使用 range
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
单列索引的范围条件定义如下：
- 对于
BTREE和HASH索引，当使用=、<=>、IN()、IS NULL或IS NOT NULL运算符时，将键部分与常量值进行比较是一个范围条件。 - 此外，对于
BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或<>运算符，或者如果LIKE的参数是一个不以通配符开头的常量字符串，则将键部分与常量值进行比较也是一个范围条件。 - 对于所有索引类型，多个使用
OR或AND组合的范围条件构成一个范围条件。
上述描述中的“常量值”是指以下之一：
索引合并优化
索引合并访问方法使用多个 range 扫描来检索行，并将它们的结果合并到一个结果中。这种访问方法只合并来自单个表的索引扫描，而不是跨多个表的扫描。合并可以生成其基础扫描的并集、交集或并集的交集。
在 EXPLAIN 输出中，索引合并方法在 type 列中显示为 index_merge。在这种情况下，key 列包含使用的索引列表，而 key_len 包含这些索引的最长键部分列表。
索引合并访问方法具有几种算法，这些算法显示在 EXPLAIN 输出的 Extra 字段中
使用 intersect(...)使用 union(...)使用 sort_union(...)
[!NOTICE]
索引合并优化算法有以下已知限制
影响索引合并优化
索引合并的使用受 index_merge、index_merge_intersection、index_merge_union 和 index_merge_sort_union 这些标志的值控制，这些标志属于 optimizer_switch 系统变量。参见 第 10.9.2 节，“可切换优化”。默认情况下，所有这些标志都为 on。要仅启用某些算法，请将 index_merge 设置为 off，并仅启用应允许的其他算法。
除了使用 optimizer_switch 系统变量在会话级别控制优化器对索引合并算法的使用外，MySQL 还支持优化器提示，以便在每个语句的基础上影响优化器。参见 第 10.9.3 节，“优化器提示”。
索引合并交集访问算法
当 WHERE 子句使用 AND 组合的不同键上的多个范围条件，并且每个条件都是以下之一时适用：
在
InnoDB表的主键上的任何范围条件。这种形式的
N部分表达式，其中索引正好有N部分（即，所有索引部分都已覆盖）key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
索引合并交集算法对所有使用的索引执行同步扫描，并生成它从合并的索引扫描中接收的行序列的交集。
如果查询中使用的所有列都由使用的索引覆盖，则不会检索完整表行（在这种情况下，EXPLAIN 输出的 Extra 字段包含 Using index）。如果使用的索引没有覆盖查询中使用的所有列，则只有当所有使用的键的范围条件都满足时才会检索完整行。如果合并的条件之一是在 InnoDB 表的主键上的条件，则它不会用于行检索，而是用于过滤掉使用其他条件检索到的行。
CREATE TABLE t1 (
pk INT PRIMARY KEY,
c1_udx INT,
c2_udx_p1 INT,
c3_udx_p2 INT,
c4_idx INT,
c5_idx_p1 INT,
c6_idx_p2 INT,
c7 INT,
UNIQUE (c1_udx),
UNIQUE (c2_udx_p1, c3_udx_p2),
INDEX (c4_idx),
INDEX (c5_idx_p1, c6_idx_p2)
);
示例
SELECT * FROM innodb_table
WHERE primary_key < 10 AND key_col1 = 20;
SELECT * FROM tbl_name
WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;
以下是一个此类查询的示例
SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;
索引合并并集访问算法
此算法的标准与索引合并交集算法的标准类似。当表的 WHERE 子句转换为使用 OR 组合的不同键上的多个范围条件时，该算法适用，并且每个条件都是以下之一
这种形式的
N部分表达式，其中索引正好有N部分（即，所有索引部分都已覆盖）key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN在
InnoDB表的主键上的任何范围条件。索引合并交集算法适用的条件。
示例
SELECT * FROM t1
WHERE key1 = 1 OR key2 = 2 OR key3 = 3;
SELECT * FROM innodb_table
WHERE (key1 = 1 AND key2 = 2)
OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;
EXPLAIN SELECT * FROM t1 WHERE c2_udx_p1 = 245 OR c4_idx = 25;
优化器基于统计信息和成本估算来决定是否使用 Index Merge Optimization。如果优化器认为其他访问方法更高效，它可能会选择不使用索引合并。
某些查询条件和索引类型可能不支持索引合并。
查询类型：索引合并主要适用于SELECT查询。对于INSERT、UPDATE和DELETE操作，索引合并通常不适用。
索引类型：不是所有类型的索引都可以参与索引合并。通常，B-tree索引是参与索引合并的主要类型。
————————————————
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/qq_26664043/article/details/135728732
索引合并是MySQL查询优化器的一种策略，它并不总是被使用。优化器会基于查询的成本估算来选择最佳的执行计划
IS NULL 优化
MySQL 可以对 col_name IS NULL 进行与对 col_name = constant_value 相同的优化。例如，MySQL 可以使用索引和范围来搜索带有 IS NULL 的 NULL。
若 WHERE 子句包含 col_name IS NULL 条件但 col_name 被定义为 NOT NULL，则该表达式将被优化而去掉，若该列允许为 NULL，该优化不会执行（例如，该列来自 LEFT JOIN 右侧的表）。
MySQL 还可以优化组合 *col_name* = *expr* OR *col_name* IS NULL，这是一种在解析后的子查询中常见的形式。 EXPLAIN 在使用此优化时显示 ref_or_null。
此优化可以处理任何键部分的一个 IS NULL。
假设表 t1 中 a和 b 列上有索引，则MySQL 会对如下语句进行 IS NULL 优化：
SELECT * FROM t1 WHERE t1.a IS NULL;
SELECT * FROM t1 WHERE t1.a <=> NULL;
SELECT * FROM t1 WHERE t1.a=1 OR t1.a=9 OR t1.a IS NULL;
SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;
SELECT * FROM t1, t2
WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;
SELECT * FROM t1, t2
WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
OR (t1.a=t2.a AND t2.a IS NULL AND ...);
ref_or_null 通过首先对引用键进行读取，然后对具有 NULL 键值的行的单独搜索来工作。
优化只能处理一个 IS NULL 层级。在以下查询中，MySQL 仅对表达式 (t1.a=t2.a AND t2.a IS NULL) 使用键查找，并且无法在 b 上使用键部分
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL)
OR (t1.b=t2.b AND t2.b IS NULL);
如何判断是否使用了 Index Merge Optimization
使用 EXPLAIN 语句可以查看查询的执行计划，如果 type 列显示为 index_merge，则说明 MySQL 使用了 Index Merge Optimization。
索引下推优化
表连接优化
嵌套循环连接算法
MySQL 使用嵌套循环连接算法（Nested-Loop Join Algorithm）或其变体来执行表之间的连接。
嵌套循环联接 （NLJ） 算法一次从循环中的第一个表中读取一行，将每一行传递给一个嵌套循环，该循环处理联接中的下一个表。此过程将重复多次，只要仍有要连接的表。
假设t1、t2和t3之间以如下连接类型进行连接：
Table Join Type
t1 range
t2 ref
t3 ALL
那么连接将按如下方式进行：
for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions, send to client
}
}
}
由于 NLJ 算法一次将一行从外部循环传递到内部循环，因此它通常会多次读取在内部循环中处理的表。
这里存在的问题是，有些失效场景我不能理解
https://developer.aliyun.com/article/1509769?spm=a2c6h.12873639.article-detail.9.72ba6560NaswWP
在上述表结构中有三个索引：
- id：为数据库主键;
- union_idx：为id_no、username、age构成的联合索引;
- create_time_idx：是由create_time构成的普通索引;
CREATE TABLE `t_user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`id_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份编号',
`username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`target_rating` int
`subjective_rating` int,
`objective_rating` int,
`actual_rating` int
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `union_idx` (`id_no`,`username`,`age`),
KEY `create_time_idx` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1001', 'Tom1', 11, '2022-02-27 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1002', 'Tom2', 12, '2022-02-26 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1003', 'Tom3', 13, '2022-02-25 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1004', 'Tom4', 14, '2023-02-25 09:04:23');
用于生成大量数据的存储过程
-- 删除历史存储过程
DROP PROCEDURE IF EXISTS `insert_t_user`
-- 创建存储过程
delimiter $
CREATE PROCEDURE insert_t_user(IN limit_num int)
BEGIN
DECLARE i INT DEFAULT 10;
DECLARE id_no varchar(18) ;
DECLARE username varchar(32) ;
DECLARE age TINYINT DEFAULT 1;
WHILE i < limit_num DO
SET id_no = CONCAT("NO", i);
SET username = CONCAT("Tom",i);
SET age = FLOOR(10 + RAND()*2);
INSERT INTO `t_user` VALUES (NULL, id_no, username, age, NOW());
SET i = i + 1;
END WHILE;
END $
-- 调用存储过程
call insert_t_user(100);
版本
select version();
分析索引是否失效，有一个重要的前提条件，就是是否使用覆盖索引，能使用覆盖索引的情况通常更容易使用索引
- 联合索引不满足最左前缀匹配原则
可以用到索引
explain select * from t_user where id_no = '098765432123456789' and username = 'Eve';
只能用一部分
explain select * from t_user where id_no = '098765432123456789' and age = 28;
完全用不了索引
explain select * from t_user where username = 'Eve' and age = 28;
2.左模糊
一定会导致索引失效
explain select * from t_user where id_no like '%12%';
explain select * from t_user where id_no like '%12';
2.索引列参与计算，或作为函数参数，或发生隐式类型转换
直接来看示例：
-- 查询所有3年后恰好三十五岁的用户
explain select * from t_user where age + 3 = 35;
-- 以
explain select * from t_user where target_rating + actual_rating = 119;
可以看到，即便id列有索引，由于进行了计算处理，导致无法正常走索引。
针对这种情况，其实不单单是索引的问题，还会增加数据库的计算负担。就以上述SQL语句为例，数据库需要全表扫描出所有的id字段值，然后对其计算，计算之后再与参数值进行比较。如果每次执行都经历上述步骤，性能损耗可想而知。
建议的使用方式是：先在内存中进行计算好预期的值，或者在SQL语句条件的右侧进行参数值的计算。
针对上述示例的优化如下：
-- 内存计算，得知要查询的id为1
explain select * from t_user where id = 1 ;
-- 参数侧计算
explain select * from t_user where id = 2 - 1 ;
第三种索引失效情况：索引列参与了运算，会导致全表扫描，索引失效。
explain select * from t_user where SUBSTR(id_no,1,3) = '100';
上述示例中，索引列使用了函数(SUBSTR，字符串截取)，导致索引失效。
此时，索引失效的原因与第三种情况一样，都是因为数据库要先进行全表扫描，获得数据之后再进行截取、计算，导致索引索引失效。同时，还伴随着性能问题。
示例中只列举了SUBSTR函数，像CONCAT等类似的函数，也都会出现类似的情况。解决方案可参考第三种场景，可考虑先通过内存计算或其他方式减少数据库来进行内容的处理。
id_no列发生隐式类型转换
explain select * from t_user where id_no = 99999999999;
比较操作
两列比较
两列数据做比较，无论是哪种比较，即便两列都创建了索引，索引也会失效。
explain select * from t_user where target_rating > actual_rating;
explain select * from t_user where target_rating = actual_rating;
explain select * from t_user where target_rating < actual_rating;
不等比较
explain select * from t_user where id_no <> '1002';
当查询条件为字符串时，使用”<>“或”!=“作为条件查询，有可能不走索引，但也不全是。
explain select * from t_user where create_time != '2022-02-27 09:56:42';
上述SQL中，由于“2022-02-27 09:56:42”是存储过程在同一秒生成的，大量数据是这个时间。执行之后会发现，当查询结果集占比比较小时，会走索引，占比比较大时不会走索引。此处与结果集与总体的占比有关。
需要注意的是：上述语句如果是id进行不等操作，则正常走索引。因为id是唯一的，该值仅仅会出现一次，很好把其他数据过滤出来
explain select * from t_user where id != 2;
is null、is not null
需要看优化器能否走索引
explain select * from t_user where id_no is not null;
11. not in和not exists
在日常中使用比较多的范围查询有in、exists、not in、not exists、between and等。
explain select * from t_user where id in (2,3);
explain select * from t_user where id_no in ('1001','1002');
explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id = 2 and u2.id = u1.id);
explain select * from t_user where id_no between '1002' and '1003';
上述四种语句执行时都会正常走索引，具体的explain结果就不再展示。主要看不走索引的情况：
explain select * from t_user where id_no not in('1002' , '1003');
explain结果：
当使用not in时，不走索引?把条件列换成主键试试：
explain select * from t_user where id not in (2,3);
explain结果：
如果是主键，则正常走索引。
第十一种索引失效情况：查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。
再来看看not exists：
explain select * from t_user u1 where not exists (select 1 from t_user u2 where u2.id = 2 and u2.id = u1.id);
explain结果：
当查询条件使用not exists时，不走索引。
第十一种索引失效情况：查询条件使用not exists时，索引失效。
优化
索引下推
索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。
MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。
索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。
我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：
- 存储引擎读取索引记录；
- 根据索引中的主键值，定位并读取完整的行记录；
- 存储引擎把记录交给
Server层去检测该记录是否满足WHERE条件。
使用ICP的情况下，查询过程：
- 存储引擎读取索引记录（不是完整的行记录）；
- 判断
WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录； - 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
- 存储引擎把记录交给
Server层，Server层检测该记录是否满足WHERE条件的其余部分。
索引条件下推的具体实践
理论比较抽象，我们来上一个实践。
使用一张用户表tuser，表里创建联合索引（name, age）。
如果现在有一个需求：检索出表中名字第一个字是张，而且年龄是10岁的所有用户。那么，SQL语句是这么写的：
sql
代码解读
复制代码select * from tuser where name like '张%' and age=10;
假如你了解索引最左匹配原则，那么就知道这个语句在搜索索引树的时候，只能用 张，找到的第一个满足条件的记录id为1。
那接下来的步骤是什么呢？
没有使用ICP
在MySQL 5.6之前，存储引擎根据通过联合索引找到name like '张%' 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。
我们看一下示意图：
可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。
使用ICP
而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到name like '张%'，由于联合索引中包含age列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。
我们看一下示意图：
可以看到只回表了一次。
除此之外我们还可以看一下执行计划，看到Extra一列里 Using index condition，这就是用到了索引下推。
sql 代码解读复制代码+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tuser | NULL | range | na_index | na_index | 102 | NULL | 2 | 25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
索引条件下推使用条件
- 只能用于
range、ref、eq_ref、ref_or_null访问方法； - 只能用于
InnoDB和MyISAM存储引擎及其分区表； - 对
InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;
索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于
InnoDB的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。
- 引用了子查询的条件不能下推；
- 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。
相关系统参数
索引条件下推默认是开启的，可以使用系统参数optimizer_switch来控制器是否开启。
mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@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,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)
手动切换状态
set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";