文章目录
- mysql架构
- sql语句执行流程
- step1:连接数据库
- 长连接和短连接
- step2:查询缓存
- step3:词法分析
- step4:优化
- step5:执行
- step6:写入(仅限更新操作)
- redo log
- binlog
- 完整流程
- 数据回滚
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:执行
- 先判定用户是否有访问当前表的权限
- 通过存储引擎提供的接口进行查询
step6:写入(仅限更新操作)
redo log
因为每次更新写入磁盘效率低,可通过先更新内存并写入redo log,等系统空闲时根据日志写入磁盘的方式进行更新
redo log日志InnoDB引擎特有,用于记录某数据页做了什么修改,共4G大小
优点是:
- 保证数据库异常退出后提交记录不丢失
- 避免每次更新写入磁盘,提高效率
binlog
binlog日志实现位于服务层,适用所有引擎,记录原始sql语句
binlog无内存限制,但无法保证数据库异常退出后提交记录不丢失
完整流程
对于InnoDB来说:
- 读取数据对其修改
- 调用存储引擎接口,写入修改后的数据(实质是把新数据交给存储引擎)
- 引擎将新数据更新到内存,更新操作记录到redo log,此时redo log处于prepare状态
- 将该sql语句记录到binlog
- 执行器调用引擎的提交事务接口,把刚写入的redo log改为commit状态(实质就是上锁,保证redo log与redo log同步)
注: innodb_flush_log_at_trx_commit 设置为1时,每次事务的 redo log 都直接持久化到磁盘,保证 MySQL 异常重启后数据不丢失; ync_binlog 设置为1表示每次事务的 binlog 都持久化到磁盘,保证 MySQL 异常重启后 binlog 不丢失
数据回滚
假如说想要回到昨天上午10点的状态:
- 将昨天上午10点之前最近一次的全量备份恢复到临时库
- 从备份时间开始,将之后的binlog取出并重放到昨天上午10点
- 此时临时库就跟昨天上午10点一样了,可按需要进一步恢复到线上库