blocks|key|4073700|text|对于记录数量与您的表一样多的表，使用连接通常会非常慢。这是因为joins将遍历表中的每条记录，这会使查询花费大量时间。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4073701|作为个人经验丰富的解决方案，我建议您尽量使用WHERE来过滤查询结果，然后使用连接来减少查询结果。|offset|length|style|CODE|4073702|entityMap^0|0|M|5|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Using joins with tables that have as large an amount of records as yours are often very slow. This is so because joins will go over every record in a table which makes the query take a lot of time.

As a personally experienced solution I would suggest that you try and cut down the results of your query by using <code>WHERE</code> as much as you can to filter down the results and then use joins.

blocks|key|4073554|text|不，您无法通过使用视图来获得性能。在后台，原始查询是在查询视图时运行的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4073555|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

No, you cannot gain performance from using a view. Behind the scene, your original query is run when you query the view.

blocks|key|293002|text|有时使用视图可以获得一点性能，就像它在High+Performance+MySQL中所说的那样|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|293003|另一方面，这本书的作者写了这个博客：Views+as+performance+trouble+maker|293004|entityMap|0|LINK|mutability|MUTABLE|url|https://rads.stackoverflow.com/amzn/click/com/0596101716|1|http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/^0|J|M|0|0|I|Y|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]]]

Sometimes using views can gain a small bit of performance, like it says in <a href="https://rads.stackoverflow.com/amzn/click/com/0596101716" rel="nofollow noreferrer" rel="nofollow noreferrer">High Performance MySQL</a>

On the other hand the author of the book has written this blog:
<a href="http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/" rel="nofollow noreferrer">Views as performance trouble maker</a>

blocks|key|4073630|text|一般来说，这取决于您提交查询的方式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4073631|视图可能更快：|offset|length|style|BOLD|4073632|例如，在PHP中，通常的做法是“动态”提交查询(即不是prepared+statement)。这意味着MySQL必须在您每次调用它时编译查询。使用视图时，此操作在创建视图时执行一次。|4073633|关于MySQL作为一种数据库管理系统，我在早期版本中听说过带有视图的performance+issues。(虽然不知道当前的情况是什么)。|4073634|对于这类问题的一般规则是，只需对查询进行基准测试即可获得实际结果。看起来您已经用大量数据填充了数据库，因此这应该会产生有意义的结果。(不要忘记在MySQL中禁用缓存)。|4073635|entityMap|0|LINK|mutability|MUTABLE|url|http://php.net/manual/en/pdo.prepared-statements.php|1|http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/^0|0|2|2|0|R|I|0|0|Y|I|1|0|K|1S|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@$D|Z|E|10|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|11|8|@]|9|@$D|12|E|13|1|14]]|A|$]]|$1|J|3|K|5|6|7|15|8|@]|9|@$D|16|E|17|1|18]]|A|$]]|$1|L|3|M|5|6|7|19|8|@$D|1A|E|1B|F|G]]|9|@]|A|$]]|$1|N|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|O|$P|$5|Q|R|S|A|$T|U]]|V|$5|Q|R|S|A|$T|W]]]]

Generally speaking, this depends on how you submit your query.

The view MAY be faster:

For example, in PHP it's common practice to submit the query "dynamically" (i.e. NOT as an <a href="http://php.net/manual/en/pdo.prepared-statements.php" rel="nofollow noreferrer">prepared statement</a>).
That means MySQL has to compile the query every time you call it. When using a view, this in done once when the view is created.

Regarding MySQL as an DBMS, I heard about <a href="http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/" rel="nofollow noreferrer">performance issues</a> with Views in earlier versions. (Don't know what the current situation is, though).

As a general rule in such questions, just benchmark your query to get real life results. Looks like you have already populated your database with a lot of data, so this should yield meaningful results. (Don't forget to disable caching in MySQL).

blocks|key|2660128|text|没有什么理由让视图运行查询，而不是自己运行查询，使用MySQL会更快。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2660129|MySQL中的视图通常实现得很差，我们不得不在我们的许多项目中放弃使用它们。|2660130|当您将查询放在视图中时，查看该查询，即使它是视图的一部分，它也可能仍然可以使用正确的索引，因此它至少不会变慢。|2660131|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

There's little reason having a view run your query instead of running the query yourself be any faster with MySQL. 

Views in MySQL is generally so poorly implemented, we had to back out
of using them for many of our projects.

Check with EXPLAIN what your query does when you place it in a view, looking at that query, it can probably still use the proper indexes even it's part of a view, so it'll atleast not be slower.

Following is the query which is hit every table has over 100,000 records.

<pre><code> SELECT b.login as userEmail, imgateway_instance_id as img, u.id as userId 
 FROM buddy b
 INNER JOIN `user` u ON b.username = u.login
 INNER JOIN bot_to_buddy btb ON b.id = btb.buddy_id
 INNER JOIN bot ON btb.bot_id = bot.id
 WHERE u.id IN 14242
</code></pre>

Is creating a view of multiple joining table faster then gettin that data directly using the join in a query?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

以下是命中每个表有超过100,000条记录的查询。   SELECT b.login as userEmail, imgateway_instance_id as img, u.id as userId    FROM  buddy b   INNER JOIN `user` u ON b.username = u.login    INNER JOIN bot_to_buddy btb ON b

问创建多个连接表的视图比在查询中直接使用连接获取数据快吗？
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问创建多个连接表的视图比在查询中直接使用连接获取数据快吗？EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问创建多个连接表的视图比在查询中直接使用连接获取数据快吗？
EN