MySQL：视图与存储过程

MySQL: Views vs Stored Procedures

提问by Peter Bailey

Since MySQL started supporting stored procedures, I've never really used them. Partly because I'm not a great query writer, partly because I often work with DBAs who make those choices for me, partly because I'm just comfy with What I Know.

自从 MySQL 开始支持存储过程以来，我从未真正使用过它们。部分是因为我不是一个出色的查询编写者，部分是因为我经常与为我做出这些选择的 DBA 一起工作，部分是因为我只是对我所知道的感到满意。

In terms of doing data selection, specifically when considering a select that is essentially a de-normalization (joins) and aggregate (avg or max, subqueries w/counts, etc) selection of data, what is the right choice in MySQL 5.x? A view? Or a stored procedure?

在进行数据选择方面，特别是在考虑一个本质上是非规范化（连接）和聚合（平均或最大、子查询 w/计数等）选择的选择时，MySQL 5.x 中的正确选择是什么? 一个看法？还是存储过程？

Views I'm comfortable with - you know what your SELECT query is supposed to look like so you just create that, make sure it indexed and whatnot, then just do a CREATE VIEW [View] AS SELECT [...]. Then, in my application, I treat the view as a read-only table - it represents a de-normalized version of my normalized data.

我喜欢的视图 - 你知道你的 SELECT 查询应该是什么样子，所以你只需创建它，确保它被索引等等，然后只做一个CREATE VIEW [View] AS SELECT [...]. 然后，在我的应用程序中，我将视图视为只读表 - 它代表我的规范化数据的非规范化版本。

What are the disadvantages here - if any? And what would change (gains or losses) if I moved that exact same SELECT statement into a stored procedure?

这里有什么缺点 - 如果有的话？如果我将完全相同的 SELECT 语句移动到存储过程中会发生什么变化（收益或损失）？

I'm hoping to find some good 'under the hood' info that has been difficult to find while googling this topic but really I welcome all comments and answers.

我希望能找到一些很好的“幕后”信息，这些信息在谷歌搜索这个主题时很难找到，但我真的欢迎所有评论和答案。

采纳答案by Chris Johnston

In my opinion, Stored Procedures should be used solely for data manipulation when the same routine needs to be used amongst several different application or for ETL between databases or tables, nothing more. Basically, do as much in code as you can until you run into the DRY principle or what you are doing is simply moving data from one place to another within the DB.

在我看来，当需要在几个不同的应用程序之间使用相同的例程或数据库或表之间的 ETL 时，存储过程应该仅用于数据操作，仅此而已。基本上，在您遇到 DRY 原则之前尽可能多地编写代码，或者您所做的只是将数据从数据库中的一个地方移动到另一个地方。

Views can be used to provide an alternate or simplified "view" into the data. As such, I would go with a view as you are not really manipulating the data as much as finding a different method of displaying it.

视图可用于提供数据的替代或简化“视图”。因此，我会采用一种观点，因为您实际上并没有像找到一种不同的显示方法那样操纵数据。

回答by Learning

Not sure if it's an either/or choice. Stored procedures can do a wide variety of things that views would struggle (think populating data in temp table then running cursor on it and then doing aggregation and returning a result set).

不确定这是不是/或选择。存储过程可以做很多视图会遇到困难的事情（想想在临时表中填充数据，然后在它上面运行游标，然后进行聚合并返回结果集）。

Views on the other hand can hide complex sql / access rights and present a modified view of the schema.

另一方面，视图可以隐藏复杂的 sql/访问权限并呈现架构的修改视图。

I think both have a place in the scheme of things and both are useful for a successful schema implementation.

我认为两者都在事物方案中占有一席之地，并且对于成功的模式实现都很有用。

回答by Chris Nava

I use views for de-normalisation or output formatting and stored procedures for filtering and data manipulation (things that require parameter inputs) or iteration (cursors).

我使用视图进行反规范化或输出格式化，并使用存储过程进行过滤和数据操作（需要参数输入的事物）或迭代（游标）。

I often access a view inside a stored procedure when both de-normalisation and filtering are required.

当需要反规范化和过滤时，我经常访问存储过程中的视图。

回答by bob

One thing to note, at least with mysql view results are stored in a temporary table and unlike most decent database engines this table is not indexed, so if using to just simplify queries, view are great when your program is going to grab all of the results from the view, however if your then searching the results of that view, based on parameters it is incredibly slow especially if there are millions of records to sift through and even worse if the view is built on top of other views and so on.

需要注意的一件事，至少对于 mysql，视图结果存储在一个临时表中，并且与大多数体面的数据库引擎不同，该表没有索引，因此如果仅用于简化查询，当您的程序要获取所有视图的结果，但是，如果您然后根据参数搜索该视图的结果，它会非常慢，特别是如果有数百万条记录要筛选，如果视图构建在其他视图之上，则更糟，等等。

A stored procedure however you can pass those search parameters in and run the query directly against the underlining (indexed) tables. the downside is the results will need to be fetched every time the procedure is run, which may also occur with a view anyway depending on server configuration.

但是，存储过程可以传递这些搜索参数，并直接针对下划线（索引）表运行查询。缺点是每次运行过程时都需要获取结果，这也可能发生在视图中，具体取决于服务器配置。

so basically if your using a view try to minimise the number of results (if you then need to search it) else use a stored procedure.

所以基本上，如果您使用视图尝试尽量减少结果的数量（如果您需要搜索它），则使用存储过程。