mysql join语句分析（一）

全表扫描

​ 假设，现在对一个200G的innodb的表，做全表扫描，把扫描结果保存在客户端。

mysql ‑h$host ‑P$port ‑u$user ‑p$pwd ‑e "select * from db1.t" > $target_file

​ 这个语句的结果集存在哪里呢？

  • 实际上，服务端并不需要保存一个完整的结果集。取数据和发数据流程如下：
    1. 获取一行，写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是
      16k。
    2. 重复获取行，直到net_buffer写满，调用网络接口发出去。
    3. 如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。
    4. 如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send
      buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
  • 一个查询在发送过程中，占用mysql内部的内存最大的就是net_buffer_length，并不会达到200G
  • socket send buffer 也不可能达到200G
  • 也就是说，MySQL是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会
    导致MySQL服务端由于结果发不出去，这个事务的执行时间变长。

全表扫描对innodb的影响

  • innodb内存的一个作用是保存更新的结果，再配合redo log，避免了随机斜盘。
  • 内存的数据页在Buffer Pool中，在wal里，buffer pool即起到了加速更新的作用，也有个作用是加速查询。其依赖一个重要指标：内存命中率。
    • 执行 show engine innodb status 可以看到命中率
    • 如果所有查询需要的数据页都能够直接从内存得到，那是最好的，对应的命中率就是100%。
      但，这在实际生产上是很难做到的。
    • InnoDB Buffer Pool的大小是由参数 innodb_buffer_pool_size确定的，一般建议设置成可用物理内存的60%~80%。
    • InnoDB内存管理用的是最近最少使用 (Least Recently Used, LRU)算法，这个算法的核心就是淘汰最久未使用的数据。
  • 全表扫描还是比较耗费IO资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的。

mysql如何使用join

CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;

可以看到，这两个表都有一个主键索引id和一个索引a，字段b上无索引。假设表t2有1000行数据，表t1是100行数据。

  • select * from t1 straight_join t2 on (t1.a=t2.a);

    straight_join可以让mysql使用固定的连接方式执行join，这个语句里，ti是驱动表，t2是被驱动表。执行流程如下：

    • 从表t1读入一行数据R
    • 从R中，取出a到t2里查找
    • 取出t2中满足的行和R一行，作为结果集的一部分
    • 重复以上步骤，直到t1的循环结束。

    这个过程是先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记
    录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所
    以我们称之为“Index Nested-Loop Join”，简称NLJ。

    流程图如下：

    t1做了全表扫描，100行，t2是走了树搜索过程，也是100行。整个执行流程，总扫描行数是200。

  • 假设不适用join，用单表查询。

    1. 执行select * from t1，查出表t1的所有数据，这里有100行；
    2. 循环遍历这100行数据：
      • 从每一行R取出字段a的值$R.a；
      • 执行select * from t2 where a=$R.a；
      • 把返回的结果和R构成结果集的一行。

    可以看到，在这个查询过程，也是扫描了200行，但是总共执行了101条语句，比直接join多了
    100次交互。除此之外，客户端还要自己拼接SQL语句和结果。

如何选择驱动表

  • 之前的join语句执行过程中，驱动表走的是全表扫描，被驱动表走树搜索。
  • 假设被驱动表的行数是M，每次在被驱动表插一行数据，先搜索索引a，再搜索主键索引，每次搜索一次近似复杂度是logM，在被驱动表上查一行的时间复杂度是2*logM。
  • 假设驱动表行数是N，搜索过程就要扫描驱动表N行，对于每一行，到被驱动表匹配一次，因此整个过程复杂度近似N+N*2*logM，显然，N对扫描行数的影响更大，应该让小表做驱动表。
  • 小结：
    • 使用join语句，性能比强行拆成多个单表执行sql语句性能要好
    • 如果使用join语句，需要让小表做驱动表。
    • 注意：前提是可以使用被驱动表的索引。

Simple Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.b);

由于表t2的字段b没有索引，因此再用上图的流程去匹配的时候，就要做一次全表扫描。

那么这一次扫描的行数就是100*1000 = 10万行。这个就叫Simple Nested-Loop Join，

很笨重，下面是mysql使用的另一种算法Block Nested-Loop Join

Block Nested-Loop Join

  1. 把表t1的数据读入线程内存join_buffer中，由于是select * ,因此是把整个表t1放入内存

  2. 扫描表t2，把t2中每一行取出来，和join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

    所以，在整个过程中，只对t1和t2都做了一次全表扫描，总行数是1100，由于join_buffer是无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共次数是10万次，从时间复杂度来看，两者是一样的，但BNL的算法是内存上的操作，速度会更快，性能页更好。

  • 如何选择驱动表

    假设小标N行，大表M行，扫描的总行数是M+N，内存判断次数是M*N，所以选择大表还是小标，耗时是一样的。

    如果t1是大表，join_buffer会不会放不下呢？

    join_buffer可以设置大小，默认是256k，如果放不下t1的所有数据，就分段放。流程如下：

    1. 扫描表t1，顺序读取数据行放入join_buffer中，放完第88行join_buffer满了，继续第2步；
    2. 扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回；
    3. 清空join_buffer；
    4. 继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。

    所以这个算法体现了BLOCK，表示分块去join，判读的总次数是不变的。再看一下驱动表的选择问题。比如100*1000 = （88+12）*1000，分块越多，被驱动表扫描次数也就越多。

    驱动表的数据行数是N，需要分K段才能完成算法流程，被驱动表的数据行数是M。

    这里的K不是常数，N越大K就会越大，因此把K表示为λ*N，显然λ的取值范围是(0,1)。

    1. 扫描行数是 N+λ*N*M；
    2. 内存判断 N*M次。

    内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数，在M和N大小确定的情况下，N小一些，整个算式的结果会更小。所以结论是，应该让小表当驱动表。

  • 小表的定义

    我们前面的例子是没有加条件的。如果我在语句的where条件加上 t2.id<=50这个限定条件，再来看下这两条语句：

    select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;

    如果是用第二个语句的话，join_buffer只需要放入t2的前50行，显然是更好的。所以这里，“t2的前50行”是那个相对小的表，也就是“小表”。

    select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;

    这个例子里，表t1 和 t2都是只有100行参加join。但是，这两条语句每次查询放入join_buffer中的数据是不一样的：

    • 表t1只查字段b，因此如果把t1放到join_buffer中，则join_buffer中只需要放入b的值；
    • 表t2需要查所有的字段，因此如果把表t2放到join_buffer中的话，就需要放入三个字段id、a和b。我们应该选择表t1作为驱动表。也就是说在这个例子里，“只需要一列参与join的表t1”是那个相对小的表。
    • 在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完
      成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jimmyhe/p/11167231.html