为什么SQL查询以%开头索引就失效了呢?-51CTO.COM

今天我们来聊一下索引失效的问题，因为在开发的过程中，不光需要我们去写业务代码，有时候还可能会接手一些别人的代码，而别人写的一些 SQL ，可能有几百行，甚至有时候遇到一些时间较久的系统，一个完整的 SQL 甚至可能比一个方法都长，这时候，就会出现一种情况，需要你去优化，而优化的时候，我们就会首先从索引层面下手，这时候，我们就遇到了问题了，比如我们做模糊查询的时候，很多人的习惯是 LIKE '%xxxx%'，殊不知，这种虽然能满足条件，但是缺忽略了索引，导致索引不生效，今天我们就来说说这个。

建表验证

如果有些朋友不信的话，那么我们来验证一下，首先我们建一张表，然后写入数据，脚本如下：

CREATE TABLE `user`  (
  `ID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `USER_NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `AGE` int(11) NULL DEFAULT NULL,
  `ADDRESS` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `IP` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `PHONE` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `EMAIL` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `NAME_PHONE`(`USER_NAME`, `PHONE`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

图片

同时我们给 USER_NAME 和 PHONE 建立了联合索引，我们插入数据。

INSERT INTO `baseboot`.`user`(`ID`, `USER_NAME`, `AGE`, `ADDRESS`, `IP`, `PHONE`, `EMAIL`) VALUES ('28715757596573696', '测试人员', 12, '北京市海淀区', '127.0.0.1', '17788779981', '789911@163.com');
INSERT INTO `baseboot`.`user`(`ID`, `USER_NAME`, `AGE`, `ADDRESS`, `IP`, `PHONE`, `EMAIL`) VALUES ('28715757596573697', '开发人员', 22, '北京市海淀区', '127.0.0.1', '17788779981', '789911@163.com');

我们插入两条数据，接下来我们看看效果。

图片

然后我们通过 LIKE 来进行查询。

使用 LIKE '%xxx%'。

图片

使用 LIKE 'xxx%'。

图片

我们使用的是 EXPLAN 来分析我们的 SQL ，对比明显，一个使用了我们创建的索引，另外一个没有使用我们创建的索引，所以，为什么失效了呢？

为什么 LIKE '%xxxx%' 索引失效了？

当我们遇到这个问题的时候，这个题目也是面试官比较喜欢问到的问题，百分号在左和在右的区别是哪里呢？

我们都知道在SQL查询中，LIKE操作符用于模糊匹配字符串。通配符%在LIKE语句中的位置对查询的性能和结果有着显著的影响。当%位于LIKE语句的左边、右边或两边时，它们的行为和效果是不同的。

% 在左边：

当%位于LIKE语句的左边时，如LIKE '%keyword'，这种查询模式通常会导致索引失效。因为数据库系统需要扫描整个表或索引来查找包含指定关键词的记录，而不是利用索引的有序性来快速定位。这会导致查询性能下降，尤其是在大型表中。
索引失效的原因是数据库无法高效匹配这种模式。数据库难以直接根据索引列的值来定位数据，而需要进行全表扫描或索引的全扫描。

% 在右边：

当%位于LIKE语句的右边时，如LIKE 'keyword%'，这种查询模式通常可以利用索引，如果索引是按照从左到右的顺序构建的（如常见的B+树索引）。数据库系统可以从索引的起始位置开始，沿着索引的顺序查找以指定关键词开头的记录。
虽然索引可以帮助加速查询，但是如果查询中还有其他条件或限制，或者如果索引的选择性较低（即索引中的值大多相同或重复），那么查询性能可能仍然不如预期。

% 在两边：

当%同时位于LIKE语句的左边和右边时，如LIKE '%keyword%'，这种查询模式通常也会导致索引失效。因为数据库系统需要在整个表或索引中查找包含指定关键词的记录，而无法利用索引的有序性进行快速定位。
与%在左边的情况类似，这种查询模式需要进行全表扫描或索引的全扫描，从而导致查询性能下降。

使 LIKE '%xxx%' 索引生效的操作是什么样子呢？

那么我们如何让在左边的百分号能命中索引，让索引不失效呢？

我们可以这样操作：

图片

也就是说，我们需要把有索引的字段，放在最开始的位置，并且尽量的精确索引的字段，而没有其他的字段，这种情况也是可以命中索引的。

而这种情况下，其实就可以理解为是覆盖索引，就是下图的样子。

图片

那么，什么是覆盖索引呢？

什么是覆盖索引

覆盖索引（Covering Index）是一种特殊的索引结构，其设计初衷是为了提高查询性能。与传统的索引不同，覆盖索引包含了查询所需的所有列，因此，当执行查询时，数据库引擎可以直接从索引中获取所需的数据，而无需回表去访问实际的表数据。

在传统的索引结构中，索引通常只包含被索引列的值和指向表中对应行的指针。当查询需要从表中获取数据时，数据库引擎首先通过索引找到对应的指针，然后再根据这些指针去表中获取实际的数据。这个过程被称为“回表操作”，它增加了查询的IO操作和数据传输的开销，影响了查询性能。

而覆盖索引则避免了回表操作。因为覆盖索引本身已经包含了查询所需的所有列的值，所以数据库引擎在查询时可以直接从索引中获取数据，无需再去访问表。这样就减少了IO操作和数据传输，大大提高了查询效率。

要创建一个覆盖索引，需要确保索引包含了查询中WHERE子句和SELECT子句引用的所有列。这样，当执行查询时，数据库引擎就可以仅通过扫描索引来满足查询的需求，无需访问表。

值得注意的是，虽然覆盖索引可以提高查询性能，但它也会增加数据库的存储空间和索引维护的开销。因此，在设计和使用覆盖索引时，需要权衡其带来的性能提升和额外的存储与维护成本。

总的来说，覆盖索引是一种有效的优化手段，可以在某些情况下显著提高SQL查询的性能。然而，它的使用需要根据具体的查询和数据库环境进行仔细的考虑和测试。

总的来说，%在LIKE语句中的位置对查询性能有着重要影响。为了提高查询性能，尽量避免在LIKE语句的开始处使用%通配符，而是尽量将通配符放在查询模式的末尾或中间位置。同时，合理设计和使用索引也是提高查询性能的关键。

所以，你知道为什么失效了么？