Sempre que executo uma consulta de atualização do MySQL, leva de 6 a 7 horas, pois há 150 milhões de linhas na tabela e, fora dela, 36 milhões de linhas são atualizadas.
Eu não acho que demore mais tempo para atualizar com 36 milhões de linhas Nota: CAT_NOe PERIOD_YEARsão usados como partição em vez de um índice.
Update ace_dwh.fa_nls_o set dynamic_l52_weeks_flag = 0
where cat_no in (0,1,2,3,4,5,6,7,8,9,10,11) and period_year in (201605,201606);
Update ace_dwh.fa_nls_o set dynamic_l26_weeks_flag = 0
where cat_no in (0,1,2,3,4,5,6,7,8,9,10,11) and period_year in (201611,201512);
Update ace_dwh.fa_nls_o set dynamic_l13_weeks_flag = 0
where cat_no in (0,1,2,3,4,5,6,7,8,9,10,11) and period_year in (201702,201703);
Update ace_dwh.fa_nls_o set dynamic_lm1_flag = 0
where cat_no in (0,1,2,3,4,5,6,7,8,9,10,11) and period_year in (201704,201705);
Quando executo esta consulta de atualização, está demorando mais de 6 horas, o que acho que não deveria?
Minha tabela está assim:
CREATE TABLE `fa_nls_o` (
`MKT` varchar(100) DEFAULT NULL,
`PROD` varchar(1000) DEFAULT NULL,
`PRODUCT_LEVEL` varchar(100) DEFAULT NULL,
`CUSTOM_MONTH` varchar(50) DEFAULT NULL,
`CUSTOM_QTR` varchar(50) DEFAULT NULL,
`DYNAMIC_LM1_FLAG` int(10) DEFAULT NULL,
`DYNAMIC_L13_WEEKS_FLAG` int(10) DEFAULT NULL,
`DYNAMIC_L26_WEEKS_FLAG` int(10) DEFAULT NULL,
`DYNAMIC_L52_WEEKS_FLAG` int(10) DEFAULT NULL,
`DYNAMIC_YTD_2016_FLAG` int(10) DEFAULT NULL,
`DYNAMIC_YTD_2017_FLAG` int(10) DEFAULT NULL,
`DYNAMIC_FY_2015_FLAG` int(10) DEFAULT NULL,
`DYNAMIC_FY_2016_FLAG` int(10) DEFAULT NULL,
`DYNAMIC_FY_2014_FLAG` int(10) DEFAULT NULL,
`CURRENT_PERIOD_FLAG` int(10) DEFAULT NULL,
`CHARACTERESTIC_1` varchar(50) DEFAULT NULL,
`CHARACTERESTIC_2` varchar(50) DEFAULT NULL,
`CHARACTERESTIC_3` varchar(50) DEFAULT NULL,
`CHOOSE_METRIC` double(50,4) DEFAULT NULL,
`PERIOD_YEAR` int(50) DEFAULT NULL,
`CAT_NO` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (PERIOD_YEAR)
SUBPARTITION BY KEY (CAT_NO)
SUBPARTITIONS 12
(PARTITION p0 VALUES LESS THAN (201401) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (201402) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (201403) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (201404) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (201405) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (201406) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN (201407) ENGINE = InnoDB,
PARTITION p14 VALUES LESS THAN (201408) ENGINE = InnoDB,
PARTITION p16 VALUES LESS THAN (201409) ENGINE = InnoDB,
PARTITION p18 VALUES LESS THAN (201410) ENGINE = InnoDB,
PARTITION p20 VALUES LESS THAN (201411) ENGINE = InnoDB,
PARTITION p22 VALUES LESS THAN (201412) ENGINE = InnoDB,
PARTITION p24 VALUES LESS THAN (201501) ENGINE = InnoDB,
PARTITION p26 VALUES LESS THAN (201502) ENGINE = InnoDB,
PARTITION p28 VALUES LESS THAN (201503) ENGINE = InnoDB,
PARTITION p30 VALUES LESS THAN (201504) ENGINE = InnoDB,
PARTITION p32 VALUES LESS THAN (201505) ENGINE = InnoDB,
PARTITION p34 VALUES LESS THAN (201506) ENGINE = InnoDB,
PARTITION p36 VALUES LESS THAN (201507) ENGINE = InnoDB,
PARTITION p38 VALUES LESS THAN (201508) ENGINE = InnoDB,
PARTITION p40 VALUES LESS THAN (201509) ENGINE = InnoDB,
PARTITION p42 VALUES LESS THAN (201510) ENGINE = InnoDB,
PARTITION p44 VALUES LESS THAN (201511) ENGINE = InnoDB,
PARTITION p46 VALUES LESS THAN (201512) ENGINE = InnoDB,
PARTITION p48 VALUES LESS THAN (201601) ENGINE = InnoDB,
PARTITION p50 VALUES LESS THAN (201602) ENGINE = InnoDB,
PARTITION p52 VALUES LESS THAN (201603) ENGINE = InnoDB,
PARTITION p54 VALUES LESS THAN (201604) ENGINE = InnoDB,
PARTITION p56 VALUES LESS THAN (201605) ENGINE = InnoDB,
PARTITION p58 VALUES LESS THAN (201606) ENGINE = InnoDB,
PARTITION p60 VALUES LESS THAN (201607) ENGINE = InnoDB,
PARTITION p62 VALUES LESS THAN (201608) ENGINE = InnoDB,
PARTITION p64 VALUES LESS THAN (201609) ENGINE = InnoDB,
PARTITION p66 VALUES LESS THAN (201610) ENGINE = InnoDB,
PARTITION p68 VALUES LESS THAN (201611) ENGINE = InnoDB,
PARTITION p70 VALUES LESS THAN (201612) ENGINE = InnoDB,
PARTITION p72 VALUES LESS THAN (201701) ENGINE = InnoDB,
PARTITION p74 VALUES LESS THAN (201702) ENGINE = InnoDB,
PARTITION p76 VALUES LESS THAN (201703) ENGINE = InnoDB,
PARTITION p78 VALUES LESS THAN (201704) ENGINE = InnoDB,
PARTITION p80 VALUES LESS THAN (201705) ENGINE = InnoDB,
PARTITION p82 VALUES LESS THAN (201706) ENGINE = InnoDB,
PARTITION p84 VALUES LESS THAN (201707) ENGINE = InnoDB,
PARTITION p86 VALUES LESS THAN (201708) ENGINE = InnoDB,
PARTITION p88 VALUES LESS THAN (201709) ENGINE = InnoDB,
PARTITION p90 VALUES LESS THAN (201710) ENGINE = InnoDB,
PARTITION p92 VALUES LESS THAN (201711) ENGINE = InnoDB,
PARTITION p94 VALUES LESS THAN (201712) ENGINE = InnoDB,
PARTITION p96 VALUES LESS THAN (201801) ENGINE = InnoDB) */
Ouvi dizer que se criarmos um procedimento armazenado em vez de uma consulta de atualização, os resultados da consulta serão mais rápidos.
Você pode me ajudar a escrever esses procedimentos armazenados com base nessas 4 consultas de atualização em vez de consultas de atualização direta?
Eu tentei procedimentos armazenados assim:
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `proc_WeekFlagUpdate`()
BEGIN
UPDATE ace_dwh.fa_nls_o
SET dynamic_l52_weeks_flag = 0
WHERE cat_no IN (0 , 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
AND period_year IN (201605 , 201606);
UPDATE ace_dwh.fa_nls_o
SET dynamic_l26_weeks_flag = 0
WHERE cat_no IN (0 , 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
AND period_year IN (201611 , 201512);
UPDATE ace_dwh.fa_nls_o
SET dynamic_l13_weeks_flag = 0
WHERE cat_no IN (0 , 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
AND period_year IN (201702 , 201703);
UPDATE ace_dwh.fa_nls_o
SET dynamic_lm1_flag = 0
WHERE cat_no IN (0 , 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
AND period_year IN (201704 , 201705);
END$$
DELIMITER ;
Demora ainda mais tempo? Preciso de ajuda ? Obrigado
