是否有任何 MySQL 基准测试工具？[关闭]

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

Mysql 中的更新查询非常慢，需要帮助编写存储过程代码以获得更快的结果？

772

每当我从 MySQL 运行更新查询时，需要 6 到 7 个小时，因为表中有 1.5 亿行，其中更新了 3600 万行。

我认为更新 3600 万行不需要花费更多时间注意：CAT_NO并且PERIOD_YEAR用作分区而不是索引。

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);

当我运行这个更新查询时，它需要超过 6 个小时，我认为它不应该吗？

我的表看起来像这样：

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) */

我听说如果我们创建一个存储过程而不是更新查询，那么查询结果会更快。

你能帮我如何根据这 4 个更新查询而不是直接更新查询来编写这个存储过程吗？

我试过这样的存储过程：

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 ;

还需要更多时间吗？需要帮忙？谢谢

2 个回答

Voted

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

UPDATEing3600 万行需要很长时间。时期。

在电源故障和自动回滚的情况下需要复制行。这对于 36M 行来说代价高昂。时期。

有些事情可以缓解这个问题。

越小越快——缩小数据类型。如果 FLAG 为 0/1，则使用 1 字节的 TINYINT 而不是 4 字节的 INT。等等。
索引比分区好。
超过 50 个分区（包括子分区）效率低下。
有多少cat_no个值？如果只有 12 个，那么对该列的过滤或分区什么也不做（除了让读者感到困惑之外）。
不要包括未来的分区（除了LESS THAN MAXVALUE）；它减慢了速度。
分块更新。讨论

但是，最重要的是，需要定期修改数百万行是糟糕的设计。为了避免这种情况，重新考虑应用程序。也许...有一个包含 3 列的表：period_year、cat_no、flag。那将只有几百行，而不是数百万行。更改一行而不是数百万会非常快。当您需要该值时，您可以JOIN到表中。（一定要把它编入索引—— 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

存储的例程对非索引表没有帮助。

没有索引的行是通过蛮力搜索的。为 (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

Mysql 中的更新查询非常慢，需要帮助编写存储过程代码以获得更快的结果？

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

Mysql 中的更新查询非常慢，需要帮助编写存储过程代码以获得更快的结果？

2 个回答

相关问题