Mysql SQL底层执行原理

一、MySQL内部组件结构

1、Server层

1.1、连接器

当有客户端要向mysql发起通信必须先跟Server端建立通信连接，而建立连接的工作由连接器完成的

首先连到数据库，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接

mysql -h 127.0.0.1 -u root -p 123456 -P 3306

在完成 TCP 握手后，连接器就会开始进行认证用户名和密码，认证通过后，连接器会到权限表里查出你拥有的权限

当一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。用户的权限表在系统表空间的mysql的user表中。

// 创建新用户
// username：你将创建的用户名
// host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
// password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

// 授权
// privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
// databasename：数据库名
// tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
GRANT privileges ON databasename.tablename TO 'username'@'host'

//刷新数据库
flush privileges; 

//(设置用户名密码)
update user set password=password("123456") where user='root';

//查看当前用户的权限
show grants for root@"%"; 

客户端如果长时间不发送command到Server端，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

show global variables like "wait_timeout";
// 设置全局服务器关闭非交互连接之前等待活动的秒数
set global wait_timeout=28800; 

1.2、查询缓存

当发起一个SQL查询时，会先到查询缓存查看之前是否有执行过该语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

一般建议只对极少更新的表做查询缓存

1.3、词法分析器

对sql语句做解析。分析器先会做词法分析，语法分析，检查你的sql是否满足规则。大概是一下6个步骤:

1. 词法分析
2. 语法分析
3. 语义分析
4. 构造执行树
5. 生成执行计划
6. 计划的执行

1.4、优化器

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

1.5、执行器

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

2、Store层

存储引擎层负责数据的存储和提取。支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB。在我们创建表的时候会默认设置存储引擎为InnoDB

二、bin-log日志

binlog是Server层实现的二进制日志，会记录crud操作，有以下几个特点：

• Binlog在MySQL的Server层实现（引擎共用）
• Binlog为逻辑日志,记录的是一条语句的原始逻辑
• Binlog不限大小，追加写入，不会覆盖以前的日志

开启binlog日志，配置my.cnf文件

log-bin=/usr/local/mysql/data/binlog/mysql-bin
#注意5.7以及更高版本需要配置本项：server-id=123454（自定义,保证唯一性）;
#binlog格式，有3种statement,row,mixed
binlog-format=ROW
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync-binlog=1

binlog模式分三种（row，statement，mixed）

1、row

基于行的复制(row-based replication,RBR)

日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改

优点：在row模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了，所以row的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程和function，以及trigger的调用和出发无法被正确复制问题。
缺点：在row模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容

2、statement

基于SQL语句的复制(statement-based replication,SBR)

每一条会修改数据的 SQL 都会记录到 master 的 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。
优点：在statement模式下首先就是解决了row模式的缺点，不需要记录每一行数据的变化减少了binlog日志量，节省了I/O以及存储资源，提高性能。因为他只需要记录在master上所执行的语句的细节以及执行语句的上下文信息。
缺点：在statement模式下，由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于mysql现在发展比较快，很多的新功能不断的加入，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement中，目前已经发现不少情况会造成Mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row是基于每一行来记录的变化，所以不会出现，类似的问题。

3、mixed

混合模式复制(mixed-based replication,MBR)

在mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选择一种。新版本中的statement还是和以前一样，仅仅记录执行的语句。而新版本的MySQL中对row模式也做了优化，并不是所有的修改都会以row模式来记录，比如遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实是update或者delete等修改数据的语句，那么还是会记录所有行的变更

#查看bin-log是否开启
show variables like '%log_bin%';
#会多一个最新的bin-log日志
flush logs; 
#查看最后一个bin-log日志的相关信息
show master status; 
#清空所有的bin-log日志
reset master; 

数据归档操作

#恢复全部数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p xxx
#恢复指定位置数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="408" --stop-position="731"  /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p xxx
#恢复指定时间段数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --stop-date= "xxxx"  --start-date= "xxxx"|mysql -uroot -p xxx