1.怎么发现有问题的SQL?(通过MySQL慢查询日志对有效率问题的SQL进行监控)
1.1.查看慢查询的开启状态:show variables like‘slow_query_log’
1.2.设置慢查询文件的存储位置:set global slow_query_log_file =‘位置’
1.3.是否要把没有使用索引的SQL记录:set global log_queries_not_using_indexes = on
1.4.执行时间超过多少秒记录下来:set global long_query_time = 1
1.5.查看慢查询中日志设置的情况:show variables like‘%log%’
1.6.查看慢日志中执行时间记录情况:show variable like‘long_query_time’
1.7.开启慢查询:set global slow_query_log = on
1.8.查看慢查询记录的位置:show varialbes like‘slow%'
1.9.慢查询日志的格式(五个部分)
1)# Time: 160827 10:58:04(执行时间)
2)# User@Host: root[root] @ localhost [127.0.0.1](用户和主机信息)
3)# Query_time: 0.097006 Lock_time: 0.071005 Rows_sent: 4 Rows_examined: 4(查询的执行时间、锁定的时间、所发生的行数、所扫描的行数)
4)SET timestamp=1472266684;(以时间戳的格式记录执行的时间)
5)select * from t_user;’(执行的语句内容)
2.MySQL慢查询分析工具mysqldumpslow(安装好MySQL后自带)
2.1.简单用法
1)mysqldumpslow [-s ORDER] [-t] 路径
注明:-s(根据什么排序);-t(显示多少条记录)
3.MySQL慢查询分析工具pt_query_digest(更完善更具体)
3.1.通过pt_query_disgest --help查看常用的命令
3.2.pt_query_disgest --help 路径
3.3.结果分为三部分:头(日志的时间范围,SQL数量)、SQL的统计信息(次数,执行时间)、SQL的内容
4.如何通过慢日志发现有问题的SQL
4.1.查询出执行的次数多占用的时间长的SQL
4.2.IO大的SQL(注意pt_query_disgest分析中的Rows examine项)
4.3.未命中索引的SQL(注意pt_query_disgest分析中的Rows examine(扫描的行数)项和 Rows Send(发送的行数)的对比);如果扫描的函数远远大于实际发送的函数则说明索引命中率不高,基本使用比较扫描的方式查询。
5.通过explain查询和分析SQL的执行计划
5.1.语法:explain SQL
5.2.返回各列的含义例如:
当扩展列extra出现Using filesort和Using temporay则表示SQL需要优化了(使用了临时表和文件排序的方式)。
3.Count()和Max()的优化例如:
6.1.Max()
6.1.1.这个查询没有使用索引,利用的是扫描的方式进行查询当行数过多IO过大时候时间会很久效率低,此时建立一个索引create index idex_paydate on payment(pay_date)
6.2.再次通过explain分析SQL
6.2.1此时不需要通过查询表的数据,通过索引知道执行结果,不需要表的操作。
6.3.Count()
6.3.1.count(*)和count(id)返回的结果不一样前者会计算空值后者不会。
7.子查询的优化
7.1.在使用join连接时候是否存在一对多的关系,存在会返回多行数据就存在去重的问题(DISTINCT)。
8.group by的优化
8.1.group by 最好使用同一表中的字段
8.2.优化例子:
优化前:
优化后:
备注:
1)ON子句的语法格式为:table1.column_name = table2.column_name;当模式设计对联接表的列采用了相同的命名样式时,就可以使用USING 语法来简化ON 语法,格式为:USING(column_name)
9.Limit的优化
9.1.常用语分页处理,后面一般会结合order by从句使用,因此大多时候会使用filesorts这样会造成大量的IO问题。
9.2.一般对主键进行排序