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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
| 1>慢查询日志开启
//开启慢查询日志
mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slow_query%';
+
| Variable_name | Value |
+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/ubuntu-slow.log |
+
2>慢查询日志相关设置
(1) 设置sql语句超时时间
两点需要注意。首先，设置long_query_time这个阈值后，MySQL数据库会记录运行时间超过该值的所有SQL语句，但运行时间正好等于long_query_time的情况并不会被记录下。其次，从MySQL 5.1开始，long_query_time开始以微秒记录SQL语句运行的时间，之前仅用秒为单位记录。
//设置sql语句超时时间
mysql> show variables like 'long_query_time';
+
| Variable_name | Value |
+
| long_query_time | 10.000000 |
+
mysql> set session long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'long_query_time';
+
| Variable_name | Value |
+
| long_query_time | 1.000000 |
+
1 row in set (0.00 sec)
mysql> select @@global.long_query_time;
+
| @@global.long_query_time |
+
| 1.000000 |
+
1 row in set (0.00 sec)
mysql> select @@session.long_query_time;
+
| @@session.long_query_time |
+
| 1.000000 |
+
(2) 开启未使用索引查询的sql语句
//开启未使用索引查询的sql语句
mysql> set global log_queries_not_using_indexes=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_queries_not_using_indexes';
+
| Variable_name | Value |
+
| log_queries_not_using_indexes | ON |
+
//每分钟允许记录到slow log的且未使用索引的SQL语句次数:0 无限制
mysql> show variables like 'log_throttle_queries_not_using_indexes';
+
| Variable_name | Value |
+
| log_throttle_queries_not_using_indexes | 0 |
+
3>使用慢查询日志
(1)创建一个测试表
//选择测试库
mysql> show databases;
mysql> use learn;
//创建表
create table t (
id INT AUTO_INCREMENT ,
a INT,
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
//插入测试数据
INSERT INTO t (id,a) VALUES (1,1);
INSERT INTO t (id,a) VALUES (2,2);
//查看数据
mysql> select * from t;
+
| id | a |
+
| 1 | 1 |
| 2 | 2 |
+
(2)操作
mysql> select * from t ;
mysql> select * from t where b=2;
//不按索引查询的sql语句
root@ubuntu:/var/lib/mysql# mysqldumpslow ubuntu-slow.log
Reading mysql slow query log from ubuntu-slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=2.0 (2), root[root]@localhost
select * from t
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select * from t where a=N
//执行时间最长的10条SQL语句
root@ubuntu:/var/lib/mysql# mysqldumpslow -s al -n 10 ubuntu-slow.log
(3)查看mysql.slow_log 表信息
//mysql版本
mysql> select version();
+
| version() |
+
| 5.7.30-0ubuntu0.18.04.1 |
+
1 row in set (0.00 sec)
//mysql.slow_log 表信息
mysql> SHOW CREATE TABLE mysql.slow_log;
+
| Table | Create Table |
+
| slow_log | CREATE TABLE `slow_log` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumblob NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |
+
1 row in set (0.00 sec)
(4)将慢查询输出格式由文件转化为表
mysql> SHOW VARIABLES LIKE'log_output';
+
| Variable_name | Value |
+
| log_output | FILE |
+
1 row in set (0.00 sec)
mysql> SET GLOBAL log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE'log_output';
+
| Variable_name | Value |
+
| log_output | TABLE |
+
1 row in set (0.00 sec)
//测试
mysql> select sleep(10);
+
| sleep(10) |
+
| 0 |
+
1 row in set (10.00 sec)
mysql> SELECT * FROM mysql.slow_log;
+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+
| 2020-07-17 16:01:31.610090 | root[root] @ localhost [] | 00:00:10.001169 | 00:00:00.000000 | 1 | 0 | learn | 0 | 0 | 0 | select sleep(10) | 2 |
+
(5)修改mysql.slow_log 表存储引擎,提高在大数据下查询效率
//slow_log表使用的是CSV引擎，对大数据量下的查询效率可能不高。用户可以把slow_log表的引擎转换到MyISAM，并在start_time列上添加索引以进一步提高查询的效率
//不能忽视的是，将slow_log表的存储引擎更改为MyISAM后，还是会对数据库造成额外的开销。不过好在很多关于慢查询的参数都是动态的，用户可以方便地在线进行设置或修改。
mysql> SET GLOBAL slow_query_log=off;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE mysql.slow_log ENGINE=MyISAM;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE mysql.slow_log;
+
| Table | Create Table |
+
| slow_log | CREATE TABLE `slow_log` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumblob NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log' |
+
1 row in set (0.00 sec)
|