LinkNemo





Nemo博客

路漫漫其修远兮,吾将上下而求索。

  1. 博客主页
  2. 最新文章
  3. SQL like%%模糊搜索优化方案一 -- 主键查询

SQL like%%模糊搜索优化方案一 -- 主键查询

mysql> select count(*),now() from music_song_base;
+----------+---------------------+
| count(*) | now()               |
+----------+---------------------+
|   474025 | 2018-05-25 15:30:59 |
+----------+---------------------+
1 row in set (0.20 sec)

这张music_song_base表已经有47w的数据,这时候尝试使用like%%搜索关键字:

mysql> select id,name,cover from music_song_base where name like '%消愁%';
+--------+----------------------+---------------------------------------------------------------------------------------+
| id     | name                 | cover                                                                                 |
+--------+----------------------+---------------------------------------------------------------------------------------+
|  30809 | 消愁                 |                                                                                       |
|  30845 | 消愁 (3D版)          |                                                                                       |
|  31724 | 消愁 (Live)          |                                                                                       |
+--------+----------------------+---------------------------------------------------------------------------------------+
3 rows in set (28.58 sec)

需要说明的是,name字段已经建立了索引,但是,整句sql依然执行了28.58s。

查看sql释义:


mysql> explain select id,name,cover from music_song_base where name like '%消愁%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: music_song_base
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 413449
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

显而易见,在这个搜索中,name字段的索引并没有用上,like%%依然导致了全表扫描。

尝试嵌套主键搜索的方式来优化:

mysql> select id,name,cover from music_song_base where id in(select id from music_song_base where name like '%消愁%');
+--------+----------------------+---------------------------------------------------------------------------------------+
| id     | name                 | cover                                                                                 |
+--------+----------------------+---------------------------------------------------------------------------------------+
|  30809 | 消愁                 |                                                                                       |
|  30845 | 消愁(3D版)          |                                                                                       |
|  31724 | 消愁(live)         |                                                                                       |
+--------+----------------------+---------------------------------------------------------------------------------------+
3 rows in set (0.34 sec)

耗时0.34s。

查看sql释义,发现确实也是使用了name字段的索引:

mysql> explain select id,name,cover from music_song_base where id in(select id from music_song_base where name like '%消愁%') \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: music_song_base
   partitions: NULL
         type: index
possible_keys: PRIMARY,id
          key: name
      key_len: 2003
          ref: NULL
         rows: 412944
     filtered: 11.11
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: music_song_base
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,id
          key: PRIMARY
      key_len: 4
          ref: city_circle.music_song_base.id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

ERROR:
No query specified

证明这种方式来优化like%%是OK的。

不过需要注意的是,,这种方式只对innodb数据库有效。

评论

About ME

Nemo

Nemo

欢迎使用这个博客!如果您在使用的过程中有好的建议或者遇到问题,欢迎给我留言~

Other ME

性别:男

生日:1993-01-01

所在城市:深圳市

工作职务:菜鸟工程师

创建时间:2015/12/31 02:13:14

My SUMMARY

我的文章:357

我的留言:42

我的粉丝:44

我的关注:97

我的点击:255210

么么哒