MySQL：优化组查询中的最大行

Zbynek 发表于 Dev

兹比涅克

我有Addresses以下架构的表：

CREATE TABLE `Address` (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ID_Person INT NOT NULL,
    Street VARCHAR(50),
    HouseNumber VARCHAR(10),
    City VARCHAR(50),
    Zipcode CHAR(5),
    Country CHAR(2),
    Version INT,
    ValidFrom DATE,
    ValidTo DATE,
);

每个地址都属于用户（ID_Person），每个用户可以有多个地址。每个地址都有Version，即INT，一个用户可以有多个具有相同版本的地址。我想检索Version每个用户的最高地址。如果一个用户有多个最高版本，则可以从最高值中随机选择地址。

我所做的是，下面是：

我得到每个用户的地址的最高版本：

CREATE VIEW vw_highest_addresses AS
SELECT 
    ID,
    ID_Person,
    Max(Version) AS Version
FROM
    Address
WHERE ValidTo IS NULL OR ValidTo < NOW()
GROUP BY ID_Person

然后根据组合加入addresesID_Person和Version

CREATE VIEW vw_addresses AS
SELECT 
    a.ID,
    a.ID_Person,
    a.Street,
    a.HouseNumber,
    a.City,
    a.Zipcode,
    a.Country,
    a.Version
FROM
    Address AS a, vw_primary_addresses AS aprimary
WHERE
    a.ID_Person = aprimary.ID_Person
  AND a.Version = aprimary.Version
  AND a.ValidTo IS NULL OR a.ValidTo < NOW()
GROUP BY a.ID_Person;

我有这个索引：

CREATE INDEX idx_addresses ON Address(ID_Person, IsPrimary, ValidTo)

这样可以提供正确的结果，但是非常慢（地址中的6000行需要6秒才能执行查询）

这是查询执行计划：

id  select_type     table   type    possible_keys   key             key_len     ref     rows    Extra   
1   PRIMARY     <derived2>  ALL     NULL            NULL            NULL        NULL    5936    
2   DERIVED     a           ALL     idx_addresses   NULL            NULL        NULL    5565    Using where; Using temporary; Using filesort
2   DERIVED     <derived3>  ALL     NULL            NULL            NULL        NULL    5936    Using where; Using join buffer
3   DERIVED     a           index   NULL            idx_addresses   10          NULL    5565    Using where; Using index

如何优化查询？

巴尔玛

您所查询的是由于优先级的不正确AND和OR。AND散列更高的优先级，因此将其视为已编写。

CREATE VIEW vw_addresses AS
SELECT 
    a.ID,
    a.ID_Person,
    a.Street,
    a.HouseNumber,
    a.City,
    a.Zipcode,
    a.Country,
    a.Version
FROM
    Address AS a, vw_primary_addresses AS aprimary
WHERE
    (a.ID_Person = aprimary.ID_Person
        AND a.Version = aprimary.Version
        AND a.ValidTo IS NULL)
    OR a.ValidTo < NOW()
GROUP BY a.ID_Person;

因此ValidTo < NOW()，除了符合加入条件的商品外，每个带有的商品都将作为交叉产品返回。

如果您将其写为显式会更好JOIN，那么您就不会遇到这个问题。

CREATE VIEW vw_addresses AS
SELECT 
    a.ID,
    a.ID_Person,
    a.Street,
    a.HouseNumber,
    a.City,
    a.Zipcode,
    a.Country,
    a.Version
FROM
    Address AS a
JOIN 
    vw_primary_addresses AS aprimary
ON
    a.ID_Person = aprimary.ID_Person
  AND a.Version = aprimary.Version
WHERE 
  a.ValidTo IS NULL OR a.ValidTo < NOW()
GROUP BY a.ID_Person;

请注意，如果有多个具有相同版本的地址，则SELECT可能会从不同的地址中选择不同的列。为了解决这个问题，您需要另一级子查询。

CREATE VIEW vw_addresses_id AS
SELECT 
    MAX(a.ID) AS id
FROM
    Address AS a
JOIN 
    vw_primary_addresses AS aprimary
ON
    a.ID_Person = aprimary.ID_Person
  AND a.Version = aprimary.Version
WHERE 
  a.ValidTo IS NULL OR a.ValidTo < NOW()
GROUP BY a.ID_Person;

CREATE VIEW vw_addresses AS
SELECT a.*
FROM Address AS a
JOIN vw_addresses_id AS aprimary
ON a.ID = aprimary.ID

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。