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

Question

我的表 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

然后根据ID_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

如何优化查询？

Answer 1

由于 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