MySQL的索引类型

目录

1. 主键索引 (PRIMARY KEY)

2. 唯一索引 (UNIQUE)

3. 普通索引 (INDEX)

4. 全文索引 (FULLTEXT)

5. 空间索引 (SPATIAL)

6. 组合索引 (COMPOSITE INDEX)

7. 前缀索引 (PREFIX INDEX)

8. 覆盖索引 (COVERING INDEX)

---

1. 主键索引 (PRIMARY KEY)

• 描述：表中的主键列自动创建的索引，用于唯一标识表中的每一行数据。每个表只能有一个主键索引，主键列不能有NULL值。
• 举例 

  CREATE TABLE users (id INT AUTO_INCREMENT,username VARCHAR(50) NOT NULL,email VARCHAR(100) NOT NULL,PRIMARY KEY (id)
  );

  在这个例子中，id列被定义为主键，MySQL会自动为这个列创建一个主键索引。

2. 唯一索引 (UNIQUE)

• 描述：确保列中的每个值都是唯一的。尝试插入重复值时，数据库将拒绝该操作。唯一索引可以有多个，但每个唯一索引列中的值必须唯一，可以包含NULL值，但只能有一个NULL值。
• 举例

  CREATE TABLE users (id INT AUTO_INCREMENT,username VARCHAR(50) NOT NULL,email VARCHAR(100) NOT NULL,PRIMARY KEY (id),UNIQUE INDEX `unique_email` (email)
  );

   在这里，email列有一个唯一索引，确保每个电子邮件地址在表中是唯一的。

3. 普通索引 (INDEX)

• 描述：最基本的索引类型，没有唯一性的限制，主要用于提高查询性能。一个表可以有多个普通索引。
• 举例

  CREATE INDEX idx_username ON users (username);
  

  这个例子为users表的username列创建了一个普通索引，用来加速基于username的查询。

4. 全文索引 (FULLTEXT)

• 描述：专门用于全文搜索的索引。只有在MyISAM和InnoDB（从MySQL 5.6版本开始支持）存储引擎的CHAR、VARCHAR或TEXT类型列上才能创建全文索引。
• 举例

  CREATE FULLTEXT INDEX ft_idx_content ON articles (content);
  

  在这个例子中，articles表的content列上创建了一个全文索引，用于全文搜索。

5. 空间索引 (SPATIAL)

• 描述：用于空间数据类型的索引，如MySQL的地理数据类型GEOMETRY。这种索引类型主要用于空间数据查询，只有MyISAM存储引擎支持空间索引。
• 举例

  CREATE SPATIAL INDEX sp_idx_location ON parks (location);
  

  在这里，parks表的location列是一个地理数据类型，创建了一个空间索引，用于地理位置查询。

6. 组合索引 (COMPOSITE INDEX)

• 描述：在多个列上创建的索引，用于优化涉及这些列的查询性能。组合索引遵循最左前缀原则，查询从索引的最左边开始匹配列。
• 举例

  CREATE INDEX idx_name_date ON events (name, date);
  

  这个例子创建了一个组合索引，包含events表的name和date两个列，可以加速同时涉及这两个字段的查询。

7. 前缀索引 (PREFIX INDEX)

• 描述：对于文本类的长字段，可以创建前缀索引来提高索引效率，只索引字段的前面一部分字符。
• 举例

  CREATE INDEX idx_text_prefix ON articles (content(100));
  

  在这个例子中，articles表的content列创建了一个前缀索引，只索引了内容的前100个字符。

8. 覆盖索引 (COVERING INDEX)

• 描述：如果一个索引包含了查询所需的所有数据，那么这个索引被称为覆盖索引。查询可以直接通过索引来获取数据，无需回表查询。
• 举例 假设有以下查询：

  SELECT username, email FROM users WHERE username = 'john_doe'; 

  如果存在一个索引：

  CREATE INDEX idx_username_email ON users (username, email);

     这个索引就是一个覆盖索引，因为它包含了查询所需的所有字段username和email（无论是返回的数据还是过滤个条件都是索引字段），查询可以直接使用索引来获取数据，不需要访问表的其他部分。