Mysql效率优化定位较低sql的两种方式

关于mysql效率优化一般通过以下两种方式定位执行效率较低的sql语句。

通过慢查询日志定位那些执行效率较低的 SQL 语句，用 --log-slow-queries[=file_name] 选项启动时， mysqld 会 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件，通过查看这个日志文件定位效率较低的 SQL 。

慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的 执行情况，同时对一些锁表操作进行优化。

下面我们举例说明一下，如何通过慢查询日志定位执行效率底的 SQL 语句：

开启慢查询日志 , 配置样例：

log-slow-queries

在 my.cnf 配置文件中增加上述配置项并重启 mysql 服务，这时 mysql 慢查询功能生效。慢查询 日志将写入参数 DATADIR （数据目录）指定的路径下，默认文件名是 host_name-slow.log 。

和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。下例中演示了慢查询日志的设置和读取过程。

（ 1 ）首先查询一下 long_query_time 的值 。

mysql> show variables like 'long%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 10 |
+-----------------+-------+
1 row in set (0.00 sec)

（ 2 ）为了方便测试，将修改慢查询时间为 5 秒。

mysql> set long_query_time=5;
Query OK, 0 rows affected (0.02 sec)

（ 3 ）依次执行下面两个查询语句。

第一个查询因为查询时间低于 5 秒而不会出现在慢查询日志中：

mysql> select count(*) from order2008;
+----------+
| count(*) |
+----------+
| 208 |
+----------+
1 row in set (0.00 sec)

第二个查询因为查询时间大于 5 秒而应该出现在慢查询日志中：

mysql> select count(*) from t_user;
+----------+
| count(*) |
+----------+
| 6552961 |
+----------+
1 row in set (11.07 sec)

（ 4 ）查看慢查询日志。

[root@localhost mysql]# more localhost-slow.log
# Time: 081026 19:46:34
# User@Host: root[root] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961
select count(*) from t_user;

从上面日志中，可以发现查询时间超过 5 秒的 SQL ，而小于 5 秒的则没有出现在此日志中。
如果慢查询日志中记录内容很多，可以使用 mysqldumpslow 工具（ MySQL 客户端安装自带）来对慢查询日志进行分类汇总。下例中对日志文件 mysql_master-slow.log 进行了分类汇总，只显示汇总后摘要结果：

[root@mysql_master mysql_data]# mysqldumpslow mysql_master-slow.log
Reading mysql slow query log from mysql_master-slow.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql_master
select count(N) from t_user;

对于 SQL 文本完全一致，只是变量不同的语句， mysqldumpslow 将会自动视为同一个语句进行统计，变量值用 N 来代替。这个统计结果将大大增加用户阅读慢查询日志的效率，并迅速定位系统的 SQL 瓶颈。

注意：慢查询日志对于我们发现应用中有性能问题的 SQL 很有帮助，建议正常情况下，打开此日志并经常查看分析。

以上是给大家介绍的Mysql效率优化定位较低sql的两种方式 ，希望以上所述对大家有所帮助。

赞 (0)

相关推荐

  • 详解MySQL性能优化（二）

    详解MySQL性能优化（二）

    接着上一篇学习:http://www.jb51.net/article/70528.htm 七.MySQL数据库Schema设计的性能优化 高效的模型设计 适度冗余-让Query尽两减少Join 大字段垂直分拆-summary表优化 大表水平分拆-基于类型的分拆优化 统计表-准实时优化 合适的数据类型 时间存储格式总类并不是太多,我们常用的主要就是DATETIME,DATE和TIMESTAMP这三种了.从存储空间来看TIMESTAMP最少,四个字节,而其他两种数据类型都是八个字节,多了一倍.而T

  • 大幅优化MySQL查询性能的奇技淫巧

    大幅优化MySQL查询性能的奇技淫巧

    回顾 MySQL / InnoDB 的改善历史.你能很容易发现.在MySQL 5.6稳定版本中从来没有在read-only 这么快的提速,它很容易搞懂,以及在read-only(RO)有着良好的扩张性.也很期待它在read+write(RW)上达到一个较高水平.(特别是在读取数据是数据库主要工作的时候) 然而.我们对于RO在 MySQL 5.6的表现也十分的高兴,在5.7这个版本中,主要工作集中在 read+write (RW)上, 因为在大数据的处理上还没能达到我们的期望.但是RW依赖RO下.

  • 浅谈MySQL中优化sql语句查询常用的30种方法

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描. 3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from

  • MySQL查询优化之explain的深入解析

    在分析查询性能时,考虑EXPLAIN关键字同样很管用.EXPLAIN关键字一般放在SELECT查询语句的前面,用于描述MySQL如何执行查询操作.以及MySQL成功返回结果集需要执行的行数.explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们查询,让查询优化器能够更好的工作. 一.MySQL 查询优化器是如何工作的MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行.最终目标是提交 SEL

  • MySQL Order by 语句用法与优化详解

    MySQL Order By keyword是用来给记录中的数据进行分类的.MySQL Order By Keyword根据关键词分类ORDER BY keyword是用来给记录中的数据进行分类的. 复制代码 代码如下: SELECT column_name(s) FROM table_name ORDER BY column_name 例子 SQL创建代码: 复制代码 代码如下: CREATE TABLE IF NOT EXISTS mysql_order_by_test (  uid int

  • mysql优化limit查询语句的5个方法

    mysql的分页比较简单,只需要limit offset,length就可以获取数据了,但是当offset和length比较大的时候,mysql明显性能下降 1.子查询优化法 先找出第一条数据,然后大于等于这条数据的id就是要获取的数据 缺点:数据必须是连续的,可以说不能有where条件,where条件会筛选数据,导致数据失去连续性,具体方法请看下面的查询实例: 复制代码 代码如下: mysql> set profiling=1; Query OK, 0 rows affected (0.00

  • MySQL优化之对RAND()的优化方法

    众所周知,在MySQL中,如果直接 ORDER BY RAND() 的话,效率非常差,因为会多次执行.事实上,如果等值查询也是用 RAND() 的话也如此,我们先来看看下面这几个SQL的不同执行计划和执行耗时. 首先,看下建表DDL,这是一个没有显式自增主键的InnoDB表: 复制代码 代码如下: [yejr@imysql]> show create table t_innodb_random\G *************************** 1. row **************

  • MYSQL IN 与 EXISTS 的优化示例介绍

    优化原则:小表驱动大表,即小的数据集驱动大的数据集. ############# 原理 (RBO) ##################### select * from A where id in (select id from B) 等价于: for select id from B for select * from A where A.id = B.id 当B表的数据集必须小于A表的数据集时,用in优于exists. select * from A where exists (selec

  • 浅析Mysql Join语法以及性能优化

    浅析Mysql Join语法以及性能优化

    一．Join语法概述 join 用于多表中字段之间的联系,语法如下: 复制代码 代码如下: ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona table1:左表:table2:右表. JOIN 按照功能大致分为如下三类: INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录. LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录. RIGHT JOIN

  • MySQL优化GROUP BY方案

    执行GROUP BY子句的最一般的方法:先扫描整个表,然后创建一个新的临时表,表中每个组的所有行应为连续的,最后使用该临时表来找到组并应用聚集函数(如果有聚集函数).在某些情况中,MySQL通过访问索引就可以得到结果,而不用创建临时表.此类查询的 EXPLAIN 输出显示 Extra列的值为 Using index for group-by. 一. 松散索引扫描 1.满足条件 查询针对一个表.  GROUP BY 使用索引的最左前缀.  只可以使用MIN()和MAX()聚集函数,并且它们均指向相

  • 探究MySQL优化器对索引和JOIN顺序的选择

    本文通过一个案例来看看MySQL优化器如何选择索引和JOIN顺序.表结构和数据准备参考本文最后部分"测试环境".这里主要介绍MySQL优化器的主要执行流程,而不是介绍一个优化器的各个组件(这是另一个话题). 我们知道,MySQL优化器只有两个自由度:顺序选择:单表访问方式:这里将详细剖析下面的SQL,看看MySQL优化器如何做出每一步的选择. explain select * from employee as A,department as B where A.LastName = '

  • mysql数据库优化总结(心得)

    mysql数据库优化总结(心得)

     1. 优化你的MySQL查询缓存在MySQL服务器上进行查询,可以启用高速查询缓存.让数据库引擎在后台悄悄的处理是提高性能的最有效方法之一.当同一个查询被执行多次时,如果结果是从缓存中提取,那是相当快的.但主要的问题是,它是那么容易被隐藏起来以至于我们大多数程序员会忽略它.在有些处理任务中,我们实际上是可以阻止查询缓存工作的. 复制代码 代码如下: // query cache does NOT work$r = mysql_query("SELECT username FROM user W

  • MySQL性能优化之table_cache配置参数浅析

    MySQL性能优化之table_cache配置参数浅析

    table_cache是一个非常重要的MySQL性能参数,它在5.1.3之后的版本中叫做table_open_cache.table_cache主要用于设置table高速缓存的数量.由于每个客户端连接都会至少访问一个表,因此此参数的值与max_connections有关. 缓存机制 当某一连接访问一个表时,MySQL会检查当前已缓存表的数量.如果该表已经在缓存中打开,则会直接访问缓存中的表已加快查询速度:如果该表未被缓存,则会将当前的表添加进缓存并进行查询. 在执行缓存操作之前,table_ca

  • MySQL数据库优化详解

    mysql表复制 复制表结构+复制表数据 mysql> create table t3 like t1; mysql> insert into t3 select * from t1; mysql索引 ALTER TABLE用来创建普通索引.UNIQUE索引或PRIMARY KEY索引 ALTER TABLE table_name ADD INDEX index_name (column_list) ALTER TABLE table_name ADD UNIQUE (column_list)

  • MySQL性能优化之Open_Table配置参数的合理配置建议

    在MySQL数据库中,Opened_tables表示打开过的表数量,下面将对MySQL Open_Table的合理配置作详细的说明介绍. MySQL Opened_tables表示打开过的表数量,下文就将教您如何合理配置MySQL Open_Table的值,希望对您学习MySQL数据库能有所帮助. MySQL Open_Table情况: 复制代码 代码如下: mysql> show global status like 'open%tables%';  +---------------+----

  • MySQL5.6基本优化配置

    随着 大量默认选项的改进, MySQL 5.6比以前版本需要调优的选项大为减少. 在本文中我将讲述需要优化的配置项. InnoDB设置 1.innodb_buffer_pool_size  -- 默认值为 128M. 这是最主要的优化选项,因为它指定 InnoDB 使用多少内存来加载数据和索引(data+indexes). 针对专用MySQL服务器,建议指定为物理内存的 50-80%这个范围. 例如,拥有64GB物理内存的机器,缓存池应该设置为50GB左右. 如果将该值设置得更大可能会存在风险,

  • 21条MySQL优化建议（经验总结）

    今天一个朋友向我咨询怎么去优化 MySQL,我按着思维整理了一下,大概粗的可以分为21个方向. 还有一些细节东西(table cache, 表设计,索引设计,程序端缓存之类的)先不列了,对一个系统,初期能把下面做完也是一个不错的系统. 1. 要确保有足够的内存 数据库能够高效的运行,最关建的因素需要内存足更大了,能缓存住数据,更新也可以在内存先完成.但不同的业务对内存需要强度不一样,一推荐内存要占到数据的15-25%的比例,特别的热的数据,内存基本要达到数据库的80%大小. 2. 需要更多更快的

  • MySQL中数据类型相关的优化办法

    有助于效率的类型选择 1.使你的数据尽可能小 最基本的优化之一是使你的数据(和索引)在磁盘上(并且在内存中)占据的空间尽可能小.这能给出巨大的改进,因为磁盘读入较快并且通常也用较少的主存储器.如果在更小的列上做索引,索引也占据较少的资源. 你能用下面的技术使表的性能更好并且使存储空间最小: ·尽可能地使用最有效(最小)的类型.MySQL有很多节省磁盘空间和内存的专业化类型. ·如果可能使表更小,使用较小的整数类型.例如,MEDIUMINT经常比INT好一些. ·如果可能,声明列为NOT NULL

  • MySQL优化必须调整的10项配置

    当我们被人雇来监测MySQL性能时,人们希望我们能够检视一下MySQL配置然后给出一些提高建议.许多人在事后都非常惊讶,因为我们建议他们仅仅改动几个设置,即使是这里有好几百个配置项.这篇文章的目的在于给你一份非常重要的配置项清单. 我们曾在几年前在博客里给出了这样的建议,但是MySQL的世界变化实在太快了!写在开始前-即使是经验老道的人也会犯错,会引起很多麻烦.所以在盲目的运用这些推荐之前,请记住下面的内容: 一次只改变一个设置!这是测试改变是否有益的唯一方法. 大多数配置能在运行时使用SET

随机推荐