使用视图作为数据库中模块之间的数据接口
我正在研究 Mysql 中一个简单的小型数据库的数据库布局。 我们希望模块化这个系统,以便为我们将要进行的不同实现提供更大的灵活性。现在,我们的想法是让数据库中的一个模块(简单的一组表之间具有约束)通过视图将其数据传递到下一个模块。这样,一个模块中的更改不会影响其他模块,因为我们可以确保视图中随时存在正确的数据,尽管表的底层结构可能不同。
处理数据库的应用程序的结构同样会模块化。
这是有时会做的事情吗? 在技术方面,据我了解视图不能有主键 - 那么我将如何处理这样的视图? 还应该考虑哪些问题?
I am working on the database layout of a straighforward small database in Mysql.
We want to modularize this system in order to have more flexiblity for different implementations we are going to make. Now, the idea was to have one module in the database (simple a group of tables with constraints between them) pass its data to the next module via views. In this way, changes in one module would not affect the other ones, as we can make sure in the view that the right data is present there at any time, although the underlying structure of tables might be different.
The structure of the App handling the database would likewise be modularized.
Is this something that is sometimes done?
On a technical side, as I understand views can't have primary keys - how would I then adress such a view?
What other issues should be considered?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这是一种有效的方法,但您当然应该小心:
确保您的查询通过分析的查询计划进行了良好的性能测试。视图将使用基础表的索引,但错误执行计划的可能性更高。
确保存在足够的视图来涵盖所有需要的信息。
This is a valid approach, though you should of course be careful:
Make sure that your queries are very well tested for performance with query plans analyzed. The views will use the underlying tables's indexes, BUT the likelyhood of bad execution plan is higher.
Make sure that enough views exist to cover all needed info.
是的,视图通常用于将事物与处于变化状态的数据模型隔离开来。
MySQL 不支持物化视图。非物化视图只是准备好的 SQL 语句 - 它们不存在于数据模型中,性能最终取决于基础表中存在的内容和查询优化。
也就是说,不建议分层视图(创建一个从另一个视图中进行选择的视图)——它很脆弱,并且存在性能下降的很大风险,因为有人想要简单性而不是查询优化。
Yes, views are often used to insulate things from data models in a state of flux.
MySQL doesn't support materialized views. Non-materialized views are just prepared SQL statements - they don't exist in the data model, and performance is ultimately based on what exists on the underlying table(s) and query optimizations.
That said, layering views (creating a view that selects from another) is not recommended - it's brittle, and there's a big risk that performance will decrease because someone wants simplicity over query optimization.