MySQL 创建唯一索引

1. 什么是索引

索引是数据库中用于提高查询性能的一种数据结构。它类似于书籍的目录，可以快速定位到需要的数据。在数据库中，索引可以加快数据的查找速度，降低数据的存储空间和CPU资源的消耗。

2. 为什么使用索引

一个数据库中可能包含数百万条数据，如果没有索引，每次查询都需要遍历整个表格来查找所需数据，这种方式效率非常低下。而使用索引可以将数据按照一定的规则进行排序并存储，使得查找数据时可以更快地定位到目标数据，从而提高查询效率。

此外，索引还可以用来保证数据的唯一性、加速数据的插入和更新操作。

3. 索引的分类

根据索引的数据结构和实现方式，索引可以分为多种类型。常见的索引类型包括B树索引、哈希索引、全文索引等。其中，B树索引是最常用的一种索引类型，本文着重介绍该类型的唯一索引。

4. B树索引

B树是一种平衡多路搜索树，每个节点可以存储多个数据和子节点。B树具有以下特点：

所有叶子节点都在同一层。
每个节点可以存储多个数据项，采用排序的方式进行存储。
每个节点可以存储多个子节点。

B树的数据结构使得在B树索引中进行查找时，只需要进行少量的IO操作，从而提高查询速度。

5. 创建索引的语法

在MySQL中，可以使用CREATE INDEX语句来创建索引。语法如下：

CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, ...);

其中，UNIQUE关键字用于指定唯一索引。index_name是索引的名字，table_name是需要创建索引的表名，(column1, column2, ...)是需要创建索引的列名。

6. 示例代码

假设有一个名为users的表，用于存储用户信息，结构如下：

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(50)
);

我们可以为username列创建一个唯一索引，示例代码如下：

CREATE UNIQUE INDEX idx_username
ON users (username);

上述代码将为username列创建一个唯一索引，并命名为idx_username。

7. 索引的效果

创建了唯一索引后，查询、插入和更新操作的效率会得到提升。下面通过示例代码进行演示。

首先，我们往users表中插入一些数据：

INSERT INTO users (id, username, email)
VALUES (1, 'john', 'john@example.com'),
       (2, 'jane', 'jane@example.com'),
       (3, 'mary', 'mary@example.com');

然后，通过以下代码进行查询：

SELECT * FROM users WHERE username = 'john';

查询结果如下：

+----+----------+------------------+
| id | username | email            |
+----+----------+------------------+
|  1 | john     | john@example.com |
+----+----------+------------------+

可以看到，通过唯一索引，我们可以快速定位到username为’john’的记录。

接下来，我们尝试插入一个重复的记录：

INSERT INTO users (id, username, email)
VALUES (4, 'john', 'test@example.com');

由于username列有唯一索引，插入重复记录时会报错：

ERROR 1062 (23000): Duplicate entry 'john' for key 'idx_username'

这说明唯一索引可以保证数据的唯一性。

最后，我们尝试更新一条记录：

UPDATE users SET email = 'new@example.com' WHERE id = 1;

更新成功后，我们再次查询username为’john’的记录：

SELECT * FROM users WHERE username = 'john';

查询结果如下：

+----+----------+------------------+
| id | username | email            |
+----+----------+------------------+
|  1 | john     | new@example.com  |
+----+----------+------------------+

可以看到，更新操作不会影响到索引的使用。

综上所述，使用唯一索引可以提高查询数据的速度，并保证数据的唯一性。