SQL基础优化(1)：定位、分析慢SQL

SQL优化的前提是对慢SQL进行定位，之后使用不同的方式对其进行分析。如果某个设计数据库的功能需要很久才能返回数据，就应该去分析是否是因为慢查询导致的。

定位慢SQL（慢查询）

定位慢SQL有两种解决方式，

1. 查看慢查询日志，确定已经执行完的慢查询
2. show processlist查看正在执行的慢查询

慢查询日志定位

MySQL的慢查询日志用来记录在MySQL中执行时间超过long_query_time参数(默认是10，单位是秒) 的设置值且扫描记录数不少于min_examined_row_limit参数(默认值为0) 的设置值的SQL语句。

默认情况下，MySQL不会记录查询时间超过long_query_time设置值但是不使用索引的语句，可以通过配置log_queries_not_using_indexes = on 使得不使用索引的SQL语句也会被记录到日志中。

慢查询的使用分为四步，

1. 开启慢查询日志
   开启慢查询日志需要对slow_query_log参数进行设置，默认环境下，慢查询日志是关闭的，

set global slow_query_log = on;

2. 设置慢查询时间阈值
   对long_query_time参数进行设置，

set global long_query_time = 1;

上面的语句设置了慢查询时间阈值为1秒。
一般建议线上的业务将时间阈值设置为1秒，如果某些业务对MySQL有较高的QPS(query per second)要求，可以将该值设置为0.1秒。
一般测试环境建议设置较小的时间阈值，实际环境中可以设置的稍微大一些（如测试环境时间阈值的2倍），这有助于在测试过程中发现慢SQL。
对于线上重要业务，测试环境的时间阈值可以设置为0，以便记录下所有语句。

3. 确定慢查询日志路径
   慢查询日志的路径默认是MySQL的数据目录，

mysql> show global variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

4. 确定慢查询日志文件名

mysql> show global variables like 'slow_query_log_file';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
1 row in set (0.00 sec)

设置并确定日志文件位置后可以对文件进行查看，获取其中的信息，如，

tail -n 5 /var/lib/mysql/localhost-slow.log

得到的日志文件中，日志的信息存放在如下字段中，

字段	含义
Time	慢查询发生时间
User@Host	客户端用户及IP
Query_time	查询时间
Lock_time	等待表锁的时间
Row_sent	语句返回的行数
Row_examined	语句执行期间从存储引擎读取的行数

show processlist 定位

当慢查询还没执行完时，慢查询日志中是看不到任何语句记录的。此时使用MySQL的 show processlist 命令可以能够判断正在执行的慢查询。该命令会显示所有正在执行的线程，如果有PROCESS权限，能够看到所有线程；否则，只能看到当前会话的线程。

show full processlist 可以显示执行语句的全部字符，否则默认不使用full的情况下，语句最多显示100个字符

show processlist;

show processlist\G;

语句1和2都会返回目前正在执行的SQL语句。不同的是前者展示形式是一张表格。

Id	User	Host	db	Command	Time	State	Info

以上表格字段是返回的信息。后者的返回形式是文本，

	 Id: 62
   User: root
   Host: localhost:52794
     db: xxx
Command: Query
   Time: 0		# 执行时间
  State: Sending data	
   Info: select * from xxxx		# SQL语句

分析慢查询

定位到慢查询后，进一步可使用explain、show profile和trace对慢查询进行分析。

explain分析慢查询

explain可以获取MySQL中语句的执行计划，使用的方式是在查询语句前加上explain 即可。这种方法是分析SQL时最常用也是最推荐的一组方式。

创建一个SQL文件，输入如下内容，

use db;
drop table if exists t1;

create table t1 (
id int(11) not null auto_increment,
a int(11) default null,
b int(11) default null,
create_time datetime not null default current_timestamp comment '记录创建时间',
update_time datetime not null default current_timestamp on update current_timestamp comment '记录更新时间',
primary key (id),
key idx_a (a),
key idx_b (b)
) engine=InnoDB default charset=utf8mb4;

drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */
delimiter ;;
create procedure insert_t1()        /* 创建存储过程insert_t1 */
begin
  declare i int;                    /* 声明变量i */
  set i=1;                          /* 设置i的初始值为1 */
  while(i<=1000)do                  /* 对满足i<=1000的值进行while循环 */
    insert into t1(a,b) values(i, i); /* 写入表t1中a、b两个字段，值都为i当前的值 */
    set i=i