mysql慢查询
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;
select * from a where id in (select id from b );
select * from a where exists(select * from b where b.id=a.id );
5)exists查询的弊端
由exists执行原理可知,a表(外表)使用不了索引,必须全表扫描,因为是拿a表的数据到b表查。而且必须得使用a表的数据到b表中查(外表到里表中),顺序是固定死的。