一、索引

1. 为什么使用索引

提高查询效率，避免全表扫描;

2. 如何定位并优化慢sql

先说步骤：

第一步：根据慢日志定位慢查询sql
- 根据慢查询日志确定慢sql：
  - 使用show variables like '%quer%';可以看到一些变量（可以使用set global xx=..;, 也可以在my.cnf配置文件里修改，是mysql server的启动配置），其中:
    1. slow_query_log 表示满查询日志是否开启；
    2. long_query_time 超过了这个时间的将被视为慢查询（一般超过1秒就被视为慢查询, 修改完需要重新获取connection）
    3. slow_query_log_file: 慢查询日志保存位置
  - 系统状态：
    - 使用show status like '%slow_queries%' ; 查询下面这个变量
    - Slow_queries: 慢查询的数量 ( 客户端重新连接时，这个数据会被清0 )

第二步：使用explain等工具分析sql
- 使用explain去分析这个慢sql，这里只介绍常用的关键字段，更多字段参考这里.
  - type： 表示sql找到数据行的方式, 其取值范围如下（性能递减，越往下，性能越差）:
    - system:
      - 只有一条数据的系统表，或者派生表只有一条数据自查询
        1
        2
        select a.name
        from ( select * from teacher t where t.id = 1 ) a ;
    - const:
      - 查询的结果“只有一条记录” 的sql，用于主键索引或唯一索引, 如果where中使用的条件列上没有主键或者唯一索引，那么就一定不是const.
    - eq_ref：
      - 唯一性索引，对于每个键的查询，返回匹配唯一行数据
        
        const和eq_ref的区别是：
      - 简单地说是const是直接按主键或唯一键读取，eq_ref用于联表查询的情况，按联表的主键或唯一键联合查询。
      例如下面这个例子是单表中的，所以是const：
      1
      SELECT * FROM t1 WHERE primary_key=1;
      而下面这个是对多个表的联合查询，所以是eq_ref:
      1
      2
      select name from t1, t2
      where t1.user_id = t2.id;
    - ref:
      - 非唯一索引访问(只有普通索引，唯一索引和非唯一索引的区别见附录)
    - fulltext
    - ref_or_null
    - index_merge
    - unique_subquery
    - index_subquery
    - range：
      - range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<’外，in和or也是索引范围扫描。
    - index：另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序
    - all：全表扫描，需要优化！
  - extra： 获取更为详细的信息，辅助了解sql的执行方式：
    - Using filesort：
      - 表示Mysql会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上排序。MYSQL中无法利用索引完成的排序操作称为“文件排序”。一句话来说就是：mysql的索引无法起到排序的作用，而是需要通过外部的文件排序来完成，会很慢！需要优化！！
    - Using temporary：
      - 表示MYSQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by。使用临时表来完成排序，也很慢！！需要优化了！！
第三步：修改sql或者让sql尽量走索引
- 修改sql 或让sql走索引

2. 最左前缀匹配

个人理解是这样的：

Innodb使用B+树实现了索引，当建立联合索引的时候，假设index(col1, col2)，叶子结点也是递增有序的，这种排序方式是先根据看col1的key进行排序，如果col1相等，再根据col2进行排序，以此类推；因此，在where条件中如果有对col1的筛选条件，那么就能用到B+树的有序性，在logN复杂度下找到首个匹配的col1的位置，同时，如果不仅有col1，还有col2的话，还可以继续这样进行索引下推的去匹配… 但是，如果在where的条件中没有col1，只有col2，那将无法使用到这个索引，因为，整体上来看col2实际上是无序的（只有在col1相等所有情况中col2才是有序的）。
看懂了上面的机制之后，也就明白为什么：如果在联合索引的第 i 个位置命中了范围查询，那么 i 之后的列就无法使用到索引了。
另外，where条件中的列匹配的顺序并不一样非要按照索引的顺序排列才能生效，Innodb内部会帮我们按照尽量按照能命中索引的方式进行排序.

3. 索引建立的越多越好吗？

数据量小的表不需要建立所以，建立会增加额外开销；
数据变更需要维护索引，因此更多的索引意味着更多的维护成本
更多的索引意味着也需要更多的空间

二、日志

1、redo Log 和 bin log的区别

redo log是Innodb引擎独有的，是引擎层面的。而bin log是MySQL的Server层实现的，所有引擎都可以使用。
redo log的作用是实现crack-safe；binlog主要用于数据归档和数据恢复，实现 “让数据库恢复到半个月内任意一秒的状态”；
redo log记录的是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”
redo log是循环写的，空间固定会用完（[checkpoint, writepos] 是已写数据）。
binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

2、两段式提交

2.1 Innodb引擎执行一条update的流程：

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

上述过程中，将redo log的写入拆成了两个步骤：prepare和commit，这就是”两阶段提交”。

2.2 为什么要两段式提交？

redo log 和 bin log的操作必须要保证其原子性，如果不用两阶段提交，要么就是先写完redo log再写binlog，或者采用反过来的顺序，这两种情况均无法保证原子性！

先写redo log后写binlog：
- 写完redo log，系统就挂了。。在恢复的时候发现redo log已经commit了，因此就继续执行，但此时bin log缺没有被记录。下次在恢复数据的时候，就会出现错误；
先写bin log 再写redo log:
- 写完bin log，系统就挂了。。在恢复的时候发现redo log还没写，因此就重新执行这个事务，但此时binlog里已经写过了，然后会再次记录这条记录，就会多一次操作记录，下次再恢复的时候依然会出现错误！

因此，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

2.3 在两阶段提交的不同时刻MySQL出现异常，重启后会出现什么情况

在崩溃恢复时，MySQL会做以下判断规则：
- 如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；（这种情况就是commit完成之后发生了crack）
- 如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整；
  - 如果binlog已经写完，则进行commit。
  - 如果binlog未写或没有写完整，则进行回滚。
MySQL怎么知道binlog是完整的？
- tatement格式的binlog，最后会有COMMIT；
- row格式的binlog，最后会有一个XID event。
- 另外，在MySQL 5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容和正确性，对于binlog日志由于磁盘的原因，可能会在日志中间出错的情况，MySQL可以通过校验checksum的结果来发现，所以，MySQL还是有办法验证事务binlog的完整性的。
redo log和binlog是怎么关联起来的？
- 他们都有一个共同的数据字段，叫XID。奔溃恢复的时候，会被顺序扫描redo log：
  - 如果碰到既有prepare，又有commit的redo log，就直接提交。
  - 如果碰到只有prepare，没有commit的redo log，就拿着XID去binlog找对应的事情。

参考

2.4 binlog的三种模式

1. STATMENT模式：

基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。

优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。
缺点：在某些情况下会导致master-slave中的数据不一致（例如插入时间为当前时间，就会导致插入数据不一致）。

2. ROW模式

基于行的复制(row-based replication, RBR)格式：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

3. Mixed模式

前两种模式的组合；根据sql语句来采用不同的模式；

三、事务

3.1 事务的四大特性

原子性
将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
一致性
事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100。并发一致性问题：
- 丢失修改：
  - T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
- 读脏数据：
  - T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
- 不可重复读：
  - T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
- 幻影读：
  - T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。如果说不可重复读是读取同一数据的结果不一样的话，幻读就是存在性的结果的不一致。
持久性
　　
　持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。　　
隔离性
如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离，换句话说，对于事务本身来说，它所感知的数据库，应该只有它自己在操作。

然而多个事务执行时，如果严格遵守上面的要求，那么执行效率就会低下. 因此提出了四个隔离级别。

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200417144414834.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NjE2Mjk2,size_16,color_FFFFFF,t_70)
- #### 未提交读
    - 一个事务还没提交时，它做的变更就能被别的事务看到

- #### 提交读
    - 一个事务提交之后，它做的变更才会被其他事务看到

- #### 可重复读：
    - 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
- #### 可串行化：
    - 强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。
    - 该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。

3.2 多版本并发控制( MVCC )

Mysql中的Innodb引擎，对读提交和可重复读这两个隔离级别的具体实现是通过MVCC(多版本并发控制)实现的。

1. 解决的问题

解决了读写隔离性的问题（读提交，可重复读）。
在实际场景中读操作往往多于写操作，而读写操作又需要加锁来进行互斥完成，这不仅使得加大了锁带来的额外开销，还加大了死锁的风险。因此就使用<多版本>的思想来使得读写操作不再互斥.
MVCC的实现的主要思想是：保存多版本的快照，使得每条记录的不同版本对不同事务而言，可见性不一样.

2. 版本号

系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
事务版本号 TRX_ID ：事务开始时的系统版本号（在一个事务中，首次进行增删改操作的时候，才会生成这个系统版本号！）。
- 严格递增的事务版本号起到了”时间戳”的作用，从而能够辨别对某一行记录的操作相对于当前事务是提交前还是未提交还是未操作。

3. Undo 日志

Innodb引擎会对每一条记录多增加三个属性用于mvcc，这三个属性分别是：
- RowID：隐藏的自增ID，当建表没有指定主键，InnoDB会使用该RowID创建一个聚簇索引。
- DB_TRX_ID：最近修改（更新/删除/插入）该记录的事务ID。
- DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本。
MVCC 的多版本指的是多个版本的快照，每对一条记录进行增删改操作，就会生成一个快照存储到 Undo 日志中，在进行修改完值之后，还会修改其DB_TRX_ID属性为执行这条操作的事务ID，并修改DB_ROLL_PTR字段指向Undo log中刚刚保存的那条最新的旧记录，用于日后回滚（就是链表的头插法，每一个节点就是一个历史记录快照）。
MVCC通过db_roll_ptr来得到每一条记录在不同时期的历史版本，根据read_view来去对一行记录的每一个版本的trx_id字段进行可见性分析！！因此read_view决定了当前事务能够访问到哪个时期的这条记录！！！这也是实现可重复读和读已提交的不同之处的关键！！读已提交就是每次在执行select语句时，都会去主动获取最新的read_view，而可重复读则是在事务未结束之前都去使用最初获得的那个read_view！！

4. read_view

执行查询sql时，会生成一个一致性视图read-view（一定要注意，只有在查询语句开始执行的时候，才会生成这个read-view！！执行任何查询语句都会产生！！），根据read_view来去遍历指定的记录每一个时期的版本，由此来决定应该选择哪一个时期的版本。

read_view维护的内容:
- 当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, …}
- TRX_ID_MIN: 未提交事务列表中的最小事务版本号
- TRX_ID_MAX: 已创建的事务的最大事务版本号
可见性算法:
- 找出这一条记录对于当前事务而言的最新可见版本的方法（假设：对于某一条记录的版本A, 他的事务版本号为idx）：
  - idx<TRX_ID_MIN:
```
**<font color=red>说明此版本对于当前事务来说是已经commit过的了.. 因此可见.</font>**
```
  - idx>TRX_ID_MAX:
    属于尚未进行的事务，不可见。
  - TRX_ID_MIN<=idx<=TRX_ID_MAX:
    如果idx不等于当前版本号且 idx在{尚未提交}的事务列表中，那么不可见;否则，可见.
- 读提交:
  每一次操作，都获取最新的read_view。
- 可重复读:
  在第一次操作时，获取read_view, 之后每次操作都使用这个read_view。
- 删除的情况：
  - 对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将其DB_TRX_ID修改成执行删除操作的事务ID，同时在该条记录的头信息里的deleted flag标记为true, 意味着该记录已经被删除。（在查询遍历时，如果发现delete flag为true，那么就意味着数据已被删除，则不返回数据）。

5. 快照读/当前读

快照读:
上面介绍的这种mvcc通过read-view来进行的机制就是快照读。

SELECT * FROM table ...;

当前读:
- 当前读是基于临键锁（next-Key Lock）（行锁 Record Locks + 间歇锁 Gap Locks）来实现的，适用于 insert，update，delete， select … for update， select … lock in share mode 语句，以及加锁了的 select 语句。
- 更新数据时，都是先读后写，而这个读，就是当前读。读取数据时，读取该条数据的已经提交的最新的事务，生成的 readView。
- 例如事务 A 有2个 sql 语句，事务开始时生成 readView（id = n），第一个 sql 操作一条数据时读当前的 readView（id = n）。此时开始事务B生成 readView（id = n + 1），并且对该条数据做了操作（非简单 select 操作）。事务A的第2个 sql 语句当前读该数据时，就会读取该数据的最新事务视图 readView (id =n + 1) 的值。
- 而假如事务A的第二个 sql 语句操作数据时，事务B还未提交（非简单 select 操作），那么该条数据此时被事务B的写锁锁住。事务A的第二个 sql 语句操作数据（非简单 select 操作），那么也要获取该条数据的锁。而此时锁被事务B持有，事务A就会阻塞，等待事务B释放锁。

6. MVCC无法解决幻读

1. 举个例子：

t Session A                  Session B
|
| START TRANSACTION;         START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| empty set
|                            INSERT INTO t_bitfly
|                            VALUES (1, 'a');
|
| SELECT * FROM t_bitfly;
| empty set
|                            COMMIT;
|
| SELECT * FROM t_bitfly;
| empty set
|
| INSERT INTO t_bitfly VALUES (1, 'a');
| ERROR 1062 (23000):
| Duplicate entry '1' for key 1
v (shit, 刚刚明明告诉我没有这条记录的)

就是SessionA看不到B的提交，就没查出id=1的记录，于是再提交时就error了。

2. Next-Key Locks

Record Locks
- 锁定一个记录上的索引，而不是记录本身。
  如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。
Gap Locks
- 锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。
Next-Key Locks
- 它是Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间，

3. 解决方案

屏蔽其他事务的提交影响是保证可重复读的关键；但同时又是幻读产生的原因。
因此，mysql的innoDB提供的策略是：
- 普通的读，会得到一致性的结果（屏蔽了其他事务的commit）;
- 如果使用了加锁的读，就会读到“最新的”“提交”读的结果；
  结论：
结论：MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。

参考链接

四、索引

关于索引，cs-note其实总结的就非常好. 接下来我再补充一些.

1. 使用索引的注意事项？

在经常搜索的列上、经常出现在where中的列、经常需要排序的列上建立索引。
表非常非常大和表很小的情况下，不适合建索引，前者需要较大的维护开销，后者没有必要建立索引
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在使用InnoDB时，应该使用与业务无关的自增主键作为主键索引，而不要使用业务主键。因为，如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。总的来说就是可以提高查询和插入的性能。
limit offset 查询比较慢的时候，也可以借助索引提高性能

2. 创建索引需要注意什么？

选择合适的字段
被频繁使用的非NULL字段，例如：被作为查询条件，被作为查询的列，被频繁用于连接的字段等等
最左前缀原则
创建联合索引的时候，尽量保证从左到右使用频率递减的顺序去创建. 因为创建了一个<a,b,c>的联合索引，相当于创建了< a >, <a,b> , <a,b,c>这三个索引… 尽量保证 < a > ，<a,b> , <a,b,c>的使用频率都尽量的高。
尽可能创建联合索引，而不是单列索引

能创建联合索引，尽量创建联合索引，少创建单列索引，这是因为一个联合索引相当于创建了多个索引且对应一棵B+树，相比单个索引对应一颗B+树，对空间的利用率更高，间接的节省空间。

不适合创建索引的字段
- 频繁进行更新的字段 (因为索引的维护成本也是不小的)
- 不被经常查询的字段
避免冗余索引
冗余索引指的是索引的功能相同，能够命中就肯定能命中，那么就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

3. B树和B+树的区别

B树的所有节点既存key，又存数据(data); 而B+树只有叶子节点存key和数据，非叶子节点只存key。
因为1的特性，B树在查找的时候在非叶子节点处就能够查找到想要的值，而B+树则是每次一定查找到叶子节点上。
B树的叶子节点都是独立的，而B+树的叶子节点有一条引用链指向它相邻的叶子节点。

附录

唯一索引和非唯一索引的区别

非聚集索引又分唯一非聚集索引和非唯一两种，这同样是依据业务逻辑而定的，唯一非聚集索引指的是一个由多个业务字段组成的索引，在业务逻辑上是唯一的，反之就是非唯一的非聚集索引。比如“中国+身份证号”，这就是一个唯一索引，因为中国的每个身份证号理论上都是唯一的。而“中国+姓名”则是一个非聚集索引，因为中国又很多重名的人。