我创建了一个视图作为几个表的连接来隐藏复杂性。
现在当我执行
select * from view
直接执行select语句所需的执行时间是定义视图的执行时间的10倍多(大约70ms vs 900ms)。
这种行为发生在mysql 5.7.23服务器上。
我比较了两个查询的执行计划,唯一的区别(如预期)是派生表:
这是观点的正常行为还是我该如何解决?
附笔。:
请求的CREATE VIEW语句(混淆的名称):
create view vrs as select
rs."id" as "id",
l."language" as "language",
rss."t" as "discriminator",
rss."type" as "type",
coalesce(lt3."text", concat('!',s3."textkey",'(',l."language",')')) as "typeText",
rss."model" as "model",
coalesce(lt2."text", concat('!',s2."textkey",'(',l."language",')')) as "modelText",
rs."name" as "name",
rs."reqCATGroup" as "CATGroup",
rs."devID" as "devID",
case s."builtIn"
when 0 then s."displayName"
else (select lt1."text"
from loctext lt1
where lt1."language"=l."language"
and lt1."textkey"=s."displayName")
end as "sName",
s."id" as "sId",
s."postcode" as "sPostcode",
s."place" as "sPlace",
s."street" as "sStreet",
s."streetNumber" as "sStreetNumber",
s."tId" as "tId",
t."tName" as "tName",
rss."CCVMajor" as "CCVMajor",
rss."CCVMinor" as "CCVMinor",
rss."CCVPatch" as "CCVPatch",
rss."CCHV" as "CCHV",
coalesce(lHw."text", concat('!',sHw."textkey",'(',l."language",')')) as "CCHVText",
rss."MCVMajor" as "MCVMajor",
rss."MCVMinor" as "MCVMinor",
rss."MCVPatch" as "MCVPatch",
rss."level",
coalesce(lLevel."text", concat('!', sLevel."textkey", '(', l."language", ')')) as "levelText",
rss."event",
coalesce(lEvent."text", concat('!', sEvent."textkey", '(', l."language", ')')) as "eventText",
rss."isBusy" as "isBusy",
rss."tsLastComIn" as "tsLastCom",
rss."tsBT" AS "tsBT",
b1."ordinal" as "isSending",
coalesce(ltSend."text", concat('!',b1."textkey",'(',l."language",')')) as "isSendingText",
b2."ordinal" as "isReceiving",
coalesce(ltReceive."text", concat('!',b2."textkey",'(',l."language",')')) as "isReceivingText",
rs."historyCreateDevTs",
rs."historyCreateDevUserId",
rs."historyCreateDevUserLoginName",
rs."historyModifyDevLinkTs",
rs."historyModifyDevLinkUserId",
rs."historyModifyDevLinkUserLoginName",
rs."monitorStartTs",
rs."monitorEndTs"
from synch rss
cross join loclanguages l
join cfgrs rs on rs."id" = rss."id"
join cfgs s on s."id" = rs."sId"
join cfgt t on t."id" = s."tId"
left join locmodel s2 on s2."ordinal" = rss."model"
left join loctext lt2 on lt2."textkey" = s2."textkey" and lt2."language" = l."language"
left join loctype s3 on s3."ordinal" = rss."type"
left join loctext lt3 on lt3."textkey" = s3."textkey" and lt3."language" = l."language"
left join locbooleanrange b1 on rs."sending"+1 = b1."ordinal"
left join loctext ltSend on ltSend."textkey" = b1."textkey" and ltSend."language" = l."language"
left join locbooleanrange b2 on rs."receiving"+1 = b2."ordinal"
left join loctext ltReceive on ltReceive."textkey" = b2."textkey" and ltReceive."language" = l."language"
left join loceventlevel sLevel on sLevel."ordinal" = rss."level"
left join loctext lLevel on lLevel."textkey" = sLevel."textkey" and lLevel."language" = l."language"
left join locevent sEvent on sEvent."ordinal" = rss."event" and sEvent."type" = rss."type"
left join loctext lEvent on lEvent."textkey" = sEvent."textkey" and lEvent."language" = l."language"
left join loccchv sHw on sHw."ordinal" = rss."CCHV"
left join loctext lHw on lHw."textkey" = sHw."textkey" and lHw."language" = l."language"
where rs."deleted" = 0
;
P.P.S.:完整的执行计划如下:
直接呼叫:
观点:
