mysql 簡單更新 4000 萬和 128GB RAM 花費太多時間

我們在單個表上的簡單更新需要很長時間時遇到問題。該表包含約 4000 萬行。

並且該作業每天執行，截斷表並在該表中插入來自其他源的新數據。

這是表格：

CREATE TABLE temp (
 NO int(4) NOT NULL AUTO_INCREMENT,
 DATE1 date DEFAULT NULL,
 CODE int(4) DEFAULT NULL,
 TYPE varchar(20) DEFAULT NULL,
 SCODE int(4) DEFAULT NULL,
 Nature varchar(25) DEFAULT NULL,
 UNITS decimal(19,4) DEFAULT NULL,
 BNITS decimal(19,4) DEFAULT NULL,
 DRECD double DEFAULT '0',
 FNO varchar(50) DEFAULT NULL,
 FLAG varchar(5) DEFAULT NULL,
 MBAL double DEFAULT NULL,
 PBAL double DEFAULT NULL,
 MTotalBal double DEFAULT NULL,
 PLNOT decimal(19,4) DEFAULT NULL,
 PLBOOK decimal(19,4) DEFAULT NULL,
 AGE int(4) DEFAULT NULL,
 RETABS decimal(19,4) DEFAULT NULL,
 RETAGR decimal(19,4) DEFAULT NULL,
 INDEX1 decimal(19,4) DEFAULT NULL,
 RETINDEXABS decimal(19,4) DEFAULT NULL,
 RetIndexCAGR decimal(19,4) DEFAULT NULL,
 CURRAMT decimal(19,4) DEFAULT NULL,
 GLOSSLT decimal(19,4) DEFAULT NULL,
 GLOSSST decimal(19,4) DEFAULT NULL,
 UNITSFORDIVID decimal(19,4) DEFAULT NULL,
 factor double DEFAULT NULL,
 LNav double DEFAULT '10',
 Date2 date DEFAULT NULL,
 IType int(4) DEFAULT NULL,
 Rate double DEFAULT NULL,
 CurrAmt double DEFAULT NULL,
 IndexVal double DEFAULT NULL,
 LatestIndexVal double DEFAULT NULL,
 Field int(4) DEFAULT NULL,
 C_Code int(4) DEFAULT NULL,
 B_Code int(4) DEFAULT NULL,
 Rm_Code int(4) DEFAULT NULL,
 Group_Name varchar(100) DEFAULT NULL,
 Type1 varchar(20) DEFAULT NULL,
 Type2 varchar(20) DEFAULT NULL,
 IsOnline tinyint(3) unsigned DEFAULT NULL,
 SFactor double DEFAULT NULL,
 OS_Code int(4) DEFAULT NULL,
 PRIMARY KEY (NO),
 KEY SCODE (SCODE),
 KEY C_Code (C_Code),
 KEY TYPE (TYPE),
 KEY OS_Code (OS_Code),
 KEY LNav (LNav),
 KEY IDX_1 (AGE,Type2),
 KEY DATE1 (DATE1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

注意：擁有這麼多索引的原因是我們在 SP 中有許多選擇會減少表掃描。

UPDATE Temp 
     INNER JOIN SchDate ON Temp.Sch_Code = SchDate.Sch_Code
   SET LatestNav = NavRs, NavDate = LDate   ;
-- SchDate table contain 41K record

UPDATE Temp
   SET Age = DATEDIFF(NAVDATE, TR_DATE),
       CurrAmt = (LatestNav * Units),
       PL_Notional = (UNITS * (LatestNav - Rate)),
       Divd_Recd = 0;
here is my.cnf for reference

[client]
port=3307
max_execution_time = 0
local_infile = 1

[mysql]
no-beep

[mysqld]

port=3307
#skip-locking
#skip-name-resolve
default_authentication_plugin=mysql_native_password
wait_timeout = 300
interactive_timeout = 300
default-storage-engine=INNODB
sql-mode="NO_ENGINE_SUBSTITUTION,ANSI_QUOTES"
max_execution_time = 0
innodb_autoinc_lock_mode = 0
group_concat_max_len=153600
skip-log-bin
log_bin_trust_function_creators = 1
#expire_logs_days = 3
local_infile = 1
skip-log-bin


### Cache/Buffer Related Parameters ###
table_open_cache=1024000
open_files_limit=2048000
key_buffer_size=2147483648

#myisam_max_sort_file_size=1G
#myisam_sort_buffer_size=512M
#myisam_repair_threads=1



# General and Slow logging.
log-output=FILE
#general-log=0
#general_log_file="E:\Mysql\MySQL Server 8.0\Data\2016SERVER.log"
#slow-query-log=1
#slow_query_log_file="E:\Mysql\MySQL Server 8.0\Data\2016SERVER-slow.log"
long_query_time=100


# Thread Specific Values
sort_buffer_size=2147483648
read_buffer_size=2147483648
read_rnd_buffer_size=1073741824
join_buffer_size=1073741824
thread_cache_size=600
bulk_insert_buffer_size=4294967296

### Mysql Directory & Tables ###
datadir="E:\Mysql\Data\Data\"
tmp_table_size=17179869184

max_heap_table_size=8589934592



### Innodb Related Parameters ###
#innodb_force_recovery=3

## Innodb startup-shutdown related parameter
innodb_max_dirty_pages_pct = 0
innodb_buffer_pool_dump_pct = 100
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

innodb_change_buffer_max_size = 50
innodb_file_per_table = 1
innodb_log_file_size = 10G
innodb_log_buffer_size = 4294967295
innodb_log_files_in_group = 10
#innodb_buffer_pool_chunk_size = 1024M
innodb_buffer_pool_size = 96636764160
###innodb_buffer_pool_size=90G
innodb_buffer_pool_instances = 50
#innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 100
innodb_write_io_threads = 64
innodb_read_io_threads = 64

# Binary Logging.
#log-bin="E:\Mysql\Data\Data\2016SERVER-bin"

# Error Logging.
log-error="E:\Mysql\Data\Data\2016SERVER.err"

# Server-Id.
server-id=2

lower_case_table_names=1

# Secure File Priv.
secure-file-priv="E:\Mysql\Uploads"
max_connections=500
#innodb_thread_concurrency=9

innodb_thread_concurrency=0
innodb_adaptive_max_sleep_delay=150000
innodb_autoextend_increment = 2048
#innodb_concurrency_tickets=5000
#innodb_old_blocks_time=1000
innodb_open_files=1500
innodb_stats_on_metadata=0
innodb_checksum_algorithm=0
#back_log=80
#flush_time=0
max_allowed_packet=512M
table_definition_cache=1400
binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000
loose_mysqlx_port=33060

innodb_flush_method = unbuffered
###innodb_flush_method = async_unbuffered
default-time-zone = +05:30
tmpdir = "C:/TEMP"
innodb_io_capacity = 1000
plugin_dir = "C:/Program Files/MySQL/MySQL Server 8/lib/plugin"
innodb_log_write_ahead_size = 16394
mysqlx_max_connections = 500
innodb_random_read_ahead = 1

第一次更新需要 30 到 35 分鐘，第二次更新需要 15 分鐘。

這是更新1的解釋計劃

1   SIMPLE  SchDate     index   PRIMARY,Sch_Code,IDX_1  Sch_Code    4       39064   100 Using index
1   SIMPLE  temp        ref SCH_Code    SCH_Code    9   SchDate.Sch_Code    1   100 Using index condition

我在 Windows 10 上執行此查詢。有什麼方法可以提高 UPDATE 查詢的速度嗎？任何與配置相關的更改都會有幫助嗎？

UPDATEing所有 40M 行都需要很長時間。時期。句點。

它必須複製每一行以防崩潰；這樣它就可以回滾更改。

根據要求，分塊執行更新可能很有用。在這裡討論。

完成更新有什麼急事？表鎖定？完成工作？其他衝突？不同的原因可能導致不同的解決方法。

“每天執行的作業會截斷表並在該表中插入來自其他來源的新數據”——如果這是一個實時表，那麼有更好的方法。但是，既然你叫它temp，也許它就不需要了？

建議刪除二級索引，載入表，然後重新添加索引。

OTOH，“SP 中的許多選擇將減少表掃描”——如果這些選擇需要讀取所有行，那麼擁有索引沒有任何優勢。乾脆做SELECT ... ORDER BY ...。

但是，Sch_Code需要一個索引，因為您一次可以訪問這些行。

INT(4)——4沒有意義。AnINT始終為 4 個字節。如果您正在考慮 4 位數字，那麼請考慮 2-byte SMALLINT。

如果您使用 HDD 將其更改為 SSD，這將對您的 IOPS 和更新時間產生巨大影響。

引用自：https://dba.stackexchange.com/questions/227555