主键 - 随机字节好或坏

Question

Ive read a few articles on this subject and just wanted to get some clarificiation regarding using random primary keys instead of auto increment. I am building an application and instead of using auto-increment primary keys, I am using the following code to generate a random primary key that includes both numbers and letters:

$bytes = random_bytes(8);
$bytes = bin2hex($bytes);

The reason I would like to use random primary keys is to prevent url tampering. My application also has record level security but I wanted random primary key as a secondary measure. Also, I have this column set to unique as to prevent any rare collisions. If there is a collision i just throw on on screen error asking using to resubmit the form which generates another random key. So my questions are as follows:

1. My db is innodb and my application is all relationship based (PK/FK relationships). Does having a random key (using the code above) effect search performance and/or indexing speed as compared to auto increment key?
2. Are there any reasons I should not be using a random key (as the primary / indexed key) other than possible collision which I have protected against using duplicate error handling?
3. Before I finalize this application, are there any other suggestions or best practices I should be using as it relates to primary key other than how I am doing it above?

Thank you. Again, i know there are other articles out there but many of them are outdated (prior to php 7 random bytes) so I thought id get a fresh take on whether or not random primary keys are considered best practice (why or why not). Thanks.

Answer 1

Bad -- very bad, in fact. Random primary keys play havoc with MySQL's page cache, which will make performance poor under load. It also means some SQL features will be unavailable to you, like multiple-row INSERT.

If you want to protect the ID in a URL from tampering, consider including a keyed hash (e.g, HMAC) of the identifier in the URL.