面试题：MySQL表结构中索引的作用及常见类型有哪些

Question

请详细阐述MySQL表结构中索引对于数据查询和存储的作用，并且列举出至少三种常见的索引类型，同时说明每种索引类型适用的场景。

Answer 1

索引对于数据查询和存储的作用

1. 数据查询
   • 提高查询效率：索引就像一本书的目录，通过在表的列上创建索引，数据库在执行查询时无需全表扫描，而是可以直接定位到包含所需数据的行，大大减少了数据的读取量，从而显著提高查询速度。例如，在一个包含大量用户信息的表中，若经常根据用户ID查询用户详细信息，在用户ID列上创建索引后，查询可以迅速定位到对应的用户记录。
   • 优化排序和分组操作：当查询涉及到ORDER BY或GROUP BY子句时，如果排序或分组的列上有索引，数据库可以利用索引的有序性直接进行排序或分组，避免了额外的数据排序操作，提升操作效率。
2. 数据存储
   • 增加存储开销：索引本身需要占用额外的存储空间。每个索引都会创建一个数据结构（如B - Tree等）来存储索引键值和对应的行指针，这意味着随着索引数量的增加，数据库占用的磁盘空间也会相应增大。
   • 影响数据修改性能：在对表进行插入、更新或删除操作时，不仅要修改表中的数据，还需要同时更新相关的索引。这会增加操作的复杂度和时间开销，降低数据修改的性能。例如，频繁插入数据到有多个索引的表中，插入操作会相对较慢。

常见索引类型及适用场景

1. 普通索引（Index）
   • 定义：最基本的索引类型，没有任何限制。
   • 适用场景：
     • 频繁查询的列：当某个列经常在WHERE子句中作为条件使用时，适合创建普通索引。例如，在商品表中，经常根据商品名称查询商品信息，可在商品名称列上创建普通索引。
     • 关联查询的列：在多表关联查询中，用于连接表的列（如外键列）上创建普通索引，可以加速表之间的连接操作。比如订单表和用户表通过用户ID关联，在订单表的用户ID列上创建普通索引。
2. 唯一索引（Unique Index）
   • 定义：该索引要求索引列的值必须唯一，但允许有空值（如果是组合唯一索引，则所有列值组合必须唯一）。
   • 适用场景：
     • 确保数据唯一性：用于保证某些列的数据具有唯一性约束。例如，用户表中的邮箱列，每个用户的邮箱应该是唯一的，在邮箱列上创建唯一索引可以防止重复邮箱的插入。
     • 优化查询：因为唯一性，在查询时如果基于唯一索引列进行条件查询，查询效率会非常高，类似于普通索引，但数据具有唯一性保证。
3. 主键索引（Primary Key）
   • 定义：特殊的唯一索引，不允许有空值。一张表只能有一个主键索引。
   • 适用场景：
     • 标识表中的每一行：作为表的唯一标识，用于唯一确定表中的每一条记录。例如，在员工表中，员工ID通常作为主键索引，方便准确地定位每个员工的记录。
     • 加速关联和排序：在关联查询和排序操作中，主键索引能提供高效的支持，因为主键索引具有唯一性和有序性，在多表连接和ORDER BY操作时能快速定位和排序数据。
4. 全文索引（Full - Text Index）
   • 定义：主要用于文本类型（CHAR、VARCHAR、TEXT）的列，它可以对文本中的单词进行索引，而不仅仅是对整个列值进行索引，支持更复杂的文本搜索。
   • 适用场景：
     • 文本搜索场景：当需要在大量文本数据中进行模糊查询时非常有用。例如，在新闻文章表中，文章内容通常是大段文本，若要实现根据关键词搜索文章的功能，在文章内容列上创建全文索引比普通索引的搜索效果更好、效率更高。
     • 支持自然语言搜索：全文索引支持自然语言搜索模式，能处理更复杂的语义查询，适用于搜索引擎等应用场景。
5. 组合索引（Composite Index）
   • 定义：基于表的多个列创建的索引，这些列组合在一起构成一个索引键。
   • 适用场景：
     • 多条件联合查询：当查询经常涉及多个列作为条件时，创建组合索引可以提高查询效率。例如，在订单表中，经常根据订单日期和客户ID查询订单信息，在（订单日期，客户ID）列上创建组合索引，数据库可以直接通过这个组合索引定位到满足条件的订单记录。注意，组合索引遵循最左前缀原则，查询条件需从左到右匹配索引列才能发挥索引的最大效能。