sql语句优化 - ITeye问答

0 0

sql语句优化10

现在有两张表tb1,tb2
tb1的表结构
id(主键),name
tb2的表结构
id(主键),type(主键),content,note,date
type可选值是：a,b,c,d,e

tb1,tb2是一对多的关系，通过id关联

例如下面的数据

tb1
id     name
01     tom
02     jim

tb2
id    type    content     note     date
01    a       xxx1        xx1      20130102
01    b       xxx2        xx2      20130103
01    c       xxx3        xx3      20130103
01    d       xxx4        xx4      20130103
01    e       xxx5        xx5      20130103
02    d       xxx4        xx4      20130103
02    e       xxx5        xx5      20130103

我现在想抽出这样的数据

id     name   type    content     note     date
01     tom    a       xxx1        xx1      20130102
01     tom    b       xxx2        xx2      20130103
01     tom    c       xxx3        xx3      20130103
01     tom    d       xxx4        xx4      20130103
01     tom    e       xxx5        xx5      20130103
02     jim    d       xxx4        xx4      20130103
02     jim    e       xxx5        xx5      20130103

写一个执行效率最高的SQL，我用的是MYSQL，
而且不要用到传说中的MYSQL的所谓临时表

问题补充：不好意思，我不是想要上面的结果，我是想要下面的结果

id     name   type1    content1     note1     date1   type2    content2     note2     date2      type3    content3     note3     date3     ...
01     tom    a       xxx1        xx1      20130102   b        xxx2         xx2       20130103   c        xxx3         xx3       20130103 ...
02     jim                                            b        xxx4         xx4       20130103                                             ...

问题补充：当某些SQL命令在MySQL数据库中被执行的时候，它可能需要先创建一些内部的临时表来完成比较复杂的排序或分组查询。MySQL的临时表分为 in-memory 和 on-disk 两种。如有可能，MySQL 总是首先使用 in-memory 的临时表，而当临时表变得太大的时候，也可能被转存为on-disk 的临时表。

如下几个条件下可能导致SQL命令需要创建临时表：

* 使用了不同的 ORDER BY 和 GROUP BY 条件，或它们包含了JOIN查询中非首先表的字段；
* 同时使用了DISTINCT和ORDER BY;
* 如果SQL命令使用了SQL_SMALL_RESULT选项，那么需要时将创建in-memory临时表，除非
查询中还包含有需用 on-disk 存储的元素；

一些条件下可能阻碍使用in-memory临时表, 导致MySQL改用on-disk临时表：

* 数据表中含有BLOB类型或TEXT类型字段列；
* 在GROUP BY或DISTINCT任何条件中含有超过512字节的列；
* 如果使用了UNION或UNION ALL，而且SELECT列中含有任何超过512字节的列；
* 如果in-memory临时表变得太大，超过tmp_table_size或max_heap_table_size数值时；

MySQL数据库用Created_tmp_tables和Created_tmp_disk_tables变量记录所用临时表的数目；

关于MySQL内部临时表（internal temporary table）的优化：

(1) 首先，应该尽可能考虑如何避免您的SQL命令创建临时表；

对于一个查询连接非常繁忙的数据库，频繁地使用需要创建临时表的查询本身就已经是一个性能瓶颈。作为一个 DBA，您需要重新检视您的数据表的结构以及各表之间的关联，重新考虑主键和索引，重组数据结构以减少应用中需不同的ORDER BY和GROUP BY的情况。
作为一个程序员，您则可能需要重新分析您的应用需求，设法简化SQL命令的复杂程度，例如，尽可能将单次的多层关联查询，拆分为较少关联层次的多次查询，或使用View表。

(2) 其次，应该尽量设法确保临时表被创建于内存而非磁盘之中；

如果实在是无法避免创建临时表，那么退而求其次，则需要尽量确保这些临时表能够被创建在内存之中。避免在结构设计和查询命令中使用BLOB和TEXT类型字段，或可考虑用 SUBSTRRING(colum,length)函数将其转换为字符串类型；用SQL_SMALL_RESULT选项通知数据库使用in-memory临时表；使用View来简化查询；使用RAM disk内存盘来存储MySQL 数据库的临时表（需确保无使用BLOB和TEXT字段）。

如何避免 On-Disk Temporary Tables

因为 Memory 存储引擎不支持 BLOB和TEXT 类型，所以包含有 BLOB和TEXT 类型字段的查询，当它需要用到隐式临时表（implicit temporary table）的时候，就不得不使用 on-disk的MyISAM临时表，即使它的查询结果可能只有很简单的几行数据。

这会导致一个严重的性能瓶颈，即使您能配置将MySQL的临时表存储到RAM disk上,依然还是会需要用到许多昂贵的操作系统的调用函数。在实用中还发现，某些SQL语句的临时表甚至根本连RAM disk都不能使用（此时SQL查询命令会因为不能创建临时表而失败）。

数据库 sql语句优化

2014年2月28日 05:58

笨笨老公
0
0 0 4

3个答案按时间排序按投票排序

0 0

你这个查询可以走索引的, 具体的join算法是nest loop join, 应该比较快的.

2014年2月28日 12:36

jimmee
78
0 0 6

添加评论

0 0

目测LZ压根没理解临时表是做什么用,这明显是最基础的join问题..就上面的的问题,直接用inner join或left join即可,sql如下:

select tb1.id, tb1.name, tb2.type, tb2.content, tb2.note, tb2.date
from tb1 inner join tb2 on tb1.id = tb2.id;

select tb1.id, tb1.name, tb2.type, tb2.content, tb2.note, tb2.date
from tb1 left join tb2 on tb1.id = tb2.id;

2014年2月28日 10:54

joaboo
107
1 1 11