mysql - 需要调整 mysql 查询

Question

我有一个 MySQL 查询需要超过 6 天才能完成，activation_member 表中有大约 2.5 亿条记录，temp_message_split 表中有大约 190 万条记录。在调整此查询时寻求帮助：

UPDATE TEMP_MESSAGE_SPLIT A,
                            (SELECT

                                            ACTIVATION_MEMBER_KEY,
                                            dh_member_id,
                                            alt_id,
                                            drsn
                            FROM ACTIVATION_MEMBER AM )  B  
                            SET A.ACTIVATION_MEMBER_KEY = B.ACTIVATION_MEMBER_KEY, A.STATUS = 'U'
                            WHERE  A.DH_MEMBER_ID = B.DH_MEMBER_ID OR ( (A.ALT_ID = b.alt_id) AND (A.DRSN = b.drsn)) ;

更新查询用于更新 TEMP_MESSAGE_SPLIT 表中的 TEMP_MESSAGE_SPLIT.STATUS = 'U' 以获取所有已存在于activation_member 表中的成员。我们需要优化这个查询，使其最少花费。尽可能多的执行时间。

表创建语句：

  CREATE TABLE
activation_member
(
    ACTIVATION_MEMBER_KEY bigint NOT NULL AUTO_INCREMENT,
    PORTAL_STATEMENT_LOC_KEY bigint,
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(30),
    DOB DATE,
    EMPLOYEE_ID VARCHAR(20),
    CES_CUST_NUM VARCHAR(7),
    MED_POLICY_NUM VARCHAR(20),
    EMAIL_ADDR VARCHAR(50),
    DH_MEMBER_ID VARCHAR(9),
    ALT_ID VARCHAR(20),
    DRSN VARCHAR(2),
    SSN VARCHAR(9),
    EPIPHANY_MEMBER_ID bigint,
    SYSTEM_HIS_CNT mediumint,
    SYSTEM_USER VARCHAR(30),
    SYSTEM_TIMESTAMP DATETIME,
    PRIMARY KEY (ACTIVATION_MEMBER_KEY),
    CONSTRAINT ACTIVATION_MEMBER_FK1 FOREIGN KEY (PORTAL_STATEMENT_LOC_KEY) REFERENCES
    `portal_statement_loc` (`PORTAL_STATEMENT_LOC_KEY`),
    INDEX ACTIVATION_MEMBER_IDX1 (PORTAL_STATEMENT_LOC_KEY),
    INDEX ACTIVATION_MEMBER_IDX2 (DH_MEMBER_ID),
    INDEX ACTIVATION_MEMBER_IDX3 (EMPLOYEE_ID, FIRST_NAME, MED_POLICY_NUM, DOB),
    INDEX ACTIVATION_MEMBER_IDX4 (EPIPHANY_MEMBER_ID),
    INDEX ACTIVATION_MEMBER_IDX5 (ALT_ID, DRSN)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE
portal_statement_loc
(
    PORTAL_STATEMENT_LOC_KEY bigint NOT NULL AUTO_INCREMENT,
    PORTAL_ADDRESS VARCHAR(200),
    STATEMENT_VENDOR VARCHAR(100),
    SYSTEM_HIS_CNT mediumint,
    SYSTEM_USER VARCHAR(30),
    SYSTEM_TIMESTAMP DATETIME,
    PRIMARY KEY (PORTAL_STATEMENT_LOC_KEY)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE
temp_message_split
(
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(30),
    DOB VARCHAR(10),
    EMPLOYEE_ID VARCHAR(20),
    CES_CUST_NUM VARCHAR(7),
    MED_POLICY_NUM VARCHAR(20),
    EMAIL_ADDR VARCHAR(50),
    DH_MEMBER_ID VARCHAR(9),
    ALT_ID VARCHAR(20),
    DRSN VARCHAR(2),
    SSN VARCHAR(9),
    EPIPHANY_MEMBER_ID VARCHAR(18),
    PORTAL_ADDRESS VARCHAR(30),
    STATEMENT_VENDOR VARCHAR(20),
    CONTENT_KEY VARCHAR(18),
    EPIPHANY_COMMUNICATION_ID VARCHAR(200),
    PRIORITY VARCHAR(4),
    DAYS_UNTIL_EXPIRED VARCHAR(4),
    CONTENT_DTL_KEY VARCHAR(18),
    STATUS VARCHAR(1),
    ACTIVATION_MEMBER_KEY bigint,
    MESSAGE_BOARD_KEY bigint,
    PORTAL_STATEMENT_LOC_KEY bigint,
    temp_message_split_KEY bigint NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (temp_message_split_KEY),
    INDEX EPIPHANY_COMMUNICATION_ID_IDX (EPIPHANY_COMMUNICATION_ID),
    INDEX TEMP_MESSAGE_SPLIT_IDX1 (DH_MEMBER_ID),
    INDEX TEMP_MESSAGE_SPLIT_IDX2 (ALT_ID),
    INDEX TEMP_MESSAGE_SPLIT_IDX3 (DRSN)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

标签： mysqlperformanceoptimizationmysql-5.7

Answer 1

我认为正确的更新是：

 UPDATE  TEMP_MESSAGE_SPLIT A
JOIN  ACTIVATION_MEMBER B  ON A.DH_MEMBER_ID = B.DH_MEMBER_ID
SET A.ACTIVATION_MEMBER_KEY = B.ACTIVATION_MEMBER_KEY,
    A.STATUS = 'U';

   UPDATE  TEMP_MESSAGE_SPLIT A
JOIN  ACTIVATION_MEMBER B  ON A.ALT_ID = B.ALT_ID AND A.DRSN = B.DRSN
SET A.ACTIVATION_MEMBER_KEY = B.ACTIVATION_MEMBER_KEY,
    A.STATUS = 'U';