【mysql】mysql如何提高视图查询速度？

最近数据库从5.5.18升级到了5.6.38，出现了一个问题，有个视图同样的数据量执行效率相比老数据库慢了20倍，应该怎么优化？

如下是视图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版本执行时间和explain结果：
【mysql】mysql如何提高视图查询速度？

【mysql】mysql如何提高视图查询速度？

这里是5.5版本执行时间和explain结果：
【mysql】mysql如何提高视图查询速度？
【mysql】mysql如何提高视图查询速度？

回答

看执行计划是一样的，看不出问题在哪里，建议按照按照下面的思路排查：
1、两个环境都多执行几次，看平均时间，排查缓存影响。
2、升级后如果大部分sql都慢，可能是参数配置的问题
3、尝试重建username索引

以上是 【mysql】mysql如何提高视图查询速度？ 的全部内容， 来源链接： utcz.com/a/72345.html

回到顶部