MySQL是怎样运行的-数据查询篇

_风不会停息
37 阅读19分钟

主要包含单表访问方法、连表查询的原理、基于成本的SQL优化、InnoDB统计数据的收集、基于规则的优化、查询计划的Explain

单表访问方法

MySQL Server有个称为优化器的模块，MySQL Server在对一条查询语句进行语法解析之后，会将其交给优化器来优化，优化的结果就是生成一个执行计划。这个执行计划表明了应该用哪些索引进行查询、表之间的连接顺序是啥样的 等等。最后会按照执行计划中的步骤调用存储引擎提供的接口进行真正的查询，并将结果返回给客户端。现在先让我们看看MySQL怎么执行单表查询。 本章需要的基础知识包括：数据记录的格式、数据页结构、以及索引的内容

  • const：通过主键或者唯一二级索引来定位一条记录的访问方法称为const，意思是常数级别的，代价可以忽略不计。如果主键或者唯一二级索引由多个列构成，则只有在索引列中的每一个列都与常数进行比较时才有效。
  • ref：将普通的二级索引列与常数进行等值比较，形成的扫描区间称为单点扫描区间，采用二级索引来执行查询的访问方法称为ref。如果索引列中最左边连续的列不全是等值比较的话，他的访问方法就不能称之为ref了
  • ref_or_null：如果想查询某个二级索引列的值等于某个常数，而且还想把列中值为null的记录也查询出来，这种访问方式称为ref_or_null。值得注意的是，MySQL会把值为null的记录全部放在索引的最左边
  • range：使用索引执行查询时，对应的扫描区间为若干个单点扫描区间（in条件）或者范围扫描区间（>或者<条件）的访问方法称为range
  • index：扫描二级索引上的全部记录、但不需要回表的访问方法称为index。另外，当全表扫描包含了 “order by 主键” 的语句，也会被认定为使用的是index
  • all：全表扫描

索引合并 索引合并的意思就是对从不同索引中扫描到的记录的id值取交集，只为这些id值执行回表操作

  • Intersection索引合并
  • Union索引合并
  • Sort-Unoin索引合并

连接的原理

从本质上来说，连接就是把各个表中的记录都取出来进行依次匹配，并把匹配后的组合发送给客户端。需要注意的是，连表查询是每获取一条驱动表记录，就立即到被驱动表中寻找匹配的记录，而不是把驱动表中的所有记录先查出来放到某个地方再遍历这些记录。驱动表和被驱动表的概念如下：

  • 驱动表：首先被访问的表，其记录用于逐行匹配被驱动表。在内连接中通常选择数据量较小的表作为驱动表
  • 被驱动表：根据驱动表的记录进行匹配的表，相当于嵌套循环中的内层循环

驱动表与被驱动表区分方法‌

  • 执行计划中排在第一行的表是驱动表
  • 左连接时左表为驱动表，右连接时右表为驱动表
  • 无WHERE条件时，MySQL自动选择数据量小的表作为驱动表

内连接和外连接的区别

  • 内连接：如果驱动表中的记录在被驱动表中找不到记录，则该记录不会被加入到结果集中
  • 外连接：如果驱动表中的记录在被驱动表中找不到记录，则该记录仍然会被加入到结果集中

当不想把全部记录都加入到结果集中时，可以使用where子句或者on子句过滤记录

  • where：不论是内连接还是外连接，不符合where条件的记录都不会被加入到结果集
  • on：对于外连接来说，如果无法在被驱动表中找到匹配on子句中过滤条件的记录时，驱动表中的记录仍然会被加入到结果集中返回，对应的被驱动表字段都为null
    • 注意：on子句是专门为“外连接查询”这个场景提出的，如果把on子句放到内连接中，where和on的作用是等价的。所以内连接不要求写明on子句

连接的原理

嵌套循环连接： 驱动表只访问一次，被驱动表却可能访问多次，且访问次数取决于对驱动表执行单标查询后的结果集中有多少条记录，这种连接执行方式称为嵌套循环连接，这种是最简单的连接查询

如果被驱动表的关联条件是索引列，就可以尝试用索引来加快查询速度。 在连接查询讯中对被驱动表的主键或者不允许为null的唯一索引进行等值查询使用的访问方法称为 eq_ref

基于块的嵌套循环连接： 如果可以申请一块固定大小的内存，把被驱动表的记录加载到这块内存中，然后开始扫描被驱动表，每一条被驱动表的记录一次性的与内存中的多条驱动表记录进行匹配，这样就可以大大减少重复从磁盘中加载被驱动表的I/O代价了。这块专门的内存空间被命名为 Join Buffer（连接缓冲区，默认256KB），这种加入了Join Buffer 的嵌套循环连接算法称为基于块的嵌套循环连接

要注意的是，只有查询列表中的列和过滤条件中的列才会被放到Join Buffer中，所以最好不要把 * 作为查询列表，只查询关心的列就好

查询成本计算

MySQL在执行一个查询时可以有多种执行方案，，它可以选择成本最低的方案去真正的执行。MySQL中的执行成本是由两方面组成的

  • IO成本：数据库中的数据和索引会存储在磁盘上。当查询表中的数据时，需要先把数据或者索引加载到内存中，再进行操作。
  • CPU成本：读取记录以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作消耗的时间称为CPU成本

读取磁盘中的一个页面花费的成本默认为1.0，读取以及检测一条记录是否符合搜索条件的成本默认为0.2。1.0、 0.2 这些数字称为成本常数

基于成本的优化步骤

在真正执行一条单表查询语句之前，MySQL的优化器会找出所有可以用来执行该语句的方案，并在对比这些方案之后找出成本最低的方案。这个过程总结一下如下：

  1. 根据搜索条件，找出所有可能使用的索引
  2. 计算全表扫描的代价
  3. 计算使用不同索引执行查询的代价
  4. 对比各种执行方案的代价，找出成本最低的方案

可能使用的索引

只要索引列和常数产生一个扫描区间，这些搜索条件就可能会使用索引。一个查询中可能使用的索引称之为 possible keys，

全表扫描的代价

总成本 = 磁盘中的页面数*IO成本常数 + 统计数据中表的记录数*CPU成本常数

使用索引执行查询的代价

总成本 = 扫描区间数*IO成本常数 + 需要回表的记录数*CPU成本常数

基于索引统计数据的成本计算

如果使用索引执行查询时有许多单点扫描区间，由于有些索引并不是唯一二级索引，所以不能确定每个扫描区间中的记录条数有多少，需要先获取索引对应的B+树的区间最左记录和区间最右记录，然后计算这两条记录之间有多少记录，这种通过直接访问索引对应的B+树来计算某个扫描区间内对应索引记录数的方式称为 index dive。

连接查询的成本

对于两表连接查询来说，它的查询成本由两部分组成：

  • 单次查询驱动表的成本
  • 多次查询被驱动表的成本（具体查询多少次取决于针对驱动表查询后的结果集中有多少条记录）

我们把查询驱动表后得到的记录条所述称为驱动表的扇出。扇出值越少，对被驱动表的查询次数也就越少连接的总成本也就越低。MySQL查询优化器会计算每一种连接顺序的成本。不过MySQL会尽力优化因计算不同连接顺序下的查询成本而带来的性能损耗。 连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出值*单次访问被驱动表的成本。

对于左连接和右连接查询来说，他们的驱动表是固定的，所以只需要分别为驱动表和被驱动表选择成本最低的访问方法即可。 对于内连接来说，驱动表和被驱动表的位置是可以互换的，因此要考虑两方面的问题：

  • 当不同的表作为驱动表时，最终的查询成本可能不同，也就算是需要考虑最优的表连接顺序
  • 然后分别为驱动表和被驱动表选择成本最低的访问方法

InnoDB统计数据的收集

InnoDB提供了两张存储统计数据的方式，分别是基于磁盘的永久性存储统计数据和基于内存的非永久性存储统计数据。自 MySQL 5.6.6 版本起，统计数据默认被保存到磁盘上。InnoDB默认以表为单位来收集和存储统计数据。

当选择把某个表以及表索引的统计数据放到磁盘上时，实际上是把这些统计数据存储到了 innodb_index_stats 和 innodb_table_stats 这两个表。这两个表都位于mysql系统数据库下面。其中：

  • innodb_table_stats 存储了关于表的统计数据，每一条记录对于这一个表的统计数据
  • innodb_index_stats 存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据

InnoDB在它统计一个表有多少行数据时，大体过程是这样的：按照一定的算法从聚簇索引中选取几个叶子结点，统计每个页面中包含的记录数量，然后计算一个页面中平均包含的记录数量，并将其乘以全部子节点的数量，结果就是该表的 n_rows。这就是为什么n_rows统计项是估计值。

统计表中的数据也会随着增删改查操作而更新，MySQL提供了两种更新统计数据的方式

  • 自动计算统计数据：每个表都维护了一个变量，记录着对该表进行增删改的记录条数，如果发生变动的记录数量超过了该表大小的10%，那么服务器会重新计算一次统计数据，并且更新innodb_table_stats和innodb_index_stats表。这个自动重新计算的过程是异步的，所以可能会存在数据更新延迟。
  • 手动调用 ANALYZE TABLE：调用 ANALYZE TABLE 预计会立即重新计算统计数据，这个过程是同步的。例如 ANALYZE TABLE single_table

基于规则的优化

  • 移除不必要的括号：如 select * from (t1,(t2,t3)) 优化成 select * from t1,t2,t3
  • 常量传递：如 a = 5 and b > a 优化成 a = 5 and b = 5
  • 移除没用的条件：如 (a < 1 and b = b) or (a != a) 优化成 a < 1
  • 表达式计算：如 a = 5 + 1 优化成 a = 6
  • Having子句和where子句合并：如果查询语句中没有出现 sum、max 这样的聚集函数以及GROUP BY子句，查询优化器就会合并Having子句和where子句
  • 外连接消除：外连接查询中如果无法再被驱动表中找到匹配ON子句过滤条件的记录，那么该驱动表记录仍然会被加入到结果集中，被驱动表的列用null填充。此时如果在where子句中指定“被驱动表的列不为null”，这种情况下外连接和内连接是等价的，可以相互转换。这种转换的好处就是 优化器可以通过评估表的不同连接顺序的成本，选出成本最低的连接顺序来执行查询。我们把where子句中包含被驱动表的列不为null值得条件称为空值拒绝。

子查询优化

本来这部分内容才是主要的，但是由于这部分字数较多，理解较复杂，所以忽略，感兴趣这部分可以看原文~ 这里涉及了几个概念：物化、派生表、半连接

  • 物化：将子查询结果集中的记录保存到临时表中的过程 称为物化
  • 派生表：在FROM子句后面出现的子查询逻辑上相当于一个表，这种子查询称为派生表，如 select * from (select age+2 from students)
  • 半连接：将表s1和表S2进行半连接的意思就是，对于s1表中的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表中的记录。

Explain 执行计划

MySQL查询优化器在基于成本和规则对一条查询进行优化后，会生成一个执行计划，这个执行计划展示了接下来执行查询的具体方式。本章内容帮大家看懂Explain语句的各个输出项含义。

列名描述
id在一个大的查询语句中，每个select关键字都对应一个唯一的id值
select_typeSELECT关键字对应的查询类型
table表名
partitions匹配的分区信息
type针对当前行单表执行查询时的访问方式
possible_keys可能用到的索引
key实际使用的索引
key_len实际使用的索引长度 = 每个字段占用的最大字节数 * 对应记录条数
ref当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows预估的需要读取的记录条数
filtered针对预估的需要读取的记录，经过搜索条件过滤后剩余记录条数的百分比
Extra一些额外信息

table
EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，table列就是对应单表的表名

id

  • 查询语句每出现一个select关键字，MySQL就会为它分配一个唯一的id值。
  • 对于包含子查询的语句来说，可能涉及多个select关键字，每个select关键字都会对应一个唯一的id值
  • 对于连接查询来说，一个select语句后面的from子句中可以跟随多张表，在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的
  • 对于UNION子句，它会把多个查询结果集合并起来并对结果集进行去重。怎么去重呢？MySQL使用内部临时表，这种合并两个查询的结果集而创建的临时表对应的id为NULL

select_type

  • SIMPLE：查询语句中不包含UNION或者子查询的查询都算SIMPLE
  • PRIMARY：对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小的查询组成的，最左边那个查询的select_type就是PRIMARY
  • UNION：对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小的查询组成的，除了最左边那个查询以外的小查询就是 UNION
  • UNION RESULT：针对UNION查询去重的临时表的select_type就是 UNION RESULT
  • SUBQUERY：如果包含子查询的查询与不能转换为对应的半连接形式，并且子查询是相关子查询，而且查询优化器决定采用讲该子查询物化的方案来执行该子查询，该子查询的select_type就是 SUBQUERY
  • DEPENDENT SUBQUERY：
  • DEPENDENT UNION：
  • DERIVERD：在包含派生表的查询中，如果是以物化派生表的方式执行查询，则派生表对应的子查询select_type就是DERIVERD
  • MATERIALIZED：在执行包含子查询的语句时，选择将子查询物化后与外层查询进行连接查询，该子查询对应的select_type就是MATERIALIZED

type
完整的访问方法有：system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、all

  • system：确定表中只有一条记录
  • const：使用主键或者唯一二级索引进行等值匹配
  • eq_ref：执行廉洁查询时，如果被驱动表时通过主键或者非null的唯一索引等值匹配的方式进行访问（如果是联合索引，则所有索引列都必须等值匹配），则对该被驱动表的访问方法就是eq_ref
  • ref：通过普通的二级索引与常量进行等值匹配
  • ref_or_null：通过普通的 允许为null的 二级索引与常量进行等值匹配
  • index_merge：使用索引合并的方式执行查询（某些情况下可以使用Intersection、Union、Sort-Union）
  • union_subquery：针对一些包含IN子句的查询语句，如果查询优化器决定将In查询转换为EXISTS子查询，而且在转换之后可以使用 主键或者非NULL的唯一索引 进行等值匹配，那就是union_subquery
  • index_subquery：与union_subquery类似，只不过在访问子查询中的表时使用的是普通索引
  • range：使用索引获取某些单点区间的记录，如IN或者>、<
  • index：可以使用索引覆盖，但需要扫描索引表的全部索引记录。（对于InnoDB来说，当需要使用全表扫描，并且要对主键进行排序时，此时的type也是index）
  • ALL：全表扫描
  • fulltext：全文索引

partitions
未提及，跳过

possible_keys和key

  • possible_keys：表示在某个查询中，对某个表执行单表查询可能用到的索引。可以使用的索引越多，查询优化器在计算查询成本时花费的时间越长
  • key：表示实际用到的索引

key_len

  • 对于固定长度的列来说，每个字段值的长度是对应类型占用的字节数
  • 对于变长类型来说，按照该列数据最多占用的字节数计算，同时还要加上存储占用空间长度的最大字节数。如果可以存储NULL，还要再加上 1

ref
当访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery中的其中一个时，ref列展示的是与索引列进行等值匹配的东西是啥，比如是一个常量或者是某个列

rows
在查询优化器决定使用全表扫描对某个表进行查询时，rows列就代表该表的估计行数。如果使用索引执行查询，rows列就代表预计扫描的索引记录行数

filtered
在连接查询中之前提到的条件过滤（condition filtering）就是MySQL在计算驱动表扇出时采用的策略。对于单表查询来说，filtered值没意义。我们更关注连接查询中驱动表对应的filtered值。

  • 如果使用全表扫描来执行单表查询，那么计算驱动表扇出时需要估算出满足全部搜索条件的记录有多少条
  • 如果使用索引来执行单表查询，那么计算驱动表扇出时要估算出满足形成索引扫描区间的搜索条件外，还满足其他搜索条件的记录有多少条

Extra

  • Using index：使用覆盖索引执行查询
  • Using index condition：搜索条件中虽然出现了索引列，但却不能充当边界条件来形成扫描扇区，也就是不能用来减少需要扫描的记录行数，如 key1 like '%abc'
  • Using where：某个搜索条件需要在server层进行判断
  • Using join buffer：使用了基于块的嵌套循环算法（Join Buffer）来执行连接查询
  • Using intersect(...)、Using union(...)、Using sort_union(...)：使用索引合并的方式执行查询
  • Using filesort：在无法使用索引进行排序时，在内存中（记录较少时）或者磁盘中（结果较多时）对结果集中的记录进行排序统称为 filesort。MySQL在包含GROUP BY子句中会默认添加ORDER BY子句，这时候可能会提示 Using filesort
  • Using temporary：需要借助临时表来完成一些功能，比如去重、排序之类的。最好使用索引代替临时表

执行计划的额外信息

JSON格式的执行计划
我们可以通过在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON来查看某个执行计划花费的成本，比如 explain format=json select * from t1 where key1 like '%a'

optimizer trace
optimizer trace 可以让用户查看优化器生成执行计划的整个过程。这个功能的开关由系统变量 optimizer_trace 来决定，可以通过 set optimizer_trace="enabled=on" 来开启这个功能。打开这个功能后用户可以从 information_schema.OPTIMIZER_TRACE 表中查看每个查询的优化过程。 OPTIMIZER_TRACE表由四列，如下

  • QUERY：输入的查询语句
  • TRACE：优化过程的JSON格式的文本
  • MISSING_BYTES_BEYOND_MAX_MEM_SIZE：在执行计划的生成过程中可能会输出很多内容，如果超出这个限制，多余的文本将不会显示。这个字段展示被忽略的文本字节数
  • INSUFFICIENT_PRIVILEGES：表示是否有权限查看执行计划的生成过程，默认是 0 表示有权限查看
avatar
文章
阅读
粉丝