请大家帮我看看这个怪异的MYSQL现象!

antonyhot

MYSQL版本:4.1.2

INSERT INTO T_M_CATEGORY_SEN
(RBID, CATE6_DIV, LOCALCATEID, CONSOLICATEID, EXP_DATE_FROM, EXP_DATE_TO,
CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, GENEID)
SELECT e.RBID, 1, d.LOCALCATEID, d.CONSOLICATEID, d.EXP_DATE_FROM, d.EXP_DATE_TO,
d.CREATE_USER, d.CREATE_DATE, d.UPDATE_USER, d.UPDATE_DATE, d.GENEID
FROM (SELECT * FROM T_M_CONSOLICATE a, T_M_LOCALCATE b, T_M_6DIGIT2 c
WHERE a.CON_CATE_CODE = b.LOC_CATE_CODE and c.CATE6_CODE = a.CON_CATE_CODE) d,
(SELECT RBID FROM T_M_REGIONB) e
WHERE NOT EXISTS
(SELECT * FROM T_M_CATEGORY_SEN f WHERE
d.LOCALCATEID = f.LOCALCATEID and d.CONSOLICATEID = f.CONSOLICATEID
AND e.RBID = f.RBID
group by f.LOCALCATEID, f.CONSOLICATEID);

上面的这个SQL语句,在MYSQL上有时执行两个小时,有时却只执行一分钟.用show processlist监视SQL运行时的情况,发现

执行两个小时的情况下:State一直为Sending data
而执行一分钟的情况下:State先为Sending data(约5秒钟),然后变为Copying to tmp table

用EXPLAIN 解析SELECT 语句,显示以下信息

id,"select_type","table","type",possible_keys,key,key_len,ref,rows,"Extra"
1,"PRIMARY","<derived2>","ALL",NULL,NULL,NULL,NULL,377,""
1,"PRIMARY","<derived3>","ALL",NULL,NULL,NULL,NULL,91,"Using where"
4,"DEPENDENT SUBQUERY","f","ref","T_M_CATEGORY_SEN_FK1,T_M_CATEGORY_SEN_FK2","T_M_CATEGORY_SEN_FK1",5,"d.LOCALCATEID",69,"Using where; Using temporary; Using filesort"
3,"DERIVED","T_M_REGIONB","index",NULL,"PRIMARY",4,NULL,91,"Using index"
2,"DERIVED","c","ALL",NULL,NULL,NULL,NULL,321,""
2,"DERIVED","a","ALL",NULL,NULL,NULL,NULL,3649,"Using where"
2,"DERIVED","b","ALL",NULL,NULL,NULL,NULL,3653,"Using where"


请问为什么这个SQL语句时快时慢?何解?

P.S. 这个SQL执行前会有DB的备份,备份命令为mysqldump DBNAME -h IP -P PORT --set-charset=utf-8 -u USERNAME --password=PASSWORD > F:\DBBackUp\NAME.sql
请问如这个操作有关吗?

Redecor

这个应该是和你备份的远程机器有关

antonyhot

应该跟备份没有关系了，两个没有交叉。

antonyhot

没有人回答

antonyhot

没有人知道