MySQL中like语句及相关优化器tips

背景

         MySQL中在对某个字段做包含匹配时可以用like。

先看这个结构和结果

 

CREATE TABLE `tb` (

 

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` bigint(20) DEFAULT NULL,

  `title` varchar(128) NOT NULL,

  `memo` varchar(2000) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `title` (`title`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

mysql> explain select * from tb where title like ‘%abcd%’;

+—-+————-+——-+——+—————+——+———+——+——+————-+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+—-+————-+——-+——+—————+——+———+——+——+————-+

|  1 | SIMPLE      | tb    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |

+—-+————-+——-+——+—————+——+———+——+——+————-+

1 row in set (1.65 sec)

 

由于like用的是 ‘%xx%’, 不符合前缀匹配的规则，因此用不上索引title，只能作全表扫描。

 

问题

  以上为官方回答。但是如果是在 InnoDB这种聚集索引组织的表中，假设这个表单行很大，比如后面还有若干个类似memo的字段。

  这样聚集索引会很大，导致全表扫描需要读更多的磁盘。而理想情况应该是这个流程

1)       遍历title索引，从中读取和过滤所有title中匹配like条件的id

2)       用id到聚簇索引中读数据。

在单行很大，而like能够过滤掉比较多语句的情况下，上面的流程肯定比全表扫描更快，也更省资源。

 

FORCE INDEX行不行？

         第一个反应是用force index。

mysql> explain select * from tb force index(title) where title like ‘%abcd%’;

 

+—-+————-+——-+——+—————+——+———+——+——+————-+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+—-+————-+——-+——+—————+——+———+——+——+————-+

|  1 | SIMPLE      | tb    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |

+—-+————-+——-+——+—————+——+———+——+——+————-+

1 row in set (0.00 sec)

 

 

         显然不行。原因是通常情况下，force index只能从possible_keys中强制选择某一个索引，但是这个查询的possible_keys是NULL, force index 无效。

 

覆盖索引

   我们想到覆盖索引，试验这个语句。

mysql> explain select id from tb  where title like ‘%abcd%’;                  

 

+—-+————-+——-+——-+—————+——-+———+——+——+————————–+

| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |

+—-+————-+——-+——-+—————+——-+———+——+——+————————–+

|  1 | SIMPLE      | tb    | index | NULL          | title | 386     | NULL |    1 | Using where; Using index |

+—-+————-+——-+——-+—————+——-+———+——+——+————————–+

1 row in set (0.00 sec)

 

我们看到这个语句用上了title索引，而且Using index表明用上了覆盖索引。

 

有同学可能会疑惑，这里possible_keys是NULL, 为什么key用上了title，应了那句“nothing imposible”?

 

实际上在MySQL优化器里面专门加了这一段，在type= JT_ALL时，会特别扫一下所有能够满足的覆盖索引，并找长度最短的那个。

这么做的考虑就是基于选择小的索引，减少读盘。重要的是，这个优化对于现有的引擎是通用的。

 

因此上面说的“通常情况下”的例外就是：force index可以强制使用覆盖索引。比如常见的 select count(*) from tb. 这时候你force index所有已存在的索引都是可以生效的。

 

权宜之计

         了解了覆盖索引的效果，我们可以把查询改写为如下，以满足我们最开始希望的执行流程。

mysql> explain Select * from (select id from tb where title like ‘%a’) t1 join tb  using (id);

 

+—-+————-+————-+——–+—————+————+———+——-+——+————————–+

| id | select_type | table       | type   | possible_keys | key        | key_len | ref   | rows | Extra                    |

+—-+————-+————-+——–+—————+————+———+——-+——+————————–+

|  1 | PRIMARY     | <derived2>  | system | NULL          | NULL       | NULL    | NULL  |    1 |                          |

|  1 | PRIMARY     | tb | const  | PRIMARY       | PRIMARY    | 4       | const |    1 |                          |

|  2 | DERIVED     | tb | index  | NULL          | idx_userid | 386     | NULL  |    1 | Using where; Using index |

+—-+————-+————-+——–+—————+————+———+——-+——+————————–+

3 rows in set (0.00 sec)

 

 

从explain结果中看执行流程是按照我们之前描述的那样，但是引入了JOIN。

 

 补充说明

JOIN写法还会引入primary key查询的时候是随机查询，因此最终的效率受like的过滤效果影响。

这个改写对性能的提升效果取决于要使用的索引与总数据量的大小比较，需要作应用测试。

 

评论

3 楼 丁林.tb 2012-09-29  

jinnianshilongnian 写道

oracle中没有这种覆盖索引吧？

有的。
只是oracle就直接最原始的写法就能够满足我们要的那个最优流程了

2 楼 jinnianshilongnian 2012-09-28  

oracle中没有这种覆盖索引吧？

1 楼 jinnianshilongnian 2012-09-28  

FORCE INDEX 不行的原因 是因为那个 * 造成的查询的数据不在索引里

引用

这个改写对性能的提升效果取决于要使用的索引与总数据量的大小比较，需要作应用测试。

有木有12306的同学测试下  哈哈