sql查询优化之预估查询扫描行数-explain学习

最新推荐文章于 2024-03-29 19:20:53 发布

左左在右边

最新推荐文章于 2024-03-29 19:20:53 发布

阅读量4.1k

点赞数

分类专栏： # 数据库高性能mysql学习笔记

本文链接：https://blog.csdn.net/weixin_41609327/article/details/103363878

版权

数据库高性能mysql学习笔记专栏收录该内容

16 篇文章 1 订阅

订阅专栏

sql中提供了explain 关键字来分析查询性能，语法如下：

explain SELECT * FROM tpi.cqca_userintegrationsummary where Id =313253。

重点关注字段

type	本次查询表联接类型，从这里可以看到本次查询大概的效率
key	最终选择的索引，如果没有索引的话，本次查询效率通常很差
key_len	本次查询用于结果过滤的索引实际长度
rows	预计需要扫描的记录数，预计需要扫描的记录数越小越好
Extra	额外附加信息，主要确认是否出现 Using filesort、Using temporary 这两种情况

explain关键字结果

列名所代表的的含义:

Id: MySQL QueryOptimizer 选定的执行计划中查询的序列号。表示查询中执行select 子句或操作表的顺序,id 值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。

Select_type：

SIMPLE: 简单的 select 查询,不使用 union 及子查询
PRIMARY: 最外层的 select 查询
UNION: UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集
DERIVED: 用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询, 把结果放在临时表里。
UNION RESULT：UNION的结果
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)

Table:输出查询中所引用的表

Type:由上至下效率越来越低

各自的含义如下:

NULL：MySQL不访问任何表或索引，直接返回结果
system: 表仅有一行(=系统表)。这是 const 连接类型的一个特例。
const: const 用于用常数值比较 PRIMARY KEY 时。当查询的表仅有一行时,使用 System。
eq_ref: 从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
ref: 连接不能基于关键字选择单个行,可能查找到多个符合条件的行。叫做 ref 是因为索引要跟某个参考值相比较。这个参考值或者是一个常数,或者是来自一个表里的多表查询的结果值。
ref_or_null: 如同 ref, 但是 MySQL 必须在初次查找的结果里找出 null 条目,然后进行二次查找。
index_merge: 说明索引合并优化被使用了。
unique_subquery: 在某些 IN 查询中使用此种类型,而不是常规的 ref:valueIN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery: 在某些 IN 查询中使用此种类型 , 与unique_subquery 类似,但是查询的是非唯一性索引
range: 只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range（一般该查询是最低忍受范围，一下两个就是慢的）。
index: 全表扫描,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。
all: 最坏的情况,从头到尾全表扫描（出现了说明需要优化了）。

possible_keys : 指出能在该表中使用哪些索引有助于查询。如果为空,说明没有可用的索引。

key:实际从 possible_key 选择使用的索引。如果为 NULL,则没有使用索引。很少的情况下,MYSQL 会选择优化不足的索引。这种情况下,可以在 SELECT语句中使用 USE INDEX (indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制 MYSQL 忽略索引

key_len: 使用的索引的长度。在不损失精确性的情况下,长度越短越好。

ref: 显示索引的哪一列被使用了

rows: 认为必须检查的用来返回请求数据的行数

extra：详细信息。

Distinct：在select部分使用了distinc关键字。MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
Using filesort：排序时无法使用到索引时，就会出现这个。
Using temporary：表示使用了临时表存储中间结果。例如：ORDER BY或GROUP BY中的字段都来自左连接非驱动表（第二张表），就会创建一个临时表了

Using index：索引覆盖。直接通过索引就可以获取查询的数据。
Using where：需要回行。在查找使用索引的情况下，需要回表去查询所需的数据
Using sort_union、Using union、Using intersect：这些函数说明如何为index_merge联接类型合并索引扫描。
通过相乘EXPLAIN输出的rows列的所有值，可以粗略地知道MySQL必须检查多少行以执行查询。

结论：

1）出现了Using temporary，一般临时表排序，需要优化

2）出现了Using filesort，排序时无法使用到索引时，就会出现这个

3）rows过多，或者几乎是全表的记录数

4）key 是 (NULL)

5）possible_keys 出现过多（待选）索引