Nemo

Nemo 关注TA

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

Nemo

Nemo

关注TA

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

  •  普罗旺斯
  • 负责帅就完事了
  • 写了1,493,291字

该文章投稿至Nemo社区   数据库  板块 复制链接


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

发布于 2018/05/25 15:39 2,761浏览 0回复 5,885

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数据库有效。

点赞(0)
点了个评