InnoDB一定会在索引中加上主键吗

9月 20th, 2012 | Posted by | Filed under 数据库

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/database/will_innodb_store_pk_in_index.html

DBA群里在讨论一个问题,到底InnoDB会不会在索引末尾加上主键,什么时候会加?

我之前看代码记得是如果索引末尾就是主键,那么InnoDB就不再添加主键了,如果索引末尾不是主键,那么会添加主键,但是这跟测试结果不符:

CREATE TABLE t (
  a char(32) not null primary key,
  b char(32) not null,
  KEY idx1 (a,b),
  KEY idx2 (b,a)
) Engine=InnoDB;

插入部分数据后可以看到idx1和idx2两个索引的大小相同。这说明idx1和idx2的内部结构是一样的,因此 不可能 是idx1在内部存为(a,b,a)。

在登博的指导下看了 dict0dict.cc:dict_index_build_internal_non_clust() 这个函数,就是构造索引的数据字典的过程,理解了这个过程就明白了,我们接下来解读下这个函数(基于5.6最近trunk):

2727 /*******************************************************************//**
2728 Builds the internal dictionary cache representation for a non-clustered
2729 index, containing also system fields not defined by the user.
2730 @return own: the internal representation of the non-clustered index */
2731 static
2732 dict_index_t*
2733 dict_index_build_internal_non_clust(
2734 /*================================*/
2735   const dict_table_t* table,  /*!< in: table */
2736   dict_index_t*   index)  /*!< in: user representation of
2737           a non-clustered index */
2738 {
2739   dict_field_t* field;
2740   dict_index_t* new_index;
2741   dict_index_t* clust_index;
2742   ulint   i;
2743   ibool*    indexed;
2744 
2745   ut_ad(table && index);
2746   ut_ad(!dict_index_is_clust(index));
2747   ut_ad(mutex_own(&(dict_sys->mutex)));
2748   ut_ad(table->magic_n == DICT_TABLE_MAGIC_N);
2749 
2750   /* The clustered index should be the first in the list of indexes */
2751   clust_index = UT_LIST_GET_FIRST(table->indexes);
2752 
2753   ut_ad(clust_index);
2754   ut_ad(dict_index_is_clust(clust_index));
2755   ut_ad(!dict_index_is_univ(clust_index));
2756 
2757   /* Create a new index */
2758   new_index = dict_mem_index_create(
2759     table->name, index->name, index->space, index->type,
2760     index->n_fields + 1 + clust_index->n_uniq);
2761 
2762   /* Copy other relevant data from the old index
2763   struct to the new struct: it inherits the values */
2764 
2765   new_index->n_user_defined_cols = index->n_fields;
2766 
2767   new_index->id = index->id;
2768 
2769   /* Copy fields from index to new_index */
2770   dict_index_copy(new_index, index, table, 0, index->n_fields);
2771 
2772   /* Remember the table columns already contained in new_index */
2773   indexed = static_cast(
2774     mem_zalloc(table->n_cols * sizeof *indexed));
2775 
2776   /* Mark the table columns already contained in new_index */
2777   for (i = 0; i < new_index->n_def; i++) {
2778 
2779     field = dict_index_get_nth_field(new_index, i);
2780 
2781     /* If there is only a prefix of the column in the index
2782     field, do not mark the column as contained in the index */
2783 
2784     if (field->prefix_len == 0) {
2785 
2786       indexed[field->col->ind] = TRUE;
2787     }
2788   }
2789 
2790   /* Add to new_index the columns necessary to determine the clustered
2791   index entry uniquely */
2792 
2793   for (i = 0; i < clust_index->n_uniq; i++) {
2794 
2795     field = dict_index_get_nth_field(clust_index, i);
2796 
2797     if (!indexed[field->col->ind]) {
2798       dict_index_add_col(new_index, table, field->col,
2799              field->prefix_len);
2800     }
2801   }
2802 
2803   mem_free(indexed);
2804 
2805   if (dict_index_is_unique(index)) {
2806     new_index->n_uniq = index->n_fields;
2807   } else {
2808     new_index->n_uniq = new_index->n_def;
2809   }
2810 
2811   /* Set the n_fields value in new_index to the actual defined
2812   number of fields */
2813 
2814   new_index->n_fields = new_index->n_def;
2815 
2816   new_index->cached = TRUE;
2817 
2818   return(new_index);
2819 }

这是整个函数,读者最好可以先自己读读这个函数理解一下,然后再看分析。

好了,下面我们开始分析了,首先把 dict_table_t 这个结构体的相关成员解释一下:

 474   unsigned  n_user_defined_cols:10;
 475         /*!< number of columns the user defined to
 476         be in the index: in the internal
 477         representation we add more columns */
 478   unsigned  n_uniq:10;/*!< number of fields from the beginning                                                                                                                                                                         
 479         which are enough to determine an index
 480         entry uniquely */
 481   unsigned  n_def:10;/*!< number of fields defined so far */
 482   unsigned  n_fields:10;/*!< number of fields in the index */

注释很好理解,主要是 n_uniq 表示索引中需要多少个字段来唯一标识一行数据,只对唯一索引有效;n_def 是有多少个字段用了扩展存储空间,就是索引中只存前缀; n_fields 是索引最终一共有多少字段,包括系统加的;n_user_defined_cols 是用户定义的字段数,不包括系统自动加的。

然后我们来看两段最主要的代码:

2772   /* Remember the table columns already contained in new_index */
2773   indexed = static_cast(
2774     mem_zalloc(table->n_cols * sizeof *indexed));
2775 
2776   /* Mark the table columns already contained in new_index */
2777   for (i = 0; i < new_index->n_def; i++) {
2778 
2779     field = dict_index_get_nth_field(new_index, i);
2780 
2781     /* If there is only a prefix of the column in the index
2782     field, do not mark the column as contained in the index */
2783 
2784     if (field->prefix_len == 0) {
2785 
2786       indexed[field->col->ind] = TRUE;
2787     }
2788   }

InnoDB首先创建了一个布尔型数组,然后依次循环索引上的每一个字段,如果这个字段不是只有前缀,那么就在数组中记下它的索引号,标记这个字段在索引中出现了。因此indexed数组就存下了索引中用户定义的所有字段序号。

2790   /* Add to new_index the columns necessary to determine the clustered
2791   index entry uniquely */
2792 
2793   for (i = 0; i < clust_index->n_uniq; i++) {
2794 
2795     field = dict_index_get_nth_field(clust_index, i);
2796 
2797     if (!indexed[field->col->ind]) {
2798       dict_index_add_col(new_index, table, field->col,
2799              field->prefix_len);
2800     }
2801   }

这一段就开始循环聚集索引(主键)的每个字段,盘下indexed数组中这个字段是不是有了,如果没有,那么再调用 dict_index_add_col 把字段加到索引中。

因此只要用户定义的索引字段中包含了主键中的字段,那么这个字段就不会再被InnoDB自动加到索引中了,如果用户的索引字段中没有完全包含主键字段,InnoDB就会把剩下的主键字段加到索引末尾。

因此我们最初的例子中, idx1 和 idx2 两个索引内部大小完全一样,没有区别。

最后再补充下组合主键的例子:

CREATE TABLE t (
  a char(32) not null,
  b char(32) not null,
  c char(32) not null,
  d char(32) not null,
  PRIMARY KEY (a,b)
  KEY idx1 (c,a),
  KEY idx2 (d,b)
) Engine=InnoDB;

这个表InnoDB会自动补全主键字典,idx1 实际上内部存储为 (c,a,b),idx2 实际上内部存储为 (d,b,a)。
但是这个自动添加的字段,Server层是不知道的,所以MySQL优化器并不知道这个字段的存在,所以如果你有一个查询:

SELECT * FROM t WHERE d=x1 AND b=x2 ORDER BY a;

其实内部存储的idx2(d,b,a)可以让这个查询完全走索引,但是由于Server层不知道,所以最终MySQL优化器可能选择 idx2(d,b) 做过滤然后排序 a 字段,或者直接用PK扫描避免排序。

而如果我们定义表结构的时候就定义为 KEY idx2(d,b,a) ,那么MySQL就知道(d,b,a)三个字段索引中都有,并且InnoDB发现用户定义的索引中包含了所有的主键字段,也不会再添加了,并没有增加存储空间。

因此,由衷的建议,所有的DBA建索引的时候,都在业务要求的索引字段后面补上主键字段,这没有任何损失,但是可能给你带来意外的惊喜。

希望大家能理解。这篇木有国际友人需要看,就木有英文版了~

标签: , ,
  1. 698346
    9月 20th, 201217:25

    请问5.5也是这样的吗?
    是否与5.6不同呢?感谢

    [回复]

    P.Linux 回复:

    @698346, 5.5也是,5.1我没有看

    [回复]

  2. stamhe
    9月 21st, 201210:44

    那如果一般索引中,没有包含任何主键索引中的字段呢?是会把主键索引的字段全部跟上去还是全部不跟?

    [回复]

    P.Linux 回复:

    @stamhe, 注释说的很明白了,要加上足以定位一行记录的字段。

    [回复]

  3. 七尺大陆
    1月 5th, 201322:51

    还是有区别的。
    当第二索引是unique的时候,如果dba主动加了主键在后面,那么第二索引的内部节点也会包括主键了。

    2805 if (dict_index_is_unique(index)) {
    2806 new_index->n_uniq = index->n_fields;
    2807 } else {
    2808 new_index->n_uniq = new_index->n_def;
    2809 }

    从这里可以看出n_uniq会变,在看dict_index_build_node_ptr()函数就知道了。

    至于为什么第二索引不是唯一的时候要把主键加到内部节点,我认为是方便更新第二索引的时候找到要更新的记录。

    [回复]

    P.Linux 回复:

    @七尺大陆, 这段代码的意思是如果是唯一索引,那么索引本身的字段就可以唯一判断一行记录,否则就需要所有字段一起来判断,因为经过添加缺失的主键字段,已经可以保证足以定位一条记录。即使是唯一键,本来也需要定位到主键去拿记录,因为索引组表中每一行数据都挂在主键后面,所以没有区别。所以不管DBA加不加,二级索引不管唯一不唯一,都需要加上主键中的字段才能定位到主键。

    [回复]

  4. baozailove
    5月 26th, 201320:08

    想问下P叔,在mysql里如果我某个表有3个索引,我想分别知道这三个索引每个索引的大小?这个能知道吗?

    [回复]

    P.Linux 回复:

    看information_schema里面有index_statics表

    [回复]

  5. qxfly
    6月 6th, 201318:51

    “插入部分数据后可以看到idx1和idx2两个索引的大小相同。这说明idx1和idx2的内部结构是一样的”
    这里内部结构不一样,索引key顺序不同,索引数据存放顺序也就不同,
    没有认真看楼主文章的朋友可能深深记着了这个结论。
    建议楼主更正以免广大粉丝误解,多谢

    [回复]