MySQL Explain

 

1、使用 Navicat

Navicat 新建查询,然后 F6 就可以打开命令行窗口了。虽然 explain 可以在查询窗口执行,但是很多语句都是需要在命令行执行。

 

2、Explain 语法、意义

Explain 的意义就是为当前的SQL语句生成一个查询执行计划(Query Execution Plan,QEP),mysql 执行一条SQL语句时会先检查语法问题(编译),然后构造一个QEP,QEP 决定了MySQL从底层存储引擎中获取信息的方式,换句话QEP是存储引擎执行的依据。

QEP 是在每条SQL语句执行的时候动态计算出来的。

在查询语句前面加一个Explain,就会显示QEP结果,大多数情况下explain 不会真正的执行SQL语句,当然也有例外的情况,比如复制的子查询,或者 from 后面的接的是派生表等。

1)explain 语法

Explain [exetended | partitions] select .... ;

2)explain desc

Explain table; 类似于 desc table

 

3、Explain 字段反馈的建议我们的操作

以下的列反馈的是select语句对每个表的 QEP,这个表可能是真实的表亦或是执行SQL生成的临时表。

意义
id 表的连续引用
type 指定表使用的连接方式,const、system、ref、all.....一定要注意all,如果是all,说明需要全表扫描,那么说明这条SQL需要优化
table 表名称,表的别名,临时表(派生表、子查询或集合)
select_type table列引用的使用方式类型,simple 、primary、derived....其中simple表示不包括子查询或其他复杂语法的简单查询
possible_keys 优化器为SQL列出可能被使用是的索引,如果你以为列出的索引越多越好,那你就错了,如果备选的数量大于3那说明已经太多了,因为太多会导致选择索引而损耗性能,所以建表时字段最好精简,同时也要建立联合索引,无效的单列索引是很可怕的
key 执行SQL时该表最终选择使用的索引,一般一张表只是用一个索引
key_len 被使用索引的长度,这个长度是指使用中的长度,而不是指索引本身的长度,比如一个联合索引,可能查询中只会用到部分列,那么这个长度就是指这部分列的长度,所以 key_len 对于确认索引的有效性以及多列索引中用到的列的数目很重要。key_len 列的值只和用在连接和where条件中的索引的列有关。
ref  
rows 试图分析所有存在于累计结果集中的行数,但是这只是一个估值,却也足以反映出SQL执行所需要扫描的行数,因此这个值越小越好,通过 show session status like '%Handler_read%' 可以查看SQL执行时实际读取行的操作
Extra 一些额外信息,比如 Using where,Using Temporay,Impossible Where....

 

4、其他关联的命令

1)show session status like '%Handler_read%'

可以查看SQL执行时实际读取行的操作。

 

2)show indexes from t_exam_result;

可以查看该表的所有索引,其中 Cardinality(基数) 重点关注,该值代表在索引中每一列唯一值的数量估计值,很明显越大越好,最好的结果就是 Cardinality 的数量和rows的数量相等,也就是主键。因此这个值越大也就越有可能被QEP选中。

 

3)关于数据类型的相关知识

Java int 4 个字节,long 8 个字节,因为带有符号位,

所以 int 的最大数是 2的31次方-1 = Integer.MAX_VALUE = https://my.oschina.net/heweipo/blog/2147483647

所以 long 的最大数是 2的63次方-1 = Long.MAX_VALUE = https://my.oschina.net/heweipo/blog/9223372036854775807

然而我们定义 mysql 时都是 int(num) ,bigint(num),int 4个字节,bigint 8 个字节

但是他们都可以定义是声明符号位,如 bigint(20) unsigned,这说明不需要符号位。

对于后面的 num,其实他只是一个最大显示宽度,也就是宽度不足在前面用0补足。对于数据大小的限定只有数据类型和标志位,比如你使用 tinyint 那么他只有1个字节所以最大值是 2的7次方-1 = 127;因此如果插入一个大于他的值时最后插入的就是 127。

另外来说mysql的 char 类型,同样也是这样定义 char(num) ,其中num是字符的最大长度,记住是字符并不是字节哦,比如说 num = 1 ,那么你可以插入任意字符,但是只能是一个,多的会被截取掉,当然 char 也是有字节宽度限制的,char 最大 255 个字节,因为一个字符不一定就是一个字节,所以定义char 时不可能定义到 char(256)否则会出现如下错误:

Column length too big for column 'm_type' (max = 255); use BLOB or TEXT instead

就算你定义到 255,那么也不可能插入 255 个字符,就如上面所说字节和字符是有区别的。

还记得有一次修改 t_paper_type 的值时,他定义的数据类型是 char(1) , 这个 1 表示字符的长度,也就是只有一个,结果我想插入 20 ,但是最后插入进去的是 2 ,可想而知,对于字符类型和数字类型的计算是有很大差别的,一个是计算最大值,一个是计算长度。

 

4)关于 key_len 的计算

可以看出来是否可以为空、可变长度列以及字符集都影响到索引的内部内存大小。

计算规则: 如果是utf-8的varchar 需要 num*2+2

key_len 一个很重要的指标就是可以知道联合索引的使用情况,要知道联合索引要比单列索引的效率高很多,前提是联合索引的列用到最多,为什么这么说呢?因为我们知道mysql使用索引时一个表一般使用一个索引,那么如果是查询条件有很多,结果你又只能用一个单列索引,可想而知,数据的唯一性比较差,查询效果不佳;

要是这个时候有一个联合索引,使你查询的大部分列都被这个联合索引所包含,那么数据唯一性增强,数据查询就会变快。

关于联合索引的一些知识,mysql联合索引有"最左前缀"原理。简单的理解就是只从最左面的开始组合出不同的索引。

假设 table 有三列 name status value ,

建立索引是 index(name,status,value),这个时候相当于在mysql中拥有了如下的3个索引文件:

1)name-status-value

2)name-status

3)name

使用查询语句(条件顺序无所谓)

1) where name = 1 and status = 1 and value =https://my.oschina.net/heweipo/blog/1 // 用到索引,而且查询最快

2) where name = 1 and status = 1 // 用到索引,查询速度中上

3) where name = 1 // 用到索引,效果一般

4) where status = 1 and value =https://my.oschina.net/heweipo/blog/1 // 没用到索引,全部扫描

 

5)关于type的一些知识

 

赞 (0) 评论 分享 ()