MySQL 索引

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度

索引分单列索引和组合索引：

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引
• 组合索引，即一个索引包含多个列

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引虽然能够提高查询性能，但也需要注意以下几点：

• 索引需要占用额外的存储空间
• 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能
• 过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引

普通索引

创建索引

使用 CREATE INDEX 语句可以创建普通索引。

普通索引是最常见的索引类型，用于加速对表中数据的查询。

CREATE INDEX 的语法：

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• CREATE INDEX: 用于创建普通索引的关键字
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的
• table_name: 指定要在哪个表上创建索引
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序

示例：

CREATE INDEX index_id ON username (id);

结果：

mysql> CREATE INDEX index_id ON username (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc username;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  | MUL | NULL    |       |
| Username | varchar(255) | NO   | PRI | NULL    |       |
| Password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

成功在id 列上添加索引

修改表结构(添加索引)

我们可以使用 ALTER TABLE 命令可以在已有的表中创建索引

ALTER TABLE 允许你修改表的结构，包括添加、修改或删除索引

ALTER TABLE 创建索引的语法：

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• ALTER TABLE: 用于修改表结构的关键字
• table_name: 指定要修改的表的名称
• ADD INDEX: 添加索引的子句。ADD INDEX用于创建普通索引
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序

示例：

ALTER TABLE username
ADD INDEX index_id (id);

结果：

mysql> ALTER TABLE username
    -> ADD INDEX index_id (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc username;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  | MUL | NULL    |       |
| Username | varchar(255) | NO   | PRI | NULL    |       |
| Password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

创建表的时候直接指定

我们可以在创建表的时候，你可以在 CREATE TABLE 语句中直接指定索引，以创建表和索引的组合。

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

• CREATE TABLE: 用于创建新表的关键字
• table_name: 指定要创建的表的名称
• (column1, column2, ...): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期
• INDEX: 用于创建普通索引的关键字
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序

下面是一个实例，我们要创建一个名为 students 的表，并在 age 列上创建一个普通索引

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  INDEX index_id (id)
);

在上述实例中，我们在 students 表的 id 列上创建了一个名为 index_id 的普通索引

删除索引的语法

我们可以使用 DROP INDEX 语句来删除索引。

DROP INDEX 的语法：

DROP INDEX index_name ON table_name;

• DROP INDEX: 用于删除索引的关键字。
• index_name: 指定要删除的索引的名称。
• ON table_name: 指定要在哪个表上删除索引。

使用 ALTER TABLE 语句删除索引的语法如下：

ALTER TABLE table_name
DROP INDEX index_name;

• ALTER TABLE: 用于修改表结构的关键字
• table_name: 指定要修改的表的名称
• DROP INDEX: 用于删除索引的子句
• index_name: 指定要删除的索引的名称

示例：

DROP INDEX index_id ON username;
或
ALTER TABLE username
DROP INDEX index_id;

结果：

mysql> DROP INDEX index_id ON username;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc username;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| Username | varchar(255) | NO   | PRI | NULL    |       |
| Password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

唯一索引

在 MySQL 中，你可以使用 CREATE UNIQUE INDEX 语句来创建唯一索引

唯一索引确保索引中的值是唯一的，不允许有重复值

因为与普通索引相似，所以不再展示参数、结果，直接给出示例

创建索引

示例：

CREATE UNIQUE INDEX index_id ON username (id);

修改表结构添加索引

示例：

ALTER TABLE username 
ADD CONSTRAINT index_id UNIQUE (id);

创建表的时候直接指定

示例：

CREATE TABLE username (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  birth_data VARCHAR(100) UNIQUE
);

使用 UNIQUE 关键字后，索引名称将自动生成，你也可以根据需要指定索引名称

使用 ALTER 命令添加和删除主键

主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）

示例：

添加主键

ALTER TABLE username ADD PRIMARY KEY (username);

删除主键

ALTER TABLE username DROP PRIMARY KEY;