为什么PHP版本比MySQL运行得更快？

Question

我有两个非常大的表需要合并，所以我一直在尝试优化更新速度。我注意到部分使用PHP进行更新可以显著加快速度，因此我认为这意味着我可能没有正确地使用MySQL。

我已经简化了问题，以便缩小范围...

GRID_TABLE POSTCODE_TABLE idNo, lat, lng, nearestPostcode postcode, lat, lng ________________________________ _____________________ 1 57.1 -2.3 - AB12 3BA 56.3 -2.5 2 56.8 -1.9 - AB12 1YA 56.2 -2.3 . . . . . .

(200条目) (35,000条目)

我想使用纬度（lat）和经度（lng）查找每个网格点最近的邮政编码，从POSTCODE_TABLE中获取nearestPostcode更新GRID_TABLE。

update grid_table set nearestPostcode = (
    select postcode from postcode_table 
    where lat > grid_table.lat -0.0037 and lat < grid_table.lat +0.0037 
        and lng > grid_table.lng -0.0068 and lng < grid_table.lng +0.0068
    order by POW(lat - grid_table.lat,2) + POW((lng - grid_table.lng) *0.546,2) 
    limit 1 
    )

这个想法是使用索引缩小候选集合，然后使用“order by”子句在该集合中找到最近的一个，从而加快搜索速度。

这个 MySQL 更新需要 30 秒，但如果我在 PHP 中逐个更新每个 GRID_TABLE 行，则可以在眨眼之间完成。

$queryStg = "select * from grid_table ;";
$sqlQuery1 = mysqli_query($mysqliLink, $queryStg);

while( $sqlRow = mysqli_fetch_assoc( $sqlQuery1 ) ) {

    $idNo = $sqlRow['idNo'];
    $lat = $sqlRow['lat'];
    $lng = $sqlRow['lng'];

    $queryStg = "
        update grid_table
            set nearestPostcode = (
                SELECT postcode
                FROM postcode_table
                where
                    lat > " . ($lat - 0.0037) . " and
                    lat < " . ($lat + 0.0037) . " and
                    lng > " . ($lng - 0.0068) . " and
                    lng < " . ($lng + 0.0068) . "
                ORDER BY
                    POW(lat - $lat, 2) +
                    POW((lng - $lng) * 0.546, 2)
                    ASC
                limit 1
                )
            where idNo= $idNo;
        ";

    $sqlQuery2 = mysqli_query($mysqliLink, $queryStg);

}

“MySQL版本肯定比PHP版本更快吧？”这是MySQL的表格信息：

``` CREATE TABLE `grid_table` ( `idNo` INT(11) NOT NULL AUTO_INCREMENT, `lat` FLOAT(6,4) NOT NULL COMMENT 'latitude', `lng` FLOAT(6,4) NOT NULL COMMENT 'longitude', `nearestPostcode` CHAR(8) NOT NULL, PRIMARY KEY (`idNo`), INDEX `lat_lng` (`lat`, `lng`) ) ENGINE=MyISAM ROW_FORMAT=DEFAULT AUTO_INCREMENT=30047

CREATE TABLE `postcode_table` ( `postcode` CHAR(8) NOT NULL, `lat` FLOAT(6,4) NOT NULL COMMENT 'latitude', `lng` FLOAT(6,4) NOT NULL COMMENT 'longitude', PRIMARY KEY (`postcode`), INDEX `lat` (`lat`), INDEX `lng` (`lng`), INDEX `lat_lng` (`lat`, `lng`) ) ENGINE=MyISAM ROW_FORMAT=DEFAULT ```

MySQL导入文件在这里... https://docs.google.com/leaf?id=0B93lksnTC7_cM2Y2ZDk1Y2YtMGQ3Yy00OTIxLTk0ZDAtZmE2NmQ3YTc1ZWRm&hl=en （如果你运行UPDATE，将会添加10个最近的邮政编码）。

回答后更新...

我运行了这个...

explain extended
 SELECT postcode FROM postcode_table 
 where lat > 57.0 and lat < 57.0074
 and lng > -2.013 and lng < -2
 ORDER BY POW(lat - 57.0, 2) + POW((lng - -2) * 0.546, 2) ASC 

它返回...

id,select_type,table,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,postcode_table,range,lat,lng,lat_lng,lat_lng,8,NULL,65,100.00,使用where; 使用filesort

移除 'order by' 子句 -> 速度没有变化。

简化 'where' 子句，移除 'lng'，即

where lat between grid_table.lat - 0.0037 and grid_table.lat + 0.0037 

-> 更快：3秒而不是30秒。

使用空间列和索引（见下文）-> 更慢（190秒）。不确定我是否正确实现了这个。

ALTER TABLE `grid_table` ADD COLUMN `coords` POINT NOT NULL;
update grid_table set coords = POINT(lat, lng);
ALTER TABLE `grid_table` ADD SPATIAL INDEX `coords` (`coords`);
ALTER TABLE `postcode_table` ADD COLUMN `coords` POINT NOT NULL;
update postcode_table set coords = POINT(lat, lng);
ALTER TABLE `postcode_table` ADD SPATIAL INDEX `coords` (`coords`);
analyze table grid_table;
optimize table grid_table;
analyze table postcode_table;
optimize table postcode_table;

更新grid_table表中的nearestPostcode字段为(
    从postcode_table表中选择邮政编码
    WHERE MBRContains(GeomFromText(concat(
         'POLYGON((', 
          grid_table.lat - 0.0037, ' ', grid_table.lng - 0.0068, ', ',
          grid_table.lat - 0.0037, ' ', grid_table.lng + 0.0068, ', ',
          grid_table.lat + 0.0037, ' ', grid_table.lng - 0.0068, ', ',
          grid_table.lat - 0.0037, ' ', grid_table.lng - 0.0068, 
          '))')), postcode_table.coords)
     按照 POW(lat - grid_table.lat,2) + POW((lng - grid_table.lng) *0.546,2) 进行排序
     取1行
     )

- spiderplant0

---

在表GRID_TABLE上建立一个关于lng的索引是必要的。请查看查询计划（现在和添加此索引后的计划）。 - ypercubeᵀᴹ

嗨，@John-Cartwright。你的意思是我得到了错误的读数吗？ - spiderplant0

重新启动了我的服务器并清理了Firefox的缓存 - 仍然运行得很快。还有其他什么我可以做来清除所有东西吗？ - spiderplant0

@spiderplant：我认为最好的解决方案是使用空间对象（POINT）和空间索引，而不是存储 lat 和 lng。参见 MySQL 空间扩展：http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html - ypercubeᵀᴹ

@gaRex：完全没有黑客行为。空间数据库非常适合（并且专门用于）这种类型的查询。大多数使用一种称为R树的特殊索引：http://en.wikipedia.org/wiki/R-tree - ypercubeᵀᴹ

Answer 1

在您的MySQL版本中，子查询适用于所有30000个grid_table记录，而在您的PHP版本中只适用于一个。当您在外部表主键上添加where时。

我建议您在此更改更新查询。例如，尝试不使用子查询进行多次更新，如 http://dev.mysql.com/doc/refman/5.0/en/update.html。

我相信这应该会有所帮助。

类似于：

update grid_table, postcode_table
set grid_table.nearestPostcode = postcode_table.postcode
where postcode_table.lat > grid_table.lat - 0.0037
and postcode_table.lat < grid_table.lat + 0.0037 
and postcode_table.lng > grid_table.lng - 0.0068
and lng < grid_table.lng + 0.0068
group by grid_table.idNo
having (POW(lat - grid_table.lat,2) + POW((lng - grid_table.lng) *0.546,2)) = min(POW(lat - grid_table.lat,2) + POW((lng - grid_table.lng) *0.546,2))

也许这个版本可以帮到你，但我不确定。我认为，你的第一个版本的主要根本问题是对所有记录进行子查询。

要进行“解释更新”，可以将其“转换”为类似的选择：

explain
select
    *,
    (
        select postcode from postcode_table
        where lat > grid_table.lat -0.0037 and lat < grid_table.lat +0.0037
            and lng > grid_table.lng -0.0068 and lng < grid_table.lng +0.0068
        order by POW(lat - grid_table.lat,2) + POW((lng - grid_table.lng) *0.546,2)
        limit 1
    ) nearestPostcode   
from grid_table

您将会看到：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY grid_table  ALL                 224 
2   DEPENDENT SUBQUERY  postcode_table  ALL lat,lng,lat_lng             35605   Using where; Using temporary; Using filesort

但是在身份证号的情况下，我们有：

explain
select
    *,
    (
        select postcode from postcode_table
        where lat > grid_table.lat -0.0037 and lat < grid_table.lat +0.0037
            and lng > grid_table.lng -0.0068 and lng < grid_table.lng +0.0068
        order by POW(lat - grid_table.lat,2) + POW((lng - grid_table.lng) *0.546,2)
        limit 1
    ) nearestPostcode   
from grid_table
where idNo = 1487;

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY grid_table  const   PRIMARY PRIMARY 4   const   1   
2   DEPENDENT SUBQUERY  postcode_table  range   lat,lng,lat_lng lat 4       18  Using where; Using filesort

因此，我们有35605行对比于约18*224（约4000）个。

要找到正确的查询，请先尝试找到好的选择。

更新

子查询不是这里的根 :( 所以我认为我们应该尝试一些预计算和索引列。目标是避免order by SOMEFUNC()。

- gaRex

---

@更新：无法避免使用somefunc()进行排序。必须在给定范围内的每个网格点和邮政编码之间计算距离。查询比php版本慢的主要原因确实是子查询和mysql无法在相关子查询的where子句上使用索引。如果删除order by，您还应该看到postcode表的完整扫描。距离计算和排序是在非常有限的行集上完成的，这不是根本原因。 - piotrm

@piotrm，该死...那么我们需要另一个理论。 - gaRex

嗨@gaRex。我很难理解你的第一个建议。你确定可以在“UPDATE”中使用“GROUP BY”吗？http://dev.mysql.com/doc/refman/5.6/en/update.html - spiderplant0

此外，这35,000条记录是针对postcode_table的。grid_table只有200行。 - spiderplant0

@gaRex 非常感谢你的建议，可以将 UPDATE 转换为 SELECT 以查看索引的使用情况。 - spiderplant0

Answer 2

我的猜测是，差异是由于您在逐行查询中提供了$lat的值，从而节省了在此处进行查找的大量扫描：

order by POW(lat - grid_table.lat,2)

就像Mr47所说的那样，你可以通过解释SQL语句来查看。

- davek

---

嗨@davek。我不确定我是否理解你的意思。你是说PHP更快有很好的理由吗？还是你在说我可以做些什么来加快MySQL的速度呢？看到上面运行EXPLAIN的结果了吗？ - spiderplant0

我的意思是，在第一个版本中，您强制mysql进行更多的“查找”工作，因为您在子查询中定义了一个变量，而在第二个版本中，您提供了该值（正如gaRex所说）。这不是PHP与MySQL的问题（最终都是SQL），而是关于如何构建查询的问题。您的第二个版本要快得多，因为尽管您会触发多个查询（更多的工作），但通过减少子查询查找次数，这一点已经得到了弥补。 - davek

Answer 3

查看执行计划以了解什么导致了如此长时间的等待。 http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html

- Mr47

---

我运行了优化和分析，并运行了“解释”。问题是这仅适用于“选择”，而且PHP和MySQL版本都使用相同的选择。运行“解释”报告称正在使用键“lat”的“范围”。 - spiderplant0