MySQL——进阶教程【2】

一、索引

在正式介绍索引的查看、创建、删除之前，我们需要知道：索引是什么。博主的理解是：一个索引相当于书的一节目录。书的目录对于一本书的意义几何？当我们想找这本书的某部分内容时，我们很自然的想到去翻看目录，原因就是：这样才能以最快的速度定位到我们想要找的内容。同样，索引被创建出来的作用就是：加快查找速度。

我们初学者还接触不到真正海量的数据，我们对索引并没有一个真实的印象，但博主希望目录这个比喻能让大家印象深刻，日后工作中我们面对的数据动辄就是TB级别。所以，索引这种加快查找速度的作用是非常有必要且强大的！

1.查看

查看索引的方式非常简单，语法格式如下：

show index from 表名；

接下来，我们可以先看看索引是长什么样子的，大家可以先在编译器中运行一下SQL语句。注意：因为student表做了foreign key的约束，这就要求我们要优先运行teachers表，否则studnet表不能够建立起去外键约束。

drop table if exists student;
create table student (
	id int primary key auto_increment,
	name varchar(20) not null,
	class varchar(20) unique,
	teacher int not null,
	foreign key (teacher) references teachers(id)
);

# 先运行这里
drop table if exists teachers;
create table teachers (
	id int primary key auto_increment,
	name varchar(20) not null
);

查找索引之后，运行结果如下：

这里比较值得我们注意的是：Key_name、Column_name、Index_type这三个地方。

Column_name很明显，就是我们字段名；而Index_type的意思是：索引类型，我们可以观察到目前的索引都是BTREE这个数据结构：为什么是这个数据结构 ？以及这个数据结构是什么样的 ？甚至数据库索引中真正好用的数据结构是什么 ？博主都会在下文讲到，我们暂时不纠结。

Key_name就是索引名。当我们给表中的某个字段创建索引之后，MySQL就会生成一个索引文件，当我们需要查找某条数据的时候，就会先检查是否有这个索引，如果有的话可能会根据索引名在索引文件去寻找某条数据。但是Key_name下出现的三个索引名又是什么情况呢？我们明明没有创建索引，为什么会自己冒出三个索引呢？实际上：被primary key、unique、foreign key约束的字段，会被编译器自动创建为索引。

2.创建 与 删除

因为我们查询的字段不一定被primary key、unique、foreign key约束，那么对于需要快速查找的字段，为它们创建约束就很有必要了。

创建的语法如下：

create index 索引名 on 表名(字段名)；

比如说，name这个字段是经常会被使用来查询的，那么我们就可以为其添加一个索引。代码如下，运行截图如下：

create index student_name on student(name);

删除索引非常简单，语法格式如下：

drop index student_name on student;

关于索引有一下三点大家需要警惕的。首先，我们要：在设计数据库表的时候就需要设计好索引！当数据量大时我们再去修改索引是非常危险的，因为给一张记录了很多数据的表创建索引会占用大量磁盘IO，这段时间里数据库无法被正常使用，用户无法去数据库调用到需要的数据。

其次，关于索引我们还需要知道：创建索引其实也是需要付出代价的。代价主要有两条：

第一，索引也是消耗磁盘空间的，就像一本书因为添加了目录会多出相应的目录页；
第二，当我们给多个字段创建索引的时候，我们若是想修改一条数据，我们就要同时维护这个若干的索引。

最后，创建了索引之后，对数据进行查询、修改、删除的时候并不一定真的每一次都会走索引。利用索引 与 遍历整个表 去查找是两种路径，类似效果如下图。

因此我们知道：索引并不能达到通吃的效果。举个具体的例子：给一个学生表的年龄添加索引。我们知道一个学校的学生年龄不会相差太大，那么这样添加索引不会产生什么实际效果。这时候，编译器很有可能就会优化当前的执行方案，并不选择用索引。

因此，索引并不是越多越好，也不是越少越好，而是需要根据业务的需要进行创建。

下方补充有一些关于编译器的内部实现细节图，博主只是简单解释，各位读者可以选择性阅读。

3.索引的数据结构

讲到此处，我们可以填会上方Index_type挖的坑了。在我们查看数据库表索引的是时候发现：索引都是BTREE这个数据结构。紧接着，我们引出了三个问题： 为什么是这个数据结构 ？以及这个数据结构是什么样的 ？甚至数据库索引中真正好用的数据结构是什么 ？

我们首先需要思考反而不是前两个问题，而是最后一个数据库索引中真正好用的数据结构是什么？

不少读者会脱口而出HashTable。这是一个还算不错的选择，因为利用HashTable可以把查询的时间复杂度降到O(1)，在空间成本不那么重要的今天，这个复杂度确实属于秒天秒地级别的。但是，我们在操作数据库的时候一定是针对某一条数据吗？我们难道不会查找范围吗？比如说where id between 6 and 66;这样的操作，我们还用用上哈希表吗？显而易见，对于范围查询的情况，HashTable不够给力。

那么接力棒的下一个数据结构是什么呢？博主觉得会是一个树形结构。

了解过一些高阶数据结构的读者可能会想到二叉搜索树。这个选择比HashTable好不少，起码解决了范围查找的问题，而且还能时间复杂度也不是很高，一般情况下只有O(logN)，极少数单分支的情况下才会是O(N)。但是，这样不够好，因为当树的层数太高的时候，每次比较元素的时候都要去硬盘读取节点。

那么有没有办法可以减少去硬盘读取节点的次数呢?当然有，把二叉收搜索树升级一下，变成N叉搜索树。虽然这样并不会减少比较次数，但是访问硬盘的次数减少，搜索速度也自然加快了。而我们上面看到的BTREE就是N叉搜索树的一种体现，其数据结构示意图如下：

这里注意观察蓝圈与橙框之间的相对位置，以34为例，这个节点是小于

尽管BTREE以及比二叉搜索树更加适合做数据库索引，但是这样的数据结构还是不够。针对索引这个场景，N叉搜索树又引用了B+TREE的数据结构，一种在BTREE上改进的结果。那么B+TREE跟BTREE相比有什么不同呢？博主还是请各位读者自行看下方的示意图吧~

我们观察上方的示意图就会发现：所有在非叶子节点出现的值，都会以最大值的身份出现在自己的子叶上，而且所有的叶子节点会用类似于链表的方式连接起来。

那么上面两个特点有什么作用呢？跟BTREE比又有什么改进呢？主要有三点：

1.更加适合范围查询。
2.查询的速度是相同的。
3.非叶子节点只存储了索引列的值（比如id），叶子节点才存储了真实的数据。

针对1：假设我们需要取where id between(3,75);那么直接就可以在叶子节点中把区间的所有数据取到，更加适合用于范围查询。

针对2：由于BTRE中的值可能存储在非叶子节点，那么就有一些值查得快，有一些查得慢。这是肯定不行的，不患寡而患不均嘛~

针对3：这一手简直是绝妙。因为所有的id必定在叶子节点中，那么非叶子节点就不必存储真实的数据，这样就能大大减少空间的消耗。

那么上的画的B+TREE示意图并不完全正确，真正的示意图如下：

当然，我们要注意：并不是所有的数据库索引都是B+TREE，就像我们上方的截图所示，数据库索引用的可能是BTREE，博主最后介绍的这个B+TREE只是针对MySQL的InnoDB（一种主流的数据库存储引擎）这个数据库引擎。

二、事务

1.初识事务

事务指：一组操作中，组成这部操作的各个单元，要么都成功，要么都是失败。

为什么需要事务这种机制呢？这是防止一些特殊情况。比如说：A给B转了3000元，但是转的过程中因为网络错误 或者 数据库挂掉，B就无法接收到这3000元，事后B去查看自己的账户，也没有发现A转过来的3000元。这就造成了一个巨大矛盾。因此我们想到事务这种解决方案：要么全部成功，要么全部失败。

（1）事务开启

开启事务其实很简单，格式如下：

start transaction:
SQL语句
.......
SQL语句
commit;

用上方A与B转账的示例写个例子吧：

# 开启事务
start transaction:
update account set money=money-3000 where name = "A";
update account set money=money+3000 where name = "B";
commit;

（2）事务回滚（rollback）

我们上方谈到事务要么成功，要么失败。那么什么时候才算成功，什么时候才算失败呢？这里就涉及到InnoDB内部的实现细节了。由于篇幅有比较长，我们等等再讲。

当事务失败的时候，我们就需要用到rollback操作了，这个操作其实就类似于：计算机上的时光倒流。当事务开启之后，数据库会把事务之内的每个执行操作都记录下来，如果出现需要回滚的情况，就会进行逆操作，也就是说：原本是插入，现在就删除；原本是修改，那就改回去。具体代码如下：

# 事务回滚
start transaction:
update account set money=money-3000 where name = "A";
update account set money=money+3000 where name = "B";
rollback;

如上所说，数据是可以回滚的，那么我们之前的删库跑路是不是又可以干了？不是！因为回滚这个大杀器是很占用开销的，回滚本质上就是在执行逆操作，计算机只能保存一部分数据，并不能无限保存数据！假设数据库有10亿条数据，需要占用几百个G的空间，我们不可能花几个T的空间来记录这10亿条数据是怎么来的。

2.事务的四大特性

事务的四大特性分别是：原子性、一致性、持久性、隔离性。

上方四大特性中最关键的就是：原子性。其意思就是：把一个事务当成一个（不可分割的）原子，也就是把一个事务视为一个整体。（注：原子是可分割的，之所以叫原子性属于历史遗留问题。）

而一致性又是什么意思呢？这指的是：事务在执行前后的数据都必须是合法状态。比如说，A给B转了3000元之后，B不能多出6000元。必须是严丝合缝的：+3000-3000=0。因此，我们可以说：一致性是事务产生的结果，一致性依赖原子性实现

持久性

持久性就是保证我们在开启事务后执行的操作都是生效的，让数据能够被长久的保存下来。

这就带来了两个问题：

1.持久性是如何保证的数据的生效？
2.我们如何知道我们何时需要rollback？

我们先简单回答第二个问题，有可能需要rollback的场景有：网络错误 或者 数据库奔溃，以及操作系统崩溃（不过这个发生的概率比较小）。注意：不是发生了就需要rollback，而是可能需要。

要从原理的角度给大家解答上方的两个问题，我们就需要看看一张InnoDB内部实现细节图。接下来我们就结合着下方这张图逐步解释。

假设我们在事务内部进行了update操作，这时候内存就需要根据SQL语句去磁盘中寻找被操作的那条数据。但是，磁盘并不是只传一条数据，而是把一页的数据加载出来，MySQL的数据是以页为单位的。加载出来的页叫数据页。上方1的操作本质上就是：从磁盘中读取数据页到内存的buffer pool。这里我们顺便谈一下，把数据页放入到buffer pool中是一个相当高明的操作，因为后续的查询都会先从buffer pool中找，没有命中再去硬盘加载。这样可以减少硬盘 IO 开销，提升性能。

在buffer pool这个地方，内存根据SQL语句的指示去修改具体的内容，并且在修改的同时讲一个叫redo log的东西给到redo log buffer，也就完成了上方2的操作。

关于redo log

那么，这个redo log是一个什么呢？其实就是英文的字面意思：重做日志。redo log记录的是物理级别上的页修改操作。每条redo记录由：表空间号+数据页号+偏移量+修改数据长度+具体修改的数据 组成。

redo log buffer在接收到buffer pool传来的redo log时，会先标记为prepare状态。那么什么时候，redo log会从prepare状态转变成commit状态呢？

（1）InnoDB存储引擎已经准备好提交事务，处于prepare状态；
（2）执行器接收到存储引擎已经就绪的通知之后，就会生成对应的binlog log；
（3）执行器调用引擎的事务提交接口，将redo log的状态改为commit状态，更新完成。

我们知道，内存是“易失的”，只有将数据传到磁盘中才不会因为断电而丢失数据。铺垫了这么久，操作3才是我们要讲的：保证持久性的关键。接下来博主要向大家介绍的就是InnoDB的刷盘策略。

InnoDB的刷盘策略与innodb_flush_log_at_commit参数的配置有关，一共有三个参数。在为大家介绍不同参数的策略之前，博主先告诉大家redo log从redo log buffer传到redo log file的具体流程。为了提高文件写入效率 ，redo log并不是直接从内存传到磁盘，而是需要经过page cache，也就是文件系统缓存。真正何时写入会交给操作系统自己决定。示意图如下：

接着我们来介绍具体的刷盘策略：
当innodb_flush_log_at_commit参数为1时是最安全的，但效率也是最低的。此时：每次事务提交后都会被同步刷盘。具体流程是：redo log buffer会将redo log强制写入page cache，文件系统缓存又会强制fsync，马上把日志刷到redo log file中。参数1的特点可以总结为：实时写，实时刷。

当innodb_flush_log_at_commit参数为2时是较安全的，也是较高效的。此时：每次事务提交后，只是强行写入，但都会被同步刷盘。这就会有一个问题：如果操作系统宕机了，那么在page cache的数据就会丢失，这就不能满足我们持久化的要求了。参数2的特点可以总结为：实时写，延迟刷。

当innodb_flush_log_at_commit参数为0时是最不安全的，但是最为高效。此时：每次事务提交后都不进行刷盘。同样但不止于 操作系统宕机， 数据库宕机 与 网络错误 都会导致数据丢失。这个策略也不能满足我们持久化的要求。参数0的特点可以总结为：延迟写，延迟刷。

关于上方的三个策略还有一个问题：参数为2 与 0时，我们会丢失多少数据？上方截图中其实已经说明了。不论是数据库宕机还是操作系统宕机，都只会丢失1s的数据。

读完上方的内容，我们终于知道了：持久性是如何保证的数据的生效。这是通过提交到redo log file中的日志状态决定。redo log在进行数据重做时，读到commit标识，才会认为这条redo log是完整的，才会进行数据重做。否则，会认为redo log不完整，不会进行数据重做，这就有两个可能：一种是事务还没提交，此时并不影响磁盘中的数据，也不需要rollback，重新在start transaction开始执行就即可；另一种是事务提交了，但是数据也真的丢失了（策略2 与 0）。

上方的内容已经可以解开大家非常多的疑惑了，但我们还有一个重要的问题需要解答：

为什么不直接在buffer pool修改后直接传到磁盘？

我们上方说了，硬盘是以数据页的方式传给buffer pool的。而数据页大小就是16KB。与redo log相比主要是两个原因：

1.刷盘是需要消耗资源与时间，我们可能就修改了数据页里的几Byte的数据，没有必要把完整的数据页刷盘
2.数据页刷盘是随机写（需要移动磁头），一个数据页对应的位置可能在硬盘文件的随机位置。

虽然redo log也会有一次磁盘IO操作，但我们只是将redo log追加到文件末尾，属于顺序写（不需要移动磁头）。因此redo log机制更优，更能提升MySQL的性能。

隔离性

隔离性这个特性产生的原因是：服务器要同时给多个客户提供服务。因此多个客户端可能会同时发起多个事务，当这多个事务同时操作同一个数据库的同一张表时，就有可能会引来麻烦。因此我们设置了四个隔离级别来降低或者消除这种影响。

隔离级别越高，意味着事务之间并发程度越低，执行效率越慢，但是数据的准确性越高；隔离级别低则相反。具体要选择何种隔离级别需要结合业务需求具体分析。

（1）脏读

首先，什么是脏读呢？博主举一个例子给大家说明：有一天博主正在写作业，这时博主的室友就像抄我的作业，他来的时候看到我的答案是ABCD，于是他就把这个填了上去，但是博主在写完之后又检查了一遍，把原本的答案改成了CCCC。这时候就会发生脏读。因为室友读到了的答案被修改了。

那么脏读就是：一个事务进中，有读操作和写操作，而写操作会影响到读操作，导致读到的是不正确的。

那么我们如何去避免脏读呢？专业的说法是：给写操作加锁。相当于，博主自己躲在小黑屋写，等真正写完了再拿出来，这时室友读到的就直接是CCCC。这个操作就提高了隔离性，降低了并发程度。

（2）不可重复读

我们继续用上面的示例给大家解释什么不可重复读。在室友看我的答案时呀，完全没有想到，我写答案的那支笔是一支消失笔。因此他看到一半就会发现我下面的答案不见了。相当于读到一半，答案被我修改了。

因此不可重复读就是：一个事务中，连续两次读到的结果不一样。

那么如何解决不可重复读问题呢？同样专业的说法是：给读操作也加锁。意思就是：博主在写的时候不能用消失笔了，在室友读这份答案的时候，答案不能发生任何改变。给读操作加锁，同样提高了隔离性，降低了并发程度，而数据的准确性再一次提升了。

（3）幻读

在读操作和写操作被加锁之后，博主在室友读原本那份答案时不能去修改它，此时我没事干就决定：再写一张试卷。这是室友就发现，怎么要看的试卷越来越多了。

这时我们就知道，幻读问题就是：同一事务中，两次读到的结果集不相同。

幻读问题的解决方法就很干脆了：彻底舍弃并发，串行化编程。这就说明，室友在读答案的时候，我只能摸摸鱼，玩玩手机。

（4）四个隔离级别

四个隔离级别如下：

1.read uncommited --> 随意并发，会产生：脏读+不可重复读+幻读
2.read committed --> 只解决了脏读，仍然存在：不可重复读+幻读
3.repeatable read --> 再次提高隔离程度，只可能产生：幻读
4.serializable --> 严格串行化，隔离性最高。解决所有问题，但执行速度最慢。

引用

MySQL三大日志详解
redo log —— MySQL宕机时数据不丢失的原理