小熊学Java个人网站:https://javaxiaobear.gitee.io/,每周持续更新干货,建议收藏!
当MySQL接收到客户端的查询SQL之后,仅仅只需要对其进行相应的权限验证之后,就会通过Query Cache来查找结果,甚至都不需要经过Optimizer模块进行执行计划的分析优化,更不需要发生任何存储引擎的交互 MySQL5.7支持内部缓存,8.0之后就废弃掉了
缓存的意义在于快速查询提升系统性能,可以灵活控制缓存的一致性 MySQL缓存的限制
应用层组织缓存,最简单的是使用redis,ehcached等
Connectors(客户端)
MySQL服务器之外的客户端程序,与具体的语言相关,例如Java中的JDBC,图形用户界面SQLyog等。本质上都是在TCP连接上通过MySQL协议和MySQL服务器进行通信。
MySQL Server(服务器)
第1层:连接层
第一件事就是建立 TCP 连接
。身份认证、权限获取
。,会收到一个
Access denied for user错误,客户端程序结束执行
用户名密码认证通过
,会从权限表查出账号拥有的权限
与连接关联,之后的权限判断逻辑,都将依赖于此时读到的权限第2层:服务层
Management Serveices & Utilities:系统管理和控制工具
SQL Interface:SQL接口:
接收用户的SQL命令,并且返回用户需要查询的结果。
比如SELECT ... FROM就是调用SQL InterfaceParser:解析器:
语法分析、语法解析
,并为其创建语法树
。语法分析
语法分析主要是把输入转化成若干个tokens,包含key和非key。
在分析之后,会得到4个Token,其中有2个key,它们分别是SELECT、FROM。
key | 非key | key | 非key |
---|---|---|---|
SELECT | age | FROM | user |
Optimizer:查询优化器:
确定SQL语句的执行路径,生成一个执行计划
。Caches & Buffers:查询缓存组件:
第3层:引擎层
插件式存储引擎层( Storage Engines),负责MySQL中数据的存储和提取,对物理服务器级别维护的底层数据执行操作,服务器通过API与存储引擎进行通信
。不同的存储引擎具有的功能不同,管理的表有不同的存储结构,采用的存取算法也不同,这样我们可以根据自己的实际需要进行选取。例如MyISAM引擎和InnoDB引擎。
存储层
所有的数据、数据库、表的定义、表的每一行的内容、索引,都是存在文件系统
上,以文件的方式存在,并完成与存储引擎的交互。
1.5、查询流程说明
首先, MySQL客户端通过协议与MySQL服务器建连接,通过SQL接口发送SQL语句,先检查查询缓存,如果命中,直接返回结果,否则进行语句解析。
也就是说,在解析查询之前,服务器会先访问查询缓存,如果某个查询结果已经位于缓存中,服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可,这将大大提高系统的性能。
接下来, MySQL解析器通过关键字将SQL语句进行解析,并生成一棵对应的解析树,
解析器使用MySQL语法规则验证和解析SQL语句。例如,它将验证是否使用了错误的关键字,或者使用关键字的顺序是否正确,引号能否前后匹配等;预处理器则根据MySQL规则进一步检查解析树是否合法,
例如,这里将检查数据表和数据列是否存在,还会解析名字和别名,看是否有歧义等。然后预处理器会进行查询重写,生成一棵新解析树。
接下来, 查询优化器将解析树转化成执行计划。
MySQL优化程序会对我们的语句做一些优化,如子查询转换为连接、表达式简化等等。优化的结果就是生成一个执行计划,这个执行计划表明了应该使用哪些索引执行查询,以及表之间的连接顺序是啥样,等等。我们可以使用EXPLAIN语句来查看某个语句的执行计划。
最后, 进入执行器阶段。
完成查询优化后,查询执行引擎
会按照生成的执行计划调用存储引擎提供的接口执行SQL查询并将结果返回给客户端。在MySQL8以下的版本,如果设置了查询缓存,这时会将查询结果进行缓存,再返回给客户端。
是在Parser:解析器 分析sql语法的时候检查的列。
在优化器阶段Optimizer:查询优化器:
查看MySQL提供什么存储引擎
SHOW ENGINES;
下面的结果表示MySQL中默认使用的存储引擎是InnoDB,支持事务,行锁,外键,支持分布式事务(XA),支持保存点(回滚)
也可以通过以下语句查看默认的存储引擎:
SHOW VARIABLES LIKE '%default_storage_engine%';
1. InnoDB存储引擎
处理大量的短期(short-lived)事务
。可以确保事务的完整提交(Commit)和回滚(Rollback)。应该优先考虑InnoDB引擎
。2. MyISAM存储引擎
MyISAM不支持事务和行级锁
,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。3. Archive引擎
Archive档案存储引擎只支持INSERT和SELECT操作
。4. Blackhole引擎
Blackhole引擎没有实现任何存储机制,它会丢弃所有插入的数据,不做任何保存
。5. CSV引擎
CSV引擎可以将普通的CSV文件作为MySQL的表来处理,但不支持索引
。6. Memory引擎
7. Federated引擎
Federated引擎是访问其他MySQL服务器的一个代理(跨库关联查询)
,尽管该引擎看起来提供了一种很好的跨服务器的灵活性,但也经常带来问题,因此默认是禁用的。https://dev.MySQL.com/doc/refman/5.7/en/innodb-architecture.html
下面是官方的InnoDB引擎结构图,主要分为内存结构和磁盘结构两大部分。
内存区域
Buffer Pool:在InnoDB访问表记录和索引时会在Buffer Pool的页中缓存,以后使用可以减少磁盘IO操作,提升效率。主要用来缓存热的数据页和索引页。
Log Buffer:用来缓存redolog
Adaptive Hash Index:自适应哈希索引
Change Buffer:它是一种应用在非唯一普通索引页(non-unique secondary index page)不在缓冲池中,对页进行了写操作,并不会立刻将磁盘页加载到缓冲池,而仅仅记录缓冲变更(Buffer Changes),等未来数据被读取时,再将数据合并(Merge)恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO,提升数据库性能。
磁盘区域
磁盘中的结构分为两大类:表空间和重做日志。
官方文档:
https://dev.MySQL.com/doc/refman/8.0/en/innodb-storage-engine.html
方法1:
设置默认存储引擎:
SET DEFAULT_STORAGE_ENGINE=MyISAM;
方法2:
或者修改 my.cnf 文件:vim /etc/my.cnf 新增一行:default-storage-engine=MyISAM 重启MySQL:systemctl restart MySQLd
方法3:
我们可以为 不同的表设置不同的存储引擎
CREATE TABLE 表名( 建表语句; ) ENGINE = 存储引擎名称;
ALTER TABLE 表名 ENGINE = 存储引擎名称;
开发存储引擎并不难,难的是开发出来高效的有意义的存储引擎。
简单例子可以看一下官方源码中的示例,可以实现一个什么也没做的存储引擎。
有兴趣可以参考官方文档:https://dev.MySQL.com/doc/dev/MySQL-server/latest/
对比项 | MyISAM | InnoDB |
---|---|---|
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 | 行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响 |
关注点 | 并发查询,节省资源、消耗少、简单业务 | 并发写、事务、多表关系、更大资源 |
默认安装 | Y | Y |
默认使用 | N | Y |
自带系统表使用 | Y | N |
除非几乎没有写操作全部都是高频的读操作可以选择MyISAM作为表的存储引擎,其他业务可以一律使用InnoDB。
事务:
事务都有 ACID 特性
1 、原子性 atomicity
过程的保证
只做一个步骤:给钱 ——> 去买 ——> 交回来
事务是数据库的逻辑工作单位,事务中包含的各操作要么都做,要么都不做
2 、一致性 consistency
结果的保证
保证要吃完 刚张嘴挂了,失去一致性
事 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时,就说数据库处于一致性状态。如果数据库系统 运行中发生故障,有些事务尚未完成就被迫中断,这些未完成事务对数据库所做的修改有一部分已写入物理数据库,这时数据库就处于一种不正确的状态,或者说是 不一致的状态。 3 、隔离性 isolation
并发事务互相干扰
不被干扰 刚张嘴别人塞了东西
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的,并发执行的各个事务之间不能互相干扰。 4 、持续性 永久性 durability
保存 吃到肚子里
也称永久性,指一个事务一旦提交,它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。
多个事务并发执行一定会产生相互争夺资源的问题
脏读(Dirty read) 是一个事务在处理过程中读取了另外一个事务未提交的数据 当一个事务正在访问数据并且对其进行了修改,但是还没提交事务,这时另外一个事务也访问了这个数据,然后使用了这个数据,因为这个数据的修改还没提交到数据库,所以另外一个事务读取的数据就是“脏数据”,这种行为就是“脏读”,依据“脏数据”所做的操作可能是会出现问题的。
修改丢失(Lost of modify) :是指一个事务读取一个数据时,另外一个数据也访问了该数据,那么在第一个事务修改了这个数据之后,第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失,这种情况就被称为* 修改丢失
不可重复读(Unrepeatableread) :指在一个事务内多次读取同一数据 ,在这个事务还没结束时,另外一个事务也访问了这个数据并对这个数据进行了修改 ,那么就可能造成第一个事务两次读取的数据不一致,这种情况就被称为 不可重复读。
幻读(Phantom read) 是指同一个事务内多次查询返回的结果集总数不一样(比如增加了或者减少了行记录)。 幻读与不可重复读类似,幻读是指一个事务读取了几行数据,这个事务还没结束,接着另外一个事务插入了一些数据,在随后的查询中,第一个事务读取到的数据就会比原本读取到的多,就好像发生了幻觉一样,所以称为幻读。
不可重复读 针对的是一份数据的修改 幻读 针对的是行数修改
避免事务并发问题是需要付出性能代价的,此时和分布式系统设计一样(CAP定理及base理论),为了保证一致性就一定会牺牲性能,要做取舍 在MySQL内部通过加锁的方式实现好了解决方案可供选择,就是配置事务隔离级别
事务隔离级别 脏读 不可重复读(被修改) 幻读(删减)
读未提交(read-uncommitted) 是 是 是
不可重复读(read-committed) 否 是 是
可重复读(repeatable-read) 否 否 是
串行化(serializable) 否 否 否
MySQL InnoDB
存储引擎默认的事务隔离级别是可重复读(REPEATABLE-READ)
MySQL 5.7 SELECT @@tx_isolation;
MySQL 8.0 SELECT @@transaction_isolation;
隔离级别越低,事务请求的锁越少相应性能也就越高,如没有特殊要求或有错误发生,使用默认的隔离级别即可,如果系统中有高频读写并且对一致性要求高那么就需要比较高的事务隔离级别甚至串行化。
提升事务级别的目的本质是提供更高的数据一致性,如果前置有缓存,那么缓存只能提供高效读并不能保证数据及时一致性,相反的我们还需要对缓存管理有额外的开销。
隔离的实现主要是读写锁和MVCC
锁定读
使用到了读写锁
读写锁是最简单直接的的事务隔离实现方式
锁机制,解决的就是多个事务同时更新数据,此时必须要有一个加锁的机制
下列操作属于锁定读
select ... lock in share mode
select ... for update
insert、update、delete
非锁定读
v10 -> age=18
v11 ->age=19
v12 ->age=15
使用mvcc 多版本控制实现
https://dev.MySQL.com/doc/refman/5.7/en/innodb-multi-versioning.html Multi-Version Concurrency Control 多版本并发控制,MVCC 是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问 InnoDB是一个多版本的存储引擎。它保存有关已更改行的旧版本的信息,以支持并发和回滚等事务特性。这些信息存储在一个称为回滚段的数据结构中的系统表空间或undo表空间中。参见第14.6.3.4节“撤消表空间”。InnoDB使用回滚段中的信息来执行事务回滚所需的撤消操作。它还使用这些信息构建行的早期版本,以实现一致的读取 MVCC 的实现依赖于:隐藏字段、Read View、undo log
隐藏字段
DB_TRX_ID
用来标识最近一次对本行记录做修改 (insert 、update) 的事务的标识符 ,即最后一次修改本行记录的事务 id。如果是 delete 操作, 在 InnoDB 存储引擎内部也属于一次 update 操作,即更新行中的一个特殊位 ,将行标识为己删除,并非真正删除。DB_ROLL_PTR
回滚指针,指向该行的 undo log 。如果该行未被更新,则为空.DB_ROW_ID
如果没有设置主键且该表没有唯一非空索引时,InnoDB
会使用该 id 来生成聚簇索引.Read View
不同的事务隔离级别中,当有事物在执行过程中修改了数据(更新版本号),在并发事务时需要判断一下版本链中的哪个版本是当前事务可见的。为此InnoDB有了ReadView的概念,使用ReadView来记录和隔离不同事务并发时此记录的哪些版本是对当前访问事物可见的。
undo log
除了用来回滚数据,还可以读取可见版本的数据。以此实现非锁定读
首先是通过锁和mvcc实现了执行过程中的一致性和原子性 其次是在灾备方面通过Redo log实现,Redo log会把事务在执行过程中对数据库所做的所有修改都记录下来,在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来。
使用Redo log保证了事务的持久性。当事务提交时,必须先将事务的所有日志写入日志文件进行持久化,就是我们常说的WAL(write ahead log)机制,如果出现断电重启便可以从redolog中恢复,如果redolog写入失败那么也就意味着修改失败整个事务也就直接回滚了。
表级锁:串行化(serializable)时,整表加锁,事务访问表数据时需要申请锁,虽然可分为读锁和写锁,但毕竟是锁住整张表,会导致并发能力下降,一般是做ddl处理时使用 行级锁:除了串行化(serializable)时 InnoDB使用的都是行级锁,只锁一行数据,其他行数据不影响,并发能力强。
行级锁实现比较复杂不是单纯锁住一行数据,是由mvcc完成的。
共享锁或S锁,其它事务可以继续加共享锁,但不能加排它锁
排它锁或X锁,在进行写操作之前要申请并获得,其它事务不能再获得任何锁。
它分为意向共享锁(IS)和意向排他锁(IX) 一个事务对一张表的某行添加共享锁前,必须获得对该表一个IS锁或者优先级更高的锁。一个事务对一张表的某行添加排他锁之前,它必须对该表获取一个IX锁。 意向锁属于表锁,它不与innodb中的行锁冲突,任意两个意向锁之间也不会产生冲突,但是会与表锁(S锁和X锁)产生冲突
表锁,行锁,间隙锁,Next-Key锁等 在Serializable中读加共享锁,写加排他锁,读写互斥 两段锁协议,将事务分成两个阶段,加锁阶段和解锁阶段(所以叫两段锁)
当前读 :在锁定读(使用锁隔离事物)的时候读到的是最新版本的数据 快照读:可重复读(repeatable-read)下 mvcc生效读取的是数据的快照,并不是最新版本的数据(未提交事物的数据)
https://dev.MySQL.com/doc/refman/8.0/en/xa.html
MySQL的xa事务分为两部分:
5.7 SHOW VARIABLES LIKE '%innodb_support_xa%';
8.0 默认开启无法关闭
XA 事务语法示例如下:
XA START '自定义事务id';
SQL语句...
XA END '自定义事务id';
XA PREPARE '自定义事务id';
XA COMMIT\ROLLBACK '自定义事务id';
XA PREPARE 执行成功后,事务信息将被持久化。即使会话终止甚至应用服务宕机,只要我们将【自定义事务id】记录下来,后续仍然可以使用它对事务进行 rollback 或者 commit。
xa事务可以跨库或跨服务器,属于分布式事务,同时xa事务还支撑了InnoDB内部日志两阶段记录 普通事务只能在单库中执行
两阶段提交协议与3阶段提交协议,额外增加了参与的角色保证分布式事务完成更完善
查询库存 = 100 0 扣减库存 = -1 99
记录日志 = log
提交 commit
select本身是一个查询语句,查询语句是不会产生冲突的一种行为,一般情况下是没有锁的,用select for update 会让select语句产生一个排它锁(X), 这个锁和update的效果一样,会使两个事务无法同时更新一条记录。
https://dev.MySQL.com/doc/refman/8.0/en/innodb-locks-set.html
https://dev.MySQL.com/doc/refman/8.0/en/select.html
示例
SELECT … FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED];
select * from t for update 会等待行锁释放之后,返回查询结果。
select * from t for update nowait 不等待行锁释放,提示锁冲突,不返回结果
select * from t for update wait 5 等待5秒,若行锁仍未释放,则提示锁冲突,不返回结果
select * from t for update skip locked 查询返回查询结果,但忽略有行锁的记录
事务 a
表 t id=100 更新 加行锁
表 t id=200 更新 已加锁
事务 b
表 t id=200 更新 加行锁
表 t id=100 更新 已加锁
排查:
show status like "innodb_row_lock%";
lnnodb_row_lock_current_waits:当前正在等待锁定的数量; lnnodb_row_lock_time :从系统启动到现在锁定的总时间长度,单位ms; Innodb_row_lock_time_avg :每次等待所花平均时间; Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间; lnnodb_row_lock_waits :从系统启动到现在总共等待的次数。
解决:
error log主要记录MySQL在启动、关闭或者运行过程中的错误信息,在MySQL的配置文件my.cnf中,可以通过log-error=/var/log/MySQLd.log 执行MySQL错误日志的位置。
0.1秒 Ø MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。 Ø long_query_time的默认值为10,意思是运行10秒以上的语句。 Ø 由他来查看哪些SQL超出了我们的最大忍耐时间值,比如一条sql执行超过5秒钟,我们就算慢SQL,希望能收集超过5秒的sql,结合之前explain进行全面分析。 Ø 默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数。 Ø 当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。 在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具MySQLdumpslow。
general log 记录了客户端连接信息以及执行的SQL语句信息,通过MySQL的命令
MySQL的bin log日志是用来记录MySQL中增删改时的记录日志。 当你的一条sql操作对数据库中的内容进行了更新,就会增加一条bin log日志。查询操作不会记录到bin log中。 bin log最大的用处就是进行主从复制,以及数据库的恢复。
redo log是一种基于磁盘的数据结构,用来在MySQL宕机情况下将不完整的事务执行数据纠正,redo日志记录事务执行后的状态。 当事务开始后,redo log就开始产生,并且随着事务的执行不断写入redo log file中。redo log file中记录了xxx页做了xx修改的信息,我们都知道数据库的更新操作会在内存中先执行,最后刷入磁盘。 redo log就是为了恢复更新了内存但是由于宕机等原因没有刷入磁盘中的那部分数据。
undo log主要用来回滚到某一个版本,是一种逻辑日志。 undo log记录的是修改之前的数据,比如:当delete一条记录时,undolog中会记录一条对应的insert记录,从而保证能恢复到数据修改之前。在执行事务回滚的时候,就可以通过undo log中的记录内容并以此进行回滚。 undo log还可以提供多版本并发控制下的读取(MVCC)。
每个日志都不一样,这个得分情况考虑
sync_binlog
参数控制着事务提交时binlog写入磁盘的策略 binlog 写入策略: 1、sync_binlog=0 的时候,表示每次提交事务binlog不会马上写入到磁盘,而是先写到page cache,相对于磁盘写入来说写page cache要快得多,不过在MySQL 崩溃的时候会有丢失日志的风险。 2、sync_binlog=1 的时候,表示每次提交事务都会执行 fsync 写入到磁盘 ; 3、sync_binlog的值大于1 的时候,表示每次提交事务都 先写到page cach,只有等到积累了N个事务之后才fsync 写入到磁盘,同样在此设置下MySQL 崩溃的时候会有丢失N个事务日志的风险。 很显然三种模式下,sync_binlog=1 是强一致的选择,选择0或者N的情况下在极端情况下就会有丢失日志的风险,具体选择什么模式还是得看系统对于一致性的要求。
事务执行过程中,InnoDB会先把redo log日志写到InnoDB的log buffer内存中。MySQL支持用户自定义在commit(这里的commit指的是sql中的commit,在具体的两阶段提交中对应的prepare阶段)时
将log buffer中的日志刷log file中的策略
,通过innodb_flush_log_at_trx_commit
参数设置
设置为0
:仅将日志写入log file buffer中。该模式下,在事务提交的时候,不会主动触发写入磁盘的操作,仅依靠InnoDB 的后台线程每秒执行一次刷盘操作,即每秒一次write cache和flush disk
。设置为1
:每次事务commit时MySQL都会把log buffer的数据立即写入log file的os cache中,并且立即flush刷到磁盘中去。即每次commit都write cache和flush disk,这是默认设置
。设置为2
:每次事务commit时MySQL都会把log buffer的数据写入log file的os cache 缓存,但是flush刷到磁盘的操作并不会同时进行,仅依靠InnoDB 的后台线程每秒执行一次真正的刷盘操作。即每次commit都write cache,每秒一次flush disk
。undo log 是一种用于撤销回退的日志。在事务没提交之前,MySQL 会先记录更新前的数据到 undo log 日志文件里面,当事务回滚时,可以利用 undo log 来进行回滚。 条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id:
版本链如下图:
undo log 两大作用:
时机顺序:
logbin格式:
粉丝福利,后台回复“MySQL面试题”获得本篇面试题资料
如果觉得内容不错的话,希望大家可以帮忙点赞转发一波,这是对我最大的鼓励,感谢🙏🏻
END