MySQL中是否支持like索引优化 ?
MySQL中,使用like关键字进行模糊查询到时候,是十分常见的应用场景,我们应该注意什么?
1. 如果通配符(% _)在匹配的第一个字符,则进行全表扫描。
例如:explain extended select * from like1 where name like '%abc';
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | like1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
2. 如果通配符(% _)不在匹配的第一个字符,则进行索引范围查找 。
例如:explain extended select * from like1 where name like 'abc%';
+----+-------------+-------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | like1 | range | nameindex | nameindex | 23 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)