答案

索引是特殊的数据结构（通常基于B树），用于根据特定列或列集快速查找数据。索引加快了数据的检索、排序和过滤，减少了查看的行数。

索引类型：

• 普通索引（B-tree, hash）
• 复合索引（多列索引）
• 唯一索引（确保值的唯一性）
• 覆盖索引（covering）
• 全文索引（full-text）

索引加快了：

• WHERE ... = ...
• 在索引列上进行的JOIN
• 在索引列上进行的ORDER BY和GROUP BY

可能减慢：

• 插入、更新、删除——索引需要额外的操作来维护其结构。
• 如果查询经常使用没有索引的列，则索引将毫无用处，而且在大量插入时甚至会造成损害。

创建索引的示例：

CREATE INDEX idx_user_email ON users (email);

索引无效的示例：

SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- 如果索引建立在email上，而查询使用了函数lower(email)，则索引不被使用！

误导性问题

如果对表中所有列添加索引，所有SELECT查询的执行是否总是会加快？

答案：
不。索引仅加快那些严格根据索引列进行过滤或排序的查询，且没有应用任何函数或操作来阻止使用索引。过多的索引不仅会减慢INSERT/UPDATE/DELETE的速度，还会占用大量空间，某些复杂查询甚至可能完全绕过索引（例如，在使用范围表达式扫描时）。

示例：

SELECT * FROM orders WHERE year(order_date) = 2023;
-- 如果索引仅存在于order_date上，则由于year()函数，索引不工作

---

历史

在电子邮件系统中，创建了一个触发器，为每个经常使用的字段创建索引。经过半年，系统性能下降——每次插入或修改行所花费的时间增加了4-5倍。经过审核，减少了索引数量，系统速度恢复。

---

历史

在广告平台中，常常有带有substring(url, 1, 10)过滤器的SELECT查询。尽管存在针对url的索引，但由于使用了substring函数，SQL并未调用索引。解决方案是引入一个单独的字段来进行这种选择，并对其建立索引。

---

历史

在忠诚度程序中，创建了一个复合索引，包含字段(customer_id, shop_id)。在仅根据shop_id的查询中，索引未被应用，导致进行全表扫描（full scan）。这导致计算奖金时失去了索引的收益。优化的结果是为shop_id建立了单独的索引。