MyISAM索引解析、InnoDB索引解析

安娜和她的笔记本

已于 2022-12-27 00:44:35 修改

阅读量685

点赞数

分类专栏：数据库文章标签：数据库 java mysql

于 2022-12-27 00:24:08 首次发布

本文链接：https://blog.csdn.net/qq_55123599/article/details/128450372

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

我们经常说到的存储引擎是说数据库级别还是说表级别？
答：表级别。（数据库级别也可以设置，但是最终它的级别生效是在表级别）

1、MylSAM存储引擎索引实现

MylSAM索引文件和数据文件是分离的（非聚集）
在这里插入图片描述
MyIsam 存储引擎独立于操作系统，简单说就是可用在windows上使用，也可用将数据转移到Linux操作系统上。系统兼容性很好！！！。这种存储引擎在建表的时候，它会创建3个文件。分别是（.frm, .MYD, .MYI），简单说明一下：.frm 存储表的定义（也就是表结构啦），.MYD 就是表里面的数据，.MYD存储索引。这样的划分操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。

特点:
1、不支持事务
2、不支持外键
3、查询速度很快。如果数据库insert和update的操作比较多的话采用表锁效率低（建议使用innodb）。

2、InnoDB存储引擎索引实现

lnnoDB索引实现（聚集）

表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引-叶节点包含了完整的数据记录
为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

InnoDB支持事务和行锁设计、支持外键、支持全文索引（InnoDB1.2之后版本），主要面向OLTP（On-Line Analytical Processing，在线事务处理）数据库应用，支持类似Oracle的非锁定读，即默认读取操作不会产生锁。

从MySQL5.5.8版本开始，InnoDB存储引擎就是默认的存储引擎， InnoDB存储引擎将数据放在一个默认的表空间中，这个表空间像黑盒一样由InnoDB存储引擎管理

InnoDB通过使用多版本并发控制MVCC来获的高并发性，并且实现SQL标准的四种隔离级别，默认是REPEATABLE，同时使用next-key locking的策略来避免幻读

除此之外，InnoDB存储引擎还提供了以下四种高性能和高可用的功能：
（1）插入缓冲（insert buffer）
（2）二次写（double write）
（3）自适应哈希索引（adaptive hash index）
（4）预读（read ahead）

对于表中数据的存储，InnoDB存储引擎采用聚集clustered的方式，因此每个表的数据都是按照主键的顺序进行存放，如果没有显示的指定主键，InnoDB默认会为每一行生产一个6字节的ROWID并以此作为主键；每个InnoDB存储引擎的在磁盘上存储为两个文件：
.frm文件（表结构的数据文件）
.ibd文件（用来存储数据和索引文件）

对于InnoDB表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM 表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引的前面几列进行排序后递增的。

`为什么建议InnoDB表必须建主键`

首先，我们知道InnoDB采用B+树作为存储结构，那么必然需要一个列作为key，什么是key？

一个B+树的节点可以存储key、地址、行数据(仅叶子节点)，key 就是不重复的值且可以比较(确保树进行分裂时，可以确定是左孩子还是右孩子)。

我们知道主键的特点就是主键的值不可重复，也不可为空，正好符合B+树key的要求

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引（确切说会选择一个唯一非空的列作为主键）代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。

如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可

聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引，这个要看实际情况

`为什么主键通常建议使用自增id`

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。聚簇索引的顺序和磁盘中数据的存储顺序是一致的，如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

补充：因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）

不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？（另一个参考答案）

为什么要设置主键？
毫无疑问的是，当对Mysql中的表设置了InnoDB存储引擎的时候，那么就需要对一列数据建立索引，方便快速查找，如果不设置主键的，Mysql会在表中逐列对比寻找无重复项的列作为逐渐，如果没有查找到，那么Mysql会在表中添加默认列作为主键。如果不设置主键列，那么Mysql会消耗大量的资源去寻找索引列或者是创建新的索引列，如果设置了索引列那么将极大的降低Mysql的资源消耗。
为什么要采用整型的数据？
Mysql底层对数据查找的时候，会涉及到大量的对比操作，如果采用UUID的字符串模式，那么Mysql在对比过程中会消耗资源用于字符串中每个字符的对比，这样做将极大的消耗系统的性能。
为什么要采用自增主键？
了解 B+ 树的节点存储机制的话，就一定会知道当某个节点的索引数量达到阈值的时候会分裂节点重新调整树的平衡。这样做对系统性能的消耗是相当不友好，在 B+ 树的叶子节点中，通过双向指针维护了一根将索引按照升序排列的双向链表，采用自增主键，新的索引将会被添加在链表的尾部，避免了B+ 树的分裂所带来的系统消耗。