首页 / 数据库 / MySQL / MySQL 二级索引会不会自动补齐主键

开篇：一直对MySQL 二级索引是否自动加入主键问题有怀疑，今天又时间就5.5和5.6分析了一把：mysql> select version（）;+------------+| version（）  |+------------+| 5.6.16-log |mysql> create table t9（    -> id int not null ,    -> a int ,    -> b int,    -> c int,    -> primary key（id）,    -> key ab_idx（a,b）    -> ）engine=innodb;Query OK, 0 rows affected （0.00 sec）show variables like "%optimizer_swit%";....  use_index_extensions=offmysql> alter table t9 drop primary key ;Query OK, 16 rows affected （0.01 sec）Records: 16  Duplicates: 0  Warnings: 0 mysql> alter table t9 add primary key（id,id2）;mysql> desc select * from t9 where a=2 and b=1 order by id;+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+| id | select_type | table | type | possible_keys | key    | key_len | ref         | rows | Extra       |+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+|  1 | SIMPLE      | t9    | ref  | ab_idx        | ab_idx | 10      | const,const |    3 | Using where |+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+1 row in set （0.00 sec） mysql> desc select * from t9 where a=2 and b=1 order by id,id2;+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+| id | select_type | table | type | possible_keys | key    | key_len | ref         | rows | Extra       |+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+|  1 | SIMPLE      | t9    | ref  | ab_idx        | ab_idx | 10      | const,const |    3 | Using where |+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+* 发现是会自动补齐mysql> select version（）; +------------+| version（）  |+------------+| 5.5.36-log |mysql> CREATE TABLE t01 （    ->   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 idx2 （d,b）    -> ） Engine=InnoDB;mysql> explain select * from t01 where d="w" and b="g" order by a;                                                                   +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra       |+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+|  1 | SIMPLE      | t01   | ref  | idx2          | idx2 | 192     | const,const |    3 | Using where |+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+1 row in set （0.00 sec） mysql> explain select * from t01 where d="w" and b="g" order by a,b;+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra       |+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+|  1 | SIMPLE      | t01   | ref  | idx2          | idx2 | 192     | const,const |    3 | Using where |+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+MySQL 5.6.16 源码 storage/innobase/dict/dict0dict.cc/*******************************************************************//**Builds the internal dictionary cache representation for a clusteredindex, containing also system fields not defined by the user.@return own: the internal representation of the clustered index */staticdict_index_t*dict_index_build_internal_clust（/*============================*/    const dict_table_t*    table,  /*！< in: table */    dict_index_t*     index）  /*！< in: user representation of                    a clustered index */{    dict_index_t* new_index;    dict_field_t* field;    ulint     trx_id_pos;    ulint     i;    ibool*      indexed;     ut_ad（table && index）;    ut_ad（dict_index_is_clust（index））;    ut_ad（mutex_own（&（dict_sys->mutex）））;    ut_ad（table->magic_n == DICT_TABLE_MAGIC_N）;     /* Create a new index object with certainly enough fields */    new_index = dict_mem_index_create（table->name,                      index->name, table->space,                      index->type,                      index->n_fields + table->n_cols）;    /* Remember the table columns already contained in new_index */    indexed = static_cast<ibool*>（        mem_zalloc（table->n_cols * sizeof *indexed））;     /* Mark the table columns already contained in new_index */    for （i = 0; i < new_index->n_def; i++） {         field = dict_index_get_nth_field（new_index, i）;         /* If there is only a prefix of the column in the index        field, do not mark the column as contained in the index */         if （field->prefix_len == 0） {             indexed[field->col->ind] = TRUE;        }    }总结：1.从源代码看出在引擎层是做了主动补齐主键到二级索引的最后面，但是server层并不知道主键补齐到后面，mysql server层不一定动能自动识别二级索引后面的主键列，强烈建议创建二级索引的时候加上主键列。2.我上面的演示实例M ySQL server层是自动自动识别了二级索引后的主键列，看运气吧，目前还没遇到过不识别的。--------------------------------------分割线 --------------------------------------Ubuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm《MySQL权威指南（原书第2版）》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htmUbuntu 14.04 LTS 安装 LNMP NginxPHP5 （PHP-FPM）MySQL http://www.linuxidc.com/Linux/2014-05/102351.htmUbuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htmUbuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htmUbuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htmMySQL-5.5.38通用二进制安装 http://www.linuxidc.com/Linux/2014-07/104509.htm--------------------------------------分割线 --------------------------------------本文永久更新链接地址