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.查看慢查询日志开启情况
MariaDB [(none)]> 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.查询当前慢查询的语句的个数
MariaDB [(none)]> 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表中查（外表到里表中），顺序是固定死的。