php - 使用 WHERE 子句或应用程序代码过滤结果集哪个更好？

Question

好的，这是问题的简单抽象:

2 个变量(male_users 和 female_users)存储 2 组用户，即男性和女性

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

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

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

1. 另一种方法是只运行一个查询:

'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}
}

哪种方法更有效并被认为是更好的方法？

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

提前致谢!

Answer 1

任何应用程序的经验法则是让数据库做它擅长的事情:过滤、排序和连接。

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

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

更新

我在 Steven 的 PostgreSQL 演示中演示了一个全表扫描查询，其性能是两个单独的索引查询的两倍，并使用 MySQL(在实际问题中使用)模仿它:

架构

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

我认为根据这些数据，什么是更好的方法是相当明显的。