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通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：