MySQL常规知识点总结
mysql的知识点远远没有这个文档里描述的这么肤浅。如果你需要，推荐你
极客时间丁奇的专题课程《MySQL实战45讲》
https://time.geekbang.org/column/intro/139
SQL的执行流程
MySQL的索引结构
索引覆盖
EXPLAIN查询命令
ICP索引下推
锁的分类
Redo log重做日志
undo log 回滚日志
事务隔离级别之读(RU)未提交
MVCC多版本并发控制
事务隔离级别之读已提交(RC)readview解决脏读
事务隔离级别之可重复读(RR)readview解决不可重复读
事务隔离级别之可重复读(RR)间隙锁解决幻读
关于死锁
SQL的执行流程
客户端请求连接，服务端接收连接请求，连接器做权限验证分配连接，分析器解析SQL形成语法树，如果是查询语句则到
缓存中查询，如果有直接返回(MySQL8取消了缓存机制)，如果没有优化器则会优化SQL形成执行计划，选择索引，接下来
执行器调用SQL接口，调用存储引擎层。
MySQL的索引结构
MySQL基于B+树实现索引,根节点和叶子节点（只有最后一层是叶子节点，换而言之没有分支的是叶子节点）可能重复，根节点不存数据。
以Innodb引擎来说，无论是主键索引还是其他辅助索引（不是主键索引都是辅助索引，换而言之，唯一索引，组合索引，全文索引都是辅助索引）
索引根节点上只存储索引本身的值，不存储具体的行记录。若是主键索引则最后一层的叶子节点上除了存储索引值，还存储该索引表示的行记录。
即主键索引的叶子节点记录的是一行记录，而辅助索引最后一层的叶子节点存储的是主键索引（引用）
索引覆盖
上一个点上说到，辅助索引的叶子节点存储的是主键索引。如果一条查询语句的返回字段是主键索引，次要索引这两个列。则表示查询辅助索引
树就能满足返回列。辅助索引树上查询不到需要返回的列，则需要根据主键索引再次回到主键索引树查询。这个过程叫做回表，而无需回表就是
索引覆盖。
EXPLAIN查询命令
id:一条SQL可能是几个SQL组成的，例如子查询，关联查询。例如SELECT * FROM USER WHERE ID IN (SELECT ID FROM USER);
这样的SQL根据EXPLAIN查询出来的是多行，id越大执行优先级越高，id相同则从上到下执行。
select_type:标记查询是什么类型，SIMPLE表示简单查询，UNION表示查询结果使用了连接，SUBQUERY表示使用到了子查询。等
table:使用到的表，如果是<xx>表示这是个临时表，<union M,N>表示结果是union的结果。
type:访问类型性能从好到差system const eq_ref ref range index all。除了all以外其他表示使用到了索引。
system:如果是只有一行数据或者是空表。
const:使用了唯一索引或主键索引。
eq_ref:关联查询时等值两边使用的是主键索引或唯一索引，也就是ON a.id=b.id 两个都是主键或唯一索引。
ref:关联查询时等值两边没有使用主键索引或唯一索引。
range:查询中使用了 >, <,is null,between and,in,like等范围查询。
index:select结果列中使用了索引，多数都是使用了索引覆盖
all:全表扫描数据然后在server层过滤记录。
possible_keys:这次查询可能会用到的索引。
key:这次查询实际用到的索引。
key_len:索引的长度。
ref:如果是关联查询，被驱动表的执行计划会显示驱动表的关联字段。
rows:可能返回的行，不准。
extra:一些额外的表述字段
using filesort:表示排序没有使用索引
using index:表示使用了索引覆盖
using where:表示需要到server层进行过滤
using index condition:表示使用了索引下推(ICP)
ICP索引下推
索引下推功能在MySQL6才有。MySQL6之前查询数据分为两个步骤 index key和table filter
在MySQL6之后增加了index filter
存储引擎层通过通过索引确定查询范围，叫index key。将查询范围内的索引，如果需要回表则回表查询其他字段，最后返回到server层，
server层在再次进行条件过滤叫table filter。官方给出了一个例子。
SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
上面例子中的 lastername like '%etrunia%' 和 address like '%Main Street%' 本来是无法 使用复合索引
index(zipcode, lastername, firstname) 进行过滤的，但是因为有了ICP技术，所 以他们可以在 index filter
阶段使用索引进行过滤，无需进行 table filter 。
通俗的将有了ICP将某些本该索引失效的查询，现在也可以使用到索引了。
锁的分类
这里需要掌握的锁有表锁，元数据锁，行锁。有一点需要各位注意的是无论是以上哪一种锁，都遵循一个规则，写和写不互斥，写和读肯定互斥。
表锁几乎不需要我们自动加，大部分情况都是行锁升级为表锁。行锁锁的是索引，也就是说无论是行S锁还是行X锁，它们都是锁的索引。既如果
是全表扫描的状态，则升级为表锁，很简单如果有索引可以通过index key找出范围内的索引将其锁定，而全表扫描无法确定具体的行记录，只能
升级表锁。元数据锁也就是一个线程在更改表结构时其他的线程无法对该表做任何的增删改查，同样如果有线程在执行增删改查，则无法更改表
结构。
Redo log重做日志
Redo log是InnoDB中的日志它的作用是保证MySQL异常宕机后，数据可以被回复。在这里给出一张InnoDB的架构图。
InnoDB分为三个模块，1内存模块2线程模块3磁盘文件。磁盘文件需要额外的说明，在MySQL5.6.6之前所有表的表
结构和表数据都在ibdata数据文件中，称之为共享表空间，共享表空间默认只有一个名为ibdata1，默认大小为10MB。
可以通过参数 innodb_data_file_path=/db/ibdata1:1000M;/dr2/db/ibdata2:1000M:autoextend 设置共享表空间
的数量和单个大小。上边设置表示初始化有ibdata1,ibdata2两个文件大小分别为1000M，两个不够用时自动增长。
而在5.6.6及其以后的版本MySQL默认的将每个表的表结构和表数据都单独放在了两个文件中 .frm存储表结构 .ibd
存储表数据也就是索引数据，这称之为用户表空间。通过参数innodb_file_per_table = 1 同样可以将用户表空间
从系统表空间剥离出来。
内存模块通过线程模块将磁盘文件加载到内存，当我们需要对数据做处理时，先从磁盘文件加载索引，加载索引以page为单位，
一页为16K,也就是说，当我们需要某一段数据时，线程模块需要从磁盘文件16K，16K的将数据读取到内存模块。在内存中的数
据可以返回给server层使用，也可以做更改，不过此时的更改只是更改了内存中的数据，在内存中被修改的数据没有持久化到
磁盘上的数据称为脏页。InnoDB无论无何要保证脏页能够安全的落盘就也是持久化到idb文件中，否则一旦MySQL异常崩溃将造
成内存数据和磁盘文件数据不一致。
InnoDB使用Redo log日志保证数据一定会被刷新到磁盘，Redo log默认有两个ib_logfile0和id_logfile1。这两个日志文件的
大小可以通过innodb_log_file_size设置。有了这两个文件现在落盘的流程大概是下图这样的，BufferPoll中的内存脏页数据
会自动刷盘到ibd文件中，同时也会写入到redo log中。那么既然一定会写入到ibd文件中为什么还要多非劲写入一份数据到redo
log中呢？其实是这样的，redo_log文件是顺序写入，而idb文件是随机写入。顺序写入意味这redo log日志文件在磁盘中的位置
是固定的每次磁针只需要定位一次到redo log日志文件位置就可以接着快速写入。而随机写入意味着需要先找磁盘中哪一块有位
置，然后才能写入。实际上，redo log的写入频率一定是比ibd文件的写入频率高的。内存数据更改后先更新redo log日志文件
然后就算事务提交成功，在真正的落盘时机也就是checkpoint时才会把内存数据再次写入到ibd文件中。如果在数据没有写入到
ibd文件时MySQL崩溃则从redo log中恢复数据。
其次要说的是redo log也就是ib_logfile0和id_logfile1这两个文件是循环写入的，当0文件写满时写1文件，1文件快写满时，将0文件擦除一部分
然后继续往0文件中写。这意味着innodb_log_file_size的大小要合理的控制，太小了写不了多少日志，太多了当系统崩溃时需要恢复大量的数据。
既然redo log也是磁盘文件那它的写入规则是怎样的呢？innodb_flflush_log_at_trx_commit 参数有三个可选值，默认是1
0 表示事务提交时不进行写入重做日志操作，该操作只在主线程中完成。这意味着事务提交后数据也不一定写入到 redo log日志中。
1 表示事务提交必须进行一次fsync操作，最安全的做法，事务提交后只有真正落盘到redo log后才算提交完成。
2 表示提交时写入重做日志，但是只写入文件系统缓存，不进行fsync操作。
最后要说的是checkpoint的落盘时机，这里再次声明一点，如果MySQL没有异常崩溃，那么ibd文件中的内容一定是从内存中落盘
的，而不是从redo log中更新过来的。
1 在buffer pool不够用时，也就是MySQL的内存快满了，此时就要做刷盘操作，将脏页落盘。
2 redo log空间快满了，也要刷新，因为redo log在快满时一定会删除一部分日志，如果不刷新
则表示一旦MySQL崩溃被redo log删除的数据无法恢复。
3 数据库正常关闭时一定会将脏页落盘。
最后落到ibd文件的步骤也不是说起来那么简单的，在innodb架构图中有一块功能较double write的功能。
undo log 回滚日志
undo log日志很重要，MVCC多版本并发读，事务回滚，解决事务脏读，事务不可重复读都依赖于undo log日志。
首先undo log日志没有单独的日志文件而是存储在ibdata系统表空间中。根据类型undo log日志两种
事务隔离级别之读(RU)未提交
读未提交是MySQL的第一个隔离级别，它没有加锁。一个事务内更改了数据，
没有提交，另外一个事务内可以看到该次更改的记录。显而易见，读未提交
产生的问题就是脏读，我还没提交你就读到值了，我要是回滚了怎么办？你
读到的肯定是脏数据了。
MVCC多版本并发控制
本来应该接着写隔离级别之读已提交，但是这里先插入一下什么是多版本并发控制。
在上边的读未提交中出现脏读的原因是对要更改的行数据没有加锁，导致这一行记录
被A线程读了，也被B线程写了。那把这行记录锁起来是不是就可以了，是的，当然可以，
但MySQL没有这么做。MySQL对于读和写同一行没有加锁，而是分快照读和当前读。
当操作是更改或者删除，需要对这一行加X写锁，其他线程不能写，但是可以读。其他
线程读的是快照也就是undo log中已经提交的最新记录。
事务隔离级别之读已提交(RC)readview解决脏读
好了，我们已经引出了快照读和当前读的概念，解决了读写并发问题。接下来
隔离级别升级成了读已提交，它解决的问题是脏读。上边我们说过，一个线程
读数据读的是快照，简单的说，我们只要从快照中筛选已经提交的版本就能解决
脏读。每次读数据我们总是从获取当前活跃未提交得本版号，从undo log版本链中
中筛选已经提交得数据可见得数据，这样就可以完美得解决数据脏读的问题，但是
这也引发另一个问题，不可重复读，在同一事物内，两次读取竟然不一致。
事务隔离级别之可重复读(RR)readview解决不可重复读
不可重复读的解决方案也是使用readview解决的，简单的思路就是，当事务开启后第一次获取的readview
被重复使用。
事务隔离级别之可重复读(RR)间隙锁解决幻读
这个暂时跳过，因为我发现，直到现在我都不能十分准确的描述什么是幻读！找了一下午的资料
发现很多都把不可重复读和幻读搞到一块。甚至说解决了不可重复度就解决了幻读。虽然我没有
准确的结果，但我认为这么说是错误的。间隙锁可以先聊一下。所谓的间隙锁就是当对某个索引
范围内的数据做更改或者删除操作时，除了会锁住命中索引的几行外，还会将他们中间也锁上。
不让其他线程插入或者更改。以下图例name是索引，id是主键索引。session1要删除name为5-8的
数据没有提交事务，session2是无法插入一条id为4，name为6的数据的。原因就是使用了间隙锁。
关于死锁
有一个常见的问题是mysql中如何造成一个死锁。简单的大家都会想到A,B和B,A的方式。两个线程互相
持有对方需要的行，就会造成死锁。其实根据上边的间隙锁还有一种更优雅的答案。每个session只有
一条SQL也可以死锁。id是主键索引，name和pubtime是辅助索引。线程1的条件是辅助索引name首先它锁定
两行，然后它会依次锁定对应的两个主键索引1，6。线程2条件是辅助索引pubtime它需要锁定的主键索引是
6，1。造成死锁的顺序就是线程1去先锁定主键1，线程2先锁定主键6。这样就死锁了。这个例子别去测试，
只是理论上的。
