当主键值非常高时，MySQL 会变慢吗？
如果我的表中主键的值范围是从 200,000,000 到 200,000,100
查询会比值是 1000 到 1100 时慢很多吗？
If the values of the primary key in my table range from 200,000,000 to 200,000,100
Will queries be much slower than if the values were 1000 to 1100?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
不会。除非您对这些数字使用 char/varchar 字段，否则数字将以固定大小的原始二进制格式存储，并且数字的“大小”对搜索速度没有影响 -
1将在数据库中占用与999999999999一样多的空间。No. Unless you're using char/varchar fields for those numbers, a number is stored in a fixed-sized raw binary format, and the "size" of a number has no bearing on search speeds -
1will occupy as much space inside the database as999999999999.你的具体问题的答案是否定的，它们不会慢很多。
也许需要几纳秒，因为额外的位会通过线路发送并进行处理，但没有什么意义。
但是，如果您的问题是“200,000,100 行会比 1,100 行慢吗？”那么是的，它会慢一点。
The answer to your specific question is no, they will not be much slower.
Maybe a few nanoseconds as the extra bits get sent down the wire and processed, but nothing significant.
However, if your question was "will having 200,000,100 rows be slower than 1,100?" then yes, it would be a bit slower.
不会，但正如其他人所指出的，表中的记录数量较多通常需要更多 IO 来检索记录，尽管可以通过索引来缓解这一情况。
主键的数据类型会略有不同（200M 仍然适合 4 字节 INT，但 8 字节 BIGINT 会稍大，而 CHAR(100) 会更浪费等）。此选择将导致该表、其索引以及具有该表外键的其他表的存储需求稍大。
No, but as others have noted, a larger number of records in your table will generally require more IO to retrieve records, although it can be mitigated through indexes.
The data type for your primary key will make a slight difference (200M still fits into a 4 byte INT, but an 8 byte BIGINT will be slightly larger, and a CHAR(100) would be more wasteful etc). This choice will result in slightly larger storage requirements, for this table, its indexes, and other tables with foreign keys to this table.
嗯，定义慢得多......
值 1000-1100 可以放入 SMALLINT（2 字节）中，而 200,000,000 则必须放入 INT（4 字节）中。因此，内存中 SMALLINT 的记录数量是 INT 的两倍。所以1000-11000会更快
Well, define much slower.....
The values 1000-1100 can fit in a SMALLINT (2 bytes) and 200,000,000 will have to be put into an INT (4 bytes). So there can be twice as many records in memory for a SMALLINT as for an INT. So 1000-11000 will be faster
不，否则算法会非常愚蠢......你的值完全适合 32 位整数。
Nope, otherwise the algorithm would be very stupid... Your values perfectly fit into 32 bit integer.
查询将完全相同。
然而，当表中有 200,000,000 到 200,000,100 行时，与 1000 到 1100 行相比，它们会变慢。
The queries will be exactly the same.
They will be slower, however, when you have between 200,000,000 to 200,000,100 rows in your table when compared to 1000 to 1100 rows.
一点也不。根本不会。 mysql 开发人员非常注重高价值-速度互操作性。
Not at all. It wouldn't at all. The mysql developers are very particular about high value - speed interoperability.