关系大致是这样：
mysql中的unique约束是通过索引实现的；
key的含义是概念级别的，意味着唯一性，key的概念等价于unique；
所以说只要加了unique约束或者key，就会建立一个索引。
在mysql中，使用index或者unique（以及key）都会简历索引，区别在于是否允许重复，这个可以在show index命令中看到。
-
CREATE
TABLE user1(
-
id
INT PRIMARY
KEY AUTO_INCREMENT
COMMENT
'主键',
-
name
VARCHAR(
200)
COMMENT
'姓名',
-
age
int
COMMENT
'年龄',
-
unique aaa (
`name`,
`age`)
-
)
-
-
CREATE
TABLE user1(
-
id
INT PRIMARY
KEY AUTO_INCREMENT
COMMENT
'主键',
-
name
VARCHAR(
200)
COMMENT
'姓名',
-
age
int
COMMENT
'年龄',
-
constraint aaa
unique(
`name`,
`age`)
-
)
这两种建表语句都会建立一个联合索引：
mysql> show index from user1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| user1 | 0 | aaa | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| user1 | 0 | aaa | 2 | age | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
都有一个index，第二列表明这个index是否允许重复。0代表不允许重复。
那么把这个aaa的unique删掉，建立一个普通的index：
mysql> drop index aaa on user1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index aaa on user1(`name`, `age`);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from user1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| user1 | 1 | aaa | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| user1 | 1 | aaa | 2 | age | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
可以看到仍然有索引，但是第二列为1，表示该index可以允许重复。
原文地址:https://blog.csdn.net/u010900754/article/details/94314066
