【MySQL】复合主键每个主键字段都能使用索引吗？可重复插入吗？
【MySQL】复合主键每个主键字段都能使用索引吗？可重复插入吗？
文章目录
- 【MySQL】复合主键每个主键字段都能使用索引吗？可重复插入吗？
- 一、主键
- 1.1 创建主键
- 1.2 删除主键
- 二、复合主键
- 2.1 Question
- 2.2 实验环境
- 2.3 实验验证
- 2.4 实验结论
- 三、复合主键的唯一性
一、主键
primary key用来唯一约束该字段里面的数据。其主要特征有：
● 不能重复，不能为空
● 一张表中最多只有一个primary key
● primary key 所在列通常是整数类型
1.1 创建主键
create table [表名] ([字段1] [字段类型1] primary key,[字段2] [字段类型2], …);
create table [表名] ([字段1] [字段类型1],[字段2] [字段类型2], … ,primary key(字段))；
alter table [表名] add primary key(字段);
1.2 删除主键
alter table [表名] drop primary key;
二、复合主键
在创建表的时候，在所有字段之后，使用primary key(字段)来创建主键，如果有多个字段作为主键，可以使用符合主键。
MariaDB [class_info]> create table test(id int unsigned,course char(10) comment '课程代码',score int unsigned default 60 comment '成绩',primary key(id,course));
Query OK, 0 rows affected (0.01 sec)MariaDB [class_info]> show create table test\G
*************************** 1. row ***************************Table: test
Create Table: CREATE TABLE `test` (`id` int(10) unsigned NOT NULL DEFAULT '0',`course` char(10) NOT NULL DEFAULT '' COMMENT '课程代码',`score` int(10) unsigned DEFAULT '60' COMMENT '成绩',PRIMARY KEY (`id`,`course`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)MariaDB [class_info]> desc test;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | 0 | |
| course | char(10) | NO | PRI | | |
| score | int(10) unsigned | YES | | 60 | |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2.1 Question
那么问题来了，在主键中说过“ 一张表中最多只有一个primary key”，那么复合主键，每个主键字段都能使用索引吗？
防止以后在应用环境中不当的使用索引，使得mysql性能下降，今天来验证一下这个问题。
2.2 实验环境
- MySQL 5.5
- InnoDB引擎
2.3 实验验证
(1) 创建test1表和test2表
MariaDB [class_info]> CREATE TABLE `test1` (`id` INT(11) NOT NULL,`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',`age` INT(11) NOT NULL,`gender` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',PRIMARY KEY (`id`, `age`))COLLATE='utf8_bin'ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)MariaDB [class_info]> desc test1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(11) | NO | PRI | NULL | |
| gender | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
MariaDB [class_info]> CREATE TABLE `test2` (`id` INT(11) NOT NULL,`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',`age` INT(11) NOT NULL,`gender` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',PRIMARY KEY (`age`, `id`))COLLATE='utf8_bin'ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)MariaDB [class_info]> desc test2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(11) | NO | PRI | NULL | |
| gender | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
可以看出 test1 和 test2 两个表的字段顺序是一样的，都是用了 id 和 age 的复合主键，不同的是 test1 使用的复合主键的顺序是 (id,age)，test2 使用的复合主键的顺序是 (age,id)。
(2) 使用索引
推荐文章：【MySQL】explain 用法详解
观察上面两张图，可知：对于test1表，使用id字段查询时使用到了主键索引，而使用age字段查询时未使用索引。对于test2表，使用age字段查询时使用到了主键索引，而使用id字段查询时未使用索引。
2.4 实验结论
由此证明，在MySQL中创建主键索引时字段的先后顺序是非常重要的。复合索引的第一位字段在查询时才可以用到索引，后面的字段在查询时都用不到索引。如果要频繁查询时，则应另外创建索引。
那么对于上面的Question嘛，也就迎刃而解，这里的联合主键就是用多个字段一起作为一张表的主键，所以才说一张表中最多只有一个primary key。
三、复合主键的唯一性
(1) 虽然复合主键只有第一个字段在查询时才可以用到索引，但是这多个字段组成了唯一的组合，不能同时重复，不然在插入时就会报错。
MariaDB [class_info]> select * from test1;
+----+--------+-----+--------+
| id | name | age | gender |
+----+--------+-----+--------+
| 1 | 李明 | 18 | 男 |
| 2 | 刘梅 | 17 | 女 |
+----+--------+-----+--------+
2 rows in set (0.00 sec)MariaDB [class_info]> insert into test1 values(2,'路遥',19,'女');
Query OK, 1 row affected (0.01 sec)MariaDB [class_info]> select * from test1;
+----+--------+-----+--------+
| id | name | age | gender |
+----+--------+-----+--------+
| 1 | 李明 | 18 | 男 |
| 2 | 刘梅 | 17 | 女 |
| 2 | 路遥 | 19 | 女 |
+----+--------+-----+--------+
3 rows in set (0.00 sec)MariaDB [class_info]> insert into test1 values(3,'马力',19,'男');
Query OK, 1 row affected (0.00 sec)MariaDB [class_info]> select * from test1;
+----+--------+-----+--------+
| id | name | age | gender |
+----+--------+-----+--------+
| 1 | 李明 | 18 | 男 |
| 2 | 刘梅 | 17 | 女 |
| 2 | 路遥 | 19 | 女 |
| 3 | 马力 | 19 | 男 |
+----+--------+-----+--------+
4 rows in set (0.00 sec)MariaDB [class_info]> insert into test1 values(3,'宋江',19,'男');
ERROR 1062 (23000): Duplicate entry '3-19' for key 'PRIMARY'
test1将id和age连接起来作为复合主键，每一个字段时可以重复的，但是两个字段不能同时重复。多个字段的复合主键也是一样的，比如有A、B、C三个字段作为复合主键，当插入(1,1,1)时与前面的记录数据完全相同就会出错。
| A | B | C | √ / × |
|---|---|---|---|
| 1 | 1 | 1 | √ |
| 1 | 1 | 2 | √ |
| 1 | 2 | 1 | √ |
| 2 | 1 | 1 | √ |
| 1 | 2 | 2 | √ |
| 2 | 1 | 2 | √ |
| 2 | 2 | 1 | √ |
| 1 | 1 | 1 | × |
(2) 虽然复合主键只有第一个字段在查询时才可以用到索引，但是这多个字段组成了唯一的组合，不能为空，不然在插入时就会报错。
MariaDB [class_info]> select * from test1;
+----+--------+-----+--------+
| id | name | age | gender |
+----+--------+-----+--------+
| 1 | 李明 | 18 | 男 |
| 2 | 刘梅 | 17 | 女 |
+----+--------+-----+--------+
2 rows in set (0.00 sec)MariaDB [class_info]> insert into test1(name,age,gender) values('张三',20,'男');
ERROR 1364 (HY000): Field 'id' doesn't have a default valueMariaDB [class_info]> insert into test1(id,name,gender) values(3,'李四','男');
ERROR 1364 (HY000): Field 'age' doesn't have a default valueMariaDB [class_info]> insert into test1(name,gender) values('王麻子','男');
ERROR 1364 (HY000): Field 'id' doesn't have a default valueMariaDB [class_info]> desc test1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(11) | NO | PRI | NULL | |
| gender | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！