Mysql 索引

 在 MySQL 中，索引是一种数据结构，它能够帮助快速查找和检索表中的记录，从而提高查询性能。索引在数据库中类似于书籍的目录，可以快速定位到所需的内容。下面是关于 MySQL 索引的详细介绍，包括其概念、类型、如何使用以及如何在建表时指定索引。

什么是索引

索引是存储在数据库中的一种结构，用于加速数据检索。它可以减少查询扫描的数据量，从而提高查询效率。索引通常基于某些列（一个或多个列），这些列被称为索引键（index key）。

索引的类型

MySQL 支持多种类型的索引，主要包括：

1. PRIMARY KEY 索引：主键索引，表中每行数据的唯一标识，不能有重复值，也不能为 NULL。一个表只能有一个主键。
2. UNIQUE 索引：唯一索引，保证列中的所有值都是唯一的，可以有 NULL 值。
3. INDEX（普通索引）：普通索引，能够加速数据检索，没有唯一性要求。
4. FULLTEXT 索引：全文索引，主要用于全文检索，在大文本字段（如 CHAR, VARCHAR, TEXT）上使用。
5. SPATIAL 索引：空间索引，主要用于地理空间数据类型（如 POINT, LINESTRING, POLYGON）。

假设的学生表结构

我们有一个名为 students 的表，包含以下列：

• student_id：学生 ID，主键
• first_name：名
• last_name：姓
• email：电子邮件
• date_of_birth：出生日期
• class_id：班级 ID
• enrollment_date：入学日期

创建表并指定索引

在创建表时，可以同时指定各种类型的索引：

CREATE TABLE students (
  student_id INT AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  date_of_birth DATE,
  class_id INT,
  enrollment_date DATE,
  PRIMARY KEY (student_id),                  -- 主键索引
  UNIQUE (email),                            -- 唯一索引
  INDEX idx_last_name (last_name),           -- 普通索引
  INDEX idx_class_enrollment (class_id, enrollment_date),  -- 组合索引
  FULLTEXT (first_name, last_name)           -- 全文索引
);

在已有表上添加索引

假设我们已经有一个学生表，但没有索引。我们可以使用 ALTER TABLE 语句添加索引：

ALTER TABLE students ADD PRIMARY KEY (student_id);         -- 添加主键索引
ALTER TABLE students ADD UNIQUE (email);                   -- 添加唯一索引
ALTER TABLE students ADD INDEX idx_last_name (last_name);  -- 添加普通索引
ALTER TABLE students ADD INDEX idx_class_enrollment (class_id, enrollment_date);  -- 添加组合索引
ALTER TABLE students ADD FULLTEXT (first_name, last_name); -- 添加全文索引

索引的使用场景

1. 主键索引

主键索引是表中每行记录的唯一标识，通常用于唯一标识每个学生。在 students 表中，student_id 是主键索引：

SELECT * FROM students WHERE student_id = 123;

2. 唯一索引

唯一索引保证列中的所有值都是唯一的，可以用于防止重复数据。在 students 表中，email 列是唯一索引：

SELECT * FROM students WHERE email = '[email protected]';

3. 普通索引

普通索引用于加速查询，在 students 表中，last_name 列是普通索引，可以加速按姓氏查询的操作：

SELECT * FROM students WHERE last_name = 'Smith';

4. 组合索引

组合索引用于加速涉及多个列的查询。在 students 表中，class_id 和 enrollment_date 组合索引可以加速按班级和入学日期查询的操作：

SELECT * FROM students WHERE class_id = 10 AND enrollment_date = '2022-09-01';

5. 全文索引

全文索引用于加速文本字段的全文检索。在 students 表中，first_name 和 last_name 列的全文索引可以加速名字和姓氏的全文检索：

SELECT * FROM students WHERE MATCH(first_name, last_name) AGAINST ('John Smith');