`
hudeyong926
  • 浏览: 2031563 次
  • 来自: 武汉
社区版块
存档分类
最新评论

EXPLAIN sql优化方法(3)DERIVED

阅读更多

派生表和视图的性能

从MySQL 4.1开始,它已经支持派生表、联机视图或者基本的FROM从句的子查询。

这些特性之间彼此相关,但是它们之间的性能比较如何呢?

MySQL 5.0 中的派生表似乎和视图实现的方式不同,尽管我从合并的代码基数来看觉得在查询优化上应该是一样的。

派生表仍然以临时表的方式显式地处理,而且还是没有索引的临时表(因此最好不要像在例子中那样连接2个派生表)

需要考虑的另一方面是,派生表需要被显式处理,尽管只是执行 EXPLAIN 语句。因此如果在 FROM 字句中的 SELELCT 操作上犯了错误,例如忘记了写上连接的条件,那么 EXPLAIN 可能会一直在运行。

视图则不同,它无需被显式处理,只是把查询简单地重写了一下。只有在无法合并查询或者试图创建者请求时才需要被显式处理。

这意味着它们在性能上的差别如下:

在基本的表上执行有索引 的查询,这非常快

mysql> SELECT * FROM test WHERE i=5 ;
+---+----------------------------------+
| i | j                                |
+---+----------------------------------+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+---+----------------------------------+
1 row IN SET ( 0 .03 sec)

在派生表上做同样的查询,则如老牛拉破车

mysql> SELECT * FROM ( SELECT * FROM test) t WHERE i=5 ;
+---+----------------------------------+
| i | j                                |
+---+----------------------------------+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+---+----------------------------------+
1 row IN SET ( 1 min 40 .86 sec)

在视图上查询,又快起来了 

mysql> CREATE VIEW v AS SELECT * FROM test;
Query OK, 0 rows affected ( 0 .08 sec)
 
mysql> SELECT * FROM v  WHERE i=5 ;
+---+----------------------------------+
| i | j                                |
+---+----------------------------------+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+---+----------------------------------+
1 row IN SET ( 0 .10 sec)

下面的2条EXPLAIN结果也许会让你很惊讶

mysql> EXPLAIN SELECT * FROM v  WHERE i=5 ;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | TABLE | type  | possible_keys | KEY      | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY      | test  | const | PRIMARY        | PRIMARY | 4        | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row IN SET ( 0 .02 sec)
 
mysql> EXPLAIN SELECT * FROM ( SELECT * FROM test) t WHERE i=5 ;
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | TABLE       | type | possible_keys | KEY   | key_len | ref  | rows    | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
|  1 | PRIMARY      | <derived2> | ALL   | NULL           | NULL | NULL     | NULL | 1638400 | USING WHERE |
|  2 | DERIVED     | test       | ALL   | NULL           | NULL | NULL     | NULL | 1638400 |             |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
2 rows IN SET ( 54 .90 sec)

避免使用派生表 -- 如果可能,最好采用其他方式来编写查询语句,大部分情况都比派生表来的快。很多情况下,甚至连独立的临时表都来的快,因为可以适当增加索引。

可以考虑使用临时试图来取代派生表 如果确实需要在 FROM 子句中使用到子查询,可以考虑在查询时创建试图,当查询完之后删除试图。

不适合多表视图,多表时用派生表取代视图

explain  select sum(pdm.qty) pre_total,pd.pre_doc_id from prepare_doc pd
left join pre_doc_item pdm on pd.pre_doc_id=pdm.pre_doc_id group by pd.pre_doc_id


 
 

 

  • 大小: 52.4 KB
分享到:
评论

相关推荐

    SQL优化和SQL执行分析工具Explain的使用详解和示例

    ### SQL优化与SQL执行分析工具Explain的使用详解 #### 一、SQL优化的重要性 SQL优化一直是数据库领域的重要话题,特别是在大数据量处理和高并发访问的场景下,优化后的SQL查询能够显著提升系统的响应速度和资源...

    SQL语句性能分析之explain

    ### SQL语句性能分析之explain #### 概述 在数据库管理及开发过程中,SQL查询语句的性能优化是一项至关重要的...通过以上方法,我们可以有效地利用`EXPLAIN`来分析和优化SQL查询性能,进而提高数据库的整体运行效率。

    MySQL SQL语句优化explain关键字

    MySQL中的`EXPLAIN`关键字是分析SQL查询性能的关键工具,它可以帮助我们理解查询的执行计划,以便进行有效的优化。本篇文章将深入探讨`EXPLAIN`的使用方法以及如何通过索引来提升查询效率。 首先,`EXPLAIN`关键字...

    【大厂面试题】MySQL执行计划及SQL优化

    知识点标签:explain、sql优化、索引、sql性能问题 题目描述 MySQL执行计划及SQL优化 1.SQL语句表头运行一个explain时,执行后所显示的表头字段如下: id : select查询的序列号,包含一组数字,表示查询中执行select...

    100 透彻研究通过explain命令得到的SQL执行计划(1).pdf

    在使用MySQL数据库时,explain命令是一个非常重要的工具,它可以帮助开发者理解MySQL优化器是如何执行一个给定的SQL查询语句的。通过分析执行计划,开发者可以更深入地了解查询性能,从而对SQL语句进行优化。本文将...

    MySQL执行计划及SQL优化Java系列2021.pdf

    了解并分析`EXPLAIN`输出可以帮助我们识别SQL查询的瓶颈,然后采取适当的优化措施,如添加合适的索引、调整查询结构或重写查询。在处理大型数据集时,SQL优化是必不可少的,因为它直接影响应用程序的响应时间和资源...

    explain分析sql效率的方法

    在SQL查询优化中,`EXPLAIN`命令是一个至关重要的工具,它可以帮助我们理解查询的执行计划,从而识别可能存在的性能瓶颈。本篇文章将详细介绍如何使用`EXPLAIN`来分析SQL语句的效率,并解释其各个属性的含义。 首先...

    查询优化之EXPLAIN的使用分析

    MySQL 提供了一个强大的工具,即 `EXPLAIN` 关键字,帮助我们理解 SQL 查询的执行计划,以便进行有效的优化。本篇文章将深入探讨 `EXPLAIN` 的使用分析及其在查询优化中的作用。 首先,让我们解析一下 `EXPLAIN` ...

    MYSQL EXPLAIN详解

    这对于理解 SQL 查询的工作原理、诊断性能问题以及优化查询具有重要意义。 在 MySQL 中,`EXPLAIN` 命令主要用于 SELECT 查询。例如,通过执行以下命令: ```sql EXPLAIN SELECT title FROM sakila.film WHERE ...

    Explain详解与索引最佳实践.docx

    在MySQL数据库管理中,了解如何使用`EXPLAIN`关键字对于优化SQL查询性能至关重要。`EXPLAIN`可以帮助我们分析查询执行计划,揭示MySQL是如何处理SQL语句的,从而找到潜在的性能瓶颈。本文档主要探讨了`EXPLAIN`的...

    mysqlexplain.ppt

    通过分析EXPLAIN输出,我们可以识别查询中的瓶颈,比如全表扫描(ALL)、索引未被使用(key为NULL)等情况,从而调整SQL语句,添加合适的索引,优化查询性能。理解这些执行计划的关键概念对于提升MySQL数据库的性能...

    Mysql Explain详细解析

    3. **使用 EXPLAIN ANALYZE**:在 MySQL 8.0.19 及更高版本中,可以通过 `EXPLAIN FORMAT=JSON` 或 `EXPLAIN ANALYZE` 来获取更详细的执行计划和性能数据。 4. **优化表结构**:如果 `EXPLAIN` 显示存在大量全表扫描...

    MySQL-Explain 详解

    MySQL Explain 是 MySQL 中的一个重要工具,用于解释和优化 SQL 查询语句。通过 Explain,可以了解 MySQL 是如何执行查询语句的,并且可以根据 Explain 的结果来优化查询语句。 一、用法 Explain 的用法非常简单...

    mysql explain详解

    MySQL中的`EXPLAIN`命令是用于分析SQL查询执行计划的重要工具,它可以帮助数据库管理员和开发者了解MySQL如何处理SQL语句,从而进行性能优化。在深入理解`EXPLAIN`之前,我们先来了解一下数据库优化的基本概念。 ...

    Mysql Explain

    通过 `EXPLAIN` 的分析结果,我们可以针对性地对查询语句进行优化,从而使查询优化器更好地工作。 #### 二、MySQL 查询优化器的工作原理 MySQL 查询优化器的主要目标之一是尽可能利用索引,并通过最严格的索引来...

    mysql explain的用法(使用explain优化查询语句)

    MySQL的`EXPLAIN`命令是数据库管理员和开发者用于分析SQL查询执行计划的重要工具。通过`EXPLAIN`,我们可以深入了解查询如何工作,以及如何优化数据库性能。以下是对`EXPLAIN`命令各个列的详细解释: 1. **id**: ...

    Mysql Explain 详解.docx

    MySQL中的`EXPLAIN`命令是用于分析SQL查询执行计划的重要工具,它可以帮助数据库管理员和开发者了解MySQL如何处理SQL语句,从而优化查询性能。在处理慢查询时,`EXPLAIN`是不可或缺的辅助手段。 一、`EXPLAIN`基本...

Global site tag (gtag.js) - Google Analytics