唯一约束使散列无用？
我很困惑MySQL中的唯一约束是物理索引还是虚拟索引？既然在列上定义了唯一约束，那么考虑到唯一约束列包含可变数量的字符（大多超过 40 个字符），是否还需要创建一个哈希列并在哈希列上定义索引以加快查询过程平均50+？
总记录数超过150+万条。
我问了另一个问题，其中包含哈希值及其索引计划的详细信息。 考虑 RAM 的网址索引或哈希
I am confused if in MySQL unique constraint is physical index or virtual index? And as unique constraint is defined on a column then, Is there any need to make a hash column also and defining index on hash column to speed up the process of queries considering that unique constraint column contains variable number of characters mostly above 40 characters and on average 50+?
And total records are 150+ million plus.
I have asked another question with details of hashes and its indexing plan. index on url or hashing considering RAM
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
唯一约束几乎维护了一个索引，但从技术上讲，它是一个需要索引的约束。但是，如果您想采用哈希只是为了使搜索速度更快，那么您需要使哈希列唯一，并且如果您选择强哈希，则无需担心冲突。如果您有 256 位散列，则 150+ 百万个样本不会太大而不会产生冲突机会，如果您选择平均有 50+ 个字符的其他文本列，这将为您节省 18 个字节。这样您可以节省大约 2GB 的索引空间，但代价是 4GB 的磁盘空间。
Unique constraint pretty much maintains an index but technically its a constraint which requires an index. But if you want to adopt hashes just to make search faster than you need to make hash column as unique and not need to worry about collision if you choose a strong hash. 150+ million sample is not too big to have collision chances if you have 256 bit hash which will save you 18 bytes if you choose other text column with 50+ characters avg. This way you can save around 2GB of you indexing space but at the cost of you 4GB of disk space.