MySQL:查询字段数量多少对查询效率的影响

  这个问题是最近在分析一个SQL时发起的疑问,然后请教高鹏(重庆八怪)老师,八怪老师予以解答,特此转载老师的文章(本文内容格式略有调整)。
源码版本:5.7.22

一、问题由来

  我们知道执行计划的不同肯定会带来效率的不同,但是在本例中执行计划完全一致,都是全表扫描,不同的只有字段个数而已。其次,测试中都使用了where条件进行过滤(Using where),过滤后没有数据返回,我们常说的where过滤实际上是在MySQL层,当然某些情况下使用ICP会提前在Innodb层过滤数据,这里我们先不考虑ICP,我会在后面的文章中详细描述ICP的流程,本文也会给出where过滤的接口,供大家参考。
  下面的截图来自两个朋友,感谢他们的测试和问题提出。另外对于大数据量访问来讲可能涉及到物理IO,首次访问和随后的访问因为Innodb buffer的关系,效率不同是正常,需要多测试几次。
MySQL:查询字段数量多少对查询效率的影响
MySQL:查询字段数量多少对查询效率的影响
MySQL:查询字段数量多少对查询效率的影响
MySQL:查询字段数量多少对查询效率的影响
  我们通过上面的测试,可以发现随着字段的不断减少,效率越来越高,并且主要的区别都在sending data下面,send data这个状态是select语句才会有的,如果是DML则不同但是都有等同的阶段如下:
select:Sending data
insert语句:Update
delete/update:Updating
这个阶段非常的巨大,它至少包含了:
Innodb 层数据的定位返回给MySQL 层
Innodb 层数据的查询返回给MySQL 层
Innodb 层数据的修改(如果是MDL)
Innodb 层加锁以及等待
等待进入Innodb层(innodb_thread_concurrency参数)
MySQL 层发送数据给客户端
  这个状态我曾经大概描述过参考文章:

https://www.jianshu.com/p/46ad0aaf7ed7
https://www.jianshu.com/p/4cdec711adef

  简单的说Innodb数据的获取和Innodb数据到MySQL层数据的传递都包含在其中。

二、全表访问数据的流程

  这里我将简单描述一下这种全表扫描的流程,实际上其中有一个核心接口就是row_search_mvcc,它大概包含了如下功能:

  • 通过预取缓存获取数据
  • 打开事务
  • 定位索引位置(包含使用AHI快速定位)
  • 是否开启readview
  • 通过持久化游标不断访问下一条数据
  • 加Innodb表锁、加Innodb行锁
  • 可见性判断
  • 根据主键回表(可能回表需要加行锁)
  • ICP优化
  • SEMI update优化

  并且作为访问数据的必须经历的接口,这个函数也是很值得大家细细研读的。
  下面我主要结合字段多少和全表扫描2个方面做一个简单的流程介绍。
1、通过select字段构建read_set(MySQL层)
  首先需要构建一个叫做read_set的位图,来表示访问的字段位置及数量。它和write set一起,在记录binlog的Event的时候也会起着重要作用,可以参考我的《深入理解MySQL主从原理》中关于binlog_row_image参数一节。这里构建的主要接口为TABLE::mark_column_used函数,每个需要访问的字段都会调用它来设置自己的位图。下面是其中的一段如下:

case MARK_COLUMNS_READ:
    bitmap_set_bit(read_set, field->field_index);

  从栈帧来看这个构建read_set的过程位于状态‘init’下面。栈帧见结尾栈帧1。
2、初次访问定位的时候还会构建一个模板(mysql_row_templ_t)(Innodb层)
  本模板主要用于当Innodb层数据到MySQL层做转换的时候使用,其中记录了使用的字段数量、字段的字符集、字段的类型等等。接口build_template_field用于构建这个模板。栈帧见结尾栈帧2。
  但是需要注意的是,这里构建模板就会通过我们上面说的read_set去判断到底有多少字段需要构建到模板中,然后才会调用build_template_field函数。如下是最重要的代码,它位于build_template_needs_field接口中。

bitmap_is_set(table->read_set, static_cast<uint>(i)

  可以看到这里正在测试本字段是否出现在了read_set中,如果不在则跳过这个字段。下面是函数build_template_needs_field的注释:

Determines if a field is needed in a m_prebuilt struct 'template'.
@return field to use, or NULL if the field is not needed */

  到这里我们需要访问的字段已经确立下来了。
3、初次定位数据,定位游标到主键索引的第一行记录,为全表扫描做好准备
  对于这种全表扫描的执行方式,定位数据就变得简单了,我们只需要找到主键索引的第一条数据就好了,它和平时我们使用(ref/range)定位方式不同,不需要二分法的支持。因此对于全表扫描的初次定位调用函数为btr_cur_open_at_index_side_func,而不是通常我们说的btr_pcur_open_with_no_init_func。
  如果大概看一下函数btr_cur_open_at_index_side_func的功能,我们很容易看到,它就是通过B+树结构,定位掉叶子结点的开头第一个块,然后调用函数page_cur_set_before_first,将游标放到了所有记录的开头,目的只有一个为全表扫描做好准备。栈帧见结尾栈帧3。
  注意这里正是通过我们row_search_mvcc调用下去的。
4、获取Innodb层的第一条数据(Innodb层)
  拿到了游标过后就可以获取数据了,这里也很简单代码就是一句如下:

rec = btr_pcur_get_rec(pcur);//获取记录 从持久化游标整行数据

  但是需要注意的是这里获取的数据只是一个指针,言外之意可以理解为整行数据,其格式也是原始的Innodb数据,其中还包含了一些伪列比如(rollback ptr和trx id)。这里实际上和访问的字段个数无关。
5、将第一行记录转换为MySQL格式(Innodb层)
  这一步完成后我们可以认为记录已经返回给了MySQL层,这里就是实际的数据拷贝了,并不是指针,整个过程放到了函数row_sel_store_mysql_rec中。
  我们前面的模板(mysql_row_templ_t)也会在这里发挥它的作用,这是一个字段过滤的过程,我们先来看一个循环
for (i = 0; i < prebuilt->n_template; i++),其中prebuilt->n_template就是字段模板的个数,我们前面已经说过了,通过read_set的过滤,对于我们不需要的字段是不会建立模板的。因此这里的模板数量是和我们访问的字段个数一样的。
  然后在这个循环下面会调用row_sel_store_mysql_field_func然后调用row_sel_field_store_in_mysql_format_func将字段一个一个转换为MySQL的格式。我们来看一下其中一种类型的转换如下:

    case DATA_INT:
        /* Convert integer data from Innobase to a little-endian
        format, sign bit restored to normal */

        ptr = dest + len;

        for (;;) {
            ptr--;
            *ptr = *data;//值拷贝 内存拷贝
            if (ptr == dest) {
                break;
            }
            data++;
        }

  我们可以发现这是一种实际的转换,也就是需要花费内存空间的。栈帧见结尾栈帧4。到这里我们大概知道了,查询的字段越多那么着这里转换的过程越长,并且这里都是实际的内存拷贝,最终这行数据会存储到row_search_mvcc的形参 buffer中返回给MySQL层,这个形参的注释如下:

@param[out] buf     buffer for the fetched row in MySQL format

6、对第一条数据进行where过滤(MySQL层)
  拿到数据后当然还不能作为最终的结果返回给用户,我们需要在MySQL层做一个过滤操作,这个条件比较位于函数evaluate_join_record的开头,其中比较就是下面一句话

found= MY_TEST(condition->val_int()); //进行比较 调用到 条件和 返回会记录的比较

  如果和条件不匹配将会返回False。这里比较会最终调用Item_func的各种方法,如果等于则是Item_func_eq,栈帧见结尾栈帧5。
7、访问下一条数据
  上面我已经展示了访问第一条数据的大体流程,接下面需要做的就是继续访问下去,如下:

移动游标到下一行
访问数据
根据模板转换数据返回给MySQL层
根据where条件过滤

  整个过程会持续到全部主键索引数据访问完成。但是需要注意的是上层接口有些变化,由ha_innobase::index_first会变为ha_innobase::rnd_next,统计数据由Handler_read_first变为Handler_read_rnd_next,这点可以参考我的文章:
https://www.jianshu.com/p/25fed8f1f05e
  并且row_search_mvcc的流程肯定也会有变化。这里不再熬述。但是实际的获取数据转换过程和过滤过程并没有改变。注意这些步骤除了步骤1,基本都处于sending data下面。

三、回到问题本身

  好了到这里我们大概知道全表扫描的访问数据的流程了,我们就来看看一下在全表扫描流程中字段的多少到底有哪些异同点:
不同点:

  • 构建的read_set不同,字段越多read_set中为‘1’的位数越多
  • 建立的模板不同,字段越多模板数量越多
  • 每行数据转换为MySQL格式的时候不同,字段越多模板越多,那么循环转换每个字段的循环次数也就越多,并且这是每行都要处理的。返回给MySQL层的行内存消耗越大。
    相同点:
  • 访问的行数一致
  • 访问的流程一致
  • where过滤的方式一致
      在整个不同点中,我认为最耗时的部分应该是每行数据转换为MySQL格式的消耗最大,因为每行每个字段都需要做这样的转换,这也刚好是除以sending data状态下面。我们线上大于10个字段的表比比皆是,如果我们只需要访问其中的少量字段,我们最好还是写实际的字段而不是‘*’,来规避这个问题。

四、写在最后

  虽然本文中以全表扫描为列进行了解释,但是实际上任何情况下我们都应该缩减访问字段的数量,应该只访问需要的字段。

五、备用栈帧

  栈帧1 read_set构建

#0  TABLE::mark_column_used (this=0x7ffe7c996c50, thd=0x7ffe7c000b70, field=0x7ffe7c997c88, mark=MARK_COLUMNS_READ)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/table.cc:6344
#1  0x00000000015449b4 in find_field_in_table_ref (thd=0x7ffe7c000b70, table_list=0x7ffe7c0071f0, name=0x7ffe7c006a38 "id", length=2, item_name=0x7ffe7c006a38 "id", 
    db_name=0x0, table_name=0x0, ref=0x7ffe7c006bc0, want_privilege=1, allow_rowid=true, cached_field_index_ptr=0x7ffe7c0071a0, register_tree_change=true, 
    actual_table=0x7fffec0f46d8) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_base.cc:7730
#2  0x0000000001544efc in find_field_in_tables (thd=0x7ffe7c000b70, item=0x7ffe7c0070c8, first_table=0x7ffe7c0071f0, last_table=0x0, ref=0x7ffe7c006bc0, 
    report_error=IGNORE_EXCEPT_NON_UNIQUE, want_privilege=1, register_tree_change=true) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_base.cc:7914
#3  0x0000000000faadd8 in Item_field::fix_fields (this=0x7ffe7c0070c8, thd=0x7ffe7c000b70, reference=0x7ffe7c006bc0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/item.cc:5857
#4  0x00000000015478ee in setup_fields (thd=0x7ffe7c000b70, ref_pointer_array=..., fields=..., want_privilege=1, sum_func_list=0x7ffe7c005d90, allow_sum_func=true, 
    column_update=false) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_base.cc:9047
#5  0x000000000161419d in st_select_lex::prepare (this=0x7ffe7c005c30, thd=0x7ffe7c000b70) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_resolver.cc:190

  栈帧2 构建模板

#0  build_template_field (prebuilt=0x7ffe7c99b880, clust_index=0x7ffe7c999c20, index=0x7ffe7c999c20, table=0x7ffe7c996c50, field=0x7ffe7c997c88, i=0, v_no=0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:7571
#1  0x00000000019d1dc1 in ha_innobase::build_template (this=0x7ffe7c997610, whole_row=false)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:8034
#2  0x00000000019d60f5 in ha_innobase::change_active_index (this=0x7ffe7c997610, keynr=0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9805
#3  0x00000000019d682b in ha_innobase::rnd_init (this=0x7ffe7c997610, scan=true)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:10031
#4  0x0000000000f833b9 in handler::ha_rnd_init (this=0x7ffe7c997610, scan=true) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:3096
#5  0x00000000014e24d1 in init_read_record (info=0x7ffe7cf47d60, thd=0x7ffe7c000b70, table=0x7ffe7c996c50, qep_tab=0x7ffe7cf47d10, use_record_cache=1, 
    print_error=true, disable_rr_cache=false) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/records.cc:315

  栈帧3 全表扫描初次定位栈帧

#0  page_cur_set_before_first (block=0x7fff4d02f4a0, cur=0x7ffe7c99bab0) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/include/page0cur.ic:99
#1  0x0000000001c5187f in btr_cur_open_at_index_side_func (from_left=true, index=0x7ffe7c999c20, latch_mode=1, cursor=0x7ffe7c99baa8, level=0, 
    file=0x239d388 "/root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/include/btr0pcur.ic", line=562, mtr=0x7fffec0f3570)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/btr/btr0cur.cc:2422
#2  0x0000000001b6e9c9 in btr_pcur_open_at_index_side (from_left=true, index=0x7ffe7c999c20, latch_mode=1, pcur=0x7ffe7c99baa8, init_pcur=false, level=0, 
    mtr=0x7fffec0f3570) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/include/btr0pcur.ic:562
#3  0x0000000001b79a35 in row_search_mvcc (buf=0x7ffe7c997b50 "\377", mode=PAGE_CUR_G, prebuilt=0x7ffe7c99b880, match_mode=0, direction=0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:5213
#4  0x00000000019d5493 in ha_innobase::index_read (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9536
#5  0x00000000019d66ea in ha_innobase::index_first (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\377")
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9977
#6  0x00000000019d6934 in ha_innobase::rnd_next (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\377")
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:10075
#7  0x0000000000f83725 in handler::ha_rnd_next (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\377")
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:3146
#8  0x00000000014e2b3d in rr_sequential (info=0x7ffe7cf47d60) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/records.cc:521

  栈帧4 MySQL格式的转换

#0  row_sel_field_store_in_mysql_format_func (dest=0x7ffe7c997b51 "", templ=0x7ffe7c9a27f8, index=0x7ffe7c999c20, field_no=0, data=0x7fff4daec0a1 "\200", len=4, 
    prebuilt=0x7ffe7c99b880, sec_field=18446744073709551615) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:2888
#1  0x0000000001b754b9 in row_sel_store_mysql_field_func (mysql_rec=0x7ffe7c997b50 "\377", prebuilt=0x7ffe7c99b880, rec=0x7fff4daec0a1 "\200", index=0x7ffe7c999c20, 
    offsets=0x7fffec0f3a80, field_no=0, templ=0x7ffe7c9a27f8, sec_field_no=18446744073709551615)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:3255
#2  0x0000000001b75c85 in row_sel_store_mysql_rec (mysql_rec=0x7ffe7c997b50 "\377", prebuilt=0x7ffe7c99b880, rec=0x7fff4daec0a1 "\200", vrow=0x0, rec_clust=0, 
    index=0x7ffe7c999c20, offsets=0x7fffec0f3a80, clust_templ_for_sec=false) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:3434
#3  0x0000000001b7bd61 in row_search_mvcc (buf=0x7ffe7c997b50 "\377", mode=PAGE_CUR_G, prebuilt=0x7ffe7c99b880, match_mode=0, direction=0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0sel.cc:6123
#4  0x00000000019d5493 in ha_innobase::index_read (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\377", key_ptr=0x0, key_len=0, find_flag=HA_READ_AFTER_KEY)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9536
#5  0x00000000019d66ea in ha_innobase::index_first (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\377")
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9977
#6  0x00000000019d6934 in ha_innobase::rnd_next (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\377")
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:10075
#7  0x0000000000f83725 in handler::ha_rnd_next (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "\377")
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:3146
#8  0x00000000014e2b3d in rr_sequential (info=0x7ffe7cf47d60) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/records.cc:521
#9  0x0000000001584264 in join_init_read_record (tab=0x7ffe7cf47d10) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2487
#10 0x0000000001581349 in sub_select (join=0x7ffe7cf47660, qep_tab=0x7ffe7cf47d10, end_of_records=false)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1277
#11 0x0000000001580cce in do_select (join=0x7ffe7cf47660) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950

  栈帧5 String的等值比较

#0  Arg_comparator::compare_string (this=0x7ffe7c0072f0) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/item_cmpfunc.cc:1669
#1  0x0000000000fde1e4 in Arg_comparator::compare (this=0x7ffe7c0072f0) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/item_cmpfunc.h:92
#2  0x0000000000fcb0a1 in Item_func_eq::val_int (this=0x7ffe7c007218) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/item_cmpfunc.cc:2507
#3  0x0000000001581af9 in evaluate_join_record (join=0x7ffe7c0077d8, qep_tab=0x7ffe7cb1dc70)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1492
#4  0x000000000158145a in sub_select (join=0x7ffe7c0077d8, qep_tab=0x7ffe7cb1dc70, end_of_records=false)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1297
#5  0x0000000001580cce in do_select (join=0x7ffe7c0077d8) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950
#6  0x000000000157eb8a in JOIN::exec (this=0x7ffe7c0077d8) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199
上一篇:《C++游戏编程入门(第4版)》——2.4 使用带else子句的if语句序列


下一篇:视觉冲击!12个精美绝伦的扁平化网站设计