代码之家 › 专栏 › 技术社区 › ztech

Mysql IN()vs UNION ALL性能

database sql mysql

ztech · 技术社区 · 6 年前

查询1

SELECT id FROM users WHERE status IN(1, 6, 11, 13);

(SELECT id FROM users WHERE status = 1)
UNION ALL
(SELECT id FROM users WHERE status = 6)
UNION ALL
(SELECT id FROM users WHERE status = 11)
UNION ALL
(SELECT id FROM users WHERE status = 13)

我知道问题1对人类来说更容易理解。

我很感兴趣的是讨论它们的性能特征如何相同或不同。您可以假设有一个关于users.status的索引

3 回复 | 直到 6 年前

Gordon Linoff 6 年前

有了一个指数,从性能的角度来看,两者应该基本上是等价的。索引可用时的基本过程是索引查找以获取匹配的行,然后获取数据页。第一个在一个查询单元中执行四次。第二个对一个id执行四个查询单元。

如果没有索引,第一个应该更快——如果每次都需要从磁盘读取表,则速度应该是原来的四倍。或者,如果表适合可用内存,并且后续扫描使用的是热数据页缓存,则以增量方式加快速度。

MichaÅ Turczyn 6 年前

在第二个查询中,分别查询表。对于每个 select 它必须读一张桌子。在第一种方法中,它必须读一次表。

如果没有索引,每次读取的表都必须是全表扫描,这两种方法之间的性能差异将更大。

因此,第一次查询将更快。

slaakso 6 年前

第一个会更快。您可以通过分析看到:

mysql> SET profiling = 1;
mysql> select * from bigtable where id in (200, 900, 22000, 88888);
mysql> select * from bigtable where id =200
-> union all
-> select * from bigtable where id =900
-> union all
-> select * from bigtable where id =22000
-> union all
-> select * from bigtable where id =88888;
mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000046 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000013 |
| init                 | 0.000024 |
| System lock          | 0.000008 |
| optimizing           | 0.000009 |
| statistics           | 0.000031 |
| preparing            | 0.000010 |
| executing            | 0.000003 |
| Sending data         | 0.000072 |
| end                  | 0.000004 |
| query end            | 0.000006 |
| closing tables       | 0.000006 |
| freeing items        | 0.000013 |
| cleaning up          | 0.000008 |
+----------------------+----------+
15 rows in set, 1 warning (0,00 sec)

mysql> show profile for query 2;
+----------------------+----------+                                                                                                                                 
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000054 |
| checking permissions | 0.000006 |
| checking permissions | 0.000002 |
| checking permissions | 0.000003 |
| checking permissions | 0.000004 |
| Opening tables       | 0.000112 |
| init                 | 0.000058 |
| System lock          | 0.000009 |
| optimizing           | 0.000008 |
| statistics           | 0.000032 |
| preparing            | 0.000009 |
| optimizing           | 0.000005 |
| statistics           | 0.000017 |
| preparing            | 0.000005 |
| optimizing           | 0.000005 |
| statistics           | 0.000014 |
| preparing            | 0.000005 |
| optimizing           | 0.000005 |
| statistics           | 0.000014 |
| preparing            | 0.000006 |
| executing            | 0.000003 |
| Sending data         | 0.000018 |
| executing            | 0.000003 |
| Sending data         | 0.000008 |
| executing            | 0.000003 |
| Sending data         | 0.000007 |
| executing            | 0.000003 |
| Sending data         | 0.000007 |
| end                  | 0.000004 |
| query end            | 0.000007 |
| removing tmp table   | 0.000009 |
| query end            | 0.000002 |
| closing tables       | 0.000008 |
| freeing items        | 0.000020 |
| cleaning up          | 0.000011 |
+----------------------+----------+
35 rows in set, 1 warning (0,00 sec)

因此,即使实际执行速度很快(因为查询使用索引),union中单独查询的开销也是存在的。