Mysql LIKE什么时候走索引，什么时候不走索引

在 MySQL 中，LIKE 查询是否走索引，主要取决于通配符的位置和使用的存储引擎。

使用索引的情况

1. 前缀匹配：

   • 当 LIKE 查询中的通配符出现在字符串的末尾时，查询可以利用索引。例如，LIKE 'abc%' 。
   • 这种情况下，索引可以快速定位以 abc 开头的所有记录，因为索引是按顺序存储的。

   SELECT * FROM table WHERE column LIKE 'abc%';
   

2. 全匹配：

   • LIKE 查询如果没有通配符，等同于 = 查询，也可以利用索引。

   SELECT * FROM table WHERE column LIKE 'abc';
   

不使用索引的情况

1. 前缀通配符：

   • 当 LIKE 查询中的通配符出现在字符串的开头时，查询无法利用索引。例如，LIKE '%abc' 或 LIKE '%abc%' 。
   • 这种情况下，MySQL 需要扫描整个表来找到匹配的记录，因为无法利用索引进行前缀查找。

   SELECT * FROM table WHERE column LIKE '%abc';
   SELECT * FROM table WHERE column LIKE '%abc%';
   

2. 中间通配符：

   • 当通配符出现在字符串的中间部分时，同样无法利用索引。例如，LIKE 'a%bc' 。

   SELECT * FROM table WHERE column LIKE 'a%bc';
   

特殊情况

1. MySQL 5.6 及以后版本的 InnoDB 存储引擎：

   • 对于较长的前缀匹配，即使索引的前缀较短，InnoDB 也可以使用索引。为了有效利用索引，可以使用前缀索引。

2. 字符集和排序规则：

   • 索引的使用还取决于列的字符集和排序规则。如果列的字符集和排序规则不一致，可能导致索引无法被正确利用。

示例

-- 能利用索引
CREATE INDEX idx_column ON table (column);
SELECT * FROM table WHERE column LIKE 'abc%';-- 不能利用索引
SELECT * FROM table WHERE column LIKE '%abc';
SELECT * FROM table WHERE column LIKE '%abc%';

优化建议

1. 前缀索引：

   • 对于较长字符串列，可以使用前缀索引。例如，CREATE INDEX idx_column ON table (column(10)); 这样在前 10 个字符上创建索引。

2. 全文索引：

   • 对于需要模糊查询的文本字段，可以考虑使用 MySQL 的全文索引 (FULLTEXT)，这在 InnoDB 和 MyISAM 存储引擎中都可以使用。

   CREATE FULLTEXT INDEX idx_column ON table (column);
   SELECT * FROM table WHERE MATCH(column) AGAINST ('search_term');
   

了解这些规则和优化建议，可以帮助你在设计数据库和编写查询时更有效地利用索引，从而提高查询性能。