查询结束概念

Question

我的表MYTABLE包含1400万条记录数据

CREATE MYTABLE `MYTABLE` (
`COLUM1` int(10) unsigned NOT NULL DEFAULT '0',
`COLUM2` int(10) unsigned NOT NULL DEFAULT '0',
`COLUM4` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`COLUM3` char(1) NOT NULL,
 UNIQUE KEY `COLUM2` (`COLUM2`,`COLUM1`),
 KEY `COLUM1` (`COLUM1`)
) ENGINE=InnoDB

显示给定时间的进程列表

 | 318 1204333 | user | xx.xxx.xx.xxx:24396 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11285294','9765238',now())              | 
 | 318 1204334 | user | xx.xxx.xx.xxx:38671  | newjs | Query       |        0 | Updating                                                              | UPCOLUM4 newjs.MYTABLE SET COLUM3='Y' WHERE COLUM1=11200140 AND COLUM2=10927439                 | 
 | 318 1204335 | user | xx.xxx.xx.xxx:35259 | newjs | Query       |        0 | query end                                                             | UPCOLUM4 newjs.MYTABLE SET COLUM3='Y' WHERE COLUM1=8667546 AND COLUM2=9731954                   | 
 | 318 1204337 | user | xx.xxx.xx.xxx:24405 | newjs | Sleep       |        0 |                                                                       | NULL                                                                                                 | 
 | 318 1204339 | user | xx.xxx.xx.xxx:24412 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11267957','11250673',now())             | 
 | 318 1204342 | user | xx.xxx.xx.xxx:35287 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11229209','9873622',now())              | 
 | 318 1204343 | user | xx.xxx.xx.xxx:38681  | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('10157460','10967184',now())             | 
 | 318 1204347 | user | xx.xxx.xx.xxx:24424 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11268751','9423745',now())              | 
 | 318 1204348 | user | xx.xxx.xx.xxx:35316 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11268611','9263111',now())              | 
 | 318 1204349 | user | xx.xxx.xx.xxx:35319 | newjs | Sleep       |        0 |                                                                       | NULL                                                                                                 | 
 | 318 1204350 | user | xx.xxx.xx.xxx:35345 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('9049736','9585434',now())               | 
 | 318 1204352 | user | xx.xxx.xx.xxx:35375 | newjs | Sleep       |        0 |                                                                       | NULL                                                                                                 | 
 | 318 1204353 | user | xx.xxx.xx.xxx:35376 | newjs | Query       |        0 | uptime                                                                | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('10578170','9843201',now())              | 
 | 318 1204354 | user | xx.xxx.xx.xxx:35379 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('8445209','11055214',now())              | 
 | 318 1204355 | user | xx.xxx.xx.xxx:24485 | newjs | Query       |        0 | uptime                                                                | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('9699909','11236269',now())              | 
 | 318 1204357 | user | xx.xxx.xx.xxx:35396 | newjs | Sleep       |        0 |                                                                       | NULL                                                                                                 | 
 | 318 1204359 | user | xx.xxx.xx.xxx:24531 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('8925641','7987794',now())               | 
 | 318 1204360 | user | xx.xxx.xx.xxx:24550 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11242379','9740270',now())              | 
 | 318 1204362 | user | xx.xxx.xx.xxx:35464 | newjs | Sleep       |        0 |                                  

Answer 1

" END"连接状态用于写入二进制日志记录、查询缓存操作和其他操作.."MySQL

在InnoDB表上等待事务回滚可能是一个线程在kill或query end停留较长时间的最常见原因。这有时需要花费很多时间来完成，特别是当必须删除数十万或数百万个更改时。

"实际上，有两种不同的情况。如果发出KILL，则进程列表中会有被杀死的进程。KILL QUERY 不会终止连接，而只是停止连接中正在运行的查询，因此在这种情况下，可能会出现查询结束文本。

许多人报告说他们在"The End"-State中有性能问题。修复:

• 更新MySQL

• 减少缓存