MySQL Forums
Forum List  »  Chinese

请大家帮我看看这个怪异的MYSQL现象!
Posted by: Chen Bo
Date: December 03, 2007 12:18AM

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
请问如这个操作有关吗?

Options: ReplyQuote


Subject
Views
Written By
Posted
请大家帮我看看这个怪异的MYSQL现象!
8494
December 03, 2007 12:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.