from : 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<ibool*>( 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<ibool*>( 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建索引的时候,都在业务要求的索引字段后面补上主键字段,这没有任何损失,但是可能给你带来意外的惊喜。
希望大家能理解。这篇木有国际友人需要看,就木有英文版了~