1.索引

分类

索引分单列索引和组合索引。

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
• 组合索引，即一个索引包含多个列。
  索引也可以分为普通索引和唯一性索引
• 普通索引:最基本的索引，它没有任何限制
• 唯一性索引:与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

索引的缺点:

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
• 建立索引会占用磁盘空间的索引文件。

2.主键

定义

主键(primary key):一列(或者一组列),其值能够唯一区分表中的每个行；
没有主键，更新或删除表中的特定行很难，因为没有安全的办法保证只涉及相关的行。

规则

• 任何两行都不能具有相同的主键值；
• 每个行都必须具有一个主键值(主键列不允许有NULL值)；

3.一条SQL语句如何执行的？

3.1 MySQL架构

image.png
大体来说，MySQL分为两层架构:server层和存储引擎层：
server：包括连接器，查询缓存，分析器，优化器和执行器，涵盖MySQL的大多数核心服务功能。所有跨存储引擎的功能都在这一层实现。
存储引擎层:负责数据的提取和存储。架构模式是可插拔式的，InnoDB为默认的存储引擎。

3.2 连接器

作用划分:建立连接、获取权限、维持和管理连接。

3.2.1权限

• 一个用户成功建立连接以后，即使用管理员账户对这个用户的权限进行了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

3.2.2 长连接

• 建立连接的过程是比较消耗资源的，所以在使用中尽量减少建立连接的动作，即尽量使用长连接。
  使用长连接后内存涨的特别快的原因？
  -- MySQL在执行过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才释放。如果长连接累积下来，可能会导致内存占用过大，被系统强行杀掉(OOM),从现象上看就是MySQL异常重启。
  如何解决长连接造成的大内存呢？
• 定期断开长连接。使用一段时间或者判断程序里面执行一个占用内存的大查询后，断开连接，之后要查询再重新连接。
• 在每次执行一个比较大的操作后，执行mysql_reset_connection 来初始化连接资源。这个过程不需要重连和重新做权限校验，但是会将连接恢复到刚刚创建完的状态。

3.3 查询缓存

弊端

1. 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
2. 对于更新频繁的数据库来说，查询缓存的命中率会非常的低；

故适合静态表，很长时间才会更新一次，比如一个系统的配置表；

3.4分析器

词法分析

你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。

MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

语法分析

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。
这个就是在分析器中的报错。

3.5 优化器

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。
优化器是在表里面有多个索引的时候，决定使用哪个索引；
或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

3.6 执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

• 开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误。
• 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

4.redo log和binlog

4.1 redo log

问题

如果MySQL每一次更新操作都写进磁盘，同时磁盘也要找到对应的那条记录，然后更新。
整个过程的IO成本、查找成本都很高；

如何解决

MySQL中的WAL(Write Ahead Logging)：先写日志，再写磁盘；
具体流程:
当有一条记录需要更新的时候，InnoDB首先会把记录写到redo log，然后更新到内存中，整个过程就算完成了。
InnoDB会在适当的时候，将redo log中的数据更新写入磁盘。

适当的时候:1.系统比较空闲的时候；2.redo log空间已满的时候

crash-safe

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe

4.2 bin log

定位

redo log是InnoDB独有的日志。
server层也有自己的日志，binlog，即归档日志，没有crash-safe的能力。

bin log VS redo log

• redo log 是InnoDB独有的日志，具有crash-safe的能力；binlog 即归档日志，是所有搜索引擎都可以使用的，没有crash-safe的能力；
• redo log 是逻辑日志，是某个数据页上面做了什么修改；bin log是逻辑日志，记录的是这个语句的原始逻辑，比如某表某行的某个字段加1
• redo log 大小的固定的，可能会存储满；bin log是可以追加写的，没有大小的限制，即有归档的概念；

• Redo log记录这个页 “做了什么改动”。
• Binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

4.3 update的流程

image.png

• 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
• 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
• 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

4.3.1 怎样让数据库恢复到半个月内任意一秒的状态？

前提:binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。

• 备份系统中一定会保存最近半个月的所有 binlog;
• 同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。
  如何操作:
  当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：
• 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
• 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。
• 这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

4.3.2 为什么日志需要“两阶段提交”

一句话总结：简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

这里不妨用反证法来进行解释。
由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。

仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

1. 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

各个时间点奔溃的问题引申：

1 prepare阶段 2 写binlog 3 commit
当在2之前崩溃时
重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。
一致
当在3之前崩溃
重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

引申:读写分离时，读库的数据如何复制？
全量备份加上应用 binlog 来实现的

4.3.3 bin log与crash safe的关系

什么是crash safe

CrashSafe指MySQL服务器宕机重启后，能够保证：

• 所有已经提交事务的数据仍然存在。
• 所有没有提交事务的数据自动回滚。
  Innodb通过Redo Log和Undo Log可以保证以上两点。

为了保证严格的CrashSafe，必须要在每个事务提交的时候，将Redo Log写入硬件存储。这样做会牺牲一些性能，但是可靠性最好

使用2PC协议。
事务的协调者Binlog
Binlog在2PC中充当了事务的协调者。由Binlog来通知InnoDB引擎来执行prepare，commit或者rollback的步骤。事务提交的整个过程如下：

1. 协调者准备阶段(Prepare Phase)
   告诉引擎做Prepare，InnoDB更改事务状态，并将Redo Log刷入磁盘。
2. 协调者提交阶段(Commit Phase)
   2.1 记录协调者日志，即Binlog日志。
   2.2 告诉引擎做commit。
   注意：记录Binlog是在InnoDB引擎Prepare(即Redo Log写入磁盘)之后，这点至关重要。

恢复前事务的状态
在恢复开始前事务有以下几种状态：

• InnoDB中已经提交
  根据前面2PC的过程，可知Binlog中也一定记录了该事务。所以这种事务是一致的不需要处理。
• InnoDB中是prepared状态，Binlog中有该事务的Events。
  需要通知InnoDB提交这些事务。
• InnoDB中是prepared状态，Binlog中没有该事务的Events。
  因为Binlog还没记录，需要通知InnoDB回滚这些事务。
• Before InnoDB Prepare
  事务可能还没执行完，因此InnoDB中的状态还没有prepare。根据2PC的过程，Binlog中也没有该事务的events。 需要通知InnoDB回滚这些事务。

恢复过程
从上面的事务状态可以看出：恢复时事务要提交还是回滚，是由Binlog来决定的。

• 事务的Xid_log_event存在，就要提交。
• 事务的Xid_log_event不存在，就要回滚。

恢复的过程非常简单：

• 从Binlog中读出所有的Xid_log_event
• 告诉InnoDB提交这些XID的事务
• InnoDB回滚其它的事务

CrashSafe的写盘次数
保证CrashSafe就要设置下面两个参数为1：
sync_binlog=1
innodb_flush_log_at_trx_commit=1

• sync_binlog
  sync_binlog是控制Binlog写盘的，1表示每次都写。由于Binlog使用了组提交(Group Commit)的机制，它代表一组事务提交时必须要将Binlog文件写入硬件存储1次。
• innodb_flush_log_at_trx_commit的写盘次数
  这个变量是用来控制InnoDB commit时写盘的方法的。现在commit被分成了两个阶段，到底在哪个阶段写盘，还是两个阶段都要写盘呢？
• Prepare阶段时需要写盘
  2PC要求在Prepare时就要将数据持久化，只有这样，恢复时才能提交已经记录了Xid_log_event的事务。
• Commit阶段时不需要写盘
  如果Commit阶段不写盘，会造成什么结果呢？已经Cmmit了的事务，在恢复时的状态可能是Prepared。由于恢复时，Prepared的事务可以通过Xid_log_event来提交事务，所以在恢复后事务的状态就是正确的。因此在Commit阶段不需要写盘。

总的来说保证MySQL服务的CrashSafe需要写两次盘。在2PC的过程中，InnoDB只在prepare阶段时，写一次盘。Binlog在commit阶段，会设置一个参数告诉InnoDB不要写盘。

总结

MySQL通过两阶段提交的方式来保证CrashSafe。CrashSafe需要Server层、Binlog和InnoDB的协同工作才能完成。由于DDL和MyISAM不支持事务性，因此没办法保证CrashSafe。

5 事务隔离级别

5.1隔离性与隔离级别

• 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
  如何实现？

数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

1. 在“可重复读”隔离级别下：这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
2. 在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
3. “读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；
4. “串行化”隔离级别下直接用加锁的方式来避免并行访问。

什么时候需要“可重复读”的场景呢？
假设你在管理一个个人银行账户表。
一个表存了账户余额，一个表存了账单明细。
到了月底你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。
你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响。

5.2 事务隔离级别的实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录的最新值，通过回滚操作，都可以得到前一个状态的值。

image.png

在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。

回滚日志什么时候删除呢？
答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。

5.3 事务的启动方式

有几个概念捋清楚：

1. 默认的MySQL行为是自动提交所有更改的。即任何时候执行一条语句，该语句实际上都是针对表执行的，而且所做的更改立即执行。
   如果不想自动提交，需要设置 set autocommit=0.
   因此:
   autocommit=0，不会自动提交，需要手动commit；
   autocommit=1,每次执行修改语句会自动执行commit，但是在transcation流程控制中不会触发。
2. 有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。
   因此，我会建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。（显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback）。
3. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
4. 在 autocommit 为 1 的情况下(这里有个点注意下：在事务处理块中，提交不会隐含地进行)，用 begin 显式启动事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

参考文档

https://blog.csdn.net/n88Lpo/article/details/78099207?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase