InnoDB一定会在索引中加上主键吗
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了InnoDB一定会在索引中加上主键吗，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7353字，纯文字阅读大概需要11分钟。
内容图文
DBA 群里在讨论一个问题，到底InnoDB会不会在索引末尾加上主键，什么时候会加？ 我之前看代码记得是如果索引末尾就是主键，那么InnoDB就不再添加主键了，如果索引末尾不是主键，那么会添加主键，但是这跟测试结果不符： CREATETABLE t ( a char(32)notnullpr
DBA群里在讨论一个问题，到底InnoDB会不会在索引末尾加上主键，什么时候会加？
我之前看代码记得是如果索引末尾就是主键，那么InnoDB就不再添加主键了，如果索引末尾不是主键，那么会添加主键，但是这跟测试结果不符：
CREATETABLE t ( a char(32)notnullprimarykey, b char(32)notnull,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-clustered2729 index, containing also system fields not defined by the user.2730 @return own: the internal representation of the non-clustered index */2731static2732 dict_index_t*2733 dict_index_build_internal_non_clust(2734/*================================*/2735const dict_table_t* table, /*!< in: table */2736 dict_index_t* index)/*!< in: user representation of2737 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;27442745 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);27492750/* The clustered index should be the first in the list of indexes */2751 clust_index = UT_LIST_GET_FIRST(table->indexes);27522753 ut_ad(clust_index);2754 ut_ad(dict_index_is_clust(clust_index));2755 ut_ad(!dict_index_is_univ(clust_index));27562757/* 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);27612762/* Copy other relevant data from the old index2763 struct to the new struct: it inherits the values */27642765 new_index->n_user_defined_cols = index->n_fields;27662767 new_index->id = index->id;27682769/* Copy fields from index to new_index */2770 dict_index_copy(new_index, index, table, 0, index->n_fields);27712772/* Remember the table columns already contained in new_index */2773 indexed =static_cast<ibool*>(2774 mem_zalloc(table->n_cols *sizeof*indexed));27752776/* Mark the table columns already contained in new_index */2777for(i =0; i < new_index->n_def; i++){27782779 field = dict_index_get_nth_field(new_index, i);27802781/* If there is only a prefix of the column in the index2782 field, do not mark the column as contained in the index */27832784if(field->prefix_len ==0){27852786 indexed[field->col->ind]= TRUE;2787}2788}27892790/* Add to new_index the columns necessary to determine the clustered2791 index entry uniquely */27922793for(i =0; i < clust_index->n_uniq; i++){27942795 field = dict_index_get_nth_field(clust_index, i);27962797if(!indexed[field->col->ind]){2798 dict_index_add_col(new_index, table, field->col,
2799 field->prefix_len);2800}2801}28022803 mem_free(indexed);28042805if(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}28102811/* Set the n_fields value in new_index to the actual defined2812 number of fields */28132814 new_index->n_fields = new_index->n_def;28152816 new_index->cached = TRUE;28172818return(new_index);2819}
|
这是整个函数，读者最好可以先自己读读这个函数理解一下，然后再看分析。
好了，下面我们开始分析了，首先把 dict_table_t 这个结构体的相关成员解释一下：
474unsigned 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 */478unsigned n_uniq:10;/*!< number of fields from the beginning 479 which are enough to determine an index 480 entry uniquely */481unsigned n_def:10;/*!< number of fields defined so far */482unsigned 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));27752776/* Mark the table columns already contained in new_index */2777for(i =0; i < new_index->n_def; i++){27782779 field = dict_index_get_nth_field(new_index, i);27802781/* If there is only a prefix of the column in the index2782 field, do not mark the column as contained in the index */27832784if(field->prefix_len ==0){27852786 indexed[field->col->ind]= TRUE;2787}2788}
|
InnoDB首先创建了一个布尔型数组，然后依次循环索引上的每一个字段，如果这个字段不是只有前缀，那么就在数组中记下它的索引号，标记这个字段在索引中出现了。因此indexed数组就存下了索引中用户定义的所有字段序号。
2790/* Add to new_index the columns necessary to determine the clustered2791 index entry uniquely */27922793for(i =0; i < clust_index->n_uniq; i++){27942795 field = dict_index_get_nth_field(clust_index, i);27962797if(!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 两个索引内部大小完全一样，没有区别。
最后再补充下组合主键的例子：
CREATETABLE t ( a char(32)notnull, b char(32)notnull, c char(32)notnull, d char(32)notnull,PRIMARYKEY(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建索引的时候，都在业务要求的索引字段后面补上主键字段，这没有任何损失，但是可能给你带来意外的惊喜。
原文地址：InnoDB一定会在索引中加上主键吗, 感谢原作者分享。
内容总结
以上是互联网集市为您收集整理的InnoDB一定会在索引中加上主键吗全部内容，希望文章能够帮你解决InnoDB一定会在索引中加上主键吗所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。