英文:
API REST & MySQL : using both auto increment ID and uuid
问题
我正在创建一个与MySQL数据库相关的REST API。我想知道是否使用自动递增的ID作为主键(以保持良好的性能),以及唯一的UUID字段(用作API ID),是一个不好的主意?如果是,为什么?
(来自评论)UUID的目的是在API中提供一个不透明的ID,同时在内部使用更简单、更高效的BIGINT。
英文:
I am creating a REST API with a MySQL database. I would like to know if using auto-incrementing IDs as primary keys, (to keep good performance) and unique uuid fields (used as API ID) is a bad idea? If so why?
(from Comment) The purpose of the UUID is to provide an opaque id in the API, while using a simpler, more efficient, BIGINT for internal purposes.
答案1
得分: 1
UUIDs 具有以下优点:
- 它们可以由多个客户端独立创建,同时保持唯一性。
- 它们对标识进行混淆。(示例:避免黑客发现有效的标识。)
标识(IDs) 具有以下优点:
- 需要更少的磁盘空间(和缓存),因此速度较快。
- 暂时定向(“最近”插入会“聚合”在一起)。这对于非常大的表格(或较小的 RAM)是一种性能优势。
“自然”主键(一列或多列的组合,具有内在唯一性):
- 可能更小
- 可能更快
- 更合乎逻辑。
- 示例:在多对多映射表的情况下(只有两个标识指向其他两个表),
PRIMARY KEY(a_id, b_id), INDEX(b_id, a_id)
显然更快且更小。
UUIDs 长度为 36 或 16 字节;标识的长度为 8 字节或更小。自然主键可能不占用额外的字节(也可能不占用)。
回答你的问题: “取决于情况”。
我建立的表格具有主键:
- 自然主键 - 表格的三分之二
- 自增标识 - 表格的三分之一
- UUID - 几乎没有。
(附注:我认为 REST 不够灵活且没有真正的好处,因此我避免使用它。)
基于评论
可能你需要:
- 在数据库的所有地方都使用自增标识;
- 在向用户不透明地发送时使用 UUID。这可以避免各种可能会用自增标识进行的黑客操作。
因此,在主表格中,
CREATE TABLE main (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
uuid BINARY(16) NOT NULL,
....
PRIMARY KEY (id),
UNIQUE(uuid),
...
) ENGINE=InnoDB
在创建新行时,计算一个新的 UUID,去掉连字符并进行 FROM_HEX()
转换。
在向用户发送消息时,包括 uuid
,而不是 id
。
在接收回复消息时,通过可用的索引快速切换到使用 id
。也许可以这样实现:
SELECT id FROM main WHERE uuid = ?
英文:
UUIDs have these benefits:
- They can be created independently by multiple clients, while being unique.
- They obfuscate the ids. (Example: avoid hackers discovering valid ids.)
IDs have these benefits:
- Smaller disk space (and cache) needed, hence somewhat faster.
- Temporarily oriented ("recent" inserts are clustered "together"). This is a performance benefit for very large tables (or small RAMs).
"Natural" Primary keys (a column or combination of columns that is intrinsically unique):
- may be smaller
- may be faster
- more logical.
- Example: In the case of a many-to-many mapping table (just 2 ids pointing to two other tables),
PRIMARY KEY(a_id, b_id), INDEX(b_id, a_id)
is clearly faster and smaller.
UUIDs are 36 or 16 bytes; ids are 8 bytes or 4 or smaller. A natural key may take 0 extra bytes (or may not).
To answer your question: "It depends".
The tables I build have PKs:
- Natural - 2/3 of the tables
- Auto_inc - 1/3
- UUID - essentially none.
(PS: I find REST to be clumsy and provide no real benefits, so I avoid it.)
Based on Comment
Probably you what:
- An auto_inc id everywhere in the database;
- A UUID for opaquely sending to the user. This avoids various hacking games that might be played with an auto_inc.
So, in the the main table,
CREATE TABLE main (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
uuid BINARY(16) NOT NULL,
....
PRIMARY KEY (id),
UNIQUE(uuid),
...
) ENGINE=InnoDB
When creating a new row, compute a new UUID, strip the dashes and convert FROM_HEX()
.
When sending a message to the user, include uuid
, not id
.
When receiving a reply message, quickly switch to using id
by looking it up via that available index. Perhaps this way:
SELECT id FROM main WHERE uuid = ?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论