SQL语句的优化步骤（一）

        mysql数据库是常用的一种数据库处理语言，也是限制IT行业应用较广的一种数据库语言。数据库是用来处理数据，对数据进行增删改查（CRUD），对于大容量的数据处理来说，需要处理的事件可能有1天，2天，那对SQL语句的优化就至关重要了。

        以下从5个方式来初步理解SQL优化的基本步骤

一、查询执行效率 

1、查看数据库中各命令的使用频率

show global status like 'com_______'

2、查看innoDB在进行增删改查时的频率

show global status like 'innoDB_rows_%' ;       

 二、定位低效率的执行SQL频率

          可以通过以下两种方式进行定位执行效率较低的SQL语句

1、慢查询日志：（查看已经执行完成的SQL语句的执行频率）

2、show processlist (实时查看语句的运行速度)

 下面介绍每个字段的含义

标题	含义
Id	系统分配的之前执行的每条语句的序号
User	当前用户名
Host	显示这条语句发送的主机号、端口，可以用来跟踪出现问题的用户
db	显示这个进程目前连接的时哪个数据库
Command	显示目前连接的命令执行状态，休眠(sleep)，查询(query)，连接(connect)
Time	显示这个状态持续的时间
state	显示目前SQL语句状态
Info	显示这一行数据的记录时对应哪条执行语句的

注意：state状态语句：

            eg: select 语句为列，要经过copying to tmp  table,sorting result,sending  data等状态才能完成。

三、explain 分析执行计划

        在sql语句之前加上explain 可以查询这条语句执行时候的执行计划

explain select * from tb_score;

分析每个字段的含义：

字段	含义
id	select查询的序列号，表示执行select语句的顺序
select_type	表示select的类型，常见的有SIMPLE(简单表)、PRIMARY(主键查询)、UNION（连接查询，union后语句）、SUBQUERY(子查询中的第一个select)
table	输出结构的表
type	表示表连接的类型，性能由高到低排序（system-->const-->eq_rf-->ref-->ref_or_null-->index_merge-->index_subquery-->range-->index-->all）
possible_keys	表示查询时候可能用到的索引
key	表示查询的时候实际用到的索引
key_len	所用索引的的长度
rows	扫描行的数量
extra	执行情况的说明和描述  using filesort  / using index/using tenporary

3.1  Id  select 查询序列号

       d字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序，有一下集中情况

       1、 id 相同表示加载顺利从上往下

       2、 id 不同id值越大，优先级越高，优先被执行

3.2 select_type 表类型

        取值含义：（从上到下效率越来越低）

simple 简单的select查询，不包含子查询或者union

primary 在含有子查询的语句中，最外面的查询就是primary

subquery 子查询

derived （衍生），将子查询的表放在临时表中

union 联合查询

union result 从union表中获取结构的select

3.3 table 和 type

         table 标识来源哪个表

         type 显示的是访问类型，是较为重要的一个指标

         类型，从上往下访问，访问速度越来越低

null 不访问任何表和索引，直接返回结果 select * from now();

system 表中只有一条记录（系统表）

const （常量唯一索引查询）只通过一次索引就能找到，一般用于主键和unique,只有一条记录

eq_ref 关联查询且结果只有一条

ref 非唯一性索引查询，返回某一单独值的所有行

range 范围查询

index 遍历了所有的索引数（b-tree结构）

all 将遍历全表以找到匹配的行

3.4  key

possible key 可能用到的索引

key 实际用到的索引，如果是null 就是没有走索引

key_len key索引的长度，越短越好

3.5 rows

         rows 扫描的行数

3.6 xtra

using filesort :使用文件进行排序 ，效率低，耗性能

explain select * from student order by sname

此时sname不是索引，即需要全盘扫描再排序，效率低，优化过程就是给sname进行创建索引。

using temporary 使用临时表，效率低，耗性能

using index 使用索引，速度最快

注意：如果出现前两个就需要考虑优化

四、show profile 分析sql

       检测sql语句执行的时间

  4.1、检查当前系统是否支持 profile指令

          select @@have_profiling；

  4.2、检查profile是否开启

           select @@profiling；

   4.3、开始profile功能

           set @@profiling；

    4.4、查看之前所有指令的耗时时间

            show profiles;

    4.5、查看某条指令耗时再哪些地方

               show prifile for query 5;          //query 5表示执行的顺序id

五、trace分析优化器

sql语句-->封装-->trace优化器

1、开始优化器

 set optmizer_trace='enabled=on',end_markers_in_json=on;

2、设置占用的内存大小

set optimizer_trace_max_men_size=1000000;

3、查看优化器执行计划

select  * from information_schma.optimizer_trace\G;