慢查询日志文件

1. 开启慢查询日志记录

   在MySQL中，慢查询日志的记录需要通过手动开启，可以在控制台通过set命令来设置，如下

   #开启慢查询日志
   set global slow_query_log='ON';
   
   #设置慢查询日志存放的位置
   set global slow_query_log_file='/user/local/mysql/data/slow.log';         //linux
   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表，可以用用作记录慢查询日志，相比于文件日志，表记录的查询时间只能精确到秒级。

   开启慢查询功能后，会根据配置产生慢查询日志，一条慢查询日志的数据组成如下：

   “Time: 2021-04-05T07:50:53.243703Z”：查询执行时间
   “User@Host: root[root] @ localhost []  Id:     3”：用户名 、用户的IP信息、线程ID号
   “Query_time: 0.000495”：执行花费的时长【单位：秒】
   “Lock_time: 0.000170”：执行获得锁的时长
   “Rows_sent”：获得的结果行数
   “Rows_examined”：扫描的数据行数
   “SET timestamp”：这SQL执行的具体时间
   最后一行：执行的SQL语句
   

2. 慢查询参数其他参数配置
   如果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;
   

   MySQL提供了慢查询日志分析工具mysqldumpslow，汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。

   例如用户希望的到执行时间最长的10条SQL语句，运行命令如下：

   mysqldumpslow -s r -t 10 slow-mysql.log
   

   相关参数说明：

   -s order (c,t,l,r,at,al,ar) 
            c:总次数
            t:总时间
            l:锁的时间
            r:获得的结果行数
            at,al,ar :指t,l,r平均数  【例如：at = 总时间/总次数】
   -s 对结果进行排序，怎么排，根据后面所带的 (c,t,l,r,at,al,ar)，缺省为at
   -t  NUM       just show the top n queries：仅显示前n条查询
   -g PATTERN   grep: only consider stmts that include this string：通过grep来筛选语句。
   

3. slow_log表
   在数据库实例下的mysql库中，可以查看到slow_log表的模型，show 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'
   

   从表模型中可以发现，slow_log的存储引擎用的是CSV，对大数据量下的查询效率可能不高，可以将slow_log表的引擎转换为MyISAM，并根据需求在指定的字段加上索引以进一步提高查询效率。需要注意的是，将slow_log表的存储引擎更改为MyISAM后，还是会对数据库造成额外的开销

4. 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的官方文档中，均未找到