SAM AB's questions

每当我从 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 ;

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

我正在将数据目录从具有 raid 控制器硬盘的 D 驱动器移动到在 windows 环境中安装操作系统的 C 驱动器。

以下是问题：

Stop Mysql server
open my.ini --> data directory -> "C盘路径"
copy data directory文件夹到c盘文件夹
start mysql server

它会因为这是 innodb 引擎而工作吗？我听说我们需要恢复为 .sql 文件，将原始文件从一个地方复制到另一个地方是行不通的。
这是真的还是因为有 600 GB 的数据大小，您还有其他解决方法吗？

谁能告诉我在Windows环境中备份 500Gb 数据大小的简单方法是什么？我的物理内存是服务器上的 672 GB RAM？哪些工具可以快速备份和恢复？注意：对于 500GB 的数据大小，mysqldump 将花费超过 1 周的时间任何有关详细信息的建议，是否有任何解决方法可以在 2 天内完成？

我的服务器配置有 auto_commit = off 这在插入和更新时有助于大型事务但是如果 innodb_autoinc_lock_mode = 1 怎么办因为我的表没有带 auto_increment 的主键？它会有助于更快的结果还是我们需要达到 2？有什么办法可以帮助它吗？