业务场景实践

予早 2025-09-03 01:34:47
Categories： Tags：

本文主要基于 MySQL 8.4 展开实践。

元信息

# MySQL 所在机器系统时区，不受 MySQL 配置影响
select @@system_time_zone;
# MySQL 实例全局时区，若为 SYSTEM 表示采用系统时区
SELECT @@global.time_zone;
# 查看当然会话时区设置，会话时区默认为 MySQL 实例全局时区
SELECT @@session.time_zone;
-- 查看系统时间，时区为会话时区
SELECT now();

-- 查看系统版本
SELECT VERSION();
-- 查看当前用户
SELECT USER();
-- 查看当前数据库
SELECT DATABASE();
-- 显示表描述信息
DESC mysql.user;

-- 查看事务隔离级别
SELECT @@transaction_isolation;

账户管理

-- 查看用户信息
USE mysql;
SELECT HOST, USER FROM USER;

-- 查看权限
SHOW GRANTS FOR 'root' @'%';
SHOW GRANTS FOR 'root' @'localhost';
SHOW GRANTS FOR 'root' @'192.168.1.1';

-- 创建用户
USE mysql;
CREATE USER 'service_xxx' @'localhost' IDENTIFIED BY 'mYsql123456_';
FLUSH PRIVILEGES;

-- 修改用户密码
USE mysql;
ALTER USER 'service_xxx' @'localhost' IDENTIFIED BY 'mYsql123456_';
FLUSH PRIVILEGES;

-- 删除用户
USE mysql;
DELETE FROM USER WHERE USER = 'service_xxx' AND HOST = 'localhost';
FLUSH PRIVILEGES;

-- 赋予权限、撤销权限
-- 赋予部分权限（四类 DML 权限 + 存储过程/函数执行权限，一般业务服务已足够），其中的 db_serivce_xxx.* 表示对以 db_serivce_xxx 所有文件操作
GRANT SELECT, DELETE, UPDATE, INSERT, EXECUTE ON `db_serivce_xxx`.* TO 'service_xxx' @'localhost';
FLUSH PRIVILEGES;
-- 赋予所有权限
GRANT ALL PRIVILEGES ON `db_serivce_xxx`.* TO 'service_xxx' @'localhost';
FLUSH PRIVILEGES;
-- 撤销 UPDATE、DELETE权限
REVOKE UPDATE, DELETE ON `db_serivce_xxx`.* FROM 'service_xxx' @'localhost';
-- 撤销所有权限
REVOKE ALL ON `db_serivce_xxx`.* FROM 'service_xxx' @'localhost';

数据库设计

schema名、表名、字段名

表设计

数据库三范式

目前关系数据库有六种范式，理论上，一般而言应当实现第三范式，实际上根据业务考量不会严格实现。

  1. 第一范式，1NF

    数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项，一列仅仅表示一个不可分割的属性

  2. 第二范式，2NF

    在第一范式的基础上，非码属性必须完全依赖于候选码，第二范式要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。

  3. 第三范式，3NF

    在第二范式的基础上，任何非主属性不依赖与其他非主属性

字段设计

主键设计

无意义业务无关ID

无意义的意思其实就是 ID 中不应该包含任何与具体场景或者业务相关的内容，包含这些内容并不是不可以，只是一旦出现这些内容，要么 ID 重复的可能性会增加，这很可能对我们的业务逻辑造成比较严重的影响，以我们的身份证号为例，它的 18 位数字（或符号）大多都是有意义的。

这 18 位数字中的前 6 位表示的是地区，也就是省份、城市和区县，随后的 8 位表示的是出生年月日，接下来的 3 位才同时表示 ID 和性别，最后 1 位用于做校验码防止出现身份证号输错的情况。用上述图中的黄色部分中有一半的数字是用来表示出生的男性，另一半表示出生的女性，所以如果同一个地区的同一天，同时出生了 501 位男性或者女性就会导致潜在的重复问题。

上面谈到的问题其实也是我们在各种业务场景中经常能够遇到的问题，18 位的数字中真正用于表示序列的 ID 其实只有 1000 的一半，如果 18 位数都是无意义的，那它们可以表示 10 亿亿个人，但是一旦在 ID 中引入了业务上的具体信息，就增加了冲突的可能性。

IP 地址字段设计

  1. 使用字符串，纯存储和展示，不太适合搜索
  2. 使用无符号整形，支持范围查询，INET_ATON()、INET_NTOA()、INET6_ATON()、INET6_NTOA()

手机号字段设计

使用字符串而不是int，国际区号（例如，+86）需要存储

单个字段多种状态设计

爱好

前端处理

1,2,3 动漫小说电影

后端处理

表存储，二进制或字符串

 动漫、小说和写博客   select * from xxx where interest like '_11__1'
 动漫、小说和写博客   select * from xxx where interest &38
 
 无法走索引，如果状态较多，无法枚举

 visible_type，可见类型
 0b000 0 不展示
 0b001 1 对所有人展示
 0b010 2 仅对持仓者展示
 0b011 3 --
 0b100 4 仅对内展示
 0b101 5 --
 0b110 6 对持仓者和对内展示
 0b111 7 --
 
 通常状态不多，会有因为某些状态不会采用，枚举而使用索引

数据库优化

https://www.cnblogs.com/JasonCeng/p/14199298.html

SQL 语句优化

SELECT 语句优化

行构造函数表达式优化

行构造函数表达式允许同时比较多个值。

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() 表达式。请参阅 行构造函数表达式的范围优化。

WHERE 子句优化

https://dev.mysql.com/doc/refman/8.4/en/where-optimization.html

范围优化

range 基于单个索引检索数据，其检索索引值落在一个或多个索引值间隔内的表行子集。

单部分索引的范围访问方法

核心逻辑是通过索引的排序规则，将符合条件的索引行限制在 “键间隔” 内，最终实现高效的行检索。

单部分索引的范围条件定义：

  1. 对于 BTREE 和 HASH 索引，当使用 =、<=>、IN()、IS NULL 或 IS NOT NULL 运算符时，将键部分与常量值进行比较是一个范围条件。
  2. 对于 BTREE 索引，当使用 >、<、>=、<=、BETWEEN、!= 或 <> 运算符，或者如果 LIKE 的参数是一个不以通配符开头的常量字符串，则将键部分与常量值进行比较也是一个范围条件。
  3. 对于所有索引类型，多个使用 OR 或 AND 组合的范围条件构成一个范围条件。

上述描述中的“常量值”是指以下之一：

多部分索引的范围访问方法

核心逻辑是通过索引的排序规则，将符合条件的索引行限制在 “键元组间隔” 内，最终实现高效的行检索。不同索引达成范围访问的条件不一样：

一、核心概念：键元组与间隔

多部分索引的索引项是 “键元组”（由索引的多个部分组成，如 key1(key_part1, key_part2, key_part3) 的索引项是 (key_part1值, key_part2值, key_part3值)），且所有键元组按索引定义的顺序（如 key_part1 → key_part2 → key_part3）排序。
“范围条件” 的本质，就是通过查询条件锁定一个或多个连续的键元组区间—— 只有落在区间内的键元组，才会被范围访问方法检索。

典型示例

以复合索引 key1(key_part1, key_part2, key_part3) 为例，其键元组按顺序排列如下（部分数据）：

序号 key_part1 key_part2 key_part3
1 NULL 1 ‘abc’
2 NULL 1 ‘xyz’ “xyz”
3 NULL 2 ‘foo’ “呜呜”
4 1 1 ‘abc’
5 1 1 ‘xyz’ “xyz”
6 1 2 ‘abc’
7 2 1 ‘aaa’ ‘啊啊’

若查询条件为 key_part1 = 1，则对应的键元组间隔为：
(1, -inf, -inf) ≤ (key_part1, key_part2, key_part3) < (1, +inf, +inf)
（-inf 表示 “最小值”，+inf 表示 “最大值”）
该间隔覆盖序号 4、5、6 的键元组，范围访问会直接检索这部分数据，无需扫描全部索引。

反之，若条件为 key_part3 = 'abc'，由于索引排序以 key_part1 为先，key_part3 = 'abc' 对应的键元组（序号 1、4、6）不连续，无法形成 “单个间隔”，因此无法使用范围访问方法。

HASH

适用条件：

  1. 必须为全索引列
  2. 必须为常量比较，运算符仅支持等式类， =、<=>、IS NULL
  3. 必须为 AND 连接
    key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;

示例：

-- HASH 索引 (a, b, c)
a = 1 AND b IS NULL AND c = 'foo' -- 符合范围访问使用条件
a = 1 AND c = 'foo' -- 缺少一个索引列，不满足范围访问条件
a > 1 AND b = 2 -- 非等值比较，不满足范围访问条件
BTREE

适用条件：

  1. 最左前缀索引列

  2. 必须为常量比较，运算符支持：

  3. 支持 AND 和 OR 组合，按逻辑进行区间合并。

    • OR 对应 “区间的并集”：多个条件用 OR 连接时，最终生效的索引区间是这些条件各自区间的 “合并范围”（只要满足任意一个区间即可）。
    • AND 对应 “区间的交集”：多个条件用 AND 连接时，最终生效的索引区间是这些条件各自区间的 “重叠范围”（必须同时满足所有区间）。
纯 AND 交集场景

优化器会按索引定义的顺序（如 key_part1 → key_part2 → key_part3）逐部分匹配条件，规则是：

以复合索引 (key_part1, key_part2, key_part3) 为例，条件为 key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10：

  1. key_part1 = 'foo'：等式运算符，继续匹配下一部分；
  2. key_part2 >= 10：范围运算符，停止后续匹配；
  3. key_part3 > 10：不参与 “间隔构建”。

最终形成的间隔为：
('foo', 10, -inf) < (key_part1, key_part2, key_part3) < ('foo', +inf, +inf)

⚠️ 注意：该间隔可能包含不满足原始条件的行（如 ('foo', 11, 0) 满足间隔，但不满足 key_part3 > 10），因此 MySQL 会在范围扫描后，额外检查剩余条件以过滤无效行。

以组合索引 (key_part1, key_part2, key_part3) 和条件 key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10 为例：

带 OR 并集场景

以两部分索引（假设为 (key_part1, key_part2)）和条件 (key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5) 为例，直观理解区间生成：

（3）OR 条件的间隔处理

若条件用 OR 组合，会形成多个间隔的 “并集”；用 AND 组合则形成多个间隔的 “交集”。

示例：对两部分索引 (key_part1, key_part2)，条件 (key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)：

多值比较的相等范围优化

该优化的本质是 “在‘估算准确性’和‘计算效率’之间找平衡”：通过区分唯一 / 非唯一索引场景、提供两种估算方式及切换开关，让优化器在 “小值列表”（用潜水保精准）和 “大值列表”（用统计保速度）场景下，都能高效选择执行计划；

col_name为索引列，相等范围比较条件指，col_name为多个值中任何一个。

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

优化器需要估算每个相等范围匹配的行数，进而计算成本，最终判断是否采用索引或者采用何种索引策略。

  1. 若col_name列上为唯一索引，则最多匹配到一行
  2. 若col_name列上为非唯一索引，优化器可以通过索引下潜或利用索引统计信息来估计每个范围的行数。

索引下潜（Index Dives）

索引统计信息（Index Statistics）

eq_range_index_dive_limit

SHOW VARIABLES LIKE "eq_range_index_dive_limit";

MySQL 通过系统变量 eq_range_index_dive_limit 控制优化器何时从 “索引潜水” 切换到 “统计信息”，核心逻辑是 “根据 IN/OR 中的值数量决定”，默认为 200：

[!IMPORTANT]

MySQL 自 8.4 及以后引入若干跳过索引下潜的场景（为减少索引下潜开销）：

  1. 仅查询单个表（非多表关联）；
  2. 显式使用 FORCE INDEX 强制指定单个非唯一索引（非全文索引）；
  3. 无 subquery（子查询）；
  4. 无 DISTINCT、GROUP BY、ORDER BY 子句。

跳过扫描范围访问方法

Skip Scan 是 MySQL 中针对复合索引的一种优化访问策略，核心目标是解决 “复合索引前缀列无过滤条件时，无法直接用范围扫描” 的问题，通过 “拆分扫描范围” 提升查询效率，避免全索引扫描的性能损耗。

问题场景

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

复合索引使用未遵循最左前缀匹配，无法使用常规范围扫描。

核心原理

跳过前缀 + 子范围扫描

核心逻辑是先拆分复合索引的前缀列（如 f1）的不同值，再对每个前缀值对应的后续列（如 f2）做范围扫描，步骤如下（以示例为例）：

  1. 获取前缀列的 distinct 值：先找到 f1 的所有不同值（示例中是 1 和 2）；
  2. 构造子范围条件：对每个 f1 值，结合 f2 的范围条件，生成子范围（如 f1=1 AND f2>40、f1=2 AND f2>40）；
  3. 执行子范围扫描：针对每个子范围单独做范围扫描，直接过滤出符合条件的行；
  4. 合并结果：将所有子范围的扫描结果合并，得到最终结果。

本质是 “用多个小范围扫描替代全索引扫描”，跳过前缀列不同值对应的无效行（如 f1=1 中 f2≤40 的行）。

三、适用条件（必须全部满足）

Skip Scan 是 MySQL 针对 “复合索引 + 覆盖索引 + 单表范围查询” 的专项优化，核心是通过 “分组跳过无效行” 减少访问量，但对索引结构和查询逻辑的限制较多，只有完全匹配上述条件时才会生效。

条件类别 具体要求与解读
1. 复合索引结构要求 索引必须是 多列复合索引，且列的顺序需符合 [A组, B组, C, D组] 格式： - A 组（可选）：A_1~A_k，可空（即索引可无 A 列）； - B 组（必选）：B_1~B_m，非空（必须有至少 1 列）； - C（必选）：单独 1 列，非空（核心范围列）； - D 组（可选）：D_1~D_n，可空（索引可无 D 列）。 例：索引 (a1,a2, b1, c, d1) 符合（A 组：a1/a2，B 组：b1，C：c，D 组：d1）。
2. 查询对象限制 仅支持 单表查询，不支持多表关联（JOIN）场景。
3. 聚合 / 去重限制 查询中不能用 GROUP BY（分组）或 DISTINCT（去重），因为这类操作会改变行的筛选逻辑，与 Skip Scan 的 “范围扫描逻辑” 冲突。
4. 列的引用限制 查询中用到的所有列（如 SELECT 后的列、WHERE 中的条件列）必须都在复合索引中（即 “覆盖索引” 场景），无需回表查原表数据。
5. A 组列的条件限制 若索引有 A 组列（A_1~A_k），则对这些列的查询条件必须是 “等于常量”： - 支持 =（如 a1=1）、IN()（如 a2 IN (2,3)）； - 支持 =（如 a1=1）、IN（）（如 a2 IN （2,3））; - 不支持范围条件（如 a1>1）或变量（如 a1=@x）。
6. 查询条件的逻辑结构 整个 WHERE 条件必须是 “AND 连接的 OR 条件”（即 “合取范式”）： 格式为 (cond1 OR cond2) AND (cond3 OR cond4) AND ...，且每个 cond 是针对单个索引列的条件（如 (b1=2 OR b1=3) AND (c>40)）。
7. C 列的核心条件 必须对 C 列（索引中 B 组后的单独列）设置范围条件： - 支持 >、<、>=、<=、BETWEEN、LIKE（非前缀通配符） 等（如 c>40、c BETWEEN 10 AND 20）； - 支持 >、<、>=、<=、BETWEEN、LIKE（非前缀通配符） 等（如 c>40、c BETWEEN 10 AND 20）; - 这是触发 Skip Scan 的核心 ——C 列的范围条件是 “筛选分组内行” 的依据。
8. D 组列的条件限制 若索引有 D 组列（D_1~D_n），则对这些列的条件需 与 C 列的范围条件用 AND 连接（如 c>40 AND d1=5），不能单独对 D 组列设条件。

五、开关控制

六、核心优势

相比全索引扫描，Skip Scan 减少了 “无需过滤的行” 的访问（如示例中 f2≤40 的行），尤其当前缀列（如 f1）的 distinct 值较少、后续列（如 f2）的范围过滤性强时，性能提升更明显。

行构造函数表达式的范围优化

优化器能够将范围扫描访问方法应用于以下形式的查询：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

以前，要使用范围扫描，必须将查询编写为：

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' ) OR ( col_1 = 'c' AND col_2 = 'd' );

限制范围优化的内存使用

系统变量 range_optimizer_max_mem_size 控制范围优化最大可用内存大小。

SHOW VARIABLES LIKE "range_optimizer_max_mem_size";

要估算处理范围表达式所需的内存量，请遵循以下准则：

  1. 对于如下所示的简单查询，其中范围访问方法有一个候选键，每个与OR组合的谓词大约使用230字节：

    SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;

  2. 类似地，对于如下所示的查询，每个通过AND组合的谓词大约使用125字节：

    SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;

  3. 对于带有 IN（）谓词的查询，IN（）列表中的每个文字值都算作一个与 OR 结合的谓词。如果有两个 IN（）列表，则与 OR 结合的谓词数量是每个列表中文字值数量的乘积。因此，在前面的情况下，与 OR 结合的谓词数量为 M×N。：

    SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);

索引合并优化

索引合并访问方法使用多个 range 扫描来检索行，并将它们的结果合并到一个结果中。这种访问方法只合并来自单个表的索引扫描，而不是跨多个表的扫描。合并可以生成其基础扫描的并集、交集或并集的交集。

在 EXPLAIN 输出中，索引合并方法在 type 列中显示为 index_merge。在这种情况下，key 列包含使用的索引列表，而 key_len 包含这些索引的最长键部分列表。

索引合并访问方法具有几种算法，这些算法显示在 EXPLAIN 输出的 Extra 字段中

[!NOTICE]

索引合并优化算法有以下已知限制

  • 如果您的查询具有一个复杂的 WHERE 子句，包含深层 AND/OR 嵌套，而 MySQL 未选择最佳计划，请尝试使用以下恒等变换来分配项

    (x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

  • 索引合并不适用于全文索引。

影响索引合并优化

索引合并的使用受 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 组合的不同键上的多个范围条件，并且每个条件都是以下之一时适用：

  1. 在 InnoDB 表的主键上的任何范围条件。

  2. 这种形式的 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 组合的不同键上的多个范围条件时，该算法适用，并且每个条件都是以下之一

示例

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查询优化器的一种策略，它并不总是被使用。优化器会基于查询的成本估算来选择最佳的执行计划

如何判断是否使用了 Index Merge Optimization

使用 EXPLAIN 语句可以查看查询的执行计划，如果 type 列显示为 index_merge，则说明 MySQL 使用了 Index Merge Optimization。

哈希连接优化，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设置得更高，而无需担心小查询分配非常大量的内存。

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)
  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

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);

索引条件下推，Index Condition Pushdown

针对二级联合索引的优化

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

使用ICP的情况下，查询过程：

索引条件下推的具体实践

理论比较抽象，我们来上一个实践。

使用一张用户表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 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

索引条件下推使用条件

索引下推的目的是为了减少回表次数，也就是要减少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";

111

表连接优化

包含所有表连接方面的优化

嵌套循环连接算法

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 算法一次将一行从外部循环传递到内部循环，因此它通常会多次读取在内部循环中处理的表。

嵌套连接优化

表连接允许嵌套。

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具有以下状态：

在这种情况下，第一个表达式返回一个结果集，其中包括行(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条件中的谓词引起，则禁止在相同嵌套连接中从一个内部表通过键访问另一个内部表。

常量折叠优化（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位，则折叠。

限制

常量折叠优化无法适用如下场景：

  1. 使用 BETWEEN 和 IN 的比较
  2. BIT 列或者日期时间类型的比较
  3. 在预处理语句的优化阶段才能执行该优化，预处理语句的准备阶段不行，因为准备阶段常量值还未知。

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);

11

1.简介
限流算法