mysql - 在不同的行上选择满足不同条件的值？

Question

这是一个非常基本的查询，我无法弄清楚......

假设我有一个像这样的两列表格:

userid  |  roleid
--------|--------
   1    |    1
   1    |    2
   1    |    3
   2    |    1

我想获取具有 roleids 1、2 和 3 的所有不同用户 ID。使用上面的示例，我想要返回的唯一结果是 userid 1。如何做我这样做？

Answer 1

好吧，我对此投了反对票，所以我决定测试一下:

CREATE TABLE userrole (
  userid INT,
  roleid INT,
  PRIMARY KEY (userid, roleid)
);

CREATE INDEX ON userrole (roleid);

运行这个:

<?php
ini_set('max_execution_time', 120); // takes over a minute to insert 500k+ records 

$start = microtime(true);

echo "<pre>\n";
mysql_connect('localhost', 'scratch', 'scratch');
if (mysql_error()) {
    echo "Connect error: " . mysql_error() . "\n";
}
mysql_select_db('scratch');
if (mysql_error()) {
    echo "Selct DB error: " . mysql_error() . "\n";
}

$users = 200000;
$count = 0;
for ($i=1; $i<=$users; $i++) {
    $roles = rand(1, 4);
    $available = range(1, 5);
    for ($j=0; $j<$roles; $j++) {
        $extract = array_splice($available, rand(0, sizeof($available)-1), 1);
        $id = $extract[0];
        query("INSERT INTO userrole (userid, roleid) VALUES ($i, $id)");
        $count++;
    }
}

$stop = microtime(true);
$duration = $stop - $start;
$insert = $duration / $count;

echo "$count users added.\n";
echo "Program ran for $duration seconds.\n";
echo "Insert time $insert seconds.\n";
echo "</pre>\n";

function query($str) {
    mysql_query($str);
    if (mysql_error()) {
        echo "$str: " . mysql_error() . "\n";
    }
}
?>

输出:

499872 users added.
Program ran for 56.5513510704 seconds.
Insert time 0.000113131663847 seconds.

这会添加 500,000 个随机用户角色组合，大约有 25,000 个符合所选条件。

第一个查询:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

查询时间:0.312s

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

查询时间:0.016s

没错。我建议的连接版本比聚合版本快二十倍。

抱歉，但我这样做是为了在现实世界中生活和工作，在现实世界中，我们测试 SQL，结果不言而喻。

原因应该很清楚了。聚合查询的成本将随着表的大小而变化。每行都通过 HAVING 子句进行处理、聚合和过滤(或不进行处理)。连接版本将(使用索引)根据给定角色选择用户子集，然后对照第二个角色检查该子集，最后对照第三个角色检查该子集。每个selection (以 relational algebra 术语来说)适用于越来越小的子集。由此你可以得出结论:

连接版本的性能变得更好，匹配发生率更低。

如果只有 500 个用户(在上面的 50 万个示例中)具有三个指定角色，则加入版本将显着加快。聚合版本不会(任何性能改进都是传输 500 个用户而不是 25,000 个用户的结果，而加入版本显然也得到了这一点)。

我也很好奇真正的数据库(即 Oracle)将如何处理这个问题。因此，我基本上在 Oracle XE 上重复了相同的练习(与上一个示例中的 MySQL 在同一台 Windows XP 桌面计算机上运行)，结果几乎相同。

连接似乎不受欢迎，但正如我所演示的，聚合查询可能会慢一个数量级。

更新:经过一些extensive testing ，情况比较复杂，答案将取决于您的数据、数据库和其他因素。这个故事的寓意是测试、测试、测试。