Mysql 索引

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

什么是索引

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

索引的类型

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

PRIMARY KEY 索引：主键索引，表中每行数据的唯一标识，不能有重复值，也不能为 NULL。一个表只能有一个主键。
UNIQUE 索引：唯一索引，保证列中的所有值都是唯一的，可以有 NULL 值。
INDEX（普通索引）：普通索引，能够加速数据检索，没有唯一性要求。
FULLTEXT 索引：全文索引，主要用于全文检索，在大文本字段（如 CHAR, VARCHAR, TEXT）上使用。
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 = 'student@example.com';

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');

在已有表创建索引

CREATE INDEX idx_last_name ON students (last_name);
CREATE INDEX idx_class_enrollment ON students (class_id, enrollment_date);

创建索引的过程

索引构建：MySQL 会扫描整个表的数据并根据索引列构建索引。这涉及读取表中的每一行，并将索引列的值插入到索引结构中（如B树或哈希表）。
磁盘IO和CPU使用：构建索引是一个高强度的I/O和CPU操作。MySQL需要读取大量的数据，并进行排序和插入操作，这会占用大量的磁盘I/O和CPU资源。
锁定表：在创建索引时，表可能会被锁定。具体锁定行为取决于存储引擎和索引类型。
- InnoDB：对大多数索引操作，InnoDB 会使用在线DDL（Data Definition Language）功能，以减少对表的完全锁定，允许读写操作在某种程度上继续进行。
- MyISAM：通常会锁定整个表，阻止读写操作，直到索引创建完成。
事务日志：在使用InnoDB时，创建索引过程会记录到事务日志中，以便在崩溃恢复时能够重建索引。

影响和考虑

性能影响：
- 查询性能：在索引创建过程中，查询性能可能会受到影响，查询可能变慢或者需要等待索引创建完成。
- 插入/更新/删除性能：在索引创建过程中，对表的插入、更新和删除操作可能会变慢，或者在某些情况下被阻塞，特别是在使用MyISAM存储引擎时。
磁盘空间：索引会占用额外的磁盘空间。对于一个包含2000万行的表，索引的大小可能会非常大，具体取决于索引列的数据类型和索引的复杂性。
内存使用：MySQL需要在内存中维护索引的部分数据结构，这可能会增加内存的使用。
操作时间：创建索引的时间取决于表的大小、硬件性能、当前系统负载和索引的类型。对于一个包含2000万行的表，索引创建过程可能需要几分钟到几个小时不等。