Innodb和MyIsam在B+树中的区别是什么？

左手的ㄟ右手 2023-02-20 04:56 33阅读 0赞

可以先了解下[B+树][B]

说起索引就引出了`聚集索引`和`非聚集索引`，它们的区别是什么呢？

`叶节点存放一整行记录的索引`叫`聚集索引`，反之，叫`非聚集索引`

## Innodb ##

*  按照主键B+树的排列方式存放，子节点存放的就是数据。（如果没有主键，以第一列为聚集索引）
 *  只有一个聚集索引。
 *  普通索引指向聚集索引。
 *  `叶子节点存放的是：除该索引外的一整行数据`

有一个人员表：主键为id，列为 name（索引），class，age。那么他们的存储方式为：  
数据为：  
![在这里插入图片描述][20200623143206894.png]  
聚集索引为

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70]  
而也可能会有很多普通索引，普通索引的子节点指向聚集索引。例如当普通索引通过xiaohuang查到id为1后，再到聚集索引中继续查询到子节点，也就是说通过普通索引查询会调用两次索引。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 1]

## MyIsam ##

*  普通索引和非聚集索引没什么区别。
 *  `叶子节点存放的是：磁盘地址`。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 2]

下面是聚集索引规则，感兴趣的同学，可以看下

## InnoDB选取聚集索引规则： ##

英文文档：

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. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
    
        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.
    
        How Secondary Indexes Relate to the Clustered Index
        All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
    
        If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.
    
        For guidelines to take advantage of InnoDB clustered and secondary indexes, see Section 8.3, “Optimization and Indexes”.
    
        InnoDB中有一个6字节的隐藏字段ROWID。 如果有主键（或者未建立主键但是有非空唯一索引，该列即为主键），则rowid的值与主键的值相等，并以主键构造聚集索引。 否则使用rowid（将其设置为自增）作为主键，并在rowid上建立聚集索引 GEN_CLUST_INDEX 。

翻译如下：

每个InnoDB表都有一个特殊的索引，称为聚集索引，用于存储行数据。通常，聚集索引是主键的同义词。要从查询、插入和其他数据库操作中获得最佳性能，您必须了解InnoDB如何使用聚集索引来优化每个表最常见的查找和DML操作。
    当你在表上定义一个主键时，InnoDB使用它作为聚集索引。为创建的每个表定义一个主键。如果没有逻辑惟一且非空的列或列集，则添加一个新的自动递增的列，其值将自动填充。
    如果你没有为你的表定义一个主键，MySQL找到第一个唯一的索引，其中所有的键列不是空的，InnoDB使用它作为聚集索引。
    如果表没有主键或合适的唯一索引，InnoDB内部会在包含行ID值的合成列上生成一个名为GEN_CLUST_INDEX的隐藏聚集索引。这些行是按照InnoDB给表中的行分配的ID排序的。行ID是一个6字节的字段，在插入新行时单调增加。因此，按行ID排序的行在物理上是按插入顺序排列的。
    聚集索引如何加速查询
    通过聚集索引访问一行非常快，因为索引搜索直接指向包含所有行数据的页面。如果表很大，与使用与索引记录不同的页存储行数据的存储组织相比，聚集索引体系结构通常会节省一次磁盘I/O操作。
    二级索引如何与聚集索引关联
    聚集索引以外的所有索引都称为二级索引。在InnoDB中，辅助索引中的每条记录都包含该行的主键列，以及为辅助索引指定的列。InnoDB使用这个主键值在聚集索引中搜索行。
    如果主键很长，辅助索引会使用更多的空间，所以使用短的主键比较有利。
    关于如何利用InnoDB聚集和二级索引的指导，请参见8.3节“优化和索引”。
    InnoDB中有一个字6节的隐藏字段ROWID。如果有主键(或者未建立主键但是有非空唯一索引,该列即为主键),则ROWID的值与主键的值相等,并以主键构造聚集索引。否则使用ROWID(将其设置为自增)作为主键,并在ROWID上建立聚集索引GEN_CLUST_INDEX。

[B]: https://blog.csdn.net/weixin_42740530/article/details/106637486
[20200623143206894.png]: https://img-blog.csdnimg.cn/20200623143206894.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200623143225775.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200623143245543.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2020062314332223.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA==,size_16,color_FFFFFF,t_70