MySQL：查詢字段數量多少對查詢效率的影響

這個問題是最近在分析一個SQL時發起的疑問，然後請教高鵬(重慶八怪)老師，八怪老師予以解答，特此轉載老師的文章(本文内容格式略有調整)。

源碼版本：5.7.22

一、問題由來

我們知道執行計劃的不同肯定會帶來效率的不同，但是在本例中執行計劃完全一緻，都是全表掃描，不同的隻有字段個數而已。其次，測試中都使用了where條件進行過濾（Using where），過濾後沒有資料傳回，我們常說的where過濾實際上是在MySQL層，當然某些情況下使用ICP會提前在Innodb層過濾資料，這裡我們先不考慮ICP，我會在後面的文章中較長的描述ICP的流程，本文也會給出where過濾的接口，供大家參考。

下面的截圖來自兩個朋友，感謝他們的測試和問題提出。另外對于大資料量通路來講可能涉及到實體IO，首次通路和随後的通路因為Innodb buffer的關系，效率不同是正常，需要多測試幾次。

我們通過上面的測試，可以發現随着字段的不斷減少，效率越來越高，并且主要的差別都在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

MySQL：查詢字段數量多少對查詢效率的影響

一、問題由來

二、全表通路資料的流程

三、回到問題本身

四、寫在最後

五、備用棧幀

繼續閱讀

SQL優化SQL語句優化的目的

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離