Mysql模糊查找

在业务开发过程中，经常会碰到需要搜索的需求；结合msyql在模糊搜索的时候，很明显会用到like语句，一般情况下，在数据量比较小的时候，按行检索的效率也不是很明显的低效，但当达到百万级，千万级数据量的时候，查询的效率低下是一回事，很可能把数据库拖垮，严重影响可用性。这个时候提高查询效率就显得很重要！

模糊查找

一般情况，我们在查找时候的写法(field肯定是已经建了索引):

1
SELECT `column` FROM `table` WHERE `field` like '%keyword%';

上面的语句用explain解释来看，SQL语句并未使用索引，而是全表扫描，数据量比较大的时候，这效率可想而知。

对比下面的写法：

1
SELECT `column` FROM `table` WHERE `field` like 'keyword%';

这样的写法用explain解释看到，SQL语句使用了索引，搜索的效率大大的提高了。

但是有的时候，我们在做功能需求的时候，并非要想查询的关键词都在开头，所以如果不是特别的要求，”keywork%”并不能适应所有的查找。

所以，我们需要另一种方法。

LOCATE（’substr’,str,start_pos）

Mysql提供LOCATE函数，该方法返回查询字符串在被查询字段下的索引。第一个参数为要查询的字符串，第二个为数据库中的字段名称，第三个代表从字段对应的值的第几个字符串开始查找.

例， 有如下表：

1
2
3
4
5
CREATE TABLE `meetings` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `des` varchar(225) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

以下sql则查询des字段匹配“hello”的行数。这个“hello” 在des中的可以是开头，可以是结尾，也可以是中间，非常方便。

1
SELECT * from meetings where LOCATE('hello',`des`) > 0;

LOCTATE这个函数有第三个参数，是查找的起始位置，比如可以在上面的sql中加入：

1
SELECT * from meetings where LOCATE('hello',`des`, 5) > 0;

我们使用explain来检查执行是否命中索引，会发现对搜索的字段如果存在索引，确实可以命中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain SELECT * from meetings where LOCATE('hello',`des`) > 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: meetings
   partitions: NULL
         type: index
possible_keys: NULL
          key: des_meeting
      key_len: 227
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

如上explain的输出语句，确实使用了索引。

POSITION(‘substr’ IN field)

position可以看做是locate的别名，功能跟locate一样。其实个人理解，position只是查找是否包含子串，不能指定位置开始。

1
SELECT * from meetings where POSITION('hello' in `des`);

INSTR(str,’substr’)

1
SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0

这个INSTR也是子串判断

FIND_IN_SET

FIND_IN_SET(str1,str2)

返回str2中str1所在的位置索引，其中str2必须以”,”分割开。

1
SELECT * FROM person WHERE FIND_IN_SET('apply',name);

name的内容是以逗号分隔的，如

1
apple,pear,orange

就可以使用FIND_IN_SET

1
select * from `table` where FIND_IN_SET('apple', name);

个人觉得这个主要是针对sql数组数据的查找; 数组数据以逗号分隔存储到一个字段，FIND_IN_SET可以快速找到包含数组元素的行。

总结

Mysql在进行模糊查找需要注意，前缀匹配的时候会用到索引，前后都模糊，则无法使用索引；

可以使用Mysql提供的函数来“曲线救国”来命中索引。