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

标签: MySQL源码分析 主键 InnoDB | 发表时间:2013-02-19 22:04 | 作者:OurMySQL
出处:http://ourmysql.com

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

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

CREATETABLE t (
  a char(32)notnullprimarykey,
  b char(32)notnull,KEY idx1 (a,b),KEY idx2 (b,a)) Engine=      ;

插入部分数据后可以看到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一定会在索引中加上主键吗

- - OurMySQL
DBA群里在讨论一个问题,到底InnoDB会不会在索引末尾加上主键,什么时候会加. 我之前看代码记得是如果索引末尾就是主键,那么InnoDB就不再添加主键了,如果索引末尾不是主键,那么会添加主键,但是这跟测试结果不符:. 插入部分数据后可以看到idx1和idx2两个索引的大小相同. 这说明idx1和idx2的内部结构是一样的,因此 不可能 是idx1在内部存为(a,b,a).

MySQL InnoDB B+树索引

- - OurMySQL
B+树索引在DB中有一个特点就是高扇出性,一般在DB中B+树的高度在2-3层左右,也就意味着只需要2-3次的IO操作即可. 而现在的磁盘每秒差不多在100次IO左右,2-3次意味着查询时间只需0.02-0.03秒. InnoDB存储引擎表是索引组织表,即表中数据安装主键顺序存放. 而聚集索引就是按照每张表的主键构造一颗B+,并且叶节点存放着整张表的行记录数据,因此也让聚集索引也是索引的一部分.

Innodb索引和锁的学习笔记

- - 数据库 - ITeye博客
附录:前段时间学习了下innodb锁的相关知识,对锁和事务有了大体理解,这里做个小总结. 1.Innodb事务和锁的关系.    Innodb区别于MyISAM的两个特点就是Innodb对于事务的支持和对行锁的支持. 事务要求了一组SQL语句的ACID特性,同时为了避免对一行记录的并发更新,innodb本身会在一定情况下加锁,然后等语句所在的事务退出后(rollbak或者commit)释放锁.

建索引的原则-以innodb为例

- - CSDN博客推荐文章
随着开发、测试任务进入尾声,大家都在整理一些项目发布前的一些准备工作,其中一个重要的工作就是为之前写的一些sql语句建立索引,这高并发、高访问量的环境下是非常有必要的,建立一个好的索引能够极大地提高sql语句的查询效率,那么问题来了,到底什么是索引,怎样才能建立一个好的索引呢. 本文以mysql Innodb存储引擎为例,结合实际的项目来看一下,如何建立一个好的而索引.

关于InnoDB索引长度限制的tips

- - IT技术博客大学习
有同学问到MySQL数据库InnoDB存储引擎的索引长度问题,简单说几个tips. 大家经常碰到InnoDB单列索引长度不能超过767bytes,实际上联合索引还有一个限制是3072. 可以看到,由于每个字段占用255*3, 因此这个索引的大小是3825>3072,报错. 我们知道InnoDB一个page的默认大小是16k.

[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键

- - MySQL中文网
我们先了解下InnoDB引擎表的一些关键特征:. InnoDB引擎表是基于B+树的索引组织表(IOT);. 每个表都需要有一个聚集索引(clustered index);. 所有的行记录都存储在B+树的叶子节点(leaf pages of the tree);. 基于聚集索引的增、删、改、查的效率相对是最高的;.

Mysql InnoDB锁

- - 数据库 - ITeye博客
抄自:http://www.cnblogs.com/qq78292959/archive/2013/01/30/2882745.html. Mysql常用存储引擎的锁机制. MyISAM和MEMORY采用表级锁(table-level locking). BDB采用页面锁(page-leve locking)或表级锁,默认为页面锁.

[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响

- - MySQL中文网
本次,我们来看看索引、提交频率对InnoDB表写入速度的影响,了解有哪些需要注意的. 1、关于索引对写入速度的影响: a、如果有自增列做主键,相对完全没索引的情况,写入速度约提升 3.11%; b、如果有自增列做主键,并且二级索引,相对完全没索引的情况,写入速度约降低 27.37%;. 因此,InnoDB表最好总是有一个自增列做主键.

Mysql Innodb 引擎优化

- 彦强 - 阿辉的空间
作/译者:吴炳锡,来源:http://imysql.cn/ & http://www.mysqlsupport.cn 转载请注明作/译者和出处,并且不能用于商业用途,违者必究. InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎. InnoDB锁定在行级并且也在SELECT语句提供 一个Oracle风格一致的非锁定读.

Percona XtraBackup InnoDB 備份工具

- - 小惡魔 - 電腦技術 - 工作筆記 - AppleBOY
大家可以選擇透過 yum 或 apt Repository 方式安裝,下面介紹 apt 方式即可. 將 apt 伺服器寫入 /etc/apt/sources.list. VERSION 請至換 Ubuntu Server 版號,如果您想測試實驗性版本請加入底下連結. 根據不同的 MySQL 版本來選擇 XtraBackup 指令,可以參考 Choosing the Right Binary,所以大家不要用錯指令了.