MySQL 索引

在 MySQL 中，索引 是通过特定的数据结构来加速查询操作。MySQL 支持多种类型的索引，其中 B+ 树索引 是最常见的一种。

1. B+ 树索引（B+ Tree Index）

B+ 树的特点：

1. 所有数据存储在叶子节点：

   • 所有的数据都存储在 B+ 树的叶子节点中，非叶子节点仅存储索引。

2. 叶子节点链表：

   • B+ 树的叶子节点通过链表连接，使得在进行范围查询时，可以快速遍历所有匹配的数据。

3. 多路平衡：

   • B+ 树是多路查找树，每个节点可以有多个子节点，保持树的高度较低，从而提高查询效率。

4. 高度平衡：

   • B+ 树保持高度平衡，所有叶子节点都位于树的同一层级，确保查找操作的时间复杂度为 O(log n)。

B+ 树的工作原理：

• 查找操作：查找时，通过从根节点开始，按照键值的大小比较，逐层向下查找，最终到达叶子节点。在叶子节点中，可以通过链表进行范围查询。
• 插入和删除操作：插入和删除操作会保持树的平衡，可能会引起节点的分裂或合并，保证 B+ 树始终是平衡的，从而保证查找的效率。

B+ 树和 B 树的区别：

• 数据存储位置：在 B 树中，数据存储在非叶子节点和叶子节点，而 B+ 树的所有数据都存储在叶子节点。
• 叶子节点链接：B+ 树的叶子节点通过链表连接，支持范围查询，而 B 树没有这种结构。
• 查询性能：B+ 树的查询性能通常优于 B 树，尤其是在范围查询时，B+ 树具有更高的效率。

2. 适合加索引的情况

加索引可以显著提高查询效率，特别是在处理大数据量的表和复杂查询时。以下是几种适合加索引的情况：

1. 查询条件中使用 JOIN 操作：

   • 如果两个表通过某个列进行连接查询，这个列应该加上索引。通过索引可以加速连接操作，避免全表扫描。

   SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

   在这种情况下，orders.customer_id 和 customers.customer_id 应该加索引。

2. 需要进行分组操作（GROUP BY）：

   • 当查询需要进行分组时（如 COUNT()、SUM() 等聚合函数），加上索引可以加速分组操作，尤其是在分组列上加索引。

   SELECT department, COUNT(*) FROM employees GROUP BY department;

3. 涉及到 ORDER BY 的查询：

   • 如果查询结果需要排序，在排序的列上加索引可以提高排序效率，避免进行全表扫描和排序。

   SELECT * FROM employees ORDER BY salary DESC;

4. 对于具有唯一性的字段（如用户名、邮箱等）：

   • 唯一性字段上加索引可以加速查询，并避免重复值，保证数据一致性。

   CREATE UNIQUE INDEX idx_email ON users (email);

5. 用于防止全表扫描的查询：

   • 对于经常在 WHERE 子句中查询的字段，尤其是范围较小的字段，应该考虑加索引来避免全表扫描。

   SELECT * FROM users WHERE age = 25;

6. 使用 DISTINCT 查询：

   • 如果查询包含 DISTINCT 操作，索引可以帮助快速排除重复记录，提高查询效率。

   SELECT DISTINCT department FROM employees;

7. 外键列：

   • 外键列的索引有助于提高查询性能，并在更新或删除父表记录时加速约束检查。

8. 经常用作过滤条件的列：

   • 如果某列经常作为过滤条件出现，并且该列的数据基数较高（即非唯一性且具有较好的选择性），应该考虑加索引。

3. 什么时候索引会失效？

虽然索引能够提高查询效率，但在某些情况下，索引会失效。以下是几种常见的情况：

1. 使用 OR 操作符：

   • 如果查询条件中使用了 OR 操作符，MySQL 可能会选择其中无法使用索引的条件，导致索引失效。

   SELECT * FROM users WHERE age = 25 OR name = 'Alice';

2. 使用 LIKE 进行模糊查询（尤其是以 % 开头）：

   • 对于 LIKE '%text%' 这种查询模式，索引会失效，因为 MySQL 无法通过索引定位模糊匹配的位置。

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

3. 使用不等号 <> 或 !=：

   • 使用不等于操作符通常会导致索引失效，因为 MySQL 不能直接通过索引来确定不等的条件。

   SELECT * FROM users WHERE age <> 30;

4. 数据类型不匹配：

   • 当查询条件中的数据类型与列的数据类型不一致时，可能导致索引失效，尤其是在比较字符串与数字时。

   SELECT * FROM users WHERE age = '30';  -- age 是 INT 类型

5. 函数操作：

   • 如果在查询条件中使用了函数，MySQL 将无法使用索引，因为函数会影响列的值，使得索引无法有效地参与查询。

   SELECT * FROM users WHERE YEAR(birth_date) = 1990;

6. NULL 值查询：

   • 查询含有 NULL 值的字段时，索引可能失效，尤其是在查询条件中使用 IS NULL 或 IS NOT NULL 时。

   SELECT * FROM users WHERE birth_date IS NULL;

7. 部分列的联合索引：

   • 在使用多列索引时，如果查询条件没有包含索引的最左边部分，可能导致索引失效。

   CREATE INDEX idx_name_age ON users (name, age);
   SELECT * FROM users WHERE age = 30;  -- 没有涉及 name 列，索引失效

总结

• B+ 树索引：适用于大多数查询，包括精确查找、范围查找和排序操作。它是 MySQL 默认的索引类型，支持高效的查询。

• 适合加索引的情况：

  • 常用的查询条件列，特别是用于 WHERE、JOIN、ORDER BY、GROUP BY 等的列。
  • 大数据量表、需要排序或唯一性要求的场景。
  • 经常进行连接查询、分组操作、排序和去重操作的列。

• 索引失效的情况：

  • 使用 OR、LIKE（以 % 开头）、函数操作、不等于操作等。