Java面试必会之-InnoDB

InnoDB两大类索引

  • 聚集索引(clustered index) 也较聚簇索引
  • 普通索引(secondary index)

简单来说，通常主键为聚集索引，其他属性为普通索引

InnoDB规定每个表都必须有聚集索引且只有一个：

  1. 如果定义了主键，那么主键就是聚集索引
  2. 如果没有定义，第一个非空 unique列就是聚集索引
  3. 如果再没有，会自动生成一个隐藏的row-id作为聚集索引

结构

索引的结构为B+树

  1. 聚集索引：非叶子节点存储key，叶子节点存储行记录
  2. 普通索引：非叶子节点存储key，叶子节点存储value值 + 主键值 （不是存储行记录头指针！MyISAM的索引叶子节点存储记录指针。）
  3. 索引包含null：放在B+树的最左边

image-20200916132258779

回表查询：

普通索引无法直接定位行记录，所以对于普通索引，需要二次查找，先通过普通索引找到聚集索引，再通过聚集索引找到行记录。

先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

索引覆盖

在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

简单来讲，只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

也就是说，根据索引查询时能一次性命中要查询的数据就是索引覆盖，无需会表再查一次，提高效率，此时explain后出现Extra：Using index

索引覆盖的实现

select id,name from user where name='xxx'; （Extra：Using index。）

能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。

select id,name,sex from user where name='shenjian'; （Extra：Using index condition）

能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。

此时如果使用联合索引 {index(name, sex)} ，就能避免回表查询： Extra：Using index。

create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name, sex)
)engine=innodb;

limit、offset

//从表的第2条开始，读10条数据
select * from C limit 10 offset 1
//等价于
select * from C limit 1,10

如果要从第100000… 开始读数据，那么offset会特别大，必然慢SQL，可以通过以下方法解决：

  • 延迟加载

  • 使用联合索引 index(sex, score)

    //方式一：
select <col> from A where sex = 'M' order by score limit 100000,10

  • 使用覆盖索引：先从覆盖索引中获取100010个id，在丢掉前100000条id，保留最后10个；

    //方式二：
select <col> from A inner join(select id from A where a.sex = 'M' order by score limit 100000,10) as a using(id)

索引覆盖优化SQL

  • MySQL5.6之后对覆盖索引做了进一步的优化，支持索引下推的功能，把覆盖索引所覆盖的字段进一步筛选，尽量减少回表的次数，此时为 （Extra：Using index condition）。

  • 如果使用的是机械硬盘的话，要进一步进行优化，随机硬盘很怕随机读写，有一个磁盘寻址的开销，可以打开Multi range read，可以在回表之前把ID读到buffer里，进行排序，把原来的一个随机操作变成一个顺序操作。

  • 覆盖索引可以避免比如在排序时用到的一些临时文件，可以利用最左原则和覆盖索引配合，减少索引的维护

  • 对于普通索引，如果是写多读少的服务，并且服务的唯一性要求没那么高，或者业务代码可以保证唯一性时，可以用普通索引。因为普通索引可以用到Change Buffer，可以把一些写操作缓存下来，在读的时候进行merge的操作，这样可以提高写入的速度和内存的命中率

  • 如果索引走不上，可以考虑：

    • 是否是SQL有问题；
    • 或者对索引字段进行一些函数操作，在连接查询时两个表的编码不一样；
    • 或者字段类型不一样，比如String赋给它一个ID，在MySQL里默认会把String转为ID，用到了隐式的cast函数转换
    • 可能是索引统计信息有问题，可以Analyze table重新统计索引信息，因为索引信息不是一个准确值，是随机采样的过程
    • 如果业务表增加太多，内存的空洞比较多，都有可能造成内存索引选择的问题

场景1：全表count查询优化

添加索引：

alter table user add key(name);

然后进行查询：select count(name) from user; 此时就通过索引覆盖提高查询效率

场景2：列查询回表优化

select id,name,sex ... where name='shenjian';

将单列索引(name)升级为联合索引 index(name, sex)，即可避免回表。

场景3：分页查询

select id,name,sex ... order by name limit 500,100;

将单列索引(name)升级为联合索引 index(name, sex)，也可以避免回表

MVCC机制

MVCC: Multiversion Concurrency Control,翻译为多版本并发控制，其目标就是为了提高数据库在高并发场景下的性能。

MVCC最大的优势：读不加锁，读写不冲突。在读多写少的场景下极大的增加了系统的并发性能

MySQL基本机构

image-20200916132304206

MYISAM并不支持事务，所以InnoDB实现了MVCC的事务并发处理机制

SQL事务隔离级别

read uncommitted 读未提交： 一个事务还没提交时，它做的变更就能被别的事务看到。

read committed 读已提交：一个事务提交之后，它做的变更才会被其他事务看到。

repeatable read 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

serializable 串行化 ：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。

而MVCC只在RC读提交和RR可重复度的隔离级别下才出发该机制
InnoDB相比与MYISAM的提升就是对于行级锁的支持和对事务的支持，而应对高并发事务, MVCC 比单纯的加行锁更有效, 开销更小。
在不同的隔离级别下，数据库通过 MVCC 和隔离级别，让事务之间并行操作遵循了某种规则，来保证单个事务内前后数据的一致性。

并发事务可能出现的问题

Lost Update更新丢失: 多个事务对同一行数据进行读取初值更新时，由于每个事务对其他事务都未感知，会造成最后的更新覆盖了其他事务所做的更新。

dirty read脏读: 事务一个正在对一条记录进行修改，在完成并提交前事务二也来读取该条记录，事务二读取了事务一修改但未提交的数据，如果事务一回滚，那么事务二读取到的数据就成了“脏”数据。

non-repeatable read不可重复读: 个事务在读取某些数据后的某个时间再次读取之前读取过的数据，发现读出的数据已经发生了改变或者删除，这种现象称为“不可重复读”

phantom read幻读: 个事务按相同的查询条件重新读取以前检索过的数据，发现其他事务插入了满足查询条件的新数据，这种现象称为“幻读”

MVCC原理

在InnoDB中MVCC的实现通过两个重要的字段进行连接：DB_TRX_ID和DB_ROLL_PT，在多个事务并行操作某行数据的情况下，不同事务对该行数据的UPDATE会产生多个版本，数据库通过DB_TRX_ID来标记版本，然后用DB_ROLL_PT回滚指针将这些版本以先后顺序连接成一条 Undo Log 链。

  • 表中每行记录

image-20200916132308648

DB_TRX_ID: 事务id，6byte，每处理一个事务，值自动加一。
DB_ROLL_PT: 回滚指针，7byte，指向当前记录的ROLLBACK SEGMENT 的undolog记录，通过这个指针获得之前版本的数据。该行记录上所有旧版本在 undolog 中都通过链表的形式组织。

还有一个DB_ROW_ID(隐含id,6byte，由innodb自动产生)，InnoDB下聚簇索引B+Tree的构造规则:

如果声明了主键，InnoDB以用户指定的主键构建B+Tree，如果未声明主键，InnoDB 会自动生成一个隐藏主键，说的就是DB_ROW_ID。另外，每条记录的头信息（record header）里都有一个专门的bit（deleted_flag）来表示当前记录是否已经被删除

Undo log链的构建

  1. 事务A对DB_ROW_ID=1这一行加排它锁

  2. 将修改行原本的值拷贝到Undo log中

  3. 修改目标值产生一个新版本，将DB_TRX_ID设为当前事务ID即100，将DB_ROLL_PT指向拷贝到Undo log中的旧版本记录

  4. 记录redo log， binlog

image-20200916132313054

INSERT: 产生一条新的记录，该记录的DB_TRX_ID为当前事务ID
DELETE: 特殊的UPDATE，在DB_TRX_ID上记录下当前事务的ID，同时将delete_flag设为true，在执行commit时才进行删除操作

MVCC的规则大概就是以上所述，那么它是如何实现高并发下RC和RR的隔离性呢，这就是在MVCC机制下基于生成的Undo log链和一致性视图ReadView来实现的。

一致性视图的生成 ReadView

要实现read committed在另一个事务提交之后其他事务可见和repeatable read在一个事务中SELECT操作一致，就是依靠ReadView，对于read uncommitted，直接读取最新值即可，而serializable采用加锁的策略通过牺牲并发能力而保证数据安全，因此只有RC和RR这两个级别需要在MVCC机制下通过ReadView来实现。

在read committed级别下，readview会在事务中的每一个SELECT语句查询发送前生成（也可以在声明事务时显式声明START TRANSACTION WITH CONSISTENT SNAPSHOT），因此每次SELECT都可以获取到当前已提交事务和自己修改的最新版本。而在repeatable read级别下，每个事务只会在第一个SELECT语句查询发送前或显式声明处生成，其他查询操作都会基于这个ReadView，这样就保证了一个事务中的多次查询结果都是相同的，因为他们都是基于同一个ReadView下进行MVCC机制的查询操作。

InnoDB为每一个事务构造了一个数组m_ids用于保存一致性视图生成瞬间当前所有活跃事务(开始但未提交事务)的ID，将数组中事务ID最小值记为低水位m_up_limit_id，当前系统中已创建事务ID最大值+1记为高水位m_low_limit_id，构成如图所示:

image-20200916132318039

mvcc-read_view_arr.png

一致性视图下查询操作的流程如下:

  1. 当查询发生时根据以上条件生成ReadView，该查询操作遍历Undo log链，根据当前被访问版本(可以理解为Undo log链中每一个记录即一个版本，遍历都是从最新版本向老版本遍历)的DB_TRX_ID，如果DB_TRX_ID小于m_up_limit_id,则该版本在ReadView生成前就已经完成提交，该版本可以被当前事务访问。DB_TRX_ID在绿色范围内的可以被访问
  2. 若被访问版本的DB_TRX_ID大于m_up_limit_id，说明该版本在ReadView生成之后才生成，因此该版本不能被访问，根据当前版本指向上一版本的指针DB_ROLL_PT访问上一个版本，继续判断。DB_TRX_ID在蓝色范围内的都不允许被访问
  3. 若被访问版本的DB_TRX_ID在[m_up_limit_id, m_low_limit_id)区间内，则判断DB_TRX_ID是否等于当前事务ID，等于则证明是当前事务做的修改，可以被访问，否则不可被访问, 继续向上寻找。只有DB_TRX_ID等于当前事务ID才允许访问橙色范围内的版本
  4. 最后，还要确保满足以上要求的可访问版本的数据的delete_flag不为true，否则查询到的就会是删除的数据。

所以以上总结就是只有当前事务修改的未commit版本和所有已提交事务版本允许被访问。我想现在看文章的你应该是明白了(主要是说我自己)。

一致性读和当前读

前面说的都是查询相关，那么涉及到多个事务的查询同时还有更新操作时，MVCC机制如何保证在实现事务隔离级别的同时进行正确的数据更新操作，保证事务的正确性呢，我们可以看一个案例:

DROP TABLE IF EXISTS `mvccs`;
CREATE TABLE `mvccs`( `field` INT)ENGINE=InnoDB;
INSERT INTO `mvccs` VALUES(1); -- 插入一条数据

image-20200916132324105

mvcc-transaction_consts_view.png

假设在所有事务开始前当前有一个活跃事务10，且这三个事务期间没有其他并发事务:

  1. 在操作1开始SELECT语句时，需要创建一致性视图，此时当前事务的一致性视图为[10, 100, 200，301), 事务100开始查询Undo log链，第一个查询到的版本为为事务200的操作4的更新操作， DB_TRX_ID在m_ids数组但并不等于当前事务ID， 不可被访问；
  2. 向上查询下一个即事务300在操作6时生成的版本，小于高水位m_up_limit_id，且不在m_ids中，处于已提交状态，因此可被访问；
  3. 综上在RR和RC下得到操作1查询的结果都是2

那么操作5查询到的field的值是多少呢？

在RR下，我们可以明确操作2和操作3查询field的值都是1，在RC下操作2为1，操作3的值为2，那么操作5的值呢？

答案在RR和RC下都是是3，我一开始以为RR下是2，因为这里如果按照一致性读的规则，事务300在操作2时都未提交，对于事务200来说应该时不可见状态，你看我说的是不是好像很有道理的样子？

上面的问题在于UPDATE操作都是读取当前读(current read)数据进行更新的，而不是一致性视图ReadView，因为如果读取的是ReadView，那么事务300的操作会丢失。当前读会读取记录中的最新数据，从而解决以上情形下的并发更新丢失问题。

Explain分析索引

Explain分析出来的索引不一定是最优的，可能会选错，因为索引时可能会涉及到回表操作，或者排序操作，可能导致索引选错

索引查询速度慢的情况

  • 首先可以考虑用force index，强制走一个索引，但只能作为一个应急预案，不推荐经常使用。因为一旦迁移到别的数据库里就不支持了，还需要做代码的重新发布

  • 可以用覆盖索引 + 最左前缀原则，考虑能否把原来的索引删掉重新分析选择索引

posted @ 2020-09-16 13:24  JavaJayV  阅读(365)  评论(0)    收藏  举报