在MySQL中,使用like进行模糊查询,在某些场景下是无法使索引生效的,如下所示:
- 当like值前有匹配符时
%abc
,无法使用索引
EXPLAIN select *from test where user_name like '%abc'
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+