Will MySQL secondary indexes automatically complete the primary key?

Will MySQL secondary indexes automatically complete the primary key?

At the beginning: I have been skeptical about whether MySQL secondary indexes are automatically added to the primary key. Today I have analyzed 5.5 and 5.6 again:

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 | +----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+ * It is automatically completed. 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 source code 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;         }     }

Summary:

1. from the source code, we can see that at the engine layer, the primary key is automatically filled to the end of the secondary index, but the server layer does not know that the primary key is completed to the end, the mysql server layer does not necessarily automatically identify the primary key columns behind the secondary index. We strongly recommend that you add the primary key columns when creating the secondary index.

2. In the demo above, the M ySQL server layer automatically recognizes the primary key columns after the secondary index. Check your luck. I have never met any other ones.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: