MySQL将列定义为具有AUTO_INCREMENT的UNIQUE而不是主键 [英] MySQL defining column as UNIQUE with AUTO_INCREMENT instead of as Primary Key

问题描述

我正在与一个合同开发人员合作，该开发人员开始在不定义主键的情况下创建表(MySQL).相反，他正在定义具有UNIQUE约束且具有AUTO_INCREMENT的列.我以前从未见过这样做，所以我想了解以这种方式定义表的含义. 如果需要的话，不利的一面是无法创建外键.用这种方式创建表还有什么其他的好处，也有坏处的.也许我在这里错过了一个概念……

I'm working with a contract developer who is starting to create tables (MySQL) without define a Primary Key. Instead, he is defining a column with a UNIQUE constraint with an AUTO_INCREMENT. I've never seen this done before, so I wanted to understand the implications of defining tables this way. One downside would be not being able to create foreign keys if needed. What are some other implications, good or bad, of creating tables this way. Maybe I'm missing a concept here…

推荐答案

在没有显式主键的情况下定义MySQL是一个非常糟糕的主意.
如果缺少PK，MySQL将创建一个隐式(但非常真实)的整数自动递增主键.
此PK将包含在InnoDB的每个辅助键中，并将确定MyISAM中的主要排序顺序.

Defining MySQL without explicit primary keys is a very very bad idea.
If a PK is missing, MySQL will create an implicit (but very real) integer autoincrementing primary key.
This PK will be included in every secondary key in InnoDB and will determine your primary sort order in MyISAM.

后果
您刚刚降低了每次选择，插入和更新的性能.
毫无目的.

Consequence
You have just slowed down the performance of every select, insert and update.
For no purpose what so ever.

InnoDB:需要额外的查找才能获取表数据
在InnoDB中，需要进行额外的查找，因为所有二级索引都引用PK，而不引用行本身.

InnoDB: extra lookup required to get to table data
In InnoDB an extra lookup needs to be done, because all secondary indexes refer to the PK and not to the rows themselves.

MyISAM:浪费了空间
在MyISAM中，惩罚不是那么大，但是您仍然会拖着4个未使用的未使用的字段.

MyISAM: wasted space
In MyISAM the penalty is not that great, but you're still dragging along a 4 byte unused field that doesn't get used.

InnoDB + MyISAM:自动生成字段的无用生成
因为创建了隐式的自动增量PK，并且您还需要一个额外的自动增量键才能进行连接；为了防止重复的自动增量字段，您现在没有1个锁，而是每个插入有2个表锁.

InnoDB + MyISAM: Useless generation of autoincrement field
Because an implicit autoincrementing PK gets created, and you also needed a extra autoincrementing key to do joins; In order to prevent duplicate autoincrement fields, you now have not 1, but 2 table locks per insert.

InnoDB:与上述提到的查找探针加倍
如果您使用非PK字段进行联接，则InnoDB需要进行每个联接的额外查找，以获取该其他表的记录.

InnoDB: with joins the lookup probem mentioned above doubles
If you do a join using a field that's not the PK, InnoDB needs to do an extra lookup per join to get to the records of that other table.

InnoDB:最糟糕的是您失去了覆盖索引的好处
您已禁用InnoDB的最佳优化之一，它涵盖了索引.
如果MySQL只能使用索引中的数据来解析查询，它将永远不会读取表，这将显着提高速度.现在，InnoDB中每个索引的50％是未使用的空间，您已经不再使用这种优化了.

InnoDB: worst of all you lose the benefit of covering indexes
You have disabled one of the best optimizations in InnoDB, covering indexes.
If MySQL can resolve the query using only the data in the indexes, it will never read the table, this will result in a significant speed gain. Now that 50% of every index in InnoDB is unused space you have just nixed your chances of that optimization being used.

请用线索棒打败这个承包商！

链接:
http://www.xaprb .com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/(慢速链接，但建议阅读).
O'依靠InnoDB的覆盖索引 http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

Links:
http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/ (slow link, but recommend reading).
O'Reilly on InnoDB's covering indexes http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

顺便说一句，如果您的承包商说，没关系，因为他正在使用MyISAM，再次击败他，除非有充分理由，否则您应该始终使用InnoDB.
InnoDB在生产中要安全得多，MyISAM有其用途，但很容易损坏.

BTW, if your contractor says, it does't matter much because he's using MyISAM, beat him again, you should always use InnoDB unless you have a good reason not too.
InnoDB is much much safer in production, MyISAM has its uses but gets corrupted too easily.

这篇关于MySQL将列定义为具有AUTO_INCREMENT的UNIQUE而不是主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！