MySQL查询如何优化？_vlambda技术博客

vlambda
2020-07-28

MySQL查询如何优化？

查询优化

EXPLAIN

在 MySQL 中可以使用 EXPLAIN 查看 SQL 执行计划，语法为：

EXPLAIN select * from table

使用 EXPLAIN 查看 SQL 的执行计划可以得知数据库运行该 SQL 时具体的执行顺序，使用了哪些表、索引，以及扫描了表数据的具体行数。

EXPLAIN 结果说明

在这里为大家整理了一张表格用来对 explain 执行计划的查询结果的解释说明，觉得有用可以收藏一下。

字段	说明
id	select 查询语句的执行序号，包含一组数字，表示查询中执行 select 语句或操作表的执行顺序。不同的 id 值由大到小执行，相等的 id 值由上至下执行。
select_type	select 语句的类型，可以有以下任意一种： SIMPLE：简单的 select 查询，不使用关联查询或者子查询。 PRIMARY：查询语句中包含复杂的子部分，其外层查询就被标记为：PRIMARY。 UNION：连接查询的第 2 个或后面的查询语句。 DEPENDENT UNION：表示在 UNION 查询中的第二个或后面的 SELECT 语句，其取决于外面的查询。 UNION RESULT：表示做为连接查询的结果。 SUBQUERY：所有子查询的第 1 个 SELECT 语句。 DEPENDENT SUBQUERY：所有子查询的第 1 个 SELECT 语句，其取决于外面的查询。 DERIVED：`SELECT( FROM 语句的子查询部分)`。
table	输出行数据所引用的具体表
type	表的连接类型。以下按照“从最佳类型到最差类型排序”： system：全表仅有一行数据。 const：全表只匹配到一行数据，查询速度很快，常用于主键或者唯一索引的查询，是最优的查询方式。 eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了 const 类型。它用在主键或唯一索引的所有部分被联接使用。eq_ref 可以用于使用 = 比较带索引的列。 ref：查询条件索引既不是主键也不是唯一索引的情况。可使用 `=` 或 `<` 或 `>` 操作符与索引列关联。 ref_or_null：同 ref，但是添加了可以专门搜索包含 NULL 值的行。在解决子查询中经常使用该联接类型的优化。上面这五种情况都是很理想的索引使用情况。 index_merge：该类型表示使用了索引合并的优化方法。在这种情况下，执行计划的 key 列包含了使用的索引清单，key_len 列包含了使用的索引的最长关键元素。 unique_subquery：该类型替换了下面形式的 IN 子查询的 `ref: value in ( select primary_key from single_table where some_expr )`。unique_subquery 是一个索引查找函数,可以完全替换子查询,效率更高。 index_subquery：该类型有点类似于 unique_subquery。可以代替 IN 查询,但只适合下列形式的子查询中的非唯一索引: value IN (select column from table where expr )。 range：使用一个索引检索一定范围的行数据。 index：索引树被扫描，因为索引文件通常比数据文件小，所以速度比 ALL 快一点。 ALL：进行完整的表扫描（性能最差）。
possible_keys	用来指明 MySQL 能使用的索引。如果为 NULL，说明没有使用索引，可以创建索引提高性能。
key	显示 MySQL 实际使用的索引，如果没有使用索引则显示 NULL。可以强制使用所以或者忽略索引：强制使用索引：USE INDEX（列名）忽略使用索引：IGNORE INDEX（列名）
key_len	显示 MySQL 使用的索引长度。如果没有使用索引则显示长度也为 NULL。key_len 的值是确定了 MySQL 讲时间使用的索引长度。
ref	显示使用了哪些列或者常数，与 key 一起从表中选择行数据。
rows	显示 MySQL 执行语句时扫描的行数。多个查询语句之间显示的行数相乘，可以估算整个语句扫描的行数。值越小扫描行数越少，性能越高。
Extra	显示执行语句的详细信息： Distinct：MySQL 在发现第 1 个匹配行后,停止为当前的行搜索更多的行。 Not exists：MySQL 能够对查询语句进行 LEFT JOIN 优化,在发现 1 个匹配 LEFT JOIN 标准的行后,不再为前面的的行组合在该表内检查更多的行。 range checked for each record(index map: #)：MySQL 没有发现可用的索引,因此对从前面表中来的每一行组合，检查使用哪个索引，从表中返回行。这个效率比较低，但是比不使用索引好一点。 Using filesort：MySQL 需要额外的一次传递,来确定如何按照排序顺序检索行。 Using index：只使用索引中的信息，不需要通过索引回表就能得到期望的数据。 Using temporary：创建一个临时表来容纳数据结果。 Using where：表示使用索引后还需要通过索引回表查询数据。 Using sort_union：使用两个或者两个以上的索引提取数据，但无法确保每个索引会提取到一个自然排好序的结果，所以为了排除多余的数据，需要额外的处理。 Using union：使用两个或者两个以上的索引提取数据，分别取得已排序的结果，通过合并就可以获得正确结果。 Using intersect：使用两个或者两个以上的索引提取数据，分别取得已排序的结果，通过求交就可以获得正确结果。 Using index for group-by：表示使用一个索引,查询 GROUP BY 或 DISTINCT 的所有列。

重要字段说明

1. id 说明

从 id 字段看执行顺序。图一中 id 相同，执行顺序由上至下即先查询 sys_user u 表，再查询 sys_user_role ur 表，最后查询 sys_role r 表。

MySQL查询如何优化？

当 id 存在不相同的时候，如下图所示，先执行 id 值大的语句，id 值相等时依旧由上至下执行，实际执行顺序已用红色标注。

MySQL查询如何优化？

2. select_type 说明

表示查询中每个 select 子句的类型。有以下几种值：

SIMPLE：查询语句中不含子查询或者 UNION 查询
PRIMARY：查询语句中包含复杂的子部分，其外层查询就被标记为：PRIMARY。

MySQL查询如何优化？

UNION：连接查询的第 2 个或后面的查询语句。

MySQL查询如何优化？

DEPENDENT UNION：表示在 UNION 查询中的第二个或后面的 SELECT 语句，其取决于外面的查询。
UNION RESULT：表示做为连接查询的结果。
SUBQUERY：所有子查询的第 1 个 SELECT 语句。

MySQL查询如何优化？

DEPENDENT SUBQUERY：所有子查询的第 1 个 SELECT 语句，其取决于外面的查询。
DERIVED：SELECT(FROM 语句的子查询部分)。

3. type 说明（重点）

system 类型：全表仅有一行数据。
const 类型：全表只匹配到一行数据，查询速度很快，常用于主键或者唯一索引的查询，是最优的查询方式。

MySQL查询如何优化？

eq_ref 类型：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了 const 类型。它用在主键或唯一索引的所有部分被联接使用。eq_ref 可以用于使用 = 比较带索引的列。

MySQL查询如何优化？

ref 类型：查询条件索引既不是主键也不是唯一索引的情况。可使用 = 或 < 或 > 操作符与索引列关联。

MySQL查询如何优化？

ref_or_null 类型：同 ref，但是添加了可以专门搜索包含 NULL 值的行。在解决子查询中经常使用该联接类型的优化。

上面这五种情况都是很理想的索引使用情况。

index_merge 类型：该类型表示使用了索引合并的优化方法。在这种情况下，执行计划的 key 列包含了使用的索引清单，key_len 列包含了使用的索引的最长关键元素。
unique_subquery 类型：该类型替换了下面形式的 IN 子查询的 ref：value in ( select primary_key from single_table where some_expr )。unique_subquery 是一个索引查找函数,可以完全替换子查询，效率更高。
index_subquery 类型：该类型有点类似于 unique_subquery。可以代替 IN 查询,但只适合下列形式的子查询中的非唯一索引：value IN (select column from table where expr )。
range 类型：使用一个索引检索一定范围的行数据。

MySQL查询如何优化？