慢查询_慢查询日志 - CSDN
精华内容
参与话题
  • 常见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慢查询

    万次阅读 多人点赞 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-07-29 19:23:50
    我们平台过一段时间就会把生产数据库的慢查询导出来分析,要嘛修改写法,要嘛新增索引。以下是一些笔记、总结整理   慢查询排查    show status; // 查询mysql数据库的一些运行状态  show status like '...

       我们平台过一段时间就会把生产数据库的慢查询导出来分析,要嘛修改写法,要嘛新增索引。以下是一些笔记、总结整理

     

    慢查询排查

     

            show status;  // 查询mysql数据库的一些运行状态

            show status like 'uptime'; // 查看mysql数据库启动多长时间,myisam存储引擎长时间启动需要进行碎片整理

     

            查看慢查询

            show status like 'slow_queries';

     

            查询慢查询时间

            show variables like 'long_query_time';

     

            设置慢查询时间 

            set long_query_time = 0.5;

     

    分析执行情况 EXPLAIN详解

     

            分析查询语句的执行情况,可以分析出所查询的表的一些特征

            EXPLAIN/DESCRIBE/DESCSELECT * FROM...;

            

     


    每个字段说明:

     

    id:SELECT标识符。这是SELECT的查询序列号。

     

    select_type:表示SELECT语句的类型。它可以是以下几种取值:

        SIMPLE:表示简单杳询,其中不包括连接查询和子查询;

        PRIMARY:表示主查询,或者最外层的查询语句;

        UNION:表示连接查询的第2个或后面的查询语句;

        DEPENDENT UNION:连接查询中的第2个或后面的SELECT语句,取决于外面的查询;

        UNION RESULT:连接查询的结果;

        SUBQUERY:子查询中的第一个SELECT语句;

        DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询;

        DERIVED:导出表的SELECT (FROM语句的子查询)。

     

    table:表示查询的表。

     

    type:表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型:

        (1) system

            该表仅有一行的系统表。这是const连接类型的一个特例。

     

        (2) const

            数据表最多只有一个匹配行,它将在查询开始时被读取,并在余下的査询优化中作为常量对待。const表查询速度很快,因为它们只读取一次。const用于使用常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。

            在下面查询中,tb1_name可用const表:

            SELECT  *  from tb1_name WHERE primary_key=1;

            SELECT * from tb1_name WHERE primary_key_part1=1 AND primary_key_part2=2

     

        (3) eq_ref

            对于每个来自前面的表的行组合,从该表中读取一行。当一个索引的所有部分都在查询中使用,并且索引是UNIQUE或者PRIMARY KEY时,即可使用这种类型。

            eq_ref可以用于使用“=”操作符比较带索引的列。比较值可以为常量或者一个在该表前面所读取的表的列的表达式。

            在下面例子中,MySQL可以使用eq_ref来处理ref_tables:

            SELECT * FROM ref_table,other_table WHERE ref_table.key_cloumn = other_table.cloumn;

            SELECT * FROM ref_table, other_tbale WHERE ref_table.key_cloumn_part1 = other_table.cloumn AND ref_table.key_cloumn_part2 = 1;

     

        (4)ref

            对于来自前面的表的任意组合,将从该表中读取所有匹配的行。这种类型用于索引既不是UNIQUE也不是PRIMARY KEY的情况,或者查询中使用了索引列在左子集,既索引中左边的部分列组合。ref可以用于使用=或者<=>操作符的带索引的列。

            以下的几个例子中,mysql将使用 ref 来处理ref_table:   

            select * from ref_table where key_column=expr; 

            select * from ref_table,other_table where ref_table.key_column=other_table.column; 

            select * from ref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;

     

        (5)ref_or_null

            这种连接类型类似ref,不同的是mysql会在检索的时候额外的搜索包含null值的记录。在解决子查询中经常使用该链接类型的优化。

            在以下的例子中,mysql使用ref_or_null 类型来处理 ref_table:

            select * from ref_table where key_column=expr or key_column is null;

     

        (6)index_merge

            该链接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。

     

        (7)unique_subquery

            该类型替换了下面形式的IN子查询的ref:

            value in (select primary_key from single_table where some_expr)

     

        (8)index_subquery

            这种连接类型类似 unique_subquery。可以替换IN子查询,不过它用于在子查询中没有唯一索引的情况下,

            例如以下形式:

            value in (select key_column from single_table where some_expr)

     

        (9)range

            只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。ken_len包含所使用索引的最长关键元素。当使用 =, <>, >,>=, <, <=, is null, <=>, between, 或 in操作符,用常量比较关键字列时,类型为range。

            下面介绍几种检索制定行的情况:

            select * from tbl_name where key_column = 10; 

            select * from tbl_name where key_column between 10 and 20; 

            select * from tbl_name where key_column in (10,20,30); 

            select * from tbl_name where key_part1= 10 and key_part2 in (10,20,30);

     

        (10)index

             连接类型跟ALL一样,不同的是它只扫描索引树。它通常会比ALL快点,因为索引文件通常比数据文件小。

     

        (11)ALL

            对于前面的表的任意行组合,进行完整的表扫描。如果第一个表没有被标识为const的话就不大好了,在其他情况下通常是非常糟糕的。正常地,可以通过增加索引使得能从表中更快的取得记录以避免ALL。

     

    possible_keys

            possible_keys字段是指MySQL在搜索表记录时可能使用哪个索引。如果这个字段的值是NULL,就表示没有索引被用到。这种情况下,就可以检查WHERE子句中哪些字段哪些字段适合增加索引以提高查询的性能。创建一下索引,然后再用explain 检查一下。

     

    key

           key字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候,这个字段的值就是NULL。想要让MySQL强行使用或者忽略在 possible_keys字段中的索引列表,可以在查询语句中使用关键字force index, use index或 ignore index。参考SELECT语法。

     

    key_len

            key_len 字段显示了mysql使用索引的长度。当key 字段的值为NULL时,索引的长度就是NULL。注意,key_len的值可以告诉你在联合索引中MySQL会真正使用了哪些索引。

     

    ref

            表示使用哪个列或常数与索引一起来查询记录。

     

    rows

            显示MySQL在表中进行查询时必须检查的行数。 

     

    Extra

            本字段显示了查询中mysql的附加信息。以下是这个字段的几个不同值的解释

     

            distinct

            MySQL当找到当前记录的匹配联合结果的第一条记录之后,就不再搜索其他记录了。 

     

            not exists

            MySQL在查询时做一个LEFT JOIN优化时,当它在当前表中找到了和前一条记录符合LEFT JOIN条件后,就不再搜索更多的记录了。下面是一个这种类型的查询例子:

            select * from t1 left join t2 on t1.id=t2.id where t2.id is null;

            假使 t2.id 定义为 not null。这种情况下,MySQL将会扫描表 t1并且用 t1.id 的值在 t2 中查找记录。当在 t2中找到一条匹配的记录时,这就意味着 t2.id 肯定不会都是null,就不会再在 t2 中查找相同id值的其他记录了。也可以这么说,对于 t1 中的每个记录,mysql只需要在t2 中做一次查找,而不管在 t2 中实际有多少匹配的记录。

     

            range checked for each record (index map: #)

            mysql没找到合适的可用的索引。取代的办法是,对于前一个表的每一个行连接,它会做一个检验以决定该使用哪个索引(如果有的话),并且使用这个索引来从表里取得记录。这个过程不会很快,但总比没有任何索引时做表连接来得快。

            

            using filesort

            MySQL需要额外的做一遍从已排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录,并且将所有符合where条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了,对应的记录也会按照排好的顺序取出来。详情请看"7.2.9how mysql optimizes order by"。

     

            using index

            字段的信息直接从索引树中的信息取得,而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。

     

            using temporary

            mysql需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了group by和order by子句,它以不同的方式列出了各个字段。

     

            using where

            where子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你特别地想要取得或者检查表种的所有记录,否则的话当查询的extra字段值不是using where并且表连接类型是all或index时可能表示有问题。

       

            如果你想要让查询尽可能的快,那么就应该注意extra字段的值为using filesort和using temporary的情况。    

     

    其他

     

    索引没起作用的情况

     

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

     

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

     

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

     

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

     

    3. 使用OR关键字的查询语句

     

            查询语句的查询条件中只有OR关键字,且OR前后的两个条件中的列都是索引时,查询中才会使用索引。否则,查询将不使用索引。

     

    优化数据库结构

     

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

     

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

            

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

     

    2. 增加中间表

     

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

     

    3. 增加冗余字段

     

            设计数据库表时应尽量遵循范式理论的规约,尽可能减少冗余字段,让数据库设计看起来精致、优雅。但是合理加入冗余字段可以提高查询速度。 

     

    切分查询

            

            比如我们要删除旧的数据,可能需要一次性删除很多数据会锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切成多个较小的查询可以尽可能小的影响MySQL性能,同时还可以减少MySQL复制的延迟,如下:

            DELETE FROM table WHERE create_date < NOW(); 

            替换为

            rows_affected = 0

            do { 

                rows_affected = do_query(

                    "DELETE FROM table WHERE create_date < NOW() LIMIT 10000"    

                )

            } while rows_affected > 0

            一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法,如果每次暂停一会会更好。

     

    分解关联查询

     

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

            

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

            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);

            有很多场景都可以使用:比如当应用能够方便地缓存单个查询的结果的时候、当可以将数据分布到不同MySQL服务器上的时候、当能够使用IN()的方式代替关联查询的时候、当查询中使用同一个数据表的时候。

            

    优化LIMIT分页

     

            在分页偏移量很大的时候,如LIMIT 10000,20这样的查询,MySQL需要查询10020条记录然后只返回最后20条,前面10000条记录都被抛弃,这样代价非常高。

     

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

            SELECT file_id, description FROM film ORDER BY title LIMIT 50, 5;

            修改为:

            SELECT film.film_id, film.description FROM film 
            INNER JOIN (SELCT film_id FROM film ORDER BY title LIMIT 50, 5) AS lim USING(film_id);

            

            这里“延迟关联”将大大提升查询效率,它让MySQL扫描尽可能少的页面,获取需要访问的记录后在根据关联列回原表查询需要的所有列。

    展开全文
  • Mysql慢查询日志的使用 和 Mysql的优化

    万次阅读 多人点赞 2018-02-08 15:17:50
    一、生成实验数据 原理:sql 蠕虫复制(这种生成数据方式同样适用于数据表中有主键的情况)。 insert into comic (name,pen_name,cover) select name,pen_name,cover ...1、临时开启慢查询日志(如果需要长时间...

    一、生成实验数据

    原理:sql 蠕虫复制(这种生成数据方式同样适用于数据表中有主键的情况)。

    insert into comic (name,pen_name,cover) select name,pen_name,cover from comic

     

    二、慢查询日志设置

    当语句执行时间较长时,通过日志的方式进行记录,这种方式就是慢查询的日志。

    1、临时开启慢查询日志(如果需要长时间开启,则需要更改mysql配置文件,第6点有介绍)

    set global slow_query_log = on; 

    注:如果想关闭慢查询日志,只需要执行 set global slow_query_log = off; 即可

     

    2、临时设置慢查询时间临界点  查询时间高于这个临界点的都会被记录到慢查询日志中(如果需要长时间开启,则需要更改mysql配置文件,第6点有介绍)。

    set long_query_time = 1;

    现在起,所有执行时间超过1秒的sql都将被记录到慢查询文件中(我这里就是 /data/mysql/mysql-slow.log)。

     

    3、设置慢查询存储的方式

    set globle log_output = file;

    说明: 可以看到,我这里设置为了file,就是说我的慢查询日志是通过file体现的,默认是none,我们可以设置为table或者file,如果是table则慢查询信息会保存到mysql库下的slow_log表中

     

    4、查询慢查询日志的开启状态和慢查询日志储存的位置

    show variables like '%quer%';

    参数说明:

    slow_query_log : 是否已经开启慢查询

    slow_query_log_file : 慢查询日志文件路径

    long_query_time :  超过多少秒的查询就写入日志 

    log_queries_not_using_indexes 如果值设置为ON,则会记录所有没有利用索引的查询(性能优化时开启此项,平时不要开启)

     

    5、使用慢查询日志示例

    cat -n  /data/mysql/mysql-slow.log

    从慢查询日志中,我们可以看到每一条查询时间高于1s钟的sql语句,并可以看到执行的时间是多少。

    比如上面,就表示 sql语句  select * from comic where comic_id < 1952000;  执行时间为3.902864秒,超出了我们设置的慢查询时间临界点1s,所以被记录下来了。

     

    6、永久设置慢查询日志开启,以及设置慢查询日志时间临界点

    linux中,mysql配置文件一般默认在 /etc/my.cnf

    更改对应参数即可。

     

     

    三、对慢查询日志进行分析

    我们通过查看慢查询日志可以发现,很乱,数据量大的时候,可能一天会产生几个G的日志,根本没有办法去清晰明了的分析。所以,这里,我们采用工具进行分析。

    1、使用mysqldumpslow进行分析第一种方式

    mysqldumpslow -t 10  /data/mysql/mysql-slow.log  #显示出慢查询日志中最慢的10条sql

    注:mysqldumpslow工具还有其他参数,以提供其他功能,这里,只以最基本的-t做了介绍。

     

    2、使用pt-query-digest工具进行分析

    mysqldumpslow是mysql安装后就自带的工具,用于分析慢查询日志,但是pt-query-digest却不是mysql自带的,如果想使用pt-query-digest进行慢查询日志的分析,则需要自己安装pt-query-digest。pt-query-digest工具相较于mysqldumpslow功能多一点。

    (1)安装

    yum install perl-DBI

    yum install perl-DBD-MySQL

    yum install perl-Time-HiRes

    yum install perl-IO-Socket-SSL

    wget percona.com/get/pt-query-digest

    chmod u+x pt-query-digest 

    mv pt-query-digest  /usr/bin/  

     

    (2)查看具体参数作用

    pt-query-digest --help

     

    (3)使用

    pt-query-digest  /data/mysql/mysql-slow.log

    查询出来的结果分为三部分

     第一部分:

    显示出了日志的时间范围,以及总的sql数量和不同的sql数量。

    第二部分:

    显示出统计信息。

    第三部分:

    每一个sql具体的分析

    pct是percent的简写,表示占的百分比

    cout是占总sql个数的百分比,exec time 是占总执行时间的百分比,lock time 表示占总的锁表时间的百分比。

     

    (4)如何通过pt-query-digest 慢查询日志发现有问题的sql

    1)查询次数多且每次查询占用时间长的sql

    通常为pt-query-digest分析的前几个查询

    2)IO消耗大的sql

    注意pt-query-digest分析中的Rows examine项

    3)为命中索引的sql

    注意pt-query-digest分析中Rows examine(扫描行数) 和 Rows sent (发送行数)的对比 ,如果扫描行数远远大于发送行数,则说明索引命中率并不高。

     

    四、对sql进行优化

    1、使用explain查询sql的执行计划

    explain select comic_id,name,pen_name,cover,last_verify_time from comic;

    参数分析:

    table:表示属于哪张数据表

    type:最重要的参数,表示连接使用了何种类型。从最好到最差的连接类型为const,eq_reg,ref,range,index和ALL。

    possible_keys:显示可能应用在这张表中的索引。如果为null,则表示没有可能的索引。

    key:实际使用的索引。如果为null,则表示没有使用索引。

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

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

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

     

    2、count() 和 Max() 的优化方法

    (1)优化前,是没有为last_update_time字段建立索引的情况,查询最大的时间戳

     

    (2)优化后,是为last_update_time字段建立索引的情况,查询最大的时间戳

    create index update_time on comic(last_update_time);

    对比,可以看到,在没有为字段建立索引的情况下,查询时间是11秒多,建立索引之后,查询时间变成0秒了。

    所以总结就是,如果经常用于count和max操作的字段,可以为其添加索引。

    还有,值得注意的地方是:count() 计算时,count(*)会将这一列中的null值但也算进去,而count(comic_id)则不会将null算进去。

     

    3、子查询的优化

    通常情况下,需要把子查询优化为join查询,但在优化时要注意关联键是否有一对多的关系,如果有,是可能会出现重复数据的。所以如果存在一对多关系,则应该使用distinct进行限制。

    例如:

    select t.id from t where t.id in (select k.kid from k);

    优化成:

    select distinct t.id from t join k on t.id = k.kid;

     

    4、group by 的优化

    #待补

    5、limit的优化

     

    五、对索引进行优化

    1、选择合适的列建立索引

    2、索引优化sql的方法

    3、索引维护的方法

     

    六、数据库结构优化

    1、选择合适的数据类型

    2、数据库表的范式化优化

    3、数据库表的反范式优化

    4、数据库表的垂直拆分

    5、数据库表的水平拆分

     

    七、系统配置优化

    1、数据库系统配置优化

    2、Mysql配置文件优化

    3、第三方配置工具使用

     

    八、服务器硬件优化

     

     

     

     

     

    展开全文
  • 浅谈慢查询

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

    千次阅读 2018-08-19 11:37:26
    开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。 二、参数说明 slow_query_log 慢查询开启状态 slow_query_log_file 慢查询日志存放的位置...
  • 浅谈MySQL中优化sql语句查询常用的30种方法 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。  2.应尽量避免在 where 子句中使用!=或&lt;&gt;操作符,否则将...
  • MySQL数据库慢查询问题排查方法

    千次阅读 2019-05-19 13:43:31
    最近碰到了几次数据库响应变慢的问题,整理了一下处理的流程和...一般来说一个正常运行的MySQL服务器,每分钟的慢查询在个位数是正常的,偶尔飙升到两位数也不是不能接受,接近100系统可能就有问题了,但是还能勉...
  • 详解慢查询

    2019-11-01 10:46:19
    查询mysql的操作信息 show status -- 显示全部mysql操作信息 show status like "com_insert%"; -- 获得mysql的插入次数; show status like "com_delete%"; -- 获得mysql的删除次数; show status like ...
  • 慢查询

    2019-05-20 21:31:04
    慢查询: 查询的执行时间 大于 mysql系统 设置的指定时间(long_query_time)的话,该查询就叫做慢查询,会被记录到慢查询日志文件里面。 ( show variables like "long%" show variables like "%slow%"; 查看...
  • 什么是慢查询

    2019-11-30 11:21:39
    什么是慢查询 MySQL默认10秒内没有响应SQL结果,则为慢查询 可以去修改MySQL慢查询默认时间 如何修改慢查询 --查询慢查询时间 show variables like 'long_query_time'; --修改慢查询时间 set long_query_...
  • 一、慢查询分析 慢查询日志帮助开发和运维人员定位系统存在的慢操作。慢查询日志就是系统在命令执行前后计算每条命令的执行时间,当超过预设阀值,就将这条命令的相关信息(慢查询ID,发生时间戳,耗时,命令的详细...
  • MySQL慢查询日志总结

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

    千次阅读 2018-07-15 13:48:40
    和很多关系型数据库(例如:MySQL)一样, Redis 也提供了慢查询日志记录,Redis 会把命令执行时间超过 slowlog-log-slower-than 的都记录在 Reids 内部的一个列表(list)中,该列表的长度最大为 slowlog-max-len ...
  • MySQL慢查询日志分析详解

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

    千次阅读 2019-04-28 21:31:32
    慢查询日志概念 MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,...
  • Mysql慢查询日志以及优化

    千次阅读 2019-06-11 15:01:16
    慢查询日志设置 当语句执行时间较长时,通过日志的方式进行记录,这种方式就是慢查询的日志。 1、临时开启慢查询日志(如果需要长时间开启,则需要更改mysql配置文件) set global slow_query_log = on; 注:...
  • Redis 慢查询日志

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

    千次阅读 2018-05-07 16:32:56
    MySQL慢查询(一) - 开启慢查询一、简介开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。二、参数说明slow_query_log 慢查询开启状态slow_...
  • MySQL中的日志包括:通用查询日志、慢查询日志、错误日志、二进制日志等等。这里主要记录一下两种比较常用的日志:通用查询日志和慢查询日志。 (1)通用查询日志:记录建立的客户端连接和执行的语句。 (2)...
1 2 3 4 5 ... 20
收藏数 388,892
精华内容 155,556
关键字:

慢查询