MySQL UPDATE 查詢停留在“發送數據”狀態

September 25, 2015

UPDATE由於程序卡在“發送數據”狀態，我很難成功執行查詢。

我覺得這與關於“發送數據”狀態的類似問題不同，因為它發生在 UPDATE 查詢期間，MySQL文件讓我相信不應該發生這種情況。（？）

這是兩個相關的表格：

CREATE TABLE `CustomerVisits` (
 `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `OrderID` INT(10) UNSIGNED NOT NULL,
 `MerchantID` INT(10) UNSIGNED NOT NULL,
 `LocationID` INT(10) UNSIGNED NOT NULL,
 `CTime` INT(10) UNSIGNED NOT NULL,
 `CustomerID` INT(10) UNSIGNED NULL DEFAULT NULL,
 `SinceLastVisit` INT(10) UNSIGNED NULL DEFAULT NULL,
 PRIMARY KEY (`ID`),
 INDEX `CTime` (`CTime`) USING BTREE,
 INDEX `LocationID_OrderID_CTime` (`LocationID`, `OrderID`, `CTime`) USING BTREE,
 INDEX `OrderID` (`OrderID`),
 INDEX `CustomerID` (`CustomerID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

CREATE TABLE `VisitDiffTimes` (
 `DiffTime` DOUBLE NULL DEFAULT NULL,
 `OrderID` INT(10) UNSIGNED NOT NULL,
 `MerchantID` INT(10) UNSIGNED NOT NULL DEFAULT '0',
 `CustomerID` INT(10) UNSIGNED NOT NULL DEFAULT '0',
 `CTime` INT(10) UNSIGNED NULL DEFAULT NULL,
 PRIMARY KEY (`MerchantID`, `OrderID`, `CustomerID`) USING HASH,
 INDEX `DiffTime` (`DiffTime`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

CustomerVisits有 1200 萬行，並且VisitDiffTimes有 400 萬行。

該CustomerVisits.SinceLastVisit列目前是全部NULL- 我想用以下查詢填充它：

UPDATE CustomerVisits V
 JOIN VisitDiffTimes D
   ON V.OrderID = D.OrderID AND V.CustomerID = D.CustomerID
  SET V.SinceLastVisit = D.DiffTime

此查詢的輸出EXPLAIN是：

id  select_type  table  type   possible_keys       key       key_len  ref        rows     Extra
1   SIMPLE       D      index  NULL                DiffTime  9        NULL       4065127  Using index
1   SIMPLE       V      ref    OrderID,CustomerID  OrderID   4        D.OrderID  1        Using where

我的查詢或表定義是否有問題可以使此操作更快？

更新

我已經在下面實現了 RolandoMySQLDBA 的建議，但不幸的是，查詢仍在無休止地執行。

我已將SHOW ENGINE InnoDB STATUS. 我看到引擎正在報告0.00 updates/s，所以我擔心這個查詢不會在任何合理的時間範圍內完成。

=====================================
2015-09-24 15:55:40 7f9d02217700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 7 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 65705 srv_active, 0 srv_shutdown, 46262 srv_idle
srv_master_thread log flush and writes: 111963
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2282726
OS WAIT ARRAY INFO: signal count 3647755
Mutex spin waits 3344497, rounds 28268630, OS waits 400079
RW-shared spins 2898160, rounds 69659833, OS waits 1747791
RW-excl spins 365077, rounds 10516470, OS waits 86454
Spin rounds per wait: 8.45 mutex, 24.04 RW-shared, 28.81 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 26361458
Purge done for trx's n:o &lt; 26361455 undo n:o &lt; 0 state: running but idle
History list length 1926
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 1595, OS thread handle 0x7f9d02217700, query id 70163629 (IP Address) (Username) init
SHOW ENGINE InnoDB STATUS
---TRANSACTION 26361457, ACTIVE 2764 sec fetching rows
mysql tables in use 2, locked 2
50350 lock struct(s), heap size 4617768, 181942 row lock(s)
MySQL thread id 1579, OS thread handle 0x7f9d02299700, query id 70163374 (IP Address) (Username) Sending data
UPDATE SaleOrderPayment P
 JOIN SaleOrderPayment_Diff_Merchant T
   ON P.OrderID = T.OrderID AND P.CustomerID = T.CustomerID
  SET P.SinceLastVisitMerchant = T.DiffTime
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
137002174 OS file reads, 20383973 OS file writes, 844094 OS fsyncs
18277.25 reads/s, 16771 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 2723, seg size 2725, 5175728 merges
merged operations:
insert 13665166, delete mark 8438509, delete 8253
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 415109, node heap has 71 buffer(s)
443.79 hash searches/s, 32426.94 non-hash searches/s
---
LOG
---
Log sequence number 125059941125
Log flushed up to   125059941125
Pages flushed up to 125059941125
Last checkpoint at  125059941125
0 pending log writes, 0 pending chkp writes
11664686 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 214630400; in additional pool allocated 0
Dictionary memory allocated 172905
Buffer pool size   12799
Free buffers       0
Database pages     12447
Old database pages 4602
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1352321, not young 1300347433
0.00 youngs/s, 147255.82 non-youngs/s
Pages read 144210003, created 530043, written 12368276
18709.33 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 876 / 1000, young-making rate 0 / 1000 not 983 / 1000
Pages read ahead 438.79/s, evicted without access 66.99/s, Random read ahead 0.00/s
LRU len: 12447, unzip_LRU len: 0
I/O sum[915059]:cur[17449], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 24315, id 140312316348160, state: sleeping
Number of rows inserted 36456765, updated 13112249, deleted 16214487, read 688045738
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 32871.02 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

更新 2

EXPLAIN以下是遵循 RolandoMySQLDBA 的以下建議後的更新輸出。

id  select_type  table  type   possible_keys                  key         key_le   ref           rows     Extra
1   SIMPLE       D      index  PRIMARY                        DiffTime    9        NULL          4044212  Using index
1   SIMPLE       V      ref    CustomerID,OrderID_CustomerID  CustomerID  5        D.CustomerID  1        Using where

更新 3

我在下面實現了 RolandoMySQLDBA 的建議 4；更新後的EXPLAIN輸出是：

id  select_type  table  type   possible_keys                          key                 key_len  ref                     rows     Extra
1   SIMPLE       D      index  PRIMARY                                DiffTime            9        NULL                    4044212  Using index
1   SIMPLE       V      ref    OrderID_CustomerID,CustomerID_OrderID  OrderID_CustomerID  9        D.OrderID,D.CustomerID  1        NULL

問題
在 EXPLAIN 計劃中，select_type是SIMPLE.
這是一個完整的索引掃描VisitDiffTimes和範圍掃描CustomerVisits。
建議 #1
您需要重新排序 PRIMARY KEY 中的列VisitDiffTimes
ALTER TABLE VisitDiffTimes DROP PRIMARY KEY;
ALTER TABLE VisitDiffTimes ADD PRIMARY KEY (`OrderID`, `CustomerID`, `MerchantID`);
這將消除完全掃描VisitDiffTimes
建議 #2（可選）
在這兩列上創建一個複合索引CustomerVisits
ALTER TABLE CustomerVisits
   DROP INDEX OrderID,
   ADD INDEX OrderID_CustomerID_ndx (OrderID,CustomerID)
;
JOIN 可能會更乾淨
建議#3（可選）
將 join_buffer_size 設置為 16M
將此添加到my.cnf
[mysqld]
join_buffer_size = 16M
您不必重新啟動。只需登錄root@localhost並執行
mysql&gt; SET GLOBAL join_buffer_size = 16 * 1024 * 1024;
試一試！！！
建議#4（可選）
另一個帶列的索引(CustomerID,OrderID)
ALTER TABLE CustomerVisits
   DROP INDEX CustomerID,
   ADD INDEX CustomerID_OrderID_ndx (CustomerID,OrderID)
;

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

MySQL UPDATE 查詢停留在“發送數據”狀態

更新

更新 2

更新 3

問題

建議 #1

建議 #2（可選）

建議#3（可選）

試一試！！！

建議#4（可選）

相關問答

有索引的查詢比沒有索引的要慢

優化 MySQL 更新語句

優化表和查詢，按日期或日期-小時聚合

MySQL (InnoDB) UPDATE SET 性能與 WHERE AND 子句中的值

MySQL 崩潰，目前 error.log 文件很大

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

MySQL UPDATE 查詢停留在“發送數據”狀態

更新

更新 2

更新 3

問題

建議 #1

建議 #2（可選）

建議#3（可選）

試一試 ！！！

建議#4（可選）

相關問答

有索引的查詢比沒有索引的要慢

優化 MySQL 更新語句

優化表和查詢，按日期或日期-小時聚合

MySQL (InnoDB) UPDATE SET 性能與 WHERE AND 子句中的值

MySQL 崩潰，目前 error.log 文件很大

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

試一試！！！