MySQL的Union和OR查询

1. 创建表和索引

根据表结构，为 phone 和 email 列创建单列索引：

CREATE INDEX idx_phone ON tb_user(phone);
CREATE INDEX idx_email ON tb_user(email);

2. 编写 UNION 查询

编写一个 UNION 查询，分别基于 phone 和 email 列进行查询：

SELECT * FROM tb_user WHERE phone = '12345678901'
UNION
SELECT * FROM tb_user WHERE email = 'example@example.com';

3. 使用 EXPLAIN 分析查询

使用 EXPLAIN 命令查看查询的执行计划，判断是否使用了索引合并功能：

EXPLAIN
SELECT * FROM tb_user WHERE phone = '12345678901'
UNION
SELECT * FROM tb_user WHERE email = 'example@example.com';

4. 分析 EXPLAIN 结果

在 EXPLAIN 的输出中，重点关注以下字段：

• type：访问类型，ref 表示使用了索引。
• key：使用的索引名称。
• Extra：额外信息，如果出现 Using union 或 Using index merge，则表示使用了索引合并。

可能的结果分析：

1. 仅使用单列索引：

   • 每个子查询的 type 为 ref，key 为 idx_phone 或 idx_email。
   • Extra 中没有 Using index merge。
   • 说明每个子查询分别使用了 phone 和 email 的单列索引。

2. 使用了索引合并：

   • type 为 index_merge。
   • key 可能显示 idx_phone,idx_email。
   • Extra 中显示 Using union(idx_phone,idx_email)。
   • 说明 MySQL 使用了索引合并功能。

5. 验证索引合并

如果 EXPLAIN 结果显示没有使用索引合并，可以尝试强制使用索引合并：

SELECT * FROM tb_user WHERE phone = '12345678901' OR email = 'example@example.com';

然后再次使用 EXPLAIN 分析：

EXPLAIN
SELECT * FROM tb_user WHERE phone = '12345678901' OR email = 'example@example.com';

观察是否使用了索引合并。
type:index_merge

总结

• 使用 EXPLAIN 分析查询计划。
• 如果 EXPLAIN 的 Extra 字段显示 Using index merge，则使用了索引合并功能。
• 如果每个子查询分别使用了单列索引，则说明没有使用索引合并。

通过以上方法，可以测试并判断 UNION 操作是否使用了索引合并功能。

实操之后

mysql> EXPLAIN
-> SELECT * FROM tb_user WHERE phone = '12345678901'
-> UNION
-> SELECT * FROM tb_user WHERE email = 'example@example.com';
+----+--------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | tb_user | NULL | ref | idx_phone | idx_phone | 35 | const | 1 | 100.00 | NULL |
| 2 | UNION | tb_user | NULL | ref | idx_email | idx_email | 303 | const | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------+

从 EXPLAIN 的结果可以看出，UNION 操作和 OR 条件的执行计划有所不同。以下是详细解释：

1. UNION 操作的分析

UNION 操作的执行计划分为三个部分：

• 第一部分：SELECT * FROM tb_user WHERE phone = '12345678901'
  • type: ref，表示使用了索引。
  • key: idx_phone，表示使用了 phone 列的单列索引。
  • Extra: 无额外信息。
• 第二部分：SELECT * FROM tb_user WHERE email = 'example@example.com'
  • type: ref，表示使用了索引。
  • key: idx_email，表示使用了 email 列的单列索引。
  • Extra: 无额外信息。
• 第三部分：UNION RESULT
  • type: ALL，表示需要扫描所有行。
  • Extra: Using temporary，表示使用了临时表来存储 UNION 的结果。

为什么使用临时表？

UNION 操作会将两个查询的结果集合并，并自动去除重复的行。为了实现这一点，MySQL 会将两个查询的结果存储在一个临时表中，然后对临时表进行去重操作。因此，UNION 操作通常会使用临时表。

2. OR 条件的分析

对于 OR 条件，MySQL 可能会使用索引合并（Index Merge）功能。例如：

EXPLAIN
SELECT * FROM tb_user WHERE phone = '12345678901' OR email = 'example@example.com';

执行计划可能如下：

• type: index_merge，表示使用了索引合并。
• key: idx_phone,idx_email，表示同时使用了 phone 和 email 列的单列索引。
• Extra: Using union(idx_phone,idx_email); Using where，表示 MySQL 使用了索引合并功能，并通过 WHERE 条件过滤结果。

为什么使用索引合并？

OR 条件需要同时满足两个条件中的任意一个。MySQL 会分别使用 phone 和 email 列的单列索引，然后将结果合并（Using union），最后通过 WHERE 条件过滤结果。

3. UNION 和 OR 的区别

总结

• UNION：
  • 分别使用单列索引。
  • 使用临时表存储结果并去重。
  • 适合需要合并并去重的场景。
• OR：
  • 可能使用索引合并（Using union）。
  • 不需要临时表，性能通常更好。
  • 适合需要同时满足多个条件的场景。

根据实际需求选择合适的查询方式。如果需要去重，使用 UNION；如果不需要去重，使用 OR。