用WHERE子句筛选结果集还是用应用程序代码更好？

Question

用WHERE子句筛选结果集还是用应用程序代码更好？

phpsqlmysql

11

好的，以下是问题的简单抽象：

有两个变量(male_users和female_users)，分别用于存储两组用户即男性和女性

第一种方法是使用两个查询来选择它们:

select * from users where gender = 'male' 然后将结果存储在 male_users 中

select * from users where gender = 'female' 然后将结果存储在 female_users 中

另一种方法是仅运行一个查询：

使用 'select * from users' 查询语句，然后在程序中循环遍历结果集以过滤男性用户， php 代码片段应该类似于此：

$result = mysql_query('select * from users');

while (($row=mysql_fetch_assoc(result)) != null) {
  if ($row['gender'] == 'male'){// add to male_users}
  else if ($row['gender'] == 'female'){// add to female_users}
}

哪个方法更有效率，被认为是更好的方法？

这只是问题的简单说明。实际项目可能有更大的表格需要查询和更多的过滤选项。

谢谢！

- Yang

3个回答

4

我认为没有必要让数据库执行WHERE子句的额外工作。既然你确实想要所有记录，那么你将不得不去获取它们。如果你从表中选择一条记录，它将按表顺序检索它们，并且你可以自己分区。如果你选择WHERE male和WHERE female，你将不得不为每个操作命中一个索引，并且你将失去一些数据局部性。

例如，如果您的磁盘上的记录是交替的男女，并且数据集比内存大得多，则如果您执行两个单独的查询，您可能需要两次读取整个数据库，而同时选择则是单个表扫描。

编辑：由于我被downmodded到了无人问津，所以我决定实际运行测试。我生成了一个表

CREATE TEMPORARY TABLE gender_test (some_data DOUBLE PRECISION, gender CHARACTER VARYING(20));

我生成了一些随机数据，

select gender, count(*) from gender_test group by gender; gender | count --------+---------- female | 12603133 male | 10465539 (2 rows)

首先，让我们在没有索引的情况下运行这些测试，这种情况下我非常确定我是正确的...

EXPLAIN ANALYSE SELECT * FROM gender_test WHERE gender='male'; QUERY PLAN Seq Scan on gender_test (cost=0.00..468402.00 rows=96519 width=66) (actual time=0.030..4595.367 rows=10465539 loops=1) Filter: ((gender)::text = 'male'::text) Total runtime: 5150.263 ms

EXPLAIN ANALYSE SELECT * FROM gender_test WHERE gender='female'; QUERY PLAN Seq Scan on gender_test (cost=0.00..468402.00 rows=96519 width=66) (actual time=0.029..4751.219 rows=12603133 loops=1) Filter: ((gender)::text = 'female'::text) Total runtime: 5418.891 ms

EXPLAIN ANALYSE SELECT * FROM gender_test; QUERY PLAN Seq Scan on gender_test (cost=0.00..420142.40 rows=19303840 width=66) (actual time=0.021..3326.164 rows=23068672 loops=1) Total runtime: 4543.393 ms

有趣的是，没有筛选条件地扫描表格获取数据确实更快！实际上，快了两倍以上！（5150 + 5418 > 4543）就像我预测的那样！:-p 现在，让我们创建一个索引并看看它是否改变了结果...

CREATE INDEX test_index ON gender_test(gender);

现在重新运行同样的查询...

test=> EXPLAIN ANALYSE SELECT FROM gender_test WHERE gender='male';

查询计划 --------------------------- Bitmap Heap Scan on gender_test (cost=2164.69..195922.27 rows=115343 width=66) (actual time=2008.877..4388.348 rows=10465539 loops=1) Recheck Cond: ((gender)::text = 'male'::text) -> Bitmap Index Scan on test_index (cost=0.00..2135.85 rows=115343 width=0) (actual time=2006.047..2006.047 rows=10465539 loops=1) Index Cond: ((gender)::text = 'male'::text) Total runtime: 4941.64 ms

test => EXPLAIN ANALYSE SELECT * FROM gender_test WHERE gender='female';

查询计划 --------------------------- Bitmap Heap Scan on gender_test (cost=2164.69..195922.27 rows=115343 width=66) (actual time=1915.385..4269.933 rows=12603133 loops=1) Recheck Cond: ((gender)::text = 'female'::text) -> Bitmap Index Scan on test_index (cost=0.00..2135.85 rows=115343 width=0) (actual time=1912.587..1912.587 rows=12603133 loops=1) Index Cond: ((gender)::text = 'female'::text) Total runtime: 4931.555 ms (5 rows)

test=> EXPLAIN ANALYSE SELECT * FROM gender_test;

查询计划 --------------------------- Seq Scan on gender_test (cost=0.00..457790.72 rows=23068672 width=66) (actual time=0.021..3304.836 rows=23068672 loops=1) Total runtime: 4523.754 ms

有趣....一次性扫描整个表仍然比索引快两倍！（4941 + 4931 vs 4523）

注意：这种方法在科学上有各种不足之处。我使用了16GB的RAM，因此整个数据集都可以放入内存中。Postgres没有配置使用那么多的内存，但磁盘缓存仍然有所帮助...我假设（但不想试一试）一旦到达磁盘，效果只会更差。我只尝试了默认的btree Postgres索引。我假设PHP分区不需要时间 - 这不是真的，但可能是一个相当合理的近似值。

所有测试都在Mac Pro 8路2.66 Xeon 16GB RAID-0 7200rpm上运行

此外，这个数据集有2600万行，可能比大多数人关心的要大一些...

显然，原始速度并不是您关心的唯一事情。在许多应用程序中，您更关心单独获取它们的逻辑“正确性”。但是，当老板说“我们需要加快速度”时，这将（显然）使您的速度提高2倍。OP明确询问效率。满意吗？

- Steven Schlansker

1

你的回答只考虑了从数据库角度优化效率的问题。在代码中多次遍历整个结果集比在数据库上进行多次完整表扫描要更耗费工作量。数据库被设计成更适合处理这种类型的活动并提高其效率。 - eric.christensen

2

@hobodave - 如果数据集足够小，那么任何操作都可以。你可以对十五个表进行笛卡尔积，也不会有什么问题。通常只有在处理大型数据集时才会涉及性能问题...至于内存适配的问题-完全有可能您没有将所有内存分配给数据库缓存，或者您正在通过流式传输结果。我并不是说您应该总是考虑这些事情，只是如果您要问一个性能问题，那么考虑一下在处理大型数据集时会发生什么是值得的。就像O(n)符号一样... - Steven Schlansker

2

因为你无论如何都要循环遍历它们，将它们加载到你所使用的任何数据结构中？示例代码已经有一个 while 循环来从 SQL 结果集中复制结果。添加决策以确定要将其放入哪个列表中，可能相比之后对其进行的任何处理来说都微不足道。 - Steven Schlansker

1

这只是说明了，如果你真的关心事物运行的速度，那么计算一下数字总是值得的。 - Steven Schlansker

1

如果未来添加了“不想透露”性别选项，但仍希望仅检索男性和女性行，您会怎么做？回到应用程序并将业务逻辑处理切换回数据库？速度优势还是面向未来的设计，这由开发人员决定。 - Nirmal

显示剩余18条评论

-1

如果你有一百万个用户，你更喜欢：

从数据库中获取一百万个用户？
还是只从数据库中获取 500k 个用户？

我想你会回答说你更喜欢只获取一半的用户；-) 而且，根据条件的不同，如果更复杂，可能会更少。

基本上，获取更少的数据意味着：

使用的网络流量更少“无用”（即获取将立即被丢弃的数据）
使用的内存更少，特别是在 PHP 服务器上
可能会减少 MySQL 服务器上的磁盘访问——因为需要从磁盘中获取的数据更少

在一般情况下，我们尽量避免获取比必要的更多的数据；即我们将过滤器放在数据库端。

当然，这意味着你必须考虑你将放置在数据库表上的索引：它们必须适合你将执行的查询的需求。

- Pascal MARTIN

请注意，您不会获取更少的数据 - 他的问题明确要求获取两组数据；无论是一次性获取还是分两次获取。 - Steven Schlansker

话虽如此，我完全同意在许多方面获取更少的数据是更好的方式；-) - Steven Schlansker

-1 问者想要所有的数据 - 他的问题并没有询问如果他只想要男性会更快。 - Elemental

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- hobodave · Accepted Answer

任何应用程序的一般规则是让数据库做它擅长的事情：过滤、排序和连接。

将查询分离为它们自己的函数或类方法：

$men = $foo->fetchMaleUsers();
$women = $foo->fetchFemaleUsers();

更新

我使用MySQL（实际问题中使用的数据库）模仿了Steven关于全表扫描查询性能比两个单独索引查询性能好两倍的PostgreSQL演示：

模式

CREATE TABLE `gender_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `gender` enum('male','female') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26017396 DEFAULT CHARSET=utf8

我将性别类型更改为非VARCHAR（20），因为这对于此列的目的更为现实，我还提供了一个主键，就像表格上所期望的那样，而不是任意的DOUBLE值。

未索引的结果

mysql> select sql_no_cache * from gender_test WHERE gender = 'male';

12995993 rows in set (31.72 sec)

mysql> select sql_no_cache * from gender_test WHERE gender = 'female';

13004007 rows in set (31.52 sec)

mysql> select sql_no_cache * from gender_test;

26000000 rows in set (32.95 sec)

我相信这不需要解释。

索引结果

ALTER TABLE gender_test ADD INDEX (gender);

...

mysql> select sql_no_cache * from gender_test WHERE gender = 'male';

12995993 rows in set (15.97 sec)

mysql> select sql_no_cache * from gender_test WHERE gender = 'female';

13004007 rows in set (15.65 sec)

mysql> select sql_no_cache * from gender_test;

26000000 rows in set (27.80 sec)

这里显示的结果与Steven的数据截然不同。索引查询执行速度几乎是全表扫描的两倍。这是使用常识列定义正确索引化的表。我完全不了解PostgreSQL，但在Steven的示例中必须存在一些重大的配置错误才能不显示类似的结果。

考虑到PostgreSQL比MySQL做得更好，或者至少和MySQL一样出色，我敢说如果正确使用，PostgreSQL会展示类似的性能。

此外，请注意，在同一台机器上，一个过于简化的for循环执行5200万次比较需要额外7.3秒才能执行。

<?php
$N = 52000000;
for($i = 0; $i < $N; $i++) {
    if (true == true) {
    }
}

我认为，考虑到这些数据，更好的方法是相当明显的。