MySQL 索引

Question

MySQL 支持多种索引类型，每种类型都有其特定用途和优化场景。下面是 MySQL 中常见的几种索引类型：

1.B-Tree 索引：

• 最常见的索引类型，适用于全值匹配、值范围或前缀查找。
• 在等于、大于、小于、BETWEEN、IN 等查询中效果最佳。
• 对于字符串字段，可以优化 LIKE 查询，但仅当模式的开始部分不是通配符时。

  ALTER TABLE table_name ADD INDEX index_name (column_name);
  或者
  CREATE INDEX index_name ON table_name (column_name);
  

2.哈希索引：

• 基于哈希表实现，只有精确匹配索引列的查询才有效。
• 非常快的查找速度，但不支持排序和部分查找。
• 通常在内存数据库（例如 MySQL 的 MEMORY 表类型）中使用。

  CREATE TABLE table_name (
      column_name column_type,
      INDEX USING HASH (column_name)
  );
  

3.全文索引（Full-text Indexes）：

• 专门用于全文搜索。
• 只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。
• 使用全文索引可以在包含大量文本的字段上进行有效的搜索。

  ALTER TABLE table_name ADD FULLTEXT index_name (column_name);
  或者
  CREATE FULLTEXT INDEX index_name ON table_name (column_name);
  

4.空间索引（Spatial Indexes）：

• 用于空间数据类型，如几何数据。
• 可以用于空间查询，如查找某个给定范围内的所有点。

  ALTER TABLE table_name ADD SPATIAL INDEX index_name (column_name);
  或者
  CREATE SPATIAL INDEX index_name ON table_name (column_name);
  

5.前缀索引：

• 如果列的值很长，可以使用前缀索引，即只索引字段值的前 N 个字符。
• 前缀索引可以减少索引占用的空间，但可能不如完整索引精确。

  CREATE INDEX index_name ON table_name (column_name(10));
  

6.组合索引：

• 在表的多个列上创建的索引。
• 可以加速对这些列的组合查询，但其效率取决于查询条件与索引列的匹配程度。

  CREATE INDEX index_name ON table_name (column1, column2, column3);
  

在使用索引时，重要的是要根据数据的特性和查询的需求来选择合适的索引类型。适当的索引可以显著提高查询性能，但不恰当的索引可能会降低性能并增加存储空间的需求。