Mysql优化之慢查询

[mysqld]

# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# shared-memory

# shared-memory-base-name=MYSQL

# The Pipe the MySQL Server will use
# socket=MYSQL

# The TCP/IP Port the MySQL Server will listen on
port=3306

# Path to installation directory. All paths are usually resolved relative to this.
# basedir="C:/Program Files/MySQL/MySQL Server 5.6/"

# Path to the database root
datadir=C:/ProgramData/MySQL/MySQL Server 5.6/Data

# The default character set that will be used when a new schema or table is
# created and no character set is defined
# character-set-server=

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# General and Slow logging.
log-output=FILE
general-log=0
general_log_file="DESKTOP-MOP0GDT.log"
slow-query-log=1
slow_query_log_file="DESKTOP-MOP0GDT-slow.log"
long_query_time=0

set global slow_query_log = 1; --开启慢查询日志
set global long_query_time=0;
set global log_output='FILE,TABLE'

show VARIABLES like '%slow_query_log%'

show VARIABLES like '%slow_query_log_file%'

show VARIABLES like '%long_query_time%'

show VARIABLES like '%log_queries_not_using_indexes%'

show VARIABLES like 'log_output'

perl mysqldumpslow.pl -s r -t 5 d:\DESKTOP-MOP0GDT-slow.log

-s order (c,t,l,r,at,al,ar) 
         c:总次数
         t:总时间
         l:锁的时间
         r:总数据行
         at,al,ar  :t,l,r平均数  【例如：at = 总时间/总次数】

-t  top   指定取前面作为结果输出