MySQL性能优化二之SQL的优化

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.一般对主键进行排序