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

转载 2013年12月01日 21:35:10

背景

         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的过滤效果影响。

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

from :   http://dinglin.iteye.com/blog/1687358




同样的道理


  1. mysql> explain select count(*) from s where id<>2;  
  2. +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+  
  3. | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |  
  4. +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+  
  5. |  1 | SIMPLE      | s     | index | id            | id   | 5       | NULL |    3 | Using where; Using index |  
  6. +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+  
  7. 1 row in set (0.00 sec)  

 在这个查询中,我们查询的是id不等于2的count(*),结果explain显示用到了index。

根据以前的知识,我们知道,数据库中使用不等号是不会用到索引的。那么这里是怎么回事呢?

下面是我个人的理解。

这里涉及到了一个“覆盖索引”的问题。

先说一下什么是覆盖索引呢?其实这不是一个特定结构的索引。只是说,如果我们要查询的东西,能够直接从索引上得到,而不用再去表中取数据,那么这个使用的索引就是覆盖索引。

回到我们的问题。select count(*) from s where id<>2;由于id列上有索引,而这个查询在索引上完全能够做到(查找索引上id不是2的即可)。所以这里是利用了覆盖索引的思想。

------------------------------------------------

上面的 查询是用了索引的,再看下面这个:

  1. mysql> explain select count(score) from s where id<>2;  
  2. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |  
  4. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
  5. |  1 | SIMPLE      | s     | ALL  | id            | NULL | NULL    | NULL |    3 | Using where |  
  6. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
  7. 1 row in set (0.01 sec)  

由于score不在索引上,所以这里用不到覆盖索引。那么Extra列自然也不会有using index 了。 
在《高性能mysql》中,page121页第二段也提到了,extra中,出现了Using index是指用到了覆盖索引。

同样的道理


  1. mysql> explain select count(*) from s where id<>2;  
  2. +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+  
  3. | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |  
  4. +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+  
  5. |  1 | SIMPLE      | s     | index | id            | id   | 5       | NULL |    3 | Using where; Using index |  
  6. +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+  
  7. 1 row in set (0.00 sec)  

 在这个查询中,我们查询的是id不等于2的count(*),结果explain显示用到了index。

根据以前的知识,我们知道,数据库中使用不等号是不会用到索引的。那么这里是怎么回事呢?

下面是我个人的理解。

这里涉及到了一个“覆盖索引”的问题。

先说一下什么是覆盖索引呢?其实这不是一个特定结构的索引。只是说,如果我们要查询的东西,能够直接从索引上得到,而不用再去表中取数据,那么这个使用的索引就是覆盖索引。

回到我们的问题。select count(*) from s where id<>2;由于id列上有索引,而这个查询在索引上完全能够做到(查找索引上id不是2的即可)。所以这里是利用了覆盖索引的思想。

------------------------------------------------

上面的 查询是用了索引的,再看下面这个:

  1. mysql> explain select count(score) from s where id<>2;  
  2. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |  
  4. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
  5. |  1 | SIMPLE      | s     | ALL  | id            | NULL | NULL    | NULL |    3 | Using where |  
  6. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+  
  7. 1 row in set (0.01 sec)  

由于score不在索引上,所以这里用不到覆盖索引。那么Extra列自然也不会有using index 了。

相关文章推荐

mysql like查询字符串示例语句

MySQL提供标准的SQL模式匹配,以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式一、SQL模式SQL的模式匹配允许你使用“_”匹配任何单个字符,而“%”匹配任意...

MySql语句(Like模糊匹配)

Like用来匹配一部分的,% 任何字符出现任何位置区分大小写。 SELECT * FROM TABLE WHERE col Like '%a';//检索以a结尾的内容 SELECT * FROM TA...

关于mysql中的like语句带参数问题

一个题目引入: 查询与“B1100”银行在同一城市(假设银行名称的第5和第6个字符为城市名称)的其他的银行的名称。 表结构: 数据: 这里首先有一个问题,如何获取第五个和第...

用table+物化视图优化复杂语句中以%开头like特定格式字段查询

基于此复杂语句中性能是由语句中的like '%%'引

【译】一些优化你的SQL语句的TIPs

http://www.cnblogs.com/CareySon/archive/2012/02/15/2352256.html   简介     对于写出实现功能的SQL语句和既能实现功能又...
  • newnazi
  • newnazi
  • 2013年06月29日 21:29
  • 518

mysql order by 造成语句 执行计划中Using filesort,Using temporary相关语句的优化解决

mysql> explain  select permission.* from t_rbac_permission   permission  inner JOIN  t_rbac_acl  acl...

[MySQL] 管理表和索引的相关语句

1. 创建表 MySQL中创建表是通过create table语句实现的,语法这里就不介绍了,非常复杂,可以去官网上查询,所幸的是,我们并不需要记住所有的选项,因为大部分都采用默认即可,下面介绍几个比...

mysql相关常用sq语句

查看进程:show full processlist; show processlist; //联合索引 ALTER TABLE `forumdata_userttt8` ADD INDEX `i...

oracle移植至mysql相关sql语句

一. 项目已用到 oracle 函数的转换 1.  Oracle 中的 TO_DATE () 示例: select * from admadjustmoney t where t.sendtime...
  • jj88888
  • jj88888
  • 2014年09月15日 10:53
  • 417

mysql 整理之mysql sql语句相关

mysql 面试(五) 整理之mysql sql语句相关
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL中like语句及<>相关优化器tips
举报原因:
原因补充:

(最多只允许输入30个字)