好吧，我对此投了反对票，所以我决定测试一下：
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";
}
}
?>
Output:
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条款。连接版本将（使用索引）根据给定角色选择用户子集，然后对照第二个角色检查该子集，最后对照第三个角色检查该子集。每个选择 http://en.wikipedia.org/wiki/Selection_(relational_algebra) (in 关系代数 http://en.wikipedia.org/wiki/Relational_algebra术语）适用于越来越小的子集。由此你可以得出结论：
连接版本的性能变得更好，匹配发生率更低。
如果只有 500 个用户（在上面的 50 万个示例中）具有三个指定的角色，则加入版本将显着加快。聚合版本不会（并且任何性能改进都是传输 500 个用户而不是 25k 个用户的结果，加入版本显然也得到了 25k）。
我也很好奇真正的数据库（即 Oracle）将如何处理这个问题。因此，我基本上在 Oracle XE 上重复了相同的练习（与上一个示例中的 MySQL 在同一台 Windows XP 桌面计算机上运行），结果几乎相同。
连接似乎不受欢迎，但正如我所演示的，聚合查询可能会慢一个数量级。
Update:经过一些广泛的测试后，情况变得更加复杂，答案将取决于您的数据、数据库和其他因素。这个故事的寓意是测试、测试、测试。
