select distinct
tbd.*,
tbc.WeightExponent,tbc.BP,
tsk.SickDate,tsk.Nothing,tsk.SjtPark,tsk.XueN,tsk.SjtTime,
tpc.KUB,tpc.complication,tpc.Ksize,
tsc.Diagnosis,tsc.Pre_Cure,tsc.LocateMeth,tsc.machine1,tsc.Post,tsc.CurePart1LOrR,tsc.CureNo,
tsc.FirstDepth,tsc.FirstArea,tsc.Voltage,tsc.StrikeTimes,tsc.Feedback,tsc.tengtingzhishu,
(case trs.NoRock when 0 then '否' when 1 then '是' end) as NoRock,(case trs.ReShock when 0 then '否' when 1 then '是' end) as ReShock,(case trs.FirstShock when 0 then '否' when 1 then '是' end) as FirstShock,
trs.SWLassistcuring,trs.SWLassistcured,trs.SWLAfterAssistcure,trs.Result,
trs.syndrome,trs.compose1,trs.compose2,trs.compose3,trs.compose4,trs.metabolizecontent,trs.partcontent
from (select tm.EswlID,tb.EswlNo,tb.PName,tb.Sex,(period_diff(date_format(now(),'%Y%m'),date_format(tb.Birthday,'%Y%m')) div 12) as Age,tb.UsuallyProvince,tb.UsuallyCity,tb.Duty,DATE_FORMAT(tm.BookDate,'%Y-%m-%d') BookDate from t_basicdata tb inner join t_Main tm on tm.EswlNo=tb.EswlNo) as tbd
left join t_bodycheck tbc on tbd.EswlID=tbc.EswlID
left join t_sicknow tsk on tbd.EswlID=tsk.EswlID
left join t_piccheck tpc on tbd.EswlID=tpc.EswlID
left join t_SWLCure tsc on tbd.EswlID=tsc.EswlID
left join t_resultsurvey trs on tbd.EswlID=trs.EswlID
order by tbd.EswlNo asc,tbd.EswlID asc
当数据达到10000条的时候,查询速度非常慢要50秒以上,请高手指教!
tbd.*,
tbc.WeightExponent,tbc.BP,
tsk.SickDate,tsk.Nothing,tsk.SjtPark,tsk.XueN,tsk.SjtTime,
tpc.KUB,tpc.complication,tpc.Ksize,
tsc.Diagnosis,tsc.Pre_Cure,tsc.LocateMeth,tsc.machine1,tsc.Post,tsc.CurePart1LOrR,tsc.CureNo,
tsc.FirstDepth,tsc.FirstArea,tsc.Voltage,tsc.StrikeTimes,tsc.Feedback,tsc.tengtingzhishu,
(case trs.NoRock when 0 then '否' when 1 then '是' end) as NoRock,(case trs.ReShock when 0 then '否' when 1 then '是' end) as ReShock,(case trs.FirstShock when 0 then '否' when 1 then '是' end) as FirstShock,
trs.SWLassistcuring,trs.SWLassistcured,trs.SWLAfterAssistcure,trs.Result,
trs.syndrome,trs.compose1,trs.compose2,trs.compose3,trs.compose4,trs.metabolizecontent,trs.partcontent
from (select tm.EswlID,tb.EswlNo,tb.PName,tb.Sex,(period_diff(date_format(now(),'%Y%m'),date_format(tb.Birthday,'%Y%m')) div 12) as Age,tb.UsuallyProvince,tb.UsuallyCity,tb.Duty,DATE_FORMAT(tm.BookDate,'%Y-%m-%d') BookDate from t_basicdata tb inner join t_Main tm on tm.EswlNo=tb.EswlNo) as tbd
left join t_bodycheck tbc on tbd.EswlID=tbc.EswlID
left join t_sicknow tsk on tbd.EswlID=tsk.EswlID
left join t_piccheck tpc on tbd.EswlID=tpc.EswlID
left join t_SWLCure tsc on tbd.EswlID=tsc.EswlID
left join t_resultsurvey trs on tbd.EswlID=trs.EswlID
order by tbd.EswlNo asc,tbd.EswlID asc
当数据达到10000条的时候,查询速度非常慢要50秒以上,请高手指教!
tbd.*,
tbc.WeightExponent,tbc.BP,
tsk.SickDate,tsk.Nothing,tsk.SjtPark,tsk.XueN,tsk.SjtTime,
tpc.KUB,tpc.complication,tpc.Ksize,
tsc.Diagnosis,tsc.Pr贴出来以供分析。
1 PRIMARY <derived2> ALL 10000 Using temporary; Using filesort
1 PRIMARY tbc ref FK_Reference_4 FK_Reference_4 5 tbd.EswlID 1
1 PRIMARY tsk ref FK_Reference_2 FK_Reference_2 5 tbd.EswlID 1
1 PRIMARY tpc ref FK_Reference_10 FK_Reference_10 5 tbd.EswlID 1
1 PRIMARY tsc ref FK_Reference_11 FK_Reference_11 4 tbd.EswlID 1
1 PRIMARY trs eq_ref PRIMARY PRIMARY 4 tbd.EswlID 1
2 DERIVED tm ALL 11427
2 DERIVED tb eq_ref PRIMARY,EswlNo PRIMARY 4 dmslt.tm.EswlNo 1 Using where
这个慢不慢
这个不慢,t_mian表有建索引
select tm.EswlID,tb.EswlNo,tb.PName,tb.Sex,(period_diff(date_format(now(),'%Y%m'),date_format(tb.Birthday,'%Y%m')) div 12) as Age,tb.UsuallyProvince,tb.UsuallyCity,tb.Duty,DATE_FORMAT(tm.BookDate,'%Y-%m-%d') BookDate from t_basicdata tb inner join t_Main tm on tm.EswlNo=tb.EswlNo
做一个memory temp table试试create table tbname(xxx,xxx,,,)engine=memory
insert into tbname
select tm.EswlID,tb.EswlNo,tb.PName,tb.Sex,(period_diff(date_format(now(),'%Y%m'),date_format(tb.Birthday,'%Y%m')) div 12) as Age,tb.UsuallyProvince,tb.UsuallyCity,tb.Duty,DATE_FORMAT(tm.BookDate,'%Y-%m-%d') BookDate from t_basicdata tb inner join t_Main tm on tm.EswlNo=tb.EswlNo
CREATE TABLE `t_main` (
`EswlNo` varchar(20) NOT NULL,
`SerialNo` int(11) NOT NULL,
`BookDate` date NOT NULL,
`EswlID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`EswlID`),
KEY `EswlID` (`EswlID`),
KEY `tmain_eswlNo` (`EswlNo`)
) ENGINE=InnoDB AUTO_INCREMENT=11012 DEFAULT CHARSET=gb2312
CREATE TABLE `t_piccheck` (
`PicID` int(11) NOT NULL AUTO_INCREMENT,
`EswlID` int(11) DEFAULT NULL,
`Bultra` varchar(50) DEFAULT NULL,
`KUB` varchar(50) DEFAULT NULL,
`xno` varchar(50) DEFAULT NULL,
`IVP` varchar(50) DEFAULT NULL,
`CT` varchar(50) DEFAULT NULL,
`MRI` varchar(50) DEFAULT NULL,
`ECT` varchar(50) DEFAULT NULL,
`KLNum` varchar(10) DEFAULT NULL,
`KLMinSize` varchar(10) DEFAULT NULL,
`KLMaxSize` varchar(10) DEFAULT NULL,
`KLFisrtArea` varchar(20) DEFAULT NULL,
`KLFirstLocation` varchar(10) DEFAULT NULL,
`KLSecondMinSize` varchar(10) DEFAULT NULL,
`KLSecondMaxSize` varchar(10) DEFAULT NULL,
`KLSecondArea` varchar(20) DEFAULT NULL,
`KLSecondLocation` varchar(10) DEFAULT NULL,
`KLsxzkz` bit(1) DEFAULT NULL,
`KRSWS` bit(1) DEFAULT NULL,
`ULSWS` bit(1) DEFAULT NULL,
`URSWS` bit(1) DEFAULT NULL,
`KLsdzkz` bit(1) DEFAULT NULL,
`KLqdjs` bit(1) DEFAULT NULL,
`KLMiddjs` bit(1) DEFAULT NULL,
`KLHighdjs` bit(1) DEFAULT NULL,
`KLnlgr` bit(1) DEFAULT NULL,
`KLSWS` bit(1) DEFAULT NULL,
`KLsgnsh` bit(1) DEFAULT NULL,
`KRNum` varchar(10) DEFAULT NULL,
`KRMinSize` varchar(10) DEFAULT NULL,
`KRFisrtArea` varchar(20) DEFAULT NULL,
`KRMaxSize` varchar(10) DEFAULT NULL,
`KRFirstLocation` varchar(10) DEFAULT NULL,
`KRSecondMinSize` varchar(10) DEFAULT NULL,
`KRSecondMaxSize` varchar(10) DEFAULT NULL,
`KRSecondArea` varchar(20) DEFAULT NULL,
`KRSecondLocation` varchar(10) DEFAULT NULL,
`ULNum` varchar(10) DEFAULT NULL,
`ULMinSize` varchar(10) DEFAULT NULL,
`ULMaxSize` varchar(10) DEFAULT NULL,
`ULFirstLocation` varchar(10) DEFAULT NULL,
`ULSecondMinSize` varchar(10) DEFAULT NULL,
`ULSecondMaxSize` varchar(10) DEFAULT NULL,
`ULSecondLocation` varchar(10) DEFAULT NULL,
`ULqdjs` bit(1) DEFAULT NULL,
`ULMiddjs` bit(1) DEFAULT NULL,
`ULHighdjs` bit(1) DEFAULT NULL,
`ULnlgr` bit(1) DEFAULT NULL,
`ULsgnsh` bit(1) DEFAULT NULL,
`URNum` varchar(10) DEFAULT NULL,
`URMinSize` varchar(10) DEFAULT NULL,
`URMaxSize` varchar(10) DEFAULT NULL,
`ULFirstArea` varchar(20) DEFAULT NULL,
`URFirstLocation` varchar(10) DEFAULT NULL,
`URSecondMinSize` varchar(10) DEFAULT NULL,
`URSecondMaxSize` varchar(10) DEFAULT NULL,
`ULSecondArea` varchar(20) DEFAULT NULL,
`URSecondLocation` varchar(10) DEFAULT NULL,
`URqdjs` bit(1) DEFAULT NULL,
`URMiddjs` bit(1) DEFAULT NULL,
`URHighdjs` bit(1) DEFAULT NULL,
`URnlgr` bit(1) DEFAULT NULL,
`URsgnsh` bit(1) DEFAULT NULL,
`UTNum` varchar(10) DEFAULT NULL,
`UTMinSize` varchar(10) DEFAULT NULL,
`UTMaxSize` varchar(10) DEFAULT NULL,
`URFirstArea` varchar(20) DEFAULT NULL,
`UTFirstLocation` varchar(10) DEFAULT NULL,
`UTSecondMinSize` varchar(10) DEFAULT NULL,
`UTSecondMaxSize` varchar(10) DEFAULT NULL,
`URSecondArea` varchar(20) DEFAULT NULL,
`UTSecondLocation` varchar(10) DEFAULT NULL,
`UTnzl` bit(1) DEFAULT NULL,
`UTnlgr` bit(1) DEFAULT NULL,
`BTNum` varchar(10) DEFAULT NULL,
`BTMinSize` varchar(10) DEFAULT NULL,
`BTMaxSize` varchar(10) DEFAULT NULL,
`UTFirstArea` varchar(20) DEFAULT NULL,
`BTFirstLocation` varchar(10) DEFAULT NULL,
`BTSecondMinSize` varchar(10) DEFAULT NULL,
`BTSecondMaxSize` varchar(10) DEFAULT NULL,
`UTSecondArea` varchar(20) DEFAULT NULL,
`BTSecondLocation` varchar(10) DEFAULT NULL,
`KUBShape` varchar(200) DEFAULT NULL,
`Ksize` varchar(200) DEFAULT NULL,
`partdns` bit(1) DEFAULT NULL,
`BTFirstArea` varchar(20) DEFAULT NULL,
`partcfs` bit(1) DEFAULT NULL,
`partmts` bit(1) DEFAULT NULL,
`parthms` bit(1) DEFAULT NULL,
`BTSecondArea` varchar(20) DEFAULT NULL,
`BTnzl` bit(1) DEFAULT NULL,
`BTnlgr` bit(1) DEFAULT NULL,
`szxs` bit(1) DEFAULT NULL,
`snz` bit(1) DEFAULT NULL,
`upjxz` bit(1) DEFAULT NULL,
`yws` bit(1) DEFAULT NULL,
`partnlxz` bit(1) DEFAULT NULL,
`sngnz` bit(1) DEFAULT NULL,
`pgxs` bit(1) DEFAULT NULL,
`sjyxpg` bit(1) DEFAULT NULL,
`BPH` bit(1) DEFAULT NULL,
`partnlgr` bit(1) DEFAULT NULL,
`partnlgz` bit(1) DEFAULT NULL,
`ndxs` bit(1) DEFAULT NULL,
`partqt` bit(1) DEFAULT NULL,
`partqtContent` varchar(50) DEFAULT NULL,
`Operator` varchar(10) DEFAULT NULL COMMENT '为空时,保存当前登录用户',
`Operatdate` varchar(10) DEFAULT NULL COMMENT '为空时,记录当前日期',
`Photo` mediumblob COMMENT '二进制保存,后期还涉及到术后图片及图片获取,编辑',
`Reserve1` varchar(30) DEFAULT NULL,
`Reserve2` varchar(30) DEFAULT NULL,
`Reserve3` varchar(30) DEFAULT NULL,
`KRsxzkz` bit(1) DEFAULT NULL,
`KRsdzkz` bit(1) DEFAULT NULL,
`KRqdjs` bit(1) DEFAULT NULL,
`KRMiddjs` bit(1) DEFAULT NULL,
`KRHighdjs` bit(1) DEFAULT NULL,
`KRnlgr` bit(1) DEFAULT NULL,
`KRsgnsh` bit(1) DEFAULT NULL,
`KLcomplication` varchar(200) DEFAULT NULL,
`KRcomplication` varchar(200) DEFAULT NULL,
`ULcomplication` varchar(200) DEFAULT NULL,
`URcomplication` varchar(200) DEFAULT NULL,
`BTcomplication` varchar(200) DEFAULT NULL,
`UTcomplication` varchar(200) DEFAULT NULL,
`complication` varchar(200) DEFAULT NULL,
`KLkub` varchar(10) DEFAULT NULL,
`KRkub` varchar(10) DEFAULT NULL,
`ULkub` varchar(10) DEFAULT NULL,
`URkub` varchar(10) DEFAULT NULL,
`BTkub` varchar(10) DEFAULT NULL,
`UTkub` varchar(10) DEFAULT NULL,
`KLkubsize` varchar(10) DEFAULT NULL,
`KRkubsize` varchar(10) DEFAULT NULL,
`ULkubsize` varchar(10) DEFAULT NULL,
`URkubsize` varchar(10) DEFAULT NULL,
`BTkubsize` varchar(10) DEFAULT NULL,
`UTkubsize` varchar(10) DEFAULT NULL,
PRIMARY KEY (`PicID`),
KEY `FK_Reference_10` (`EswlID`),
CONSTRAINT `FK_Reference_10` FOREIGN KEY (`EswlID`) REFERENCES `t_main` (`EswlID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
((select tm.EswlID,tb.EswlNo,tb.PName,tb.Sex,(period_diff(date_format(now(),'%Y%m'),date_format(tb.Birthday,'%Y%m')) div 12) as Age,tb.UsuallyProvince,tb.UsuallyCity,tb.Duty,DATE_FORMAT(tm.BookDate,'%Y-%m-%d') BookDate from t_basicdata tb inner join t_Main tm on tm.EswlNo=tb.EswlNo) as tbd
left join t_piccheck tpc on tbd.EswlID=tpc.EswlID执行速度如何,EXPLAIN一下,看看是否用到索引
执行速度1.641S explain后的结果为:
1 PRIMARY <derived2> ALL 10000
1 PRIMARY tpc ref FK_Reference_10 FK_Reference_10 5 tbd.EswlID 1
2 DERIVED tm ALL tmain_eswlNo 11423
2 DERIVED tb eq_ref PRIMARY,EswlNo PRIMARY 4 dmslt.tm.EswlNo 1 Using where
可你这要用到left join呀,