MySQL EXPLAIN语句

MySQL中的UPDATE语句经常用于修改已有的数据。在执行更新操作时，我们可能会遇到一些性能问题，这时就需要用到MySQL的EXPLAIN语句来帮助分析出问题所在。

阅读更多：MySQL 教程

EXPLAIN语句的基本用法

EXPLAIN语句可以用于查看MySQL执行查询或更新语句时的执行计划，以及各个子操作的执行顺序、使用的索引等。

以一个简单的例子来说明EXPLAIN语句的基本用法：

EXPLAIN UPDATE users SET age = age + 1 WHERE gender = 'female';

执行以上语句，输出结果包含以下列：

列名	描述
id	操作序号，与Select的EXPLAIN类似，从1开始
select_type	操作类型。UPDATE语句的操作类型为SIMPLE
table	要更新的表
partitions	分区
type	访问类型，常见的有ALL、index、range、ref、eq_ref和const。
possible_keys	可以使用的索引
key	实际使用的索引
key_len	使用的键的长度
ref	与该行匹配的索引列值
rows	扫描的行数。
filtered	从表中返回的行数所占的百分比
Extra	额外的信息或备注。

解释一下，上述列的含义：

id：操作序号，表示操作的执行顺序，每个操作从1开始编号。由于UPDATE语句只有一个操作，因此只有一个序号为1的操作。
select_type：操作类型。UPDATE语句的操作类型为SIMPLE，表示简单的UPDATE操作。
table：要更新的表名，即users。
partitions：分区，如果表有分区，该列显示分区信息。
type：访问类型。UPDATE语句只涉及到一个表，因此该列只会出现一个值。访问类型常见有ALL、index、range、ref、eq_ref和const。ALL表示全表扫描，显然不是好的访问类型；index表示索引扫描，范围较大，但比ALL要好；range表示范围扫描，对于有序列或日期字段的表会使用range扫描；ref表示使用非唯一索引扫描；eq_ref表示使用唯一索引扫描；const表示使用常量扫描，常用于子查询。
possible_keys：可使用的索引，该列显示MySQL可能使用的索引，所有列都被包含在内。
key：实际使用的索引。该列显示MySQL使用的实际索引。
key_len：使用的键的长度。表示MySQL使用的索引长度。
ref：与该行匹配的索引列值。该列对应Possible_keys列，用于显示MySQL关于索引的优化信息。
rows：扫描的行数。该列信息用于显示MySQL扫描表行的数量。对于使用了索引的查询，该值为索引项的匹配行数；对于未使用索引的查询，该值为表行数。
filtered: 是％的估计值。表示满足WHERE条件和索引的行所占的百分比。它的值越小，表示MySQL需要扫描的数据越少，查询性能越好，一般值低于50%会比较合理。
Extra：额外的信息或备注。该列信息用于显示MySQL在查询中使用的额外状态。该字段常出现的额外信息有using filesort(使用了文件排序)、usingtemporary(使用了临时表)、using index(覆盖索引)和using where(使用了WHERE条件)等。

对EXPLAIN语句结果的分析

通过执行EXPLAIN语句，可以了解MySQL执行UPDATE语句时的执行计划，以及哪些操作使用了索引。根据不同的实际情况，可以做出优化SELECT语句的决策。

扫描行数的关系

在EXPLAIN语句的结果中，行扫描数（Rows）是一个重要的参数，它表示在执行查询时需要扫描的行数。对于数量更多的行扫描，会导致查询所需要的过程时间更长。因此，我们可以通过减少需要扫描的行数来提高查询的性能。

给定以下的表：

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    age INT NOT NULL
);

当执行以下查询：

EXPLAIN UPDATE users SET age = age + 1 WHERE username = 'john.doe';

输出的结果中，Rows列的值显示为1。表示在执行此查询时，数据库引擎将查找1行。此时我们可以看到age列没有索引，因此MySQL将使用全表扫描来查找符合条件的行。

如果我们为age列创建一个索引，再执行同样的查询：

ALTER TABLE users ADD INDEX (age);
EXPLAIN UPDATE users SET age = age + 1 WHERE username = 'john.doe';

输出的结果会显示Rows列的值大于1，因为现在MySQL使用了索引来查找符合条件的行。在这种情况下，索引查找通常比全表扫描更快。

访问类型的优化

访问类型（type）指示MySQL如何在表中查找行。访问类型越优化，执行查询的速度越快。

访问类型有以下几种：

ALL：全表扫描，这种情况下MySQL将遍历全表以查找匹配的行。
index：通过索引扫描从表中读取所有行（索引将覆盖所有查询）。
range：只检索给定范围的行，使用一个索引来选择行。
ref：使用非唯一索引查找所有匹配某个单独值的行。此访问类型用于JOIN操作或自然连接中的查找。
eq_ref：类似于ref访问类型，唯一索引查找相等的行。
const：根据常量表达式的值从表中读取一行。
system：此访问类型仅由MySQL内部使用。
NULL：MySQL无法确定要使用哪种访问类型。

我们可以通过以下方法优化访问类型：

创建索引：创建索引可以避免全表扫描，从而大幅提高查询效率。你应该创建适当的索引，能够在符合查询条件的行中查找。比如，在创建联合索引时，把列包含在索引中顺序决定了MySQL使用该索引来执行的查询。如果将两个或更多列组合起来作为联合索引，那么MySQL只会在索引中找到第一列，所以只有当查找的列是索引的一部分时，MySQL才会选择该索引。
优化查询：有一些查询改写方法可以使查询结果与原始查询相同，但使用了更少的资源来执行查询。如使用覆盖索引查询来处理SELECT语句，这意味着使用的索引覆盖了SELECT语句中选择和过滤的列，而不需要进一步查询表数据。
使用分区：分区允许您将表分割成更小的块（分区），这使得在限制条件下，MySQL只需要在单个分区中查找行并避免全表扫描。如果您有一个大型的数据表，则可以考虑将其拆分成较小的分区。这将大大减少查询所需要的时间。

索引的优化

索引是优化数据库查询的一种方法，尽管它们可以使查询更快，并提高数据检索的效率，但它们不能用于所有类型的查询。以下是关于索引的一些最佳实践：

创建正确的索引：创建索引通常需要权衡，建议使用联合索引而不是单列索引，以避免在WHERE子句中使用不同的OR条件而不能使用索引的情况，此时应该使用联合索引以覆盖所有这些列。但是，在创建索引时，应该选择适当的索引长度和搜索顺序，例如：如果您想查找以字符“a”开始的所有列，则应该逆序索引。在使用LIKE查询时，为避免LIKE查询的性能问题，可以使用全文索引来优化SELECT语句。
避免在查询中使用函数：这会使MySQL无法使用索引，从而导致全表扫描，因此，在查询中使用函数应该少用或不用。
避免太多的顺序排序：因为这会使MySQL更加困难，所以如果您需要按表中的多个列排序，则应使用合适的索引或联合索引来优化排序。
在表中使用正确的数据类型：在mysql中使用正确的数据类型非常重要，因为它会影响到数据库的性能。如果您的系列合并了大量的重复数据（例如：许多0和1），建议使用Enum或set数据类型，将它们作为字符串保存，而不是使用整数类型或将重复的数据作为单独的行存储。
使用统计信息：MySQL提供了一些用于统计表和查询性能的信息，如SHOW INDEX、SHOW TABLE STATUS和EXPLAIN，它们可以帮助您在优化数据库查询时选择正确的索引。

总结

MySQL EXPLAIN语句可以帮助分析UPDATE语句的执行计划和优化表的访问。我们必须注意分析访问类型和优化查询语句，以便使用合适的索引和优化语句来提高查询性能，减少行扫描数量。另外，在创建索引时应当权衡建议使用聚合索引而不是单列索引。在查询中避免使用函数和大量的排序指令，以及在表中使用正确的数据类型都能提高MySQL的性能。最后，使用统计信息来帮助您在优化数据库查询时选择正确的索引和SQL语句。