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.
@return own: the internal representation of the clustered index */
static
dict_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
《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF
Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主从服务器
Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群
Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb
MySQL-5.5.38通用二进制安装
--------------------------------------分割线 --------------------------------------
本文永久更新链接地址：
评论暂时关闭