亲宝软件园·资讯

展开

mysql慢查询

darkly 人气:0

1、 什么是慢查询

  1.MySQL日志文件系统的组成
       1)错误日志:记录启动、运行或停止mysqld时出现的问题。
       2)通用日志:记录建立的客户端连接和执行的语句。
       3)更新日志:记录更改数据的语句。该日志在MySQL 5.1中已不再使用。
       4)二进制日志:记录所有更改数据的语句。还用于复制。
       5)慢查询日志:记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

  2.慢查询:全名慢查询日志,用来记录在MySQL中响应时间超过阀值的语句。

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

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

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

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

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

# ON为开启慢查询日志,OFF则为关闭慢查询日志
slow_query_log 

# 指定了慢查询的阈值,即如果执行语句的时间超过该阈值则为慢查询语句,默认值为10秒
long_query_time

# 记录的慢查询日志到文件中(注意:默认名为主机名.log,慢查询日志是否写入指定文件中,需要指定慢查询的输出日志格式为文件,
相关命令为:show variables like ‘%log_output%’;去查看输出的格式) slow_query_log_file # 这个参数设置为ON,可以捕获到所有未使用索引的SQL语句(注意:如果只是将log_queries_not_using_indexes设置为ON,
而将slow_query_log设置为OFF,此时该设置也不会生效,即该设置生效的前提是slow_query_log的值设置为ON) log_queries_not_using_indexes

2、分析慢查询

  1.查看慢查询日志开启情况

mysql> show variables like '%quer%';
+-------------------------------+--------------------------+
| Variable_name                 | Value                    |
+-------------------------------+--------------------------+
| expensive_subquery_limit      | 100                      |
| ft_query_expansion_limit      | 20                       |
| have_query_cache              | YES                      |
| log_queries_not_using_indexes | OFF                      |
| long_query_time               | 10.000000                |
| query_alloc_block_size        | 16384                    |
| query_cache_limit             | 1048576                  |
| query_cache_min_res_unit      | 4096                     |
| query_cache_size              | 1048576                  |
| query_cache_strip_comments    | OFF                      |
| query_cache_type              | OFF                      |
| query_cache_wlock_invalidate  | OFF                      |
| query_prealloc_size           | 24576                    |
| slow_query_log                | OFF                      |
| slow_query_log_file           | LAPTOP-M0LH1533-slow.log |
+-------------------------------+--------------------------+
15 rows in set (0.00 sec)

  如上述,log_queries_not_using_indexes、slow_query_log、slow_query_log_file 就是慢查询日志。

  2.查询当前慢查询的语句的个数

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 0     |
+---------------------+-------+
2 rows in set (0.00 sec)

  3.如何开启慢查询

    1)在服务器上找到mysql的配置文件my.cnf , 然后再mysqld模块里追加一下内容。(需要重启)

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

    2)通过修改mysql的全局变量来处理(不需要重启,不过重启后失效)

#开启慢查询功能,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';

  4.慢查询日志内容及字段意义

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;
--------------------------------------------------------------------------------------------------------------
- Time:SQL查询执行的时间 
- User@Host:执行SQL查询的连接信息,用户和连接IP 
- Query_time,这条SQL执行的时间,越长则越慢
    - Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
    - Rows_sent,查询返回的行数
    - Rows_examined,查询检查的行数,越长就当然越费时间
SET timestamp:设置时间戳,没有实际意义,只是和Time对应执行时间。
最后,执行的sql语句记录信息,因为sql可能会很长。

  5.如何解决慢查询问题

    要解决慢查询,就是优化这些查询缓慢的语句,或是重新组织自己的数据。

    重新组织数据的表现形式是分表。对于成熟的业务系统而言,分表的代价是极高的。所以如何组织一张表仍然是建表的重要决策。

    因此,优化语句才是解决慢查询的基本方法。

  6.优化语句

    1)索引。为合适的列添加合适的索引往往能有效解决问题。

    关于索引有一个需要注意的地方,即,使用LIKE关键字时,如果匹配字符串的第一个字符为'%',那么索引不会起作用。'%'必须不能再第一个位置。

    2)通过explain命令获取语句的执行计划,以了解到MySQL是否使用了预期的索引来帮助查询。

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

    例如:explain select surname,first_name form a,b where a.id=b.id

    3)分解关联查询。

    将一个大的查询分解为多个小查询。尽量多的采用单表查询。多表的连接可以分解到应用中去做。

    4)分页。

    分页是一个常见的情景。分页通常会使用limit加上偏移量的方法实现,同时加上合适的order by 子句。

    如果有对应的索引,通常效率会不错,否则MySQL需要做大量的文件排序操作。

    但是,当偏移量非常大的时候,例如可能是limit 10000,20这样的查询,MySQL需要查询10020条,但是只返回最后20条,这样的代价很高。

    优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。

    对于偏移量很大的时候这样做的效率会得到很大提升。

    例:select id,title from collect limit 10000,10;
    该语句存在的最大问题在于limit M,N中偏移量M太大(暂不考虑筛选字段上要不要添加索引的影响),导致每次查询都要先从整个表中找到满足条件 的前M条记录,之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大,且筛选字段没有合适的索引,且M特别大那么这样的代价是非常高的。如果我们下一次的查询能从前一次查询结束后标记的位置开始查找,找到满足条件的100条记录,并记下下一次查询应该开始的位置,以便于下一次查询能直接从该位置开始,这样就不必每次查询都先从整个表中先找到满足条件的前M条记录,舍弃,在从M+1开始再找到100条满足条件的记录了。比如说,先查询出10000条数据对应的主键id的值,然后通过该id的值直接查询该id后面的数据。
    例: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表的这条记录。

    5)exists查询的弊端

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



加载全部内容

相关教程
猜你喜欢
用户评论