MySQL 是否为主键创建了一个额外的索引或使用数据本身作为“索引"? [英] Does MySQL create an extra index for primary key or uses the data itself as an "index"
问题描述
找不到明确的答案.我知道，当你创建主键时，MySQL根据该主键对数据进行排序，问题是，它是否真的创建了另一个索引，或者因为它应该按主键排序而使用实际数据作为索引?
如果我的表有索引 A 和索引 B 并且没有主键，我有数据 + 索引 A + 索引 B.如果我将表更改为索引 A 的列作为主键，我只会有数据(也用作索引)+索引 B 对吗?以上是内存使用情况
每个 InnoDB 表都有一个称为聚集索引的特殊索引，用于存储行的数据.通常，聚集索引与主键同义. 要从查询、插入和其他数据库操作中获得最佳性能，您必须了解 InnoDB 如何使用聚集索引来优化最常见的查找和 DML 操作每张桌子.
当您在表上定义 PRIMARY KEY 时，InnoDB 将其用作聚集索引
如果你没有为你的表定义 PRIMARY KEY，MySQL 会定位第一个 UNIQUE 索引，其中所有的键列都是 NOT NULL 并且 InnoDB 使用它作为聚集索引.>
如果表没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB 在包含行 ID 值的合成列上内部生成一个名为 GEN_CLUST_INDEX 的隐藏聚集索引. 行按 ID 排序InnoDB 分配给此类表中的行.行 ID 是一个 6 字节的字段，随着插入新行而单调增加.因此，按行 ID 排序的行在物理上是按插入顺序排列的.
聚集索引如何加快查询速度
通过聚簇索引访问一行很快，因为索引搜索直接通向包含所有行数据的页面.如果表很大，聚簇索引架构通常会节省一个磁盘 I/与使用与索引记录不同的页面存储行数据的存储组织相比，O 操作.
Cant find a explicit answer of that. I know that when you create a primary key, MySQL orders the data according to that primary key, question is, does it actually create another index, or uses the actual data as an index since it should be ordered by the primary key?
EDIT:
if I have a table with has index A and index B and no primary key, I have the data + index A + index B. If I change the table to have columns of index A as the primary key, I will only have data (which is also used as an index) + index B right? The above is in terms of memory usage
Clustered and Secondary Indexes
Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table.
When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index
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.
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.
How the Clustered Index Speeds Up Queries
Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record.
这篇关于MySQL 是否为主键创建了一个额外的索引或使用数据本身作为“索引"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
