索引区别

  • 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用到了索引，涉及到的行都会被上共享锁或者排它锁

MyISAM

InnoDB

场景

频繁执行全表count语句：用一个变量保存整个表的行数对

数据进行增删改频率不高时，查询非常频繁

无事务

增删改查都非常频繁

可靠性比较高，要求支持事务

RR级别：通过引入next-key锁来避免幻读问题

next-key由Record lock和Gap lock组成

Gap lock会用在非唯一索引或不走索引的当前读，以及仅命中部分条件的部分结果集，并且是用到主键索引和唯一索引的当前读中

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锁