Select * from view比直接使用MySQL 5.7执行视图create语句慢10倍以上

Question

我创建了一个视图作为多个表的连接，以隐藏复杂性。

现在，当我执行select * from view时，执行select语句所需的时间是直接执行select语句的10倍以上，select语句定义了视图(大约70ms对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
;

附注:完整的执行计划如下所示：

直接呼叫：

​

查看：

​

Answer 1

这个链接是针对MySQL 8.0的，但当我上一次在MySQL 5.6、5.7中遇到视图性能问题并深入研究时，我得到了相同的答案:在使用视图时，基本上不能利用索引。

https://dev.mysql.com/doc/refman/8.0/en/view-restrictions.html

我个人发现MySQL中的视图总体上没有用，这是由于上面描述的限制，特别是由于性能影响。

读者们--如果MySQL中的视图能够以某种方式运行，那么我肯定在某个地方遗漏了一个关键的备忘录。如果是这样，请纠正我。