MySql Explain优化命令使用

MySql Explain优化命令使用

truncate table student // 自增id 从 0 开始

delete from student // 自增id 会保留 ， 108

区别： 1：自增id

2：delete 可以恢复

truncate 无法恢复

前言

EXPLAIN 是一个用于获取 SQL 语句执行计划的命令，用于帮助理解查询的执行过程以及如何优化。这里主要是索引优化

1.准备

truncate table student DROP PROCEDURE IF EXISTS InsertRecords;
CREATE PROCEDURE InsertRecords()
BEGINDECLARE i INT DEFAULT 1;WHILE i <= 100000 DOSET @name = CONCAT('张', i);SET @age =  i;INSERT INTO student(name, age) VALUES (@name, @age);SET i = i + 1;END WHILE;
END

SQL

2.2 select_type列

"select_type"列是用来描述查询的类型

下面是常见的"select_type"值及其含义：

1. SIMPLE: 表示查询中不包含子查询或UNION操作符的简单查询。

2. PRIMARY: 表示查询中包含子查询，并且该子查询处于顶层位置。

3. SUBQUERY: 表示查询中的子查询，在FROM子句中被其他查询引用。

4. DERIVED: 表示查询中的派生表，是从子查询结果中创建的临时表。

5. UNION: 表示查询中使用了UNION或UNION ALL操作符进行多个查询结果的合并。

6. UNION RESULT: 表示UNION操作后的结果集。

7. DEPENDENT SUBQUERY: 表示子查询的结果取决于外部查询的值。

8. DEPENDENT UNION: 表示UNION操作的结果取决于外部查询的输入。

9. UNCACHEABLE SUBQUERY: 表示子查询的结果无法被缓存，通常是因为子查询中使用了非确定性函数或用户变量。

10. DEPENDENT UNION RESULT: 表示外部查询对于UNION操作结果的依赖。

2.3 table列

，"table"列用于表示查询所涉及的表或派生表的名称。

下面是"table"列可能出现的不同值及其含义：

1. 实际表名：表示查询中直接引用的物理表的名称。

2. 表的别名：如果查询中使用了表的别名，那么"table"列将显示该别名。

3. 派生表：对于包含子查询的查询，"table"列可能显示一个派生表的名称，表示在查询执行过程中创建的临时表。

2.4 type 列（重点）

"type"列用于表示访问表时所采用的访问类型。

下面是常见的"type"值及其含义：

1. system: 表示只有一行的表，通常是系统表。

2. const: 表示通过索引只能匹配到一行数据。 explain select * from student where id = 1688

3. eq_ref: 表示使用了等值连接（例如，使用主键或唯一索引连接表）。explain SELECT * FROM student s1 JOIN student s2 ON s1.id = s2.id WHERE s1.age = 25;

4. ref: 表示使用了非唯一索引进行查找，并返回匹配的多行或一行数据。 explain select * from student where name = '张68'

5. range: 表示使用了索引进行范围查找，例如使用比较符（>, <, BETWEEN）或IN操作符。 explain select * from student where age < 1688

6. index: 表示全索引扫描，也就是说用了某一个索引的全部, 通常发生在查询使用索引覆盖的情况下。explain select count(*) from student ；explain select sum(age) from student

7. all: 表示全表扫描，即没有使用索引，需要遍历整个表进行查询。 explain select * from student

需要注意的是，"type"列显示的结果是一个逐渐优化的输出，从最好到最差的顺序。尽可能选择更快和更有效的访问方法。

2.5 key列

在MySQL的EXPLAIN查询结果中，"key"列用于表示用于访问表的索引的名称。它显示了优化器选择的用于访问表的索引的名称，或者标识没有使用索引。

下面是"key"列可能出现的不同值及其含义：

1. 索引名称：表示使用了具名索引，优化器选择了该索引进行查询。

2. NULL：表示查询没有使用任何索引，即进行了全表扫描

2.6 key_len列

"key_len"列用于表示索引字段的最大长度。它显示了优化器使用的索引字段的最大长度，以字节为单位。

下面是"key_len"列的一些常见值及其含义：

1. 固定长度：如果索引字段是固定长度的（如整数、日期等），那么"key_len"列将显示该固定长度。

2. 可变长度：如果索引字段是可变长度的（如字符串），那么"key_len"列将显示该字段的最大长度。

"key_len"列的值是根据索引字段和索引类型来计算的。它可以帮助我们了解索引的大小和使用情况。

2.7 ref列

在MySQL的EXPLAIN查询结果中，"ref"列用于表示连接条件所引用的列或常量。它显示了查询中使用的引用，用于连接表或进行进一步的过滤。

下面是"ref"列可能出现的不同值及其含义：

1. 列名：表示引用了查询中的某个表的列，通常用于等值连接。

2. 常量：表示引用了查询中的一个常量值，用于与表中的列进行匹配。

3. 空白：表示没有引用列或常量，通常出现在全表扫描的情况下。

需要注意的是，如果查询中存在多个表连接，"ref"列可能会显示多个列名或常量，以逗号分隔。

2.8 rows列

在MySQL的EXPLAIN查询结果中，"rows"列用于表示优化器估计的扫描或访问的行数。它显示了查询执行过程中预计要处理的行数。

下面是"rows"列的一些常见值及其含义：

1. 具体的行数：表示优化器估计的该操作将处理的实际行数。

2. 0：表示优化器估计的该操作将不需要扫描或访问任何行。

3. NULL：表示优化器无法提供有关操作计划的行数估计。

"rows"列的值主要基于表的统计信息和查询条件来估计，因此它并不总是准确的。它仅作为一个参考，用于评估查询执行计划的成本和性能。

2.9 filtered列

"filtered"列用于表示查询结果经过表过滤后的预计百分比。它显示了查询条件对表数据的过滤效果。

"filtered"列的值范围从0到1之间，其中1表示所有行都满足查询条件，0表示没有行满足查询条件。

下面是"filtered"列的一些常见值及其含义：

1. 具体的百分比：表示优化器估计的满足查询条件的行占总行数的百分比。

2. NULL：表示优化器无法提供有关过滤效果的估计信息。

"filtered"列的值可以帮助我们了解查询条件对表数据的过滤程度。较高的过滤值意味着查询条件对表的过滤效果较好，可以减少进一步处理的数据量。

在上述示例中，"filtered"列的值为100/1000，即0.1。这表示优化器估计查询结果将过滤掉90%的行，仅返回10%的行，这是基于索引"student_age_IDX"和查询条件"age < 1688"的预计过滤效果。

需要注意的是，"filtered"列提供的是优化器的估计信息，并不一定与实际运行时的过滤效果完全一致。实际的过滤效果还受到数据分布和统计信息准确性等因素的影响。

2.10 Extra列

"Extra"列提供了关于执行计划中其他附加信息的说明。该列包含了一些额外的操作、提示和优化器的相关信息。

下面是"Extra"列可能出现的一些常见值及其含义：

1. “Using index”：表示查询通过使用覆盖索引（索引包含了所有查询所需的列）来避免访问表的实际行数据。

2. “Using where”：表示查询需要在返回结果之前进行附加的过滤操作，可能需要在查询过程中进行表的扫描或访问。

3. “Using temporary”：表示查询需要创建一个临时表来处理一些操作（如排序、分组），这可能会增加额外的内存和磁盘开销。

4. “Using filesort”：表示查询需要在内存或磁盘上进行排序操作，这可能会影响性能。

5. “Using join buffer”：表示查询使用了连接缓冲区来处理连接操作，这可以改善连接性能。

6. “Distinct”：表示查询使用了DISTINCT关键字去除重复的行。

7. “Range checked for each record”：表示查询通过索引范围扫描进行过滤，但还需要进一步检查每一行是否满足其他条件。

8. “Full scan on NULL key”：表示查询使用了一个NULL键的索引进行全表扫描。

需要注意的是，"Extra"列的值可能会因查询的具体情况而有所不同，且某些值可能会同时出现。这些值提供了关于查询执行计划的额外信息，有助于我们理解查询的操作、性能和优化情况。