MySQL的join buffer原理

一、MySQL的join buffer

在MySQL对于join操作的处理过程中,join buffer是一个重要的概念,也是MySQL对于table join的一个重要的优化手段。虽然这个概念实现并不复杂,但是这个是实现MySQL join连接优化的一个重要方法,在"暴力"连接的时候可以极大提高join查询的效率。

关于这个概念的权威说明当然是来自MySQL文档中对于这个概念的说明,说明的文字不多,但是言简意赅,说明了这个优化的主要实现思想:
Assume you have the following join:

Table name      Type
t1              range
t2              ref
t3              ALL
The join is then done as follows:

- While rows in t1 matching range
 - Read through all rows in t2 according to reference key
  - Store used fields from t1, t2 in cache
  - If cache is full
    - Read through all rows in t3
      - Compare t3 row against all t1, t2 combinations in cache
        - If row satisfies join condition, send it to client
    - Empty cache

- Read through all rows in t3
 - Compare t3 row against all stored t1, t2 combinations in cache
   - If row satisfies join condition, send it to client

二、join buffer cache存储空间的分配

下面函数中table_count表示的就是所有join table中在该table之前的非const table数量,因为这个table要缓存自己之前所有table中的每条记录中"需读取"(tables[i].table->read_set置位)。

其中两重循环每次执行都是复制下需要缓存的field的描述结构(及其对应的数据源),或者说,二重循环只是为了赋值和保存元数据,而最后的cache->buff=(uchar*) my_malloc(size,MYF(0))才是真正的分配满足条件的记录内容。

static int
join_init_cache(THD *thd,JOIN_TAB *tables,uint table_count)
{
……
  for (i=0 ; i < table_count ; i++)
  {
    bool have_bit_fields= FALSE;
    uint null_fields=0,used_fields;
    Field **f_ptr,*field;
    MY_BITMAP *read_set= tables[i].table->read_set;
    for (f_ptr=tables[i].table->field,used_fields=tables[i].used_fields ;
 used_fields ;
 f_ptr++)
    {
      field= *f_ptr;
      if (bitmap_is_set(read_set, field->field_index))
      {
used_fields--;
length+=field->fill_cache_field(copy);
……
      }
  }

  cache->length=length+blobs*sizeof(char*);
  cache->blobs=blobs;
  *blob_ptr=0; /* End sequentel */
  size=max(thd->variables.join_buff_size, cache->length);
  if (!(cache->buff=(uchar*) my_malloc(size,MYF(0))))
    DBUG_RETURN(1); /* Don't use cache */ /* purecov: inspected */
  cache->end=cache->buff+size;
  reset_cache_write(cache);
  DBUG_RETURN(0);
}

三、普通的多表查询实现

这个"普通"当然也可以理解为"朴素"、"直观"的意思,也是大部分情况下的执行流程。普通查询其实就是对于对于各个表格进行递归调用,和矩阵的乘法一样一样的,这个对应非常直观,也非常通用。

而这个常规的查询动作就是通过sub_select函数来实现,这个函数本质性上是执行

tsecer_select()
{
for (r = first ; r != end ; r = next)
{
if(sofartest())
{
nexttable.tsecer_select()
}
}
}

其中的sofartest()表示"使用所有当前已读取表格可以进行的判断",也就是where中下推的表达式。例如 select * from a, b where a.a > 10 and b.b + a.a = 10,在a表读取之后,其实已经可以执行 a.a > 10的判断。当然这个是一个甚至算不上伪代码的描述方法,而真正的代码对应为:

enum_nested_loop_state
sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)
{
……
    error= (*join_tab->read_first_record)(join_tab);
    rc= evaluate_join_record(join, join_tab, error);
……
  while (rc == NESTED_LOOP_OK)
  {
    error= info->read_record(info);
    rc= evaluate_join_record(join, join_tab, error);
  }
……
  return rc;
}
static enum_nested_loop_state
evaluate_join_record(JOIN *join, JOIN_TAB *join_tab,
                     int error)
{
……
  if (select_cond)
  {
    select_cond_result= test(select_cond->val_int());

    /* check for errors evaluating the condition */
    if (join->thd->is_error())
      return NESTED_LOOP_ERROR;
  }
……
    if (found)
    {
      enum enum_nested_loop_state rc;
      /* A match from join_tab is found for the current partial join. */
      rc= (*join_tab->next_select)(join, join_tab+1, 0);
      if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
        return rc;
      if (join->return_tab < join_tab)
        return NESTED_LOOP_OK;
      /*
        Test if this was a SELECT DISTINCT query on a table that
        was not in the field list;  In this case we can abort if
        we found a row, as no new rows can be added to the result.
      */
      if (not_used_in_distinct && found_records != join->found_records)
        return NESTED_LOOP_NO_MORE_ROWS;
    }
……
}

这里可以看到,这个地方是一个递归,用来产生一个笛卡尔叉乘集合,从程序实现和数学表达上看都非常简洁可爱。
在MySQL的实现中,tsecer_select函数中的for循环大致相当sub_select中的while循环,而tsecer_select函数中循环体内的内容被放在了evaluate_join_record函数中,其中的sofartest对应evaluate_join_record::test(select_cond->val_int());tsecer_select中的nexttable.tsecer_select()语句对应evaluate_join_record::(*join_tab->next_select)(join, join_tab+1, 0)。

四、join buffer的select实现

当使用join buffer cache时,next_select函数指向sub_select_cache

enum_nested_loop_state
sub_select_cache(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)
{
  enum_nested_loop_state rc;

  if (end_of_records)
  {
    rc= flush_cached_records(join,join_tab,FALSE);
    if (rc == NESTED_LOOP_OK || rc == NESTED_LOOP_NO_MORE_ROWS)
      rc= sub_select(join,join_tab,end_of_records);
    return rc;
  }
  if (join->thd->killed) // If aborted by user
  {
    join->thd->send_kill_message();
    return NESTED_LOOP_KILLED;                   /* purecov: inspected */
  }
  if (join_tab->use_quick != 2 || test_if_quick_select(join_tab) <= 0)
  {
    if (!store_record_in_cache(&join_tab->cache))
      return NESTED_LOOP_OK;                     // There is more room in cache
    return flush_cached_records(join,join_tab,FALSE);
  }
  rc= flush_cached_records(join, join_tab, TRUE);
  if (rc == NESTED_LOOP_OK || rc == NESTED_LOOP_NO_MORE_ROWS)
    rc= sub_select(join, join_tab, end_of_records);
  return rc;
}

结合MySQL文档中的说明,这里的代码意义就比较明显。开始对于end_of_records的判断对应的就是

    if (!store_record_in_cache(&join_tab->cache))
      return NESTED_LOOP_OK;                     // There is more room in cache
    return flush_cached_records(join,join_tab,FALSE);

对应

  - Store used fields from t1, t2 in cache
  - If cache is full

其中store_record_in_cache函数会判断cache是否已满,如果cache可以放入更多的缓存,则把之前table的组合记录存储在cache中,并返回NESTED_LOOP_OK。注意:这个地方可以说是整个cache优化的关键,因为这里并没有启动对于table的扫描。反过来说,如果cache数据已经满了,则调用flush_cached_records函数来进行下面的流程

    - Read through all rows in t3
      - Compare t3 row against all t1, t2 combinations in cache
        - If row satisfies join condition, send it to client
    - Empty cache

这个流程的特殊之处在于遍历的驱动是通过对于table的每一条记录来和cache中所有t1、t2组合来进行比较,来判断是否满足下推where条件(If row satisfies join condition),则执行join_tab->next_select函数(send it to client)。

static enum_nested_loop_state
flush_cached_records(JOIN *join,JOIN_TAB *join_tab,bool skip_last)
{
……
  info= &join_tab->read_record;
  do
  {//遍历t3表格所有记录
……
        for (i=(join_tab->cache.records- (skip_last ? 1 : 0)) ; i-- > 0 ;)
        {//遍历cache中所有t1、t2记录组合
          read_cached_record(join_tab);
          skip_record= FALSE;
          if (select && select->skip_record(join->thd, &skip_record))
          {//
            reset_cache_write(&join_tab->cache);
            return NESTED_LOOP_ERROR;
          }
          if (!skip_record)
          {//满足下推的where条件
//执行下一个table的遍历
            rc= (join_tab->next_select)(join,join_tab+1,0);
            if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
            {
              reset_cache_write(&join_tab->cache);
              return rc;
            }
          }
……
  } while (!(error=info->read_record(info)));

五、举例来说明下这个流程

这个实现的核心思想并不复杂,结合具体的例子来看就更加的简单直观。
举个例子,其中使用两个简单的table,其中分别存储一个x,和y的值,我们希望通过一个join操作来计算这两个表格中所有的满足 x

x + y

y == 5 * 5,也就是我们最常见的"勾三股四弦五"这样的经典勾股数数值。

mysql> create table harry (x int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert harry values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create table tsecer (y int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert tsecer values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> explain select * from harry, tsecer where x * x + y * y = 5 * 5;
+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | harry  | ALL  | NULL          | NULL | NULL    | NULL |    5 |                                |
|  1 | SIMPLE      | tsecer | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using join buffer |
+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+
2 rows in set (0.00 sec)

mysql>

1、不使用joinbuffer

在不使用join buffer的情况下,对于harry表的每个x值,对应的tsecer表都要进行一次全表扫描,之后使用这个x和y的组合判断是否满足x

x + y

y == 5 * 5这条件。由于x总共有5个值,所以tsecer需要全表扫描的次数就是5次。

2、使用joinbuffer

对于x的每个值,tsecer表在执行的时候先是把这个值缓存到joinbuffer中,如果buffer缓冲内容非空,那么把此时的x的值存储在buffer中后直接返回;当join buffer满或者是最后一条记录的时候,此时开始启动对于tsecer表的扫描,对于tsecer表中读取的每一个记录,结合前面缓存的每一个记录,看是否满足自己判断条件。
对于我们看到的例子,这个地方harry表的5个值都在缓存中,在tsecer表的扫描过程中,对于从tsecer中读取的每一条记录,结合缓存中的“每一条”缓存,判断这个组合结果是否满足条件,如果任意一个组很满足,那么就继续next_select。
在这个使用buffer的例子中,可以看到这个地方只是对于tsecer表进行了一次扫描,而通常来说,数据库的扫描代码是最高的(因为要涉及到磁盘读取),这样使用buffer的方式将tsecer表的扫描降低为1次,所以这个效率提高很多,特别是在涉及到的多个table,并且/或者 每个table中的记录数量都很多的情况下。

3、cache可以优化的原因

本质上说,这个效率提高的原因在于提高了从table中获得的每条记录的“利用率”,在使用直观扫描方式时,table的全表扫描只是和一个组合进行匹配,而使用buffer之后则是和cache中的所有组合进行匹配。

以上就是MySQL的join buffer原理的详细内容,更多关于MySQL join buffer的资料请关注我们其它相关文章!

(0)

相关推荐

  • Mysql优化调优中两个重要参数table_cache和key_buffer

    本文根据作者的一点经验,讨论了Mysql服务器优化中两个非常重要的参数,分别是table_cache,key_buffer_size. table_cache指示表高速缓存的大小.当Mysql访问一个表时,如果在Mysql表缓冲区中还有空间,那么这个表就被打开并放入表缓冲区,这样做的好处是可以更快速地访问表中的内容.一般来说,可以通过查看数据库运行峰值时间的状态值Open_tables和Opened_tables,用以判断是否需要增加table_cache的值,即如果open_tables接近t

  • php中mysql操作buffer用法详解

    本文实例讲述了php中mysql操作buffer用法.分享给大家供大家参考.具体分析如下: php与mysql的连接有三种方式,mysql,mysqli,pdo.不管使用哪种方式进行连接,都有使用buffer和不使用buffer的区别. 什么叫使用buffer和不使用buffer呢? 客户端与mysql服务端进行查询操作,查询操作的时候如果获取的数据量比较大,那个这个查询结果放在哪里呢? 有两个地方可以放:客户端的缓冲区和服务端的缓冲区. 我们这里说的buffer指的是客户端的缓冲区,如果查询结

  • 从MySQL的源码剖析Innodb buffer的命中率计算

    按官方手册推荐Innodb buffer Hit Ratios的计算是: 100-((iReads / iReadRequests)*100) iReads : mysql->status->Innodb_buffer_pool_reads iReadRequests: mysql->status->Innodb_buffer_pool_read_requests 出处: http://dev.mysql.com/doc/mysql-monitor/2.0/en/mem_graph

  • 详解MySQL中的缓冲池(buffer pool)

    Mysql 中数据是要落盘的,这点大家都知道.读写磁盘速度是很慢的,尤其和内存比起来更是没的说.但是,我们平时在执行 SQL 时,无论写操作还是读操作都能很快得到结果,并没有预想中的那么慢. 可能你会说我有索引啊,有索引当然快了.但是铁子,索引文件也是存储在磁盘上的,查找过程会产生磁盘 I/O.如果同时对某行数据进行多次操作,那岂不是要重复产生很多次磁盘 IO 吗? 可能你想到了,那我把数据存在内存里不就可以了吗?内存速度比磁盘快,这准没毛病.没错,那该怎么存呢? 这就是我们今天所要讲的主题--

  • mysql优化的重要参数 key_buffer_size table_cache

    MySQL服务器端的参数有很多,但是对于大多数初学者来说,众多的参数往往使得我们不知所措,但是哪些参数是需要我们调整的,哪些对服务器的性能影响最大呢?对于使用Myisam存储引擎来说,主要有key_buffer_size和table_cache两个参数.对于InnoDB引擎来说主要还是以innodb_开始的参数,也很好辨认. 查看MySQL参数,可以使用show variables和show status命令查看,前者查看服务器静态参数,即在数据库启动后不会动态更改的值,比如缓冲区.字符集等.后

  • 优化mysql之key_buffer_size设置

    key_buffer_size key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度.通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理.比例key_reads /key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE 'key_read%'获得). key_buffer_size只对MyISAM表起作用.

  • MySQL的查询缓存和Buffer Pool

    一.Caches - 查询缓存 下图是MySQL官网给出的:MySQL架构体系图. 人们常说的查询缓存就是下图中的Cache部分. 如果将MySQL分成 Server层和存储引擎层两大部分,那么Caches位于Server层. 另外你还得知道: 当一个SQL打向MySQL Server之后,MySQL Server首选会从查询缓存中查看是否曾经执行过这个SQL,如果曾经执行过的话,之前执行的查询结果会以Key-Value的形式保存在查询缓存中.key是SQL语句,value是查询结果.我们将这个

  • mysqldump造成Buffer Pool污染的研究

    前言: 最近Oracle MySQL在其官方Blog上贴出了 5.6中一些变量默认值的修改.其中innodb_old_blocks_time 的默认值从0替换成了1000(即1s) 关于该参数的作用摘录如下: how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be moved to the new subl

  • mysql Key_buffer_size参数的优化设置

    先来看看document对这个参数的解释: 缓存myisam表的索引块大小,可以被所有进程所共享.当设置key_buffer_size,操作系统不会马上分配key_buffer_size设置的值,而是在需要的时候,再分配的.可以设置多个key_buffer,当设置不是默认key_buffer为0时,mysql会把缓存的索引块移到默认的key_buffer中去并删除不再使用的索引块.Myisam表中只能cache索引块,不能cache数据块. 原本描述: Index blocks for MyIS

  • mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size的解决方法

    今天在检查mysql服务器的时候提示Sort aborted: Out of sort memory, consider increasing server sort buffer size,安装字面意思就是 sort内存溢出,考虑增加服务器的排序缓冲区(sort_buffer_size)大小 sort_buffer_size=3M join_buffer_size = 3M 下面是针对16G 内存设置的参数: sort_buffer_size = 2M # Sort_Buffer_Size 是

  • mysql read_buffer_size 设置多少合适

    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 458624 K read_buffer_size:是MySQL读入缓冲区大小.对表进行顺序扫描的请求将分配一个读入缓冲区,MySQL会为它分配一段内存缓冲区.read_buffer_size变量控制这一缓冲区的大小.如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能. 如下是对于16g内存

  • MySQL Innodb关键特性之插入缓冲(insert buffer)

    什么是insert buffer? 插入缓冲,也称之为insert buffer,它是innodb存储引擎的关键特性之一,我们经常会理解插入缓冲时缓冲池的一个部分,这样的理解是片面的,insert buffer的信息一部分在内存中,另外一部分像数据页一样,存在于物理页中. 在innodb中,我们知道,如果一个表有自增主键,那么对于这个表的默认插入是非常快的,注意,这里的主键是自增的,如果不是自增的,那么这个插入将会变成随机的,就可能带来数据页分裂的开销,这样,插入就不是顺序的,就会变慢.还有一种

随机推荐