MySQL：视图与存储过程 [英] MySQL: Views vs Stored Procedures

问题描述

由于MySQL开始支持存储过程，所以我从来没有真正使用过它们。部分原因是因为我不是一个很好的查询作者，部分是因为我经常和DBAs合作，为我做出这些选择，部分原因是因为我只是喜欢我所知道的。

在进行数据选择方面，具体来说，当考虑到基本上是去规范化（join）和聚合（avg或max，subqueries w / counting等）数据选择时，MySQL 5中的正确选择。X？一个看法？或存储过程？

视图我很舒服 - 你知道你的SELECT查询应该是什么样子，所以你只是创建它，确保它索引，什么不，然后只需执行一个 CREATE VIEW [查看] AS SELECT [...] 。然后，在我的应用程序中，我把视图视为一个只读表 - 它代表了我的标准化数据的正规化版本。

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

我希望找到一些很好的引擎盖信息，这是一个很难找到，而谷歌搜索这个话题，但我真的欢迎所有的意见和答案。

解决方案

在我看来，只有当需要在多个不同的应用程序中使用相同的例程或数据库或表之间的ETL时，程序才能用于数据操作。基本上，尽可能多的代码，直到你遇到DRY原则，或者你正在做的只是将数据从一个地方移动到另一个地方。

视图可用于为数据提供备用或简化的视图。因此，我会去看看，因为你不是真的操纵数据，就像找到一种不同的显示方式一样。

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.

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?

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.

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?

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.

解决方案

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.

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.

这篇关于MySQL：视图与存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！