1. 数据库服务器的优化步骤

整个流程划分成了观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

---

2. 查看系统性能参数

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

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';
1

• 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：删除操作的次数。

若查询MySQL服务器的慢查询次数，则可以执行如下语句:

SHOW STATUS LIKE 'Slow_queries';
1

3. 统计SQL的查询成本: last_query_cost

一条SQL查询语句在执行前需要查询执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。

如果我们想要查看某条SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是我们评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL 语句所需要读取的读页的数量。

SELECT student_id, class_id, NAME, create_time 
FROM student_info WHERE id = 900001;
1
2

然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

SELECT student_id, class_id, NAME, create_time 
FROM student_info WHERE id BETWEEN 900001 AND 900100;
1
2

然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询：

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 21.120816 |
+-----------------+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间 基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量（last_query_cost）增加了不少 ，但是通过缓冲池的机制，并没有增加多少查询时间 。

SQL查询时一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：

• 位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从 内存 或者 磁盘 中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
• 批量决定效率。如果我们从磁盘中对单一页进行随机读，那么效率是很低的(差不多10ms)，而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

---

4. 定位执行慢的 SQL：慢查询日志

1. 开启慢查询日志参数

# OFF是关闭
mysql > show variables like '%slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.05 sec)

# slow_query_log_file是慢查询日志保存路径
mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------------------------+
| Variable_name       | Value                                                      |
+---------------------+------------------------------------------------------------+
| slow_query_log      | ON                                                         |
| slow_query_log_file | D:\Mysql\mysql-8.0.23-winx64\Data\DESKTOP-O165SR5-slow.log |
+---------------------+------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2. 修改long_query_time阈值

原理阈值为10，也就是查询时间为10s才算慢查询

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

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

mysql> set long_query_time=1;
mysql> show variables like '%long_query_time%';
1
2
3
4
5
6

永久修改

修改my.cnf 文件或my.ini，[mysqld] 下增加或修改参数 long_query_time、slow_query_log 和 slow_query_log_file 后，然后重启 MySQL 服务器。

[mysqld]
slow_query_log=ON  # 开启慢查询日志开关
slow_query_log_file=D:\Mysql\mysql-8.0.23-winx64\Data\DESKTOP-O165SR5-slow.log  # 慢查询日志的目录和文件名信息
long_query_time=3  # 设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE
1
2
3
4
5

3. 查看慢查询数目

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
1

4. 关闭MySQL服务器停止慢查询日志功能有两种方法：

[mysqld]
slow_query_log=OFF
1
2

# 临时性
SET GLOBAL slow_query_log=off;
1
2

5. 删除慢查询日志

找到相关文件去删除

# 或者重置为null
mysqladmin -uroot -p flush-logs slow
1
2

---

5. 数据实验

准备数据

CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `stuno` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `age` INT(3) DEFAULT NULL,
    `classId` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 存储过程
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 ,classId ) VALUES ((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
		UNTIL i = max_num
	END REPEAT;
	COMMIT; #提交事务
END
DELIMITER ;

#调用刚刚写好的函数, 4000000条记录,从100001号开始

CALL insert_stu1(100001,4000000);
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

# 在数据量为4百万情况下，没索引查询时间都超过了慢查询设置阈值，所以下面sql是慢查询sql
SELECT * FROM student WHERE stuno = 3455655;
SELECT * FROM student WHERE name = 'oQmLUr';
1
2
3

慢查询日志分析工具：mysqldumpslow（定位哪些是慢sql）

分析工具是帮助定位慢查询sql的，查看mysqldumpslow的帮助信息：

mysqldumpslow --help
1

要先下载一个perl编译器

D:\Mysql\mysql-8.0.23-winx64\bin>perl mysqldumpslow.pl --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time
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

mysqldumpslow 命令的具体参数如下：

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

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

# windows下的 -s t表示按照查询时间排序，-t 5表示查看前5条sql语句
perl mysqldumpslow.pl -s t -t 5 D:\Mysql\mysql-8.0.23-winx64\Data\DESKTOP-O165SR5-slow.log
1
2

工作常用参考

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more
1
2
3
4
5
6
7
8
9
10
11

---

6. 查看 慢执行SQL 执行成本：SHOW PROFILE（定位sql查询慢原因）

定位到相关慢查询sql后再进一步分析

# 查看是否开启
show variables like 'profiling';
1
2

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

# 查询id为61的sql执行成本
show profile cpu,block io for query 61
1
2

show profile的常用查询参数：

① ALL：显示所有的开销信息。
②BLOCK IO：显示块IO开销。
③CONTEXT SWITCHES：上下文切换开销。
④CPU：显示CPU开销信息。
⑤IPC：显示发送和接收开销信息。
⑥MEMORY：显示内存开销信 息。
⑦PAGE FAULTS：显示页面错误开销信息。
⑧SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。
⑨SWAPS：显示交换次数开销信息。

这里显示执行过程比较慢