精华内容
下载资源
问答
  • mysql 慢查询

    2018-10-21 15:58:56
    1. 慢查询 1 1.1. 什么是慢查询 1 1.2. 慢查询配置 1 1.2.1. 慢查询基本配置 1 1.2.2. 慢查询解读 2 1.3. 慢查询分析 3 1.3.1. Mysqldumpslow 3 1.3.2. pt_query_digest 4
  • 主要介绍了Mysql慢查询优化方法及优化原则,本文给大家介绍的非常详细,具有一定的参考借鉴价值 ,需要的朋友可以参考下
  • mysql 慢查询页面工具

    2018-05-17 09:57:51
    mysql 慢查询页面工具 页面化分析慢日志
  • MySQL慢查询日志是我们在日常工作中经常会遇到的一个功能,MySQL慢查询日志提供了超过指定时间阈值的查询信息,为性能优化提供了主要的参考依据,是一个非常实用的功能,MySQL慢查询日志的开启和配置非常简单,可以...
  • 主要介绍了MySQL慢查询日志分析的实例教程,通过设置参数从慢查询日志开始分析性能问题的原因,需要的朋友可以参考下
  • mysql慢查询

    万次阅读 多人点赞 2019-04-22 16:56:01
    MySQL慢查询,全名是慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阀值的语句。 具体环境中,运行时间超过long_query_time值的SQL语句,则会被记录到慢查询日志中。 long_query_time的...

    1 概念

    MySQL的慢查询,全名是慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阀值的语句。

    具体环境中,运行时间超过long_query_time值的SQL语句,则会被记录到慢查询日志中。

    long_query_time的默认值为10,意思是记录运行10秒以上的语句。

    默认情况下,MySQL数据库并不启动慢查询日志,需要手动来设置这个参数。

    当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。

    慢查询日志支持将日志记录写入文件和数据库表。

    官方文档,关于慢查询的日志介绍如下(部分资料,具体参考官方相关链接):

    2 参数

    MySQL 慢查询的相关参数解释:

    slow_query_log:是否开启慢查询日志,1表示开启,0表示关闭

    log-slow-queries :旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log

    slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log

    long_query_time:慢查询阈值,当查询时间多于设定的阈值时,记录日志。

    log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。

    log_output:日志存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'log_output='TABLE'表示将日志存入数据库。

    3 配置

    3.1 slow_query_log

    默认情况下slow_query_log的值为OFF,表示慢查询日志是禁用的,可以通过设置slow_query_log的值来开启,如下所示:

    mysql> show variables  like '%slow_query_log%';
     +---------------------+-----------------------------------------------+
     | Variable_name       | Value                                         |
     +---------------------+-----------------------------------------------+
     | slow_query_log      | OFF                                           |
     | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
     +---------------------+-----------------------------------------------+
     2 rows in set (0.00 sec)
    
    mysql> set global slow_query_log=1;
     Query OK, 0 rows affected (0.09 sec)

    使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效,MySQL重启后则会失效。

    如果要永久生效,就必须修改配置文件my.cnf(其它系统变量也是如此)。

    my.cnf要增加或修改参数slow_query_logslow_query_log_file,如下所示

    slow_query_log = 1
    slow_query_log_file = /tmp/mysql_slow.log

    然后重启MySQL服务器。

    3.2 slow_query_log_file

    这个参数用于指定慢查询日志的存放路径,缺省情况是host_name-slow.log文件,

    mysql> show variables like 'slow_query_log_file';
     +---------------------+-----------------------------------------------+
     | Variable_name       | Value                                         |
     +---------------------+-----------------------------------------------+
     | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
     +---------------------+-----------------------------------------------+
     1 row in set (0.00 sec)

    3.3 long_query_time

    开启了慢查询日志后,什么样的SQL才会记录到慢查询日志里面呢?

    这个是由参数long_query_time控制,默认情况下long_query_time的值为10秒,可以使用命令修改,也可以在my.cnf参数里面修改。

    关于运行时间正好等于long_query_time的情况,并不会被记录下来。

    也就是说,在mysql源码里是判断大于long_query_time,而非大于等于。

    从MySQL 5.1开始,long_query_time开始以微秒记录SQL语句运行时间,之前仅用秒为单位记录。

    如果记录到表里面,只会记录整数部分,不会记录微秒部分。

    mysql> show variables like 'long_query_time%';
     +-----------------+-----------+
     | Variable_name   | Value     |
     +-----------------+-----------+
     | long_query_time | 10.000000 |
     +-----------------+-----------+
     1 row in set (0.00 sec)
    
    mysql> set global long_query_time=4;
     Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like 'long_query_time';
     +-----------------+-----------+
     | Variable_name   | Value     |
     +-----------------+-----------+
     | long_query_time | 10.000000 |
     +-----------------+-----------+
     1 row in set (0.00 sec)

    如上所示,我修改了变量long_query_time,但是查询变量long_query_time的值还是10,难道没有修改到呢?

    注意:使用命令 set global long_query_time=4修改后,需要重新连接或新开一个会话才能看到修改值。

    show variables like 'long_query_time'查看是当前会话的变量值。

    也可以不用重新连接会话,而是用show global variables like 'long_query_time';

    3.4 log_output

    log_output参数指定日志的存储方式。

    log_output='FILE'表示将日志存入文件,默认值也是'FILE'

    log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。

    同时也支持两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'

    日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源。

    因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件

    mysql> show variables like '%log_output%';
     +---------------+-------+
     | Variable_name | Value |
     +---------------+-------+
     | log_output    | FILE  |
     +---------------+-------+
     1 row in set (0.00 sec)
    
    mysql> set global log_output='TABLE';
     Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like '%log_output%';
     +---------------+-------+
     | Variable_name | Value |
     +---------------+-------+
     | log_output    | TABLE |
     +---------------+-------+
     1 row in set (0.00 sec)
    
    mysql> select sleep(5) ;
     +----------+
     | sleep(5) |
     +----------+
     |        0 |
     +----------+
     1 row in set (5.00 sec)
    
    mysql>
    
    mysql> select * from mysql.slow_log;
     +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
     | start_time          | user_host                 | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text        | thread_id |
     +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
     | 2016-06-16 17:37:53 | root[root] @ localhost [] | 00:00:03   | 00:00:00  |         1 |             0 |    |              0 |         0 |         1 | select sleep(3) |         5 |
     | 2016-06-16 21:45:23 | root[root] @ localhost [] | 00:00:05   | 00:00:00  |         1 |             0 |    |              0 |         0 |         1 | select sleep(5) |         2 |
     +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
     2 rows in set (0.00 sec)

    3.5 log-queries-not-using-indexes

    该系统变量指定未使用索引的查询也被记录到慢查询日志中(可选项)。

    如果调优的话,建议开启这个选项。

    另外,开启了这个参数,其实使用full index scan的SQL也会被记录到慢查询日志。

    mysql> show variables like 'log_queries_not_using_indexes';
     +-------------------------------+-------+
     | Variable_name                 | Value |
     +-------------------------------+-------+
     | log_queries_not_using_indexes | OFF   |
     +-------------------------------+-------+
     1 row in set (0.00 sec)
    
    mysql> set global log_queries_not_using_indexes=1;
     Query OK, 0 rows affected (0.00 sec)
    
    mysql> show variables like 'log_queries_not_using_indexes';
     +-------------------------------+-------+
     | Variable_name                 | Value |
     +-------------------------------+-------+
     | log_queries_not_using_indexes | ON    |
     +-------------------------------+-------+
     1 row in set (0.00 sec)

    3.6 log_slow_admin_statements

    这个系统变量表示,是否将慢管理语句例如ANALYZE TABLEALTER TABLE等记入慢查询日志。

    mysql> show variables like 'log_slow_admin_statements';
     +---------------------------+-------+
     | Variable_name             | Value |
     +---------------------------+-------+
     | log_slow_admin_statements | OFF   |
     +---------------------------+-------+
     1 row in set (0.00 sec)

    3.7 Slow_queries

    如果你想查询有多少条慢查询记录,可以使用Slow_queries系统变量。

    mysql> show global status like '%Slow_queries%';
     +---------------+-------+
     | Variable_name | Value |
     +---------------+-------+
     | Slow_queries  | 2104  |
     +---------------+-------+
     1 row in set (0.00 sec)

    另外,还有log_slow_slave_statements --log-short-format 参数,可到MySQL网站了解。

    4 mysqldumpslow工具

    在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活。

    MySQL提供了日志分析工具mysqldumpslow

    查看mysqldumpslow的帮助信息:

    [root@DB-Server ~]# mysqldumpslow --help
     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(返回前面n条数据)
       -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

     

    比如,得到返回记录集最多的10个SQL。

    mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

    得到访问次数最多的10个SQL

    mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

    得到按照时间排序的前10条里面含有左连接的查询语句。

    mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

    另外建议在使用这些命令时结合 |more 使用 ,否则有可能出现刷屏的情况。

    mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
    

    ——————————————————end——————————————————

    欢迎各位交流

    群主每周会精选一些实战技术文章发布在微信群,提供给各位交流探讨与学习。考虑到群内讨论内容会导致消息被顶,因此我每周会汇总目录到我的公众号主菜单。

    大家可以关注公众号:八点半技术站    ,可以通过公众号进入我们WeChat技术交流群!!!

    原则:群内禁止鄙视、讽刺等任何初学者,否则直接踢群,禁止任何业余广告推广。

    展开全文
  • Mysql慢查询日志

    2020-12-14 16:56:26
    Mysql慢查询日志概念常用参数查询与设定查询慢查询记录数mysqldumpslow工具 概念 Mysql的慢查询是一种日志, 主要用于记录 Mysql中响应时间超过设定的阀值的语句. 常用参数 类型 说明 默认值 slow_query_log ...
  • Mysql慢查询解释 MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10...
  • 开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。下面这篇文章主要介绍了关于MySQL开启慢查询的相关资料,需要的朋友可以参考下。
  • 随着访问量的上升,MySQL数据库的压力就越大,几乎大部分使用MySQL架构的web应用在数据库上都会出现性能问题,通过mysql慢查询日志跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句。...
  • MySQL慢查询,全名是慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阀值的语句。具体环境中,运行时间超过long_query_time值的SQL语句,则会被记录到慢查询日志中。long_query_time的...
  • mysql慢查询排查,执行效率

    千次阅读 2019-10-29 11:18:13
    如何在MySQL中查找效率慢的SQL语句呢?这可能是困扰很多人的一个问题,MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,...MySQL慢查询定义 分析MySQL语句查...

    如何在MySQL中查找效率慢的SQL语句呢?这可能是困扰很多人的一个问题,MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 会写一个包含所有执行时间超过long_query_time 秒的SQL语句的日志文件,通过查看这个日志文件定位效率较低的SQL 。

    MySQL慢查询定义

    分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划,还可以让MySQL记录下查询超过指定时间的语句,我们将超过指定时间的SQL语句查询称为“慢查询”。

    慢查询的体现

    慢查询主要体现在慢上,通常意义上来讲,只要返回时间大于 >1 sec上的查询都可以称为慢查询。慢查询会导致CPU,内存消耗过高。数据库服务器压力陡然过大,那么大部分情况来讲,肯定是由某些慢查询导致的。

    开启慢查询的方法:

    MySQL5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。

    mysql> show variables like 'long%';
    
    long_query_time | 10.000000
    
    mysql> set long_query_time=1;
    
    mysql> show variables like 'slow%';
    
    slow_launch_time    | 2
    
    slow_query_log      | ON
    
    slow_query_log_file | /tmp/slow.log
    
    mysql> set global slow_query_log='ON'
    

    字段解释

    long_query_time 当SQL语句执行时间超过此数值时,就会被记录到日志中,建议设置为1或者更短。

    slow_query_log 这个参数设置为ON,可以捕获执行时间超过一定数值的SQL语句。

    slow_query_log_file 记录日志的文件名。

    一旦slow_query_log变量被设置为ON,MySQL会立即开始记录。

    /etc/my.cnf   里面可以设置上面MySQL全局变量的初始值。

    分析SQL执行效率浅谈

    一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句

    slow_query_log

    这个参数设置为ON,可以捕获执行时间超过一定数值的SQL语句。

    long_query_time

    当SQL语句执行时间超过此数值时,就会被记录到日志中,建议设置为1或者更短。

    slow_query_log_file

    记录日志的文件名。

    log_queries_not_using_indexes

    这个参数设置为ON,可以捕获到所有未使用索引的SQL语句,尽管这个SQL语句有可能执行得挺快。

    二、检测MySQL中sql语句的效率的方法

    1. 通过查询日志

    1)Windows下开启MySQL慢查询

    MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上

    代码如下

    log slow queries = F:/MySQL/log/mysqlslowquery.log

    long_query_time = 2

    2)Linux下启用MySQL慢查询

    MySQL在Linux系统中的配置文件一般是是my.cnf找到[mysqld]下面加上

    代码如下

    log slow queries = /data/mysqldata/slowquery.log

    long_query_time = 2

    说明:

    log slow queries = /data/mysqldata/slowquery.log

    为慢查询日志存放的位置,一般这个目录要有MySQL的运行帐号的可写权限,一般都将这个目录设置为MySQL的数据存放目录;

    long_query_time = 2中的2表示查询超过两秒才记录;

     

    2. show processlist 命令

    SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。

    例如如图:

    各列的含义和用途:

    Id列:一个标识,你要kill一个语句的时候很有用,用命令杀掉此查询 /*/mysqladmin kill 进程号。

    User列:显示单前用户,如果不是root,这个命令就只显示你权限范围内的sql语句。

    Host列:显示这个语句是从哪个ip的哪个端口上发出的。用于追踪出问题语句的用户。

    db列:显示这个进程目前连接的是哪个数据库。

    Command列:显示当前连接的执行的命令,一般就是休眠(sleep),查询(query),连接(connect)。

    Time列:此这个状态持续的时间,单位是秒。

    State列:显示使用当前连接的sql语句的状态,很重要的列,后续会有所有的状态的描述,请注意,state只是语句执行中的某一个状态,一个 sql语句,以查询为例,可能需要经过copying to tmp table,Sorting result,Sending data等状态才可以完成

    Info列;显示这个sql语句,因为长度有限,所以长的sql语句就显示不全,但是一个判断问题语句的重要依据。

    这个命令中最关键的就是state列,MySQL列出的状态主要有以下几种:

    Checking table:正在检查数据表(这是自动的)。

    Closing tables:正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。

    Connect Out:复制从服务器正在连接主服务器。

    Copying to tmp table on disk:由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。

    Creating tmp table:正在创建临时表以存放部分查询结果。

    deleting from main table:服务器正在执行多表删除中的第一部分,刚删除第一个表。

    deleting from reference tables:服务器正在执行多表删除中的第二部分,正在删除其他表的记录。

    Flushing tables:正在执行FLUSH TABLES,等待其他线程关闭数据表。

    Killed:发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。

    Locked:被其他查询锁住了。

    Sending data:正在处理SELECT查询的记录,同时正在把结果发送给客户端。

    Sorting for group:正在为GROUP BY做排序。

    Sorting for order:正在为ORDER BY做排序。

    Opening tables:这个过程应该会很快,除非受到其他因素的干扰。例如,在执ALTER TABLE或LOCK TABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。

    Removing duplicates:正在执行一个SELECT DISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。

    Reopen table:获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。

    Repair by sorting:修复指令正在排序以创建索引。

    Repair with keycache:修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。

    Searching rows for update:正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。

    Sleeping:正在等待客户端发送新请求.

    System lock:正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表,那么可以通过增加--skip-external-locking参数来禁止外部系统锁。

    Upgrading lock:INSERT DELAYED正在尝试取得一个锁表以插入新记录。

    Updating:正在搜索匹配的记录,并且修改它们。

    User Lock:正在等待GET_LOCK()。

    Waiting for tables:该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。

    waiting for handler insert:INSERT DELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。

    大部分状态对应很快的操作,只要有一个线程保持同一个状态好几秒钟,那么可能是有问题发生了,需要检查一下。

    还有其他的状态没在上面中列出来,不过它们大部分只是在查看服务器是否有存在错误是才用得着。

    三、explain来了解SQL执行的状态

    explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

    使用方法,在select语句前加上explain就可以了,例如下:

    explain select c.title, cc.content form comment_content cc, comment c where cc.id=c.id

    结果如图:

    EXPLAIN列的解释

    table列:显示这一行的数据是关于哪张表的

    type列:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

    possible_keys 列:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

    key列:实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句 中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引

    key_len列:使用的索引的长度。在不损失精确性的情况下,长度越短越好

    ref列:显示索引的哪一列被使用了,如果可能的话,是一个常数

    rows列:MYSQL认为必须检查的用来返回请求数据的行数

    Extra列:关于MYSQL如何解析查询的额外信息。

    最后MySQL优化建议

    索引优化,最简单粗暴的办法,给查询语句添加复合索引,但不是最好的方式

    将大表拆成小的汇总表

    重在实践,MySQL优化器在很多情况下不能给出,最快的实现方式

    避免在大表上的group by,order by,offset 操作,除非你知道如何优化的前提下

    SQL WHERE查询条件,尽量按照添加的索引顺序来

    展开全文
  • MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query...这篇文章主要给大家介绍了关于MySQL慢查询日志的基本使用的相关资料,需要的朋友可以参考下
  • mysql慢查询会话查询 schedule定时任务调度 pymysql执行sql 代码分解 mysql慢查询 #会话查询,只能查询所有会话,不能按条件过滤,不过比较好记 show PROCESSLIST; #从information_schema中查询会话,可以按条件...
  • MySQL通过慢查询日志定位执行效率较低的SQL语句,当慢查询日志的内容过多时,通过mysqldumpslow工具(MySQL客户端安装自带)来对慢查询日志进行分类汇总。  MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,...
  • MySQL慢查询日志分析详解

    千次阅读 2019-06-27 12:59:56
    MySQL慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S...

    MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

    MySQL慢查询定义

    分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划,还可以让MySQL记录下查询超过指定时间的语句,我们将超过指定时间的SQL语句查询称为“慢查询”。

    MySQL慢查询的体现

    慢查询主要体现在慢上,通常意义上来讲,只要返回时间大于 >1 sec上的查询都可以称为慢查询。慢查询会导致CPU,内存消耗过高。数据库服务器压力陡然过大,那么大部分情况来讲,肯定是由某些慢查询导致的。

    mysql慢查询开启

    1.查看当前慢查询设置情况

    #查看慢查询时间,默认10s,建议降到1s或以下
    mysql> show variables like "long_query_time";
    +-----------------+----------+
    | Variable_name   | Value    |
    +-----------------+----------+
    | long_query_time | 1.000000 |
    +-----------------+----------+
    1 row in set (0.00 sec)
    
    #查看慢查询配置情况,其中,slow_query_log的值是on就是已开启功能了。
    mysql> show variables like "%slow%";
    +-----------------------------------+----------------------+
    | Variable_name                     | Value                |
    +-----------------------------------+----------------------+
    | log_slow_admin_statements         | OFF                  |
    | log_slow_filter                   |                      |
    | log_slow_rate_limit               | 1                    |
    | log_slow_rate_type                | session              |
    | log_slow_slave_statements         | OFF                  |
    | log_slow_sp_statements            | ON                   |
    | log_slow_verbosity                |                      |
    | max_slowlog_files                 | 0                    |
    | max_slowlog_size                  | 0                    |
    | slow_launch_time                  | 2                    |
    | slow_query_log                    | ON                   |
    | slow_query_log_always_write_time  | 10.000000            |
    | slow_query_log_file               | /tmp/slow_querys.log |
    | slow_query_log_use_global_control |                      |
    +-----------------------------------+----------------------+
    14 rows in set (0.01 sec)
    

    2.如何开启慢查询功能
    方法一:在服务器上找到mysql的配置文件my.cnf , 然后再mysqld模块里追加一下内容,这样的好处是会一直生效,不好就是需要重启mysql进程。

    vim my.cnf
    [mysqld]
    slow_query_log = ON
    #定义慢查询日志的路径
    slow_query_log_file = /tmp/slow_querys.log
    #定义查过多少秒的查询算是慢查询,我这里定义的是1秒,5.6之后允许设置少于1秒,例如0.1秒
    long_query_time = 1
    #用来设置是否记录没有使用索引的查询到慢查询记录,默认关闭,看需求开启,会产生很多日志,可动态修改
    #log-queries-not-using-indexes
    管理指令也会被记录到慢查询。比如OPTIMEZE TABLE, ALTER TABLE,默认关闭,看需求开启,会产生很多日志,可动态修改
    #log-slow-admin-statements
    

    然后重启mysql服务器即可,这是通过一下命令看一下慢查询日志的情况:

    tail -f /tmp/slow_querys.log
    

    方法二:通过修改mysql的全局变量来处理,这样做的好处是,不用重启mysql服务器,登陆到mysql上执行一下sql脚本即可,不过重启后就失效了。

    #开启慢查询功能,1是开启,0是关闭
    mysql> set global slow_query_log=1;
    #定义查过多少秒的查询算是慢查询,我这里定义的是1秒,5.6之后允许设置少于1秒,例如0.1秒
    mysql> set global long_query_time=1;
    #定义慢查询日志的路径
    mysql> set global slow_query_log_file='/tmp/slow_querys.log';
    #关闭功能:set global slow_query_log=0;
    然后通过一下命令查看是否成功
    mysql> show variables like 'long%'; 
    mysql> show variables like 'slow%';
    #设置慢查询记录到表中
    #set global log_output='TABLE';
    

    特别要注意的是long_query_time的设置,5.6之后支持设置低于0.1秒,所以记录的详细程度,就看你自己的需求,数据库容量比较大的,超过0.1秒还是比较多,所以就变得有点不合理了。

    MYSQL慢查询日志的记录定义

    直接查看mysql的慢查询日志分析,比如我们可以tail -f slow_query.log查看里面的内容

    tail -f  slow_query.log
    # Time: 110107 16:22:11
    # User@Host: root[root] @ localhost []
    # Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774
    SET timestamp=1294388531;
    select count(*) from ep_friends;
    

    字段意义解析:

    第一行,SQL查询执行的时间 第二行,执行SQL查询的连接信息,用户和连接IP 第三行,记录了一些我们比较有用的信息,如下解析:
    - Query_time,这条SQL执行的时间,越长则越慢
    - Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
    - Rows_sent,查询返回的行数
    - Rows_examined,查询检查的行数,越长就当然越费时间
    第四行,设置时间戳,没有实际意义,只是和第一行对应执行时间。
    第五行及后面所有行(第二个# Time:之前),执行的sql语句记录信息,因为sql可能会很长。

    MYSQL慢查询日志分析方法

    虽然慢查询日志已经够清晰,但是往往我们的日志记录到的不是只有一条sql,可能有很多很多条,如果不加以统计,估计要看到猴年马月,这个时候就需要做统计分析了。

    方法一:使用mysql程序自带的mysqldumpslow命令分析
    例如:
    mysqldumpslow -s c -t 10 /tmp/slow-log
    这会输出记录次数最多的10条SQL语句,得出的结果和上面一般慢查询记录的格式没什么太大差别,这里就不展开来详细解析了。

    参数解析:
    -s:是表示按照何种方式排序,子参数如下:
        c、t、l、r:分别是按照记录次数、时间、查询时间、返回的记录数来排序,
        ac、at、al、ar:表示相应的倒叙;
    -t:返回前面多少条的数据,这里意思就是返回10条数据了(也可以说是前十)
    -g:后边可以写一个正则匹配模式,大小写不敏感的,比如:
        /path/mysqldumpslow -s r -t 10 /tmp/slow-log,得到返回记录集最多的10个查询。
        /path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log,得到按照时间排序的前10条里面含有左连接的查询语句。
    

    方法二:使用pt(Percona Toolkit)工具的pt-query-digest进行统计分析。
    这个是由Percona公司出品的一个用perl编写的脚本,只有安装上pt工具集才会存在,有兴趣的朋友就要先安装pt工具了。直接分析慢查询文件,执行如下:

    pt-query-digest slow_querys.log >t.txt
    

    因为记录里还是可能有很多sql在,看起来还是费劲,所以建议输出到文件来看了,具体不多过多介绍。

    参考资料: https://blog.csdn.net/enweitech/article/details/80239189
    https://blog.51cto.com/arthur376/1948944

    展开全文
  • MySQL慢查询日志记录和分析

    千次阅读 2019-01-08 21:05:41
    一、引言 在日常的开发中,有时候会有用户反馈说网站的响应速度...这个时候,我们就需要慢慢分析,我们可以通过开启慢查询日志,来监控生产环境上有没有执行特别慢的SQL,如果有,我们可以定位到是哪一条SQL,从而...

    一、引言

    在日常的开发中,有时候会收到用户或者产品经理反馈说网站的响应速度有点慢,即使是管理系统页面也会出现这种情况。导致网页响应速度慢的原因有很多,比如:数据表的某些字段没有建立索引,或者说是建立了索引,但索引失效,又或者说肯能因为最近来了一个新人同事,把某一条的SQL语句写的执行效率很低。这个时候,为了找到原因,看看是不是SQL的问题,这时我们可以通过开启MySQL的慢查询日志,来监控测试环境或者生产环境上有没有执行特别慢的SQL,如果有,我们可以定位到是哪一条SQL,从而进而优化,这就需要用到慢查询日志。

    二、慢查询日志

    MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的SQL语句,具体是指运行时间超过 long_query_time 值的SQL,这样的SQL则会被记录到慢查询日志中。long_query_time 的默认值为10,意思是运行10S以上的SQL语句。

    默认情况下,MySQL数据库并没有开启慢查询日志,需要我们手动来设置这个参数。当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。
    慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

    1、慢查询日志相关参数

    MySQL 慢查询日志的相关参数解释:

    • slow_query_log :是否开启慢查询日志,1表示开启,0表示关闭。

    • log-slow-queries :旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log。

    • slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log。

    • long_query_time :慢查询阈值,当查询时间多于设定的阈值时,该SQL会被记录日志中或者数据表中。

    • log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。

    • log_output:日志存储方式。log_output=‘FILE’表示将日志存入文件,默认值是’FILE’。log_output='TABLE’表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。

    2、慢查询日志配置

    在MySQL配置文件中的【mysqld】节点下做如下配置:

    slow_query_log=1;											//开启慢查询日志
    slow_query_log_file="/usr/local/mysql/data/"    			//慢查询如日存放的路径
    long_query_time=3;											//慢查询SQL记录的时间
    log_output=FILE												//以文件方式记录
    
    3、参数配置说明
    (1)、slow_query_log

    默认情况下 slow_query_log 的值为OFF,表示慢查询日志是禁用的,可以通过设置 slow_query_log 的值来开启,如下所示:
    在这里插入图片描述
    在这里插入图片描述
    注意:
    使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库生效,MySQL重启后该设置则会失效,必须再次重新设置。
    如果要该设置永久生效,就必须修改配置文件my.ini(其它系统变量也是如此,windows下MySQL配置文件为my.ini文件,linux下为my.cnf)

    修改my.ini文件,增加或修改参数 slow_query_log 和 slow_query_log_file 后,然后重启MySQL服务器,如下所示:

    slow_query_log =1
    slow_query_log_file=C:\ProgramData\MySQL\MySQL Server 5.1\data\Think-PC-slow.log
    

    在这里插入图片描述

    慢查询的参数 slow_query_log_file ,它指定慢查询日志文件的存放路径,系统默认会给一个缺省的文件 host_name-slow.log(主机名-slow.log文件)

    (2)、long_query_time

    那么开启了慢查询日志后,什么样的SQL才会记录到慢查询日志里面呢? 这个是由参数long_query_time 控制,默认情况下 long_query_time 的值为10秒,可以使用命令修改,也可以在 my.ini 或 my.cnf 参数里面修改。关于运行时间正好等于 long_query_time 的情况,并不会被记录下来。也就是说,在mysql源码里是判断大于 long_query_time,而非大于等于。从MySQL 5.1开始,long_query_time 开始以微秒记录SQL语句运行时间,之前仅用秒为单位记录。如果记录到表里面,只会记录整数部分,不会记录微秒部分。
    在这里插入图片描述
    在这里插入图片描述

    如上图所示,修改了变量 long_query_time,但是再次查询变量 long_query_time 的值还是10,难道没有修改到呢?注意:使用命令 set global long_query_time=4 修改后,需要重新连接或新开一个会话才能看到修改值。你也可以不用重新连接会话,而是用 show global variables like ‘long_query_time’; 如下所示:

    方式一:退出此次连接,重新进入
    在这里插入图片描述

    方式二:使用show global variables like ‘long_query_time’;命令
    在这里插入图片描述

    (3)、log_output

    log_output 参数是指定日志的存储方式。log_output=‘FILE’ 表示将日志存入文件,默认值是’FILE’。log_output=‘TABLE’ 表示将日志存入数据库,这样日志信息就会被写入到 mysql.slow_log 表中。MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。
    在这里插入图片描述

    (4)、log-queries-not-using-indexes

    系统变量 log-queries-not-using-indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。如果调优的话,建议开启这个选项。另外,开启了这个参数,其实使用 full index scan 的SQL也会被记录到慢查询日志。
    在这里插入图片描述
    在这里插入图片描述

    (5)、slow_queries

    可以使用下面命令来查看慢查询日志里面有几条被记录下来的慢SQL,初始时为0:

    show global status like '%slow_queries%';
    

    在这里插入图片描述

    下面我们用select sleep()来模拟慢查询SQL,多执行几次;
    在这里插入图片描述

    执行完后,执行上面的命令查看一下有几条慢SQL被记录,再去存放了慢查询日志路径下面,去找日志,日志记录如下图(只截了一条):
    在这里插入图片描述
    在这里插入图片描述

    三、日志分析工具mysqldumpslow

    在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具mysqldumpslow。

    1、参数的意义
    参数意义
    s表示按照何种方式排序
    c访问计数
    l锁定时间
    r返回记录
    t查询时间
    al平均锁定时间
    ar平均返回记录数
    at平均查询时间
    -t是top n的意思,即为返回前面多少条的数据
    -g后边可以写一个正则匹配模式,大小写不敏感的
    2、示例

    (1)、得到返回记录集最多的10个SQL

    mysqldumpslow -s r -t 10 /usr/mysql/mysql_slow.log
    

    (2)、得到访问次数最多的10个SQL

    mysqldumpslow -s c -t 10 /usr/mysql/mysql_slow.log
    

    (3)、得到按照时间排序的前10条里面含有左连接的查询语句

    mysqldumpslow -s t -t 10 -g “left join” /usr/mysql/mysql_slow.log
    

    (4)、另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现刷屏的情况

    mysqldumpslow -s r -t 20 /usr/mysql/mysql-slow.log | more
    

    四、小结

    (1)、默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数。
    (2)、如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。

    展开全文
  • mysql慢查询日志的使用
  • 主要介绍MySQL数据库中慢查询日志的使用流程、使用方式、以及日志分析工具mysqldumpslow的相关内容
  • 详解MySQL 慢查询

    2020-09-08 19:54:32
    主要介绍了MySQL 慢查询的相关资料,文中讲解非常细致,帮助大家更好的理解和学习MySQL,感兴趣的朋友可以了解下
  • MySQL慢查询日志总结

    万次阅读 2018-07-30 14:16:57
    MySQL慢查询日志总结 慢查询日志概念  MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_...
  • MySQL慢查询日志

    2019-05-06 16:03:29
    MySQL慢查询日志
  • MySQL 慢查询日志如何查看及配置

    千次阅读 2020-03-25 15:22:38
    MySQL 慢查询日志是排查问题 SQL 语句,以及检查当前 MySQL 性能的一个重要功能。 查看是否开启慢查询功能: mysql> show variables like 'slow_query%'; +---------------------+----------------------------...
  • Mysql慢查询日志以及优化

    千次阅读 2019-06-11 15:01:16
    1、临时开启慢查询日志(如果需要长时间开启,则需要更改mysql配置文件) set global slow_query_log = on; 注:如果想关闭慢查询日志,只需要执行 set global slow_query_log = off; 即可 2、临时设置慢查询...
  • Docker+MySql慢查询

    千次阅读 2020-08-18 11:40:33
    docker pull mysql:5.7 准备这样一个目录 我的目录 /home/shenyi -----mysql ---data ---conf --my.cnf (配置文件放在这) ---mylog my.cnf配置文件 [client] default-character-set=utf8mb4 [mysql] ...
  • mysql慢查询开启与使用

    万次阅读 2020-12-04 21:16:51
    慢查询日志,顾名思义,就是查询慢的日志,是指mysql记录所有执行超过long_query_time(默认的时间10秒)参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下,慢查询日志是关闭...
  • mysql慢查询排查处理

    千次阅读 2019-06-28 13:56:13
    事务阻塞,慢查询 2.mysql优化的方向 sql及索引优化。 数据库表结构,根据数据设计查询最优的表结构。 系统配置优化,对打开文件数和安全的限制。 硬件,选择最适合数据库的cpu,更快的IO,更大的内存,cpu...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 167,267
精华内容 66,906
关键字:

mysql慢查询

mysql 订阅