好吧,我在这个问题上被否决了,所以我决定测试它: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 "
\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
$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 "
\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 useridFROM userroleWHERE roleid IN (1, 2, 3)GROUP by useridHAVING COUNT(1) = 3
查询时间:0.312sSELECT t1.useridFROM userrole t1JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3AND t1.roleid = 1
查询时间:0.016s
没错。我建议的连接版本是比聚合版本快20倍。
对不起,我这样做是为了谋生,在现实世界中工作,在现实世界中,我们测试SQL,结果本身就说明了这一点。
原因应该很清楚。聚合查询将根据表的大小按成本进行缩放。方法处理、聚合和过滤(或不)每一行。HAVING条款。JOIN版本将(使用索引)根据给定角色选择用户子集,然后对照第二个角色检查该子集,最后根据第三个角色检查该子集。各选择(在关系代数)在一个越来越小的子集上工作。由此你可以得出结论:
在匹配率较低的情况下,JOIN版本的性能会更好。
如果只有500个用户(在上面的500k示例中)具有三个指定的角色,那么Join版本就会变得更快。聚合版本不会(任何性能的改善都是传输500个用户而不是25k用户的结果,而Join版本显然也是如此)。
我也很好奇一个真正的数据库(即Oracle)将如何处理这个问题。因此,我基本上重复了Oracle XE上的相同练习(运行在与前面示例中的MySQL相同的WindowsXP桌面机器上),结果几乎相同。
JOIN似乎不受欢迎,但正如我所演示的那样,聚合查询可能会慢一个数量级。
最新情况:之后广泛测试,图片更复杂,答案将取决于您的数据,您的数据库和其他因素。故事的寓意是考验,考验。