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; } }
总结:
从源代码看出在引擎层是做了主动补齐主键到二级索引的最后面,但是server层并不知道主键补齐到后面,mysql server层不一定动能自动识别二级索引后面的主键列,强烈建议创建二级索引的时候加上主键列。
我上面的演示实例M ySQL server层是自动自动识别了二级索引后的主键列,看运气吧,目前还没遇到过不识别的。
本文出自 “My DBA life” 博客,请务必保留此出处http://huanghualiang.blog.51cto.com/6782683/1557306
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。