如何使Like使用索引

如何使Like使用索引

• 不考虑索引覆盖的情况下
• • 只要like后需要匹配的字段前加%，索引就会失效。但是后面加%，能使用索引
  • • • 测试：
• 考虑 索引下推 加 索引覆盖 的情况下，like无论什么情况都会使用索引
• • • • 测试：

不考虑索引覆盖的情况下

只要like后需要匹配的字段前加%，索引就会失效。但是后面加%，能使用索引

测试：

表结构

CREATE TABLE `t2` (
  `id` bigint NOT NULL,
  `uuid` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `new_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
  UNIQUE KEY `uk_new_id` (`new_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Test case 1：

EXPLAIN SELECT * FROM t2 where uuid LIKE "%u%"

结果：

Test case 2：

EXPLAIN SELECT * FROM t2 where uuid LIKE "%u"

结果：

Test case 3:

EXPLAIN SELECT * FROM t2 where uuid LIKE "u%"

结果：

考虑 索引下推 加 索引覆盖 的情况下，like无论什么情况都会使用索引

测试：

表结构：

CREATE TABLE `t1` (
  `id` bigint NOT NULL,
  `uuid` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `nk_nameAgeUuid` (`name`,`age`,`uuid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

**注意：**上面这个表实际上已经索引覆盖了所有字段，因为非聚集索引叶子节点存的就是 索引值 加 主键， 下面例子 中的select * 实际上查的就是四个覆盖的字段

Test case 1：

EXPLAIN SELECT * FROM t1 where `name` LIKE "u%"

结果：

Test case 2：

EXPLAIN SELECT * FROM t1 where `name` LIKE "%u%"

结果：

Test case 3:

EXPLAIN SELECT * FROM t1 where `name` LIKE "%u"

结果：

但是如果表加一个字段，导致联合索引不覆盖。我们再看看 select * 的情况

CREATE TABLE `t1` (
  `id` bigint NOT NULL,
  `uuid` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `nk_nameAgeUuid` (`name`,`age`,`uuid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Test case 4:

EXPLAIN SELECT * FROM t1 where `name` LIKE "%u";

结果：

这个证明了一定要索引覆盖，% 前置才会生效

再拿这个表做一次索引覆盖查询：

Test case 5:

EXPLAIN SELECT id,`name`, age, uuid FROM t1 where `name` LIKE "%u";

结果：

上面的case说明，索引覆盖就会是like % 前置生效。