MySQL优化神器：explain命令及like有无索引问题

terry 2年前 (2023-09-26) 阅读数 38 #数据库

MySQL优化神器：explain命令以及2是否存在索引问题社区里有传言：有一个MySQL性能优化的神器叫explain。它可以解析select语句并输出有关选择执行过程的详细信息，让开发人员从这些信息中得到优化思路。

说一下MySQL提供的解释命令：

语法：SQL语句解释
例如：

1explain select * from user where id=1
复制代码

执行后，其输出有以下字段♿

❀ _type 分区s

如果您想知道如何使用解释性命名，请执行这些字段说明

1。 id

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

2。 select_type

每个select查询语句的类型，给定的类型以及对应的函数如下：

类型名称说明

SIMPLE ，不带ON或SELECT，不带ON含义。

PRIMARY 如果查询包含复杂的子部分，则为最外层。选择该层是主要的

UNION 第二或后续选择❙ent❀un❀p语句❀❀在联合中的第二个或后续选择语句，具体取决于外部查询

Union结果 RESULTS OF UNION

SUBQUERY 子查询中的第一个 SELECT♺♺ENDDEP 子查询中的第一个 SELECT，依赖于外部查询

DERIVED SELECT，派生表 FROM 子句的子查询

无法缓存的子查询

子查询的结果无法缓存，并且必须重新评估外部引用3 的第一行。 table

显示该行的数据正在查找哪个表，但有时简短的显示并不是表的真实名称。

4。分区

对应的分区（这个目前用处不大）

5. type

指示MySQL如何在表中查找所需行的访问类型。对应的数值及说明如下：

类型名称年级说明

系统 1 表格♶♷只有一行最多有1行匹配在查询开始时读取

eq_ref 3 使用主键或唯一键作为多表连接的条件，只读取表中的一行。

ref 4 作为查询条件的索引，从表中读取每个表中索引值对应的行。采用Select索引合并优化方法†检索特定范围的行，使用索引选择行

index

全表扫描，但是按照索引的顺序读表ALL

全表扫描查找对应的行作为访问类型，其值表示当前查询使用的类型，是性能的重要指标。从表中可以看到，从上到下，表扫描的方式越来越宽，性能越来越差。因此最好保持在域级别以上进行查询。

6。 possible_keys

主动指示查询可以使用哪个索引来搜索表中的记录
即查询的字段中索引的字段会被列出，但查询不一定会使用它们。

7。 key

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

8。 key_len

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

指示使用哪些列或常量来查找索引列中的值。

10。 rows

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

11。 Extra

显示当前查询使用的解决方案。有以下几种情况：

类型名称	说明
仅通过索引获取where	列的数据不读取实际操作表而返回信息，表示MySQL- 必须使用临时表来存储结果集，这对于排序和分组查询来说很常见
使用文件排序	MySQL 不能使用索引执行的排序操作称为“文件排序”
使用a join buffer	改变的值强调获取join条件时不使用索引，需要一个join buffer来存储中间结果。如果出现该值，请注意，根据查询的具体条件，您可能需要添加索引来提高性能。
不可能 where	该值强调 where 语句不会产生符合条件的行。
选择优化表	这个值表示仅使用索引，优化器仅从聚合函数的结果中返回一行

让我们在语法之后实际操作一下。首先创建表：

1-- 创建表
2CREATE TABLE test(
3id INT(11) NOT NULL AUTO_INCREMENT,
4uname VARCHAR(255),
5PRIMARY KEY(id) 
6);
复制代码

然后为uname字段添加索引：

1-- 添加索引
2ALTER TABLE test ADD INDEX uname_index (uname);
复制代码

检查索引是否添加成功：

1-- 查看是否有索引
2SHOW INDEX FROM test;
复制代码

输出结果：

可以看到索引已创建。然后添加一些数据：

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

全部完成，我们用explain命令看看一些类似的语句是否有索引。
like有四个位置，分别是无%、%%、左%、右%、

1。作为字段名

1EXPLAIN SELECT * FROM test WHERE uname LIKE 'j'; 
复制代码

输出可以这样查看：

:
type的值为range，key的值为uname_index，也就是说本例我们使用索引。

2。 like %field name%

1EXPLAIN SELECT * FROM test WHERE uname LIKE '%j%'; 
复制代码

输出：

可见：
类型的值为ALL，即全表扫描，key的值为NULL，即表示没有索引用过的。

3。如 % 字段名

1EXPLAIN SELECT * FROM test WHERE uname LIKE '%j'; 
复制代码

输出：

可见：
类型的值为 ALL，key 的值为 NULL，也没有使用索引。

4。 as field name %

1EXPLAIN SELECT * FROM test WHERE uname LIKE 'j%'; 
复制代码

输出：

可见：
type 的值为 range，key 的值为 uname_index，这意味着在本例中您使用的是 indexed 。

总结

从上面的实验中，我们可以总结出like是否使用索引的规则：
为了让索引有效（like语句不能以%like字段名%开头这样指令 like ) 和 (like % fieldname ) 会使索引失效，而 (like fieldname ) 和 (like fieldname %) 等语句的索引可以正常使用。