MySQL（尤其是 InnoDB 引擎）支持多种索引类型，不同索引适用于不同场景。以下是 MySQL 中常见的索引类型及其特点，按逻辑分类和物理结构两个维度说明：

---

一、按逻辑用途分类（常用）

1. 主键索引（Primary Key）

• 唯一且非空，一张表只能有一个。
• InnoDB 中，主键索引就是 聚簇索引（Clustered Index）：数据行与索引存储在一起。
• 选择原则：尽量使用自增整数（避免页分裂），避免使用 UUID（随机写性能差）。

2. 唯一索引（Unique Index）

• 索引列的值必须唯一，但允许有 NULL（多个 NULL 不冲突）。
• 适用于：手机号、订单号、用户 ID 等业务唯一字段。
• 创建方式：

  CREATE UNIQUE INDEX idx_user_phone ON users(phone);
  

3. 普通索引（Secondary Index / Normal Index）

• 最基础的索引类型，允许重复值、允许 NULL。
• 用于加速 WHERE、JOIN、ORDER BY 等查询。
• 示例：

  CREATE INDEX idx_order_status ON orders(status);
  

4. 组合索引（Composite Index / 联合索引）

• 对多个列创建一个索引，如 (col1, col2, col3)。
• 遵循 最左前缀原则（Leftmost Prefix Rule）：
  • 查询条件必须从最左列开始，才能命中索引。
  • 例如：索引 (a, b, c) 可用于 WHERE a=1、WHERE a=1 AND b=2，但不能用于 WHERE b=2。
• 建议：把区分度高（选择性好）的列放前面。

5. 前缀索引（Prefix Index）

• 对长字符串字段（如 VARCHAR(255)）只索引前 N 个字符。
• 减少索引大小，提升性能。
• 示例：

  CREATE INDEX idx_email_prefix ON users(email(20)); -- 只索引前20字符
  

• ⚠️ 注意：前缀长度需通过 SELECT COUNT(DISTINCT LEFT(email, N)) / COUNT(*) 估算区分度。

---

二、按物理结构分类（InnoDB）

1. 聚簇索引（Clustered Index）

• 数据即索引：叶子节点存储完整的数据行。
• InnoDB 自动使用主键作为聚簇索引；若无主键，则选择第一个唯一非空索引；否则用隐藏的 row_id。
• 优点：主键查询极快（一次 I/O）。
• 缺点：二级索引需“回表”（先查二级索引 → 再查聚簇索引）。

2. 二级索引（Secondary Index）

• 叶子节点存储的是主键值，不是完整数据。
• 查询流程：二级索引 → 主键值 → 聚簇索引 → 获取数据（回表）。
• 优化回表：使用 覆盖索引（Covering Index），即查询字段全部包含在索引中，无需回表。

---

三、特殊索引类型（特定场景）

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

• 用于 TEXT 或 VARCHAR 字段的全文搜索（如文章内容搜索）。
• 支持 MATCH() AGAINST 语法。
• InnoDB 从 MySQL 5.6 开始支持。
• 示例：

  CREATE FULLTEXT INDEX idx_content ON articles(content);
  SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库');
  

• ⚠️ 不适用于电商商品名等短文本（用 ES 更合适）。

2. 空间索引（SPATIAL Index）

• 用于 GEOMETRY 类型字段（如地图坐标、区域）。
• 仅 MyISAM 和 InnoDB（MySQL 5.7+）支持。
• 使用 R-TREE 结构，支持 ST_Contains() 等空间函数。

---

四、不推荐或已废弃的索引

索引类型	说明
HASH 索引	Memory 引擎支持，InnoDB 不支持（但自适应哈希索引 AHI 是内部优化）
RTREE 索引	旧版 MyISAM 用，现已被 SPATIAL 取代

💡 InnoDB 的 自适应哈希索引（Adaptive Hash Index, AHI） 是 InnoDB 自动为热点索引页构建的内存哈希结构，无需手动创建，可通过 SHOW ENGINE INNODB STATUS 查看。

---

五、索引设计黄金法则（电商场景重点）

1. 主键自增：避免 UUID 导致聚簇索引频繁页分裂。
2. 组合索引合理排序：高频过滤字段放前，范围查询字段放后（如 (user_id, create_time)）。
3. 避免冗余索引：(a,b) 和 (a) 同时存在是冗余的。
4. 大字段慎建索引：如 description 字段，优先考虑前缀索引或异构存储（如 Elasticsearch）。
5. 监控慢查询：用 EXPLAIN 分析是否命中索引，关注 type（最好 ref/range，避免 ALL）。

---

六、查看索引命令

-- 查看表索引
SHOW INDEX FROM table_name;

-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 100;

-- 查看索引使用统计（MySQL 8.0+）
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db' AND object_name = 'your_table';

---

✅ 总结：
在电商开发中，主键索引 + 唯一索引 + 合理的组合索引 足以覆盖 95% 场景。
牢记：索引不是越多越好，而是越精准越好。