SQL优化

1、概述

在系统开发的初期，数据量少，开发人员只需要满足功能的需求即可。但是随着系统上线时间的增加，数据量也不断增加，sql的性能问题也就暴露了出来，因此，SQL的优化很重要。

2、查看SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。

show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

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

show status like 'Com_______';

show status like 'Innodb_rows_%';

Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。
Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

参数	含义
Com_select	执行 select 操作的次数，一次查询只累加 1。
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update	执行 UPDATE 操作的次数。
Com_delete	执行 DELETE 操作的次数。
Innodb_rows_read	select 查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections	试图连接 MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

3、定位慢查询SQL

定位慢查询的方法有两种：

查询日志：

show processlist命令 ：

慢查询日志需要在查询结束的时候才可以查到该记录，所以在找bug时不能够定位问题。show processlist就可以查看当前线程的sql的执行情况。

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

4、explain分析低效sql语句

通过上述方法定位到慢查询sql以后，可以通过explain来分析慢查询sql。

1. id列，select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
2. select_type列，表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等。
3. table列，输出结果集的表。
4. type列，表示表的连接类型，性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref-------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------>all )。
5. possible_keys列，表示查询时，可能使用的索引。
6. key 列，表示实际使用的索引。
7. key_len 列，索引字段的长度。
8. rows列，扫描行的数量。
9. extra列，执行情况的说明和描述。