抑郁症健康,内容丰富有趣,生活中的好帮手!
抑郁症健康 > 多少行数_技术分享 | MySQL:查询字段数量多少对查询效率的影响

多少行数_技术分享 | MySQL:查询字段数量多少对查询效率的影响

时间:2020-08-12 03:24:17

相关推荐

作者:高鹏

文章末尾有他著作的《深入理解 MySQL 主从原理 32 讲》,深入透彻理解 MySQL 主从,GTID 相关技术知识。

这个问题是最近一个朋友问我的。刚好就好好看了一下,留下这样的记录。本文给出一些函数接口,末尾给出一些调用堆栈,为感兴趣的朋友做一个参考,也为自己做一个笔记。

一、问题由来

我们知道执行计划的不同肯定会带来效率的不同,但是在本例中执行计划完全一致,都是全表扫描,不同的只有字段个数而已。其次,测试中都使用了where 条件进行过滤(Using where),过滤后没有数据返回,我们常说的 where 过滤实际上是在 MySQL 层,当然某些情况下使用 ICP 会提前在 Innodb 层过滤数据,这里我们先不考虑 ICP,我会在后面的文章中详细描述 ICP 的流程,本文也会给出 where 过滤的接口,供大家参考。下面的截图来自两个朋友,感谢他们的测试和问题提出。另外对于大数据量访问来讲可能涉及到物理 IO,首次访问和随后的访问因为 Innodb buffer 的关系,效率不同是正常,需要多测试几次。

测试1:

测试2:

我们通过这两个测试,可以发现随着字段的不断减少,效率越来越高,并且主要的区别都在 sending data 下面,这个状态我曾经大概描述过参考文章:

/p/46ad0aaf7ed7/p/4cdec711adef

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

二、简单的流程介绍

下面我主要结合字段多少和全表扫描2个方面做一个简单的流程介绍。实际上其中有一个核心接口就是 row_search_mvcc,它大概包含了如下功能:

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

并且作为访问数据的必须经历的接口,这个函数也是很值得大家细细研读的。

1. 通过 select 字段构建 readset(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. 初次定位数据,定位游标到主键索引的第一行记录,为全表扫描做好准备(Innodb 层)

对于这种全表扫描的执行方式,定位数据就变得简单了,我们只需要找到主键索引的第一条数据就好了,它和平时我们使用(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->ntemplate; 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-endianformat, 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] bufbuffer 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,这点可以参考我的文章:

/p/25fed8f1f05e

并且 row_search_mvcc 的流程肯定也会有变化。这里不再赘述。但是实际的获取数据转换过程和过滤过程并没有改变。

注意了这些步骤除了步骤1,基本都处于 sending data 下面。

三、回到问题本身

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

不同点:

构建的 read_set 不同,字段越多 read_set 中为 '1' 的位数越多建立的模板不同,字段越多模板数量越多每行数据转换为 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/: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/: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/: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/: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/:9047#5 0x000000000161419d in st_select_lex::prepare (this=0x7ffe7c005c30, thd=0x7ffe7c000b70) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/: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/: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/: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/: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/:10031#4 0x0000000000f833b9 in handler::ha_rnd_init (this=0x7ffe7c997610, scan=true) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/: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/: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/: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/: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/: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/: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/:10075#7 0x0000000000f83725 in handler::ha_rnd_next (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "377")at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/:3146#8 0x00000000014e2b3d in rr_sequential (info=0x7ffe7cf47d60) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/: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/: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/: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/: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/: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/: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/: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/:10075#7 0x0000000000f83725 in handler::ha_rnd_next (this=0x7ffe7c997610, buf=0x7ffe7c997b50 "377")at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/:3146#8 0x00000000014e2b3d in rr_sequential (info=0x7ffe7cf47d60) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/:521#9 0x0000000001584264 in join_init_read_record (tab=0x7ffe7cf47d10) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/: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/:1277#11 0x0000000001580cce in do_select (join=0x7ffe7cf47660) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/:950

栈帧5 String 的等值比较

#0 Arg_comparator::compare_string (this=0x7ffe7c0072f0) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/: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/:2507#3 0x0000000001581af9 in evaluate_join_record (join=0x7ffe7c0077d8, qep_tab=0x7ffe7cb1dc70)at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/: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/:1297#5 0x0000000001580cce in do_select (join=0x7ffe7c0077d8) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/:950#6 0x000000000157eb8a in JOIN::exec (this=0x7ffe7c0077d8) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/:199

最后推荐高鹏的专栏《深入理解 MySQL 主从原理 32 讲》,想要透彻了解学习 MySQL 主从原理的朋友不容错过。

如果觉得《多少行数_技术分享 | MySQL:查询字段数量多少对查询效率的影响》对你有帮助,请点赞、收藏,并留下你的观点哦!

本内容不代表本网观点和政治立场,如有侵犯你的权益请联系我们处理。
网友评论
网友评论仅供其表达个人看法,并不表明网站立场。