MySQL性能优化二之SQL的优化

原创 2016年08月29日 10:18:27

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




版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

Unreal Engine 4 道具捡拾教程 C++ 实现

Unreal Engine 4  道具捡拾教程 C++ 实现     物品捡拾是游戏比较常用的功能,这个教程讲解实现简单的物品捡拾的过程。   1、  首先新建C++工程,输入好工程名称,注意选择C+...
  • caldera
  • caldera
  • 2016年10月08日 16:43
  • 1085

join()之让线程按顺序执行

Thread中的join()主线程创建并启动子线程,如果子线程中要进行大量的耗时运算,主线程往往将在子线程运行结束前结束。如果主线程想等待子线程执行完成后再结束(如,子线程处理一个数据,主线程需要取到...

【MySql性能优化二】利用explain进行查询和分析sql语句

在mysql数据库中为我们提供了explain方法可以通过它来帮助我们分析我们的sql语句。 登录mysql后,具体使用如下这里的了例子都以上篇博客中安装的实例数据库sakila为例: explain...

MySQL5.7性能优化系列(二)——SQL语句优化(3)——使用物化策略优化子查询

优化器使用物化策略(Materialization)来实现更有效的子查询处理。通过生成子查询结果作为临时表,通常在内存中,实现加速查询执行。 MySQL首次需要子查询结果,将该结果实现为临时表。任何随...

MySQL5.7性能优化系列(二)——SQL语句优化(2)——使用 Semi-Join半连接变换优化子查询,派生表和视图

优化器使用半连接策略来改进子查询执行,如本节所述。对于两个表之间的内部连接,连接从一个表返回一行多于另一个表中的匹配项。但是对于一些问题,唯一重要的信息是是否有匹配,而不是匹配的数量假设有一个名为cl...

MySQL性能优化 SQL优化方法技巧

  • 2016年05月23日 14:15
  • 1.08MB
  • 下载

【SQL】MySQL性能优化

1. 为查询缓存优化你的查询   大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放...

mysql通过将or改成union来优化sql性能问题一例

某系统测试环境有支SQL执行时间较长,开发人员请求dba协助优化。 原SQL如下: SELECT   g.id,           ----省略-----     FROM    g,  ...

MySQL批量sql插入性能优化

MySQL批量SQL插入性能优化 对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,可能每天花费在数据导入上的时间就会长...
  • hephec
  • hephec
  • 2014年09月11日 20:16
  • 380

mysql sql性能的优化与注意方面

mysql sql性能的优化与注意方面
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL性能优化二之SQL的优化
举报原因:
原因补充:

(最多只允许输入30个字)