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;
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+