Mysql底层十大基础知识

相关基本概念及DDL语句在这里就不赘述了，本篇文章主要聊一聊mysql底层的东西。

一、架构

mysql的架构，主要分为server层和引擎层，大体分布如下：

mysql架构

MySQL基架大致包括如下几大模块组件：

 （1）MySQL向外提供的交互接口（Connectors）

 （2）管理服务组件和工具组件(Management Service & Utilities)

 （3）连接池组件(Connection Pool)

 （4）SQL接口组件(SQL Interface)

 （5）查询分析器组件(Parser)

 （6）优化器组件（Optimizer）

 （7）缓存主件（Caches & Buffers）

 （8）插件式存储引擎（Pluggable Storage Engines）

 （9）物理文件（File System）

mysql架构

（一）MySQL向外提供的交互接口（Connectors）

Connectors组件，是MySQL向外提供的交互组件，如java,.net,php等语言可以通过该组件来操作SQL语句，实现与SQL的交互。

（二）管理服务组件和工具组件(Management Service & Utilities)

提供对MySQL的集成管理，如备份(Backup),恢复(Recovery),安全管理(Security)等

（三）连接池组件(Connection Pool)

负责监听对客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。

（四）SQL接口组件(SQL Interface)

接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户。

（五）查询分析器组件(Parser)

首先分析SQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理。

（六）优化器组件（Optimizer）

对SQL命令按照标准流程进行优化分析。

（七）缓存主件（Caches & Buffers）

缓存和缓冲组件

（八）MySQL存储引擎

（九）物理文件（File System）

实际存储MySQL 数据库文件和一些日志文件等的系统，如Linux，Unix,Windows等。

二、一个查询的主要流程

查询流程

1、连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接
在完成经典的TCP握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码
一个用户成功建立连接之后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在的连接的权限。修改完成之后，只有再新建的连接才会使用新的权限配置

2、查询缓存

拿到一个查询请求后，会先到查询缓存中查看
之前执行过的语句及其结果可能会以key-value的形式被直接缓存在内存中。key是查询的语句，value是结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会直接返回给客户端

3、分析器

如果没有命中查询缓存，就要开始真正执行语句
分析器会先做“词法分析”。你输入的是由多个字符串和空格组成的一条sql语句，mysql需要识别出里面的字符串分别是什么，代表什么，将整个sql语句打碎成一个个单词（token）
做完了这些识别之后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个sql语句是否满足sql语法，生成对应的抽象语法树AST，提供给优化器

4、优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引
或者在一个语句中有多表关联的时候，决定各个表的连接顺序

举个例子
这两种执行方法的逻辑结果是一样的，但是执行的效率会有所不同，而优化器的作用就是决定选择使用哪一个方案。

5、执行器

先判断一下你对这个表有没有执行查询的权限
有权限，就在打开表的时候，执行器会根据表的引擎定义，去使用这个引擎提供的接口
在这个例子中，sex字段没有索引，那么执行器的执行流程是这样的：
调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行；执行器将上述遍历过程所有满足条件的行组成的记录集作为结果集返回给客户端

三、行锁

InnoDB是支持行锁的，默认情况下是采用行级锁
MylSAM引擎不支持行锁
InnoDB实现了以下两种类型的行锁：
共享读锁（s）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
排他写锁（x）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。对于update、insert、delete语句，InnoDB会自动给涉及数据集加排他锁（x）

行锁的两阶段锁协议

在InnoDB事务中，行锁是需要的时候才加的，但并不是不需要了就立即释放，而是需要等到事务结束的时候才释放。这个就是两阶段锁协议
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

假如有一个类似信用卡取现的业务，需要用户a向银行b取现100元，这个业务涉及以下操作：
1.从用户a账户增加100元
2.从银行b账户减少100元
3.记录一条交易日志
个人账户一般情况下，只有一个人在使用，但是银行账户在a用户使用的同时有可能c用户、d用户同时向它取现，这些事务冲突的就是操作2，那么根据两阶段锁协议，就需要把操作2放在事务的最后执行，这就最大程度地减少了事务之间的锁等待，提升了并发度

InnoDB行锁是通过索引上的索引项加锁来实现的，InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
这里的行锁退化表锁是会锁上聚簇索引中的所有记录，并且会锁上聚簇索引中的所有间隙锁
只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由mysql通过判断不同执行计划的代价来决定的。如果mysql认为全局扫描效率更高，比如一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查slq的执行计划（可以通过explain检查sql的执行计划），以确认是否真正使用了索引
mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录，但是如果是使用相同的索引键，是会出现冲突的

四、表锁

先来看一段代码：

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE,t2 READ......;
[do something with tables t1 and t2 here]
COMMIT;
UNLOCK TABLES;

在用LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则mysql不会给表枷锁；
事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；
COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。

五、元数据锁MDL

在Mysql5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。不用显式加锁

六、InnoDB索引模型

mysql中索引分为主键索引和非主键索引
主键索引的叶子节点存的是整行数据。在InnoDB中，主键索引也被称为聚簇索引
非主键索引的叶子节点存放的内容是主键的值。在InnoDB中，非主键索引也被称为二级索引
两者查询的区别
如果语句是select * from T where ID = 500，即主键查询方式，则只需要搜索ID这棵B+树
如果语句是select * from T where k = 5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表
基于非主键索引的查询需要多扫描一棵索引树。

七、隔离级别

隔离级别

八、bin log

binlog是mysql的server层实现的，记录的是这个语句的原始逻辑
binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小会切换到下一个，并不会覆盖以前的日志

日志恢复

前提是数据定期做备份，保证musql中一段时间的binlog
当发生误操作进行数据恢复时，先找到最近一次全量备份，恢复到数据库
从备份的时间点开始，将备份的binlog依次取出来，重放到误删除表之前的那个时刻。

九、redo-log

先看一段sql语句：

UPDATE person SET person_name = 'heiwu' WHERE id = 5;

redo log是保存在引擎层的
如果每一次的更新操作都要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个IO成本、查找成本都很高
mysql使用wal技术来优化更新操作，wal的全程是Write-Ahead logging，它的关键点就是先写日志，再写磁盘。
当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log中去，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是系统比较空闲的时候做
InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这块文件总共就可以记录4GB的操作
有了redo log,InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称之为crash-safe。

十、redo-log vs binlog

还是上面的那个更新操作语句：

UPDATE person SET person_name = 'heiwu' WHERE id = 5;

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