数据库--语句执行流程

mysql架构

​ Mysql分为服务层和存储引擎两部分

​ 服务处包括 连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

​ 存储引擎层负责数据的存储和提取，支持 InnoDB、MyISAM、Memory 等引擎。不同引擎存取方式和支持功能不同，可通过sql语句中使用engine=引擎名来指定引擎，默认InnoDB

sql语句执行流程

step1：连接数据库

​ 通过输入mysql -h$ip -P$port -u$user -p来连接数据库

​ 这时服务层的连接器负责建立、维持和管理这个连接。连接成功后连接器会在权限表里查出用户的权限，此后请求都依赖此时读到的权限(也就是说即使权限修改也不会影响已建立的连接，只有新建的连接才会使用新的权限设置)，若连接长时间无响应自动断开，默认时长8小时，由参数wait_timeout设置

​ 通过show processlist可以查看mysql进程列表

长连接和短连接

​ 长连接指客户端持续有请求，一直使用同一个连接。

​ 短连接指每次执行几次查询就断开，下次查询需重新建立连接

​ 相比短连接，长连接效率高(因为建立连接比较复杂)，但是占用内存多( mysql使用的内存是管理在连接对象里的，在连接断开时才释放)，当占用太大时会被系统强行杀掉。解决办法：1.定时断开长连接 2. 执行一个比较大的操作后，执行mysql_reset_connection来重新初始化连接

step2：查询缓存

​ 之前查询结果会以key-value形式存在缓存中，目的是提高查询速率(缓存命中直接返回)

​ 一个表更新，表上所有查询缓存都被清空。对于更新频繁的数据库缓存命中非常低，只适用于不经常更新的表，例如系统配置表

​ 对此我们默认设为不使用缓存：query_cache_type=DEMAND，然后对不经常更新的表，采用类似select SQL_CACHE * from T语句来显式指定查询缓存

​ 在mysql8.0后彻底删除查询缓存模块

step3：词法分析

​ 会根据sql语言判断语法是否符合规则，即判断非法字符、不存在列或表等

step4：优化

​ 在表中有多个索引时，通过优化器来决定使用哪个索引；对于多表关联，通过优化器决定表的连接顺序

​ 例如select * form t1 join t2 using(id) where t1.a =1 and t2.b = 2;，通过优化器来决定是从t1表里取a=1再根据id关联t2表找b=2，还是从t2表里取b=2再根据id关联t1表找a=1，进而提高效率

step5：执行

1. 先判定用户是否有访问当前表的权限
2. 通过存储引擎提供的接口进行查询

step6：写入(仅限更新操作)

redo log

​ 因为每次更新写入磁盘效率低，可通过先更新内存并写入redo log，等系统空闲时根据日志写入磁盘的方式进行更新

​ redo log日志InnoDB引擎特有，用于记录某数据页做了什么修改，共4G大小

​ 优点是：

1. 保证数据库异常退出后提交记录不丢失
2. 避免每次更新写入磁盘，提高效率

binlog

​ binlog日志实现位于服务层，适用所有引擎，记录原始sql语句

​ binlog无内存限制，但无法保证数据库异常退出后提交记录不丢失

完整流程

对于InnoDB来说：

1. 读取数据对其修改
2. 调用存储引擎接口，写入修改后的数据(实质是把新数据交给存储引擎)
3. 引擎将新数据更新到内存，更新操作记录到redo log，此时redo log处于prepare状态
4. 将该sql语句记录到binlog
5. 执行器调用引擎的提交事务接口，把刚写入的redo log改为commit状态(实质就是上锁，保证redo log与redo log同步)

注： innodb_flush_log_at_trx_commit 设置为1时，每次事务的 redo log 都直接持久化到磁盘，保证 MySQL 异常重启后数据不丢失； ync_binlog 设置为1表示每次事务的 binlog 都持久化到磁盘，保证 MySQL 异常重启后 binlog 不丢失

数据回滚

​ 假如说想要回到昨天上午10点的状态：

1. 将昨天上午10点之前最近一次的全量备份恢复到临时库
2. 从备份时间开始，将之后的binlog取出并重放到昨天上午10点
3. 此时临时库就跟昨天上午10点一样了，可按需要进一步恢复到线上库