mysql索引/索引优化

1、索引类型

MySQL 数据库支持多种索引类型，不同索引类型适用于不同的场景和存储引擎。以下是常见的索引类型及其特点：

---

1. B-Tree 索引

• 支持引擎：InnoDB、MyISAM、Memory 等大多数存储引擎。
• 特点：
  • 基于平衡树（B+树）结构，支持快速查找、范围查询和排序。
  • 默认索引类型，适用于大多数场景。
• 适用场景：
  • 等值查询（WHERE col = value）。
  • 范围查询（WHERE col > value、BETWEEN）。
  • 排序（ORDER BY col）。
  • 联合索引（多列组合查询）。
• 示例：

  CREATE INDEX idx_name ON users (name);
  

---

2. 哈希索引（Hash Index）

• 支持引擎：Memory 引擎显式支持，InnoDB 支持自适应哈希索引（内部自动管理）。
• 特点：
  • 基于哈希表实现，仅支持等值查询（=），不支持范围查询或排序。
  • 查询速度极快（时间复杂度接近 O(1)）。
  • 缺点：哈希冲突可能影响性能，且不支持部分匹配或范围查询。
• 适用场景：
  • 精确匹配查询（如缓存表、内存表）。
• 示例：

  CREATE TABLE cache_table (
    id INT PRIMARY KEY,
    data VARCHAR(100)
  ) ENGINE=MEMORY;
  -- Memory 引擎默认使用哈希索引
  

---

3. 全文索引（Full-Text Index）

• 支持引擎：InnoDB（5.6+）、MyISAM。
• 特点：
  • 用于文本内容的模糊搜索（如关键词匹配）。
  • 支持自然语言搜索和布尔模式搜索。
• 适用场景：
  • 对文本字段（如文章内容、商品描述）进行关键词搜索。
• 示例：

  CREATE FULLTEXT INDEX ft_content ON articles (content);
  -- 查询
  SELECT * FROM articles WHERE MATCH(content) AGAINST('mysql optimization');
  

---

4. 空间索引（Spatial Index）

• 支持引擎：MyISAM、InnoDB（5.7+）。
• 特点：
  • 用于地理空间数据类型（如 GEOMETRY、POINT、POLYGON）。
  • 基于 R-Tree 结构，支持空间范围查询。
• 适用场景：
  • 地理位置查询（如“查找附近的商店”）。
• 示例：

  CREATE TABLE locations (
    id INT PRIMARY KEY,
    position POINT NOT NULL,
    SPATIAL INDEX(position)
  ) ENGINE=MyISAM;
  

---

5. 前缀索引（Prefix Index）

• 特点：
  • 对字符串列的前 N 个字符创建索引，减少索引大小。
  • 需权衡索引长度和选择性（避免重复前缀过多）。
• 适用场景：
  • 长文本字段（如 VARCHAR(255)）的索引优化。
• 示例：

  CREATE INDEX idx_email_prefix ON users (email(10)); -- 仅索引前10个字符
  

---

6. 联合索引（Composite Index）

• 特点：
  • 基于多列组合的 B-Tree 索引。
  • 遵循最左前缀原则：查询条件需包含左侧列才能使用索引。
• 适用场景：
  • 多列组合查询（如 WHERE a=1 AND b=2）。
  • 覆盖索引（索引包含查询所需的所有字段）。
• 示例：

  CREATE INDEX idx_a_b ON table_name (a, b);
  

---

7. 唯一索引（Unique Index）

• 特点：
  • 确保索引列的值唯一（允许 NULL 值，但唯一约束不适用于多个 NULL）。
  • 可以是单列索引或联合索引。
• 适用场景：
  • 强制唯一性约束（如用户邮箱、手机号）。
• 示例：

  CREATE UNIQUE INDEX uq_email ON users (email);
  

---

索引类型对比

索引类型	适用场景	支持引擎	是否支持范围查询	查询速度
B-Tree	大多数查询场景	InnoDB、MyISAM 等	是	快
哈希索引	精确等值查询	Memory、InnoDB（自适应）	否	极快
全文索引	文本关键词搜索	InnoDB、MyISAM	否（仅匹配词）	中等
空间索引	地理空间数据查询	MyISAM、InnoDB（5.7+）	是（空间范围）	中等

---

如何选择索引类型？

1. 默认选择 B-Tree：适用于大多数场景（等值、范围、排序）。
2. 精确匹配且无范围查询：使用哈希索引（如内存表）。
3. 文本搜索：使用全文索引。
4. 空间数据：使用空间索引。
5. 长字符串优化：使用前缀索引。

合理选择索引类型能显著提升查询性能，但需结合业务场景和数据分布进行权衡。