精华内容
下载资源
问答
  • 开启慢查询日志,可以让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 慢查询

    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慢查询和日志

    万次阅读 2021-01-06 09:53:46
    目前使用三方云数据库,基本都搭配都有慢查询和日志记录,但是如果是自己搭建都mysql,怎么开启慢查询日志呢? 而且,让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。 ...

    一、简介

    目前使用三方云数据库,基本都搭配都有慢查询和日志记录,但是如果是自己搭建都mysql,怎么开启慢查询日志呢?
    而且,让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。
    具体如下:

    二、参数说明

    slow_query_log 慢查询开启状态
    slow_query_log_file 慢查询日志存放的位置(这个目录需要MySQL的运行帐号的可写权限,一般设置为MySQL的数据存放目录)
    long_query_time 查询超过多少秒才记录

    三、设置步骤

    1.查看慢查询相关参数

    mysql> show variables like ‘slow_query%’;
    ±--------------------------±---------------------------------+
    | Variable_name | Value |
    ±--------------------------±---------------------------------+
    | slow_query_log | OFF |
    | slow_query_log_file | /mysql/data/localhost-slow.log |
    ±--------------------------±---------------------------------+

    mysql> show variables like ‘long_query_time’;
    ±----------------±----------+
    | Variable_name | Value |
    ±----------------±----------+
    | long_query_time | 10.000000 |
    ±----------------±----------+

    2.设置方法
    • 方法一:全局变量设置

      将 slow_query_log 全局变量设置为“ON”状态
      mysql> set global slow_query_log=‘ON’;

      设置慢查询日志存放的位置
      mysql> set global slow_query_log_file=’/usr/local/mysql/data/slow.log’;

      查询超过1秒就记录
      mysql> set global long_query_time=1;

    • 方法二:配置文件设置
      修改配置文件my.cnf,在[mysqld]下的下方加入
      [mysqld]
      slow_query_log = ON
      slow_query_log_file = /usr/local/mysql/data/slow.log
      long_query_time = 1

    3.重启MySQL服务

    service mysqld restart

    4.查看设置后的参数

    mysql> show variables like ‘slow_query%’;
    ±--------------------±-------------------------------+
    | Variable_name | Value |
    ±--------------------±-------------------------------+
    | slow_query_log | ON |
    | slow_query_log_file | /usr/local/mysql/data/slow.log |
    ±--------------------±-------------------------------+

    mysql> show variables like ‘long_query_time’;
    ±----------------±---------+
    | Variable_name | Value |
    ±----------------±---------+
    | long_query_time | 1.000000 |
    ±----------------±---------+

    四、测试
    1.执行一条慢查询SQL语句

    mysql> select sleep(2);

    2.查看是否生成慢查询日志

    ls /usr/local/mysql/data/slow.log
    如果日志存在,MySQL开启慢查询设置成功!

    展开全文
  • 主要介绍了MySQL慢查询日志分析的实例教程,通过设置参数从慢查询日志开始分析性能问题的原因,需要的朋友可以参考下
  • MySQL慢查询入门

    2020-12-14 14:51:23
    慢查询相关指令: #查看慢查询日志配置 show variables LIKE 'slow_query%'; #开启慢查询 SET GLOBAL slow_query_log=1; #查看慢查询的阈值 show variables LIKE 'long%'; #修改阈值为1s(修改阈值后如未生效,重新...
  • Mysql开启慢查询

    2020-12-14 21:41:20
    MySQL慢查询配置  1. 慢查询有什么用?  它能记录下所有执行超过long_query_time时间的SQL语句, 帮你找到执行慢的SQL, 方便我们对这些SQL进行优化.  2. 如何开启慢查询?  首先我们先查看mysql服务器的...
  • 数据库优化——慢查询MySQL定位优化流程

    万次阅读 多人点赞 2021-05-19 11:07:25
    如何定位并优化慢查询SQL?如何使用慢查询日志?本文带来慢查询例子演示,新手都能看懂!那查询语句慢怎么办?explain带你分析sql执行计划!当主键索引、唯一索引、普通索引都存在,查询优化器如何选择?本文带你一...

    继前篇博文看了这么多,终于理解了MySQL索引之后,给大家再来一篇日常开发需要用到的优化技巧。

    1.如何定位并优化慢查询SQL?

    一般有3个思考方向
    1.根据慢日志定位慢查询sql
    2.使用explain等工具分析sql执行计划
    3.修改sql或者尽量让sql走索引

    2.如何使用慢查询日志?

    先给出步骤,后面说明
    有3个步骤
    1.开启慢查询日志

      首先开启慢查询日志,由参数slow_query_log决定是否开启,在MySQL命令行下输入下面的命令:

    set global slow_query_log=on;
    

    默认环境下,慢查询日志是关闭的,所以这里开启。

    2.设置慢查询阈值

    set global long_query_time=1;
    

      只要你的SQL实际执行时间超过了这个阈值,就会被记录到慢查询日志里面。这个阈值默认是10s,线上业务一般建议把long_query_time设置为1s,如果某个业务的MySQL要求比较高的QPS,可设置慢查询为0.1s。发现慢查询及时优化或者提醒开发改写。一般测试环境建议long_query_time设置的阀值比生产环境的小,比如生产环境是1s,则测试环境建议配置成0.5s。便于在测试环境及时发现一些效率的SQL

      甚至某些重要业务测试环境long_query_time可以设置为0,以便记录所有语句。并留意慢查询日志的输出,上线前的功能测试完成后,分析慢查询日志每类语句的输出,重点关注Rows_examined(语句执行期间从存储引擎读取的行数),提前优化。

    3.确定慢查询日志的文件名和路径

    show global variables like 'slow_query_log_file'
    

    在这里插入图片描述
    结果会发现慢日志默认路径就是MySQL的数据目录,我们可以来看一下MySQL数据目录

     show global variables like 'datadir';
    

    在这里插入图片描述
    不用关注这里为什么不是MySQL 8.0,这和版本没什么关系的。

    来,直接上菜,干巴巴的定义我自己都看不下去

    我们先来查看一下变量,我框出了需要注意的点
    查询带有quer的相关变量

    show global variables like '%quer%';
    

    这里设置慢查询阈值为1s

    set global long_query_time=1;
    

    可以看到已经修改过来了

      但是重启mysql客户端设置和统计慢查询日志条数就会清零,即所有配置修改会还原

      命令修改配置之后,在命令行net stop mysql关闭MySQL服务,再net start mysql开启MySQL服务,接着执行show global variables like '%quer%';会发现配置还原了。

      在配置文件修改才能永久改变,否则重启数据库就还原了

    3.慢查询例子演示,新手都能看懂

    数据表结构,偷懒没写comment

    CREATE TABLE `person_info_large` (  
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  
        `account` VARCHAR (10),   
        `name` VARCHAR (20),  
        `area` VARCHAR (20),  
        `title` VARCHAR (20), 
        `motto` VARCHAR (50),
        PRIMARY KEY (`id`),  
        UNIQUE(`account`),
        KEY `index_area_title`(`area`,`title`) 
    ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8  
    

    这里的数据是200W条。请注意表结构,记住哪几个字段有索引即可,后续围绕这个表进行分析。

    这个3.36s并不是实际执行时间,实际执行时间得去慢查询日志去看Query_time参数

      可以看到Query_time: 6.337729s,超过了1s,所以会被记录,一个select语句查询这么久,简直无法忍受。

    图中其他的参数解释如下:
    Time:慢查询发生的时间
    Query_time:查询时间
    Lock_time:等待锁表的时间
    Rows_sent:语句返回的行数
    Rows_exanined:语句执行期间从存储引擎读取的行数

      上面这种方式是用系统自带的慢查询日志查看的,如果觉得系统自带的慢查询日志不方便查看,可以使用pt-query-digest或者mysqldumpslow等工具对慢查询日志进行分析。

      注意:有的慢查询正在执行,结果已经导致数据库负载过高,而由于慢查询还没执行完,因此慢查询日志看不到任何语句,此时可以使用show processlist命令查看正在执行的慢查询。show processlist显示哪些线程正在运行,如果有PROCESS权限,则可以看到所有线程。否则,只能看到当前会话线程。

    4.查询语句慢怎么办?explain带你分析sql执行计划

    根据上一节的表结构可以知道,account是添加了唯一索引的字段。explain分析一下执行计划。

      我们重点需要关注select_typetypepossible_keyskeyExtra这些列,我们来一一说明,看到select_type列,这里是SIMPLE简单查询,其他值下面给大家列出。

    type列,这里是index,表示全索引扫描

      表格从上到下代表了sql查询性能从最优到最差,如果是type类型是all,说明sql语句需要优化。

    注意:如果type = NULL,则表明个MySQL不用访问表或者索引,直接就能得到结果,比如explain select sum(1+2);

      possible_keys代表可能用到的索引列,key表示实际用到的索引列,以实际用到的索引列为准,这是查询优化器优化过后选择的,然后我们也可以根据实际情况强制使用我们自己的索引列来查询。

      Extra列,这里是Using index

      一定要注意,Extra中出现Using filesortUsing temporary代表MySQL根本不能使用索引,效率会受到严重影响,应当尽可能的去优化。

      出现Using filesort说明MySQL对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容,有索引就维护了B+树,数据本来就已经排好序了,这说明根本没有用到索引,而是数据读完之后再排序,可能在内存或者磁盘上排序。也有人将MySQL中无法利用索引的排序操作称为“文件排序”。

      出现Using temporary表示MySQL在对查询结果排序时使用临时表,常见于order by和分组查询group by

    回到上一个话题,我们看到account是添加了唯一索引的字段。explain分析了执行计划后

    直接按照account降序来查

    查看慢查询日志发现,使用索引之后,查询200W条数据的速度快了2s

    接着我们分析一下查询namesql执行计划

    然后给name字段加上索引

    加上索引之后,继续看看查询namesql执行计划

      对比一下前面name不加索引时的执行计划就会发现,加了索引后,typeALL全表扫描变成index索引扫描。order by并没有 using filesort,而是using index,这里B+树已经将这个非聚集索引的索引字段的值排好序了,而不是等到查询的时候再去排序。

      接着我们继续执行查询语句,此时name已经是添加了索引的。

      结果发现,name添加索引之前,降序查询name是花费6.337729s,添加索引之后,降序查询name花费了3.479827s,原因就是B+树的结果集已经是有序的了。

    5.当主键索引、唯一索引、普通索引都存在,查询优化器如何选择?

      查询一下数据的条数,这里count(id),分析一下sql执行计划

      这里实际使用的索引是account唯一索引。

      分析一下:实际使用哪个索引是查询优化器决定的,B+树的叶子结点就是链表结构,遍历链表就可以统计数量,但是这张表,有主键索引、唯一索引、普通索引,优化器选择了account这个唯一索引,这肯定不会使用主键索引,因为主键索引是聚集索引,每个叶子包含具体的一个行记录(很多列的数据都在里面),而非聚集索引每个叶子只包含下一个主键索引的指针,很显然叶子结点包含的数据是越少越好,查询优化器就不会选择主键索引

      当然,也可以强制使用主键索引,然后分析sql执行计划

    我们看一下优化器默认使用唯一索引大致执行时间676ms

    强制使用主键索引大致执行时间779ms

      我们可以用force index强制指定索引,然后去分析执行计划看看哪个索引是更好的,因为查询优化器选择索引不一定是百分百准确的,具体情况可以根据实际场景分析来确定是否使用查询优化器选择的索引。


    欢迎一键三连~

    有问题请留言,大家一起探讨学习

    ----------------------Talk is cheap, show me the code-----------------------
    展开全文
  • MySQL优化利器-慢查询

    2020-12-14 20:31:17
    慢查询  首先,无论进行何种优化,开启慢查询都算是前置条件。慢查询机制,将记录过慢的查询语句(事件),从而为DB维护人员提供优化目标。  检查慢查询是否开启  通过show variables like 'slow_query_log'这...
  • 常见Mysql的慢查询优化方式

    万次阅读 多人点赞 2018-09-21 16:37:02
     在公司实习的时候,导师分配了SQL慢查询优化的任务,任务是这样的:每周从平台中导出生产数据库的慢查询文件进行分析。进行SQL优化的手段也主要是修改SQL写法,或者新增索引。  现在从记录项目中的一点点做起。 ...

    这篇文章主要是就在公司实习的时候,对SQL优化工作作出的一些整理。

        在公司实习的时候,导师分配了SQL慢查询优化的任务,任务是这样的:每周从平台中导出生产数据库的慢查询文件进行分析。进行SQL优化的手段也主要是修改SQL写法,或者新增索引。

        现在从记录项目中的一点点做起。

        (1)数据库中设置SQL慢查询

          一、第一步.开启mysql慢查询  

        方式一:

           修改配置文件  在 my.ini 增加几行:  主要是慢查询的定义时间(超过2秒就是慢查询),以及慢查询log日志记录( slow_query_log)

          方法二:通过MySQL数据库开启慢查询:

    (2)分析慢查询日志         

           直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句,来分析sql慢查询语句

          例如:执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000

           得到如下结果: 显示结果分析:  

                       table |  type | possible_keys | key |key_len  | ref | rows | Extra  EXPLAIN列的解释:           

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

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

                       rows                显示需要扫描行数

                       key                   使用的索引

     

    (3)常见的慢查询优化

     

     (1)索引没起作用的情况

        1. 使用LIKE关键字的查询语句

            在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用。只有“%”不在第一个位置索引才会起作用。

        2. 使用多列索引的查询语句

            MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引,只有查询条件使用了这些字段中的第一个字段时,索引才会被使用。

     

     (2)优化数据库结构

            合理的数据库结构不仅可以使数据库占用更小的磁盘空间,而且能够使查询速度更快。数据库结构的设计,需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

    1. 将字段很多的表分解成多个表 

            对于字段比较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表。因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢。

    2. 增加中间表

            对于需要经常联合查询的表,可以建立中间表以提高查询效率。通过建立中间表,把需要经常联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询,以此来提高查询效率。

     

    (3)分解关联查询

        将一个大的查询分解为多个小查询是很有必要的。

      很多高性能的应用都会对关联查询进行分解,就是可以对每一个表进行一次单表查询,然后将查询结果在应用程序中进行关联,很多场景下这样会更高效,例如:       

     SELECT * FROM tag 
            JOIN tag_post ON tag_id = tag.id
            JOIN post ON tag_post.post_id = post.id
            WHERE tag.tag = 'mysql';
    
            分解为:
    
            SELECT * FROM tag WHERE tag = 'mysql';
            SELECT * FROM tag_post WHERE tag_id = 1234;
            SELECT * FROM post WHERE post.id in (123,456,567);

    (4)优化LIMIT分页

          在系统中需要分页的操作通常会使用limit加上偏移量的方法实现,同时加上合适的order by 子句。如果有对应的索引,通常效率会不错,否则MySQL需要做大量的文件排序操作。

          一个非常令人头疼问题就是当偏移量非常大的时候,例如可能是limit 10000,20这样的查询,这是mysql需要查询10020条然后只返回最后20条,前面的10000条记录都将被舍弃,这样的代价很高。

            优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

          对于下面的查询:

           select id,title from collect limit 90000,10;

          该语句存在的最大问题在于limit M,N中偏移量M太大(我们暂不考虑筛选字段上要不要添加索引的影响),导致每次查询都要先从整个表中找到满足条件 的前M条记录,之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大,且筛选字段没有合适的索引,且M特别大那么这样的代价是非常高的。 试想,如我们下一次的查询能从前一次查询结束后标记的位置开始查找,找到满足条件的100条记录,并记下下一次查询应该开始的位置,以便于下一次查询能直接从该位置 开始,这样就不必每次查询都先从整个表中先找到满足条件的前M条记录,舍弃,在从M+1开始再找到100条满足条件的记录了。

    方法一:虑筛选字段(title)上加索引

           title字段加索引  (此效率如何未加验证)

     

    方法二:先查询出主键id值

    select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

    原理:先查询出90000条数据对应的主键id的值,然后直接通过该id的值直接查询该id后面的数据。

     

    方法三:“关延迟联”

    如果这个表非常大,那么这个查询可以改写成如下的方式:

          Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

            这里的“关延迟联”将大大提升查询的效率,它让MySQL扫描尽可能少的页面,获取需要的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用在优化关联查询中的limit。

     

    方法四:建立复合索引 acct_id和create_time

        select * from acct_trans_log WHERE  acct_id = 3095  order by create_time desc limit 0,10

         注意sql查询慢的原因都是:引起filesort

     

    (5)分析具体的SQL语句

     1、两个表选哪个为驱动表,表面是可以以数据量的大小作为依据,但是实际经验最好交给mysql查询优化器自己去判断。

      例如:  select * from a where id in (select id from b );  

            对于这条sql语句它的执行计划其实并不是先查询出b表的所有id,然后再与a表的id进行比较。
    mysql会把in子查询转换成exists相关子查询,所以它实际等同于这条sql语句:select * from a where exists(select * from b where b.id=a.id );

            而exists相关子查询的执行原理是: 循环取出a表的每一条记录与b表进行比较,比较的条件是a.id=b.id . 看a表的每条记录的id是否在b表存在,如果存在就行返回a表的这条记录。

    exists查询有什么弊端?
          由exists执行原理可知,a表(外表)使用不了索引,必须全表扫描,因为是拿a表的数据到b表查。而且必须得使用a表的数据到b表中查(外表到里表中),顺序是固定死的。

    如何优化?
          建索引。但是由上面分析可知,要建索引只能在b表的id字段建,不能在a表的id上,mysql利用不上。

    这样优化够了吗?还差一些。
          由于exists查询它的执行计划只能拿着a表的数据到b表查(外表到里表中),虽然可以在b表的id字段建索引来提高查询效率。
    但是并不能反过来拿着b表的数据到a表查,exists子查询的查询顺序是固定死的。

    为什么要反过来?
           因为首先可以肯定的是反过来的结果也是一样的。这样就又引出了一个更细致的疑问:在双方两个表的id字段上都建有索引时,到底是a表查b表的效率高,还是b表查a表的效率高?

    该如何进一步优化?
           把查询修改成inner join连接查询:select * from a inner join b on a.id=b.id; (但是仅此还不够,接着往下看)

    为什么不用left join 和 right join?
           这时候表之间的连接的顺序就被固定住了,比如左连接就是必须先查左表全表扫描,然后一条一条的到另外表去查询,右连接同理。仍然不是最好的选择。

    为什么使用inner join就可以?
           inner join中的两张表,如: a inner join b,但实际执行的顺序是跟写法的顺序没有半毛钱关系的,最终执行也可能会是b连接a,顺序不是固定死的。如果on条件字段有索引的情况下,同样可以使用上索引。

    那我们又怎么能知道a和b什么样的执行顺序效率更高?
           你不知道,我也不知道。谁知道?mysql自己知道。让mysql自己去判断(查询优化器)。具体表的连接顺序和使用索引情况,mysql查询优化器会对每种情况做出成本评估,最终选择最优的那个做为执行计划。

            在inner join的连接中,mysql会自己评估使用a表查b表的效率高还是b表查a表高,如果两个表都建有索引的情况下,mysql同样会评估使用a表条件字段上的索引效率高还是b表的。


    利用explain字段查看执行时运用到的key(索引)
           而我们要做的就是:把两个表的连接条件的两个字段都各自建立上索引,然后explain 一下,查看执行计划,看mysql到底利用了哪个索引,最后再把没有使用索引的表的字段索引给去掉就行了。

     

     

     

    展开全文
  • MySQL的慢查询

    万次阅读 2020-09-03 14:35:20
    开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。 一、配置慢查询 1、参数说明 slow_query_log : 慢查询开启状态(默认关闭) slow_query_log...
  • 一分钟快速搞懂Redis的慢查询分析

    万次阅读 多人点赞 2020-03-24 10:39:55
    当Redis客户端出现请求超时的时候,需要检查该时间点是否有慢查询,从而分析出由于慢查询导致的命令级联阻塞。
  • MySQL慢查询-开启慢查询

    千次阅读 2018-08-20 16:18:55
    开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。 二、参数说明 slow_query_log 慢查询开启状态 slow_query_log_file 慢查询日志存放的位置...
  • MySQL慢查询 - 查看慢查询日志

    千次阅读 2019-07-02 15:29:38
    在MySQL 中,慢查询日志主要用来记录响应时间超过阈值的SQL。 响应时间超过 long_query_time(默认值为 10秒),会被记录到慢查询日志中,日志可以设置为文件或者数据库表。 在命令行输入如下命令可以检查慢查询...
  • mysql 慢查询页面工具

    2018-05-17 09:57:51
    mysql 慢查询页面工具 页面化分析慢日志
  • mysql的slow_query_log慢查询日志分析工具说明,主要用来进行慢查询日志的linux平台分析输出结果文档进行MySQL的调优/优化数据库
  • MySQL慢查询(一) - 开启慢查询

    千次阅读 2018-08-19 11:37:26
    开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。 二、参数说明 slow_query_log 慢查询开启状态 slow_query_log_file 慢查询日志存放的位置...
  • mysql慢查询开启与使用

    万次阅读 2020-12-04 21:16:51
    默认情况下,慢查询日志是关闭的,要使用慢查询日志功能,首先要开启慢查询日志功能。 查看慢查询相关的参数 show VARIABLES like '%slow_query_log%' 开启慢查询日志 set GLOBAL slow_query_log = on
  • Redis 慢查询分析

    千次阅读 2018-07-15 13:48:40
    和很多关系型数据库(例如:MySQL)一样, Redis 也提供了慢查询日志记录,Redis 会把命令执行时间超过 slowlog-log-slower-than 的都记录在 Reids 内部的一个列表(list)中,该列表的长度最大为 slowlog-max-len ...
  • 慢查询 首先,无论进行何种优化,开启慢查询都算是前置条件。慢查询机制,将记录过慢的查询语句(事件),从而为DB维护人员提供优化目标。 检查慢查询是否开启 通过show variables like 'slow_query_log'这条语句,...
  • 慢查询排查思路 mysql

    千次阅读 2021-08-13 09:51:52
    慢查询排查思路 mysql
  • redis慢查询

    千次阅读 2020-03-12 19:23:02
    这时我们需要排查一下慢查询 一般慢查询都是keys * 或者查询带*导致的 直接登陆客户端或者使用rdm工具连接上redis后直接输入命令: SLOWLOG GET 即可获得慢查询相关信息 由于本人生产信息保密,下面这个是网上...
  • 主要介绍了如何对MySQL慢查询导致的故障进行处理,慢查询是我们在mysql中经常需要使用到的一个很方便的功能,慢查询对于跟踪有问题的查询很有用,需要的朋友可以参考下
  • 浅谈慢查询

    千次阅读 2019-06-21 09:49:54
    慢查询是什么,如何操作? MySQL记录下查询超过指定时间的语句,超过指定时间的SQL语句查询称为“慢查询” 开启慢查询 方法一:用命令开启慢查询 查看默认慢查询的时间(10秒) mysql> show variables like "%...
  • MySQL慢查询,一口从天而降的锅!

    千次阅读 多人点赞 2021-04-25 22:10:07
    一条慢查询会造成什么后果?年轻时,我一直觉得不就是返回数据会慢一些么,用户体验变差?其实远远不止,我经历过几次`线上事故`,有一次就是由一条SQL慢查询导致的。
  • Redis 慢查询日志

    千次阅读 2019-02-21 11:31:02
    Redis 慢查询日志
  • sql慢查询

    千次阅读 2019-09-22 16:29:40
    1、sql慢查询 什么叫慢查询 SQL默认有个规定:只要10秒钟没有按照规定的时间返回结果,都属于慢查询,存放到日志中, show status 可以查询到数据库的服务器状态信息 --mysql数据库启动了多少时间 ...
  • 慢查询日志

    千次阅读 2017-08-21 17:29:54
    我们分别要识别出来,哪些写入操作性能比较慢,哪些查询操作性能比较慢,先要识别出来有性能问题的这些慢查询,慢写入,然后才能去考虑如何优化写入的性能,如何优化搜索的性能。 搜索慢查询日志 无论是慢查询日志...
  • 当应用程序后台 SQL 查询慢的时候我们一般第一时间会查看数据库慢查询记录,但是慢查询记录是原始文本,直接查询搜索分析比较费时费力,虽然业界有针对 MySQL 慢查询分析的命令行工具(比如:pt-query-digest),...
  • Redis 慢查询

    千次阅读 2018-03-15 15:31:59
    何为慢查询慢查询的两个配置参数 最佳实践 何为慢查询? 系统在命令执行前后计算每条命令的执行时间,当超过预定阀值时,记录命令的发生时间,耗时,命令的详细信息等。 Redis客户端执行一条命令分为发送...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 450,169
精华内容 180,067
关键字:

慢查询