mssql 临时表 MySQL EXPLAIN 命令详解
发布时间：2022-10-12 15:17:38 所属栏目：MsSql教程 来源：
导读： `post_status` varchar(20) NOT NULL DEFAULT 'publish' ,
`post_type` varchar(20) NOT NULL DEFAULT 'post',
PRIMARY KEY (`ID`),
KEY `type_status_date`(`post_typ
`post_type` varchar(20) NOT NULL DEFAULT 'post',
PRIMARY KEY (`ID`),
KEY `type_status_date`(`post_typ
|
`post_status` varchar(20) NOT NULL DEFAULT 'publish' , `post_type` varchar(20) NOT NULL DEFAULT 'post', PRIMARY KEY (`ID`), KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`) ) DEFAULT CHARSET=utf8 这个表的索引包括post_type、post_status、post_date 以及ID列。下面是一个演示索引列用法的SQL 查询： EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type=’post’ AND post_date > ‘2010-06-01’; 这个查询的QEP 返回的key_len 是62。这说明只有post_type列上的索引用到了(因为(20×3)+2=62)。尽管查询在WHERE 语句中使用了post_type 和post_date 列mssql 临时表，但只有post_type 部分被用到了。其他索引没有被使用的原因是MySQL 只能使用定义索引的最左边部分。为了更好地利用这个索引，可以修改这个查询来调整索引的列。请看下面的示例： mysql> EXPLAIN SELECT ID, post_title -> FROM wp_posts -> WHERE post_type='post' -> AND post_status='publish' -> AND post_date > '2010-06-01'; 在SELECT查询的添加一个post_status 列的限制条件后，QEP显示key_len 的值为132，这意味着post_type、post_status、post_date三列(62+62+8，(20×3)+2，(20×3)+2，8)都被用到了。此外，这个索引的主码列ID 的定义是使用MyISAM 存储索引的遗留痕迹。当使用InnoDB 存储引擎时，在非主码索引中包含主码列是多余的，这可以从key_len 的用法看出来。 相关的QEP 列还包括带有Using index 值的Extra 列。 2.5 table table 列是EXPLAIN 命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表的标识符，如派生表、子查询或集合。下面是QEP 中table 列的一些示例： table: item table: table: 表中N 和M 的值参考了另一个符合id 列值的table 行。相关的QEP 列还有select_type 2.6 select_type select_type 列提供了各种表示table 列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。 1. SIMPLE 对于不包含子查询和其他复杂语法的简单查询，这是一个常 见的类型。 2. PRIMARY 这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。 3. DERIVED 当一个表不是一个物理表时，那么就被叫做DERIVED。下面的SQL 语句给出了一个QEP 中DERIVED select-type 类型的 示例： mysql> EXPLAIN SELECT MAX(id) -> FROM (SELECT id FROM users WHERE first = ‘west’) c; 4. DEPENDENT SUBQUERY 这个select-type 值是为使用子查询而定义的。下面的SQL语句提供了这个值： mysql> EXPLAIN SELECT p.* -> FROM parent p -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c); 5. UNION 这是UNION 语句其中的一个SQL 元素。 6. UNION RESULT 这是一系列定义在UNION 语句中的表的返回结果。当select_type 为这个值时，经常可以看到table 的值是，这说明匹配的id 行是这个集合的一部分。下面的SQL产生了一个UNION和UNION RESULT select-type： mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val LIKE ‘a%’ -> UNION -> SELECT p.* FROM parent p WHERE p.id > 5; 2.7 partitions partitions 列代表给定表所使用的分区。这一列只会在EXPLAIN PARTITIONS 语句中出现。 2.8 Extra Extra 列提供了有关不同种类的MySQL 优化器路径的一系列额外信息。Extra 列可以包含多个值，可以有很多不同的取值，并且这些值还在随着MySQL 新版本的发布而进一步增加。下面给出常用值的列表。你可以从下面的地址找到更全面的值的列表：dev.mysql.com/doc/refman/5.5/en/explain-output.html。 1. Using where 这个值表示查询使用了where 语句来处理结果——例如执行全表扫描。如果也用到了索引，那么行的限制条件是通过获取必要的数据之后处理读缓冲区来实现的。 2. Using temporary 这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL 在执行查询期间创建临时表。两个常见的原因是在来自不同表的列上使用了DISTINCT，或者使用了不同的ORDER BY 和GROUP BY 列。想了解更多内容可以访问forge.mysql.com/wiki/Overview_of_query_execution_and_use_of_temp_tables。可以强制指定一个临时表使用基于磁盘的MyISAM 存储引擎。 这样做的原因主要有两个： ? 内部临时表占用的空间超过min(tmp_table_size，max_heap_table_size)系统变量的限制 ? 使用了TEXT/BLOB 列 3. Using filesort 这是ORDER BY 语句的结果。这可能是一个CPU 密集型的过程。可以通过选择合适的索引来改进性能，用索引来为查询结果排序。详细过程请参考第4章。 4. Using index 这个值重点强调了只需要使用索引就可以满足查询表的要求，不需要直接访问表数据。请参考第5 章的详细示例来理解这个值。 5. Using join buffer 这个值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能。 6. Impossible where 这个值强调了where 语句会导致没有符合条件的行。请看下面的示例：mysql> EXPLAIN SELECT * FROM user WHERE 1=2; 7. Select tables optimized away 这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。 8. Distinct 这个值意味着MySQL 在找到第一个匹配的行之后就会停止搜索其他行。 9. Index merges 当MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。 ? Using sort_union(…) ? Using union(…) ? Using intersect(…) 2.9 id id 列是在QEP 中展示的表的连续引用。 2.10 ref ref 列可以被用来标识那些用来进行索引比较的列或者常量。 2.11 filtered filtered 列给出了一个百分比的值，这个百分比值和rows 列的值一起使用，可以估计出那些将要和QEP 中的前一个表进行连接的行的数目。前一个表就是指id 列的值比当前表的id 小的表。这一列只有在EXPLAIN EXTENDED 语句中才会出现。 2.12 type type 列代表QEP 中指定的表使用的连接方式。下面是最常用的几种连接方式： ? const 当这个表最多只有一行匹配的行时出现system 这是const 的特例，当表只有一个row 时会出现 ? eq_ref 这个值表示有一行是为了每个之前确定的表而读取的 ? ref 这个值表示所有具有匹配的索引值的行都被用到 ? range 这个值表示所有符合一个给定范围值的索引行都被用到 ? ALL 这个值表示需要一次全表扫描其他类型的值还有fulltext 、ref_or_null 、index_merge 、unique_subquery、index_subquery 以及index。 想了解更多信息可以访问dev.mysql.com/doc/refman/5.5/en/explain-output.html。 三、解释EXPLAIN 输出结果 理解你的应用程序(包括技术和实现可能性)和优化SQL 语句同等重要。下面给出一个从父子关系中获取孤立的父辈记录的商业需求的例子。这个查询可以用三种不同的方式构造。尽管会产生相同的结果，但QEP 会显示三种不同的路径。 mysql> EXPLAIN SELECT p.* -> FROM parent p -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c)\G ********************* 1. row *********************** id: 1 select type: PRIMARY table: p type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 160 Extra: Using where ********************* 2. row *********************** id: 2 select_type: DEPENDENT SUBQUERY table: c type: index_subquery possible_keys: parent_id key: parent_id key_len: 4 ref: func rows: 1 Extra: Using index 2 rows in set (0.00 sec) EXPLAIN SELECT p.* FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE c.child_id IS NULL\G ********************* 1. row *********************** id: 1 select_type: SIMPLE table: p type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 160 Extra: ********************* 2. row *********************** id: 1 select_type: SIMPLE table: c type: ref possible_keys: parent_id key: parent_id key_len: 4 ref: test.p.id rows: 1 Extra: Using where; Using index; Not exists 2 rows in set (0.00 sec) （编辑：航空爱好网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐