什么时候使用sql视图

we have a table with 2 million records with PK userid and not unique field "Company" we have 35,000 select query an hour to check if a userid exists in our db and what companies he is related to.

我们有一个有2百万条记录的表，有PK userid，没有唯一字段"Company"我们每小时有35000条选择查询来检查一个userid是否存在于我们的db中，以及他与哪些公司有关联。

should we run the high amount of query on the main table or should we create a view with only userID and company fields and run the query against it?

我们应该在主表上运行大量的查询，还是应该创建一个只有userID和company字段的视图，并对其运行查询?

what are the upside and downside?

什么是上行和下行?

i will appreciate your help!

我将感激你的帮助!

P.s the 35,000 queries an hour are with random userid, and change every time. the user id and company are not updated but we add about 20,000 new rows a day. my major concern is minimize the response time of the selects even if i do updates to other fields of the table.

P。35000个查询一个小时是随机的用户id，并且每次都改变。用户id和公司没有更新，但是我们每天增加大约20,000行。我主要关心的是最小化select的响应时间，即使我对表的其他字段进行了更新。

4 个解决方案

#1

Creating a VIEW will not help. It will simply reference the underlying table every time you use it.

创建视图不会有帮助。它只会在每次使用基础表时引用它。

What might help is to create a covering index on the columns you query. Assuming you only need UserID and Company:

可能有帮助的是在查询的列上创建覆盖索引。假设您只需要UserID和Company:

 CREATE INDEX <Name> ON <Table> (UserID, Company)

Now, queries of the form

现在，查询表单

SELECT Company FROM <Table> WHERE UserID = <Value>

can be satisfied from the index without reference to the table data. This will probably improve your performance (on SELECTs).

可以从索引中得到满足，而不需要引用表数据。这可能会提高性能(在选择时)。

#2

Views will still query the main table, so there's no performance improvement. They are primarily used for

视图仍将查询主表，因此没有性能改进。它们主要用于。

secure access to only those rows/columns authorized,
只对授权的行/列进行安全访问，
simplify the client SQL if complex joins are needed.
如果需要复杂的连接，简化客户机SQL。

We need to know what your concerns are before we can answer the pros and cons of either direction.

我们需要知道你的担忧是什么，然后我们才能回答任何一个方向的利与弊。

#3

A View is nothing more then queryable queries. I will suggest that You should create a view with only userID and company fields and run the query against it. However views will also query the existing table.

视图就是可查询查询查询。我建议您创建一个只有userID和company字段的视图，并针对它运行查询。但是视图也将查询现有的表。

Some points to remember about using views are:

关于使用视图，要记住以下几点:

A view is really nothing more than a stored select statment
视图实际上只是一个存储的select语句
The data of a view is the data of tables referenced by the View.
视图的数据是视图引用的表的数据。
creating an index on a view will not work as of the current version
在视图上创建索引将在当前版本中不起作用
If merge algorithm is used, then indexes of underlying tables will be used.
如果使用合并算法，则使用底层表的索引。
The underlying indices are not visible, however. DESCRIBE on a view will show no indexed columns.
然而，根本的指数是不可见的。在视图中描述将显示没有索引列。

Hope this helps.

希望这个有帮助。

#4

As @dlgrasse said views will not help you.

正如@dlgrasse所说的那样，视图对你没有帮助。

The thing which could help you with these 35000 queries is having a lot of them already stored in the query cache. This could happen if in the same hour the queries are usually done on the same users. The other point to get the queries runned on the query cache is to avoid editing (insert/update/delete) rows from this big table, after each edit all queries from the query cache implying this table will be invalidated.

可以帮助您处理这35000个查询的东西，已经有很多已经存储在查询缓存中了。如果查询通常在同一小时内对相同的用户执行，则可能发生这种情况。在查询缓存上运行查询的另一个要点是避免编辑(插入/更新/删除)这个大表中的行，每次编辑来自查询缓存的所有查询之后，这意味着该表将无效。

So if you have other columns on this table that needs some work but you still want to get a 'fast view' on the user_id and company fields which are not moving a lot, then you could create a dedicated table, containing only those fields, and where the editions are limited.

如果你有其他列在这个表,需要一些工作但你仍然想要一个“快速查看”user_id和公司字段不移动,然后你可以创建一个专用表,只包含这些字段,版本是有限的。

#1