Mysql如何创建聚簇索引? [英] Mysql How do you create a clustered index?

问题描述

我正在阅读有关聚簇索引如何工作的所有信息，并认为它们将对我的应用程序有益.我知道主键是自动聚集索引，但是如何将聚集索引添加到非主键列中?

I'm reading all about how clustered indexes work, and think they would be beneficial to my app. I understand that primary keys are automatically clustered indexes, but how would you add a clustered index to a non-primary key column?

即用户帖子的数据存储.每个帖子都有一个ID，但也有一个用户ID，但是由于用户可以多次发布，因此该用户ID不是主键.您将如何在用户ID中添加聚簇索引，这甚至是个好主意吗?

I.e. a datastore for user posts. Each post has a ID, but also has a user-id, but since users can post multiple times, the user-id is not a primary key. How would you add a clustered index to the user-id, and is that even a good idea?

推荐答案

根据

According to Clustered and Secondary Indexes, you can have only one clustered index per table.

除聚集索引以外的所有索引都称为辅助索引.

All indexes other than the clustered index are known as secondary indexes.

如果表没有主索引而是另一个唯一索引，则将其用作聚集索引.

If a table has no primary index but another unique index, this is used as the clustered index.

如果未为表定义PRIMARY KEY，则MySQL会找到第一个UNIQUE索引，其中所有键列都不为NULL，InnoDB会将其用作聚集索引.

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

因此，我想得出的结论是，您自己不会添加聚簇索引，但是MySQL选择表的主索引或第一个唯一索引作为聚簇索引.

So, I would conclude, that you don't add a clustered index yourself, but MySQL chooses either the primary or the first unique index of a table as the clustered index.

如果您尚未定义主索引或唯一索引，则MySQL会自己创建一个索引

If you haven't defined a primary or unique index, MySQL creates an index itself

如果表没有PRIMARY KEY或合适的UNIQUE索引，则InnoDB在包含行ID值的合成列上内部生成一个名为GEN_CLUST_INDEX的隐藏的聚集索引.这些行由InnoDB分配给此类表中的行的ID进行排序.行ID是一个6字节的字段，随着插入新行而单调增加.因此，按行ID排序的行实际上在插入顺序上.

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

这篇关于Mysql如何创建聚簇索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！