mysql 架构
server (连接器,分析器,优化器,执行器)
引擎 提供数据存取的接口
mysql 执行流程
- 连接器 负责处理客户端的连接
默认为8小时,八小时内没有活动,会自动断开
show global variables like ‘wait_timeout’; //28800
优化方法:
尽量使用长连接,但是使用长连接会使内存暴涨,因为连接其实都是在内存中的
定期断开长连接。使用一段时间,或者程序里面判断执行过一个占用内存的大查询后, 断开连接,之后要查询再重连。
如果你用的是 MySQL 5.7 或更新版本,可以在每次执行一个比较大的操作后,通过执 行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权 限验证,但是会将连接恢复到刚刚创建完时的状态。
- 分析器 词法分析和语法分析
将sql语句进行解析,如果不识别报 You have an error in your SQL syntax” - 优化器
选择最佳执行方法,如选择使用那个索引 - 执行器
根据优化器的结果去存储引擎取数据
你会在数据库的慢查询日志中看到一个 rows_examined 的字段,表示这个语句执行过程 中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。
在有些场景下,执行器调用一次,在引擎内部则扫描了多行,因此引擎扫描行数跟 rows_examined 并不是完全相同的。
日志 (两阶段提交)
redo log 物理日志,引擎层
当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log(粉 板)里面,并更新内存,这个时候更新就算完成了。同时,InnoDB 引擎会在适当的时 候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做
InnoDB 的 redo log 是固定大小的,比如可以配置为一组 4 个文件,每个文 件的大小是 1GB,那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写,写到末 尾就又回到开头循环写,bin log 逻辑日志,server层
redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎 都可以使用。
redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日 志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1, 这样可以保证 MySQL 异常重启之后数据不丢失。
sync_binlog 这个参数设置成 1 的时候,表示每次事务的 binlog 都持久化到磁盘。这个 参数我也建议你设置成 1,这样可以保证 MySQL 异常重启之后 binlog 不丢失。
索引
索引模型
- 哈希表
key->value 因为是不断追加的,不能做范围查找,遇到hash碰撞后会用链表连接起来 - 有序数组
数据是有序存放的的,但添加成本太大,会重建整个数组
有序数组索引只适用于静态存储引擎,比如你要保存的是 2017 年某个城市的所有 人口信息,这类不会再修改的数据。 - 搜索树
二叉树效率最快,但树太高,所以mysql采用的是n叉树,InnoDB的n为1200左右
事务
特性:
ACID(Atomicity、Consistency、Isolation、Durability,即 原子性、一致性、隔离性、持久性)
隔离级别
- 读未提交(read uncommitted) 一个事务还没提交时,它做的变更就能被别的事务看到
别人改数据的事务尚未提交,我在我的事务中也能读到。 - 读提交(read committed) 一个事务提交之后,它做的变更才会被其他事务看到
别人改数据的事务已经提交,我在我的事务中才能读到。
oracle 默认隔离级别 - 可重复读(repeatable read) 一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。
别人改数据的事务已经提交,我在我的事务中也不去读。
mysql 的默认隔离级别 - 串行 化(serializable ) 对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行
我的事务尚未提交,别人就别想改数据
通过锁实现
查看mysql隔离级别
show variables like ‘transaction_isolation’;
特性:
ACID(Atomicity、Consistency、Isolation、Durability,即 原子性、一致性、隔离性、持久性)
隔离级别
- 读未提交(read uncommitted) 一个事务还没提交时,它做的变更就能被别的事务看到
别人改数据的事务尚未提交,我在我的事务中也能读到。- 读提交(read committed) 一个事务提交之后,它做的变更才会被其他事务看到
别人改数据的事务已经提交,我在我的事务中才能读到。
oracle 默认隔离级别- 可重复读(repeatable read) 一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。
别人改数据的事务已经提交,我在我的事务中也不去读。
mysql 的默认隔离级别- 串行 化(serializable ) 对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行
我的事务尚未提交,别人就别想改数据
通过锁实现查看mysql隔离级别
show variables like ‘transaction_isolation’;
锁
- 全局锁
- 锁库
全局锁即对整个数据库实例加锁,使得整个库处于只读状态,会阻塞DML和DDL语句。
使用如下命令(简称FTWRL)可为数据库加全局锁:
flush tables with read lock;
释放全局锁命令如下:
unlock tables;
此外,在客户端异常断开后,全局锁会自动释
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC 的支持, 这个过程中数据是可以正常更新的。
single-transaction 方法只适用于所有的表使用事务引擎的库
- 表级锁
表锁
元数据锁(meta data lock, MDL) 系统默认会加
MDL 会直到事务提交才释放,在做表结构变更的时候,你一定要小心不要导致锁住线上查 询和更新。
加锁 lock tables tb_name read/write;
解锁 unlock tables;
- 表锁用于用于为单个表加锁,表锁的类型分为读锁和写锁。
- 读锁
- 当前进程只能读,不能写,写会报错,不能操做其他表,
其他进程能读,写会被阻塞
- 当前进程只能读,不能写,写会报错,不能操做其他表,
- 写锁
- 当前进程可以进行读写操作。不能操作其他表
其他进程读写会被阻塞
- 当前进程可以进行读写操作。不能操作其他表
- 行级锁
视图
- 它是一个用查询语句定义的虚拟表,在调用的时候执行查询语句并生成结 果。创建视图的语法是 create view … ,而它的查询方法与表一样。
- InnoDB 在实现 MVCC 时用到的一致性读视图,即 consistent read view, 用于支持 RC(Read Committed,读提交)和 RR(Repeatable Read,可重复读)隔 离级别的实现。