mysql 定义列 unique,MySQL将列定义为具有AUTO_INCREMENT的UNIQUE而不是主键

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…

解决方案

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: 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: 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: 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: 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: 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.

Please beat this contractor with a clue stick!

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.