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

 

创建索引的过程

1. 索引构建：MySQL 会扫描整个表的数据并根据索引列构建索引。这涉及读取表中的每一行，并将索引列的值插入到索引结构中（如B树或哈希表）。

2. 磁盘IO和CPU使用：构建索引是一个高强度的I/O和CPU操作。MySQL需要读取大量的数据，并进行排序和插入操作，这会占用大量的磁盘I/O和CPU资源。

3. 锁定表：在创建索引时，表可能会被锁定。具体锁定行为取决于存储引擎和索引类型。

   • InnoDB：对大多数索引操作，InnoDB 会使用在线DDL（Data Definition Language）功能，以减少对表的完全锁定，允许读写操作在某种程度上继续进行。
   • MyISAM：通常会锁定整个表，阻止读写操作，直到索引创建完成。

4. 事务日志：在使用InnoDB时，创建索引过程会记录到事务日志中，以便在崩溃恢复时能够重建索引。

影响和考虑

1. 性能影响：

   • 查询性能：在索引创建过程中，查询性能可能会受到影响，查询可能变慢或者需要等待索引创建完成。
   • 插入/更新/删除性能：在索引创建过程中，对表的插入、更新和删除操作可能会变慢，或者在某些情况下被阻塞，特别是在使用MyISAM存储引擎时。

2. 磁盘空间：索引会占用额外的磁盘空间。对于一个包含2000万行的表，索引的大小可能会非常大，具体取决于索引列的数据类型和索引的复杂性。

3. 内存使用：MySQL需要在内存中维护索引的部分数据结构，这可能会增加内存的使用。

4. 操作时间：创建索引的时间取决于表的大小、硬件性能、当前系统负载和索引的类型。对于一个包含2000万行的表，索引创建过程可能需要几分钟到几个小时不等。