慢查询日志概念
MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。
默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。
慢查询日志相关参数
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'表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。
一,第一步.开启mysql慢查询
方式一:
修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间,以及慢查询log日志记录( slow_query_log)
方法二:通过MySQL数据库开启慢查询:
二,分析慢查询日志
直接分析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 使用的索引
三,常见的慢查询优化
(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
日志分析工具mysqldumpslow
在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具mysqldumpslow
查看mysqldumpslow的帮助信息:
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are --verbose verbose --help write this text to standard output -v verbose -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default ar: average rows sent c: count r: rows sent -r reverse the sort order (largest last instead of first) -a don't abstract all numbers to N and strings to 'S' -g PATTERN grep: only consider stmts that include this string default is '*', i.e. match all -l don't subtract lock time from total time
-s, 是表示按照何种方式排序,
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t, 是top n的意思,即为返回前面多少条的数据;
-g, 后边可以写一个正则匹配模式,大小写不敏感的;
比如:
得到返回记录集最多的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