JOIN 适用于无法装入内存的大型 mysql 数据库。
JOIN 是 SQL 数据库的一个方便的功能，但对于大型数据库（>10GB）又如何呢？考虑三个（两列）多对多关系的表，因为我们想要获取与一个案例关联的项目（例如一篇文章的标签）。
事实（如果我错了请纠正我）： 1. 对于 JOIN，内存中应容纳三个表。 2、单次SELECT by PRIMARY KEY不消耗内存。 3. 当我们有很多并发读连接时，多余的连接将保留在队列中（不会发出不成功的请求或过载）。
那么，执行三个简单的 SELECT 查询不是更好吗？这使得系统有点慢，但我相信处理整个千兆字节大小的表会更有效。
人们可能会认为添加更多是最终的解决方案；但我认为使用多余的 RAM 仍然处理如此大的表并不容易。
将操作限制为带有 PRIMARY KEY 的简单 SELECT 查询可能是有效处理大型数据库的实用方法。
JOIN is a handy feature of SQL databases, but what about large databases (>10GB). Consider three (two-column) tables of many-to-many relationship, as we want to get items associated to one single case (e.g. tags of ONE article).
FACTS (correct me if I'm wrong):
1. For JOIN, three tables should fit within the memory.
2. Single SELECT by PRIMARY KEY does not consume memory.
3. When we have concurrent many concurrent read connections, excess connection will be kept in queue (not making unsuccessful request or overload).
Then, isn't it better to perform three simple SELECT queries. This makes the system a little bit slower, but I believe it is more efficient to deal with the entire tables of Gigabyte size.
One may suggest that adding more is the ultimate solution; but I think still handling such large tables is not easy with excess RAM.
Limiting actions to simple SELECT queries with PRIMARY KEY can be a practical approach to work with large databases efficiently.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您声称最好在三个单独的表上进行选择，然后在数据库引擎之外自行连接数据，那么您就错了。数据库将比您更好地连接您的查询。这些表不必全部装入 RAM 才能使联接发挥作用。
If you are claiming that it's better to do the selects on three separate tables, then join the data yourself outside of the database engine, then you are wrong. The database will do a better job joining your queries than you can. The tables don't all have to fit into RAM for a join to work.
索引对于大型数据库确实很有帮助。
这些可能会有所帮助 http:// www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm
http://www.tizag.com/mysqlTutorial/mysql-index.php
索引可以数据库实现之间存在差异，如果设计/实现不正确，它们也有缺点（这就是为什么您不只为每个字段建立索引）。
Indexes can be really helpful with large databases.
These may help http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm
http://www.tizag.com/mysqlTutorial/mysql-index.php
Indexes can vary among database implementations and they have downsides too if not designed / implemented correctly (that's why you don't just index every field).