MySQL避免索引失效的方法详细介绍

避免索引失效

在MySQL中，索引是帮助MySQL高效获取数据的数据结构。它就像一本书的目录，通过索引可以快速定位到数据的具体位置，从而减少对数据库的扫描量，提高查询速度。索引可以存储在表中的一个或多个列上，创建索引后可以大大加快数据的检索速度，但也会占用额外的磁盘空间，并且在数据插入、删除和更新时需要对索引进行维护，这可能会降低这些操作的性能

尽管索引能显著提升查询性能，但在某些情况下，索引可能会失效，导致查询性能并未达到预期。以下是一些常见的导致索引失效的情况：

1. 全值匹配：
   通常，当你对索引中的所有列都指定了具体值时，索引会生效。但是，这里提到的“避免索引失效”更多是指在其他情况下，因为全值匹配本身就是为了利用索引的。

   假设有一个表employees，上面有一个索引(first_name, last_name)。

   sql复制代码
   
   SELECT * FROM employees WHERE first_name = 'John' AND last_name = 'Doe';
   

   这个查询将全值匹配索引中的所有列，因此索引会生效。

2. 最左前缀法则：
   对于复合索引（即索引包含多个列），MySQL会遵循最左前缀法则。这意味着，在查询条件中，如果索引列不是以索引中的第一个列开始，则索引可能不会被使用。例如，如果有一个索引是(A, B, C)，那么查询条件中只有A、A和B、A和B和C的组合才能有效利用索引，而只有B或B和C的组合则不会。

   继续使用上面的employees表和索引(first_name, last_name)。

   • 有效利用索引的查询：

   SELECT * FROM employees WHERE first_name = 'John';  
   SELECT * FROM employees WHERE first_name = 'John' AND last_name = 'Doe';
   

   • 不会利用索引的查询（仅针对last_name）：

   sql复制代码
   
   SELECT * FROM employees WHERE last_name = 'Doe';
   

3. 使用函数或计算：
   如果在索引列上使用了函数或进行了计算，那么索引可能不会被使用。例如，如果有一个索引在列date_col上，但查询条件为YEAR(date_col) = 2023，那么索引可能不会被利用。

   假设employees表有一个日期列hire_date，并且该列上有索引。

   -- 索引可能不会被利用  
   SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
   

   相比之下，如果查询条件没有使用函数：

   -- 索引会被利用  
   SELECT * FROM employees WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';
   

4. 隐式类型转换：
   如果索引列的数据类型和查询条件中的数据类型不一致，并且MySQL需要进行隐式类型转换来匹配它们，那么索引可能不会被使用。

   -- 隐式类型转换，索引可能不会被利用  
   SELECT * FROM employees WHERE employee_id = 123;  -- 假设employee_id是字符串类型，但查询中使用了数字
   

   如果employee_id是数字类型，则索引会被利用。

   例子：

   -- 索引可能不会被有效利用（取决于MySQL优化器的决定）  
   SELECT * FROM employees WHERE first_name != 'John';
   

5. 使用不等于（!= 或 <>）操作符：
   使用不等于操作符时，MySQL可能会选择全表扫描而不是使用索引，特别是当查询条件中的列是索引列时。

   -- 索引可能不会被有效利用（取决于MySQL优化器的决定）  
   SELECT * FROM employees WHERE first_name != 'John';
   

6. 使用IS NULL 或 IS NOT NULL：
   对于索引列，使用IS NULL或IS NOT NULL条件可能会导致索引失效，尽管这取决于MySQL的版本和具体的查询优化器行为。

   -- 对于索引列，IS NULL 或 IS NOT NULL 可能导致索引失效（取决于MySQL版本和查询优化器）  
   SELECT * FROM employees WHERE email IS NULL;  -- 假设email列上有索引
   

7. LIKE以通配符开始：
   当使用LIKE操作符并且模式以通配符（如%）开始时，MySQL可能无法使用索引。例如，name LIKE '%abc'不会利用name列上的索引。

   如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

   1.explain select * from tb_seller where name like '传智播客%';
   2.explain select * from tb_seller where name like '%传智播客';
   3.explain select * from tb_seller where name like '%传智播客%';
   

8. OR条件：
   当查询条件包含OR时，如果OR连接的两个条件分别指向不同的索引列，MySQL可能无法有效地使用索引。

   -- 如果OR连接的两个条件分别指向不同的索引列，MySQL可能无法有效地使用索引  
   SELECT * FROM employees WHERE first_name = 'John' OR last_name = 'Doe';
   

9. 索引列参与计算或函数：
   如果索引列参与了计算或函数操作，则可能导致索引失效。

   -- 索引列参与了计算，索引可能不会被利用  
   SELECT * FROM employees WHERE first_name = CONCAT('J', 'ohn');