主键索引的类型以及在MySQL中修改它们的情况
评分最高的答案在这里如何存储mysql记录按主索引的顺序。这是否意味着创建的主要索引是稀疏索引？如果是这样，如果一个人通过更改构造或修改其中一个条目的列来修改主键，会发生什么。
很抱歉在同一帖子中提出两个问题，但我认为最好一起问他们。我在查询查询时非常慢的桌子时遇到了疑问，所以我认为可能以某种方式插入行，这是基于表中的实际列会有所帮助的。
The highest rated answer here mentions how in MySQL records are stored in the order of primary index. Does that mean the primary index created is a sparse index? And if so, what happens if one modifies the primary key, by changing the column on which it is constructed or modifying one of the entries.
Apologies for asking 2 questions in the same post, but I thought it was better asking them together. I came upon this doubt while dealing with tables which were extremely slow when queried, so I thought maybe inserting rows in some manner which is based on an actual column in the table would help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我将回答MySQL的默认存储引擎InnoDB。这是您应该在MySQL中使用的所有表，除非您有非常具体的原因。
问题1：主要键不是稀疏索引。我知道该术语是指表格中仅具有一个行的值的索引。 ie带有<代码>的索引子句。但是，主键必须对表中的所有行表示。如果您需要唯一地引用任何单行，则使用的是您使用的列或列。
即使按主键值按顺序存储群集索引，它不一定以该顺序存储在磁盘上。 InnoDB表空间中的每个页面都有一个指向“下一个”页面的链接，该页面可能不会立即位于后面。它可能在文件中，甚至更早。这些页面可能会散布在其他索引的页面上（如果您使用共享表空间，甚至其他表）。这是稀疏索引的意思吗？
答案问题2：如果您更改表主要键的列，则必须重组表的存储。 InnoDB将主键用作聚类索引，这意味着其余的列与B树数据结构的叶子节点一起存储。更改主键列可能会更改存储顺序，也可以更改每个B树节点（内部节点和叶子节点）的大小。
这意味着，当您重组主钥匙时，您的服务器暂时需要大量额外的存储空间，最大程度是表格的两倍。重组完成后，表的旧版本将自动删除。
I'll answer about MySQL's default storage engine, InnoDB. This is the storage engine you should use for all tables in MySQL, unless you have a very specific reason not to.
Answer to Question 1: The primary key is not a sparse index. I know that term to refer to an index that only has values for a subset of rows in the table. I.e. an index with a
WHEREclause. But the primary key must account for all rows in the table. It is the column or columns you use if you need to reference any single row uniquely.Even though the clustered index is stored in order by primary key values, it isn't necessarily stored in that order on disk. Each page in an InnoDB tablespace has a link to the "next" page, which may not be physically located immediately after. It may be much later in the file, or even earlier. The pages may be interspersed with pages for other indexes (even other table if you are using a shared tablespace). Is this what you meant by sparse index?
Answer to Question 2: If you change the column(s) of the table's primary key, the storage of the table must be restructured. InnoDB uses the primary key as the clustered index, which means the rest of the columns are stored along with leaf nodes of the B-tree data structure. Changing the primary key column(s) may change the order of storage, and also the size of each B-tree node (both internal nodes and leaf nodes).
This means while you are restructuring the primary key, your server temporarily needs a lot of extra storage space — up to double the size of the table. Once the restructuring is finished, the old version of the table is automatically dropped.