你的like语句为什么没索引详解

前言

本文旨在用最通俗的语言讲述最枯燥的基本知识

这个话题比较有意思。昨天中午吃完饭间突然有个同事蹦出了一句:“like有索引吗?”,我顺口就说没有,另一个同事反驳说有啊,还有些同事说看情况的有,这下有点懵逼了,都不知道那种说法是正确的,于是决定花了个半小时来研究验证这个问题,终于得到答案。

怎么验证的呢?

坊间有传言:MySQL性能优化有个神器,叫做explain,它可以对select语句进行分析并且输出详细的select执行过程的详细信息,让开发者从这些信息中获得优化的思路。

下面来讲讲这个MySQL提供的explain命令:

语法:explain SQL语句例如:

1explain select * from user where id=1

执行完毕之后,它的输出有以下字段:

id
  select_type
  table
  partitions
  type
  possible_keys
  key
  key_len
  ref
  rows
  Extra

要想知道explain命名怎么使用,就必须把这些字段搞清楚

1. id

SELECT查询的标识符, 每个SELECT语句都会自动分配一个唯一的标识符

2. select_type

每个select查询字句的类型,具体类型以及对应作用如下表:

类型名 解释
SIMPLE 简单SELECT,不使用UNION或子查询等
PRIMARY 查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
UNION UNION中的第二个或后面的SELECT语句
DEPENDENT UNION UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT UNION的结果
SUBQUERY 子查询中的第一个SELECT
DEPENDENT SUBQUERY 子查询中的第一个SELECT,取决于外面的查询
DERIVED 派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存,必须重新评估外链接的第一行

3. table

显示这一行的数据是查哪张表的,不过有时短路显示的不是真实的表名。

4. partitions

匹配的分区(这个目前用处不大)

5. type

访问类型,表示MySQL在表中找到所需行的方式,对应的值和解释如下:

类型名 优级别 解释
system 1 表仅有一行
const 2 表最多有一个匹配行,在查询开始时即被读取
eq_ref 3 使用primary key或者unique key作为多表连接的条件,仅从该表中读取一行
ref 4 作为查询条件的索引在每个表匹配索引值的行从表中读取出来
fulltext 5 全文索引检索
ref_or_null 6 和ref一致,但增加了NULL值查询支持
index_merge 7 表示使用了索引合并优化方法
unique_subquery 8 使用了替换了in子查询
index_subquery 9 使用了替换了in子查询,但只适用于子查询中的非唯一索引
range 10 只检索给定范围的行,使用一个索引来选择行
index 11 全表扫描,但扫描表的方式是按索引的次序进行
ALL 12 全表扫描的方式找到匹配的行

type作为访问类型,其值代表着当前查询所用的类型,是体现性能的一个重要指标,从表中可以看到,从上到下,扫描表的方式越来越宽,性能也就越来越差,因此,对于一个查询,最好能保持在range级别以上。

6. possible_keys

主动指出查询能用哪个索引在表中找到记录也就是会列出在查询中的字段中有索引的字段,但不一定被查询所用。

7. key

显示再查询中实际使用的索引/键,如果没有索引,则显示NULL。但如果想强制查询中使用或忽视possible_keys列中的索引,则可以在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

8. key_len

表示索引中使用的字节数。

9. ref

表示哪些列或常量被用于查找索引列上的值。

10. rows

显示当前查询估算到的查找到匹配记录所需的记录行数。

11. Extra

显示当前查询所用的解决方式,它有以下几种情况:

类型名 解释
Using where 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,
Using temporary 表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询
Using filesort MySQL中无法利用索引完成的排序操作称为“文件排序”
Using join buffer 改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。
Impossible where 这个值强调了where语句会导致没有符合条件的行。
Select tables optimized away 这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行

讲完了语法,我们来实际操作一波,首先创建个表:

-- 创建表
CREATE TABLE test(
id INT(11) NOT NULL AUTO_INCREMENT,
uname VARCHAR(255),
PRIMARY KEY(id)
);

然后给uname字段加上索引:

-- 添加索引
ALTER TABLE test ADD INDEX uname_index (uname);

查看一下索引是否添加成功:

-- 查看是否有索引
SHOW INDEX FROM test;

输出结果为:

可以看出索引已经创建成功,接下来添加一些数据:

-- 添加一些数据
INSERT INTO test VALUES(1,'jay');
INSERT INTO test VALUES(2,'ja');
INSERT INTO test VALUES(3,'bril');
INSERT INTO test VALUES(4,'aybar');

一切准备就绪,下面用explain这个命令来探究一些like语句是否有索引,like有四种情况,分别为没有%、 %% 、左%、右%、

1. like 字段名

EXPLAIN SELECT * FROM test WHERE uname LIKE 'j'; 

输出为:

可以看出:type的值为:range,key的值为uname_index,也就是说这种情况下,使用了索引。

2. like %字段名%

EXPLAIN SELECT * FROM test WHERE uname LIKE '%j%'; 

输出为:

可以看出:type的值为ALL也就是全表扫描,而且key的值为NULL,也就是说没用到任何索引。

3. like %字段名

EXPLAIN SELECT * FROM test WHERE uname LIKE '%j'; 

输出为:

可以看出:type的值为ALL,key的值为NULL,同样没用到索引。

4. like 字段名%

EXPLAIN SELECT * FROM test WHERE uname LIKE 'j%'; 

输出为:

可以看出:type的值为:range,key的值为uname_index,也就是说这种情况下,使用了索引。

总结

由上面的试验可以总结出like是否使用索引的规律:like语句要使索引生效,like后不能以%开始,也就是说 (like %字段名%)  、(like %字段名)这类语句会使索引失效,而(like 字段名)、(like 字段名%)这类语句索引是可以正常使用。

其它

为了查证like索引的问题,研究了MySQL神奇explain,但explain不仅仅只能检查索引使用情况,还可以提供很多其它的性能优化方面的帮助,至于具体的使用,其实跟上面讲的一样,把explain结果列出来,然后顺藤摸瓜查阅相关的字段就可以得到相应的内容。

好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对我们的支持。

(0)

相关推荐

  • 利用reverse索引优化like语句的方法详解

    前言 在有一些情况下,开发同学经常使用like去实现一些业务需求,当使用like时,我们都知道使用like 前%(like '%111')这种情况是无法使用索引的,那么如何优化此类的SQL呢,下面是一个案例. 原SQL如下: pcc_cust_infonew 表索引如下 执行设计如下: 这里可以看到SQL执行3.96秒,执行计划中也可以发现PCC_CUST_INFONEW表走TABLE ACCESS FULL扫描,返回约380k的数据,然后再与PCC_CUST_CONTRACTCOMPANY表主

  • 你的like语句为什么没索引详解

    前言 本文旨在用最通俗的语言讲述最枯燥的基本知识 这个话题比较有意思.昨天中午吃完饭间突然有个同事蹦出了一句:"like有索引吗?",我顺口就说没有,另一个同事反驳说有啊,还有些同事说看情况的有,这下有点懵逼了,都不知道那种说法是正确的,于是决定花了个半小时来研究验证这个问题,终于得到答案. 怎么验证的呢? 坊间有传言:MySQL性能优化有个神器,叫做explain,它可以对select语句进行分析并且输出详细的select执行过程的详细信息,让开发者从这些信息中获得优化的思路. 下面

  • MySQL索引详解及演进过程及面试题延伸

    目录 1索引的概念 1.1定义 1.2类型 1.3作用 2索引的数据结构B+树的演进过程 2.1问题 2.2问题 2.3问题:怎么建目录呢?给每一个页都建一个目录吗? 2.4索引树.页的分裂与合并 2.5根据我们刚才推演的,延申出几个面试题 3什么是二级索引树 3.1那么二级索引树怎么排序? 3.2索引桥的概念是什么呢(最左匹配原则)? 3.3回表.覆盖索引.索引下推 3.4延申几个面试题: 3.5二级索引树的总结 4主键索引与二级索引的区别 1索引的概念 1.1定义 索引在关系型数据库中,是一

  • InnoDB的关键特性-插入缓存,两次写,自适应hash索引详解

    InnoDB存储引擎的关键特性包括插入缓冲.两次写(double write).自适应哈希索引(adaptive hash index).这些特性为InnoDB存储引擎带来了更好的性能和更高的可靠性. 插入缓冲 插入缓冲是InnoDB存储引擎关键特性中最令人激动的.不过,这个名字可能会让人认为插入缓冲是缓冲池中的一个部分.其实不然,InnoDB缓冲池中有Insert Buffer信息固然不错,但是Insert Buffer和数据页一样,也是物理页的一个组成部分. 主键是行唯一的标识符,在应用程序

  • mysql update语句的执行过程详解

    以前有过一篇关于MySQL查询语句的执行过程,这里总结一下update语句的执行过程.由于update涉及到数据的修改,所以,很容易推断,update语句比select语句会更复杂一些. 1,准备 创建一张test表 CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) NOT NULL DEFAULT '0' COMMENT '数值', PRIMARY KEY (`id`) ) ENGINE=InnoDB

  • MySQL数据库之索引详解

    目录 一.MySQL索引简介 二.MySQL五种类型索引详解 (一)普通索引 (二)唯一性索引 (三)主键索引 (四)复合索引 (五)全文索引 三.MySQL索引使用原则 总结 今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL索引相关内容. 一.MySQL索引简介 索引是MySQL数据库为了加快数据查询的速度,给表中的某一个或者是某几个列添加的一种"目录".MySQL的索引是一个特殊的文件,但是InnoDB类型引擎(关于MySQL的引擎我们会在今后的文章中进行讲解)的表的

  • Mysql的SELECT语句与显示表结构详解

    SELECT... SELECT 1+1, 2+2;# 直接这样写相当于下面这句 SELECT 1+1, 2+2 FROM DUAL; # 这里DUAL:伪表 SELECT ... FROM 语法: SELECT 标识选择哪些字段(列)FROM 标识从哪个表中选择 例如选择全部列: SELECT * FROM departments; 一般情况下,除非需要使用表中所有的字段数据,最好不要使用通配符‘*’.使用通配符虽然可以节省输入查询语句的时间,但是获取不需要的列数据通常会降低查询和所使用的应用

  • 对Pandas MultiIndex(多重索引)详解

    创建多重索引 In [16]: df = pd.DataFrame(np.random.randn(3, 8), index=['A', 'B', 'C'], columns=index) In [17]: df Out[17]: first bar baz foo qux \ second one two one two one two one A 0.895717 0.805244 -1.206412 2.565646 1.431256 1.340309 -1.170299 B 0.4108

  • 对Python3 goto 语句的使用方法详解

    熟悉 C 语言的小伙伴一定对 goto 语句不陌生,它可以在代码之间随意的跳来跳去,但是好多老鸟都告诫大家,不要使用 goto,因为 goto 会使你的代码逻辑变的极其混乱. 但是有时候我们不得不用它,因为它太高效了.比如进入循环内部深层一个 goto 就能回到最上层,还有可以定位到代码的任意一个位置,很是高效方便. 但是也不要所有的代码都用 goto,那样你的代码就变得像量子世界那样诡异,连你自己都控制不了. 最后一句忠告,能不用 goto 最好就不用. 首先安装一个 goto 的包(因为官方

  • Go语言基础if条件语句用法及示例详解

    目录 概述 语法 格式规则 概述 条件语句需要开发者通过指定一个或多个条件 并通过测试条件是否为 true 来决定是否执行指定语句 并在条件为 false 的情况再执行另外的语句. 语法 package main func main() { //第一种格式 if 条件表达式 { 语句1 } //第二种格式 if 初始化表达式; 条件表达式 { 语句1 } //第三种格式 if 初始化表达式; 条件表达式 { 语句1 }else{ 语句2 } //第四种格式 if 初始化表达式; 条件表达式 {

  • Web网络安全漏洞分析XSS常用语句及编码绕过详解

    目录 XSS进阶 XSS常用语句及编码绕过 XSS常用的测试语句 JS编码 HTML实体编码 URL编码 XSS进阶 XSS常用语句及编码绕过 XSS常用的测试语句 <img src=1 alert(1)> (显示1) <script>alert("xss")</script> (显示xss) <script>alert(document.cookie)</script> (显示cookie) <script>wi

随机推荐