mysql慢查询Copying to tmp table消耗99%

mysql> EXPLAIN     -> SELECT     ->   *     -> FROM     ->   (     ->     SELECT     ->       EO.ID,     ->       hos.Affiliated_System AS ResourceSystem,     ->       EO.InfluenceEntity,     ->       EW.SecurityType,     ->       EW.WarningName,     ->       EO.WarningSubType,     ->       EO.AttackSrc,     ->       EO.AttackTarget,     ->       DATE_FORMAT(     ->         EO.WarningTime,     ->         '%Y-%m-%d %H:%i:%S'     ->       ) AS WarningTime,     ->       (     ->         CASE     ->         WHEN EO.ID = apr.warning_id     ->         AND RES.OperCorp = '营销运维' THEN     ->           apr.handle_status     ->         WHEN RES.OperCorp = '营销运维' THEN     ->           '1'     ->         ELSE     ->           EO.WarningStatus     ->         END     ->       ) WarningStatus_code,     ->       EW.WarningCode,     ->       EW.WarningSrcCode,     ->       EW.WarningDescription,     ->       EO.WarningLevel_code,     ->       PER.BELONG_DEPT,     ->       hos.ManageDep_code,     ->       RES.OperCorp     ->     FROM     ->       (     ->         event_warningmarketingonly EO,     ->         resource_appsystem_marketing_view RES,     ->         resource_host_marketing_view hos     ->       )     ->     LEFT JOIN event_warningability_sec EW ON EO.WarningSubType = EW.WarningCode     ->     LEFT JOIN r_system_person PER ON PER.system_id = RES.ID     ->     LEFT JOIN alarm_processing_result apr ON EO.ID = apr.warning_id     ->     WHERE     ->       hos.Affiliated_System = RES. NAME     ->     AND hos.ManageIP = EO.AttackTarget     ->     GROUP BY     ->       EO.ID,     ->       PER.system_id     ->   ) ALLTABLE     -> WHERE     ->   1 = 1 ; +----+--------------------+------------+--------+--------------------+----------+---------+------------------------+------+---------------------------------+ | id | select_type        | table      | type   | possible_keys      | key      | key_len | ref                    | rows | Extra                           | +----+--------------------+------------+--------+--------------------+----------+---------+------------------------+------+---------------------------------+ |  1 | PRIMARY            | <derived2> | ALL    | NULL               | NULL     | NULL    | NULL                   |   86 |                                 | |  2 | DERIVED            | <derived3> | ALL    | NULL               | NULL     | NULL    | NULL                   |   94 | Using temporary; Using filesort | |  2 | DERIVED            | EO         | ALL    | NULL               | NULL     | NULL    | NULL                   |   92 | Using join buffer               | |  2 | DERIVED            | EW         | eq_ref | PRIMARY            | PRIMARY  | 4       | prod.EO.WarningSubType |    1 |                                 | |  2 | DERIVED            | PER        | ALL    | NULL               | NULL     | NULL    | NULL                   |   27 |                                 | |  2 | DERIVED            | apr        | ALL    | NULL               | NULL     | NULL    | NULL                   |    1 |                                 | |  2 | DERIVED            | <derived5> | ALL    | NULL               | NULL     | NULL    | NULL                   |  316 | Using where; Using join buffer  | |  5 | DERIVED            | g          | system | NULL               | NULL     | NULL    | NULL                   |    0 | const row not found             | |  5 | DERIVED            | a          | ALL    | NULL               | NULL     | NULL    | NULL                   |  309 |                                 | | 11 | SUBQUERY           | NULL       | NULL   | NULL               | NULL     | NULL    | NULL                   | NULL | no matching row in const table  | | 10 | DEPENDENT SUBQUERY | k          | ALL    | NULL               | NULL     | NULL    | NULL                   |    7 | Using where                     | |  9 | DEPENDENT SUBQUERY | k          | ALL    | NULL               | NULL     | NULL    | NULL                   |    7 | Using where                     | |  8 | DEPENDENT SUBQUERY | e          | ref    | resource,appsystem | resource | 303     | prod.a.ID              |  128 | Using where                     | |  8 | DEPENDENT SUBQUERY | f          | eq_ref | PRIMARY            | PRIMARY  | 302     | prod.e.Application_ID  |    1 | Using where                     | |  7 | DEPENDENT SUBQUERY | NULL       | NULL   | NULL               | NULL     | NULL    | NULL                   | NULL | no matching row in const table  | |  6 | DEPENDENT SUBQUERY | NULL       | NULL   | NULL               | NULL     | NULL    | NULL                   | NULL | no matching row in const table  | |  3 | DERIVED            | a          | ALL    | NULL               | NULL     | NULL    | NULL                   |   94 |                                 | |  4 | DEPENDENT SUBQUERY | k          | ALL    | NULL               | NULL     | NULL    | NULL                   |    7 | Using where                     | +----+--------------------+------------+--------+--------------------+----------+---------+------------------------+------+---------------------------------+ 18 rows in set (4.29 sec)

mysql>  show variables like '%tmp%'; +-------------------+----------+ | Variable_name     | Value    | +-------------------+----------+ | max_tmp_tables    | 32       | | slave_load_tmpdir | /tmp     | | tmp_table_size    | 16777216 | | tmpdir            | /tmp     | +-------------------+----------+ 4 rows in set (0.00 sec) /etc/my.cnf添加   tmp_table_size = 256M mysql> show global status like 'qcache%'; +-------------------------+-------+ | Variable_name           | Value | +-------------------------+-------+ | Qcache_free_blocks      | 0     | | Qcache_free_memory      | 0     | | Qcache_hits             | 0     | | Qcache_inserts          | 0     | | Qcache_lowmem_prunes    | 0     | | Qcache_not_cached       | 0     | | Qcache_queries_in_cache | 0     | | Qcache_total_blocks     | 0     | +-------------------------+-------+ 8 rows in set (0.00 sec)   set profiling=on; 执行sql mysql> show profile; +----------------------+----------+ | Status               | Duration | +----------------------+----------+ | executing            | 0.000003 | | executing            | 0.000002 | | executing            | 0.000002 | | Sending data      | 0.000025 | | executing            | 0.000003 | | Sending data      | 0.000022 | | executing            | 0.000004 | | executing            | 0.000002 | | executing            | 0.000002 | | Sending data      | 0.000025 | | executing            | 0.000003 | | Sending data      | 0.000030 | | executing            | 0.000004 | | executing            | 0.000002 | | executing            | 0.000002 | | Sending data      | 0.000046 | | executing            | 0.000003 | | Sending data      | 0.000022 | | executing            | 0.000003 | | executing            | 0.000003 | | executing            | 0.000002 | | Sending data      | 0.000026 | | executing            | 0.000003 | | Sending data      | 0.000046 | | executing            | 0.000004 | | executing            | 0.000003 | | executing            | 0.000002 | | Sending data      | 0.000028 | | executing            | 0.000003 | | Sending data      | 0.000023 | | executing            | 0.000003 | | executing            | 0.000002 | | executing            | 0.000002 | | Sending data      | 0.000027 | | executing            | 0.000003 | | Sending data      | 0.000022 | | executing            | 0.000003 | | executing            | 0.000003 | | executing            | 0.000002 | | Sending data      | 0.000026 | | executing            | 0.000003 | | Sending data      | 0.000022 | | executing            | 0.000003 | | executing            | 0.000002 | | executing            | 0.000002 | | Sending data      | 0.000028 | | executing            | 0.000003 | | Sending data      | 0.000022 | | executing            | 0.000003 | | executing            | 0.000003 | | executing            | 0.000002 | | Sending data      | 0.000028 | | executing            | 0.000003 | | Sending data      | 0.000033 | | executing            | 0.000004 | | executing            | 0.000003 | | executing            | 0.000002 | | Sending data      | 0.000029 | | executing            | 0.000003 | | Sending data      | 0.000022 | | executing            | 0.000003 | | executing            | 0.000003 | | executing            | 0.000002 | | Sending data      | 0.000028 | | executing            | 0.000003 | | Sending data      | 0.000022 | | executing            | 0.000003 | | executing            | 0.000003 | | executing            | 0.000002 | | Sending data      | 0.000028 | | executing            | 0.000003 | | Sending data      | 0.000058 | | optimizing           | 0.000022 | | statistics           | 0.000036 | | preparing            | 0.000034 | | Creating tmp table   | 0.000328 | | executing            | 0.000005 | | Copying to tmp table | 4.457352 | | Sorting result       | 0.000181 | | Sending data         | 0.000595 | | removing tmp table   | 0.000137 | | Sending data         | 0.000016 | | init                 | 0.000037 | | optimizing           | 0.000028 | | statistics           | 0.000014 | | preparing            | 0.000012 | | executing            | 0.000004 | | Sending data         | 0.000544 | | end                  | 0.000008 | | query end            | 0.000006 | | closing tables       | 0.000004 | | removing tmp table   | 0.000089 | | closing tables       | 0.000005 | | removing tmp table   | 0.000119 | | closing tables       | 0.000005 | | removing tmp table   | 0.000068 | | closing tables       | 0.000032 | | freeing items        | 0.000114 | | logging slow query   | 0.000005 | | cleaning up          | 0.000010 | +----------------------+----------+