常见 MySQL 的慢查询优化方式！

公众号后台回复“面试”，获取精品学习资料

扫描下方海报了解专栏详情

本文来源：Java后端技术栈

《Java工程师面试突击（第3季）》重磅升级，由原来的70讲增至160讲，内容扩充一倍多，升级部分内容请参见文末

• 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'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000

• table   显示这一行的数据是关于哪张表的           
• type    这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
• rows   显示需要扫描行数
• key     使用的索引

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

  select id,title from collect limit 90000,10;

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

  Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

select * from acct_trans_log WHERE  acct_id = 3095  order by create_time desc limit 0,10

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

参考：

https://www.cnblogs.com/davidwang456/articles/9497584.html
https://www.cnblogs.com/dalongNOTES/p/10769837.html

END

《Java工程师面试突击第三季》加餐部分大纲：（注：1-66讲的大纲请扫描文末二维码，在课程详情页获取）

详细的课程内容，大家可以扫描下方二维码了解：