MySQL5.6 运用临时表做SQL优化

以你之姓@ 2022-06-13 00:17 131阅读 0赞

有一个系统从Oracle迁移到MySQL，在Oracle中运行很快，在MySQL基本运行不出来(等了10分钟都无法运行出来),该加的索引都加了，最后用临时表解决问题。  
SELECT COUNT(1)  
  FROM (SELECT a.vendor\_id  
          FROM g\_VENDOR a LEFT JOIN g\_VENDOR\_CONTACT\_PERSON b ON a.vendor\_id = b.vendor\_id) a  
  LEFT JOIN (SELECT c.vendor\_id,  
                    d.vendor\_classify\_id,  
                    d.vendor\_classify\_code,  
                    d.vendor\_classify\_name  
               FROM g\_vendor\_classify\_link c, ipb\_vendor\_classify d  
              WHERE c.vendor\_classify\_id = d.vendor\_classify\_id) e ON a.vendor\_id =e.vendor\_id  
  LEFT JOIN (SELECT g.object\_id,  
                    f.file\_name,  
                    f.file\_type,  
                    f.state,  
                    f.update\_time,  
                    f.file\_dir,  
                    h.attachment\_type  
               FROM g\_attachment\_object\_relation g,  
                    g\_attachment                 f  
                    LEFT JOIN g\_attachment\_extend          h  
                    ON f.attachment\_id = h.attachment\_id  
              WHERE f.attachment\_id = g.attachment\_id  
                AND f.state = 2  
                AND g.state = 1  
                AND f.job\_type\_code = 'g\_Vendor\_Attachment\_File') h ON a.vendor\_id = h.object\_id;  
\+----+-------------+------------+--------+--------------------------------+--------------------------------+---------+--------------------- +------+----------------------------------------------------+  
| id | select\_type | table      | type   | possible\_keys                  | key                            | key\_len | ref                  | rows | Extra                                              |  
\+----+-------------+------------+--------+--------------------------------+--------------------------------+---------+--------------------- +------+----------------------------------------------------+  
|  1 | PRIMARY     | <derived2> | ALL    | NULL                           | NULL                           | NULL    | NULL                 | 5009 | NULL                                               |  
|  1 | PRIMARY     | <derived3> | ref    | <auto\_key0>                    | <auto\_key0>                    | 99      | a.vendor\_id          |   10 | NULL                                               |  
|  1 | PRIMARY     | <derived4> | ALL    | NULL                           | NULL                           | NULL    | NULL                 | 2970 | Using where; Using join buffer (Block Nested Loop) |  
|  4 | DERIVED     | f          | ALL    | PRIMARY                        | NULL                           | NULL    | NULL                 | 2970 | Using where                                        |  
|  4 | DERIVED     | g          | ref    | idx2\_g\_attachment\_object\_rel | idx2\_g\_attachment\_object\_rel | 98      | f.attachment\_id      |    1 | Using where                                        |  
|  4 | DERIVED     | h          | ref    | ind\_tae\_attachment\_id          | ind\_tae\_attachment\_id          | 99      | f.attachment\_id      |    1 | NULL                                               |  
|  3 | DERIVED     | c          | index  | NULL                           | index\_vendor\_classify          | 198     | NULL                 |  457 | Using where; Using index                           |  
|  3 | DERIVED     | d          | eq\_ref | PRIMARY                        | PRIMARY                        | 98      | c.vendor\_classify\_id |    1 | NULL                                               |  
|  2 | DERIVED     | a          | index  | NULL                           | ind\_sv\_VENDOR\_SOURCE           | 5       | NULL                 |  131 | Using index                                        |  
|  2 | DERIVED     | b          | ref    | ind\_svcp\_vendor\_id             | ind\_svcp\_vendor\_id             | 99      | a.vendor\_id          |    1 | Using index                                        |  
\+----+-------------+------------+--------+--------------------------------+--------------------------------+---------+------------------------------------+------+--------------------------------------+  
  
**改成如下方式，一共花费6s出来结果：**  
CREATE TEMPORARY TABLE tmp\_g\_VENDOR(vendor\_id VARCHAR(32));  
CREATE INDEX ind\_tsv\_vendor\_id ON tmp\_g\_VENDOR(vendor\_id);  
INSERT INTO tmp\_g\_VENDOR SELECT a.vendor\_id  
  FROM (SELECT a.vendor\_id  
          FROM g\_VENDOR a LEFT JOIN g\_VENDOR\_CONTACT\_PERSON b ON a.vendor\_id = b.vendor\_id) a  
  LEFT JOIN (SELECT c.vendor\_id,  
                    d.vendor\_classify\_id,  
                    d.vendor\_classify\_code,  
                    d.vendor\_classify\_name  
               FROM g\_vendor\_classify\_link c, ipb\_vendor\_classify d  
              WHERE c.vendor\_classify\_id = d.vendor\_classify\_id) e ON a.vendor\_id = e.vendor\_id;  
                
CREATE TEMPORARY TABLE tmp\_attachment\_object(object\_id VARCHAR(100));    
CREATE INDEX ind\_tao\_object\_id ON tmp\_attachment\_object(object\_id);      
INSERT INTO tmp\_attachment\_object SELECT g.object\_id  
               FROM   
                    g\_attachment\_object\_relation g,  
                    g\_attachment                 f  
                    LEFT JOIN  
                    g\_attachment\_extend          h  
                    ON f.attachment\_id = h.attachment\_id  
              WHERE f.attachment\_id = g.attachment\_id  
                AND f.state = 2  
                AND g.state = 1  
                AND f.job\_type\_code = 'g\_Vendor\_Attachment\_File';  
                        
SELECT COUNT(1) FROM tmp\_g\_VENDOR a LEFT JOIN tmp\_attachment\_object b ON a.vendor\_id = b.object\_id;

发表评论取消回复

表情：

评论列表（有 0 条评论，131人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL临时表SQL示例

在MySQL中，可以使用临时表来临时存储和处理数据。临时表在当前数据库连接会话中存在，并且在会话结束时自动删除。下面是一个创建和使用临时表的SQL示例：首先，创建临时表：

爱被打了一巴掌/ 2024年02月17日 09:40/ 0 赞/ 59 阅读

相关 SQL Server 临时表

先查看leamessages表；然后编写创建临时表的sql；临时表名字前面加\; ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10

淩亂°似流年/ 2023年01月16日 09:54/ 0 赞/ 175 阅读

相关 mysql优化：内存表和临时表

由于直接使用临时表来创建中间表，其速度不如人意，因而就有了把临时表建成内存表的想法。但内存表和临时表的区别且并不熟悉，需要查找资料了。一开始以为临时表是创建后存在，当连接

超、凢脫俗/ 2022年09月17日 13:24/ 0 赞/ 197 阅读

相关 MySQL5.6 运用临时表做SQL优化

有一个系统从Oracle迁移到MySQL，在Oracle中运行很快，在MySQL基本运行不出来(等了10分钟都无法运行出来),该加的索引都加了，最后用临时表解决问题。 SE

以你之姓@/ 2022年06月13日 00:17/ 0 赞/ 132 阅读

相关 mysql优化(2)---表的数量和临时表

author：skate time：2012/06/30 mysql优化(2)---表的数量和临时表 1.mysql如何open和close表 mysql

太过爱你忘了你带给我的痛/ 2022年06月10日 04:39/ 0 赞/ 182 阅读

相关 SQL：临时表

mysql> select version(); +-------------------------+ | version()

ゝ一世哀愁。/ 2022年04月22日 16:20/ 0 赞/ 259 阅读

相关【Mysql】临时表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0

小鱼儿/ 2022年03月01日 00:46/ 0 赞/ 355 阅读

相关 MySQL临时表

临时表临时表只在当前连接可见如果你使用PHP脚本来创建MySQL临时表，那每当PHP脚本执行完成后，该临时表也会自动销毁。如果你使用MySQL客户

待我称王封你为后i/ 2022年01月26日 14:15/ 0 赞/ 351 阅读

相关 Mysql 临时表

当工作在非常大的表上时，你可能偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些

古城微笑少年丶/ 2022年01月06日 13:29/ 0 赞/ 350 阅读

相关 SQL临时表

临时表就是那些名称以井号 (\) 开头的表。如果当用户断开连接时没有除去临时表，SQL Server 将自动除去临时表。临时表不存储在当前数据库内，而是存储在系统数据库 tem

淩亂°似流年/ 2022年01月05日 04:59/ 0 赞/ 377 阅读