-
开启慢查询日志记录
在MySQL中,慢查询日志的记录需要通过手动开启,可以在控制台通过set命令来设置,如下
#开启慢查询日志 set global slow_query_log='ON'; #设置慢查询日志存放的位置 set global slow_query_log_file='/user/local/mysql/data/slow.log'; //linix set global slow_query_log_file='D:\\mysql\data\show.log'; //windows #设置慢查询语句时间(当查询语句执行时间超过指定时间(等于不会记录),就会被记录,单位:s) #从5.1版本之后,该时间设置可以精确到毫秒 set global long_query_time=1;
通过控制台设置后,如果服务重启就会导致之前的设置失效,我们可以通过MySQL的配置文件进 行配置,如下所示
[mysqld] #开启慢查询日志 slow_query_log = ON #指定慢查询日志的文件路径 slow_query_log_file = E:\mysql\mysql-5.7.30-winx64\data\WIN-RLSMADHPTDP-slow.log #也可以通过参数log_output指定日志输出形式为表slow_log #log_output= TABLE long_query_time = 2
在MySQL实例的mysql数据库中,存在一张slow_log表,可以用用作记录慢查询日志,相比于文件日志,表记录的查询时间只能精确到秒级
-
慢查询参数其他参数配置
如果SQL语句没有使用索引查询,则可以通过设置log_queries_not_using_indexes将该查询日志记录到慢查询日志里面进行分析,开启SQL语句的索引检验#控制台设置 set global log_queries_not_using_indexes='ON'; #配置文件设置 [mysqld] log_queries_not_using_indexes=ON
从MySQL5.6.5版本开始,新增了一个参数log_throttle_queries_not_using_indexes,表示每分钟允许记录到slow log的且未使用索引的SQL语句次数。该值默认为0,表示没有限制,为了防止慢查询日志过多的记录导致文件过于庞大,可以根据需求手动设置
#控制台设置 set global log_throttle_queries_not_using_indexes=10; #配置文件设置 [mysqld] log_throttle_queries_not_using_indexes=10;
可以通过mysqldumpslow命令对慢查询的日志文件进行分析,例如用户希望的到执行时间最长的10条SQL语句,运行命令如下:
mysqldumpslow -s -al -n 10 slow_log
-
slow_log表
在数据库实例下的mysql库中,可以查看到slow_log表的模型,show create table slow_logCREATE 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'
从表模型中可以发现,slow_log的存储引擎用的是CSV,对大数据量下的查询效率可能不高,可以将slow_log表的引擎转换为MyISAM,并根据需求在指定的字段加上索引以进一步提高查询效率。需要注意的是,将slow_log表的存储引擎更改为MyISAM后,还是会对数据库造成额外的开销
-
slow log对SQL语句的捕获
InnoSQL版本加强了对SQL语句的捕获方式。在原版MySQL的基础上在slow log中添加了逻辑读取(logical reads)和物理读取(physical reads)。物理读取是指从磁盘进行IO读取的次数,逻辑读取包含所有的读取,不管是磁盘还是缓冲池。
从上面的例子中可看出该子查询的的逻辑读次数为91584次,而物理读取次数为19次。从逻辑读与物理读的比例上来看,该SQL语句可进行优化。
在《MySQL技术内部 InnoDB存储引擎》一书中,还介绍了long_query_io和slow_quert_type参数,用户可以通过额外的参数long_query_io将超过指定逻辑IO次数的SQL语句记录到slow log中。该值默认为100,即表示对于逻辑读取次数大于100的SQL语句,记录到slow log中,为了兼容原MySQL数据库的运行方式,还添加了参数slow_quert_type,用来表示启用slow log的方式,可选值
0 表示不将SQL语句记录到slow log
1 表示根据运行时间将SQL语句记录到slow log
2 表示根据逻辑IO次数将SQL语句记录到slow log
3 表示根据运行时间及逻辑IO次数将SQL语句记录到slow log
注:关于long_query_io和slow_query_type这两个参数,在MySQL的官方文档中,均为找到
慢查询日志文件
最新推荐文章于 2024-03-05 11:35:42 发布