MySQL底层索引与相关知识

待我称王封你为后i 2021-09-27 05:44 339阅读 0赞

**定义**  
MySQL官方对于索引的定义为：索引是帮助MySQL高效获取数据的数据结构。即可以理解为：*索引是数据结构*。  
【索引是一个文件】

MySQL常见的引擎包括：  
Memory引擎：

> 该存储引擎通过在内存中创建临时表来存储数据。每个基于该存储引擎的表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。

ISAM：

> ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不 支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实 时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

MYISAM引擎：

> MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。

Innodb引擎：

> InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。

索引：  
Memory引擎：采用Hash，也可以使用B+树。  
MYISAM引擎：采用B+树  
Innodb引擎：采用B+树

MYISAM引擎中B+树的结构：  
包括三个文件：.frm(表结构文件) .MYD（表数据文件） .MYI（表索引文件）

Innodb引擎中B+树的结构：  
包括连个文件：..frm(表结构文件) ,IDB文件：存储表数据文件。整个表以主键索引存储，无需单独的文件存储索引。  
（如果没有定义主键，则自动选取最佳列作为索引创建文件。）

下面以主键为自增长的id作为主键为例：  
假设存在表Student,id为主键  
id | name | age  
1 | 张三1 | 20  
2 | 张三2 | 19  
3 | 张三3 | 21  
4 | 张三4 | 20  
5 | 张三5 | 22  
6 | 张三6 | 20

MYISAM引擎的.MYI文件：  
![这里写图片描述][70]

Innodb引擎中的.IDB文件：  
![这里写图片描述][70 1]  
若自定义索引，则最后叶子节点存放的是其对应的主键。

二者的区别是MYISAM中的叶子节点对应的是记录的地址。而.IDB文件中存放的是记录。

聚集索引 VS 非聚集索引  
聚集索引 ：  
索引的逻辑顺序与相应行的物理位置一致。  
适用范围：范围搜索、排序  
非聚集索引：  
索引的逻辑顺序与相应行的物理位置不一致。

区分：  
聚集索引的叶子结点就是最终的数据结点，非聚集索引的叶子结点仍然是索引结点，但它有一个指向最终数据的指针。  
如上述.MYI文件是非聚集索引。Innodb引擎中以主键索引存储的文件是聚集索引。  
一个表中只能拥有一个聚集索引。  
一个表可以有多个非聚集索引。

MYISAM VS Innodb  
1.MYISAM 是非事务安全的，而Innodb 是事务安全型的。  
2.MYISAM 锁的粒度是表级，Innodb 支持行级锁定  
3.MYISAM支持全文类型索引 ，Innodb 不支持全文类型索引  
4.MYISAM相对简单，所以效率优于 Innodb ，小型应用可以考虑使用Innodb  
5.MYISAM是保存成文件的形式，在跨平台的数据转移中使用MYISAM存储会省去不少麻烦。  
6.Innodb 表比MYISAM更安全，可以保证在数据不会丢失的情况下，切换非事务表到事务表。

应用场景：  
1.MYISAM管理非事务表，可以提高存储和检索，以及全文搜索能力。如果应用中需要执行大量的查询，那么MYISAM是好的选择。  
2.Innodb 用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量插入或者更新，则使用Innodb ，这样可以提高多用户并发操作的性能。

ACID：  
**Atomicity原子性**  
原子性任务是一个独立的操作单元，是一种要么全部执行，要么全部未执行的原子单位性的操作。  
**Consistency一致性**  
一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。如银行转账。  
**Isolation隔离性**  
多个事务并发执行的话，结果应该与多个事务串行执行效果是一样的。  
**Durability持久性**  
事务操作完成后，对数据库的影响是持久的，即使数据库因故障受到破坏，数据库也能够恢复。

事务隔离级别：  
1.读未提交（最低级别）  
允许其他事务看到未提交的数据。– 脏读  
2.读已提交  
被读取的事务可以被其他事务修改。–不可重复读  
3.可重复读  
所有SELECT获取的数据不能被更改。–可重复读，但是不能控制增加数据。–幻读  
4.可串行化  
所有事务一个接一个执行，可以避免幻读。

![这里写图片描述][70 2]

脏读、幻读和不可重复读

![这里写图片描述][70 3]

[70]: /images/20210923/94475da781bb41bfa396da65288c0b4e.png
[70 1]: /images/20210923/00548f32efea41eebc2dde04d8809091.png
[70 2]: /images/20210923/21fc6adfdc2948a799be1debed0cbd48.png
[70 3]: /images/20210923/e772aacf28f143c3a3b82d23f7e0e862.png