mysql - 优化Mysql查询

Question

我正在尝试使用另一个表中的数据更新一个表，我想将缺少的纬度和经度添加到缺少该数据的“公司”表中。我有一张邮政编码表及其相应的纬度和经度可供提取，但我需要正确设置公司邮政编码的格式(更改为大写，并删除空格)。

理想情况下，我想将其作为一个查询来执行，因此我写了以下内容:

UPDATE companies as c JOIN add_postcodes as p 
    ON p.postcode = UPPER(replace(c.company_postcode, ' ', '')) 
SET 
    company_latitude = add_postcodes.Latitude,
    company_longitude = add_postcodes.Longitude
WHERE company_longitude IS NULL AND company_postcode != ''

这有效，但花了很长时间(几乎是字面上的)

因此，我将其更改为首先更新公司表中的邮政编码，然后运行更新。

UPDATE  companies 
   SET postcode = UPPER( REPLACE( company_postcode,  ' ',  '' ) )

UPDATE companies JOIN add_postcodes 
    ON companies.postcode = add_postcodes.postcode 
   SET company_latitude = add_postcodes.Latitude,
       company_longitude = add_postcodes.Longitude 
 WHERE company_longitude IS NULL AND  company_postcode !=  ''

这真的很快。

现在我想我明白为什么会这样，但我想知道我是否可以将我的单个查询重写得更快，这也意味着我不必更改公司表中的列。

我知道我可以创建一个附加列，更新后不会删除该列，但这对我来说更像是一个学习练习，而不是一个生产就绪的解决方案。

非常感谢您的指点。

Answer 1

第一个版本速度慢的原因是连接子句 p.postcode = UPPER(replace(c.company_postcode, ' ', '')) 必须对每个单独的内容执行字符串操作公司表中满足 where 子句条件的行。如果公司表中的邮政编码列有索引，则不会使用该索引。

您也许可以通过运行多个查询来加快速度。例如，如果有大量记录不需要连接上的字符串操作，您可以运行:

UPDATE companies as c JOIN add_postcodes as p 
    ON p.postcode = c.company_postcode 
SET 
    company_latitude = add_postcodes.Latitude,
    company_longitude = add_postcodes.Longitude
WHERE company_longitude IS NULL AND company_postcode != ''

在运行其他查询之前。由于此查询将设置经度列，因此受影响的记录将与 where 子句不匹配，并且应该在连接发生之前被过滤掉。

您可以尝试的另一件事是省略 UPPER 函数 - MySQL 默认情况下是 not case sensitive比较字符串时。这可能会更快:

UPDATE companies as c JOIN add_postcodes as p 
    ON p.postcode = replace(c.company_postcode, ' ', '') 
SET 
    company_latitude = add_postcodes.Latitude,
    company_longitude = add_postcodes.Longitude
WHERE company_longitude IS NULL 
AND company_postcode != ''