MySQL数据库使用EXPLAIN进行查询优化

最新推荐文章于 2022-07-05 10:38:12 发布

weixin_34326429

最新推荐文章于 2022-07-05 10:38:12 发布

阅读量148

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/3452433/blog/1536831

版权

优化范围的确定

在项目SQL进行优化前首先需要确定优化范围。对于稍复杂的项目来说逐个SQL进行评估就已经很不现实了。所以我们要从两方面去确定范围

1、SQL使用频率，对于经常使用的进行优化。

2、SQL执行性能，对于执行性能低下的进行优化。

我们可以通过圈定常用功能的方式来圈定使用频率高的SQL范围。而执行性能较低的SQL我们可以通过慢SQL日志及压力测试发现他们。

我们可以通过EXPLAIN对SQL进行解释，从而得到当前SQL的执行效率分析数据。

EXPLAIN(SELECT * FROM ex_user)

执行完解释会给出类似下图的结果

返回结果各字段主要含义见下表

id	MySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序,id 值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。
select_type	查询类型，表示本条解释后的查询属于那种类型。
table	本条解释所引用的表。如果表起了别名则显示别名
type	本条解释链接所使用的类型。类型决定了检索范围，检索范围越小效率也就越高。按照查询效率由高到低的顺序为system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL
possible_keys	本条解释可以使用的索引。
key	本条解释实际使用的索引。
key_len	使用的索引的长度。在不损失精确性的情况下,长度越短越好。
ref	显示索引的哪一列被使用了
rows	MYSQL 认为必须检查的用来返回请求数据的行数。需要检查的行数越多执行效率将会越差。
extra	说明信息。如果出现出Using filesort或 Using temporary项意味着 MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

下面我们来讨论一下什么样的结果需要进行优化以及优化方案

type值是index 或 all	这两种类型说明查询是全表扫描查询效率极低，可以通过建立索引减少扫描行数。
rows行数特别多	rows查询的行数越多效率越低，可以通过增加检索条件减少行数
extra出现Using filesort或Using temporary	这两个提示代表排序或分组是不能使用内部索引，外部索引的效率低下。所以需要建立索引解决此问题。

创建索引的时候我们要注意以下几点

联合索引中如果出现范围查询的索引要放在最后面。联合索引是按照排序注意匹配的，如果出现相同的值则匹配第二个，但是范围查询会让后面的索引无效所以一定要放在最后。例如三个查询字段a,b,c创建联合索引，查询条件分别为a=1 and b in (1,2,3) group by c，那么这时extra是会出现Using filesort提示的，原因就在于b属于范围条件，导致联合索引的c缩阴无法被分组使用。联合索引改成a,c,b此问题就可以解决了。
inner join 和 left join 左表行数确定需要在右表建立索引。而 right join 右表行数确定需要在左表建立索引。

转载于:https://my.oschina.net/u/3452433/blog/1536831