8.explain分析SQL

explain作用

使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

explain语法

explain + SQL语句

explain字段解释

一、id表的读取顺序

select查询的顺序号,包含一组数字,表示查询中执行select子句或操作表的顺序
两种情况:

1. id相同,执行顺序由上至下
2. id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

二、select_type—数据读取操作的操作类型

1. simple 简单的select查询,查询中不包含子查询或者union
2. primary 查询中若包含任何复杂的子部分,最外层查询则被标记。就是最后加载的那个
3. subquery 在select或where列表中包含了子查询
4. union 若第二个select出现在union之后,则被标记为union
5. union result 从union表获取结果的select

三、table—显示这一行的数据时关于那张表的

四、partitions-查询访问的分区

五、type

从最好到最差依次是

system > const > eq_ref > ref > range > index > ALL
- system,表只有一行记录(等于系统表),这是const类型的特例
- const表示通过索引一次就找到了,const用于比较primary key
- eq_ref,唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配
- ref,非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行

- range,只检索给定范围的行,使用一个索引来选择行。
- index,Full Index Scan,index与ALL区别为index类型只遍历索引树。
- ALL,将遍历全表找到匹配的行

六、possible_keys

显示可能应用在这张表中的索引,一个或多个。

七、key

实际使用的索引。如果为null,则没有使用索引
查询中若使用了覆盖索引,则该索引仅出现在key列表中

八、key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好
key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的

九、索引长度计算

varchr(24)变长字段且允许NULL
24*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchr(10)变长字段且不允许NULL  
10*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL      
10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL    
10*(Character Set：utf8=3,gbk=2,latin1=1)

十、ref

显示索引那一列被使用到了

十一、rows

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

十二、Extra

包含不适合在其他列中显示但十分重要的额外信息

- Using filesort,说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作称为（文件排序）（如果可以就进一步优化）
- Using temporary,使用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。
- Using index,使用了索引,避免了全表扫描（优先）
- Using where,使用了where过滤
- Using join buffer,使用了连接缓存
- impossible where,不可能的条件,where子句的值总是false

影响服务器性能的几个方面

1.服务器硬件
2.服务器的操作系统
3.数据库存储引擎的选择
4.数据库参数配置
5.数据库结构设计和SQL语句

SQL性能下降原因

1.查询语句写的不好
2.索引失效 
3.关联查询太多join
4.服务器调优及各个参数设置

SQL加载顺序

手写SQL的顺序

select distinct 
    <select _list>
from 
    <left_table>
join  <right_table>on<join_codition>;
where
    <where_condition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit <imit number>

机读的SQL顺序

MySQL常见瓶颈

• CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘读取数据的时候
• IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈