MySQL - 大表的基本问题
在我的数据库中有两个大表。第一个 (A) 有 170 万行，第二个 (B)：210 万行。 A 和 B 中的记录具有相当相同的大小。
我可以对A做任何操作。这需要时间，但是有效。在B上，我什么也做不了。即使是一个简单的 select count(*) 也会永远挂起。问题是我没有看到任何错误：它只是挂起（当我显示进程列表时，它只是永远显示“更新”）。
对我来说，奇怪的是，1.7 到 210 万之间的小增量（百分比）可以产生如此大的差异（从能够做所有事情，到甚至无法做最简单的操作）。
是否可以有某种 200 万行的硬限制？
我使用的是 Linux 2.6+，并且使用 innoDB。
谢谢！ 皮埃尔
In my db there are two large tables. The first one (A) has 1.7 million rows, the second one (B): 2.1 millions. Records in A and B have a fairly identical size.
I can do any operation on A. It takes time, but it works. On B, I can't do anything. Even a simple select count(*) just hangs for ever. The problem is I don't see any error: it just hangs (when I show the process list it just says "updating" for ever).
It seems weird to me that the small delta (percentage-wise) between 1.7 and 2.1 million could make such a difference (from being able to do everything, to not even be able to do the simplest operation).
Can there be some kind of 2 million rows hard limit?
I am on Linux 2.6+, and I use innoDB.
Thanks!
Pierre
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
看起来它更多地取决于每行中的数据量而不是总行数。如果行包含的数据较少，则返回的最大行数将高于包含较多数据的行。检查此链接以获取更多信息：
http://dev.mysql。 com/doc/refman/5.0/en/innodb-restrictions.html
It appears it depends more on the amount of data in each row than it does on the total number of rows. If the rows contain little data, then the maximum rows returned will be higher than rows with more data. Check this link for more info:
http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html
第二个表的行大小（存储一行所需的字节数）可能要大得多。 Count(*) 可能需要全表扫描 - 即读取磁盘上的整个表 - 较大的行意味着更多的 I/O 和更长的时间。
索引的存在/不存在也可能会产生影响。
The row size (the number of bytes needed to store one row) might be much larger for the second table. Count(*) may require a full table scan - ie reading through the entire table on disk - larger rows mean more I/O and longer time.
The presence/absence of indexes will likely make a difference too.
正如我在第一篇文章中所说，问题是两个表非常相似，因此两个表中的行大小相当接近。这就是为什么我有点惊讶，我开始想，也许，不知何故，在某个地方设置了 200 万的限制。
事实证明我的表已损坏。这很奇怪，因为我仍然能够访问一些记录（使用与其他表的联接），并且 mySQL 没有“抱怨”。我通过检查表发现：它没有返回任何错误，但每次都会使 mysqld 崩溃......
无论如何，谢谢大家对此的帮助。
皮埃尔
As I was saying in my initial post, the thing was the two tables were fairly similar, so row size would be fairly close in both tables. That's why I was a bit surprised, and I started to think that maybe, somehow, a 2 million limit was set somewhere.
It turns out my table was corrupted. It is bizarre since I was still able to access some records (using joins with other tables), and mySQL was not "complaining". I found out by doing a CHECK TABLE: it did not return any error, but it crashed mysqld every time...
Anyway, thank you all for your help on this.
Pierre