使用WHERE子句或使用应用程序代码过滤结果集是否更好？

小编典典

使用WHERE子句或使用应用程序代码过滤结果集是否更好？

sql

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

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

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

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

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

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

提前致谢！

阅读 210

2021-03-17

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

此处显示的结果与史蒂文的数据完全不同。索引查询的执行速度几乎
是全表扫描的两倍。这来自使用常识列定义的正确索引的表。我一点都不了解PostgreSQL，但是在Steven的示例中必须有一些重大的配置错误，才能显示出相似的结果。

鉴于PostgreSQL在做事上优于MySQL或至少与MySQL一样出色的声誉，我敢说如果正确使用PostgreSql也会表现出类似的性能。

还要注意，在同一台机器上，过度简化的for循环执行5200万次比较需要执行 额外的7.3秒 。

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

我认为，鉴于这些数据，哪种更好的方法是显而易见的。

2021-03-17