`
uule
  • 浏览: 6348598 次
  • 性别: Icon_minigender_1
  • 来自: 一片神奇的土地
社区版块
存档分类
最新评论

SQL优化-慢查询+explain查询

 
阅读更多

利用EXPLAIN分析sql语句的性能

mysql 优化sql语句的几种方法

大量实例让你明白SQL分析命令explain的使用 

 

慢查询 

默认情况下,MySQL数据库并不启动慢查询日志

slow_query_log :是否开启慢查询日志,1表示开启,0表示关闭。

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效,如果MySQL重启后则会失效。如果要永久生效,就必须修改配置文件。

 

linux配置文件(/etc/my.cnf)windows的my.ini文件 的[mysqld]中增加

 

log-slow-queries=/var/log/mysql/slowquery.log(指定日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log)

long_query_time=2(记录超过的时间,默认为10s)

log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)

log-long-format (如果设置了,所有没有使用索引的查询也将被记录)

  

查看慢查询

使用mysql自带命令 mysqldumpslow 查看慢查询:

 

mysqldumpslow -s c -t 20 -g "left join" host-slow.log

-s,表示按照何种方式排序,c,t,l,r分别表示按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒序;

-t,是top n的意思,即为返回前面多少条的数据;

-g,后边可以写一个正则匹配模式,大小写不敏感的;

 

 mysqldumpslow -s r -t 20 host-slow.log

  上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

 mysqldumpslow -t 10 -s t -g “left join” host-slow.log

这个是按照时间返回前10条里面含有左连接的sql语句。

 

 

explain

使用explain分析SQL.在 explain的帮助下,您就知道什么时候该给表添加索引

用法:explain  select … from … where …

 

 显示结果分析:

id | select_type | table |  type | possible_keys | key |key_len  | ref | rows | Extra 

 

a)、id

表示查询中执行select子句或操作表的顺序

id相同,执行顺序由上至下

id不同,【如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行】。

id相同不相同, 结合前面两种情况,id如何相同,可以认为是一组,从上往下执行;在所有组中,id值越大,优先级越高,越先执行。

 

b)、select_type

select语句的查询类型,主要是用于区分【普通查询、联合查询、子查询】等复杂查询。有六种情况:

 

SIMPlE(simple):简单select查询,查询中不包含子查询或者UNION。

PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为此值。

SUBQUERY:在select或者where列表中包含了子查询

DERIVERD:在FROM列表中包含的子查询被标记为DERIVED(衍生)。MySQL会递归执行这些子查询,把结果放在临时表里。

SELECT cr.cname 
FROM  ( SELECT * FROM course WHERE tid IN ( 1, 2 ) ) cr

 course表会被标记为DERIVED

 

UNION:若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVERD。

UNION RESULT:从UNION表中获取结果的SELECT。

 

c)、table 

显示这一行的数据是关于哪张表的 

d)、type 

【这是重要的列,显示连接使用了何种类型】

从最好到最差的连接类型为 system > const > eq_reg > ref > range > index > ALL 

 

system:表中只有一条记录(等于系统表),这是const类型的特列,平时不会出现。

const表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如果主键置于where列表中,mysql就能将该查询转换为一个常量。 

仅仅能查到一条数据的SQL,用于Primary key或unique索引。

【如果只是能查到一条数据,但是条件列上没有Primary key或unique索引,则不是const】

 

eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

ref:非唯一性索引扫描,返回匹配摸个单独值得所有行。本质上也是一种索引访问,它返回所有匹配某个单独值得行,然后,它可能会找到多个符合条件的行,索引他应该属于查找和扫描的混合体

rang:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引(一般就是在where语句中出现了between、<、>、in等查询)。这种范围索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。

 

index:Full Index  Scan,

index与all区别为index类型只遍历索引树。这通常比all快,因为索引文件通常比数据文件小。(也就是说虽然all和index都是读取全表,但index是从索引中读取的,而all是从硬盘中读的)

all:Full Table San,将遍历全表以找到匹配的行。

 

注:一般来说,保证查询至少达到rang级别,最好能达到ref。

  

e)、possible_keys 

显示可能应用在这张表中的索引】。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 

f)、key 

实际使用的索引。【如果为NULL,则没有使用索引】。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引 

g)、key_len 

使用的索引的长度。在不损失精确性的情况下,长度越短越好 

 

h)、ref 

显示索引的哪一列被使用了,如果可能的话,是一个常数 

i)、rows 

大致估计出找到所需的记录所需要读取的行数

 

j)、Extra 

额外信息。

 

using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySql中无法利用索引完成的排序操作称为“文件排序”。避免这种情况。

using temporary:使用了临时表保存中间结果,MySql在对查询结果排序时使用临时表。【常见于排序查询order by和分组查询group by】。避免这种情况

using index:表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错。如果同时出现using where,表示索引被用来执行索引键值得查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。

 

using where:表明使用了where过滤。

using join buffer:使用了连接缓存。

impossible where:where子句的值总是false,不能用来获取任何元组。

 

distinct:优化distinct操作,在找到第一个匹配的元组后即停止找同样值得动作。

分享到:
评论

相关推荐

    18 SQL优化--查询的优化.doc

    #### 一、SQL优化概述与步骤 SQL优化是提高数据库系统性能的关键环节之一。本文档将详细介绍如何通过分析SQL语句执行情况来优化查询效率,包括如何利用MySQL的状态变量来评估查询行为,以及如何定位并解决低效SQL...

    mysql性能优化-慢查询分析、优化索引和配置.doc

    2. **使用EXPLAIN分析查询**:通过EXPLAIN命令来分析SQL查询的执行计划,找出低效的查询语句。 3. **调整MySQL内部配置**:根据服务器硬件情况和应用需求调整MySQL的配置参数,以提高系统整体性能。 #### 二、查询...

    SQL优化-系列文档

    在SQL优化领域,Oracle数据库是业界广泛使用的高性能关系型数据库之一。这个“SQL优化-系列文档”涵盖了关于如何提升Oracle数据库性能的各种策略和技术,对于数据库管理员(DBA)和开发人员来说,无论你是初学者还是...

    mysql性能优化-慢查询分析、优化索引和配置.docx

    本文主要围绕慢查询分析、优化索引和配置调整三个方面展开。 一、优化概述 优化MySQL数据库主要关注CPU和I/O瓶颈。当CPU负荷过高,可能是因为数据加载到内存或从硬盘读取数据时过于频繁。而I/O瓶颈通常发生在内存...

    探讨:MySQL中如何查询当前正在运行的SQL语句

    为了分析这些慢查询并优化它们,可以使用`EXPLAIN`命令来查看其执行计划。`EXPLAIN`提供了一个预览,显示了MySQL如何处理SQL语句,包括表扫描方式、索引使用、排序和临时表等信息。 例如,如果你有一个慢查询的SQL...

    mysql面试题-mysql经典面试题目-数据库的基本概念-SQL语法-事务处理-索引优化-性能调优-mysql-面试题目

    - 使用EXPLAIN分析SQL查询来评估索引使用情况。 5. **性能调优**: - 查询优化涉及选择最佳执行计划,避免全表扫描,利用索引。 - 使用慢查询日志找出性能瓶颈。 - 调整数据库配置参数,如缓冲池大小、最大连接...

    sql优化 联表查询

    在SQL优化领域,联表查询(也称为JOIN查询)是数据操作的重要部分,尤其是在大型数据库系统中。当需要从多个表中提取相关数据时,联表查询就显得尤为重要。本篇将深入探讨“sql优化”与“左连接查询”的相关知识点,...

    ORACLE-SQL性能优化大全.pdf

    - **SQL优化机制**: - **SQL语句处理过程**:理解SQL语句在Oracle中的处理流程对于优化至关重要。 - **共享SQL区域**:Oracle会在内存的共享池中缓存已执行过的SQL语句,以便后续执行时可以直接使用而无需重新...

    慢SQL优化In PostgreSQL

    综上所述,针对PostgreSQL的慢SQL优化涉及多个方面,包括但不限于创建有效的索引、调整查询条件、减少不必要的操作以及合理配置系统参数等。通过对这些技术的综合运用,可以显著提高数据库系统的查询性能和响应速度...

    二、mysql优化技术-定位慢查询

    这篇博文可能详细介绍了如何诊断和解决MySQL中的慢查询问题,而`sql.txt`文件很可能包含了一些示例SQL语句或者慢查询日志。 慢查询通常是由于不恰当的索引使用、过度的数据扫描、复杂的查询逻辑或是资源争抢等原因...

    SQL-Optimization.rar_oracle_sql优化

    "SQL-Optimization.rar_oracle_sql优化"这个压缩包显然聚焦于如何提升Oracle数据库中的SQL语句性能。以下是对SQL优化及其在Oracle环境中的实践进行的详细解释。 一、SQL优化的重要性 SQL语句优化是数据库管理的核心...

    SQL Server数据库查询优化相关论文及资料

    在SQL Server数据库中,查询优化是一项至关重要的任务,它直接影响到系统的性能和效率。这篇论文集包含了一系列关于SQL语句查询优化的专业文献,对于数据库管理员、开发者以及对数据库性能优化感兴趣的学生来说,都...

    《基于Oracle的SQL优化》PDF版本下载.txt

    根据提供的文件信息,本文将对《基于Oracle的SQL优化》这一主题进行深入解析,包括但不限于SQL优化的重要性、Oracle数据库的特点以及具体的SQL优化方法等。 ### SQL优化的重要性 SQL(Structured Query Language)...

    mysql的explain分析sql语句性能(慢查询和加没加索引)

    MySQL 的 Explain 分析 SQL 语句性能(慢查询和加没加索引) MySQL 的 Explain 分析 SQL 语句性能是数据库性能优化的重要工具之一。通过 Explain 命令可以分析 SQL 语句的执行计划,了解 SQL 语句的执行过程,找出...

    【整理】数据库面试题索引sql优化+数据库SQL优化总结之百万级数据库优化

    4. **SQL性能分析**:了解如何使用EXPLAIN分析查询执行计划,找出慢查询的原因,如索引未被利用、全表扫描、排序或临时表的创建。 5. **SQL优化技巧**:这可能涉及重写SQL语句以减少资源消耗,比如使用连接操作而非...

    SQL查询慢的十大原因

    使用EXPLAIN或其他查询分析工具,找出并修复性能瓶颈,优化查询语句,是提升查询速度的重要手段。 了解并解决这些问题对于优化SQL查询性能至关重要。通过定期的性能监控、调整索引策略、优化查询语句以及升级硬件...

    SQL优化实践.pdf

    在SQL优化案例中,有三个具体的查询示例: 1. 查询1(QUERY-1):这个查询使用了子查询,可以考虑通过内联视图或临时表来优化。此外,可能需要检查是否所有索引都被有效利用。 2. 查询2(QUERY-2):这个查询使用了...

    mysql优化(1)show命令 慢查询日志 explain profiling

    在本篇中,我们将聚焦于三个MySQL优化工具:`SHOW`命令、慢查询日志和`EXPLAIN`与`PROFILING`。 一、SHOW命令 `SHOW`命令是MySQL中的一个非常实用的工具,用于获取数据库系统的信息。它可以帮助我们查看数据库的...

    Oracle SQL优化文档

    Oracle SQL优化文档提供了对Oracle数据库进行SQL语句优化的一系列方法和工具,旨在帮助数据库管理员和开发人员提高SQL查询性能和数据库效率。文档内容涵盖了从基本的SQL优化思路到具体的分析工具,详细介绍了如何...

Global site tag (gtag.js) - Google Analytics