一、問題描述

最近遇到一個(gè)問題，也就是使用分區(qū)表進(jìn)行數(shù)據(jù)查詢/加載的時(shí)候比普通表的性能下降了約50%，主要瓶頸出現(xiàn)在CPU，既然是CPU瓶頸理所當(dāng)然的我們可以采集perf top -a -g和pstack來尋找性能瓶頸所在，同時(shí)和普通表進(jìn)行對(duì)比，發(fā)現(xiàn)CPU主要耗在函數(shù)build_template_field上如下圖：

二、使用pt-pmap進(jìn)行棧分析

為了和perf top -g -a進(jìn)行相互印證，我們同時(shí)獲取了當(dāng)時(shí)的pstack，由于線程較多為了方便獲取有用的信息我們通過pt-pmap進(jìn)行了格式化如下：

格式化后我們提出掉空閑的等待棧，發(fā)現(xiàn)大量的如上，這也和perf top -a -g中的表現(xiàn)進(jìn)行了相互印證。

三、關(guān)于本列中瓶頸點(diǎn)的分析

我們看到這里大量的cpu耗在

ha_innobase::build_template
 ->build_template_field
   ->dict_col_get_clust_pos

對(duì)于template來講，其幾乎是和特定的一次的查詢進(jìn)行綁定的，也就是普通的語句至少需要一個(gè)template。其結(jié)構(gòu)為row_prebuilt_t，包含查詢?cè)M，查詢的表，查詢用到的索引，事務(wù)相關(guān)信息，持久化游標(biāo)，MySQL層查詢行的長度，自增信息，ICP相關(guān)信息，mysql_row_templ_t結(jié)構(gòu)等信息。其中mysql_row_templ_t 這個(gè)信息就是每個(gè)字段一個(gè)，主要作用記錄的是MySQL層feild信息和Innodb層columns信息的相關(guān)屬性，用于快速轉(zhuǎn)換一行記錄在MySQL層和Innodb層之間轉(zhuǎn)換。為了初始化mysql_row_templ_t 就出現(xiàn)了上面的邏輯，

大概邏輯如下：

循環(huán)表中每個(gè)字段（一層循環(huán)）ha_innobase::build_template
  是否為需要訪問的字段 build_template_needs_field
  這里包含查詢和寫入的所有字段，需要訪問的字段越多越慢
   如果不是則不作繼續(xù)循環(huán)
  如果需要訪問
  build_template_field（mysql_row_templ_t結(jié)構(gòu)體填充）
    循環(huán)主鍵的每個(gè)字段（二層循環(huán)）
    包含偽列,主鍵就是表的里面全部字段，表中字段越多越慢)dict_col_get_clust_pos  
      確認(rèn)本字段在主鍵的位置
      pos0 主鍵 pos1 DB_TRX_ID pos2 DB_ROLL_PTR pos3 開始為用戶其他字段
    循環(huán)索引的每個(gè)字段（二層循環(huán)，但是索引字段一般不會(huì)太多，因此這里不會(huì)慢）dict_index_t::get_col_pos
      確認(rèn)本字段在索引的位置，如果沒有則返回NULL
      返回pos 比如 主鍵 id1  二級(jí)索引 id2 id3 二級(jí)索引為 pos0 id2 pos1 id3 pos2 id1
    繼續(xù)完成其他屬性比如mysql null位圖，mysql顯示長度，mysql字符集等等

這里我們看到這里實(shí)際上有2層循環(huán)，也就是循環(huán)套循環(huán)（時(shí)間復(fù)雜度O(M×N)），而循環(huán)影響最大的有2個(gè)地方：

• 第一層，表中字段的多少
• 第二層，需要訪問的字段（讀和寫都算）在主鍵（也就是全部字段）中循環(huán)

這里也就是為什么這里會(huì)慢的原因。但是template通常不會(huì)一個(gè)查詢進(jìn)行多次建立，比如一個(gè)普通表的大查詢，只有在語句第一次進(jìn)行數(shù)據(jù)定位之前會(huì)進(jìn)行建立，這就不得不說這是分區(qū)表和普通表的對(duì)比中一個(gè)特殊的地方了。下面描述一下。

四、分區(qū)表中多次建立template的情況

假設(shè)我們有如下的分區(qū)表：

create table t(
    id1 int,
    id2 int,
    primary key(id1),
    key(id2)
)engine=innodb
partition by range(id1)(
    partition p0 values less than(100),
    partition p1 values less than(200),
    partition p2 values less than(300));    

insert into t values(1,1);
insert into t values(101,1);
insert into t values(201,1);
insert into t values(2,2);
insert into t values(3,2);
insert into t values(4,2);
insert into t values(7,2);
insert into t values(8,2);
insert into t values(9,2);
insert into t values(10,2);

我們使用語句"select * from t where id2=1"，顯然id2是二級(jí)索引，由于MySQL全部都是local分區(qū)的二級(jí)索引，因此這里值分別分布在3個(gè)分區(qū)中，對(duì)于這樣一個(gè)語句在本該是普通表通過上次定位后的位置繼續(xù)訪問（next_same）的時(shí)候，通過封裝分區(qū)表的方法，將其改為了index read再次定位，而我們可以清楚的看到這里是scan next partition，其part=1這是第二個(gè)分區(qū)了，也就是我們的p1（第一個(gè)為0）

這樣template需要每個(gè)分區(qū)（scan next partition）都進(jìn)行重建，這樣就出現(xiàn)了我們上面的問題。這個(gè)其實(shí)也可以理解，新的分區(qū)是新的innodb文件，這樣上次定位的持久化游標(biāo)實(shí)際已經(jīng)沒有什么用了，就相當(dāng)于一次新的表訪問。這里在是否進(jìn)行template建立還有一個(gè)判斷如下：

  if (m_prebuilt->sql_stat_start) {
    build_template(false);
  }

而m_prebuilt->sql_stat_start除了在語句開始的時(shí)候設(shè)置為true，每次更換分區(qū)依舊會(huì)設(shè)置為true如下：

ha_innopart::set_partition:
m_prebuilt->sql_stat_start = m_sql_stat_start_parts.test(part_id);

五、關(guān)于一個(gè)特殊的流程

在我們的故障pstack中還有一個(gè)棧如下：

這個(gè)棧實(shí)際并不完整，但是其中出現(xiàn)了Partition_helper::handle_ordered_index_scan，這個(gè)函數(shù)實(shí)際上和分區(qū)表的排序有關(guān)，如果我們考慮這樣一種情況，對(duì)于二級(jí)索引select max(id2) from t，那么需要首先訪問每個(gè)分區(qū)獲取其中的最大值然后對(duì)比每個(gè)分區(qū)的最大值，得到最終的結(jié)果，而MySQL則采用優(yōu)先隊(duì)列進(jìn)行處理，這應(yīng)該是就是本函數(shù)完成的部分功能（沒仔細(xì)去看）。其次我們先出現(xiàn)了QUICK_RANGE_SELECT這是范圍查詢會(huì)用到的，那么我們構(gòu)造如下：

select * from t where id2<2 order by id2;

棧：

這里就是因?yàn)閕d2這個(gè)字段只保證在分區(qū)內(nèi)部是按照大小排列的但是在整個(gè)表來講，它是無序的，需要額外的處理。

六、問題模擬

有了這些準(zhǔn)備，我們可以構(gòu)造一個(gè)300個(gè)字段和25個(gè)分區(qū)的分區(qū)表。測試版本最新8.0.26

create table tpar300col(
    id1 int,
    id2 int,
    id3 int,
    id4 int,
...
    id299 varchar(20),
    id300 varchar(20),
    primary key(id1),
    key(id2)
)engine=innodb
partition by range(id1)(
    partition p0 values less than(100),
    partition p1 values less than(200),
    partition p3 values less than(300),
 ...
    partition p25 values less than(2500));  

insert into tpar300col values(1  ,1,1,
....每個(gè)分區(qū)一條數(shù)據(jù)
insert into tpar300col values(2401,1,1

然后構(gòu)造一些其他數(shù)據(jù)id2不要為1，建立存儲(chǔ)過程：

delimiter //

CREATE PROCEDURE test300col()
begin 
  declare num int;
  set num = 1; 
while num <= 1000000 do
  select * from tpar300col where id2=1;
  set num = num+1;
end while;
end //
執(zhí)行：
 /opt/mysql/mysql3340/install/mysql8/bin/mysql -S--socket=/opt/mysql/mgr3315/data/mgr3315.sock -e'use test;call test300col();' > log.log

然后perf top 觀察如下：

這樣問題就得到了確認(rèn)。

七、總結(jié)

這個(gè)問題實(shí)際上和二級(jí)索引相對(duì)于分區(qū)鍵的數(shù)據(jù)離散度有關(guān)，但是我們無法控制二級(jí)索引的數(shù)據(jù)，并且索引也是必須使用的。只能通過一些方面盡量避免，當(dāng)然我也提交了一個(gè)BUG，如下：

https://bugs.mysql.com/bug.php?id=104576

不知道是否有辦法修復(fù)這個(gè)問題，比如對(duì)于分區(qū)表來講實(shí)際上每個(gè)分區(qū)的字段都是一樣的，是否需要每次都重建mysql_row_templ_t.clust_rec_field_no？如果不需要那么問題自解，官方目前已經(jīng)驗(yàn)證了這個(gè)問題確實(shí)存在。如下是一些避免的方式，

• 分區(qū)表字段不宜過多
• 訪問的字段不應(yīng)該一味的使用select *
• 避免使用hash分區(qū)，hash分區(qū)會(huì)增加這種問題

到此這篇關(guān)于講訴MySQL分區(qū)表的一個(gè)性能BUG的文章就介紹到這了,更多相關(guān)MySQL分區(qū)表的一個(gè)性能BUG內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論