Mysql探索(三)之InnoDB的索引与事务

Innodb的索引是核心成员，掌握了索引才能知道mysql在执行sql后，mysql是如何检索数据的，也就知道了这条sql的优劣之处，对于我们调优sql有很大的帮助。
事务的存在是mysql保证数据原子一致性的重要手段，也是认知mysql必须要懂的知识点。

索引

在mysql里，索引是灵魂，用的好，mysql就是跑车，用的不好，mysql就是三轮车。
在我看来，索引就分为两种：主键索引和非主键索引，其他的扯一大堆说来说去都是这两货。说归说，概念我还是得提一下的。

聚簇(聚集)索引：一听这名字就知道有密集恐惧症的感觉，都能猜到这是主键索引，因为主键索引的叶子节点存储了所有的数据。

普通索引：二级索引，数据结构和主键索引一样，都是B+数，但是叶子节点不存储数据。

唯一索引：看名字都知道，索引值是唯一的，和主键索引不同的是，索引值允许为null。

覆盖索引：只是一种概念的东西，是指我们查询的字段存储在叶子节点上，不需要回表操作，这些索引都叫覆盖索引，更多情况是出现在联合索引。

前缀索引：像blob，text这种大文本类型的字段，有可能需要将它们的前缀作为索引（15-20个字符最佳），但是前缀索引不能用于order by和group by。

联合索引：在几个字段上建立一个索引，最好选择性高的排在前面，该索引使用时，需要符合最左前缀原则。

哈希索引：就是用hash值来做索引的，查找一条记录很快，但是对于范围，排序，模糊查询就不友好，性能不行。

前面提到过一个选择性，其实就是指列值的不重复率。
不熟悉索引的人可能会对上边提到一些信息不太懂，其实这个不重要，说不定看完下边的就懂了，张无忌练太极，忘了它吧。

主键索引

主键索引是mysql每个表里必须存在的一个索引，如果你不指定声明一个列作为主键，则Innodb会使用第一个不包含null值的唯一索引作为主键，如果没有唯一索引，则Innodb会建一个隐式索引rowid作为主键索引，占6个字节。
主键索引的列必须唯一且不为null值。

索引就像书上的目录一样，是为了方便快速的检索数据用的，当表创建时，Innodb就会在创建索引，使用的检索数据结构是B+树。它是通过B-tree(B树)变种过来的，B树最大的特点就是所有的节点都存储了数据data(表的行数据)，而且叶子节点间不存储指针，即非双向链表结构。

那么什么是B+树呢？有以下特性：

• 叶子节点都处于树的同一层
• 只有叶子节点存储了数据data，其他的都只是存储了主键
• 叶子节点间组成了双向链表
  
  上图是个简单的B+树图，Innodb所有的数据都是存储在页中的（Innodb中的基本单位，不懂什么是页的可以看这篇介绍Innodb的存储结构），看到根节点页中只是存储了id，在第二层节点中的页，即我们的叶子节点中则存储了整条记录数据。叶子节点间用双向链表连接起来，便于范围查找，这个双向指针就存储在页结构的File Hearder里。

依照上图来模拟下检索过程，这样会有更加清晰的认知，假设我们要检索13这个数据，步骤如下：

1. 先去根节点页中查找，因为Innodb会预先将根节点页从磁盘中加载到内存中，所以在Innodb开始检索的时候可以省略这一步操作。
2. 和页中的第一条行记录对比，13>5，根据行记录中的头信息指针找到下一条行记录9，对比发现还是小于13，继续查找下一条记录，发现13<18，则证明13是在9和18行记录的范围。
3. 根据指针确认页地址（12所在页），通过IO线程将该页从磁盘中加载到内存。
4. 然后重来一遍步骤2，最后定位到13这条行记录，直接返回这条完整数据。

如果是范围查找，比如13-22，可以根据链表定位到下一个页，也要将该从磁盘中加载到页中。

q1：为什么mysql的存储是千万级别的？为什么不用B树？
我们来算个数就知道了，因为页是16kb，假设一行记录占用1kb（一般不用这么多，为了方便计算），一般我们的表索引都要控制在3层的深度，这是为了避免过度的IO消耗导致性能低下，索引一般是int型或者long型，最大占8个字节，则一个页可以存储16*1024/8 = 2048，假设2000个，方便计算。叶子节点则存储16条记录。则

B+树：2000*2000*16=64000000
B树：16*16*16 = 4096

可以看到B+树可以检索6千多万条数据，由此可以看出B+树是千万级别的。
而B树因为每个节点都会存储数据，所以能够存储的节点数就少，而且叶子节点间是没有双向链表的，所以范围查找的时候不方便。

q2: 主键索引为啥是自增整型呢？
首先，我们每insert一条语句到表里，是不是得更新一下B+树结构，如果是自增的，则Innodb每次只需要在B+树的最后面添加节点就好了，并不需要怎么调整平衡B+树。
自增表示id是有序的，则添加一条数据，可以直接把数据刷到最后一条记录的下一个磁盘地址，也就保证了顺序IO的写入（磁盘的随机IO是很消耗系统的查找性能的）。

非主键索引

非主键索引有很多花名啦，比如二级索引，普通索引等，所以不要太纠结这些名称。

所有的我们声明的索引基本都是B+树结构，Innodb内部定义的哈希索引不算，所以我们的二级索引的数据结构一样是B+树，但是会有一点点的不一样，就是叶子节点不再存储所有数据，而是只存储了索引数据+主键id，如下图所示（假设索引字段是name）：

可以看到叶子结点中只有name和id的数据，也就是说这样的索引只能满足select name,id from xxx; 这样的sql，再多一个字段就不能直接返回数据了，比如下方的sql

select name, id, age from table_name;

这时候就需要回表操作了，相信大家对这个词也不陌生，意思就是说Innodb在二级索引中找不到想要的数据，就需要根据二级索引中的主键id到主键索引中查找需要的数据，如下图：

当执行以下sql语句时就会发生上边的回表操作：

select name, id, age from xxx where name = 'ti';

到这里应该就能明白，为什么减少回表能够达到优化sql的目的，这样能减少一次主键索引的检索，换一种说法就是说在我们写sql的时候，尽量让select的字段命中索引，这样就能避免回表了。

所有的sql行为都是从Innodb的索引出发的，所以尽量让自己的业务sql落到索引上，没在索引上的就优化sql。

联合索引

联合索引也是二级索引，就是比单字段索引复杂一点，所以在这里提一下。

假设建一个这样的联合索引：

create index index_name_age_sex on talbe_name (name, age, sex);

那么该索引的B+树是怎样的呢？以name为优先级，先排好name，再排age，最后排sex，如下：

从上图可以看出：真正有序的是name（第一个字段），而age和sex字段都是局部有序（前面字段有序的前提下）。而这就是为什么要保证联合索引的前缀原则，索引能有效的前提是有序，保证不了有序性就没法利用索引。比如下方sql：

select name from table_name where age = 32;

从图中可以看出name=cc和name=ti的记录都有age=32的条件，但是却没法通过B+树的特性来查找到该两条记录，除非是全部扫描B+树（全表扫描）。

从B+树的结构图中我们就能推断出什么样的条件能够命中索引，也就知道了怎么去优化此类sql，下边给几个案例：

-- 不命中索引，如果数据量太少了，Innodb也会直接使用索引扫描的，可以自己explain看下执行计划
select name from table_name where age = 36 and sex = '女';
select name from table_name where name = 'cc' or age = 36;
-- 命中索引
select name from table_name where name = 'cc';
-- Innodb的优化器会调整where的顺序
select name from table_name where age = '36' and name = 'cc';

下面的sql就说不准了，这个得看Innodb的执行计划分析情况来说：
如果命中的数据太多，Innodb会选择全表扫描的方式，如果只有一点数据的话，会先根据索引查找name，然后再扫描age和sex条件。

select name, age from talbe_name where name > 'fb' and age = '23';

一般优化器选择全表扫描的条件是：执行计划预判出要扫描30%以上的数据，就会直接使用全表扫描。

事务

这篇主要讲的是事务的知识点，不会开篇幅去讲述事务它本质，事务本质实现是由MVCC+redo.log+undo.log来控制的，感兴趣的可以自行去了解。

不知道大家对事务是怎么理解的，以我的理解就是对update，delete，insert语句的把控，以保证事务的四大特性。

ACID特性

A(atomicity)：原子性，保证操作的原子性，要么全部成功，要么全部失败，类似原子，保证操作的完整性，不能被中间插入其他操作。
C(consistent)：一致性，保证数据的一致性，不能这个事务读到的是数据a，另一个数据读到的是数据b。
I(isolation)：隔离性，就是事务与事务一直互不干扰。
D(durable)：持久性，就是数据能够永久的保存到磁盘中。

为了保证这些特性，Innodb做了很多处理，针对隔离性，Innodb给出了4中隔离级别，开启的事务隔离级别越低，则对我们的并发开发影响就越低，但是安全性就会越低。

事务的隔离性

怎么理解这事务的隔离性呢？其实就是一种保证事务间数据不错乱的一种手段，类似于多线程下我们要保证数据共享安全问题，隔离级别就像CAS机制或者synchronize等手段，说白了就是保证数据安全性，根据隔离性安全级别不一样。

隔离级别有4种：

• read uncommitted：读未提交
• read committed：读已提交
• repeatable read：可重复读
• serializable：串行化

怎么理解这几个隔离级别意思呢？

read uncommitted：读未提交

首先得知道一点，客户端提交的数据先到内存中，然后才刷到磁盘上，所谓的未提交就是指数据还在内存中，并未持久化到磁盘上，所以从字面上就很好理解什么是读未提交了，就是读到了内存中的数据。
吃个栗子就明白了：
先开启两个窗口，把事务的隔离级别修改为read uncommitted：

mysql> set session transaction isolation level read uncommitted;

然后两边都开启事务：

mysql> begin;

窗口1发出一条查询语句：

上边是原始数据，然后在另一个窗口执行一条update语句：

这时候再去窗口1中执行同样的查询语句，能够查到在窗口2中执行的update语句的修改。

如果这时候窗口2执行了rollback操作，则修改的数据就不存在了，而窗口1的sql如果是在java程序中执行的话，则会保留那部分修改，然后拿修改的值（脏数据）去执行程序，这很明显是有bug的，这也就是我们常说的脏读。

read committed：读已提交

看名字也很好理解，就是读到了已经提交的数据，也就是持久化到磁盘的数据，可能大家会有些疑惑，读到已经提交了的数据不是应该正常的吗，那我们来看下这种情况：

我select一条记录，假设获取id=1的记录，我对它该条记录操作，但是另一个人修改了这条记录，我读到的记录就变动了，那我之前因为这条记录所跑动的程序就浪费了，假设有很多条线程都在修改这条记录，那我还要不要用了，搞我心态嘛这不。

所以应情景来考虑，我就希望读到的永远是我开始时读到的数据，直到事务结束，如果是写操作才需要读取最新的数据。也就是说这个隔离级别会读到不一样的数据，也就是有不可重复读问题。

来演示下这种情况：
还是得先改下隔离级别：

mysql> set session transaction isolation level read committed;

开启下事务：

窗口1查询下数据：

在窗口2里修改一条记录并提交：

再回到窗口1的事务中查询一下：

看到数据确实变了，也就是说有不可重复读(一个事务里读到了不一样的数据)现象。

repeatable read：可重复读

这个隔离级别更高一点，就是为了防止上边的不可重复读问题的出现，那要怎么防止呢？是不是要保证同一个事务里读到的数据都是不变的呢。Innodb是使用MVCC机制来保证数据不变的，MVCC类似于快照，定住了某一时刻的数据，事务查到的都是那一时刻的数据，怎么查都不会变，如果执行的是update，delete，insert操作，则能够更新到最新数据。
该级别下能预防update，delete操作，但是预防不了insert语句，insert语句会发生幻读问题。
我们来看吃个栗子就明白了：
先查看下当前事务级别：

-- 我当前mysql版本是5.8的
select @@transaction_isolation;
-- 以前的版本可以试下下边命令
select @@tx_isolation;

开启下事务，查询下窗口1的当前数据：

开另一个窗口2，insert一条语句：

在窗口1中可以看到插入进来的数据。

所以会导致幻读的是insert语句，这个幻读问题是可以忍受的，Innodb的默认隔离级别就是可重复读。

serializable：串行化

Innodb是通过串行化的隔离级别来防止幻读的发生的，串行化，看名字都知道，是一个个的执行，这种安全级别最高，但是并发度就很堵了，性能不行，这种情况下很少有公司会使用。

串行化的实现是通过锁的方式来实现的，Innodb里有读写锁，select语句时有读锁，也叫共享锁，有读锁的时候其他事务是可以读的，但是跟写锁是互斥的，只能等读锁释放了才能加写锁，写锁也叫互斥锁，就是有写锁的时候，任何事务都要等待写锁的释放。

演示就不弄了。下边给出关于隔离级别与引发问题的一个图表关系：

隔离级别 \ 问题	脏读	不可重复读	幻读
read uncommitted (读未提交)	yes	yes	yes
read committed (读已提交)	no	yes	yes
repeatable read (可重复读)	no	no	yes
serializable (串行化)	no	no	no

在这里简单提一下锁：

Innodb主要定义了表锁，行锁，间隙锁。
表锁不用说都明白，就是锁住整个表不被其他事务使用，比如select *这样的语句。
行锁一看也懂，就是锁住某行记录等，比如update set xxx where id = xxx, 这样的语句。
间隙锁需要说一下，它是锁住范围查询的那个间隙以及离这个范围最近的id，吃个栗子：
有个表，按顺序有个17,30,59,89的id，当我查询一个sql语句 where id > 20 and id < 60的时候，Innodb锁这的是[17,89)范围内的所有数据，包括不存在的id记录，比如18的id没有，但是也被锁住了。

还有一个很重要的点是：锁是针对索引的，也就是说锁在B+树上，如果update的不是索引条件，则会将行锁升级为表锁，这个是会影响性能的，所以写sql的时候尽量要用到索引。