索引类型包括B树、哈希、全文和空间索引，应根据查询模式选择；避免在WHERE中使用函数、隐式转换、%开头的LIKE、OR及NOT条件以防失效；在高选择性且常用于查询、JOIN、排序的列上创建索引，避免在频繁更新、低选择性或小表上创建索引。
索引就像一本书的目录，能帮你快速找到想要的信息，而不是一页一页地翻。在SQL中，索引是一种优化查询速度的数据结构。
提升查询性能的关键就在于合理使用索引。
索引类型有哪些？如何选择合适的索引？
SQL中常见的索引类型包括：
- B树索引（B-Tree Index）： 这是最常见的索引类型，适用于范围查询和排序。几乎所有数据库都支持。它就像一棵平衡树，查找效率稳定。
- 哈希索引（Hash Index）： 适用于等值查询，速度非常快。但不支持范围查询和排序。MySQL的Memory存储引擎支持。
- 全文索引（Fulltext Index）： 适用于文本搜索，例如在文章中查找关键词。MySQL和PostgreSQL都支持。
- 空间索引（Spatial Index）： 适用于地理空间数据查询，例如查找附近的餐馆。MySQL和PostgreSQL都支持。
选择合适的索引类型取决于你的查询模式。如果经常进行范围查询，选择B树索引。如果只需要等值查询，可以考虑哈希索引。对于文本搜索，使用全文索引。对于地理空间数据，使用空间索引。
举个例子，假设你有一个
users表，包含
id、
name、
age字段。如果你经常根据
CREATE INDEX idx_email ON users (email);
但如果你的查询是精确匹配email，例如
WHERE email = 'example@example.com'，那么哈希索引可能更快（但要注意哈希索引的限制）。
索引虽好，可不要贪多哦。
如何避免索引失效？
索引失效是指查询优化器无法使用索引，导致查询性能下降。以下是一些常见的索引失效情况：
-
使用函数或表达式： 在
WHERE
子句中使用函数或表达式会导致索引失效。例如：WHERE YEAR(date) = 2023
。 -
隐式类型转换： 如果查询条件的数据类型与索引列的数据类型不匹配，可能会发生隐式类型转换，导致索引失效。例如，如果
id
列是整数类型，而查询条件是字符串类型：WHERE id = '123'
。 -
LIKE
查询以%
开头：LIKE '%keyword'
会导致索引失效，因为无法利用索引的前缀匹配。 -
OR
条件： 如果OR
条件中的一个列没有索引，可能会导致整个查询无法使用索引。 -
NOT
条件：NOT
、!=
、<>
等否定条件通常会导致索引失效。 - 组合索引的顺序： 如果查询条件没有按照组合索引的顺序使用，可能会导致部分索引失效。
为了避免索引失效，应该尽量避免在
WHERE子句中使用函数或表达式，保持数据类型一致，避免
LIKE查询以
%开头，尽量使用
AND代替
OR，避免使用否定条件，并确保查询条件按照组合索引的顺序使用。
例如，如果有一个组合索引
idx_name_age，包含
name和
age字段，查询条件应该是
WHERE name = 'John' AND age = 30，而不是
WHERE age = 30 AND name = 'John'。
什么时候应该创建索引，什么时候不应该创建索引？
创建索引可以提高查询性能，但也会带来一些额外的开销。
-
应该创建索引的情况：
- 经常用于
WHERE
子句的列。 - 经常用于
JOIN
操作的列。 - 经常用于
ORDER BY
或GROUP BY
子句的列。 - 具有高选择性的列（即列中不同值的数量很多）。
- 经常用于
-
不应该创建索引的情况：
- 频繁更新的列。每次更新都会导致索引的维护，降低性能。
- 选择性低的列（即列中不同值的数量很少）。例如，性别列，只有男和女两个值。
- 小表。对于小表，全表扫描可能比使用索引更快。
- 不经常使用的列。
索引的维护成本很高，包括存储空间和更新开销。所以，在创建索引之前，要仔细评估其带来的收益是否大于成本。可以使用数据库的性能分析工具来帮助你做出决策。比如MySQL的
EXPLAIN语句可以告诉你查询是否使用了索引。
总之，索引是提升SQL查询性能的强大工具，但需要谨慎使用。选择合适的索引类型，避免索引失效，并权衡创建索引的成本和收益，才能真正发挥索引的优势。
