具有复合索引但不具有主键的MySQL表 [英] Mysql table with composite index but not primary key
问题描述
我需要一个表来存储一些评分，在此表中，我有一个综合索引(user_id，post_id)和其他列，以标识不同的评分系统.
I need a table to store some ratings, in this table I have a composite index (user_id, post_id) and other column to identify different rating system.
user_id - bigint
post_id - bigint
type - varchar
...
Composite Index (user_id, post_id)
在此表中，我没有主键，因为主键必须是唯一的，而INDEX不必是唯一的，在我的情况下，唯一性是个问题.
In this table I've not a primary key because the primary need to be unique and the INDEX not need to be unique, in my case univocity is a problem.
例如，我可以拥有
INSERT INTO tbl_rate
(user_id,post_id,type)
VALUES
(24,1234,'like'),
(24,1234,'love'),
(24,1234,'other');
缺少PRIMARY KEY可能会导致性能问题?我的表结构好还是需要更改?
The missing of PRIMARY KEY may cause performance problem? My table structure is good or I need to change it?
谢谢
推荐答案
几点:
听起来您只是在使用表的当前唯一性，并将其用作主键.那个有效.由于本地性，自然键在查询方面具有一些优势. (每个用户的数据存储在同一区域中).而且由于表是由该键聚集的，因此如果您按主键中的列进行搜索，则可以消除对数据的查找.
It sounds like you are just using what is currently unique about the table and making that as a primary key. That works. And natural keys have some advantages when it comes to querying because of locality. (The data for each user is stored in the same area). And because the table is clustered by that key which eliminates lookups to the data if you are searching by the columns in the primary.
-
但是，使用您选择的自然主键对性能也有不利影响.
But, using a natural primary key like you chose has disadvantages for performance as well.
使用非常大的主键会使innodb中的所有其他索引变得非常大，因为主索引包含在每个索引值中.
Using a very large primary key will make all other indexes very large in innodb because the primary key is included in each index value.
使用自然主键的速度不如INSERT的替代键快，因为除了更大以外，它不能每次都只插入表的末尾.它必须在该用户的部分中插入并发布等.
Using a natural primary key isn't as fast as a surrogate key for INSERT's because in addition to being bigger it can't just insert at the end of the table each time. It has to insert in the section for that user and post etc.
此外，如果u最有可能按时间搜索，除非时间是第一列，否则您将使用自然键在整个表中搜索.代理键在时间上往往是本地的，对于某些查询通常可能恰好是正确的.
Also, if u are searching by time most likely you will be seeking all over the table with a natural key unless time is your first column. surrogate keys tend to be local for time and can often be just right for some queries.
使用像您这样的自然键作为主键也可能很烦人.如果您想提及特定的选票怎么办?您需要一些字段.同样，与许多ORM一起使用也有点困难.
Using a natural key like yours as a primary key can also be annoying. What if you want to refer to a particular vote? You need a few fields. Also it's a little difficult to use with lots of ORMs.
这是答案
我将创建自己的代理键并将其用作主键，而不是依赖innodb的内部主键，因为您将能够使用它进行更新和查找.
I would create your own surrogate key and use it as a primary key rather than rely on innodb's internal primary key because you'll be able to use it for updates and lookups.
ALTER TABLE tbl_rate
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY(id);
但是，如果您确实创建了代理主键，那么我还将使您的键成为唯一键.成本相同，但可以确保正确性.
But, if you do create a surrogate primary key, I'd also make your key a UNIQUE. Same cost but it enforces correctness.
ALTER TABLE tbl_rate
ADD UNIQUE ( user_id, post_id, type );
这篇关于具有复合索引但不具有主键的MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
