MySQL之优化sql

为什么要进行优化

• 在应用开发过程中，由于数据量较小，开发人员写sql语句时往往侧重功能的实现。而随着上线，数据量加大，很多sql语句也开始产生性能上的问题，对生产的影响越来越大，因此就需要对这些sql语句进行优化

查看sql执行频率

show [session|global] status like 'Com_______';
#注意：七个_,session表示当前连接的查询，默认，global代表自数据库创建整个的操作处理执行频率

show [session|global] status like 'Innodb_row_%';
#显示存储引擎Innodb的操作次数

定位低效率执行 sql

• 慢查询日志：不推荐
• show processlist：可以实时的查看到每一个客户端正在执行的慢查询的sql（执行效率比较低的sql）

explain分析执行计划

• 通过以上步骤查询到效率低的sql语句后，可以通过explain或者desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。

explain之id

• id是select查询的序列号，是一组数据，表示的是查询中执行select子句或是操作表的顺序，id情况有三种。
• id相同表示执行表的顺序是从上到下
  
• id值越大，加载优先级越高
  
• id有相同，也有不同。那么可以看做相同的为一组，顺序为上到下。在所有组中，id越大的优先级越高
  

explain 之 select_type

select_type	含义
SIMPLE	简单的select，查询不包括子查询或者union‘
PRIMARY	若查询中包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在select或where包含的子查询
DERIVED	在FROM列表中包含的子查询，被标记为DERIVED（衍生）MySQL会递归这些子查询，把结果放在临时表中
UNION	若第二个select出现在union之后，则标记为UNION
UNION RESAULT	从UNION表获取结果的SELECT

explain 之 table，type

• table：显示的是访问哪个表的
• type显示的是访问类型，是较为重要的一个指标

type	含义
NULL	MySQL不访问任何表，索引，只返回结果（如：select now();#返回当前的日期与时间）
system	表只记录一行数据（等于系统表），这是const类型的特列，一般不会出现
const	表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以效率很快。如将主键置于where列表中，MySQL就能将主键转换为一个常量。const将主键或唯一索引的所有部分与常量值进行比较
eq_ref	类似于ref，区别在于使用的是唯一索引，或使用主键的关联查询，关联查询的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上是一种索引访问
range	只检索给定返回的行。where之后出现between,<,>,in等操作
index	index与all的区别是index只是遍历了索引树，通常比all快，all是遍历了数据文件（explain select id from tb_name)
all	将遍历全表以找到匹配的行(explain select * from tb_name)

explain 之 key，rows，extra

• possible_keys：显示可能应用在这张表的索引
  -key： 实际使用的索引，如果为null，则表示没有使用索引
• key_len：表示索引的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确度的情况下，长度越短越好
• rows：扫描行的数量
• extra：

extra	含义
using filesort	说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序”，效率低
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于order by，group by
using index	表示相应的索引操作使用了覆盖索引，避免访问表的数据行，效率不错

• 如何过出现了using filesort，using tempory就需要考虑进行优化了

show profile 分析 sql

• mysql从5.0.37开始支持增加对show profiles和show profile语句的支持，show profiles能够在做sql优化时告诉我们时间都耗费到哪去了
• 通过have_profiling参数，能够看到当前mysql是否支持profile：select @@have_profiling；
• 默认profile是关闭的,通过select @@profiling；查看是否关闭，0代表关闭
• 可以通过set语句在Session级别开启profiling：select set @@profiling=1；
• 通过show profiles查询每一条sql语句的耗时
• 通过上面的查询sql语句耗时，用show profile for query 5查看相应的sql语句的详细信息

trace分析优化器执行计划

• MySQL5.6提供了对sql的跟踪trace，通过trace文件能够了解为什么优化器使用A计划而不使用B计划
• 打开trace，设置格式为JSON，并设置trace能够使用的最大内存大小，避免解析过程中因为默认内存过小而无法使用

set optimizer_trace = "enabled=on",end_marks_in_json=on;
set optimizer_trace_max_men_size=1000000;

• 执行sql语句select * from tb_item where id<=4;
• 最后，检查information_shema.optimizer_trace就知道MySQL是如何执行sql的select * from information_shema.optimizer_trace\G;