外键列的聚类索引
考虑一个消息系统的以下示例：
create table chat_group
(
id int auto_increment primary key,
title varchar(100) not null,
date_created date not null
)
create table chat_message
(
id int auto_increment,
user_id int not null,
chat_group_id int not null,
message text charset utf8mb4 not null,
date_created datetime not null
)
现在，我看到chat_message表的最常见请求是select * select * select * from cat_group_id =？。因此，我的想法是在CHAT_GROUP_ID列上放置一个群集索引，以便聊天消息将由磁盘上的组组织。
但是在MySQL中，它需要主键（实际上是群集索引）才是唯一的，那么这里的解决方案是什么？我为给定情况做出什么聚类索引。
Consider the following example of a messaging system:
create table chat_group
(
id int auto_increment primary key,
title varchar(100) not null,
date_created date not null
)
create table chat_message
(
id int auto_increment,
user_id int not null,
chat_group_id int not null,
message text charset utf8mb4 not null,
date_created datetime not null
)
Now I see that the most common request for the chat_message table is SELECT * FROM chat_message where chat_group_id = ?. So my idea is to put a clustered index on chat_group_id column so the chat messages will be organized by groups on the disk.
But in MySQL it requires PRIMARY KEY(which actually is a clustered index) to be unique, so what is the solution here? What clustered index do I make for the given situation.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的，“您也可以吃蛋糕也可以吃”：
PK提供了小组的“聚集”；这可能会加快您的主要查询。包括
ID使其独特，这是PK的要求（在MySQL中）。次级
索引（id）是保留auto_increment快乐所需的最低限度 - 即以某些 index 从开始ID。Yes, "you can have your cake and eat it, too":
The PK provides "clustering" by the group; this is likely to speed up your main queries. Including
idmakes it UNIQUE, which is a requirement (in MySQL) for the PK.The secondary
INDEX(id)is the minimum needed to keepAUTO_INCREMENThappy -- namely having some index starting with the id.