MySQL知识总结

《MySQL是怎样运行的》知识总结

单表访问方法

表的连接原理

优化 基于查询成本的优化

优化需要的统计数据

优化 基于规则的优化

Explain详解

InnoDB缓冲区

事务

redo日志

undo 日志

MVCC原理

MySQL 锁

11 连接原理

表的结构

create table t1
(
    m1 int,
    n1 char(1)
);

create table t2
(
    m2 int,
    n2 char(1)
);

insert into t1
values (1, 'a'),
       (2, 'b'),
       (3, 'c');

insert into t2
values (2, 'b'),
       (3, 'c'),
       (4, 'd');

两表连接

select *
from t1,
     t2;

连接就是把各表的记录取出来,依次进行匹配,把匹配的组合发送给客户端,上面的语句就是把t1、t2表中的记录连接起来,组成一个更大的记录。

连接查询的结果包含了t1、t2各表中的每一条记录互相匹配组合,这个结果集就被成为笛卡尔积

我们可以连接任意数量的表,如果不附加任何限制条件,表连接起来的笛卡尔积可能非常巨大,所以连接时进行过滤是有必要的。

select *
from t1,
     t2
where t1.m1 > 1
  and t2.n2 < 'd'
  and t1.m1 = t2.m2;

这个连接查询有3个条件,查询的过程大致如下:

  1. 确定第一个需要查询的表,进行单表查询,这个表成为驱动表
  2. 从驱动表中每获取一条记录,都需要到t2表中查找匹配记录(符合过滤条件的记录),这里的过滤条件是t1.m1 = t2.m2,这时t2表被称为被驱动表,步骤1中查询t1表得到的第一记录中m1 = 2,那么连接的过滤条件是t2.m2 = 2,然后再根据条件进行t2表的单表查询

这个例子中,驱动表需要查询1次,被驱动表需要查询2次

每一次从驱动表中获取了记录就会去被驱动表查询匹配的数据

内外连接

  • 内连接:对于内连接的两个表,若驱动表中的记录在被驱动表中找不到匹配的记录,就不加入到最终的结果集

  • 外连接:即使被驱动表中没有匹配的记录,也加入最终结果集

  • 左外连接:选取左表为驱动表(右连接则相反)

  • 连接条件

    • where:不符合where子句的过滤条件的记录不会被加入结果集中
    • on:对于外连接,如果无法在被驱动表中找到符合on子句过滤条件的记录,这些记录也会被加入到结果集

在内连接中,on、where子句是等价的

对于外连接,驱动表、被驱动表的关系很重要,因为驱动表的记录即使在被驱动表中找不到匹配的记录,也会被加入到结果集中

嵌套循环连接

​ 两表连接中,驱动表只会查询1次,被驱动表可能会查询多次(取决于对驱动表查询后的记录数量),这个过程像是一个嵌套循环

对于嵌套循环连接算法,每一次从驱动表中查询到记录就立刻到被驱动表中进行匹配查询,如果得到了匹配的记录就把组合返回给客户端,“结果集”是一个抽象的概念

索引加快连接速度

explain
select *
from t1,
     t2
where t1.m1 > 1
  and t2.n2 > 'a'
  and t1.m1 = t2.m2;
  1. 在t2表中m2列上建立索引,针对m2列的条件进行等值查询,可能用到ref访问方法,对t2进行查询后,还需要在回表后再判断t2.n2 > 'a'
  2. 如果在m2上建立主键或不允许为null的唯一索引,那么访问方法是eq_ref
  3. 在t2表中n2列上建立索引,对于条件t2.n2 > 'a',可能有用到的访问方法是range,如果使用range访问方法,那么还需要在回表后判断t2表中的m2列是否成立。
  4. 如果t2表上在m2、n2上有索引,那就需要挑选一个成本较低的索引进行查询。
  5. 使用索引的情况下,连接查询的查询列表查询条件可能只涉及了被驱动表的部分列,这些列又全是索引的一部分,那么这种情况下,最坏的情况也可以使用index的访问方法,所以最好不使用*作为查询列表。

基于块的嵌套循环查询

​ 现实生活中的数据记录是非常多的,假设不能使用索引进行查询,对于驱动表结果集的每一条记录,都需要对被驱动表进行全表扫描。这会导致IO的代价非常大(比如表的数据非常多,而内存不足,表的记录一部分在内存,一部分在磁盘,这就需要将内存中的数据释放后,再将磁盘的数据加载到内存)。

​ MySQL引入了连接缓冲区的概念,执行连接查询时,会在内存申请一块固定大小的内存,再把若干条驱动表结果集记录加载到缓冲区中,然后查询被驱动表,每一条被驱动表的记录都与缓冲区的多条记录进行匹配,匹配是在内存中完成的,这显著减少了被驱动表的IO代价。

​ 最好的情况就是,缓冲区的大小能容纳所有的驱动表的结果集记录,这样只需要访问一次被驱动表就可以完成连接操作。可以通过启动选项、系统变量join_buffer_size进行配置,默认大小是256k。

​ 缓冲区不会存放驱动表的记录的所有列,只有查询列表的列、过滤条件会被放入缓冲区,所以查询列表尽量不使用*。

Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐