MySQL高级学习-第二节-Explain与sql查询截取分析

一、MySQL常见瓶颈

（1）CPU：一般发生在数据装入内存或从磁盘读取数据
（2）IO：磁盘IO瓶颈发生在装入数据远大于内存容量时
（3）服务器硬件的性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态

二、Explain

1.简介

使用EXPLAIN关键字可以模拟sql优化器执行SQL语句查询，从而知道MySQL是如何处理SQL语句的。分析查询语句或是表结构存在的性能瓶颈

2.语法

EXPLAIN+SQL语句；
如 explain select * from user；

3.Explain能查询到哪些信息

执行计划的信息：id、slect_type、table、type、possible_keys、key、key_len、ref、rows、Extra

id（$表的读取顺序$）：select查询的序列号，包含一组数字，表示查询中select子句或操作表的顺序

（1）id相同：执行顺序由上至下,如下图顺序：t1,t3,t2

（2）id不同：如果为子查询，id越大优先级越高，越先被执行，如下图顺序：t3,t1,t2

（3）id即存在相同的，又存在不同的：id相同的，执行顺序由上至下，id不同的，id越大优先级越高，越先被执行，如下图顺序：t3,derived2,t2（注意：derived2中的2是指的id，意思为id为2的查询衍生出的结果表。）

slect_type（$数据读取操作的操作类型$）：

（1）SIMPLE：简单的select查询，不包含子查询和UNION
（2）PRIMARY：查询中包含任意的复杂子查询，最为外层查询被标记为PRIMARY
（3）SUBQUERY：在SELECT或者WHERE列表中包含子查询
（4）DERIVED：在FROM列表包含的子查询被标记为DERIUED
（5）UNION：若第二个SELECT出现在UNION之后，则被标记为UNION
（6）UNION RESULT：从UNION表获取结果的SELECT

table：数据来源表名称

type：访问类型，最好到最次的顺序为：system>const>eq_ref>ref>range>index>ALL，至少达到range，最好达到ref

表结构：
user表

score表

表数据：
user表

score表

（1）system：表只有一条记录
（2）const：通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量

（3）eq_ref：唯一性索引扫描，此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果

（4）ref：非唯一性索引扫描，针对于非唯一或非主键索引，或者是使用了最左前缀规则索引的查询

（5）range： 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录

（6）index：表示全索引扫描 (full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引，而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index

（7）ALL：全表扫描

possible_keys（$哪些索引可以使用$）：查询涉及的所有索引将被列出，但实际不一定使用

key（$哪些索引被实际使用$）：查询中真正用到的索引

key_len：显示的值为索引字段的最大可能长度，并非实际使用长度，在不损失精度的情况下，越短越好

ref：显示了key列记录的索引中查找值所用的列或常量

rows：以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数

Extra：不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示。

（1）Using index： select 操作中使用了覆盖索引
（2）Using where：查询时未找到可用的索引，进而通过where条件过滤获取所需数据
（3）Using temporary：表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到
（4）Using filesort：表示无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引，通常这样的SQL都是需要优化的
（5）Using join buffer：在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。
（6）Impossible where：表示在我们用不太正确的where语句，导致没有符合条件的行
（7）No tables used：我们的查询语句中没有FROM子句，或者有 FROM DUAL子句

三、查询截取分析

1.查询截取分析过程

（1）慢查询开启并捕获
（2）explain+慢查询SQL分析
（3）show profile查询SQL在MySQL中的执行细节和生命周期
（4）MySQL数据库服务器的参数调优

2.查询优化

（1）永远小表驱动大表

（2）ORDER BY关键字优化：

• ORDER BY子句尽可能使用Index方式排序，即索引本身完成排序，避免使用FileSort方式排序
• 使用Index排序的两种情况：
  ORDER BY子句满足索引最佳左前缀原则；
  WHERE子句与ORDER BY子句满足索引最佳左前缀原则；
• FileSort两种算法：
  单路排序：从磁盘中读取查询需要的所有列，按照 order by 列在 sort_buffer(排序缓存) 缓冲区对他们进行排序，然后扫描排序后的列表输出。因为单路排序效率更快，避免了二次读取数据，把随机IO变成了顺序IO，但是会使用更多的空间
  双路排序：MySQL 4.1 之前使用的双路排序，通过两次扫描磁盘得到数据。读取主键id 和 order by 列并对其进行排序，扫描排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
• 优化策略：
  增大sort_buff_size参数设置
  增大max_length_for_sort_data参数设置：增加使用单路排序的概率

（3）GROUP BY关键字优化：

GROUP BY实际是先排序后分组，所以优化策略与ORDER BY相同；
特殊一点在于having，能在where限定的条件，不要去having限定

3.慢查询日志

慢查询日志是MySQL提供的一种日志记录，用于记录超过long_query_time值的sql，long_query_time的默认值为10s，超过该时间的sql都会被记录到慢查询日志中。

(1)慢查询日志开启（不是调优需要，不建议开启）

show variables like ‘%slow_query_log%’：查看慢查询日志是否开启以及慢查询日志存放位置
set global slow_query_log=1：开启慢查询日志
show variables like ‘long_query_time%’：查看慢查询日志记录的时间阈值
set global long_query_time=4：设置慢查询日志记录的时间阈值

4.mysqldumpslow日志分析工具

（1）-s：表示按照什么方式排序

• c：访问次数
• l：锁定时间
• r：返回记录
• t：查询时间
• al：平均锁定时间
• ar：平均返回记录数
• at：平均查询时间

（2）-t：返回前面多少条记录

（3）-g：后边可以写一个正则匹配模式，大小写不敏感的

5.Show Profile

Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

（1）基本查询命令

• show variables like ‘profiling’：来显示当前mysql是否开启profile。
• set profiling = on：开启profile
• show profiles：查看结果
• show profile 参数 for Query SQLID：根据查询ID诊断SQL，参数如下：
  All：所有开销
  Block IO：块IO开销
  CONTEXT SWITCHES：上下文切换开销
  CPU：CPU开销
  IPC：发送和接收信息开销
  MEMORY：内存开销
  PAGE FAULTS：页面错误相关开销
  SOURCE：显示Source_function,Source_file,Source_line相关开销
  SWAPS：交换次数相关开销

（2）诊断时Status四种情况必优化

Status情况	说明
converting HEAP to MyISAM	查询结果太大，内存都不够用了，往磁盘上搬了
Creating tmp table	创建临时表：1. 拷贝数据到临时表 ；2. 用完删除临时表
Copying to tmp table on disk	把内存中临时表复制到磁盘
locked	加锁