php - 通过 php 计算行数比 SQL 中的 COUNT 更快？

Question

简而言之，我的问题是:这是为什么

SELECT r.x, r.y FROM `base` AS r
WHERE r.l=50 AND AND r.n<>'name' AND 6=(SELECT COUNT(*) FROM surround AS d 
    WHERE d.x >= r.x -1 AND d.x <= r.x +1 AND 
          d.y>=r.y -1 AND d.y<=r.y +1 AND d.n='name')

比这慢很多:

$q="SELECT x,y FROM `base` WHERE l=50 AND n<>'name'";
$sr=mysql_query($q);
if(mysql_num_rows($sr)>=1){
    while($row=mysql_fetch_assoc($sr)){
        $q2="SELECT x,y FROM surround WHERE n='name' AND x<=".
            ($row["x"]+1)." AND x>=".($row["x"]-1).
            " AND y<=".($row["y"]+1)." AND y>=".($row["y"]-1)." ";
        $sr2=mysql_query($q2);
        if(mysql_num_rows($sr2)=6){
            echo $row['x'].','.$row[y].'\n';
        }
    }
}

php 版本大约需要 300 毫秒才能完成，如果我运行“纯 SQL”版本，无论是通过 phpadmin 还是通过 php，大约需要 5 秒(当我使用 BETWEEN 来处理这些 x 范围时甚至需要 13 秒和你)

我怀疑 SQL 版本通常会更快，至少更高效，所以我想知道，我做错了什么，或者它是否有意义？

编辑:我按要求添加了两个表的结构:

CREATE TABLE IF NOT EXISTS `base` (
  `bid` int(12) NOT NULL COMMENT 'Base ID',
  `n` varchar(25) NOT NULL COMMENT 'Name',
  `l` int(3) NOT NULL,
  `x` int(3) NOT NULL,
  `y` int(3) NOT NULL,
  `LastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `coord` (`x`,`y`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `surround` (
  `bid` int(12) NOT NULL COMMENT 'Base ID',
  `n` varchar(25) NOT NULL COMMENT 'Name',
  `l` int(3) NOT NULL,
  `x` int(3) NOT NULL,
  `y` int(3) NOT NULL,
  `LastModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `coord` (`x`,`y`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

编辑 2:

EXPLAIN SELECT 对于上面的查询:(关键坐标是 x 和 y 的组合)

id  select_type         table   type    possible_keys   key     key_len     ref     rows    Extra   
1   PRIMARY             r       range   coord,n         coord   4           NULL    4998    Using where
2   DEPENDENT SUBQUERY  d       ALL     coord           NULL    NULL        NULL    57241   Range checked for each record (index map: 0x1)

Answer 1

您正在自己连接两个表。你是一个优化器。您选择的“基础”表是嵌套循环连接的外部表。我猜 MySQL 的优化器产生了执行计划，它和你不一样。

所以人们希望 EXPLAIN 输出来查看连接顺序并检查索引是否被使用。

顺便问一下，你能试试这个查询吗？:

SELECT r.x, r.y
FROM `base` AS r, surround AS d
WHERE r.l=50
  AND r.n<>'name'
  AND d.x >= r.x -1
  AND d.x <= r.x +1
  AND d.y>=r.y -1
  AND d.y<=r.y +1
  AND d.n='name'
GROUP BY r.x, r.y
HAVING COUNT(*) = 6

已更新

你的原始查询是如何工作的

这是第一次看到 Range checked for each record (index map: 0x1) 所以我不知道你的查询是如何工作的。 MySQL Manual给我们一些关于它的信息。似乎 surround 中的每一行(surround 有 57k 行？)都与 base 的 x,y 进行比较。如果是这样，您的查询将使用 3 深度嵌套循环连接进行评估。 (base => surround => base) 而且 surround 中的每一行都被比较(这是低效的)

稍后我会更加努力地寻找它是如何工作的。该上类了。