（转）前端开发之 MySQL 分区表中的性能 BUG

​

一、问题描述

最近遇到一个问题，也就是使用分区表进行数据查询/加载的时候比普通表的性能下降了约 50%，主要瓶颈出现在 CPU，既前端培训然是 CPU 瓶颈理所当然的我们可以采集 perf top -a -g 和 pstack 来寻找性能瓶颈所在，同时和普通表进行对比，发现 CPU 主要耗在函数 build_template_field 上如下图：

二、使用 pt-pmap 进行栈分析

为了和 perf top -g -a 进行相互印证，我们同时获取了当时的 pstack，由于线程较多为了方便获取有用的信息我们通过 pt-pmap 进行了格式化如下：

格式化后我们提出掉空闲的等待栈，发现大量的如上，这也和 perf top -a -g 中的表现进行了相互印证。

三、关于本列中瓶颈点的分析

我们看到这里大量的 cpu 耗在

ha_innobase::build_template ->build_template_field   ->dict_col_get_clust_pos

对于 template 来讲，其几乎是和特定的一次的查询进行绑定的，也就是普通的语句至少需要一个 template。其结构为 row_prebuilt_t，包含查询元组，查询的表，查询用到的索引，事务相关信息，持久化游标，MySQL 层查询行的长度，自增信息，ICP 相关信息，mysql_row_templ_t 结构等信息。其中 mysql_row_templ_t 这个信息就是每个字段一个，主要作用记录的是 MySQL 层 feild 信息和 Innodb 层 columns 信息的相关属性，用于快速转换一行记录在 MySQL 层和 Innodb 层之间转换。为了初始化 mysql_row_templ_t 就出现了上面的逻辑，大概逻辑如下：

循环表中每个字段（一层循环）ha_innobase::build_template  是否为需要访问的字段 build_template_needs_field  这里包含查询和写入的所有字段，需要访问的字段越多越慢   如果不是则不作继续循环  如果需要访问  build_template_field（mysql_row_templ_t结构体填充）    循环主键的每个字段（二层循环）    包含伪列,主键就是表的里面全部字段，表中字段越多越慢)dict_col_get_clust_pos        确认本字段在主键的位置       pos0 主键 pos1 DB_TRX_ID pos2 DB_ROLL_PTR pos3 开始为用户其他字段    循环索引的每个字段（二层循环，但是索引字段一般不会太多，因此这里不会慢）dict_index_t::get_col_pos      确认本字段在索引的位置，如果没有则返回NULL      返回pos 比如 主键 id1  二级索引 id2 id3 二级索引为 pos0 id2 pos1 id3 pos2 id1    继续完成其他属性比如mysql null位图，mysql显示长度，mysql字符集等等

这里我们看到这里实际上有 2 层循环，也就是循环套循环（时间复杂度 O(M×N)），而循环影响最大的有 2 个地方：

• 第一层，表中字段的多少

• 第二层，需要访问的字段（读和写都算）在主键（也就是全部字段）中循环

这里也就是为什么这里会慢的原因。但是 template 通常不会一个查询进行多次建立，比如一个普通表的大查询，只有在语句第一次进行数据定位之前会进行建立，这就不得不说这是分区表和普通表的对比中一个特殊的地方了。下面描述一下。

四、分区表中多次建立 template 的情况

假设我们有如下的分区表：

create table t(    id1 int,    id2 int,    primary key(id1),    key(id2))engine=innodbpartition 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 是二级索引，由于 MySQL 全部都是 local 分区的二级索引，因此这里值分别分布在 3 个分区中，对于这样一个语句在本该是普通表通过上次定位后的位置继续访问（next_same）的时候，通过封装分区表的方法，将其改为了 index read 再次定位，而我们可以清楚的看到这里是 scan next partition，其 part=1 这是第二个分区了，也就是我们的 p1（第一个为 0）

这样 template 需要每个分区（scan next partition）都进行重建，这样就出现了我们上面的问题。这个其实也可以理解，新的分区是新的 innodb 文件，这样上次定位的持久化游标实际已经没有什么用了，就相当于一次新的表访问。这里在是否进行 template 建立还有一个判断如下：

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

而 m_prebuilt->sql_stat_start 除了在语句开始的时候设置为 true，每次更换分区依旧会设置为 true 如下：

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

五、关于一个特殊的流程

在我们的故障 pstack 中还有一个栈如下：

这个栈实际并不完整，但是其中出现了 Partition_helper::handle_ordered_index_scan，这个函数实际上和分区表的排序有关，如果我们考虑这样一种情况，对于二级索引 select max(id2) from t，那么需要首先访问每个分区获取其中的最大值然后对比每个分区的最大值，得到最终的结果，而 MySQL 则采用优先队列进行处理，这应该是就是本函数完成的部分功能（没仔细去看）。其次我们先出现了 QUICK_RANGE_SELECT 这是范围查询会用到的，那么我们构造如下：

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

栈：

这里就是因为 id2 这个字段只保证在分区内部是按照大小排列的但是在整个表来讲，它是无序的，需要额外的处理。

六、问题模拟

有了这些准备，我们可以构造一个 300 个字段和 25 个分区的分区表。测试版本最新 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=innodbpartition 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,....每个分区一条数据insert into tpar300col values(2401,1,1然后构造一些其他数据id2不要为1，建立存储过程：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 //执行： /opt/mysql/mysql3340/install/mysql8/bin/mysql -S--socket=/opt/mysql/mgr3315/data/mgr3315.sock -e'use test;call test300col();' > log.log

然后 perf top 观察如下：

这样问题就得到了确认。

七、总结

这个问题实际上和二级索引相对于分区键的数据离散度有关，但是我们无法控制二级索引的数据，并且索引也是必须使用的。只能通过一些方面尽量避免，当然我也提交了一个 BUG，如下：

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

不知道是否有办法修复这个问题，比如对于分区表来讲实际上每个分区的字段都是一样的，是否需要每次都重建 mysql_row_templ_t.clust_rec_field_no？如果不需要那么问题自解，官方目前已经验证了这个问题确实存在。如下是一些避免的方式，

• 分区表字段不宜过多

• 访问的字段不应该一味的使用 select *

• 避免使用 hash 分区，hash 分区会增加这种问题

转载于老叶茶馆

​