一、优化SQL步骤

在应用的的开发过程中,由于初期数据量小,开发人员写SQL语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多SQL语句开始逐渐显露出性能问题,对生产的影响也越来越大,此时这些有问题的SQL语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化,本章将详细介绍在MySQL中优化SQL语句的方法。当面对一个有SQL性能问题的数据库时,我们应该从何处入手来进行系统的分析,使得能够尽快定位问题SQL并尽快解决问题。

1、查看SQL执行频率

MySQL客户端连接成功后,通过show [session | global] status命令可以提供服务器状态信息。show [session | global] status可以根据需要加上参数"session”或者"global"来显示session级(当前连接)的计结果和global级(自数据库上次启动至今)的统计结果。如果不写,默认使用参数是"session"，即当前会话的sql执行频率。

下面的命令显示了当前session中所有统计参数的值:

 show status like `Com_______`；

针对InnoDB引擎数据表的操作频率记录：

show status like 'InnoDB_rows_%';

Com_xxx表示每个xxx语句的执行次数，我们通常关心一下几个参数：

2、定位低效率执行SQL

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

2.1 慢查询日志

通过慢查询日志定位那些执行效率较低的SQL语句,用slow_query_log_file=路径，选项启动时, mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。

如果你接手了一个项目，跑得很慢，想找到执行比较慢的语句，不仅仅是select语句这时候mysql的慢查询日志可能会对你有很大帮助。

数据库默认情况下是不会开启慢查询日志记录的。在配置文件my.cnf 或者 mariaDB的my.ini 或者命令行：

show variables like 'long_query_time';

执行上面命令之后就可以查询到默认的慢查询时间，也就是sql语句超时的时间，mysql默认是10秒太长，设置成1秒

set long_query_time=1;

这种写法只对当前数据库链接有效，链接资源销毁即失效，如果需要一直有效的话，需要更改数据库配置文件，找到long_query_time这个参数改成相应的秒数就行

慢查询日志文件 慢查询超时后，mysql默认是不会记录到日志文件的，也是需要手动开启

（1）方法一：在命令行使用命令开启慢查询日志 此方法适用于在MySql8.0以上版本使用，直接在命令行输入命令就可以了

show variables like '%slow_query%'; # 可以用这个查询所有的变量

##第一步
set global log_output='TABLE'; #开启慢日志,纪录到 mysql.slow_log 表
set global long_query_time=1;  #设置超过1秒的查询为慢查询
set global slow_query_log='ON'; #打开慢日志记录

##第二步 
select * form mysql.slow_log; #查询慢日志的所用信息
# 查询慢日志记录的慢sql语句
select convert(sql_text using utf8) sql_text from mysql.slow_log;

##第三步 不使用的时候记得关上日志
set global slow_query_log='OFF'; #如果不用了记得关上日志

上面的路径一旦设置好不要轻易修改

（2）方法二：在MySQL配合文件中配置相关参数 在mysql配置配置文件中直接配置如下信息：

#开启慢查询
slow_query_log = true
#慢查询日志存放地址
slow_query_log_file = /usr/local/mysql/data/slow_query_log.log
#超过的时间为1s；MySQL能够记录执行时间超过参数 long_query_time 设置值的SQL语句，默认是不记录的。
long_query_time = 1

2.2 show processlist

慢查询曰志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等,可以实时地查看SQL的执行情况,同时对一-些锁表操作进行优化。

1)id列，用户登录mysq1时，系统分配的"connection id"，可以使用函数connection_id()查看
   
2)user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sq1语句
    
3)host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
    
4)db列，显示这个进程目前连接的是哪个数据库
    
5)command列，显示当前连接的执行的命令，一般取值为休眠(sleep) ，查询(query) ，连接(connect )等
    
6)time列，显示这个状态持续的时间，单位是秒
    
7)state列，显示使用当前连接的sq1语句的状态，很重要的列。stat e描述的是语句执行中的某一 个状态。一个sq1语句，以查询为例，可能需要经过copying to tmp table、sorting result、 sending data等状态才可以完成
    
8) info列，显示这个sq1语句,是判断问题语句的一个重要依据

3、explain分析SQL执行计划 —重要

（1）什么是Explain?

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

用法：

explain sql语句

explain执行后返回的信息

explain select * from article where article_id=150;

表中各个字段的含义：

接下来我们来详细了解一下各个字段的含义。

（2）环境准备

CREATE TABLE t1(
    id INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL, 
    PRIMARY KEY (id)
);

CREATE TABLE t2(
    id INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL, 
    PRIMARY KEY (id)
);

CREATE TABLE t3(
    id INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL, 
    PRIMARY KEY (id)
);

CREATE TABLE t4(
    id INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL, 
    PRIMARY KEY (id)
);

INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

（3）explain 之 id

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

①id 相同，执行顺序由上至下

explain select * from t1,t2,t3 where t1.id=t2.id and t2.id=t3.id;

②id 不同，如果有子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

explain select t1.id from t1 where t1.id =(select t2.id from t2 where t2.id =(
   select t3.id from t3 where t3.content='')
 );

③有相同也有不同

（4）explain 之 select_type

select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

这几种查询类型的效率，从上到下越来越差。

• SIMPLE

SIMPLE简单理解就是单表查询

• PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary。

• SUBQUEUERY

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

• DERIVED

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

• UNION

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

• UNION RESULT

从 UNION 表获取结果的 SELECT。

（5）explain 之 type

type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index>ALL ，一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

• system

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

• const

表示通过索引一次就找到了,const 用于比较主键或索引。因为只匹配一行数据，所以很快如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。

• eq_ref

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

• ref

非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

创建索引之前：

创建索引之后：

• ref_or_null

对于某个字段既需要关联条件，也需要 null 值得情况下。查询优化器会选择用 ref_or_null 连接查询。

• index_merge

在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的 sql 中。

• unique_subquery

该联接类型类似于 index_subquery。 子查询中的唯一索引。

• index_subquery

利用索引来关联子查询，不再全表扫描。

• range

只检索给定范围的行,使用一个索引来选择行。key属性列显示使用了哪个索引，一般就是在你的 where 语句中出现了between、<、>、in 等范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

• index

出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。

• ALL

遍历全表以找到匹配的行，效率最低。

（6）explain 之 possible_keys 、key、key_len

• possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出， 但不一定被查询实际使用，如果没有就是NULL。
• key：使用到的索引，如果没用索引就是NULL。
• key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分。

（7）explain 之 ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

（8）explain 之 Extra

其他的额外重要的信息。常见的信息有以下几种：

4、show profile分析执行SQL花费的时间

有时候光靠explain分析执行计划并不能准确的定位sql问题，好在MySQL从5.0.37版本开始增加了对show profiles和show profile的语句支持。

通过have_profiling参数，可以看到sql是否支持profile：

默认profiling是关闭的，可以通过set语句在session级别开启profiling:

如果profiling没有开启，可以使用set profiling=1开启

set profiling=1;   //开启profiling

通过profiling，我们可以更清楚的了解SQL语句的执行过程。

之后我们就可以使用show profiles来分析每个SQL语句执行花费的时间：

定位到怀疑有问题的SQL语句之后，可以在使用show profile for query Query_Id来查看一个SQL语句执行的各个过程中花费的时间：

另外，我们还可以使用show profile all for query Query_Id来查看SQL语句执行过程中的全部信息。

总之，通过show profile这一系类命令可以在sql优化的时候告诉我们时间主要浪费在哪些地方了。

5、通过trace分析 优化器 如何选择执行计划

mysql5.6是通过trace文件进一步告诉我们优化器是如何选择执行计划的。（即mysql5.6提供了对sql的跟踪trace文件，从而得知优化器为何选择a执行计划而不选择b执行计划，有助于我们理解优化器的行为。）

首先打开trace,设置格式为json,设置trace最大能够使用内存的大小，避免解析过程中因为默认内存过小不能完全显示：

set optimizer_trace="enabled=on" ,end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

然后执行一下想做追踪的语句，之后使用如下命令就可以查看语句执行的详细信息了。

select * from information_schema.optimizer_trace\G;

至此，通过上面几部就可以定位大部分SQL执行缓慢的问题了，找到问题之后就可以对症下药。其中使用explain分析SQL执行计划是非常重要的一个环节。

二、SQL优化的些许经验

SQL优化主要需要借助explain工具进行，总的来说，通常有以下几种优化原则:

• 让主要查询语句使用到合适的索引,type出现ALL(全表扫描)需格外注意,同时建立合适的索引以减少possible_keys的数量。
• type最好能达到ref级别。
• Extra列出现Using temporary、Using filesort（文件排序）务必去除。

（1）对order by / group by 后面的字段建立索引

当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序，Using filesort（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。

但是如果我们对该字段建立索引，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）。group by同理。

因此对于order by后面的字段我们可以适当的建立索引。

（2）尽量少使用子查询，而是使用join替代

对join语句匹配关系（on）涉及的字段建立索引能够提高效率。

（3）索引覆盖

如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。

这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。

（4）like查询，不能以通配符开头

比如下面这种写法：

select * from user where name like '%晓%'

这种SQL语句是没办法使用索引的（like语句匹配表达式以通配符开头），只能做全表扫描，效率极低，在实际工程中几乎不被采用。而一般会使用第三方提供的支持中文的全文索引来做。

但是下面这种写法是可以的：

select * from user where address like '科技%'

（5）使用复合索引

尽量对多个字段创建复合索引，而不是创建多个单独索引

（6）OR优化

对于包含OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引;如果没有索引,则应该考虑增加索引。

（7）limit查询优化

我们平常使用分页语句是这么写的：

select * from user where limit 1000000,500;

这么写需要对前1000500个记录排序，但是仅仅返回500条数据，这样做的代价太大了，因此我们可以使用索引天生是排序的特性来优化SQL语句。

优化思路一

在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。

select * from user,(select id from user order by id limit 1000000,500) t where user.id=t.id;

虽然有一个嵌套查询，并且还是使用了limit，但是那是基于主键id的，主键id一般都是由索引的，因此这一块走索引会快很多。

优化思路二

该访案适用于主键自增的表,可以把limit查询转换成某个位置的查询。

select * from user where id>1000000 limit 10;

这个方法对于主键ID是连续的表很有用，但是如果表的ID不连续或无序那还是用第一种方法吧。