大体来说,MySQL可以分为 Server层 和 存储引擎层 两部分。
核心服务功能
,以及所有的内置函数(如日期、时间、数学和加密函数等)跨存储引擎
的功能都在这一层实现,比如存储过程、触发器、视图等。数据的存储和提取
。不同的存储引擎共用一个Server层
,也就是从连接器到执行器的部分。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的:
# 完整命令
mysql -h 127.0.0.1 -P 3306 -u root -p
# 本机登录(省略 -h ip -P port)
mysql -u root -p
密码也可以直接跟在-p
后面写在命令行中,但这样可能会导致你的密码泄露,所以不建议。
连接命令中的mysql是客户端工具,用来跟服务端建立连接。
连接服务包含本地socket
通信和基于客户端/服务端工具实现的类似TCP/IP
的通信。主要完成一些类似于连接处理、授权认证及相关的安全方案。
该层引入了线程池
的概念,为通过安全认证接入的客户端提供线程。同样在该层上可以实现基于SSL
的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
在完成TCP握手后,连接器开始进行认证:
这就意味着,一个用户成功建立连接后,即使修改了权限,也不会影响已经存在的连接,只有重新连接才会生效。
连接完成后,如果你没有后续的动作,这个连接就处于空闲状态(
Command
列显示为Sleep
)
# 查看连接列表
show processlist;
客户端如果太长时间没动静,连接器就会自动将它断开。这个时间由参数
wait_timeout
控制,默认值是8
小时
建立连接的过程通常是比较复杂的,因此在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。
全部使用长连接后,你可能会发现,有些时候MySQL占用内存涨得特别快
MySQL在执行过程中,临时使用的内存是管理在连接对象中的,这些资源在连接断开的时候才会释放。所以如果长连接累积下来,可能导致内存占用太大,被系统强行杀掉(OOM),从现象看就是MySQL异常重启了。
怎么解决这个问题呢?可以考虑以下两种方案。
执行过的语句及其结果可能会以key-value
的形式,被直接缓存在内存中。key是查询语句,value是查询结果。
select * from T where ID=10;
MySQL拿到一个查询请求后,会先查询缓存,如果查询命中缓存就直接返回结果,否则继续执行后面的操作。
查询缓存的失效
非常频繁,只要有对一个表的更新
,这个表上所有的查询缓存
都会被清空
。因此:
可以将参数 query_cache_type 设置成 DEMAND,默认不查询缓存。也可以通过查询的时候手动指定,如下:
# 查询缓存
select SQL_CACHE * from T where ID=10;
# 不查询缓存
select SQL_NO_CACHE * from T where ID=10;
注意:MySQL 8.0版本直接将查询缓存的整块功能删掉了,也就是说8.0开始彻底没有这个功能了。
如果没有命中缓存,就开始真正执行语句了。首先,MySQL需要知道你要做什么,因此需要对SQL语句做解析。
经过了分析器,MySQL就知道你要做什么了。在开始执行之前,还要先经过优化器的处理。
优化器阶段完成后,SQL语句的执行方案就确定下来了。
进入执行器阶段,开始操作引擎,执行语句:
对于下面的语句,分析执行器的执行流程:
select * from T where ID=10;
对于有索引的表,执行的逻辑也差不多:
在数据库的慢查询日志中有一个rows_examined
的字段
注意:rows_examined ≠ 引擎扫描行数(某些场景下,执行器调用一次,在引擎内部会扫描多行)
存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。
MySQL中的数据用各种不同的技术存储在文件或内存中,包括存储方式、索引技巧、锁定水平等不同功能。这些不同的技术以及配套的功能被称作存储引擎。
Oracle,SqlServer等数据库只有一种存储引擎。MySQL提供了插件式
的存储引擎架构(多种存储引擎)。 所以MySQL可以根据需要使用相应引擎,或者编写存储引擎。
MySQL 常用的存储引擎有三种: InnoDB 、MyISAM 、MEMORY。创建表时如果不指定存储引擎,系统就会使用默认的存储引擎, MySQL5.5之前 默认是MyISAM,MySQL5.5之后就改为了InnoDB。
特点 | InnoDB | MyISAM | MEMORY |
---|---|---|---|
存储限制 | 64TB | 有 | 有 |
事务安全 | 支持 | ||
锁机制 | 行锁,表锁 | 表锁 | 表锁 |
外键 | 支持 | ||
BTree索引 | 支持 | 支持 | 支持 |
Hash索引 | 支持 | ||
全文索引 | 支持(5.6版本后) | 支持 | |
集群索引 | 支持 | ||
数据索引 | 支持 | 支持 | |
索引缓存 | 支持 | 支持 | 支持 |
数据可压缩 | 支持 | ||
空间使用 | 高 | 低 | N/A |
内存使用 | 高 | 低 | 中等 |
批量插入速度 | 低 | 高 | 高 |
-- 查看数据库支持的存储引擎
show engines;
-- 查询某个数据表的存储引擎
show table status from 数据库名 where name = '数据表名';
-- 创建表时指定存储引擎
create table 表名(
列名 数据类型 约束,
...
) engine = 存储引擎名;
-- 修改数据表的存储引擎
alter table 表名 engine = 存储引擎名;
-- 设置默认存储引擎
set default_storage_engine = <存储引擎名>
# InnoDB(MySQL5.5之后默认的存储引擎)
1. 优点:
1)InnoDB 提供了具有提交、回滚、崩溃恢复能力的事务安全。
2)InnoDB 采用行锁,适合处理并发写操作。
2. 缺点:
会占用更多的磁盘空间以保留数据和索引
3. 场景:
1)对事务的完整性和安全性有比较高的要求。
2)在并发条件下要求数据的一致性,写多读少。
# MyISAM(MySQL5.5之前默认的存储引擎)
1. 优点:
1)MyISAM 内存要求低,节约资源。
2)MyISAM 采用表锁,读取速度快,查询效率高
2. 缺点:
1)不支持事务和外键操作
2)不适合并发写操作
3. 场景:
1)对事务的完整性、并发性要求不是很高。
2)以查询操作为主,只有很少的更新和删除操作,读多写少
# MEMORY
1. 特点:
将所有数据保存在内存中,在需要快速定位记录和其他类似数据环境下,可以提供更快的访问。
2. 使用场景:
通常用于更新不太频繁的小表,用以快速得到访问结果。
一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块,最后到达存储引擎。
一条更新语句的执行过程也差不多,如:update T set c=c+1 where ID=2;
与查询流程不一样的是,更新流程还涉及两个重要的日志模块:redo log(重做日志)和 binlog(归档日志)
在MySQL中,如果每一次的更新操作都直接写进磁盘,那么整个过程IO成本、查找成本都很高。
为了解决这个问题,InnoDB引擎使用WAL技术,全称是Write-Ahead Logging,关键点就是先写日志,再写磁盘
。
InnoDB引擎的 redo log 是固定大小的,从头开始写,写到末尾就又回到开头循环写:
有了redo log,InnoDB就可以保证即使数据库发生异常重启,之前提交的记录也不会丢失(crash-safe)
MySQL整体来看,其实就有两块:
redo log 是 InnoDB引擎 特有的日志,而Server层也有自己的日志,称为binlog(归档日志)。
binlog会记录所有的逻辑操作,并且是采用 追加写
的形式。
比如某天下午两点发现中午十二点有一次误删表,需要找回数据,那可以这么做:
这样临时库就跟误删之前的线上库一样了,然后可以把表数据从临时库取出来,按需要恢复到线上库去。
虽然 binlog 与 redo log 都属于持久化的保证,但是侧重点不同。
redo log 是 InnoDB存储引擎特有
的(MySQL自带的引擎是MyISAM,不支持,而InnoDB是以插件形式引入MySQL的)
binlog 是 MySQL的 Server层 实现的,所有存储引擎都可以使用
。
redo log 让 InnoDB存储引擎 拥有了崩溃恢复能力
(crash-safe)
binlog 只能用于归档,保证了MySQL集群架构的数据一致性
。
redo log 是 物理日志
,记录的是 “在某个数据页上做了什么修改”;
binlog 是 逻辑日志
,记录的是 “SQL语句的原始逻辑”,比如 “给ID=2这一行的c字段加1 ”。
redo log 在事务执行过程中会不断写入
,
binlog 只有在 事务提交时才会写入
。
redo log 是循环写入
的,空间固定会用完,需要 持久化并擦除
之前的记录来保存新的记录;
binlog 是追加写入
的,binlog文件写到一定大小后会切换
到下一个,并不会覆盖以前的日志。
update T set c=c+1 where ID=2;
执行器和InnoDB引擎在执行这个简单的update语句时的内部流程:
将 redo log 的写入拆成了两个步骤:prepare 和 commit,这就是"两阶段提交"。
prepare状态
,表示事务还未提交。commit状态
,表示事务已经提交。这里的commit步骤
和事务的commit语句
不一样(commit语句
执行的时候,会包含 commit步骤
)
MySQL 的两阶段提交是为了保证同时使用 redo log 和 binlog 的情况下,数据的一致性。
两阶段提交将一个事务分成 prepare 阶段和 commit 阶段,在 prepare 阶段写 redo log 和 binlog,在 commit 阶段修改 redo log 的状态并落盘 binlog。这样可以避免数据库发生异常重启或者主从切换时出现数据不一致的情况。
通过这样的流程,MySQL可以保证在任何时刻,redo log 和 bin log都是逻辑上一致的。如果MySQL发生崩溃,可以根据 redo log 来恢复数据页的状态,也可以根据bin log恢复SQL语句的执行。
以下通过反证法
说明:
因此,如果不使用“两阶段提交”,那么用日志恢复出来的数据和原库的数据就有可能不一致。
commit状态
,所以崩溃恢复的时候,这个事务会回滚。commit状态
,说明事务是完整的,则直接提交;prepare状态
,则判断对应的事务binlog是否存在并完整:
一个事务的binlog是有完整格式的:
COMMIT
;XID event
。另外,在MySQL 5.6.2版本以后,还引入了 binlog-checksum 参数,用来验证binlog内容的正确性。
redo log 和 binlog 有一个共同的数据字段,叫XID。崩溃恢复的时候,会按顺序扫描redo log:
不引入两个日志,只用binlog来支持崩溃恢复,又能支持归档,是否可行?
答案是不行。binlog只能支持归档,无法支持崩溃恢复。( binlog没有能力恢复“数据页” )
假设在图中标的位置,也就是binlog2写完了,但是整个事务还没有commit的时候,MySQL发生了crash。
对于事务2来说,重启后,可以应用binlog补回来。
对于事务1来说,系统已经认为提交完成了,不会再应用一次binlog。
因为 InnoDB引擎 使用的是WAL技术,写完内存和日志,事务就算完成了。如果之后崩溃,要依赖于日志来恢复数据页。
也就是说上图这种情况,事务1可能会发生数据页级的丢失。此时,binlog里面并没有记录数据页的更新细节,是补不回来的。
如果只从崩溃恢复的角度来讲是可以的,但是binlog有着redo log无法替代的功能:
总之,由于现在包括MySQL高可用在内的很多系统机制都依赖于binlog,所以redo log还做不到 “鸠占鹊巢”。
实际上,redo log并没有记录数据页的完整数据,所以它并没有能力自己去更新磁盘数据页
内存中的数据页被修改以后,跟磁盘的数据页不一致,称为脏页。
最终数据落盘,就是把内存中的数据页写盘。这个过程,甚至与redo log毫无关系。
在崩溃恢复场景中,InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新,
首先会将它读到内存
,然后让redo log更新内存内容
。更新完成后,内存页变成脏页,就回到步骤1,重新落盘
。
# 这个事务要往两个表中插入记录
start transaction;
insert into t1 ...
insert into t2 ...
commit;
每一个插入操作都要记录redo log日志,但又不能在还没commit的时候就直接写到redo log文件里。
所以,redo log buffer就是一块内存,一个用来先存redo log的缓冲区。
单独执行一个更新语句时,会自动开启和提交事务,过程和上面一样,只不过是“压缩”到了一个语句里面完成。
事务执行过程中不会“主动去刷盘”,以减少不必要的IO消耗。但是可能会出现“被动写入磁盘”,比如内存不够、其他事务提交等情况。
select distinct 字段/聚合函数
from 表1 别名
inner join 表2 别名
on 连接条件
where 筛选(聚合之前)
group by 分组列表 -- 这里开始可以使用 select 中的别名
having 筛选(聚合之后)
order by 排序列表
limit 起始条目索引,条目数;
1. from
对from子句中的前两个表计算笛卡尔积,产生虚拟表VT1。(选择相对小的表做基础表)
2. on
对虚拟表VT1进行on条件过滤,筛选出满足 <on-condition> 的行,产生虚拟表VT2。
3. join
如果指定了外连接,表中未匹配的行就会作为外部行添加到虚拟表VT2中,产生虚拟表VT3。
left join :将左表在第二步中过滤掉的行添加进来。
right join :将右表在第二步中过滤掉的行添加进来。
4. where
对虚拟表VT3进行where条件过滤。符合 <where-condition> 的记录插入到虚拟表VT4中。
5. group by
根据 group by 子句中的列,对虚拟表VT4中的记录进行分组操作,产生虚拟表VT5。
6. having
对虚拟表VT5进行having过滤,符合 <having-condition> 的记录插入到虚拟表VT6中。
7. select
筛选出虚拟表VT6中select指定的列,插入到虚拟表VT7中。
8. distinct
移除虚拟表VT7中相同的行,产生虚拟表VT8。
9. order by
将虚拟表VT8中的记录按照 <order by list> 进行排序操作,返回游标。
10. limit
取出指定行的记录,产生虚拟表VT9, 并作为结果集返回。
聚合函数
是在 group by之后
,having之前
执行的。having
筛选器 是 第一个
也是 唯一一个
应用到 已分组数据
的筛选器。order by
返回的不是虚拟表,而是游标
(包含特定的物理顺序的逻辑组织的对象)两张表在 join 时,首先会计算笛卡尔积
-- 使用inner join时(结果没有区别)
select * from A inner join B on condition1 and condition2
select * from A inner join B on condition1 where condition2
-- 使用left join时(condition1只对B生效,condition2对A和B都生效)
select * from A left join B on condition1 where condition2
-- 使用right join时(condition1只对A生效,condition2对A和B都生效)
select * from A right join B on condition1 where condition2
on的condition过滤的行还可以在第三步outer join时再次添加回来,而where的过滤就是最终的。
对于外连接,以左外连接
a left join b
为例
驱动表
,b表作为被驱动表
(也可能会被优化为内连接,那就不一定是a为驱动表了)被驱动表
添加索引Using join buffer
的优化
Using join buffer (Block Nested Loop)
Using join buffer (hash join)
对于内连接,例如
a inner join b
驱动表
,大表作为被驱动表
(小表驱动大表)驱动表
,有索引的表作为被驱动表
(有索引的被驱动)Tips:Explain的结果中,上边一行为驱动表,下边一行为被驱动表
从表A取出一条数据,遍历表B,将匹配到的数据放到result(驱动表A中的每一条记录都与被驱动表B的所有记录进行匹配)
这种方式的效率是非常低的,假设A表数据100,B表数据1000条,则 join比较次数 为 A*B=10w
次
lndex Nested-Loop Join 优化的思路主要是为了减少内层表数据的匹配次数,所以要求被驱动表上必须有索引才行。
从表A取出一条数据,通过索引匹配表B中的数据(避免和内层表的每条记录进行比较,极大的减少了和内层表的匹配次数)
驱动表中的每条记录通过被驱动表的索引进行访问,因为索引查询的成本比较固定,故mysql优化器都倾向使用记录数少的作为驱动表
如果被驱动表加的索引不是主键索引,还得进行一次回表查询。因此,如果被驱动表的索引是主键索引,效率会更高。
如果存在索引,那么会使用index的方式进行join,如果join的列没有索引,则每次都要从驱动表中加载一条记录,然后把被驱动表的记录加载到内存与其匹配,匹配结束后清除内存,继续匹配。这样周而复始,大大增加了IO的次数。
Block Nested Loop Join 优化的思路是减少IO的次数,引入 join buffer缓冲区,不再逐条获取驱动表的数据,而是一块一块的获取。
将驱动表join相关的部分数据(大小受join buffer的限制)缓存到 join buffer中,然后全表扫描被驱动表,一次性和 join buffer中的所有驱动表记录进行匹配(内存中操作),将 Simple Nested Loop Join 中的多次比较合并成一次,降低了被驱动表的访问频率。
注意:这里缓存的不只是关联表的列,select后面的列也会放到 join buffer 中,因此查询的时候尽量减少不必要的字段。
Block Nested Loop Join 相关信息的查看:
block_nested_loop
通过 show variables like %optimizer_switch%
查看 block_nested_loop 状态。(默认开启)
join_buffer_size
驱动表批量获取的数量取决于 join buffer 的大小,可以通过 show variables like %join_buffer%
查看(默认256k)
从MySQL的8.0.20版本开始将废弃BNLJ,因为从MySQL8.0.18版本开始就加入了hash join,默认都会使用hash join
Nested Loop:
对于被连接的数据子集较小的情况下,Nested Loop是个较好的选择。
Hash Join
Hash Join是做大数据集连接时的常用方式。优化器使用两个表中相对较小的表,利用Join Key在内存中建立散列值,然后扫描相对
较大的表,并探测散列值,找出与Hash表匹配的行。
Hash Join 的特点:
被驱动表
的关联字段创建索引(减少内层表的循环匹配次数)group by:
1、被分组字段有几类,最终结果集中就有几条(每一类只会显示首条结果)
-- 如sex有男,女两类,结果集就只有2条
2、分组查询中,select之后跟【与分组字段一对一关系的字段】和【聚合函数】
-- 因为只会取首条结果,如果不是一对一的关系,没有意义
3、一般和聚合函数一起使用:
-- 无分组,聚合函数将表中所有符合条件的数据当成一组。
-- 有分组,聚合在分组之后执行,对每一组数据进行聚合
4、分组之后就不需要再去重了:
-- 分组的时候是将列中唯一的值分成一组,这意味着所有的记录都是不同的。
无论如何排序都是一个成本很高的操作,所以从性能角度考虑,应尽可能避免排序
或者尽可能避免对大量数据进行排序
。
select city, name, age from t where city='杭州' order by name limit 1000;
当不能使用索引生成排序结果的时候,MySQL需要自己进行排序:
数据量小
则在内存中
进行,如果数据量大
则需要使用磁盘
。MySQL将这个过程统一称为文件排序
(Using Filesort ),即使完全是内存排序不需要任何磁盘文件时也是如此。
先读取查询所需要的所有列,然后再根据指定列进行排序,最后直接返回排序结果。(MySQL 4.1才引入)
单路排序的流程
city索引树
找到所有符合 where条件
的 主键id
。主键索引树
获取 name、city、age三个字段的值,存入sort_buffer中;(回表查询)排序字段name
做快速排序;读取
行指针
和需要排序的字段
,对其进行排序,然后再根据排序结果读取所需要的数据行。
双路排序的流程
city索引树
找到所有符合 where条件
的 主键id
。主键索引树
获取 name、id这两个字段的值,存入sort_buffer中;(回表查询)主键id
的值,到原表中取出city、name和age三个 要查询的字段 返回给客户端。(回表查询)两种算法的概念
单路排序
:从磁盘读取 需要排序的字段 和 需要查询的字段 ,放到sort_buffer中,这样排序后可以从内存中直接返回
查询结果,不用再回到原表去取数据。(内存换IO)双路排序
:从磁盘读取 需要排序的字段 和 主键id,放到sort_buffer中,根据排序字段进行排序,然后扫描已经排序好的列表,按照列表中的 主键id
重新到 主键索引树
回表查询
需要查询的数据。两种算法的比较
单路排序
:只要读取一次磁盘,顺序IO;但是读取字段多,占用的 sort_buffer 更多,发生外部排序
的概率更大。双路排序
:需要读取两次磁盘,第二次会产生大量随机IO;但是只需要读取少量字段,发生外部排序
的概率更小。两种算法的选择
MySQL选择算法的依据是内存是否足够,即 单行的长度(要查询的所有列的总长度)是否超过 max_length_for_sort_data
如果内存足够大,会优先选择单路排序,这样排序后可以从内存中直接返回
查询结果,不用再回到原表去取数据。
如果内存不够,会影响排序效率,会改用双路排序,这样排序过程中一次可以排序更多行
,但是需要再回到原表去取数据。
这也就体现了MySQL的一个设计思想:如果内存够,就要多利用内存,尽量减少磁盘访问。
注意:MySQL在排序时,对每一个排序记录都会分配一个足够长的定长空间来存放(根据数据库定义的字段结构大小)
因此,排序消耗的临时空间可能要比磁盘上的原表要大很多。
提高 sort_buffer_size
两种算法的数据都有可能超出sort_buffer_size的容量,从而创建tmp文件进行合并排序,导致多次I/O。
提高 max_length_for_sort_data
降低 MySQL 改用双路排序
的概率。
*order by 时不要使用 select ,只Query需要的字段
减少每次写入 sort_buffer 的数据大小,降低 MySQL 改用双路排序
的概率。
MySQL会给每个线程
分配一块内存用于排序,称为 sort_buffer
。
FireSort 可能在内存中完成,也可能需要使用外部排序,这取决于参数 sort_buffer_size
和 排序所需的内存
。
磁盘临时文件
辅助排序。(归并排序)
提高 sort_buffer_size
会降低外部排序的概率,减少磁盘临时文件的生成,减少磁盘I/O。
set sort_buffer_size = 524288;
如果 要查询的所有列的总长度
超过了 max_length_for_sort_data的大小
,MySQL就会使用双路排序
。
提高 max_length_for_sort_data
会降低改用双路排序
的概率。
set max_length_for_sort_data = 8192;
MySQL之所以需要生成临时表,并且在临时表上做排序操作,**原因是原来的数据都是无序的。**如果能够保证从索引树取出的行,天然就是有序的话,就可以不用再排序了。
而 InnoDB的索引树 默认就是按照索引字段递增排序的,因此只要对排序字段加上索引,就可以不用再排序了。
# 创建一个city和name的联合索引
alter table t add index city_user(city, name);
这样就保证了,取“下一条记录”的遍历过程中,只要city的值是杭州,name的值就一定是有序的。
select city, name, age from t where city='杭州' order by name limit 1000;
索引查询的过程(有序)
city='杭州'
条件的主键id;主键索引树
获取整行,取name、city、age三个字段的值,作为结果集的一部分直接返回;city='杭州'
条件时循环结束。# 添加覆盖索引进一步简化流程(无需回表查询)
alter table t add index city_user_age(city, name, age);
覆盖索引查询的过程(有序且无需回表)
从索引 (city,name,age) 找到第一个满足 city='杭州'
条件的记录,
取出其中的name、city、age三个字段的值,作为结果集的一部分直接返回;
从索引 (city,name,age) 取下一个记录,同样取出这三个字段的值,作为结果集的一部分直接返回;
重复执行步骤2,直到查到第1000条记录,或者是不满足 city='杭州'
条件时循环结束。
在关联查询的时候,如果需要排序,MySQL会分两种情况来处理这样的文件排序。
Using filesort
。Using temporary; Using filesort
。如果查询中有LIMIT 的话,LIMIT 也会在排序之后应用,所以即使需要返回较少的数据,临时表和需要排序的数据量仍然会非常大。
MySQL 5.6 在这里做了很多重要的改进。当只需要返回部分排序结果时,例如使用了LIMIT 子句,MySQL不再对所有的结果进行排序,而是根据实际情况,选择抛弃不满足条件的结果,然后再进行排序。
参考文章:https://blog.51cto.com/wujianwei/2534400?source=drt
有以下子查询示例:
SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
你肯定认为这个 SQL 会这样执行:
select t2.b from t2 where id < 10;
-- 结果:1,2,3,4,5,6,7,8,9
select * from t1 where t1.a in(1,2,3,4,5,6,7,8,9);
但实际上 MySQL 并不是这样做的。MySQL 会将相关的外层表压到子查询中,优化器认为这样效率更高。
也就是说,优化器会将上面的 SQL 改写成这样(针对mysql5.5以及之前的版本):
select * from t1 where exists(select b from t2 where id < 10 and t1.a=t2.b);
执行计划为:
+----+--------------------+-------+-------+---------+------+----------+-------------+
| id | select_type | table | type | key | rows | filtered | Extra |
+----+--------------------+-------+-------+---------+------+----------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | 100 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | range | PRIMARY | 9 | 10.00 | Using where |
+----+--------------------+-------+-------+---------+------+----------+-------------+
不相关子查询 变成了 相关子查询(select_type:DEPENDENT SUBQUERY),子查询需要根据 b 来关联外表 t1,因为需要外表的 t1 字段,所以子查询是没法先执行的。执行流程为:
总的扫描行数为 100+100*9=1000(这是理论值,实际值为 964,看规律是子查询结果集每多一行,总扫描行数就会少几行)。
这样会有个问题,如果外层表是一个非常大的表,对于外层查询的每一行,子查询都得执行一次,这个查询的性能会非常差。我们很容易想到将其改写成 join 来提升效率:
select t1.* from t1 inner join t2 on t1.a=t2.b and t2.id<10;
这样优化可以让 t2 表做驱动表,t1表关联字段有索引,查找效率非常高。
但这里会有个问题,join 是有可能得到重复结果的,而 in (select …) 子查询语义则不会得到重复值。而 semi join 正是解决重复值问题的一种特殊联接。
在子查询中,优化器可以识别出 in 子句中每组只需要返回一个值,在这种情况下,可以使用 semi join 来优化子查询,提升查询效率。
这是 MySQL5.6 加入的新特性,MySQL5.6 以前优化器只有 exists 一种策略来“优化”子查询。
经过 semijoin 优化后的SQL和执行计划分为:
# 注意这是优化器改写的SQL,客户端上是不能用 semi join 语法的
select
`t1`.`id`,`t1`.`a`,`t1`.`b`
from `t1` semi join `t2`
where
( (`t1`.`a` = `<subquery2>`.`b`) and (`t2`.`id` < 10) );
# 执行计划
+----+--------------+-------------+-------+---------+---------------+------+-------------+
| id | select_type | table | type | key | ref | rows | Extra |
+----+--------------+-------------+-------+---------+---------------+------+-------------+
| 1 | SIMPLE | <subquery2> | ALL | NULL | NULL | NULL | Using where |
| 1 | SIMPLE | t1 | ref | a | <subquery2>.b | 1 | NULL |
| 2 | MATERIALIZED | t2 | range | PRIMARY | NULL | 9 | Using where |
+----+--------------+-------------+-------+---------+---------------+------+-------------+
semijoin 优化实现比较复杂,其中又分FirstMatch、Materialize 等策略,上面的执行计划中 select_type=MATERIALIZED 就是代表使用了 Materialize 策略来实现的 semijoin。这里 semijoin 优化后的执行流程为:
这样一来,子查询结果有9行,即临时表也有9行(这里没有重复值),总的扫描行数为 9+9+9*1=27 行,比原来的 1000 行少了很多。
MySQL5.6版本中加入的另一种优化特性 materialization,就是把子查询结果物化成临时表,然后代入到外查询中进行查找。内存临时表包含主键(hash 索引),消除重复行,使表更小。如果子查询结果太大,超过 tmp_table_size 大小,会退化成磁盘临时表。
这跟前面提到的“我们误以为的”过程相似,这样子查询只需要执行一次,而不是对于外层查询的每一行都得执行一遍。不过要注意的是,这样外查询依旧无法通过索引快速查找到符合条件的数据,只能通过全表扫描或者全索引扫描,
经过 materialization 优化后的执行计划为:
+----+-------------+-------+-------+---------+------+------+-------------+
| id | select_type | table | type | key | ref | rows | Extra |
+----+-------------+-------+-------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | 100 | Using where |
| 2 | SUBQUERY | t2 | range | PRIMARY | NULL | 9 | Using where |
+----+-------------+-------+-------+---------+------+------+-------------+
总扫描行数为 100+9=109.
对于不同类型的子查询,优化器会选择不同的策略。
# 查看MySQL采用的优化器策略
show variables like 'optimizer_switch';
# 修改优化器策略
set global optimizer_switch='materialization=on,semijoin=on';
set global optimizer_switch='materialization=off,semijoin=off';
MySQL官方文档:https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
注意:update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略
semijoin 和 materialization 的开启是通过 optimizer_switch 参数中的 semijoin={on|off}、materialization={on|off} 标志来控制的。上文中不同的执行计划就是对 semijoin 和 materialization 进行开/关产生的。
总的来说对于子查询,先检查是否满足各种优化策略的条件(比如子查询中有 union 则无法使用 semijoin 优化),然后优化器会按成本进行选择,实在没得选就会用 exists 策略来“优化”子查询,exists 策略是没有参数来开启或者关闭的。
使用连接(Join)替代子查询。(连接通常比子查询效率更高,尤其是在大型数据集上)
使用EXISTS替代IN
EXISTS只检查是否存在满足条件的行,而IN会在子查询中返回所有匹配项,然后检查是否与主查询中的值匹配。
尽量减少子查询的嵌套层级。(每个嵌套层级都会增加查询的复杂度和执行时间)
使用合适的索引
文章浏览阅读2.1k次。 需求如下:该搜索框是对整个页面的input检索 ,但与弹出层中的input冲突 博主几经辗转 简单处理 解决问题,思路如下:排除掉特定class的input。代码如下:$('input:not(.pop)', this.footer()).on('keyup change', function () { if (that.search() !== th..._input排他选择器
文章浏览阅读5.6k次,点赞6次,收藏20次。看到别人有个1024的勋章,特意留了一篇在今年的10.24日,看看会不会获得。在日常开发中可能涉及接口之间的相互调用,虽然在现在微服务的理念推广下,很多公司都采用轻量级的JSON格式做为序列化的格式,但是不乏有些公司还是有一些XML格式的报文,最近就在对接某个合作方的时候遇到了XML报文。在JSON报文爽快的转换下很难试用一个一个的拿报文参数,还是希望能直接将报文转换成Bean。接下来就了解到..._jaxb 泛型
文章浏览阅读1.2k次。numpy的主要数据对象是多维数组,其中包含相同类型的元素,通常是数字类型,每个元素都有一个索引。使用numpy前通常要导入包。import numpy as np目录类型维度创建运算索引和切片类型numpy的数组被称为ndarray。numpy.array只处理一维数组,而ndarray对象才提供更多功能。a = np.array([[1, 2, 3], [4, 5, 6]])type(a) # <class 'numpy.ndarray'>dtype属性可以获得元素的数_ndarray的位置
文章浏览阅读1.6w次。还在苦于网上找到的一些指令已经不适用了吗?还在苦于有些地方的指令有误吗?还在苦于有些地方整理的指令不够全面吗?那么你来对地方了!小编为大家整理了《我的世界》原版游戏常用的指令,这些基本足以满足各位的基本需求了!大家来一起看看吧!注:表示的是必须输入的部分,[方括号]表示的是可选择性输入的部分基本命令列表命令描述/?/help的替代命令,提供命令使用帮助。/ban + 玩家名字将玩家加入封禁列表。/..._gamemode指令java
文章浏览阅读1.5w次,点赞3次,收藏3次。Spring Boot 结合shiro做第三方登录验证1、首先,说一下我的具体实现思路。在做spring boot拦截器的过程中,开始我准备用spring security来实现,但是研究了一段时间之后发现spring security的集成度太高,需要修改的东西比较多,而且对它本身的使用方法不是很了解,后来转而使用Apache shiro。由于是第三方登录,是不需要我来验证密码的。最开始,我陷入了_shiro 第三方token登录
文章浏览阅读1.9k次,点赞4次,收藏4次。[INFO ] __init__:get_config:71 - Loading config file from:C:\Users\xxx\.labelmercTraceback (most recent call last): File .... line 191, in <module> main() File ...., line 145, in main config = get_config(config_file_or_yaml, config_fro_file "c:\rgzn\labelme-main\setup.py", line 91, in main if sys.argv[1] == "re
文章浏览阅读8.7k次。代码错误的原因和调试方法_代码报错
文章浏览阅读5.2k次,点赞9次,收藏40次。---恢复内容开始---1.认识游戏 1.1什么是游戏 1.1.1游戏的定义 任何人类正常生理需求之外的活动均可称为游戏 1.1.2游戏的分类 RPG角色扮演游戏、ACT动作游戏、AVG冒险游戏、FPS第一人称视角射击游戏、TPS第三人称视角射击游戏、FTG格斗游戏、SPT体育游戏、RAC竞速游戏、RTS即时战略游戏、STG..._深度解析java游戏服务器开发
文章浏览阅读4k次。CSRF是什么我就不解释了,百度一搜全是,比波姐的片源还要多,千篇一律都他么是复制粘贴。那为什么这个令牌(token)操作可以防范CSRF呢?下面我就随便说说说错了大家不要介意。首先我们要知道令牌是存储在session里面的,这个很重要 php代码如下<?php namespace app\index\controller; //我直接允许跨域,因为伪装..._tp5 开启csrf令牌
文章浏览阅读1.7k次,点赞2次,收藏6次。市盈率PE市盈率 = 市值/净利润概念解析:买入一家公司,几年回本,年化收益率:净利润/市值(市盈率的倒数)举例:砖头10万买个砖头,每年拍人带来1万利润,需要10年回本市盈率:10/1 = 10年化收益率:1/10 = 10%市净率PB市净率 = 市值/净资产净资产 = 总资产 - 负债举例:张三便利店,净资产:120万市值:1..._净资产收益率和股息率
文章浏览阅读737次。教育部昨举行「102年国立馆所文创商品设计比赛」颁奖典礼,台北科技大学创新设计研究所硕士生谢镇宇,为TW艺术教育馆设计「墨器」杯垫,取「默契」谐音,用5片压克力板,展现水墨画层层渲染效果,增加立体视觉感受,并在杯架后方加入LED光源,获评审肯定夺特优奖和奖金10万元。台南应用科技大学商品设计系学生高郁翔,为国立自然科学博物馆设计「恐龙化石钉书机」,他认为小朋友把钉书机钉下去的那一刻,会觉得像暴龙準_杯垫文创设计说明
文章浏览阅读404次。XML与对象,集合的相互转化 今天小伙伴在群里问了一下关于XML与对象之间的相互转换,作为菜鸟的我正好趁着闲着的时间学习了一波,直接上代码了,有疑问或者有错误的地方还请大家指正,谢谢。。。。 1 using System; 2 using System.Collections.Generic; 3 using System.IO; 4 using System...._c# xml转集合