MySQL高级之第三天: 性能分析

今天主要是学习了MySQL的性能分析部分, 主要用到的关键字是explain, 它用于分析SQL语句的性能, 感觉今天满满的都是干货!!!

由于比较少人看我的博客, 我也不太在意你们是否会转走我的博客或者抄袭什么的, 总之希望你们都能够有收获!!!

---

MySQL性能分析

1. MySQL Query Optimizer

        MySQL有专门负责优化SELECT语句的优化器模块.

        主要功能: 通过计算分析系统中收集到的统计信息, 为客户端请求的Query提供它认为的最优执行计划.

 

2. MySQL常见瓶颈:

1. CPU: CPU在饱和的时候一般发生在数据载入内存或从磁盘上读取数据时候

2. IO: 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

3. 服务器硬件的性能瓶颈: top free iostat和vmstat来查看系统的性能状态

 

3. 分析Sql语句的关键字Explain:

3.1 是什么?

        查看执行计划, 可以模拟优化器执行SQL查询语句, 从而知道MySQL是如何处理程序员的SQL语句, 分析你的查询语句或是表结构的性能瓶颈

3.2 能干嘛?

1. 表的读取顺序(id)

2. 数据读取操作的操作类型(select_type)

3. 哪些索引可以使用(possible_keys)

4. 哪些索引被实际使用(key)

5. 表之间的引用(ref)

6. 每张表有多少行被优化器查询(rows)

3.3 怎么玩?

1. Explain + SQL语句

2. 执行计划包含的信息

id| select_type| table| type| possible_keys| key| key_len| ref| rows| Extra

3.4 各字段解释

3.4.1 id(很重要):

select查询的序列号,包含一组数字,表示查询中执行select字句或操作表的顺序

三种情况:

1. id相同, 执行顺序由上到下

2. id全部不同, 如果是子查询, id的序号会递增, id的值越大优先级越高,越先被执行

3. id相同不同, 同时存在, 第2点和第1点的结合,id值越大优先级越高,越先被执行,id相同, 执行顺序由上到下

3.4.2 select_type:

SIMPLE PRIMARY SUBQUERY DERIVED UNION (UNION RESULT)

SIMPLE:

简单的select查询, 查询中不包含子查询或者UNION

PRIMARY:

查询中若包含任何复杂的子查询,最外层查询则被标记为PRIMARY

SUBQUERY:

在SELECT或WHERE列表中包含了子查询

DERIVED:

在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归这些子查询, 把结果放在临时表中

UNION:

若第二个SELECT出现在UNION之后, 则被标记为UNION;若UNION包含在FROM子句的子查询中, 外层SELECT被标记为:DERIVED

UNION RESULT:

从UNION表获取结果的SELECT

3.4.3 table:

这一行的数据是哪张表的

3.4.4 type(很重要):

显示查询使用了何种类型,

类型有如下:

ALL index range ref eq_ref const, system NULL

从最好到最差依次是:

system>const>eq_ref>ref(常见)>range>index>ALL

一般来说,至少优化到ref.

system:

表只有一行记录(等于系统表), 这是const类型的特列, 平时不会出现, 这个可以忽略不计

const:

索引一次就找到了, const用于比较primary key或者unique索引.

eq_ref:

唯一性索引扫描, 对于每个索引键, 表中只有一条记录与之匹配.常见于主键

或称为唯一索引扫描

ref:(一般的SQL语句到达这一级,已经是相当好的了)

非唯一性索引扫描, 返回匹配某个单独值的所有行.

例子 : 

explain SELECT * FROM photo ph  LEFT JOIN project pro ON pro.pro_name = ph.photo_type;

一个项目可以对应一张照片或者多张照片, 而对pro_name建立索引后, 会对某个项目的名字作为索引去匹配图片表中所有与该名字匹配的图片的行.

只需关注红框,其余属性在后面都会有说明.第一个红框代表查询了project表, 查询类型为ref, 可能使用到的索引是idx_proName, 实际使用到的也是idx_proName, 说明没有发生索引失效问题(详情可百度, 知识的海洋无边无际哈哈哈哈哈~)

range:

只检索给定范围的行, 使用一个索引来选择行, key列显示使用了哪个索引

一般是在where语句中出现了between, <, >, in等的查询

index:

Full Index Scan, 只遍历索引树, 比ALL快, 因为索引文件通常比数据小,

index从索引里找, ALL从硬盘中找, IO大.

ALL:

遍历全表.

3.4.5 possible_keys:

显示理论上应用在这张表的索引, 可能一个或多个

查询涉及到的字段上若存在索引, 则该索引会被列出, 但不一定被查询实际使用.

3.4.6 key(很重要):

显示实际上使用到的索引, 如果为NULL, 则没有使用索引

查询中若使用了覆盖索引, 则该索引仅出现在key列表中

覆盖索引是什么?

select 后查询的字段个数和顺序与建立的复合索引个数和顺序刚好吻合.

3.4.7 key_len:

显示索引字段最大可能长度, 并非实际使用长度, 即key_len是根据表定义计算而得, 不是通过表内检索出的.

在不损失精确度的情况下, 索引字段的长度越短越好

3.4.8 ref:

显示索引的哪一列被使用了, 如果可能的话, 是一个常数.

哪些列或常量被用于查找索引列上的值.

3.4.9 rows(很重要):

根据表统计信息及索引选用情况, 大致估算出所需记录查询的行数(查询的越少越好)

3.4.10 Extra(很重要):

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

1. Using filesort(很重要) :

九死一生!!!

说明MySQL会对数据使用一个外部的索引排序, 而不是按照表内的索引顺序进行读取.

MySQL无法利用索引完成的排序操作称作"文件内排序".

若出现则代表SQL语句写的烂, 执行速度会下降.

2. Using temporary(很重要):

十死无生!!!

使用了临时表保存中间结果, 在查询结果排序时使用临时表. 大幅度影响执行性能

常见于排序order by和分组查询group by

3. Using Index(很重要):

发财啦, 看到这个是好事!!!

表示响应的select操作使用了覆盖索引(Covering Index), 避免访问了表的数据行, 效率不错!

如果同时出现using where, 表明索引被用来执行索引键值的查找;

如果没有同时出现using where, 表明索引用来读取数据而非执行查找动作

覆盖索引(Covering Index):

select的数据列只用从索引中就可以取得, 不必读取数据行, MySQL可以利用索引返回select列表中的字段, 而不必根据索引再次读取数据文件, 换句话说, 查询列要被所建的索引覆盖.

 

Join语句的优化

尽可能减少Join语句中的NestedLoop的循环总次数; "永远用小表驱动大表"

优先优化NestedLoop的内层循环

保证Join语句被驱动表上Join条件字段已经被索引;

---

结束语 : 希望我能够给你们带来一点小的帮助, 知识的坑是非常深的, 尽管跳下去吧!加油!!!