课程 › 第 10 章 › 实验四
第 10 章 · 实验四 · 在第六节之后

慢查询日志与通用查询日志

打开慢查询日志和通用查询日志，让日志同时写到表中，在 Workbench 中查看记下来的语句，最后恢复原来的设置。

环　境

MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。

执　行

Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。

注　意

输入法切到英文。表名、字段名不加引号，字符串值才用单引号。

本次实验的顺序　后一个实验用到前一个的结果

  1. 实验 4-1查看两种日志的设置
  2. 实验 4-2记录慢查询
  3. 实验 4-3记录全部语句
  4. 实验 4-4换新日志并恢复设置
第一步

数据准备（必做）

与实验一相同的脚本，执行后 teaching 库回到初始状态。

整段复制，全部执行下载 .sql
-- 第 10 章实验数据：重建教材 teaching 库，删除本章实验建的库
-- 在 Workbench 中用 root 连接整段执行。四次实验共用，想从头再做时重新执行即可。
DROP DATABASE IF EXISTS teaching_copy;
DROP DATABASE IF EXISTS teaching_empty;
DROP DATABASE IF EXISTS teaching;
CREATE DATABASE teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 课程表
CREATE TABLE c (
  cno CHAR(4) NOT NULL,
  cn VARCHAR(30) NOT NULL,
  ct INT,
  PRIMARY KEY (cno)
);
-- 学生表
CREATE TABLE s (
  sno CHAR(4) NOT NULL,
  sn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  maj VARCHAR(30),
  dept VARCHAR(30),
  PRIMARY KEY (sno)
);
-- 教师表
CREATE TABLE t (
  tno CHAR(4) NOT NULL,
  tn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  prof VARCHAR(20),
  maj VARCHAR(30),
  dept VARCHAR(30),
  sal DECIMAL(8,2),
  PRIMARY KEY (tno)
);
-- 选课表
CREATE TABLE sc (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  score DECIMAL(5,2),
  PRIMARY KEY (sno, cno),
  FOREIGN KEY (sno) REFERENCES s (sno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);
-- 授课表
CREATE TABLE tc (
  tno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  tcdate DATE,
  PRIMARY KEY (tno, cno),
  FOREIGN KEY (tno) REFERENCES t (tno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);

INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32),
('c7','高等数学',60), ('c8','控制理论',32);

INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s2','苏乐','男',20,'信息','信息学院'),
('s3','林欣怡','女',19,'信息','信息学院'),
('s4','陶然','男',19,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',18,'自动化','工学院');

INSERT INTO t VALUES
('t1','刘杨','男',38,'教授','计算机','信息学院',3800.00),
('t2','石丽','女',28,'讲师','计算机','信息学院',2800.00),
('t3','顾伟','男',35,'副教授','计算机','信息学院',3200.00),
('t4','赵礼','男',45,'教授','自动化','工学院',4267.90),
('t5','赵希希','女',33,'副教授','数学','理学院',3332.67),
('t6','张刚','男',30,'讲师','计算机','工学院',2600.00);

INSERT INTO sc VALUES
('s1','c1',90.50), ('s1','c2',88.00),
('s2','c4',70.00), ('s2','c5',57.00), ('s2','c6',81.50), ('s2','c7',NULL),
('s3','c1',75.00), ('s3','c2',76.50), ('s3','c4',85.00),
('s4','c1',93.00), ('s4','c2',82.00), ('s4','c6',NULL), ('s4','c7',68.00),
('s5','c2',79.00), ('s5','c7',84.00),
('s7','c3',77.00), ('s7','c5',62.00), ('s7','c7',100.00),
('s8','c2',66.00), ('s8','c3',96.00);

INSERT INTO tc VALUES
('t1','c1','2021-09-03'), ('t1','c2','2021-09-04'),
('t2','c5','2021-03-06'), ('t2','c6','2021-09-06'),
('t3','c2','2021-03-08'), ('t3','c4','2021-09-08'),
('t4','c3','2021-03-10'),
('t5','c7','2021-09-12'), ('t5','c8','2021-03-15');

SHOW TABLES;
4-1

查看两种日志的设置

讲义 · 慢查询日志讲义 · 通用查询日志
目的
查看慢查询日志和通用查询日志是否打开、文件放在哪里，以及慢查询的时间限制。
前提
已执行数据准备，当前库是 teaching。
  1. 查看慢查询日志的开关和文件名
  2. 查看慢查询的时间限制
  3. 查看通用查询日志的开关和文件名
  4. 查看日志的输出方式

参考答案

可以直接复制到 Workbench 执行
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'general%';
SHOW VARIABLES LIKE 'log_output';

语句解释

slow_query_log慢查询日志的开关。用 MySQL Installer 安装时默认勾选，所以可能已经是 ON
long_query_time执行时间超过它的查询才记录，默认 10 秒
general_log通用查询日志的开关，默认 OFF
log_output日志写到哪里，FILE 表示写到文件
执行后看：long_query_time 为 10，general_log 为 OFF，log_output 为 FILE。两个文件名分别是 主机名-slow.log 和 主机名.log。
4-2

记录慢查询

讲义 · 慢查询日志
目的
打开慢查询日志，把时间限制改为 1 秒，执行一条 2 秒的查询和一条很快的查询，查看哪一条被记录。
前提
已完成实验 4-1。
  1. 打开慢查询日志，时间限制改为 1 秒
  2. 让日志同时写到文件和表中
  3. 执行 SELECT SLEEP(2) 和一条普通查询
  4. 查询 mysql.slow_log 表

参考答案

可以直接复制到 Workbench 执行
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
-- 全局的设置对新建的连接有效，当前连接也要设置
SET SESSION long_query_time = 1;
-- 日志同时写到文件和表中
SET GLOBAL log_output = 'FILE,TABLE';
SELECT SLEEP(2);
SELECT COUNT(*) FROM s;
SELECT start_time, query_time,
  CONVERT(sql_text USING utf8mb4) AS sql_text
FROM mysql.slow_log;

语句解释

SET GLOBAL long_query_time修改全局的时间限制，当前连接仍用原来的 10 秒
SET SESSION long_query_time修改当前连接的时间限制
SLEEP(2)等待 2 秒再返回，用来模拟一条慢查询
mysql.slow_loglog_output 含 TABLE 时，慢查询写到这张表中
CONVERT(… USING utf8mb4)sql_text 是二进制类型，转成文字才能直接看到
执行后看：mysql.slow_log 中只有 SELECT SLEEP(2) 一行，query_time 约 2 秒。SELECT COUNT(*) 很快，没有记录。
4-3

记录全部语句

讲义 · 通用查询日志
目的
打开通用查询日志，执行一次查询和一次修改，查看日志记下了哪些语句。
前提
已完成实验 4-2，log_output 为 FILE,TABLE。
  1. 打开通用查询日志
  2. 查询 s1 的信息，再把 s1 的年龄改为 19
  3. 查看最近的 10 条通用日志

参考答案

可以直接复制到 Workbench 执行
SET GLOBAL general_log = ON;
SELECT * FROM s WHERE sno = 's1';
UPDATE s SET age = 19 WHERE sno = 's1';
SELECT event_time, command_type,
  CONVERT(argument USING utf8mb4) AS argument
FROM mysql.general_log
ORDER BY event_time DESC
LIMIT 10;

语句解释

general_log = ON打开通用查询日志，不用重启服务
mysql.general_log通用日志写到这张表中，argument 是执行的语句
command_typeQuery 表示执行语句，Connect 和 Quit 表示连接和断开
执行后看：SELECT 和 UPDATE 都有记录，还能看到 Workbench 自动执行的一些语句。二进制日志中只会有 UPDATE。
4-4

换新日志并恢复设置

讲义 · 通用查询日志
目的
用 FLUSH LOGS 重新打开全部日志，然后关闭通用查询日志，把设置改回原样。
前提
已完成实验 4-3。
  1. 执行 FLUSH LOGS，比较前后的二进制日志文件
  2. 关闭通用查询日志，时间限制改回 10 秒，输出方式改回 FILE
  3. 清空两张日志表

参考答案

可以直接复制到 Workbench 执行
SHOW BINARY LOGS;
-- 重新打开全部日志，二进制日志换一个新文件
FLUSH LOGS;
SHOW BINARY LOGS;
SET GLOBAL general_log = OFF;
SET GLOBAL long_query_time = 10;
SET GLOBAL log_output = 'FILE';
TRUNCATE TABLE mysql.general_log;
TRUNCATE TABLE mysql.slow_log;

语句解释

FLUSH LOGS关闭并重新打开四种日志。通用日志和慢查询日志接着写在原来的文件中
二进制日志FLUSH LOGS 时换一个新文件，编号加 1
TRUNCATE TABLE mysql.general_log清空日志表中的记录
SET GLOBAL只在服务运行期间有效，重启后按 my.ini 中的设置
执行后看：FLUSH LOGS 之后二进制日志多了一个文件。通用日志文件没有被清空，要换一个新文件，先把旧文件改名或删除，再执行 FLUSH LOGS。
保山学院·人工智能教研室·曹鼎鼎