这些是表的结构：
CREATE TABLE rs_emp (EmpSysID varchar(36) NOT NULL,EmpNo varchar(20) NOT NULL,EmpName varchar(50) NOT NULL DEFAULT '',EmpSexSysID varchar(36) DEFAULT NULL,CardTypeSysID varchar(36) DEFAULT NULL,RateSysID varchar(36) DEFAULT NULL,JobSysID varchar(36) DEFAULT NULL,DepartSysID varchar(36) NOT NULL,StatusSysID varchar(36) DEFAULT NULL,EmpEngName varchar(50) DEFAULT NULL,EmpHireDate datetime DEFAULT NULL,EmpPositiveDate datetime DEFAULT NULL,EmpLeaveDate datetime DEFAULT NULL,EmpLeaveReason varchar(255) DEFAULT NULL,EmpStatusID tinyint(4) DEFAULT NULL,EmpMemo varchar(200) DEFAULT NULL,CertTypeSysID varchar(36) DEFAULT NULL,EmpCertNo varchar(50) DEFAULT NULL,EmpBirthDate varchar(50) DEFAULT NULL,CountrySysID varchar(36) DEFAULT NULL,NationSysID varchar(36) DEFAULT NULL,EduSysID varchar(36) DEFAULT NULL,EmpGdSchool varchar(200) DEFAULT NULL,PolitySysID varchar(36) DEFAULT NULL,EmpLanguageAbility varchar(200) DEFAULT NULL,EmpMarrySysID varchar(36) DEFAULT NULL,EmpZipNo varchar(50) DEFAULT NULL,EmpPhoneNo varchar(50) DEFAULT NULL,NativeSysID varchar(36) DEFAULT NULL,EmpAddress varchar(200) DEFAULT NULL,EmpEmail varchar(200) DEFAULT NULL,EmpFamilyInfo varchar(200) DEFAULT NULL,EmpBiography mediumtext,AllowDelete bit(1) DEFAULT b'1',EmpLeaveStatus bit(1) DEFAULT b'0',IsAttend char(1) DEFAULT NULL,OtherCardNo varchar(10) DEFAULT NULL,CardFingerNo bigint(10) DEFAULT NULL,
PRIMARY KEY (EmpSysID),
UNIQUE KEY AK_RS_Emp (EmpNo),
KEY AK_RS_Emp2 (EmpSysID),
KEY AK_RS_Emp3 (EmpName),
KEY AK_RS_Emp6 (RateSysID),
KEY AK_RS_Emp7 (StatusSysID),
KEY AK_RS_Emp8 (CountrySysID),
KEY AK_RS_Emp9 (CertTypeSysID),
KEY AK_RS_Emp10 (EduSysID),
KEY AK_RS_Emp11 (PolitySysID),
KEY AK_RS_Emp12 (NationSysID),
KEY AK_RS_Emp23 (EmpSysID,EmpNo),
KEY AK_RS_Emp33 (EmpLeaveStatus),
KEY AK_RS_Emp22 (EmpSysID,EmpName)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
CREATE TABLE rs_depart (DepartSysID varchar(36) NOT NULL,DepartPrcID varchar(100) NOT NULL,DepartID varchar(12) NOT NULL,DepartName varchar(50) NOT NULL,DepartUpSysId varchar(36) DEFAULT NULL,DepartMemo varchar(200) DEFAULT NULL,
PRIMARY KEY (DepartSysID),
UNIQUE KEY AK_RS_Depart (DepartID) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=gbk
CREATE TABLE rs_classcerttype (CertTypeSysID varchar(36) NOT NULL,CertTypeNo varchar(20) NOT NULL,CertTypeName varchar(50) NOT NULL,
PRIMARY KEY (CertTypeSysID),
UNIQUE KEY AK_RS_ClassCertType (CertTypeNo)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
CREATE TABLE rs_classcountry (CountrySysID varchar(36) NOT NULL,CountryNo varchar(20) NOT NULL,CountryName varchar(50) NOT NULL,
PRIMARY KEY (CountrySysID),
UNIQUE KEY AK_RS_ClassCountry (CountryNo)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
跟rs_classcountry类似结构的表还有几张
最后创建视图，为这几张表的查询结果，视图：
CREATE ALGORITHM=UNDEFINED DEFINER=root@% SQL SECURITY DEFINER VIEW vrs_emp AS select a.EmpSysID AS EmpSysID,a.EmpNo AS EmpNo,a.EmpName AS EmpName,a.CardFingerNo AS CardFingerNo,a.EmpSexSysID AS EmpSexSysID,a.CardTypeSysID AS CardTypeSysID,a.RateSysID AS RateSysID,a.JobSysID AS JobSysID,a.DepartSysID AS DepartSysID,a.StatusSysID AS StatusSysID,a.EmpEngName AS EmpEngName,a.EmpHireDate AS EmpHireDate,a.EmpPositiveDate AS EmpPositiveDate,a.EmpLeaveDate AS EmpLeaveDate,a.EmpLeaveReason AS EmpLeaveReason,a.EmpStatusID AS EmpStatusID,a.EmpMemo AS EmpMemo,a.CertTypeSysID AS CertTypeSysID,a.EmpCertNo AS EmpCertNo,a.EmpBirthDate AS EmpBirthDate,a.CountrySysID AS CountrySysID,a.NationSysID AS NationSysID,a.EduSysID AS EduSysID,a.EmpGdSchool AS EmpGdSchool,a.PolitySysID AS PolitySysID,a.EmpLanguageAbility AS EmpLanguageAbility,a.EmpMarrySysID AS EmpMarrySysID,a.EmpZipNo AS EmpZipNo,a.EmpPhoneNo AS EmpPhoneNo,a.NativeSysID AS NativeSysID,a.EmpAddress AS EmpAddress,a.EmpEmail AS EmpEmail,a.EmpFamilyInfo AS EmpFamilyInfo,a.EmpBiography AS EmpBiography,a.AllowDelete AS AllowDelete,a.EmpLeaveStatus AS EmpLeaveStatus,a.IsAttend AS IsAttend,a.OtherCardNo AS OtherCardNo,(to_days(now()) - to_days(a.EmpPositiveDate)) AS EmpPositiveDay,floor(((to_days(curdate()) - to_days(a.EmpBirthDate)) / 365)) AS EmpAge,b.DepartID AS DepartID,b.DepartName AS DepartName,c.StatusNo AS StatusNo,c.StatusName AS StatusName,d.RateNo AS RateNo,d.RateName AS RateName,e.PolityNo AS PolityNo,e.PolityName AS PolityName,f.NativeNo AS NativeNo,f.NativeName AS NativeName,g.NationNo AS NationNo,g.NationName AS NationName,h.JobNo AS JobNo,h.JobName AS JobName,i.EduNo AS EduNo,i.EduName AS EduName,j.CountryNo AS CountryNo,j.CountryName AS CountryName,k.CertTypeNo AS CertTypeNo,k.CertTypeName AS CertTypeName,m.EmpSexID AS EmpSexID,m.EmpSexName AS EmpSexName,n.EmpStatusName AS EmpStatusName,o.EmpMarryID AS EmpMarryID,o.EmpMarryName AS EmpMarryName,p.CardPhysicsNo10 AS CardPhysicsNo10,p.CardPhysicsNo8 AS CardPhysicsNo8,p.CardSectorNo AS CardSectorNo,p.CardStatusID AS CardStatusID,p.CardStatusDate AS CardStatusDate,p.CardPWD AS CardPWD,p.CardStartDate AS CardStartDate,p.CardEndDate AS CardEndDate,p.CardUseDate AS CardUseDate,p.CardUseTimes AS CardUseTimes,p.RetirementFlag AS RetirementFlag,p.CardBTMoney AS CardBTMoney,p.FaDate AS FaDate,q.CardStatusName AS CardStatusName,r.EmpPhotoPath AS EmpPhotoPath from ((((((((((((((((rs_emp a join rs_depart b on((b.DepartSysID = a.DepartSysID))) left join rs_classstatus c on((c.StatusSysID = a.StatusSysID))) left join rs_classrate d on((d.RateSysID = a.RateSysID))) left join rs_classpolity e on((e.PolitySysID = a.PolitySysID))) left join rs_classnative f on((f.NativeSysID = a.NativeSysID))) left join rs_classnation g on((g.NationSysID = a.NationSysID))) left join rs_classjob h on((h.JobSysID = a.JobSysID))) left join rs_classeducation i on((i.EduSysID = a.EduSysID))) left join rs_classcountry j on((j.CountrySysID = a.CountrySysID))) left join rs_classcerttype k on((k.CertTypeSysID = a.CertTypeSysID))) left join vrs_empsex m on((m.EmpSexSysID = a.EmpSexSysID))) left join vrs_empstatus n on((n.EmpStatusID = a.EmpStatusID))) left join vrs_empmarrystatus o on((o.EmpMarrySysID = a.EmpMarrySysID))) left join rs_empcard p on((p.EmpSysID = a.EmpSysID))) left join vrs_empcardstatus q on((p.CardStatusID = q.CardStatusID))) left join rs_empphoto r on((a.EmpSysID = r.EmpSysID))) where (a.EmpLeaveStatus <> 1)
当我在mysql 用explain执行了一下 explain select * from vrs_emp where empsysid<>'' order by empno limit 0,30
出现
1 SIMPLE b system PRIMARY 1Using temporary; Using filesort
该如何优化？？都有索引
