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=off

mysql> 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 clustered

index, containing also system fields not defined by the user.

@returnown: the internal representation of the clustered index */

static

dict_index_t*

dict_index_build_internal_clust(

/*============================*/

const dict_table_t*table,/*!

dict_index_t*index)/*!

a clustered index */

{

dict_index_t*new_index;

dict_field_t*field;

ulinttrx_id_pos;

ulinti;

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(

mem_zalloc(table->n_cols * sizeof *indexed));

/* Mark the table columns already contained in new_index */

for (i = 0; i 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;

}

}

总结：从源代码看出在引擎层是做了主动补齐主键到二级索引的最后面，但是server层并不知道主键补齐到后面，mysql server层不一定动能自动识别二级索引后面的主键列，强烈建议创建二级索引的时候加上主键列。

我上面的演示实例M ySQL server层是自动自动识别了二级索引后的主键列，看运气吧，目前还没遇到过不识别的。

原文：http://huanghualiang.blog.51cto.com/6782683/1557306