联合主键和复合主键和联合索引

复合主键

其实“主键是唯一的索引”这话有点歧义的。举个例子，我们在表中创建了一个ID字段，自动增长，并设为主键，这个是没有问题的，因为“主键是唯一的索引”，ID自动增长保证了唯一性，所以可以。

此时，我们再创建一个字段name，类型为varchar，也设置为主键，你会发现，在表的多行中你是可以填写相同的name值的，这岂不是有违“主键是唯一的索引”这句话么？

所以我才说“主键是唯一的索引”是有歧义的。(复合主键)应该是“当表中只有一个主键时，它是唯一的索引；当表中有多个主键时，称为复合主键，复合主键联合保证唯一索引”。

为什么自增长ID已经可以作为唯一标识的主键，为啥还需要复合主键呢。因为，并不是所有的表都要有ID这个字段啊哈哈，比如，我们建一个学生表，没有唯一能标识学生的ID，怎么办呢，学生的名字、年龄、班级都可能重复，无法使用单个字段来唯一标识，这时，我们可以将多个字段设置为主键，形成复合主键，这多个字段联合标识唯一性，其中，某几个主键字段值出现重复是没有问题的，只要不是有多条记录的所有主键值完全一样，就不算重复。

 

 

什么是数据表的复合主键

所谓的复合主键 就是指你表的主键含有一个以上的字段组成
比如
create table test
(
   name varchar(19),
   id number,
   value varchar(10),
   primary key (name,id)
)

上面的name和id字段组合起来就是你test表的复合主键

它的出现是因为你的name字段可能会出现重名，所以要加上ID字段这样就可以保证你记录的唯一性
一般情况下，主键的字段长度和字段数目要越少越好

联合主键
什么是联合主键？
(主键原则上是唯一的，别被唯一值所困扰。)
顾名思义就是多个主键联合形成一个主键组合
一个简单的例子
主键A跟主键B组成联合主键
主键A跟主键B的数据可以完全相同(困扰吧，没关系)，联合就在于主键A跟主键B形成的联合主键是唯一的。
下例主键A数据是1，主键B数据也是1，联合主键其实是11，这个11是唯一值，绝对不充许再出现11这个唯一值。(这就是多对多关系)
主键A数据主键B数据
1　　　　　　1
2　　　　　　2
3　　　　　　3
主键A与主键B的联合主键值最多也就是
11
12
13
21
22
23
31
32
33

 

联合索引
顾名思义，就是几个字段联合起来，一起做的索引，为了讲述，首先先建立一个表，test_union:

create table test_union
(
    id int auto_increment comment 'zhujian'
        primary key,
    k1 int not null comment 'lianheziduan1',
    k2 int not null comment 'lianheziduan2',
    k3 int not null comment 'lianheziduan3',
    k4 int not null comment 'lianheziduan4',
    k5 int not null comment 'lianheziduan5',
    k6 int null comment 'ziduan1',
    k7 int null comment 'ziduan2'
)
;

create index test_union_k1_k2_k3_k4_k5_index
    on test_union (k1, k2, k3, k4, k5)
;

插入几条数据：

insert into test_union(k1,k2,k3,k4,k5,k6,k7) values(1,1,1,1,1,1,1);
insert into test_union(k1,k2,k3,k4,k5,k6,k7) values(2,2,2,2,2,2,2);
insert into test_union(k1,k2,k3,k4,k5,k6,k7) values(3,3,3,3,3,3,3);

从如下几条SQL语句讲述联合索引的使用：

EXPLAIN SELECT * FROM test_union where k1= 1;
EXPLAIN SELECT * FROM test_union where k1= 1 AND k2 =1;
EXPLAIN SELECT * FROM test_union where k1= 1 AND k3 = 1;
EXPLAIN SELECT * FROM test_union where k1= 1 AND k4 =1;
EXPLAIN SELECT * FROM test_union where k2= 1;
EXPLAIN SELECT * FROM test_union where k6 = 1;

先看看每条语句的explain分析。

---

EXPLAIN SELECT * FROM test_union where k1= 1;

用到了联合索引test_union_k1_k2_k3_k4_k5_index，但是并没有用到全部，之用到了联合索引中的字段k1，也就是只有一个字段，所以key_len的长度为4。

---

EXPLAIN SELECT * FROM test_union where k1= 1 AND k2 =1;

用到了联合索引test_union_k1_k2_k3_k4_k5_index，但是并没有用到全部，之用到了联合索引中的字段k1，k2，也就是只有两个字段，所以key_len的长度为8。

---

EXPLAIN SELECT * FROM test_union where k1= 1 AND k3 = 1;

用到了联合索引test_union_k1_k2_k3_k4_k5_index，但是并没有用到全部，由于key_len为4,可知之用到联合索引的其中一位，那就是k1，但是k3也是联合索引test_union_k1_k2_k3_k4_k5_index的字段之一，这里没有用的原因是，对于联合索引来说，开始只有第一个字段的索引可用，当使用了第一个字段后，第二个字段的索引才有效，以此类推，这里的原因，笔者个人的理解如下，举个简单的例子，一共有四条数据{(1,1),(1,2),(2,1),(2,2)},这四条数据的是按照字典序排列，但是如果没有使用第一个字段，直接使用第二个字段，对应的是{1,2,1,2},并不是排序好的，索引是无法建立的，不知道这样理解是否有些不妥，就是索引需要一次使用，不能隔着字段使用。该例中由于k1是第一个字段，索引是建立好的，可以直接使用，这时k2的索引是可以使用的，但是由于没有使用k2直接使用k3字段查询，k3的字段索引不能使用，因此只能使用k1一个字段的索引，所以key_len为4，而另一个字段k3查询走的是where条件，所以字段Extra为Using where。

---

EXPLAIN SELECT * FROM test_union where k1= 1 AND k4 =1;

该例和上一条示例类似，由于未使用k2和k3,所以不能直接使用索引的k4字段,而另一个字段k4查询走的是where条件，所以字段Extra为Using where。

注意： 以上几条都用到了联合索引，但都不是用到所有字段，记得之前接触过一个词叫”部分索引”，大概就是这个意思了吧！请大牛指正（zgsoft_happy@126.com)

---

EXPLAIN SELECT * FROM test_union WHERE k1 = 1 AND k2 = 1 AND k3 = 1 AND k4 = 1 AND k5 = 1;

该例使用了联合索引的所有字段，一共是5个字段，因此key_len为20。

---

EXPLAIN SELECT * FROM test_union where k2= 1;

这个例子的原理类似前面两个部门索引的例子，由于没有使用联合索引的字段k1，直接使用k2，这个时候k2没有索引。所以不能使用索引，而该表中又没有其他包含k2的索引，因此没有使用索引，直接遍历所有的数据记录查询的，使用的是Using where。

---

EXPLAIN SELECT * FROM test_union where k6 = 1;

这个好理解，因为k6字段没有在任何索引里，所以不能使用索引，因此是直接遍历所有的数据记录查询的，使用的是Using where。

至此，mysql的联合索引的使用介绍完毕