玖富娱乐平台全网唯一指定1956注册开户网站

MySQL慢查询日记的运用_玖富娱乐主管发布

日期:2019-01-10 浏览:
玖富娱乐是一家为代理招商,直属主管信息发布为主的资讯网站,同时也兼顾玖富娱乐代理注册登录地址。

一. 设置要领

运用慢查询日记里捕捉

启用之前须要先举行一些设置

要领一:全局变量设置

设置慢查询日记的日记文件地位

set global slow_query_log_file = "D:/slow_log/slow_log.log" ;

设置是不是对未运用索引的SQL举行纪录

set global log_queries_not_using_indexes = on;

设置只需SQL实行时候凌驾n秒的就纪录

set global long_query_time = 0.001 ;

此处设置的0.001秒,便于测试,一样平常状况比这个大

-玖富娱乐是一家为代理招商,直属主管信息发布为主的资讯网站,同时也兼顾玖富娱乐代理注册登录地址。-

启用mysql慢查询日记

set global slow_query_log = on;

要领二:配置文件设置

修正配置文件my.cnf,在[mysqld]下的下方到场

[mysqld]
slow_query_log = ON
log_queries_not_using_indexes = ON;
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

检察设置后的参数

show variables like 'slow_query%';
show variables like 'long_query__time';

二. 慢查询日记纪录的内容

Time                 Id Command    Argument
# Time: 2019-01-08T04:12:09.269315Z 
# User@Host: h5_test[h5_test] @ localhost [::1]  Id:    12  
# Query_time: 0.000831  Lock_time: 0.000198 Rows_sent: 1  Rows_examined: 3  
use mc_productdb;
SET timestamp=1546920729;
SELECT t.customer_id,t.title,t.content 
FROM (
SELECT customer_id  FROM product_comment WHERE  product_id =199726 AND audit_status = 1 LIMIT 0,15
)a JOIN product_comment t 
ON a.customer_id = t.comment_id;

Time:实行查询的日期时候
User@Host:实行查询的用户和客户端IP
Id:是实行查询的线程Id
Query_time:SQL实行所斲丧的时候
Lock_time:实行查询对纪录锁定的时候
Rows_sent:查询返回的行数
Rows_examined:为了返回查询的数据所读取的行数

三. 怎样剖析慢查询日记

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

因为慢查询日记中会含有大批的反复的SQL,为了轻易,能够经由过程mysql供应的敕令行对象 mysqldumpslow 来剖析日记

$ mysqldumpslow.pl  slow_log.log

Reading mysql slow query log from slow_log.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  C:Program FilesMySQLMySQL Server N.Nbinmysqld.exe, Version: N.N.N-log (MySQL Community Server (GPL)). started with:
  TCP Port: N, Named Pipe: MySQL
  # Time: N-N-08T04:N:N.269315Z
  # User@Host: h5_test[h5_test] @ localhost [::N]  Id:    N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  use mc_productdb;
  SET timestamp=N;
  SELECT t.customer_id,t.title,t.content
  FROM (
  SELECT customer_id  FROM product_comment WHERE  product_id =N AND audit_status = N LIMIT N,N
  )a JOIN product_comment t
  ON a.customer_id = t.comment_id

与慢查询日记中纪录的数据是类似的,只是多出了一行Count,这一行纪录的是这条SQL在纪录慢查询日记时期的实行次数,若是一个SQL屡次被实行,用这个敕令剖析时,只会涌现一个SQL日记,Count里的数值代表实行次数,其他数字为了兼并透露表现用N替代

-玖富娱乐是一家为代理招商,直属主管信息发布为主的资讯网站,同时也兼顾玖富娱乐代理注册登录地址。


平台新闻

联系方式丨CONTACT

  • 全国热线:7711177
  • 传真热线:010-88888888
  • Q Q咨询:7711177
  • 企业邮箱:
首页
电话
短信