复合索引最左前缀原则失效场景分析

复合索引失效主要由以下几种情况导致：1. 范围查询（如like或>）出现在复合索引列的中间，导致索引只能使用到范围查询之前的列；2. 查询条件未按索引列顺序排列，mysql无法利用索引的顺序特性；3. 对索引列使用函数或表达式操作，mysql无法直接利用索引；4. 索引列类型与查询条件类型不匹配，需要进行类型转换；5. 索引列包含null值，且查询条件使用is null或is not null。 理解这些失效场景，并根据查询条件设计合适的复合索引，才能提升数据库查询效率。

复合索引失效？别慌，让我来给你扒一扒！

很多朋友在数据库优化时，都听过“复合索引最左前缀原则”。 它就像数据库优化里的武林秘籍，但秘籍上往往没写清楚失效的场景，导致不少人掉坑里。这篇文章，咱们就来深入探讨一下这个原则失效的那些事儿，让你彻底掌握它！

这篇文章的目标很简单：让你彻底理解复合索引最左前缀原则，并能轻松识别它失效的场景，从而写出更高效的SQL。读完之后，你将能独立分析SQL语句，并针对索引进行优化，编写出性能更优的代码。

咱们先温习一下基础知识。复合索引，顾名思义，就是在一个表上建立多个列的索引。比如，在users表上创建index(name, age, city)索引，就是个复合索引。最左前缀原则的核心思想是：MySQL会优先使用索引中最左边的列进行匹配，只有当最左边的列匹配成功后，才会继续匹配后面的列。

那么，这个原则啥时候会失效呢？ 情况可不少！

场景一：范围查询（Range Query）

假设我们有index(name, age, city)索引，如果你的查询语句是SELECT FROM users WHERE name LIKE ‘A%’ AND age > 25 AND city = ‘Beijing’;，那么只有name列的索引会被用到。 LIKE操作符和>操作符都是范围查询，它们会阻止MySQL继续使用后面的age和city列的索引。 这是因为范围查询的特性决定了它无法高效地利用索引的顺序特性。 MySQL引擎在处理范围查询时，会先进行范围扫描，然后根据结果再进行过滤，导致索引后面的列无法参与优化。

场景二：不按索引顺序查询

还是index(name, age, city)索引，如果查询语句是SELECT FROM users WHERE city = ‘Beijing’ AND age > 25;，索引失效！ 因为你的查询条件没有按照索引列的顺序排列。MySQL只会在city列上进行查找，而不会利用age列的索引。 记住，顺序很重要！ 这就像你拿着地图找地方，如果路线不对，就算地图再好也白搭。

场景三：函数或者表达式操作索引列

如果你的查询语句是SELECT FROM users WHERE UPPER(name) = ‘JOHN’ AND age = 30;，索引失效！因为UPPER()函数会对name列进行转换，MySQL无法直接利用索引。 类似的，如果你的查询条件中包含表达式，例如SELECT FROM users WHERE name + age > 100;，索引也可能失效。 MySQL需要先计算表达式，然后再进行匹配，这会降低查询效率。

场景四：索引列类型不匹配

如果索引列是数值型，而查询条件是字符串类型，或者类型不匹配，索引也可能失效。 MySQL需要进行类型转换，这会增加额外的开销。

场景五：NULL值

如果索引列包含NULL值，那么在查询条件中使用IS NULL或IS NOT NULL时，索引可能失效。 这是因为NULL值无法进行比较。

代码示例 (MySQL):

假设我们的users表有如下结构和数据：

CREATE TABLE users (</p><pre class='brush:sql;toolbar:false;'>id INT PRIMARY KEY,name VARCHAR(255),age INT,city VARCHAR(255)

登录后复制