MySQL5.6 Internals隐藏的索引列

MySQL5.6 Internals-隐藏的索引列

Louis Hust

 

0  前言

今天本来想跟踪MySQL5.6中的新特性Index Merge，结果在跟踪的过程中，发现了一个问题，即InnoDB的二级索引中 可能会包含主索引，当然这里的包含并不是说二级索引的row里面会有pk的记录，这一点是一直存在的，这里的包含 是指，二级索引也会包含主索引进行排序。

 

1  现场重现

1.1  初始化数据

mysql> show create table index_merge\G
*************************** 1. row ***************************
       Table: index_merge
Create Table: CREATE TABLE `index_merge` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create procedure fill_index_merge\G
*************************** 1. row ***************************
           Procedure: fill_index_merge
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `fill_index_merge`(c1_cnt int, c2_cnt int)
begin
declare i,j int default 1;
repeat
   repeat
      insert into index_merge(c2,c3) values(j, repeat('a', 64));
      set j = j+1;
      until j > c2_cnt
   end repeat;
set i = i + 1;
set j = 1;
until i > c1_cnt
end repeat;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> call fill_index_merge(100, 100);

 

1.2  查看计划

mysql> explain select * from index_merge where c1 < 100 and c2 = 50\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index_merge
         type: range
possible_keys: PRIMARY,c2
          key: c2
      key_len: 9
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

可以看到，计划中竟然是range的查询，我的第一直觉应该是ref const的查询，但是确实是range，使用c2的range， 但是c2的key_len竟然是9，我去，各种疑惑啊，于是跟踪计划生成，发现key有两个(PRIMARY, c2)，但是c2的index上的列确有两列，(c2,c1)。 瞬间凌乱了，竟然隐式的修改我的index，还把用户蒙在鼓里。

 

2  原因分析

由上面的分析，可以知道c2的index在生成时，其实被MySQL隐式修改为(c2,c1)的index了，那我们就看下代码吧。

 

一开始我以为这些修改是在创建表的时候就隐式的修改了，我又错了，木有，看遍了mysql_create_frm()都没找到哪里修改了， frm文件还是那个frm文件。

 

OK，不是创建的时候修改，那只能是加载frm时候修改了，也就是内存的修改。看看数据字典的加载函数open_binary_frm()，bingo， 5.6相比5.5确实做了修改，重点代码如下：

 

open_binary_frm()
{
  ...
  use_extended_sk= (legacy_db_type == DB_TYPE_INNODB);
  ...
  for (i=0 ; i < keys ; i++, keyinfo++)
  {
    ...
    for (j=keyinfo->user_defined_key_parts ; j-- ; key_part++)
    {
      ...
      /*
      Add PK parts if engine supports PK extension for secondary keys.
      Atm it works for Innodb only. Here we add unique first key parts
      to the end of secondary key parts array and increase actual number
      of key parts. Note that primary key is always first if exists.
      Later if there is no PK in the table then number of actual keys parts
      is set to user defined key parts.
      */
      keyinfo->actual_key_parts= keyinfo->user_defined_key_parts;
      keyinfo->actual_flags= keyinfo->flags;
      if (use_extended_sk && i && !(keyinfo->flags & HA_NOSAME))
      {
        add_pk_parts_to_sk(keyinfo, share->key_info,
        &key_part, &rec_per_key);
      }
      share->key_parts+= keyinfo->hidden_key_parts;
    }
    ...
  }
  ...
}

 

重点来了，首先只有InnoDB引擎才支持隐式修改second index，然后是通过add_pk_parts_to_sk()函数将pk的列 加入到second index中，当然加入过程中有些限制，如对key中可列数和key的长度的限制。

 

3  总结

InnoDB引擎原本的二级索引中的记录就会包含pk的列，之前只是为了通过二级索引去定位主索引，也就时pk和second key之间数据 的一一映射，并没有别的用途，现在增加了在sk中对pk的排序，可以说是在没有增加存储开销的情况下，使得记录有序性更强，也就是 更加有利于最优计划的生成。当然代价可能就是插入时候key compare的时间会稍微变长。

 

总的来说，InnoDB在不断完善，Oracle也可以说是功不可没吧，虽然我个人很讨厌Oracle现在把MySQL搞的没什么活力了，很多东西不再那么open了， 比如mysql-test的test case，bug系统等，希望Oracle能把MySQL做的更好吧。

 

---

File translated from T_EX by T_TH, version 4.03.
On 27 Jan 2013, 16:51.