Mysql PK 和 FK char(36) 与 int(10)
我读到，如果表键是整数而不是 char(36) UUID，mysql 可以更好地处理索引和搜索。
值得转换为 int(10) 吗？
注意：我们只使用 1 台服务器，并且目前必须计划同时使用多个数据库，因此消除了使用 UUID 的重要原因之一。如果这有区别的话，我们的表也是 InnoDB。
提前致谢。
I have read that mysql can handle indexes and searches better if the tables keys are integers rather than char(36) UUID's.
Is it worth converting to int(10)?
NOTE: We only use 1 server and have currently have to plans to use multiple databases concurrently therefore removing one of the big reasons for using UUID. Our tables are also InnoDB if that makes a difference.
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
通常，RDBMS 可以比其他数据类型更有效地处理整数键，因为 PK 更有效。原因是它如何构建列的索引，所以是的：只要您不需要字符串（或其他类型）键，您就应该始终使用整数。
但是：CHAR(36) 和 INT(10) 远非相等，因为 INT(10) 比 CHAR(36)小得多。我不知道您是否需要这么多不同的密钥，但您应该记住这一点。
更新，完成最后一段：
INT(10)为 32 位，CHAR(36)为 36 - ^Byte^（= 288 位）。这不仅意味着INT消耗更少的空间，还意味着CHAR(36)拥有大约 4 倍的不同键。Usually RDBMS can handle Integer keys as PK more efficient, than other datatypes. The reason is how it build up the index for the column, so yes: As long as you dont require string (or other typed) keys, you should always use integers.
However: CHAR(36) and INT(10) are far away from being equal, because a INT(10) is much smaller, than CHAR(36). I dont know, if you require so many different keys, but you should keep that in mind.
Update, to complete the last paragraph:
INT(10)is 32-Bit,CHAR(36)is 36 - ^Byte^ (= 288-Bit). This does not only mean, thatINTconsumes less space, it also means, thatCHAR(36)has about 4 times more different keys.UUID 是已索引的行，但众所周知，由于索引大小，字符键比数字键慢。
但是，如果您需要 UUID 或字符连接/索引，则必须考虑到这一点：
我分享此信息并在生产环境中对其进行了测试，可以在http://forums.mysql.com/read.php?24,263462,263462
UUID's are an already indexed row, but as all people knows, character keys are slower than numeric keys because the index size.
But if you need an UUID or character join / index you have to take this into account:
I share this info and have tested it in a production environment and it can be found in http://forums.mysql.com/read.php?24,263462,263462
InnoDB 带来了巨大的变化。
如果您在表上定义主键，InnoDB 将使用它作为聚集索引。
在巨大的 innodb 表上执行 DML 操作非常昂贵。如果您有很多连接，请使用整数。如果你有很多 dml 使用 int。如果您的选择比 dml 更多，请使用自然键，无论 int 或 char
InnoDB makes huge difference.
If you define a PRIMARY KEY on your tables, InnoDB uses it as the clustered index.
It's very expensive to do DML operations on huge innodb tables. If You have many joins, use integers. If You have many dml use int. If You have much more selects than dml use natural keys, no matter int or char