逻辑架构图

image.png

第一层不是Mysql所独有的，大多数基于C/S服务都有类似的架构。比如链接处理等。
第二层是Mysql服务层。大多数Mysql的核心服务功能都在这一层，包括查询解析，分析，优化，缓存以及所有的内置函数等。
第三层是存储引擎层。负责存储提取数据。存储引擎不会解析sql。

索引

InnoDB存储引擎支持一下几种常见索引
1、B+树索引
2、hash索引
3、全文索引
注意：InnoDB的hash索引是自适应的，存储引擎根据表的使用情况自动生成hash索引。是无法任务干预的。官方文档是这样描述的。

image.png

B+树介绍

（1）B+树的非叶子节点不保存数据的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的数据大大增加；

（2）B+树叶子节点保存了父节点的所有数据的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样；

（3）B+树叶子节点的数据从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。

索引分类

聚集索引：可以理解成主键。叶子节点包含数据。
非聚集索引：一个表可以有多个非聚集索引。叶子节点不包含行记录的数据。
聚合索引：聚合索引（a,b）,最左匹配原则。索引根据a排序，b根据a排好序以后再排序。类似于(1,1),(1,3),(5,3),(5,9)这也是为什么只根据b查询不走索引的原因。
覆盖索引：从非聚集索引中就可以得到记录。比如有索引(a,b)。select b from table where a=1。这就可以理解成覆盖索引。
全文索引：全文索引通常采用倒排索引来实现。倒排索引同B+树索引一样，也是一种数据结构。它在辅助表中存储了单词与单词自身在一个或多个文档中的所在位置之间的隐射。sql语句采用match against

查看表索引 show index from table

Table ：表的名称。

Non_unique ：如果索引不能包括重复词，则为0。如果可以，则为1。

Key_name ：索引的名称。

Seq_in_index ：索引中的列序列号，从1开始。

Column_name ：列名称。

Collation ：列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。[InnoDB都是A，Heap表都是NULL]

Cardinality ：非常关键的一个参数。表示的是索引中唯一值的数目的估计值。
Cardinality/n_rows_in_table的值应尽可能接近1，如果非常小，那么用户需要考虑是否可以删除该索引。

Sub_part ：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

Packed ：指示关键字如何被压缩。如果没有被压缩，则为NULL。

Null ：索引的列中含有NULL。含有NULL则为YES。如果没有，则这里显示为空。

Index_type ：索引的类型（BTREE, FULLTEXT, HASH, RTREE）

敲黑板：表的cardinality（可以翻译为“散列程度”），优化器会根据这个值来判断是否使用这个索引。但是这个值不是实时更新的，因为实时的话代价太大了，因此这个值不是太准确，只是个估值。

锁

InnoDB存储引擎中的锁

共享锁（S Lock）：允许事务读取一行数据。
排它锁（X Lock）：允许事务删除或者新增一行数据。
意向共享锁（IS Lock）：事务想要获取表中的某几行共享锁。
意向排它锁（IX Loxk）：事务想要获取表中的某几行排它锁。

一致性非锁定读

一致性非锁定读在不同的事务隔离级别下，读的快照数据（快照数据指该行会签的版本数据，是通过undo实现的）也不一样。在RC下，一致性非锁定读读的是最新快照数据，而在InnoDB默认的RR事务隔离级别下，一致性非锁定读读的是事务开始时的快照数据。默认情况下，也是良好的满足了ACID的隔离性原则。InnoDB在默认情况下的读采取的是一致性非锁定读，但在某些情况下，InnoDB不采用这种方法。比如，你在显式对读进行加锁情况下就变成一致性锁定读。

行锁

Record Lock：单个记录上的锁。
Gap Lock：间隙锁，锁定一个范围。不包含记录本身。InnoDB采用gap解决Phantom Problem。
Next-Key Lock :Gap Lock+Record Lock 锁定一个范围，并且锁定记录本身。当查询的索引有唯一索引，InnoDB会把Next-Key Lock 降级为Record Lock。仅锁住索引本身。查询的索引是非聚集索引，锁住的是一个范围。

分析一条sql

update table set a=1 where id=2;

id列是主键隔离级别RC
结论：id是主键时，此SQL只需要在id=2这条记录上加record-lock。

id列是唯一索引，RC隔离级别
结论：id是唯一索引时，此SQL需要在id=2这条记录上加record-lock；主键索引加record-lock。

id列是普通索引（不包括唯一），RC隔离级别
结论：id是普通索引时，此SQL需要在id=2这条记录上加record-lock；主键索引加record-lock。

id列上没有索引，RC隔离级别
结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。

id列是主键，RR隔离级别
结论：针对id=2这条记录加record-lock。

id列是二级唯一索引，RR隔离级别
结论：针对id=2这条记录加record-lock，主键索引也加record-lock。

id列是二级非唯一索引，RR隔离级别
结论：针对主键索引加record-lock，id=2加gap-lock

id列上没有索引，RR隔离级别
结论：锁全表

Serializable隔离级别
结论：跟RR级别一样。
在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

死锁

死锁是指两个事务在执行的过程中，相互争夺资源而造成的一种等待。
解决死锁的方法：
设置超时时间innodb_lock_wait_timeout
wait-for graph(等待图)。这个算法的机制是看事务之间是否形成回路。

image.png

sql执行顺序

1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
2. ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
7. HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中。
8. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
9. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
11. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

事务

基本性质ACID

原子性（Atomicity）：事务的原子性是指事务中包含的所有操作要么全做，要么全不做。

一致性（Consistency）：在事务开始以前，数据库处于一致性的状态，事务结束后，数据库也必须处于一致性状态。拿银行转账来说，一致性要求事务的执行不应改变A、B 两个账户的金额总和。如果没有这种一致性要求，转账过程中就会发生钱无中生有，或者不翼而飞的现象。事务应该把数据库从一个一致性状态转换到另外一个一致性状态。

隔离性（Isolation）:事务隔离性要求系统必须保证事务不受其他并发执行的事务的影响，也即要达到这样一种效果：对于任何一对事务T1 和 T2，在事务 T1 看来，T2 要么在 T1 开始之前已经结束，要么在 T1 完成之后才开始执行。这样，每个事务都感觉不到系统中有其他事务在并发地执行。

持久性（Durability）：一个事务一旦成功完成，它对数据库的改变必须是永久的，即便是在系统遇到故障的情况下也不会丢失。数据的重要性决定了事务持久性的重要性。

redo log

持久性是由redo log（redo log是物理日志）实现。redo log（重做日志）用来实现事务的持久性，即事务ACID中的D。其由两部分组成，一是内存中的重做日志缓冲（redo log buffer），其实易失的。二是重做日志文件（redo log file），其是持久的。在一个事务中的每一次SQL操作之后都会写入一个redo log到buffer中，在最后COMMIT的时候，必须先将该事务的所有日志写入到redo log file进行fsync持久化（这里的写入是顺序写的），待事务的COMMIT操作完成才算完成。

LSN

LSN(log sequence number) 用于记录日志序号，它是一个不断递增的 unsigned long long 类型整数，占用8字节。它代表的含义有：
redo log写入的总量。
checkpoint的位置。
页的版本，用来判断是否需要进行恢复操作。
checkpoint：它是redo log中的一个检查点，这个点之前的所有数据都已经刷新回磁盘，当DB crash后，通过对checkpoint之后的redo log进行恢复就可以了。

undo log

重做日志记录了事务的行为，可以很好的通过其对页进行“重做”操作。但是事务有时候还需要进行回滚操作，也就是ACID中的A（原子性），这时就需要Undo log了。因此在数据库进行修改时，InnoDB存储引擎不但会产生Redo，还会产生一定量的Undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户一条ROLLBACK语句请求回滚，就可以利用这些Undo信息将数据库回滚到修改之前的样子。