你的like语句为啥没索引？

waterc 发布于2019-07-01 16:03 / 2426人阅读

摘要：显示再查询中实际使用的索引键，如果没有索引，则显示。表示索引中使用的字节数。表示哪些列或常量被用于查找索引列上的值。这个值强调了语句会导致没有符合条件的行。字段名输出为可以看出的值为，的值为，同样没用到索引。

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

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

怎么验证的呢？

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

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

语法：explain SQL语句
例如：

explain select * from user where id=1

执行完毕之后，它的输出有以下字段：

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，也就是说这种情况下，使用了索引。

总结

由上面的试验可以总结出当前SQL中like是否使用索引的规律：
要是当前SQL中like语句要使索引生效，like后不能以%开始，也就是说：

1. （like %字段名%）、（like %字段名）这类语句会使索引失效，
2. （like 字段名）、（like 字段名%）这类语句索引是可以正常使用。

其它

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

觉得本文对你有帮助？请分享给更多人
关注「编程无界」，提升装逼技能

服务器托管云服务器为啥没人用百度云服务器 likes LIKE not like

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/32300.html

你的like语句为啥没索引？

摘要：显示再查询中实际使用的索引键，如果没有索引，则显示。表示索引中使用的字节数。表示哪些列或常量被用于查找索引列上的值。这个值强调了语句会导致没有符合条件的行。字段名输出为可以看出的值为，的值为，同样没用到索引。本文旨在用最通俗的语言讲述最枯燥的基本知识这个话题比较有意思。昨天中午吃完饭间突然有个同事蹦出了一句：like有索引吗？，我顺口就说没有，另一个同事反驳说有啊，还有些同事说看情...

sunnyxd 2019-07-31 18:34 评论0 收藏0
Oracle总结【SQL细节、多表查询、分组查询、分页】

摘要：前言在之前已经大概了解过数据库和学过相关的知识点，但是太久没用过了，就基本忘了印象中就只有基本的语句和相关一些概念写下本博文的原因就是记载着一些以前没注意到的知识点以后或许会有用实例与数据库概念数据库服务器由两部分组成实例理解为对象看不见的前言在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点，但是太久没用过Oracle了，就基本忘了...印象中就只有基本的SQL语...

陈伟 2019-07-23 16:50 评论0 收藏0
让数据库变快的10个建议

摘要：在这篇文章中，我收录了十个优化数据库速度的技巧。对于系统和用数据库的开发者来说，这很糟糕。新建数据库时，应当将信息储存在不同的表里，采用标准的命名方式，并采用主键。采用仅获得某个特定行数的数据是非常常见的。摘要：大多数网站的内容都存在数据库里，用户通过请求来访问内容。数据库非常的快，有许多技巧能让你优化数据库的速度，使你不浪费服务器的资源。在这篇文章中，我收录了十个优化数据库速度的...

Magicer 2019-07-31 17:36 评论0 收藏0
关于MySQL优化的几点总结

摘要：所以，我整理了优化的几点建议，希望这些优化技巧对您有用，总结不到的，欢迎大家补充。所以查询时，要求直接在后面指明需要查询的对应字段名。具体的，可以了解一下子查询的执行计划相关的问题。 showImg(https://segmentfault.com/img/remote/1460000016486792?w=1024&h=768); 前言现如今，数据库的操作越来越成为整个应用的性能瓶...

ysl_unh 2019-07-31 18:34 评论0 收藏0
sql优化之逻辑优化

摘要：优化时将外连接转换成内连接的意义答查询优化器在处理外连接操作时所需要执行的操作和时间多于内连接。子查询优化为啥要执行子查询优化答查询优化器对子查询一般采用嵌套执行方式，即对父查询中的每一行，都执行一次子查询，这样子查询会执行很多次。在单机数据库系统中进行优化。面临的问题，比如说给定一个要query的sql语句，查询优化算法的目标就是找到查询的一个具有最小执行花费的执行计划，如果找到了...

JohnLui 2019-07-01 16:05 评论0 收藏0