独特的约束条件使散列无用? [英] unique constraint makes hashes useless?

问题描述

我很困惑，如果在MySQL中唯一的约束是物理索引还是虚拟索引?并且由于在列上定义了唯一约束，那么是否有必要也创建一个哈希列，并在哈希列上定义索引以加快查询过程，因为唯一约束列包含可变数量的字符(大多数超过40个字符)，并且平均50岁以上?

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+?

总记录超过150+百万.

And total records are 150+ million plus.

我问了另一个有关散列及其索引计划的详细信息. 考虑RAM的网址或哈希索引

I have asked another question with details of hashes and its indexing plan. index on url or hashing considering RAM

推荐答案

唯一约束几乎可以维护索引，但从技术上讲，它是需要索引的约束.但是，如果您要采用散列只是为了使搜索更快，而不是需要使散列为唯一，并且不必担心选择强散列的冲突.如果您具有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.

这篇关于独特的约束条件使散列无用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！