bigint mysql_与INT相比，BIGINT mysql的性能

I'm trying to find out if my table will get less performant if I change the primary key to BIGINT(20). At the moment, I'm using INT(7) and have about 300.000 entries already with large IDs (7 or 8 digits).

I searched a lot already but only found out that it uses more disk-space (which is obvious).

All of my IDs have 7 digits right now, but my customer wants to change to 8 digits. I won't be able to easily change the software in the future, so I thought about using BIGINT(20) now just in case. Would it be less performant if I use BIGINT even though I don't need to yet?

Does anyone with experience doing this have suggestions regarding speed and performance?

解决方案

To answer your question: yes it'll get less performant. Obviously, the bigger the type, the bigger the table, the slower the queries (more I/O, bigger indexes, longer access time, result less likely to fit in the various caches, and so on). So as a rule of thumb: always use the smallest type that fits you need.

That being said, performance doesn't matter. Why? Because when you reach a point where you overflow an INT, then BIGINT is the only solution and you'll have to live with it. Also at that point (considering you're using an auto increment PK, you'll be over 4 billions rows), you'll have bigger performance issues, and the overhead of a BIGINT compared to a INT will be the least of your concerns.

So, consider the following points:

Use UNSIGNED if you don't need negative values, that'll double the limit.

UNSIGNED INT max value is 4.294.967.295. If you're using an auto increment PK, and you only have 300.000 entries, you really don't need to worry. You could even use a MEDIUMINT at the moment, unless you're planning for really really fast growth. (see http://dev.mysql.com/doc/refman/5.1/en/integer-types.html)

The number in parenthesis after the type doesn't impact the max value of the type. INT(7) is the same as INT(8) or INT(32). It's used to indicate the display width if you specify ZEROFILL (see http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html)