一、数据库服务器的优化步骤

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。
整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。
小结：

二、查看系统性能参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。
SHOW STATUS语句语法如下：
SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：

• Connections：连接MySQL服务器的次数。
• Uptime：MySQL服务器的上线时间。
• Slow_queries：慢查询的次数。
• Innodb_rows_read：SELECT查询返回的行数
• Innodb_rows_inserted：执行INSERT操作插入的行数
• Innodb_rows_updated：执行UPDATE操作更新的行数
• Innodb_rows_deleted：执行DELETE操作删除的行数
• Com_select：查询操作的次数。
• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
• Com_update：更新操作的次数。
• Com_delete：删除操作的次数。

三、统计SQL的查询成本：last_query_cost

我们以student_info表为例：

如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：

SELECT student_id,class_id,`name`,create_time FROM student_info WHERE id = 900001;
1

运行结果（1 条记录，运行时间为 0.042s ）
然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：
SHOW STATUS LIKE 'last_query_cost';

如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？

SELECT
	student_id,
	class_id,
	`name`,
	create_time 
FROM
	student_info 
WHERE
	id BETWEEN 900001 
	AND 900100;
1
2
3
4
5
6
7
8
9
10

运行结果（100 条记录，运行时间为 0.046s ）：
然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询。

你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然 页数量（last_query_cost）增加了不少 ，但是通过缓冲池的机制，并 没有增加多少查询时间 。
使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

四、定位执行慢的SQL：慢查询日志

4.1、开启慢查询日志参数

1. 开启 slow_query_log

set global slow_query_log='ON';
1

然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件名：

能看到这时慢查询分析已经开启，同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件中。

2. 修改long_query_time阈值
   接下来我们来看下慢查询的时间阈值设置，使用如下命令：

show variables like '%long_query_time%';
1

这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
set global long_query_time = 1;
show global variables like '%long_query_time%';
set long_query_time=1;
show variables like '%long_query_time%';
1
2
3
4
5

4.2、查看慢查询数目

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
1

4.3、案例演示

1. 建表

CREATE TABLE `student` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`stuno` INT NOT NULL,
	`name` VARCHAR ( 20 ) DEFAULT NULL,
	`age` INT ( 3 ) DEFAULT NULL,
	`class_id` INT ( 11 ) DEFAULT NULL,
   PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
1
2
3
4
5
6
7
8

2. 设置参数log_bin_trust_function_creators
   创建函数，假如报错：This function has none of DETERMINISTIC......
   命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1;   # 不加global只是当前窗口有效。
1

3. 创建函数
   随机产生字符串：

-- 函数1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(n INT) 
	RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN 
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO 
       SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
       SET i = i + 1;
    END WHILE;
    RETURN return_str;
END //
DELIMITER ;
#测试
SELECT rand_string(10);

-- 函数2：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN 
DECLARE i INT DEFAULT 0; 
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1));
RETURN i; 
END //
DELIMITER ;
#测试：
SELECT rand_num(10,100);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

4. 创建存储过程

# 创建插入学生信息表存储过程
DELIMITER //
CREATE PROCEDURE insert_stu1(`start` INT,max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
 SET autocommit = 0; # 设置手动提交事务
 REPEAT # 循环
 SET i = i + 1; # 赋值
 INSERT INTO student(stuno,`name`,age,class_id) VALUES
 ((`start`+ i),rand_string(6),rand_num(10,100),rand_num(10, 1000));
 UNTIL i = max_num
 END REPEAT;
 COMMIT; # 提交事务
END //
DELIMITER;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

5. 调用存储过程

#调用刚刚写好的函数, 4000000条记录,从100001号开始
CALL insert_stu1(100001,4000000);
1
2

4.4、测试及分析

1. 测试
   
   从上面的结果可以看出来，查询学生编号为3455655的学生信息花费时间为3.14秒。查询学生姓名为oQmLUr的学生信息花费时间为3.23秒。已经达到了秒的数量级，说明目前查询效率是比较低的，下面我们分析一下原因。
2. 分析
   

4.5、慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow 。
查看mysqldumpslow的帮助信息
mysqldumpslow --help

mysqldumpslow 命令的具体参数如下：

• -a: 不将数字抽象成N，字符串抽象成S
• -s: 是表示按照何种方式排序：
  c: 访问次数
  l: 锁定时间
  r: 返回记录
  t: 查询时间
  al:平均锁定时间
  ar:平均返回记录数
  at:平均查询时间 （默认方式）
  ac:平均查询次数
• -t: 即为返回前面多少条的数据；
• -g: 后边搭配一个正则匹配模式，大小写不敏感的；

举例：我们想要按照查询时间排序，查看前五条SQL语句，这样写即可：

工作常用参考：

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log | more
1
2
3
4
5
6
7
8

4.6、关闭慢查询方式

MySQL服务器停止慢查询日志功能有两种方式：

1. 方式1：永久性方式
   

[mysqld]
slow_query_log=OFF
1
2

或者，把slow_query_log一项注释掉 或 删除

[mysqld]
#slow_query_log =OFF
1
2

重启MySQL服务，执行如下语句查询慢日志功能。

SHOW VARIABLES LIKE '%slow%';  #查询慢查询日志所在目录
SHOW VARIABLES LIKE '%long_query_time%';  #查询超时时长
1
2

2. 方式2：临时性方式
使用SET语句来设置。
（1）停止MySQL慢查询日志功能，具体SQL语句如下。

SET GLOBAL slow_query_log=off;
1

（2）重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下:

SHOW VARIABLES LIKE '%slow%';
#以及
SHOW VARIABLES LIKE '%long_query_time%';
1
2
3

五、查看SQL执行成本：SHOW PROFILE

show variables like 'profiling';
1

通过设置 profiling='ON’ 来开启 show profile：

然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：

你能看到当前会话一共有 1 个查询。如果我们想要查看最近一次查询的开销，可以使用：

show profile cpu,block io for query 2;
1

show profile的常用查询参数：

1. ALL：显示所有的开销信息。
2. BLOCK IO：显示块IO开销。
3. CONTEXT SWITCHES：上下文切换开销。
4. CPU：显示CPU开销信息。
5. IPC：显示发送和接收开销信息。
6. MEMORY：显示内存开销信息。
7. PAGE FAULTS：显示页面错误开销信息。
8. SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
9. SWAPS：显示交换次数开销信息。