优化多个MySQL视图的连接
我的数据库中有多个视图，当某些查询变得非常复杂时，我试图对这些视图执行 JOIN。最坏的情况是，我必须将 3 个视图与以下统计数据连接起来：
- 视图 1 有 60K+ 行和 26 个字段。
- 视图 2 有 60K+ 行和 15 个字段。
- 视图 3 有 8 万多行和 8 个字段。
连接视图 1 和 2 似乎没有问题，但每当我尝试连接第三个视图时，查询都会挂起。我想知道是否应该遵循任何最佳实践来防止这些查询挂起。我尝试使用尽可能小的字段（尽可能使用中/小整数等）。
我们使用 MySQL 5.0.92 社区版和 MyISAM 表。不确定 InnoDB 是否会更高效。
作为最后的手段，我考虑将一个查询分成两个，点击视图 1 和视图 1。 2 与第一个查询，然后分别查看 3 与第 3 个。除了进行 2 个查询之外，还有什么缺点吗？
谢谢。
I have multiple views in my database that I am trying to perform a JOIN on when certain queries get very complex. As a worst case I would have to join 3 views with the following stats:
- View 1 has 60K+ rows with 26 fields.
- View 2 has 60K+ rows with 15 fields.
- View 3 has 80K+ rows with 8 fields.
Joining views 1 and 2 seem to be no problem, but anytime I try to join the third view the query hangs. I'm wondering if there are any best practices I should be following to keep these querys from hanging. I've tried to use the smallest fields possible (medium/small ints where possible, ect).
We are using MySQL 5.0.92 community edition with MyISAM tables. Not sure if InnoDB would be more efficient.
As a last resort I thinking of splitting the one query into two, hitting views 1 & 2 with the first query, and then view 3 separately with the 3rd. Is there any downside to this other than making 2 queries?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您需要使用 EXPLAIN 来了解性能的原因很穷。
我认为您还不需要担心 MyISAM 与 InnoDB 的这种特定读取性能。 MyISAM 与 InnoDB
You need to use EXPLAIN to understand why the performance is poor.
I wouldn't think you need to worry about MyISAM vs. InnoDB for this particular read performance just yet. MyISAM versus InnoDB
我将发表我的评论作为答案：
1）看看 EXPLAIN 命令并查看其内容。
2) 检查各个视图的性能。他们的速度有你想象的那么快吗？
3) 您在 WHERE 或 JOIN 子句中使用的列，基础表是否具有适用于它们的索引？需要注意的是：
4) 您是否使用了所有列和所有视图？如果您不这样做，那么查看视图并提出查询不是更简单吗？
I am going to post my comments as an answer:
1) Take a look at the EXPLAIN command and see what it says.
2) Check the performance of the individual views. Are they as fast as you think on their own?
3) The columns you are using in your WHERE or JOIN clauses, do the underlying tables have indexes that apply to them? Something to have in mind:
4) Are you using the all the columns and all the views? If you don't wouldn't it be simpler to take a look at the views and come up with a query instead?
如果有可能了解原始视图的定义方式，那么使用它作为基础来创建您自己的单个查询可能是更好的方法......很久以前，另一个人在他们的查询中遇到了类似的问题。他需要返回此类视图的原始表，以确保它具有适当的索引来接受他尝试执行的查询的优化。请记住，视图是其他内容的子集，并且没有可使用的索引。因此，如果您无法利用视图根表处的索引，您可能会看到这样的性能损失。
If its possible to get what how the original VIEWs are defined, then use that as a basis to create your own single query might be a better approach... Way back, another person had similar issues on their query. He needed to get back to the raw table of one such view to ensure it had proper indexes to accept the optimization of the query he was trying to perform. Remember a view is a subset of something else and does not have an index to work with. So, if you can't take advantage of an index at the root table of a view, you could see such a performance hit.