【MySQL 进阶之路】索引的使用

5.索引的使用规则

在数据库管理系统（DBMS）中，索引是提高查询效率的关键机制之一。MySQL索引优化是指通过设计、调整和选择合适的索引策略，以提高数据库的查询性能和降低资源消耗。以下是一些关键的索引使用规则：

1. 最左前缀法则

• 定义：联合索引的使用顺序必须从索引的最左列开始。查询时，如果跳过联合索引的最左边某一列，后续列的索引将失效。
• 例子：

  CREATE INDEX idx_example ON table(a, b, c); 
  

  如果查询条件是 (a=1 AND b=2)，会使用索引 idx_example。但如果查询条件是 (b=2 AND a=1)，索引可能失效。

2. 索引列运算

• 定义：尽量避免在索引列上进行运算操作（例如 +, -, *, =, LIKE 等），这会导致索引失效，影响查询效率。

• 例子：

  • 错误用法：WHERE YEAR(date_column) = 2024。此操作会使得 date_column 列的索引失效。
  • 正确用法：WHERE date_column >= '2024-01-01' AND date_column < '2025-01-01'。

3. OR 和 AND 连接条件

• 定义：在条件中使用 OR 时，如果 OR 前的条件使用了索引，而后面的条件没有使用索引，可能导致查询效率低下，因为 MySQL 会做全表扫描。
• 例子：

   -- 使用索引列        where column1 = 1 OR column2 = 2;
  

如果 column1 上有索引，而 column2 上没有，MySQL 会先用索引扫描 column1 = 1，然后再全表扫描来处理 column2 = 2，这会导致索引的部分失效。

4. 数据分布影响

• 定义：如果 MySQL 评估发现使用索引的效率低于全表扫描时（例如索引选择性差，表的数据分布不均匀），则会选择不使用索引。
• 例子：在某个字段值高度重复的情况下（如性别字段 male 和 female），使用索引的效果不好，MySQL 会倾向于选择全表扫描。

5. SQL 提示

• 定义：在复杂的查询中，我们可以通过 SQL 提示（Hint）来强制使用指定的索引，或者忽略某个索引，从而影响查询优化的执行。

• 常见 SQL 提示：

  • USE INDEX (index_name)：强制查询使用指定的索引。
  • IGNORE INDEX (index_name)：忽略指定的索引。
  • FORCE INDEX (index_name)：强制查询使用指定的索引，即使 MySQL 认为不需要使用该索引。

• 例子：

SELECT * FROM table USE INDEX (idx_example) WHERE column1 = 10;

6. 覆盖索引

• 定义：覆盖索引是指查询所需的所有字段都可以从索引中直接获取，而无需回表查询。这样可以显著提高查询效率。
• 使用场景：覆盖索引通常用于 SELECT 查询，只涉及索引字段的查询，且避免使用 SELECT *。
• 例子：
  假设有一个联合索引 idx_example (a, b, c)，且查询只需要返回 a 和 b 列：
  SELECT a, b FROM table WHERE a = 10 AND b = 20;
  此时，如果有 idx_example 索引，则可以直接通过索引返回结果，而不需要访问数据表。

7. 索引失效情况

• 常见情况：

  1. 运算操作：如 WHERE column1 + column2 = 10，column1 * 2 = 100 等，运算会导致索引失效。
  2. 字符串未加单引号：例如 WHERE name = test，应改为 WHERE name = 'test'。
  3. 头部模糊查询：如 WHERE column LIKE '%value%'，这种查询无法利用前缀索引优化，效率低。
  4. OR 和 AND 连接条件：如果条件不完全使用索引，可能会导致部分索引失效。

• 例子：

   -- 错误：运算操作会导致索引失效SELECT * FROM table WHERE salary + bonus = 5000;-- 错误：未加单引号SELECT * FROM table WHERE name = test;  -- 错误，test 应加引号-- 错误：头部模糊查询SELECT * FROM table WHERE name LIKE '%abc%';  -- 头部模糊查询无法利用索引-- 错误：OR 连接条件中，后半部分没有索引SELECT * FROM table WHERE column1 = 1 OR column2 = 2;  -- 如果 column2 没有索引，可能导致效率低
  

总结：

1. 最左前缀法则是联合索引的核心，必须遵循。
2. 尽量避免在索引列上进行运算。
3. OR 和 AND 连接条件中的索引使用要谨慎。
4. 覆盖索引是查询优化的关键手段。
5. 使用 SQL 提示时，合理选择索引来优化查询。
6. 注意索引失效的常见情况，避免不必要的性能损耗。

遵循索引的使用规则，尤其是最左前缀法则、避免索引列运算、合理使用 OR 和 AND 连接条件、利用覆盖索引及 SQL 提示，可以有效提升查询性能。同时，避免常见的索引失效情况，才能确保数据库查询的高效执行。