最近數(shù)據(jù)庫從5.5.18升級(jí)到了5.6.38,出現(xiàn)了一個(gè)問題,有個(gè)視圖同樣的數(shù)據(jù)量執(zhí)行效率相比老數(shù)據(jù)庫慢了20倍,應(yīng)該怎么優(yōu)化?
如下是視圖sql:
SELECT
`wz_demand`.`id` AS `id`,
`wz_demand`.`operator` AS `operator`,
`wz_member`.`username` AS `musername`,
`wz_admin`.`truename` AS `truename`,
`wz_demand`.`cid` AS `cid`,
`wz_demand`.`title` AS `title`,
`wz_demand`.`css` AS `css`,
`wz_demand`.`thumb` AS `thumb`,
`wz_demand`.`keywords` AS `keywords`,
`wz_demand`.`remark` AS `remark`,
`wz_demand`.`url` AS `url`,
`wz_demand`.`status` AS `status`,
`wz_demand`.`route` AS `route`,
`wz_demand`.`publisher` AS `publisher`,
`wz_demand`.`addtime` AS `addtime`,
`wz_demand`.`updatetime` AS `updatetime`,
`wz_demand`.`coin` AS `coin`,
`wz_demand`.`template` AS `template`,
`wz_demand`.`areaid` AS `areaid`,
`wz_demand`.`areaid_1` AS `areaid_1`,
`wz_demand`.`areaid_2` AS `areaid_2`,
`wz_demand`.`sort` AS `sort`,
`wz_demand`.`telephone` AS `telephone`,
`wz_demand`.`address` AS `address`,
`wz_demand`.`housecategory` AS `housecategory`,
`wz_demand`.`renovation` AS `renovation`,
`wz_demand`.`renovationcategory` AS `renovationcategory`,
`wz_demand`.`housetype` AS `housetype`,
`wz_demand`.`style` AS `style`,
`wz_demand`.`area` AS `area`,
`wz_demand`.`way` AS `way`,
`wz_demand`.`budget` AS `budget`,
`wz_demand`.`iscompany` AS `iscompany`,
`wz_demand`.`leadtime` AS `leadtime`,
`wz_demand`.`decorationtime` AS `decorationtime`,
`wz_demand`.`source` AS `source`,
`wz_demand`.`homestyle` AS `homestyle`,
`wz_demand`.`content` AS `content`,
`wz_demand`.`progress` AS `progress`,
`wz_demand`.`progress1time` AS `progress1time`,
`wz_demand`.`progress2time` AS `progress2time`,
`wz_demand`.`progress3time` AS `progress3time`,
`wz_demand`.`progress4time` AS `progress4time`,
`wz_demand`.`order_no` AS `order_no`,
`wz_demand`.`wait_comment` AS `wait_comment`,
`wz_demand`.`referer` AS `referer`,
`wz_demand`.`yxgsm` AS `yxgsm`,
`wz_demand`.`orderplan` AS `orderplan`,
`wz_demand`.`ispay` AS `ispay`,
`wz_demand`.`uid` AS `uid`,
`wz_demand`.`mobile` AS `mobile`,
`wz_demand`.`sflf` AS `sflf`,
`wz_demand`.`managerid` AS `managerid`,
`wz_demand`.`managername` AS `managername`,
`wz_demand`.`housekeeperid` AS `housekeeperid`,
`wz_demand`.`housekeeper` AS `housekeeper`,
`wz_demand`.`paystatus` AS `paystatus`,
`wz_demand`.`orderstatus` AS `orderstatus`,
`wz_demand`.`orderstep` AS `orderstep`,
`wz_demand`.`nodeid` AS `nodeid`,
`wz_demand`.`nodename` AS `nodename`,
`wz_demand`.`outpaystaus` AS `outpaystaus`,
`wz_demand`.`tj_name` AS `tj_name`,
`wz_demand`.`designpay` AS `designpay`,
`wz_demand`.`totalpay` AS `totalpay`,
`wz_demand`.`designno` AS `designno`,
`wz_demand`.`contactno` AS `contactno`,
`wz_demand`.`extrapay` AS `extrapay`,
`wz_demand`.`payforcompanystatus` AS `payforcompanystatus`,
`wz_demand`.`ysclsd` AS `ysclsd`,
`wz_demand`.`ysnm` AS `ysnm`,
`wz_demand`.`ysclyq` AS `ysclyq`,
`wz_demand`.`yssd` AS `yssd`,
`wz_demand`.`ysclnm` AS `ysclnm`,
`wz_demand`.`sdys` AS `sdys`,
`wz_demand`.`paystype` AS `paystype`,
`wz_demand`.`sign` AS `sign`,
`wz_demand`.`getdate` AS `getdate`,
`wz_demand`.`seriesnumber` AS `seriesnumber`,
`wz_demand`.`logname` AS `logname`,
`wz_demand`.`tj_tel` AS `tj_tel`,
`wz_demand`.`domain` AS `domain`,
`wz_demand`.`bao` AS `bao`,
`wz_demand`.`ysyq` AS `ysyq`,
`wz_demand`.`wrzl` AS `wrzl`,
`wz_demand`.`kqzl` AS `kqzl`,
`wz_demand`.`username` AS `username`,
`wz_demand`.`applytype` AS `applytype`,
`wz_demand`.`isApply` AS `isApply`,
`wz_demand`.`name` AS `name`,
`wz_demand`.`referrals` AS `referrals`,
`wz_demand`.`referral` AS `referral`,
`wz_demand`.`community` AS `community`,
`wz_demand`.`companyname` AS `companyname`,
`wz_demand`.`collection` AS `collection`,
`wz_demand`.`three_no` AS `three_no`,
`wz_demand`.`order_source` AS `order_source`,
`wz_demand`.`wait_hf` AS `wait_hf`,
`wz_demand`.`other` AS `other`,
`wz_member`.`uid` AS `muid`,
`wz_admin`.`uid` AS `auid`,
`wz_demand`.`kfzy` AS `kfzy`
FROM
(
(
`wz_demand`
LEFT JOIN `wz_member` ON ( ( `wz_demand`.`operator` = `wz_member`.`username` ) )
)
LEFT JOIN `wz_admin` ON ( ( `wz_member`.`uid` = `wz_admin`.`uid` ) )
)
這里是5.6版本執(zhí)行時(shí)間和explain結(jié)果:
這里是5.5版本執(zhí)行時(shí)間和explain結(jié)果:
北大青鳥APTECH成立于1999年。依托北京大學(xué)優(yōu)質(zhì)雄厚的教育資源和背景,秉承“教育改變生活”的發(fā)展理念,致力于培養(yǎng)中國IT技能型緊缺人才,是大數(shù)據(jù)專業(yè)的國家
北大青鳥中博軟件學(xué)院創(chuàng)立于2003年,作為華東區(qū)著名互聯(lián)網(wǎng)學(xué)院和江蘇省首批服務(wù)外包人才培訓(xùn)基地,中博成功培育了近30000名軟件工程師走向高薪崗位,合作企業(yè)超4
中公教育集團(tuán)創(chuàng)建于1999年,經(jīng)過二十年潛心發(fā)展,已由一家北大畢業(yè)生自主創(chuàng)業(yè)的信息技術(shù)與教育服務(wù)機(jī)構(gòu),發(fā)展為教育服務(wù)業(yè)的綜合性企業(yè)集團(tuán),成為集合面授教學(xué)培訓(xùn)、網(wǎng)
達(dá)內(nèi)教育集團(tuán)成立于2002年,是一家由留學(xué)海歸創(chuàng)辦的高端職業(yè)教育培訓(xùn)機(jī)構(gòu),是中國一站式人才培養(yǎng)平臺(tái)、一站式人才輸送平臺(tái)。2014年4月3日在美國成功上市,融資1
曾工作于聯(lián)想擔(dān)任系統(tǒng)開發(fā)工程師,曾在博彥科技股份有限公司擔(dān)任項(xiàng)目經(jīng)理從事移動(dòng)互聯(lián)網(wǎng)管理及研發(fā)工作,曾創(chuàng)辦藍(lán)懿科技有限責(zé)任公司從事總經(jīng)理職務(wù)負(fù)責(zé)iOS教學(xué)及管理工作。
浪潮集團(tuán)項(xiàng)目經(jīng)理。精通Java與.NET 技術(shù), 熟練的跨平臺(tái)面向?qū)ο箝_發(fā)經(jīng)驗(yàn),技術(shù)功底深厚。 授課風(fēng)格 授課風(fēng)格清新自然、條理清晰、主次分明、重點(diǎn)難點(diǎn)突出、引人入勝。
精通HTML5和CSS3;Javascript及主流js庫,具有快速界面開發(fā)的能力,對(duì)瀏覽器兼容性、前端性能優(yōu)化等有深入理解。精通網(wǎng)頁制作和網(wǎng)頁游戲開發(fā)。
具有10 年的Java 企業(yè)應(yīng)用開發(fā)經(jīng)驗(yàn)。曾經(jīng)歷任德國Software AG 技術(shù)顧問,美國Dachieve 系統(tǒng)架構(gòu)師,美國AngelEngineers Inc. 系統(tǒng)架構(gòu)師。