MySQL Like查询浅析

引言

最近迷惑一个问题，like查询到底会不会使用索引，什么情况下会使用索引。之前看到说避免使用like ‘%string_%’这样的查询，因为会进行全表扫描；测试了下发现这种说法还是比较片面的；
首先需要了解一个概念：覆盖索引，即查询的字段包含在索引字段中，这就是一个覆盖索引，这样的查询无需回表，在索引中即可完成查询；（详见高性能mysql）
实例：

CREATE TABLE `test` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `content` varchar(333) NOT NULL,
  `perm` set('can_read','can_write','can_delete') DEFAULT NULL,
  `name` varchar(50) NOT NULL DEFAULT 'sun',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;

覆盖索引

• 右前缀匹配

mysql> explain SELECT name FROM test where name like '%s' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: index
possible_keys: NULL
          key: name
      key_len: 152
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where; Using index

• 左前缀匹配

  mysql> explain SELECT name FROM test where name like 's%' \G
  *************************** 1. row ***************************
         id: 1
  select_type: SIMPLE
      table: test
  partitions: NULL
       type: range
  possible_keys: name
        key: name
    key_len: 152
        ref: NULL
       rows: 1
   filtered: 100.00
      Extra: Using where; Using index

  结果分析：
  a.左前缀匹配时，possible_keys和key都命中了索引，type为range表示在索引中进行范围查找，rows显示实际上只扫描了一行就定位到了结果，性能是最佳的；
  b.右前缀匹配时，key显示还是使用了索引，type为index表示扫描了整个索引进行数据查找，扫描的rows为表的总行数；

非覆盖索引

• 右前缀匹配

mysql> explain SELECT content FROM test where name like '%s' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where

• 左前缀匹配

mysql> explain SELECT content FROM test where name like 's%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: range
possible_keys: name
          key: name
      key_len: 152
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition

结果分析：
a.右前缀匹配时，possible_keys和key都为null,说明未使用索引，type为all表示进行了全表扫描，如果表中数据量较大，耗时会比较久，性能最差；至于为什么会不走索引，因为mysql认为按照索引去查询的话，会产生大量random disk IO，所以直接选择了全表扫描；
b.左前缀匹配时，可命中索引，在索引内范围查找，extra中的using index condition 表示索引下推；

结论

覆盖索引条件下，like无论是“%ss”“ss%”,”%ss%”均可以使用索引，因为查询均在索引中即可完成；
非覆盖索引条件下，like仅在“ss%”情况下可以使用索引，“%ss”,”%ss%”查询条件下需全表扫描，尽量避免这种查询；