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语句及相关优化器tips

背景          MySQL中在对某个字段做包含匹配时可以用like。 先看这个结构和结果   CREATE TABLE `tb` (  `id` int(11)...
  • linglongwunv
  • linglongwunv
  • 2014年08月06日 15:05
  • 536

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

背景          MySQL中在对某个字段做包含匹配时可以用like。 先看这个结构和结果   CREATE TABLE `tb` (     `id...
  • sd4015700
  • sd4015700
  • 2015年08月19日 11:48
  • 268

Explain详述(Sql查询优化器) -Mysql深化学习-持续更新

MySql 深化学习 1 explain用法 explain可以帮助我们分析select语句,找出select语句的瓶颈,从而可以针对性地去做优化,让MySQL查询优化器更好地工作。 MySQL查询优...
  • qq_31159377
  • qq_31159377
  • 2016年07月10日 23:58
  • 852

Oracle 查询优化器 -- 改写查询语句

-- Start 当我们执行一条查询语句的时候,我们只告诉 Oracle 我们想要哪些数据,至于数据在哪里,怎么找,那是查询优化器的事情,优化器需要改写查询语句,决定访问路径(如:全表扫描,快速全索...
  • shangboerds
  • shangboerds
  • 2015年07月10日 17:17
  • 1140

Oracle 优化器

一、优化器基本知识   Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条S...
  • rlhua
  • rlhua
  • 2014年04月28日 21:21
  • 2968

MySQL 优化器可选开关详解

对MySQL 优化器的可选开关进行了详细解释。
  • yueliangdao0608
  • yueliangdao0608
  • 2014年03月13日 16:17
  • 3714

关于深度学习优化器 optimizer 的选择,你需要了解这些

在很多机器学习和深度学习的应用中,我们发现用的最多的优化器是 Adam,为什么呢?下面是 TensorFlow 中的优化器, https://www.tensorflow.org/api_guid...
  • g11d111
  • g11d111
  • 2017年08月03日 18:17
  • 1233

plsql优化器

Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在...
  • ocean1010
  • ocean1010
  • 2010年10月16日 21:40
  • 6371

mysql like语句

数据库模糊查询like语句: $sql = "select * from table where title like '%中国%'"; //包含“中国”两个字 $sql = "select *...
  • kongjunchao159
  • kongjunchao159
  • 2015年08月06日 16:50
  • 1348

功率优化器对电池组件遮挡的提升

光伏电站的建设易受环境条件制约,使电池组件工作时容易产生阴影遮挡。例如:光伏阵列间距不够大,在日出日落阶段前排组件会遮挡后排组件;电站附近有山峰或建筑物形成阴影遮挡;多云天气,部分光伏阵列被乌云遮挡。...
  • ShiKongDeJinTou
  • ShiKongDeJinTou
  • 2016年06月11日 10:25
  • 822
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL中like语句及<>相关优化器tips
举报原因:
原因补充:

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