MySql视图是动态且高效的吗? [英] Are MySql Views Dynamic and Efficient?

问题描述

我希望创建一个表视图，该表将突出显示满足特定条件的数据.例如，如果我有一个带有整数值的表，我希望我的视图显示值大于100的行.我知道如何通过在表上创建视图来实现这一点，但是视图是动态的吗?我已经在MySQL中对此进行了测试，这似乎是真的.但是，如果我的表有1000多个行，这样有效吗?视图是否仍会动态"更新为原始表中的任何更改?

I'm looking to create a view of a table which will highlight data that meets a specific criteria. For example, if I have a table with integer values, I want my view to show the rows which have a value greater than 100. I know how to achieve this by creating a view on a table, however is the view dynamic? I have tested this in MySQL and it seems to be true. But if my table has over 1000 rows, is this efficient? Will the view still update "dynamically" to any changes in the original table?

推荐答案

基本上，基本上有2个

Basically, there are basically 2 types of views in MySQL.

1. 合并视图

1. Merge Views

这种类型的视图基本上只是用视图的SQL重写查询.因此，这是您自己编写查询的捷径.这并没有真正的性能优势，但是使编写复杂的查询变得更容易并使维护更加容易(因为如果视图定义发生更改，则无需针对该视图更改100个查询，只需更改一个定义即可.)

This type of view basically just re-writes your queries with the view's SQL. So it's a short-hand for writing the queries yourself. This offers no real performance benefit, but make writing complex queries easier and making maintenance easier (since if the view definition changes, you don't need to change 100 queries against the view, only the one definition).

可引人入胜的视图

这种类型的视图使用视图SQL的查询创建临时表.它具有合并视图的所有优点，但也减少了视图表的锁定时间.因此，在高负载的服务器上，它可能会获得相当可观的性能提升.

This type of view creates a temporary table with the query from the view's SQL. It has all the benefits of the merge view, but also reduces lock time on the view's tables. Therefore on highly loaded servers it could have a fairly significant performance gain.

还有未定义"视图类型(默认)，让MySQL选择在查询时认为最佳的视图类型.

There's also the "Undefined" view type (the default), which let's MySQL pick what it thinks is the best type at query time...

但是要注意的是，MySQL不支持物化视图 .因此，与Oracle不同的是，复杂的视图将大大提高针对它的查询的性能.视图查询始终在MySQL中执行.

But note something important to note, is that MySQL does not have any support for materialized views. So it's not like Oracle where a complex view will increase the performance of queries against it significantly. The queries of the views are always executed in MySQL.

就效率而言，MySQL中的View不会提高或降低效率.它们在那里使您在编写和维护查询时更轻松.我在具有数亿行的表上使用过视图，并且它们工作得很好...

As far as the efficiency, Views in MySQL do not increase or decrease efficiency. They are there to make your life easier when writing and maintaining queries. I have used views on tables with hundreds of millions of rows, and they have worked just fine...

这篇关于MySql视图是动态且高效的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！