MySQL：附加 id 列与性能
Table1 有 3 列：id（自动增量）、citynumber（唯一）、description（varchar（1000））。 查询中从不使用列 id。它只是为了我的方便而使用（为了查看表如何增长，引用 id 比引用 citynumber 更容易，尽管在进行 sql 查询时从不使用 id）。
您强烈建议删除 id 列以提高性能吗？
Table1 has 3 columns: id (autoincrement), citynumber (unique), description (varchar(1000)).
Column id is never used in queries. It's just used for my convenience (to see how the table grows, it's easier to refer to the id, than to the citynumber, although id is never used when make sql queries).
Would you highly recommend to remove id column to increase performance?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
删除 id 列不会提高性能，或者至少不会以明智的方式提高性能。您只能保存同一磁盘。而且，PK能给你带来的好处通常比节省空间要好得多。
Removing id column will not increase the performance or, at least, not in a sensible way. You can only save same disk. Besides, the benefits that a PK can give you are usually much more better than saving space.
我知道这是有争议的，而且一般来说，我认为最好的做法是在表上保留索引，即使它们是查找表。只是我的意见，使用了遗留代码和数据库表，这些代码和数据库表中没有索引，稍后需要它们。是的，这是一个较小的更新语句，但数据库仍然需要相应地重新索引所有内容，这可能会影响此更新期间的性能。
只是我的意见。
i know this is up for debate, and generally i believe it best practice to keep indexes on tables, even if they are look tables. just my opinion having worked with legacy code and database tables that don't have indexes in them, which need them later. yes, this is a minor update statement, but the database still needs to reindex everything accordingly, which could affect performance during this update.
just my opinion.
不，ID 列几乎总是存在。如果您要添加更多表并想要加入它们，ID 列将会派上用场。它根本不会影响性能，并且只有很小的空间消耗差异。
No, ID columns are almost always there. If you were ever to add more tables and wanted to join them, the ID column will come in handy. It should not effect performance at all, and only minor space consumption difference.
性能将取决于您的查询。如果 citynumber 确实是唯一的（这是自然键吗？），那么如果您要加入或过滤的内容是在该列上，那么在该列上放置索引将有很大帮助。
performance will depend on your queries. if citynumber is indeed unique (is that a natural key?) then putting an index on that column will help greatly if that's what you're joining or filtering on.
假设您只在数据库中存储真实（非虚构）的城市，您可以轻松存储世界上所有的城市，而无需担心性能 - 数据集很小。
Assuming that you only store real (non-ficticious) cities in your database, you can easily store all of the world's cities without having to worry about performance at all - the data set is to tiny.
在InnoDB中，PK比Unique索引工作得更快，因为PK指向数据，而Unique指向PK，只有在PK之后才指向数据。
因此，删除 id，并将 citynumber 设置为主键。
尽管如此，它不会给你带来太多的性能提升。
In InnoDB PK works faster than Unique index, because PK points to data, and Unique points to PK and only after that - to data.
So, remove id, and make citynumber a primary key.
Stil, it's not going to give you much performance improvment.