MySQL 用like “%x“,索引就一定会失效吗？

在 MySQL 中，使用 LIKE "%x" 这种模式匹配时，索引是否会失效，取决于使用的存储引擎和具体的查询条件。当你在一个列上使用通配符查询时，索引的利用情况会有所差别。

一般规则

1. 前缀匹配 (LIKE "x%")

当通配符位于字符串末尾时，可以有效利用索引。例如：

SELECT * FROM my_table WHERE my_column LIKE 'abc%';

在这种情况下，如果 my_column 上有索引，MySQL 会使用索引来加速查询，因为它只需要进行前缀扫描。

2. 后缀匹配 (LIKE "%x")

当通配符位于字符串开头时，索引通常会失效。例如：

SELECT * FROM my_table WHERE my_column LIKE '%def';

MySQL 需要扫描整个表以匹配后缀，索引在这种情况下无法使用。

3. 包含匹配 (LIKE "%x%")

当通配符出现在字符串开头和中间时，索引也通常会失效。例如：

SELECT * FROM my_table WHERE my_column LIKE '%ghi%';

同样，MySQL 需要对整个表进行扫描以找到匹配的记录，索引在这种情况下无效。

特殊情况

• 全文索引：对于 MySQL 中变长的大文本数据，如果需要模糊匹配搜索，可以使用全文索引（Full-Text Index）。这种索引可以有效处理复杂的文本查询。

  CREATE FULLTEXT INDEX idx_fulltext_my_column ON my_table(my_column);
  

  使用全文索引进行查询：

  SELECT * FROM my_table WHERE MATCH(my_column) AGAINST('search_term');
  

• 前缀索引：在某些特定场景下，可以创建前缀索引。如果查询多为前缀查询，可以为部分字符创建索引。例如：

  CREATE INDEX idx_prefix_my_column ON my_table(my_column(10));
  

示例及分析

假设我们有一个表 users，且 last_name 列上有一个普通的 B-Tree 索引：

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  last_name VARCHAR(255),
  INDEX idx_last_name (last_name)
);

-- 插入一些示例数据
INSERT INTO users (last_name) VALUES ('Smith'), ('Johnson'), ('Williams'), ('Jones'), ('Brown');

前缀匹配

EXPLAIN SELECT * FROM users WHERE last_name LIKE 'Smi%';

解释计划会显示 MySQL 正在使用 idx_last_name 索引，因为查询是前缀匹配。

后缀匹配

EXPLAIN SELECT * FROM users WHERE last_name LIKE '%son';

解释计划显示 MySQL 进行全表扫描 (table scan)，索引 idx_last_name 失效。

包含匹配

EXPLAIN SELECT * FROM users WHERE last_name LIKE '%llia%';

解释计划同样显示 MySQL 进行全表扫描 (table scan)，索引 idx_last_name 失效。

优化建议

1. 全文索引：对于复杂的文本匹配，可以考虑使用全文索引。
2. 前缀匹配策略：尽量转换查询为前缀匹配，利用索引。
3. 合理设计索引：为如需精确匹配或低基数的列建立 B-Tree 索引，或者为大量文本数据使用 Full-Text 索引。
4. 分区表和分片：对于超大规模数据表，可以考虑使用分区或分片方案来降低每次查询的数据量。
5. 缓存：使用缓存层（如 Redis）来缓存常见查询和结果，减少对数据库的压力。

总之，当使用 LIKE "%x" 模式匹配时，索引在多数情况下会失效，因为 MySQL 无法利用 B-Tree 索引进行后缀或子字符串匹配。然而，通过优化查询条件、使用全文索引等策略，可以在某些场景下提升查询性能。如果有具体的查询实例和表设计，进一步优化可能需要更详细的分析。