在数据库调优中，我们的目标就是 响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式
1. 数据库服务器的优化步骤
当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。
整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）
可以看到数据库调优的步骤中越往金字塔尖走，其成本越高，效果越差，因此我们在数据库调优的过程中，要重点把握金字塔底部的 sql 及索引调优，数据库表结构调优，系统配置参数调优等软件层面的调优
2. 查看系统性能参数
可以使用 SHOW STATUS 语句查询一些数据库服务器的性能参数和使用频率。
其语法如下：
SHOW [GLOBAL][SESSION] STATUES 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：删除操作的次数。
举例：
若查询MySQL服务器的连接次数，则可以执行如下语句
mysql> SHOW STATUS LIKE 'Connections'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 34 | +---------------+-------+ 1 row in set (0.00 sec)
- 若查询服务器工作时间，则可以执行如下语句：
mysql> SHOW STATUS LIKE 'Uptime'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Uptime | 332933 | +---------------+--------+ 1 row in set (0.00 sec)
- 若查询MySQL服务器的慢查询次数，则可以执行如下语句：
mysql> SHOW STATUS LIKE 'Slow_queries'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 0 | +---------------+-------+ 1 row in set (0.00 sec)
注：慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行 表结构优化 或者查询语句优化
- 查看存储引擎增删改查的行数，则可以执行如下语句：
mysql> show status like 'innodb_rows_%'; +----------------------+----------+ | Variable_name | Value | +----------------------+----------+ | Innodb_rows_deleted | 0 | | Innodb_rows_inserted | 1000902 | | Innodb_rows_read | 37011100 | | Innodb_rows_updated | 0 | +----------------------+----------+ 4 rows in set (0.00 sec)
3. 统计 SQL 的查询成本：last_query_cost
我们依然使用student_info表为例（具体库表创建以及生成数据语句请参考上一篇章）
①如果我们想要查询 id=900001 的记录，我们可以直接在聚簇索引上进行查找：
mysql> SELECT * FROM student_info WHERE id = 900001; +--------+------------+--------+-----------+----------+---------------------+ | id | student_id | name | course_id | class_id | create_time | +--------+------------+--------+-----------+----------+---------------------+ | 900001 | 154633 | SYnwsA | 10019 | 10134 | 2022-08-08 22:33:02 | +--------+------------+--------+-----------+----------+---------------------+ 1 row in set (0.00 sec)
然后再看下查询优化器的成本，实际上我们只需要检索一个页即可。Value表示 I/O 加载的数据页的页数
mysql> SHOW STATUS LIKE 'last_query_cost'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | Last_query_cost | 1.000000 | +-----------------+----------+ 1 row in set (0.01 sec)
②我们扩大下查询范围，student_id> 199900的学生记录呢？运行时间 0.01s，这时我们大概需要进行 232个页的查询
mysql> SELECT * FROM student_info WHERE student_id > 199900; +--------+------------+--------+-----------+----------+---------------------+ | id | student_id | name | course_id | class_id | create_time | +--------+------------+--------+-----------+----------+---------------------+ //... | 523982 | 200000 | vcaUvw | 10010 | 10173 | 2022-08-08 22:32:31 | +--------+------------+--------+-----------+----------+---------------------+ 516 rows in set (0.01 sec) mysql> SHOW STATUS LIKE 'last_query_cost'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | Last_query_cost | 232.459000 | +-----------------+------------+ 1 row in set (0.00 sec)
③我们再次扩大范围，假若我们想要查询 student_id > 199000的学生记录呢？运行时间 0.02s，这时我们大概需要进行 2279个页的查询
mysql> SELECT * FROM student_info WHERE student_id > 199000; //... 5065 rows in set (0.02 sec) mysql> SHOW STATUS LIKE 'last_query_cost'; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 2279.509000 | +-----------------+-------------+ 1 row in set (0.00 sec)
不知道大家有没有发现，上面的查询页的数量是刚才的 10倍，但是查询的效率并没有明显的变化，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。
**使用场景：**查询 last_query_cost 对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候
🎈 SQL查询是一个动态的过程，从页加载的角度，我们可以得到以下两点结论：
位置决定效率：如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。即 数据库缓冲池>内存>磁盘
批量决定效率：如果我们从磁盘中单一页进行随机读，那么效率是很低的（差不多10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。即顺序读取>大于随机读取
所以说，遇到 I/O 并不用担心，方法找对了，效率还是很高的。我们首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次我们可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。
注：缓冲池和查询缓存并不是一个东西
4. 定位执行慢的 SQL：慢查询日志
4.1 开启慢查询日志
1. 开启 slow_query_log
查看慢查询日志是否开启，以及日志的位置
mysql> show variables like '%slow_query_log%'; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/hadoop102-slow.log | +---------------------+-----------------------------------+ 2 rows in set (0.03 sec)
修改慢查询日志状态为开启，注意这里要加 global，因为它是全局系统变量，否则会报错。
mysql> set global slow_query_log='ON'; Query OK, 0 rows affected (0.02 sec)
再查看
mysql> show variables like '%slow_query_log%'; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/hadoop102-slow.log | +---------------------+-----------------------------------+ 2 rows in set (0.01 sec)
2. 修改long_query_time阈值
接下来我们来看下慢查询的时间阈值设置，使用如下命令
mysql> show variables like '%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
这里如果我们想把时间缩短，比如设置成1秒，可以这样设置：
# 测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效，所以可以一并执行下列语句 mysql> set global long_query_time = 1; Query OK, 0 rows affected (0.00 sec) mysql> set long_query_time = 1; Query OK, 0 rows affected (0.00 sec)
再查看
mysql> show global variables like '%long_query_time%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set, 1 warning (0.00 sec)
4.2 案例演示
步骤一、建表
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;
步骤二、 设置参数 log_bin_trust_function_creators
创建函数，假如报错
This function has none of DETERMINISTIC......
命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效
步骤三、创建函数
随机产生字符串：(同上一章)
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);
产生随机数值：（同上一章）
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);
步骤四、创建存储过程
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号开始 mysql> CALL insert_stu1(100001,4000000); Query OK, 0 rows affected (10 min 47.03 sec)
注意，这个时间会比较长，请耐心等待几分钟哟。结束后可以查询下是不是插入成功了。
mysql> select count(*) from student; +----------+ | count(*) | +----------+ | 4000000 | +----------+ 1 row in set (0.82 sec)
4.3 测试及说明
1. 执行一下下面的查询操作，进行慢查询语句的测试
# 注意：此时long_query_time已经设置为1了哦~ mysql> SELECT * FROM student WHERE stuno = 3455655; +---------+---------+--------+------+---------+ | id | stuno | name | age | classId | +---------+---------+--------+------+---------+ | 3355654 | 3455655 | ZfCwDz | 76 | 228 | +---------+---------+--------+------+---------+ 1 row in set (1.03 sec) mysql> SELECT * FROM student WHERE name = 'ZfCwDz'; +---------+---------+--------+------+---------+ | id | stuno | name | age | classId | +---------+---------+--------+------+---------+ | 32843 | 132844 | zfcWDZ | 32 | 304 | | 889126 | 989127 | ZfCwDz | 77 | 249 | | 2015535 | 2115536 | zfcWDZ | 36 | 459 | | 3176527 | 3276528 | ZFcwdZ | 81 | 941 | | 3355654 | 3455655 | ZfCwDz | 76 | 228 | +---------+---------+--------+------+---------+ 5 rows in set (1.09 sec)
从上面的结果可以看出来，查询学生编号合和姓名花费时间 都在1s以上。已经达到了秒的数量级，说明目前查询效率是非常低的，下面我们分析一下原因
2. 先查看下慢查询的记录
mysql> show status like 'slow_queries'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 2 | +---------------+-------+ 1 row in set (0.01 sec)
🎯补充说明：
在Mysql中，除了上述变量，控制慢查询日志的还有另外一个变量 min_examined_row_limit 。这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过 long_query_time 的值，那么这个查询就被记录到慢查询日志中。反之，则不被记录到慢查询日志中。另外，min_examined_row_limit 默认是 0，我们也一般不会去修改它。
mysql> SHOW VARIABLES like 'min%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | min_examined_row_limit | 0 | +------------------------+-------+ 1 row in set (0.02 sec)
当这个值为默认值0时，与 long_query_time=10合在一起，表示只要查询的执行时间超过10秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。你也可以根据需要，通过修改"my.ini"文件，来修改查询时长，或者通过SET指令，用SQL语句修改min_examined_row_limit 的值。
4.4 慢查询日志分析工具：Mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析 SQL，显然是个体力活，MySQL 提供了日志分析工具 mysqldumpslow。
📑 注意:
1.该工具并不是 MySQL 内置的，不要在 MySQL 下执行，可以直接在根目录或者其他位置执行
2.该工具只有 Linux 下才是开箱可用的，实际上生产中mysql数据库一般也是部署在linux环境中的。如果您是windows环境下，可以参考博客https://www.cnblogs.com/-mrl/p/15770811.html。
通过 mysqldumpslow 可以查看慢查询日志帮助
mysqldumpslow --help
mysqldumpslow 命令的具体参数如下：
-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间 （默认方式）
ac:平均查询次数
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；
接下来我们可以找到慢查询日志的位置
举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：
[root@hadoop102 mysql]# mysqldumpslow -s t -t 5 /var/lib/mysql/hadoop102-slow.log Reading mysql slow query log from /var/lib/mysql/hadoop102-slow.log Count: 1 Time=283.29s (283s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@hadoop102 CALL insert_stu1(N,N) Count: 1 Time=1.09s (1s) Lock=0.00s (0s) Rows=5.0 (5), root[root]@localhost SELECT * FROM student WHERE name = 'S' Count: 1 Time=1.03s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost SELECT * FROM student WHERE stuno = N Died at /usr/bin/mysqldumpslow line 162, <> chunk 3.
可以看到上面 sql 中具体的数值类都被N代替，字符串都被使用 S 代替，如果想要显示真实的数据，可以加上参数 -a
[root@hadoop102 mysql]# mysqldumpslow -a -s t -t 5 /var/lib/mysql/hadoop102-slow.log Reading mysql slow query log from /var/lib/mysql/hadoop102-slow.log Count: 1 Time=283.29s (283s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@hadoop102 CALL insert_stu1(100001,4000000) Count: 1 Time=1.09s (1s) Lock=0.00s (0s) Rows=5.0 (5), root[root]@localhost SELECT * FROM student WHERE name = 'ZfCwDz' Count: 1 Time=1.03s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost SELECT * FROM student WHERE stuno = 3455655 Died at /usr/bin/mysqldumpslow line 162, <> chunk 3.
最后罗列下工作中常用的一些查询：
#得到返回记录集最多的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
4.5 关闭慢查询日志
MySQL 服务器停止慢查询日志功能有两种方法：
方式一：永久性方式
修改my.cnf或my.ini文件，把【mysqld】组下的slow_query_log值设置为OFF，修改保存后，再重启MySQL服务，即可生效。
#配置文件 [mysqld] slow_query_log=OFF
或者，把slow_query_log一项注释掉 或 删除
[mysqld] #slow_query_log =OFF
重启MySQL服务，执行如下语句查询慢日志功能。
SHOW VARIABLES LIKE '%slow%'; #查询慢查询日志所在目录 SHOW VARIABLES LIKE '%long_query_time%'; #查询超时时长
可以看到，MySQL系统中的慢查询日志是关闭的。
方式二：临时性方式
使用 SET 语句来设置。
（1）停止 MySQL 慢查询日志功能，具体 SQL 语句如下。
SET GLOBAL slow_query_log=off;
2）重启MySQL服务，使用 SHOW 语句查询慢查询日志功能信息，具体演示如下
[root@hadoop102 mysql]# systemctl restart mysqld; [root@hadoop102 mysql]# mysql -hlocalhost -P3306 -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.25 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. mysql> SHOW VARIABLES LIKE '%slow%'; +---------------------------+-----------------------------------+ | Variable_name | Value | +---------------------------+-----------------------------------+ | log_slow_admin_statements | OFF | | log_slow_extra | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | OFF #慢查询日志已关闭 | | slow_query_log_file | /var/lib/mysql/hadoop102-slow.log | +---------------------------+-----------------------------------+ 6 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE '%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | #已恢复至默认的 10s +-----------------+-----------+ 1 row in set (0.01 sec)
4.6 删除与恢复慢查询日志
使用SHOW语句显示慢查询日志信息，具体SQL语句如下：
mysql> SHOW VARIABLES LIKE '%slow_query_log%'; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/hadoop102-slow.log | +---------------------+-----------------------------------+ 2 rows in set (0.00 sec)
调优结束可以及时删除慢查询日志节省磁盘空间哟，当然手工删除也是可以的
如果误删了，而且还没有了备份，可以使用下面的命令来重新恢复生成哟，执行完毕后会在数据目录下重新生成查询日志文件
#先要打开慢查询日志 SET GLOBAL slow_query_log=ON; #恢复慢查询日志 mysqladmin -uroot -p flush-logs slow
提示
慢查询日志都是使用mysqladmin -uroot -p flush-logs slow 命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在于新的日志文件中，如果需要旧的查询日志，就必须事先备份。
