2023年7月23日发(作者：)

mysql慢⽇志flush_time_mysql中的慢查询⽇志⾸先我们看⼀下关于mysql中的⽇志，主要包含：错误⽇志、查询⽇志、慢查询⽇志、事务⽇志、⼆进制⽇志；⽇志是mysql数据库的重要组成部分。⽇志⽂件中记录着mysql数据库运⾏期间发⽣的变化；也就是说⽤来记录mysql数据库的客 户端连接状况、SQL语句的执⾏情况和错误信息等。当数据库遭到意外的损坏时，可以通过⽇志查看⽂件出错的原因，并且可以通过⽇志⽂件进⾏数据恢复。先看⼀下⽇志参数mysql> show variables like '%log%';+-----------------------------------------+---------------------------------------+| Variable_name | Value |+-----------------------------------------+---------------------------------------+| back_log | 80 || binlog_cache_size | 32768 || binlog_checksum | CRC32 || binlog_direct_non_transactional_updates | OFF || binlog_error_action | IGNORE_ERROR || binlog_format | STATEMENT || binlog_gtid_simple_recovery | OFF || binlog_max_flush_queue_time | 0 || binlog_order_commits | ON || binlog_row_image | FULL || binlog_rows_query_log_events | OFF || binlog_stmt_cache_size | 32768 || binlogging_impossible_mode | IGNORE_ERROR || expire_logs_days | 0 || general_log | OFF || general_log_file | /data/mysql/ || innodb_api_enable_binlog | OFF || innodb_flush_log_at_timeout | 1 || innodb_flush_log_at_trx_commit | 1 || innodb_locks_unsafe_for_binlog | OFF || innodb_log_buffer_size | 8388608 || innodb_log_compressed_pages | ON || innodb_log_file_size | 50331648 || innodb_log_files_in_group | 2 || innodb_log_group_home_dir | ./ || innodb_mirrored_log_groups | 1 || innodb_online_alter_log_max_size | 134217728 || innodb_undo_logs | 128 || log_bin | OFF || log_bin_basename | || log_bin_index | || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || log_error | /data/mysql/ || log_output | FILE || log_queries_not_using_indexes | OFF || log_slave_updates | OFF || log_slow_admin_statements | OFF || log_slow_slave_statements | OFF || log_throttle_queries_not_using_indexes | 0 || log_warnings | 1 || max_binlog_cache_size | 18446744 || max_binlog_size | 1073741824 || max_binlog_stmt_cache_size | 18446744 || max_relay_log_size | 0 || relay_log | || relay_log_basename | || relay_log_index | || relay_log_info_file | || relay_log_info_repository | FILE || relay_log_purge | ON || relay_log_recovery | OFF || relay_log_space_limit | 0 || simplified_binlog_gtid_recovery | OFF || slow_query_log | OFF || slow_query_log_file | /data/mysql/ || sql_log_bin | ON || sql_log_off | OFF || sync_binlog | 0 || sync_relay_log | 10000 || sync_relay_log_info | 10000 |+-----------------------------------------+---------------------------------------+61 rows in set (0.03 sec)看⼀下slow参数mysql> show variables like '%slow%';+---------------------------+--------------------------------+| Variable_name | Value |+---------------------------+--------------------------------+| log_slow_admin_statements | OFF || log_slow_slave_statements | OFF || slow_launch_time | 2 || slow_query_log | OFF || slow_query_log_file | /data/mysql/ |+---------------------------+--------------------------------+5 rows in set (0.00 sec)global slowmysql> show global status like '%slow%';+---------------------+-------+| Variable_name | Value |+---------------------+-------+| Slow_launch_threads | 0 || Slow_queries | 0 |+---------------------+-------+⼀、关于⼀些参数的解释1、slow_launch_time：如果创建线程的时间超过该秒数，服务器增加Slow_launch_threads状态变量。2、slow_query_log：是否记录慢查询。⽤long_query_time变量的值来确定“慢查询”。3、slow_query_log_file：慢⽇志⽂件路径4、long_query_time：慢⽇志执⾏时长(秒)，超过设定的时间才会记⽇志5、log-slow-admin-statements：⼀些管理指令，也会被记录。⽐如OPTIMEZE TABLE, ALTER TABLE等等6、log-queries-not-using-indexes：MySQL会将没有使⽤索引的查询记录到slow query⽇志中。⽆论它执⾏有多快，查询语句没有使⽤索引，都会被记录。有的时候，有些没有使⽤引索的查询⾮常快(例如扫描很⼩的表)，但也有可能导致服务器变慢，甚⾄还会使⽤⼤量的磁盘空间。⼆、配置#将下列配置放到中[mysqld]slow_query_log_file = /var/lib/mysql/_slow_queries = ONlong_query_time = 1log-queries-not-using-indexeslog-slow-admin-statements我们也可以登录之后进⾏配置set global slow_query_log=on;set global slow_query_log_file = /var/lib/mysql/ ;set global long_query_time=1;set gloabl log_slow_admin_statements=on;set global log_queries_not_using_indexes = on;查看⽇志[admin@dev config]$ sudo tail -f /var/lib/mysql/ timestamp=1437572901;show warnings;# Time: 150722 21:48:49# User@Host: root[root] @ localhost []# Query_time: 0.021936 Lock_time: 0.000121 Rows_sent: 1 Rows_examined: 5294SET timestamp=1437572929;select sql_no_cache id,bid,pic,cateid,title,tag,indextitle,dateline,summary from acc_info_article where status=1 andfind_in_set("84",bid) order by sort asc limit 1;/usr/libexec/mysqld, Version: 5.5.29-log (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /var/lib/mysql/说明第⼀⾏：执⾏时间第⼆⾏：执⾏⽤户第三⾏(重要)Query_time SQL执⾏的时间,越长则越慢Lock_time 在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间Rows_sent 查询返回的⾏数Rows_examined 查询检查的⾏数1、⽇志不能说明⼀切问题，可能跟锁表、系统繁忙的偶发性有关，a、锁表，导致查询处于等态状态，lock_time显⽰了查询等待锁被翻译的时间b、数据或索引没有被缓存,常见于第⼀次启动服务器或者服务器没有调优c、备份数据库，I/O变慢，d、同时运⾏了其它的查询，减少了当前查询，e、当然，如果某条SQL语句经常查询慢那基本可以判断是可以再次优化的2、不要开启log-queries-not-using-indexes(没有索引查询记录功能)a、这个功能实际⽤处不⼤，就是记录SQL查询的时候，没有索引的通通记录b、虽然索引对查询的速度有影响，但要看数据量⼤⼩c、因为开启了这个功能以后，select * from blog这样的查询也会被记录在⽇志中，很快⽇志⽂件就会被垃圾信息给充满，从⽽影响主要的查询慢⽇志记录的查看三、⽇志分析、⼯欲善其事必先利其器主要推荐两种⽅式1、mysqldumpslow[root@mysql_data]#mysqldumpslow ding mysql slow query log from nt: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysqlselect count(N) from t_user;mysqldumpslow -s c -t 10 /database/mysql/这会输出记录次数最多的10条SQL语句，其中：-s, 是表⽰按照何种⽅式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表⽰相应的倒叙；-t, 是top n的意思，即为返回前⾯多少条的数据；-g, 后边可以写⼀个正则匹配模式，⼤⼩写不敏感的；mysqldumpslow -s r -t 10 /database/mysql/slow-log得到返回记录集最多的10个查询。mysqldumpslow -s t -t 10 -g "left join" /database/mysql/slow-log得到按照时间排序的前10条⾥⾯含有左连接的查询语句。使⽤mysqldumpslow命令可以⾮常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化⾮常重要的⼀步。开启慢查询⽇志后，由于⽇志记录操作，在⼀定程度上会占⽤CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。2、pt-query-digest下载安装[root@localhost ~]# chmod u+x pt-query-digest参数解释--create-review-table 当使⽤--review参数把分析结果输出到表中时，如果没有表就⾃动创建。--create-history-table 当使⽤--history参数把分析结果输出到表中时，如果没有表就⾃动创建。--filter 对输⼊的慢查询按指定的字符串进⾏匹配过滤后再进⾏分析--limit限制输出结果百分⽐或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占⽐从⼤到⼩排序，输出到总和达到50%位置截⽌。--host mysql服务器地址--user mysql⽤户名--password mysql⽤户密码--history 将分析结果保存到表中，分析结果⽐较详细，下次再使⽤--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同⼀CHECKSUM来⽐较某类型查询的历史变化。--review 将分析结果保存到表中，这个分析只是对查询条件进⾏参数化，⼀个类型的查询⼀条记录，⽐较简单。当下次使⽤--review时，如果存在相同的语句分析，就不会记录到数据表中。--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，⼀般使⽤report，以便于阅读。--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的⼀个时间值：s(秒)、h(⼩时)、m(分钟)、d(天)，如12h就表⽰从12⼩时前开始统计。--until 截⽌时间，配合—since可以分析⼀段时间内的慢查询。⽤法(1)直接分析慢查询⽂件:pt-query-digest > slow_(2)分析最近12⼩时内的查询：pt-query-digest --since=12h > slow_(3)分析指定时间范围内的查询：pt-query-digest --since '2014-04-17 09:30:00' --until '2014-04-17 10:00:00'> > slow_(4)分析指含有select语句的慢查询pt-query-digest--filter '$event->{fingerprint} =~ m/^select/i' > slow_(5) 针对某个⽤户的慢查询pt-query-digest--filter '($event->{user} || "") =~ m/^root/i' > slow_(6) 查询所有所有的全表扫描或full join的慢查询pt-query-digest--filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' > slow_(7)把查询保存到query_review表pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review--create-review-table (8)把查询保存到query_history表pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_ _20140401pt-query-digest --user=root –password=abc123--review h=localhost,D=test,t=query__20140402(9)通过tcpdump抓取mysql的tcp协议数据，然后再分析tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > -query-digest --type tcpdump > slow_(10)分析binlogmysqlbinlog mysql-bin.000093 > -query-digest --type=binlog > slow_(11)分析general logpt-query-digest --type=genlog > slow_