当前位置:首页 > 开发 > 数据库 > 正文

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

发表于: 2012-09-27   作者:丁林.tb   来源:转载   浏览次数:
摘要: 背景          MySQL中在对某个字段做包含匹配时可以用like。 先看这个结构和结果   CREATE TABLE `tb` (     `id` int(11) NOT NULL AUTO_INCREMENT,   `user_id` bigi

背景

         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_keysNULL, 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_keysNULL, 为什么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的过滤效果影响。

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

 

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

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
首先说明一点id 17的位置fdzyj我是存储了一个空格 可以看出'%%'和'% %'是不一样的空格也是个字符 所
一、优化概述 二、查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profili
简介 对于写出实现功能的SQL语句和既能实现功能又能保证性能的SQL语句的差别是巨大的。很多时候开发
1.6多个查询字段(常量条件) 多个查询字段的查询处理逻辑如下所示: JOIN:prepare阶段 setup_table
2、复合查询 在进行复合查询时,为了体现外连接(left join、right join)和一般联合查询的区别,对
2.5 LEFT JOIN查询 该测试主要用于测试LEFT JOIN与JOIN的处理逻辑上的差异,具体查询处理逻辑如下所
网上流传的各种的eclipse的调优的方法都大同小异,但是调优的基本上针对eclipse或者myclipse的本身
网上流传的各种的eclipse的调优的方法都大同小异,但是调优的基本上针对eclipse或者myclipse的本身
网上流传的各种的eclipse的调优的方法都大同小异,但是调优的基本上针对eclipse或者myclipse的本身,
网上流传的各种的eclipse的调优的方法都大同小异,但是调优的基本上针对eclipse或者myclipse的本身
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号