你的like语句为什么没索引详解
作者:顾家进 发布时间:2024-01-13 14:16:18
前言
本文旨在用最通俗的语言讲述最枯燥的基本知识
这个话题比较有意思。昨天中午吃完饭间突然有个同事蹦出了一句:“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结果列出来,然后顺藤摸瓜查阅相关的字段就可以得到相应的内容。
好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。
来源:https://juejin.im/post/5bbd453ef265da0abc2b84ca
猜你喜欢
- 酱子们大家好,今天继续分享 Python 小工具今天的小程序,可以一键完成文件整理,一起来看看吧!按照文件格式整理具体来说就是不同的文件后缀
- python3中的字符串是一种常见的数据类型。字符串有多种表现形式:单引号、双引号和三引号,且这些字符串的表现形式(单、双、三)都必须是成对
- 一、PL/SQL出现的目的 结构化查询语言(Structured Query Language,简称SQL)是用来访问关系型数据库一种通用语
- 华为2019在线笔试题,现整理如下,以供之后参考GitHub题目介绍####################################
- pytorch查看模型model参数parameters示例1:pytorch自带的faster r-cnn模型import torchim
- 下面我们就分别讲述,虽然说的是Insert语句, 但是Select、Update、Delete语句都是一样的。 假如有下述表格:
- 先介绍一下jsSmarty Project:简要说明:利用JavaScript?在客户端完成SmartyPhp的工作,加速模板开发。主要是利
- 很多时候,设计师们都会通过各种渠道去了解用户的需求,然而从这些渠道反馈回来的信息大部分只是用户的期望并不是真正的用户需求,但是很多时候这些期
- 1069错误(由于登录失败而无法启动服务)解决方法在本版面出现这个问题的频率也算是很高的了,新手通常会比较多遇到这个问题原因很简单,安装SQ
- 直方图处理直方图从图像内部灰度级的角度对图像进行表述从直方图的角度对图像进行处理,可以达到增强图像显示效果的目的。直方图的含义直方图是图像内
- 在了解了JavaScript1.7的相关更新后,我们再继续关注JavaScript1.8的新特性。在W3C Group带来的JavaScri
- 因为主键可以唯一标识某一行记录,所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。当然,其它字段可以辅助我们在执行这些操作时消除共
- MySQL有的时候需要用到类似lastIndexOf的功能,然而它没有现成直接可用的函数,就需要自己来琢磨了。首先,MySQL提供了以下3个
- Facebook的网站速度做为最关键的公司任务之一。在2009年,我们成功地实现了Facebook网站速度提升两倍 。而正是我们的工程师团队
- 最近发现周围的很多小伙伴们都不太乐意使用pandas,转而投向其他的数据操作库,身为一个数据工作者,基本上是张口pandas,闭口panda
- js 数组对象操作方法如下:1. 创建数组var array1 = [1,2] //方法一var array2 = new Ar
- 背景在实现图片转码的需求时,需要支持最大 500 个图片下载后转换格式;如果是一个一个下载后转码,耗时太长,需要使用 goroutine 实
- 文 | 闲欢来源:Python 技术「ID: pythonall」今天在浏览知乎时,发现一个有趣的问题:如何优化 Python 爬
- 前言相信大家初入某个项目,一般都要看代码。有时候,想把代码文件打印下来看,不过一般代码文件数量都在两位数或更多,逐一打开、打印,确实太耗费精
- 本文实例讲述了go语言map字典删除操作的方法。分享给大家供大家参考。具体分析如下:这里先构造了一点map,添加了青岛、济南、烟台三地的拼音