在 MySQL 中，我们如何判断表的索引是否是聚集的? [英] In MySQL, how can we tell if an index of a table is clustered or not?

问题描述

在 MySQL 中，我们如何判断表的索引是否是聚簇的?

In MySQL, how can we tell if an index of a table is clustered or not?

我们可以使用 show index from table-name 来获取有关表索引的信息.但是我没有发现它显示每个索引是聚集的还是非聚集的.

We can use show index from table-name to get information about the table's indexes. But I don't find it shows whether each index is clustered or nonclustered.

这是为了 如果 MySQL 中的表有索引，那么该表是否必须有聚集索引?

推荐答案

在默认存储引擎 InnoDB 中，PRIMARY KEY 索引始终是聚集索引.

In the default storage engine, InnoDB, the PRIMARY KEY index is always the clustered index.

如果您没有 PRIMARY KEY，则它是非空列上的第一个 UNIQUE KEY 索引.

If you don't have a PRIMARY KEY, it's the first UNIQUE KEY index on non-null columns.

如果非空列上没有 PRIMARY KEY 或 UNIQUE KEY，则 InnoDB 具有隐藏的聚集索引.在这种情况下，您不能使用此聚集索引进行查询.

If you don't have either a PRIMARY KEY or UNIQUE KEY on non-null columns, then InnoDB has a hidden clustered index. You can't make a query use this clustered index in this case.

参见 https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

如果表没有 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.

MyISAM 是另一种常见的存储引擎.MyISAM 不支持聚集索引.

MyISAM is the other common storage engine. MyISAM does not support a clustered index.

这篇关于在 MySQL 中，我们如何判断表的索引是否是聚集的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！