Mysql
將數據緩慢導入Mysql
此文件大小為 23GB，它是使用工作台中的 Dump 創建的
它開始很好，但是當它達到記錄數據的 1/3 時，導入開始變得非常緩慢。
您對提高這些查詢的性能有何建議？
結果：非常慢，有人可以幫我嗎？
導入使用：
mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS = 0; \ SET UNIQUE_CHECKS = 0" -v \ 2>./logdeimport.txt \ -u usrimport -p \ --default-character-set=utf8 Products_in \ < Prd_001.sql變數：
key_buffer_size 8 MB | query_cache_size 0 MB | innodb_buffer_pool_size 49152 MB | innodb_additional_mem_pool_size 0 MB | innodb_log_buffer_size 6144 MB | BASE MEMORY 55304 MB | sort_buffer_size 0.250 MB | read_buffer_size 0.125 MB | read_rnd_buffer_size 0.250 MB | join_buffer_size 0.250 MB | thread_stack 0.273 MB | binlog_cache_size 0.031 MB | tmp_table_size 16 MB | MEMORY PER CONNECTION 17.180 MB | Max_used_connections 5 | max_connections 151 | TOTAL (MIN) 55389.898 MB | TOTAL (MAX) 57898.133 MB |SHOW ENGINE INNODB status \G; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2022-02-23 11:59:21 0x7f5571487700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 11 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 13799 srv_active, 0 srv_shutdown, 243 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 115564 OS WAIT ARRAY INFO: signal count 113111 RW-shared spins 21, rounds 22, OS waits 1 RW-excl spins 17036, rounds 510461, OS waits 17030 RW-sx spins 537, rounds 16063, OS waits 534 Spin rounds per wait: 1.05 RW-shared, 29.96 RW-excl, 29.91 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 675545 Purge done for trx's n:o < 674572 undo n:o < 0 state: running but idle History list length 29 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421501552888496, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 675544, ACTIVE 44 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 6835 MySQL thread id 76, OS thread handle 140004949817088, query id 7577 localhost root update INSERT INTO `saldoclientecca` VALUES ('2021-08-06 00:00:00',369,23,12701133,2,1,6,'93222',840.4300,'2021-08-07 08:37:44','2021-08-07 00:00:00'),('2021-08-06 00:00:00',369,3,7760333,2,1,13,'93470',1445.3300,'2021-08-07 08:37:44','2021-08-07 00:00:00'),('2021-08-06 00:00:00',369,3,7760333,2,1,6,'93470',1445.3300,'2021-08-07 08:37:44','2021-08-07 00:00:00'),('2021-08-06 00:00:00',369,51,3719333,2,1,13,'102634',61.3400,'2021-08-07 08:37:44','2021-08-07 00:00:00'),('2021-08-06 00:00:00',369,46,12700588,2,1,13,'93182',833.3300,'2021-08-07 08:37:44','2021-08-07 00:00:00'),('2021-08-06 00:00:00',369,5 -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [35, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 3 13922 OS file reads, 33040400 OS file writes, 786283 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 254.89 writes/s, 8.91 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 3093, seg size 3095, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 12750011, node heap has 13 buffer(s) Hash table size 12750011, node heap has 11 buffer(s) Hash table size 12750011, node heap has 17 buffer(s) Hash table size 12750011, node heap has 7 buffer(s) Hash table size 12750011, node heap has 37779 buffer(s) Hash table size 12750011, node heap has 1 buffer(s) Hash table size 12750011, node heap has 13 buffer(s) Hash table size 12750011, node heap has 163 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 372567137685 Log buffer assigned up to 372567137685 Log buffer completed up to 372567137685 Log written up to 372567137685 Log flushed up to 372567137685 Added dirty pages up to 372567137685 Pages flushed up to 372486561542 Last checkpoint at 372486561542 12690136 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 52747567104 Dictionary memory allocated 60770569 Buffer pool size 3145728 Free buffers 2426262 Database pages 681462 Old database pages 250574 Modified db pages 117734 Pending reads 0 Pending writes: LRU 0, flush list 37, single page 0 Pages made young 56588, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 13897, created 667568, written 19834618 0.00 reads/s, 0.00 creates/s, 254.98 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 681462, unzip_LRU len: 0 I/O sum[577104]:cur[5472], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 65536 Free buffers 50583 Database pages 14161 Old database pages 5207 Modified db pages 1822 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1848, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 279, created 13882, written 439188 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 14161, unzip_LRU len: 0 I/O sum[12023]:cur[114], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 65536 Free buffers 50694 Database pages 14049 Old database pages 5166 Modified db pages 2463 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 544, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 339, created 13710, written 387091 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 14049, unzip_LRU len: 0 I/O sum[12023]:cur[114], unzip sum[0]:cur[0] ---BUFFER POOL 2 a´te o POLL 47 é a mesma resposta (ocultei por limitação de carater) -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=4782, Main thread ID=139972405339904 , state=checking free log space Number of rows inserted 64106963, updated 1578, deleted 52, read 197358 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================CREATE TABLE `Prd_001` ( `DTSLD` datetime NOT NULL, `INSNAME` int(10) NOT NULL, `IDUNIDADEINST` int(10) NOT NULL, `IDCLI` int(10) NOT NULL, `IDPRO` smallint(5) NOT NULL, `IDMODAl` smallint(5) NOT NULL, `CODTIPOSALDO` smallint(5) NOT NULL, `IDENTNEG` varchar(50) NOT NULL, `TOTALSLO` decimal(19,4) NOT NULL, `DTHRR` datetime DEFAULT NULL, `DTCRA` datetime DEFAULT NULL, KEY `idx_IDCLIENTE` (`IDCLI`), KEY `idx_DTSLD` (`DTSMNY`), KEY `idx_CODTIPOSALDO` (`CODTPSLDO`), KEY `idx_TEMP` (`IDCLI`,`DTSMNY`,`CODTPSLDO`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE沒有這些索引。LOAD DATAALTER TABLE .. ADD INDEX ...