Existem ferramentas de benchmarking do MySQL? [fechado]

Question

SAM AB

Asked: 2017-07-13 02:15:12 +0800 CST2017-07-13 02:15:12 +0800 CST 2017-07-13 02:15:12 +0800 CST

A consulta de atualização no Mysql é muito lenta e precisa de ajuda para escrever o código de procedimentos armazenados para obter resultados mais rápidos?

772

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

2 respostas

Voted

Rick James · Answer 1 · 2017-07-13T05:55:27+08:00

UPDATEing36 milhões de linhas leva muito tempo. Período.

As linhas precisam ser copiadas em caso de falha de energia e ROLLBACK automático. Isso é caro para 36 milhões de linhas. Período.

Algumas coisas podem atenuar o problema.

Menor é mais rápido -- reduza os tipos de dados. Se os FLAGs forem 0/1, use um TINYINT de 1 byte em vez de um INT de 4 bytes. etc.
Índices são melhores que particionamento.
Mais de 50 partições (incluindo subpartições) é ineficiente.
Quantos cat_novalores existem? Se apenas 12, filtrar ou particionar nessa coluna não faz nada (além de confundir o leitor).
Não inclua partições futuras (além de LESS THAN MAXVALUE); isso atrasa as coisas.
Atualização em pedaços. Discussão

Mas, o mais importante, é um design ruim precisar modificar milhões de linhas regularmente. Repense a aplicação com a intenção de evitar tal. Talvez... Tenha uma tabela com 3 colunas: period_year, cat_no, flag. Isso teria apenas algumas centenas de linhas, não milhões. E seria extremamente rápido mudar uma linha em vez de milhões. Você pode JOINir à mesa quando precisar do valor. (Certifique-se de tê-lo indexado -- PRIMARY KEY(period_year, cat_no)!!)

Kondybas · Answer 2 · 2017-07-13T03:17:53+08:00

Best Answer

Kondybas

2017-07-13T03:17:53+08:002017-07-13T03:17:53+08:00

As rotinas armazenadas não podem ajudar com tabelas não indexadas.

Sem índices, as linhas são pesquisadas por força bruta. Crie o índice complexo para (cat_no, period_year):

CREATE TABLE `fa_nls_o` (        
   . . . . .
   `CAT_NO` int(10) DEFAULT NULL             
    INDEX `cat_no_period_year` (cat_no, period_year)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

2

A consulta de atualização no Mysql é muito lenta e precisa de ajuda para escrever o código de procedimentos armazenados para obter resultados mais rápidos?

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

A consulta de atualização no Mysql é muito lenta e precisa de ajuda para escrever o código de procedimentos armazenados para obter resultados mais rápidos?

2 respostas

relate perguntas