关闭

MySQL性能优化二之SQL的优化

标签: MySQL数据库
321人阅读 评论(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
查看评论

MySQL优化之——视图

使用视图的理由是什么? 1、安全性:一般是这样做的:创建一个视图,定义好该视图所操作的数据。 之后将用户权限与视图绑定,这样的方式是使用到了一个特性:grant语句可以针对视图进行授予权限。 2、查询性能提高 3、有灵活性的功能需求后,需要改动表的结构而导致工作量比较大,那么可以使用虚拟表的形式达到...
  • l1028386804
  • l1028386804
  • 2015-07-05 09:58
  • 1983

高级SQL优化(二) ——《12年资深DBA教你Oracle开发与优化——性能优化部分》

充分利用索引 索引的限制 1. 索引对不等号和NOT的限制   如果WHERE条件中出现!=或者 Oracle 10g起,在基于CBO的优化器模式下Oralce会进行自动优化,但在基于RBO(基于规则)的优化器模式下,依然保持此规则。   ...
  • holandstone
  • holandstone
  • 2016-05-21 23:36
  • 2011

性能调优之MYSQL高并发优化

一、数据库结构的设计 表的设计具体注意的问题: 1、数据行的长度不要超过8020字节,如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片,降低查询效率。 2、能够用数字类型的字段尽量选择数字类型而不用字符串类型的(电话号码),这会降低查询和连接的性能,并会...
  • qq_14926159
  • qq_14926159
  • 2017-01-23 17:47
  • 945

MYSQL性能优化的最佳20+条经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能。这里,我们不会讲过多的SQL语句的优...
  • u014066037
  • u014066037
  • 2017-01-03 09:04
  • 1300

一些mysql数据库性能优化方法

一、MySQL 数据库性能优化之SQL优化 注:这篇文章是以 MySQL 为背景,很多内容同时适用于其他关系型数据库,需要有一些索引知识为基础 优化目标 减少 IO 次数 IO永远是数据库最容易瓶颈的地方,这是由数据库的职责所决定的,大部分数据库操作中超过90%的时间都是...
  • benpaobagzb
  • benpaobagzb
  • 2015-08-27 22:42
  • 1655

【原创】MySQL介绍和性能优化 (PPT/PDF)

MySQL介绍和性能优化作者:heiyeluren(黑夜路人)时间:2009-07-28博客:http://blog.csdn.net/heiyeshuwu Overview 什么是MySQL?   MySQL是一个小型关系型数据库管理系统,开发者为瑞典MySQL AB公司。目前...
  • heiyeshuwu
  • heiyeshuwu
  • 2009-07-28 15:15
  • 5307

MySQL系列—如何优化你的 SQL SELECT 语句性能

SELECT语句的性能调优有时是一个非常耗时的任务,在我看来它遵循帕累托原则。20%的努力很可能会给你带来80%的性能提升,而为了获得另外20%的性能提升你可能需要花费80%的时间。除非你在金星工作,那里的每一天都等于地球上的243天,否则交付期限很有可能使你没有足够的时间来调优SQL查询。 遵循下...
  • u012758088
  • u012758088
  • 2016-07-31 18:03
  • 1118

慕客网-MySql性能优化视频

http://www.imooc.com/view/194?from=csdn
  • zhpengfei0915
  • zhpengfei0915
  • 2014-10-27 10:59
  • 2027

MySQL-SQL插入性能优化

对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此,优化数据库插入性能是很有意义的。 经过对MySQL innodb的一些性能测试,发现一些可以提高insert效率的方法,供大家参考参...
  • alexdamiao
  • alexdamiao
  • 2016-06-28 22:27
  • 8427

MySQL性能优化——易实现的MySQL优化方案汇总

1、索引优化及索引失效情况汇总2、表结构优化小技巧3、临时表的优化4、其它优化技巧
  • zhangliangzi
  • zhangliangzi
  • 2016-08-26 16:44
  • 4541
    个人资料
    • 访问:33229次
    • 积分:833
    • 等级:
    • 排名:千里之外
    • 原创:60篇
    • 转载:3篇
    • 译文:0篇
    • 评论:3条
    最新评论