MySQL方法:大型自连接设置值？

Question

我正在使用一个包含姓氏、地址、性别和生日字段的1200万条记录的MyISAM表：

ID  SURNAME  GENDER       BDATE  COUNTY         ADDRESS         CITY
 1    JONES       M  1954-11-04     015       51 OAK ST  SPRINGFIELD
 2     HILL       M  1981-02-16     009     809 PALM DR   JONESVILLE
 3     HILL       F  1979-06-23     009     809 PALM DR   JONESVILLE
 4     HILL       F  1941-10-11     009     809 PALM DR   JONESVILLE
 5    SMITH       M  1914-07-27     035  1791 MAPLE AVE     MAYBERRY
 6    SMITH       F  1954-02-05     035  1791 MAPLE AVE     MAYBERRY
 7  STEVENS       M  1962-05-05     019  404 CYPRESS ST     MAYBERRY
 .        .       .           .       .               .
 .        .       .           .       .               .
 .        .       .           .       .               .

Surname、bdate和address字段已建立索引。我的目标是添加一个用于推断婚姻状况的字段，该字段由以下条件定义:对于每个记录，如果表中存在(1)相同姓氏、(2)不同性别、(3)相同地址和(4)年龄差小于15岁的另一条记录，则设置为“已结婚”= T；否则，“已结婚”= F。

作为一名SQL新手，我最初的方法是添加一个默认为'F‘的婚姻字段，然后使用自连接来设置MARRIED = T。

ALTER TABLE MY_TABLE
ADD COLUMN MARRIED CHAR(1) NOT NULL DEFAULT 'F';

UPDATE MY_TABLE T1, MY_TABLE T2
SET T1.MARRIED = 'T' WHERE
  T1.SURNAME = T2.SURNAME AND
  T1.GENDER != T2.GENDER AND
  T1.ADDRESS = T2.ADDRESS AND
  T1.CITY    = T2.CITY AND
  ABS(YEAR(T1.BDATE)-YEAR(T2.BDATE)) < 15;

虽然这在小表上工作得很好，但我很快就了解到，在一个有1200万行的表上，我可能会在这个过程完成之前退休。我的SQL知识非常有限，所以我确信这是一种不太理想的方法。有什么建议的替代方案吗？也许索引姓氏+地址+城市？先按地址+城市分组？更好的表格设计？如有任何建议，我们将不胜感激。

提前感谢您的帮助！

Answer 1

留心兄弟姐妹们！

Answer 2

我会尝试几个变体，看看哪一个性能最好：

使用简单的Exists但使用Date_Add而不是ABS值函数的版本1：

Update My_Table
Set Married = 'T'
Where Exists    (
                Select 1
                From My_Table As T2
                Where T2.SurName = My_Table.SurName
                    And T2.Gender != My_Table.Gender
                    And T2.Address = My_Table.Address
                    And T2.City = My_Table.City
                    And (
                        T2.BDate > Date_Add(My_Date.BDate, Interval 15 Year)
                        Or T2.BDate < Date_Add(My_Date.BDate, Interval -15 Year)
                        )
                )

使用UNION ALL的版本2

Update My_Table
Set Married = 'T'
Where Exists    (
                Select 1
                From My_Table As T2
                Where T2.SurName = My_Table.SurName
                    And T2.Gender != My_Table.Gender
                    And T2.Address = My_Table.Address
                    And T2.City = My_Table.City
                    And T2.BDate > Date_Add(My_Date.BDate, Interval 15 Year)
                Union All
                Select 1
                From My_Table As T2
                Where T2.SurName = My_Table.SurName
                    And T2.Gender != My_Table.Gender
                    And T2.Address = My_Table.Address
                    And T2.City = My_Table.City
                    And T2.BDate < Date_Add(My_Date.BDate, Interval -15 Year
                )

使用内部联接和Date_Add的版本3

Update My_Table As T1
    Join My_Table As T2
            On T2.SurName = T1.SurName
                And T2.Gender != T1.Gender
                And T2.Address = T1.Address
                And T2.City = T1.City
Set Married = 'T'
Where T1.BDate > Date_Add(T2.BDate, Interval 15 Year)
        Or T1.BDate < Date_Add(T2.BDate, Interval -15 Year)

退一步讲SQL，我认为试图根据所提供的信息来推断两个人是否结婚是充满问题的。它没有考虑到年龄差异超过15岁的夫妇(Anna Nicole Smith，有人吗？)它也没有考虑到兄弟姐妹，也没有考虑到结婚但不改变姓氏的两个人。

Answer 3

那么，索引WHERE子句中的所有字段肯定会加快查询速度。

这意味着姓氏、性别、地址、城市和BDATE。

您可以尝试的另一件事是定义规则以缩小ON部分的结果范围：

UPDATE MY_TABLE T1
  LEFT JOIN MY_TABLE T2
  ON T1.SURNAME = T2.SURNAME
    AND T1.GENDER != T2.GENDER
    AND T1.CITY   = T2.CITY
  SET T1.MARRIED = 'T'
  WHERE ABS(YEAR(T1.BDATE)-YEAR(T2.BDATE)) < 15;