目录
什么是二进制日志
1)关于二进制日志的详情都记录在下面链接中
~~~~> 二进制日志详情
什么是慢查询日志
前言
MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。
开启慢查询日志
1)下载数据库
[root@hya ~]# yum -y install mariadb mariadb-server
2)配置文件开启慢查询
[root@hya ~]# vim /etc/my.cnf
slow_query_log=1 #开启慢查询
long_query_time=1 #设置时间
slow_query_log_file=/var/log/mariadb/mysql-slow.log #设置日志存放路径
######################################第二种方法 临时开启
MariaDB [(none)]> set global slow_query_log='ON';
MariaDB [(none)]> set long_query_time=1;
MariaDB [(none)]> set slow_query_log_file='/var/log/mariadb/mysql-slow.log';
Mysql慢查询的相关参数解释
1)参数解释
slow_query_log :是否开启慢查询日志,1表示开启,0表示关闭。
log-slow-queries :旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
long_query_time :慢查询阈值,当查询时间多于设定的阈值时,记录日志。
log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。
log_output:日志存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'。log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql
2)查询详情
MariaDB [(none)]> show variables like 'slow_query%'; #是否开启慢查询
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/log/mariadb/mysql-slow.log |
+---------------------+---------------------------------+
MariaDB [(none)]> show variables like 'long_query_time'; #查看查询时间
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
MariaDB [(none)]> show variables like 'log_queries_not_using_indexes'; #查看未使用索引的
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> set global log_queries_not_using_indexes=1; #设置未使用索引查询的也被记录到慢查询日志中
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show global status like '%slow_queries%'; #查询慢查询记录
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 1 |
+---------------+-------+
1 row in set (0.00 sec)
3)查看数据库最大连接数和设置
MariaDB [(none)]> show variables like '%max_connections%'; #上限连接数
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| extra_max_connections | 1 |
| max_connections | 151 |
+-----------------------+-------+
2 rows in set (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Max_used_connections'; #服务器响应最大连接数
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 1 |
+----------------------+-------+
1 row in set (0.00 sec)
比较理想的设置:Max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85%左右,如果发现比例在10%以下,MySQL服务器连接数上限设置的过高了。
#############################################################################
MariaDB [(none)]> set GLOBAL max_connections = 500; #修改最大连接数
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL STATUS; #列出MySQL服务器运行各种状态值
Mysql慢查询分析工具
慢查询的日志记录非常多,要从里面找寻一条查询慢的日志并不是很容易的事情,一般来说都需要一些工具辅助才能快速定位到需要优化的SQL语句,下面介绍mysqldumpslow慢查询辅助工具:
bin目录下的工具,汇总除查询条件外其他完全相同的SQL,并将分析结果按照参数中所指定的顺序输出。
1)分析慢查询日志
[root@hya ~]# mysqldumpslow -s r -t 10 /var/log/mariadb/mysql-slow.log
Reading mysql slow query log from /var/log/mariadb/mysql-slow.log
Count: 1 Time=10.00s (10s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=0.0 (0), root[root]@localhost
select sleep(N)
Died at /usr/bin/mysqldumpslow line 178, <> chunk 1.
#########################################################################################
-s order (c,t,l,r,at,al,ar)
c:总次数
t:总时间
l:锁的时间
r:总数据行
at,al,ar :t,l,r平均数 【例如:at = 总时间/总次数】
-t 指定取前面几天作为结果输出
常见的慢查询优化
1、索引没起到作用的情况
- 使用LIKE关键字的查询语句 在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用。只有“%”不在第一个位置索引才会起作用。
- 使用多列索引的查询语句 MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引,只有查询条件使用了这些字段中的第一个字段时,索引才会被使用。
2、优化数据库结构
- 将字段很多的表拆解成多个表
- 设置中间表
3、分解关联查询
很多高性能的应用都会对关联查询进行分解,就是可以对每一个表进行一次单表查询,然后将查询结果在应用程序中进行关联,很多场景下这样会更高效。
4、优化limit分页
在系统中需要分页的操作通常会使用limit加上偏移量的方法实现,同时加上合适的order by 子句。如果有对应的索引,通常效率会不错,否则MySQL需要做大量的文件排序操作。
一个非常令人头疼问题就是当偏移量非常大的时候,例如可能是limit 10000,20这样的查询,这是mysql需要查询10020条然后只返回最后20条,前面的10000条记录都将被舍弃,这样的代价很高。
优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。
总结
慢查询日志默认是不开启的,也就是说一般人没玩过这功能。如果你需要优化SQL语句,就可以开启这个功能,它可以让你很容易地知道哪些语句是需要优化的,至于说慢查询的优化我实际也进行优化过,我也是恰好被问到所以就把这一块跟整理出来,以便我回过头来在看。