mysql索引有哪几种

Question

mysql索引有哪几种？区别是什么？索引何时实效？ mysql索引有哪几种？

Answer 1

下午好🌅🌅🌅
本答案参考通义千问

MySQL 中的索引是用于提高数据库查询效率的重要工具。根据不同的分类方式，索引可以分为多种类型。以下是 MySQL 索引的几种主要类型、它们的区别以及何时失效的详细说明。

---

一、MySQL 索引的主要类型

1. 按数据结构分类

• B-Tree（平衡树）索引

  • 最常见的索引类型，适用于大多数场景。
  • 支持范围查询、排序、分组等操作。
  • 默认使用 B-Tree 索引（如 PRIMARY KEY, UNIQUE, INDEX）。

• Hash 索引

  • 基于哈希表实现，仅支持等值查询（=），不支持范围查询或排序。
  • 只有 Memory 存储引擎支持 Hash 索引。
  • 查询速度非常快，但无法用于模糊查询或排序。

• Full-text（全文索引）

  • 用于对文本内容进行全文搜索（如 MATCH AGAINST）。
  • 仅支持 MyISAM 和 InnoDB 引擎（从 MySQL 5.6 开始支持 InnoDB）。
  • 不适合数值型字段。

• R-Tree（空间索引）

  • 用于空间数据类型（如 GEOMETRY, POINT, LINESTRING）。
  • 主要用于地理信息系统（GIS）相关应用。

---

2. 按索引用途分类

| 类型 | 说明 | |--------------|----------------------------------------------------------------------| | 主键索引 | 每张表只能有一个，唯一且非空（PRIMARY KEY）。 | | 唯一索引 | 确保列中的值是唯一的（UNIQUE）。 | | 普通索引 | 允许重复值，仅用于加速查询（INDEX）。 | | 组合索引 | 多个列组成的索引，遵循最左前缀原则（如 (name, age)）。 | | 全文索引 | 用于文本内容的全文搜索（FULLTEXT）。 |

---

二、索引的区别总结

| 索引类型 | 是否唯一 | 是否支持范围查询 | 是否支持排序 | 是否支持模糊匹配 | 是否支持全文搜索 | |--------------|----------|------------------|--------------|------------------|------------------| | B-Tree | 否 | 是 | 是 | 是 | 否 | | Hash | 是 | 否 | 否 | 否 | 否 | | Full-text | 否 | 否 | 否 | 是 | 是 | | R-Tree | 否 | 是 | 是 | 是 | 否 |

---

三、索引何时失效？

以下情况会导致索引失效：

1. 使用 OR 连接多个条件时

   • 如果其中一个条件没有索引，或者 OR 的两个条件中有一个未使用索引，整个查询可能不走索引。

2. 对索引列进行函数运算

   • 例如：WHERE YEAR(create_time) = 2024，此时索引不会生效。

3. 使用 NOT、!=、<> 等操作符

   • 这些操作符可能导致全表扫描。

4. 索引列使用了 LIKE 通配符开头

   • 例如：WHERE name LIKE '%abc'，这种情况下索引不会被使用。

5. 索引列的数据类型不一致

   • 如索引列是 INT，而查询中使用字符串 '123'，会导致隐式转换，索引失效。

6. 查询条件中没有使用索引列

   • 例如：索引在 name 上，但查询语句是 WHERE age = 18，则索引不会被使用。

7. 组合索引未使用最左前缀

   • 例如：组合索引 (a, b, c)，如果只用 b 或 c，则索引失效。

8. 索引列上使用了 IS NULL 或 IS NOT NULL

   • 在某些版本中，这会导致索引失效。

9. 索引列的数据分布不均

   • 如果某列大部分值相同，优化器可能选择不使用索引。

10. 索引字段被强制类型转换

    • 如 WHERE id = '123'，虽然字段是整数，但使用字符串可能导致索引失效。

---

四、解决方案（如何避免索引失效）

1. 尽量避免使用 OR

   • 如果必须使用，考虑改写为 UNION 或使用覆盖索引。

2. 避免对索引列进行函数操作

   • 可以将查询条件改为直接使用索引列。

3. 避免使用 LIKE 通配符开头

   • 如果需要模糊查询，可以使用 FULLTEXT 索引。

4. 确保数据类型一致

   • 查询时与索引列的数据类型保持一致。

5. 合理使用组合索引

   • 遵循最左前缀原则，优先使用组合索引中最左边的列。

6. 定期分析表和索引

   • 使用 ANALYZE TABLE 来更新统计信息，帮助优化器做出更好的决策。

7. 避免在索引列上使用 NOT 或 !=

   • 考虑是否可以通过其他方式改写 SQL。

---

五、示例代码（避免索引失效）

示例：错误的索引使用

SELECT * FROM users WHERE name LIKE '%john';

此处 LIKE '%john' 导致索引失效。

修改后：

SELECT * FROM users WHERE name LIKE 'john%';

此时索引有效。

---

六、总结

重点总结：

• MySQL 索引主要包括 B-Tree、Hash、Full-text、R-Tree 等类型。
• 主键、唯一、普通、组合、全文 是按照用途划分的常见索引类型。
• 索引失效的原因包括：使用 OR、函数操作、通配符开头、数据类型不一致等。
• 避免索引失效的方法包括：合理使用组合索引、避免对索引列做运算、保持数据类型一致等。

如果你有具体的 SQL 语句或场景，我可以帮你进一步分析是否走了索引。