MySQL主要的两种存储引擎：MyISAM、InnoDB比较

水深无声 2022-10-22 09:12 18阅读 0赞

## 索引区别 ##

*  MyISAM不管是主键索引，唯一键索引，或者普通索引，都是属于**稀疏索引**。
 *  InnoDB必须有且仅有一个**密集索引**，这个密集索引的选取规则如下：
    
    1.  若一个主键被定义，该主键则作为密集索引；
    2.  若没有主键被定义，将表的第一个唯一非空索引则作为密集索引；
    3.  若不满足以上条件，InnoDB内部会生成一个隐藏主键（密集索引），这个隐藏的主键是一个6字节的列，该列的值会随着数据的插入而自增，也就是说，我们的InnoDB必须有一个主键，而该主键就必须作为唯一的密集索引而存在。

### InnoDB必须有一个主键 ###

非主键索引（稀疏索引）的叶子节点并不存储行数据的物理地址，而是存储该行的主键值，所以非主键索引包含了两次查找，一次是查找次级索引，然后再查找主键

### 索引过程 ###

**InnoDB：使用的是密集索引**，将主键组织到一颗B+树中，而行数据就存储在叶子节点上，因为InnoDB的主键索引和对应的数据是保存在同一个文件当中的。所以检索的时候，在加载叶子节点的主键进入内存的同时加载了对应的数据。

*  主键查询：若使用"where id = 1"这样的条件查询主键，就可以按照B+树的检索算法查找到对应的叶子节和它对应的行数据。
 *  条件查询：若对稀疏索引进行条件筛选，则需要经历两个步骤
    
    1.  在稀疏索引的B+树中检索name=“Alex”，找出该数据对应的主键
    2.  使用主键"where id = 1"在密集索引的\*\*B+\*\*树中再执行一次检索操作，最终再到达叶子节点，获取对应的行数据。

**MyISAM：使用的是稀疏索引**，稀疏索引的两颗\*\*B+**树，节点的结构是一致的，只是存储的内容不一样而已。主键索引B+树的节点存储了主键，辅助键索引**B+**树的节点存储的是辅助键，表数据是单独存储在独立的地方。这两颗**B+\*\*树的叶子节点都使用一个地址指向真正的数据。

对于表数据来说，这两个键没有任何差别，由于索引树是独立。**MyISAM**相比于**InnoDB**，通过辅助键检索，无需访问主键的索引树。

## MyISAM与InnoDB关于锁方面的区别 ##

*  **MyISAM**默认**表级锁**，**不支持**行级锁
    
     *  查询时，会给整个表上读锁（共享锁）
     *  增删改时，会给整个表上写锁（派它锁）。
        
        存在读锁时，另一个session做读操作 **不会** 被阻塞
        
        存在读锁时，另一个session做写操作 **会** 被阻塞
        
        存在写锁时，另一个session做读操作 **会** 被阻塞
        
        存在写锁时，另一个session做写操作 **会** 被阻塞
 *  **InnoDB**默认**行级锁**，**支持**表级锁
    
     *  sql没有用到索引时，用的是表级锁。
     *  sql用到索引时，用到行级锁和gap锁

> 标级锁与索引无关
> 
> 行级锁与索引有关：sql用到了索引，涉及到的行都会被上共享锁或者排它锁

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>MyISAM</th> 
   <th>InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>场景</td> 
   <td>频繁执行全表count语句：用一个变量保存整个表的行数对<br>数据进行增删改频率不高时，查询非常频繁<br>无事务</td> 
   <td>增删改查都非常频繁<br>可靠性比较高，要求支持事务</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>RR级别：通过引入<strong>next-key</strong>锁来避免幻读问题<br><strong>next-key</strong>由<strong>Record lock</strong>和<strong>Gap lock</strong>组成<br><strong>Gap lock</strong>会用在非唯一索引或不走索引的当前读，以及仅命中部分条件的部分结果集，并且是用到主键索引和唯一索引的当前读中</td> 
  </tr> 
 </tbody> 
</table>

## RC已提交读 RR可重复读 级别下的InnoDB的非阻塞读 ##

*  数据行里的DB\_TRX\_ID、DB\_ROLL\_PTR、DB\_ROW\_ID字段
    
     *  DB\_TRX\_ID：标识最后一次对本行做修改的事务标识符
     *  DB\_ROLL\_PTR：回滚指针
     *  DB\_ROW\_ID：行号
 *  undo日志：对数据进行变更操作时就会产生undo记录，存储老版数据
 *  read view：可见性判断

## 对主键索引或者唯一索引会用Gap锁吗 ##

*  如果where条件全部命中，则不会用Gap锁，只会加记录锁Record lock
 *  如果where条件部分命中或者全都不命中，则会加Gap锁