视图和表在性能上的差异
对于包含大量数据的表来说什么是最好的？
我有一个存储过程，可以根据某些过滤器创建报告。在我的 SP 中，我读取了表格并放置了所有内部联接和公式，然后在放置过滤器的 where 条件中。
谈论性能什么更好？
创建一个包含所有联接的视图或读取表（就像我正在做的那样）？
What is best for tables with a lot of data?
I have a stored procedure that creates a report based on some filters. In my SP I read the table and put all the inner joins and formulas then in the where condition I put the filters.
Talking about performance what's better?
Create a view with all the joins OR read the table (as I'm doing)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
与使用视图或直接表访问相比，性能更依赖于是否拥有适当的索引，而视图或直接表访问（物化视图除外）的行为方式完全相同。
Performance is a lot more dependent on having the appropriate indexes than if you are using a view or direct table access, which (except for materialized views) behave exactly the same way.
这取决于。
只要视图不包含聚合（或需要“预先”实现的构造），它就会具有完全相同的性能（并且在许多情况下可以通过优化器短路的条件）
您是否尝试过在您的特定环境中进行基准测试案例？
@Otávio Décio 比我先一步，提到拥有“正确”的索引将对性能产生更大的影响。
It depends.
As long as the View does not contain aggregations (or constructs that require materialisation 'upfront'), it will be exactly the same performance (and in many cases can pass through where criteria with shortcircuiting by the optimiser)
Have you tried benchmarking in your specific cases?
@Otávio Décio beat me to it, by mentioning that having the 'correct' indexes will have a greater effect on performance.
当您使用
select count(*) from view时，它会比表慢得多。由于表的标题上包含行号，因此视图没有此类信息。When you use
select count(*) from viewit will significantly slow than table. Because the table contains row number on its header, a view doesn't have such information.