“唯一"列字段是否暗示MySQL的索引，如果是，为什么? [英] Does a 'unique' column field imply an index with MySQL, and if so, why?

问题描述

尽管我知道在唯一列上具有索引非常方便-轻松高效地检查值冲突，但我认为应该由程序员指定唯一字段列是否要具有索引不论是大小优化还是速度优化.

据我了解，MySQL自动为指定为唯一的表列编制索引.是吗?

MySQL似乎不能有没有索引的唯一列的任何原因吗?是的，每个值的插入/更新都将是不带O(number_of_rows)的操作，但是就像我说的那样，难道不应该对程序员的决定征税吗?

很好奇！

解决方案

据我了解，MySQL自动为指定为唯一的表列编制索引.是吗?

是的.它不一定是自动的"，而是隐式的. UNIQUE是索引的类型，因此该列当然也已建立索引.

MySQL似乎不能有没有索引的唯一列的任何原因吗?

因为MySQL对每个INSERT/UPDATE进行全表扫描以维持唯一约束是愚蠢的.

修改:

是的，每个值插入/更新都将是不带O(number_of_rows)的操作，但是就像我说的那样，程序员不应该为该决定所累吗?

为什么程序员想要在数据存储层之外手动实施基本数据完整性?我知道您要怎么处理这个问题，但事实是索引不可能伤害任何东西(只占用更多的空间)-因此，确实没有要考虑的决定.

Although I understand that it is very convinient to have an index on a unique column - to easily and efficiently check for value collision, I think it should be up to the programmer to specify whether a unique field column is to have an index or not, with regards to size- vs speed- optimization.

As far as I understand MySQL automatically indexes a table column that is specified as unique. Is it so?

Is there any reason MySQL seemingly cannot have unique columns WITHOUT indexes? Yes, every value insertion/update will be O(number_of_rows) operation without one, but like I said, shouldn't the programmer be taxed with the decision?

Just curious!

解决方案

As far as I understand MySQL automatically indexes a table column that is specified as unique. Is it so?

Yes. It's not necessarily 'automatic' though, it's implicit. UNIQUE is type of index, so of course that column is indexed.

Is there any reason MySQL seemingly cannot have unique columns WITHOUT indexes?

Because it would be silly for MySQL to do a full table scan on each INSERT/UPDATE to uphold the unique constraint.

Edit:

Yes, every value insertion/update will be O(number_of_rows) operation without one, but like I said, shouldn't the programmer be taxed with the decision?

Why would a programmer want to manually enforce basic data integrity outside of the data storage layer? I understand where you're going with this, but the fact is an index can't possibly hurt anything (it just takes a bit more space) - so there really isn't a decision to consider.

这篇关于“唯一"列字段是否暗示MySQL的索引，如果是，为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！