JZTXT

Clustered Index 是什么？

发布时间 2023-06-02 08:17:45作者: zno2

聚集索引

Each InnoDB table has a special index called the clustered index that stores row data. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, it is important to understand how InnoDB uses the clustered index to optimize the common lookup and DML operations.

  • When you define a PRIMARY KEY on a table, InnoDB uses it as the clustered index. A primary key should be defined for each table. If there is no logical unique and non-null column or set of columns to use a the primary key, add an auto-increment column. Auto-increment column values are unique and are added automatically as new rows are inserted.

  • If you do not define a PRIMARY KEY for a table, InnoDB uses the first UNIQUE index with all key columns defined as NOT NULL as the clustered index.

  • If a table has no PRIMARY KEY or suitable UNIQUE index, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains row ID values. The rows are ordered by the row ID that InnoDB assigns. 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 order of insertion.

通常等同主键

没有主键用唯一索引

没有主键和唯一索引，自动添加隐藏聚簇索引，行号

 

除Clustered Index 外，其他都是非聚集索引

 

参考：https://docs.oracle.com/cd/E17952_01/mysql-5.6-en/innodb-index-types.html

参考：https://www.cnblogs.com/zno2/p/17087161.html  （介绍InnoDB 存储引擎时，提到了 Clustered Index)