我们在写服务端代码时,经常会与SQL语句打交道,最初写SQL时主要注重的是能不能正常执行SQL,然后查询出的数据是否正常。如果这两方面没有问题,基本不会考虑SQL执行速度的问题,毕竟这个时候数据库中数据量还是很少的,怎么写都执行都很快。但是随着数据量的增加,会发现系统查询速度越来越慢,这时就需要对系统进行优化了。
最初优化的方向就是是否存在慢查询,如果存在我们应该怎么去优化这些SQL语句,今天我们就介绍一下日常中经常使用的EXPLAIN命令。
EXPLAIN命令可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
通过EXPLAIN我们可以分析出一下几个结果:
EXPLAIN的使用方式:EXPLAIN + SQL语句,例如:
EXPLAIN SELECT * FROM t1
执行结果包含的信息,如下图:
执行计划返回有10个字段,分别是:id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面我们就具体分析执行结果中返回的每个字段的具体含义。
ID字段
ID字段是SELECT查询的序列号,表示查询中执行select子句或操作表的顺序;
ID字段大概可以出现3中不同的情况:
1、ID字段数值都相同,如下图:
此时所有的ID字段数值都相同,SQL语句执行顺序由上到下,顺序执行。SQL中不存在子查询等,如下SQL:
SELECT * FROM a , b WHERE a.id = b.id
2、ID字段数值按递增顺序出现,如下图:
此时ID字段的数值是递增出现的,ID数值越大优先级就越高,SQL语句就优先执行,对应的SQL语句如下:
SELECT * FROM a WHERE a.id = (SELECT id FROM b WHERE b.id = (SELECT id FROM c where c.sub_id = 1))
子查询级别越深,ID的数值就越大,也就优先执行。
3、ID字段中相同数值和不相同数值同时存在,如下图:
此时ID数值相同的算作一组,级别相同按顺序执行,ID数值越高的级别越高,越先执行。
注意:衍生出来的表定义为DERIVED,有多个时会在“DERIVED”后加入一个数字标识区分。
此种情况对应的SQL,如下:
SELECT * FROM ( SELECT a.id,a.sub FROM a ) b INNER JOIN c ON c.id = b.id
select_type字段
select_type字段主要用来区分查询的类型,如普通查询、联合查询、子查询等类型,具体可以分为以下几种类型:
table字段
table字段指的是当前执行的表
type字段
type字段是比较重要的,平常我们进行优化时主要关注的也是这个字段,这个字段表示的是我们在查询时访问表的方式,大概分ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好。一般来说,得保证查询至少达到range级别,最好能达到ref)。
几种类型简介:
possible_keys 和 key字段
key_len字段
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
注意:不损失精确性的情况下,长度越短越好
ref字段
显示索引的那一列被使用了,如果可能的话,最好是一个常数。哪些列或常量被用于查找索引列上的值。
rows字段
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,也就是说,用的越少越好。
Extra字段
该列包含MySQL解决查询的详细信息,有以下几种情况:
虽然EXPLAIN很简单,但是对于优化SQL还是很有帮助的,当然如果数据量特别大的时候,我们需要另外想别的方法进行优化。