关闭

MySQL性能优化二之SQL的优化

标签: MySQL数据库
178人阅读 评论(0) 收藏 举报
分类:

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(查询的执行时间、锁定的时间、所发生的行数、所扫描的行数)

4SET timestamp=1472266684;(以时间戳的格式记录执行的时间)

5select * 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 filesortUsing 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.优化例子:

优化前:


优化后:


备注:

1ON子句的语法格式为:table1.column_name = table2.column_name;当模式设计对联接表的列采用了相同的命名样式时,就可以使用USING 语法来简化ON 语法,格式为:USING(column_name)

9.Limit的优化

9.1.常用语分页处理,后面一般会结合order by从句使用,因此大多时候会使用filesorts这样会造成大量的IO问题。

9.2.一般对主键进行排序




0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:20657次
    • 积分:749
    • 等级:
    • 排名:千里之外
    • 原创:55篇
    • 转载:5篇
    • 译文:0篇
    • 评论:2条
    最新评论