MySQL 子查询很慢，单独运行会很快

Question

我必须调整一个查询，这个查询在 8000 万张表上，而且速度非常慢

 SELECT
     esm1.prop_id,
     epc.propertyname,
     esm1.thermostat_id,
     esm1.thermostat_name,
     count(esm1.thermostat_id)*5 as minutes
 FROM meta esm1 inner join data AS esdn
         on esm1.id=esdn.access_point_id_name_fk
 INNER JOIN prop  AS epc ON epc.proid = esm1.prop_id
 inner join (SELECT (esds.timebase) as tb, (esm2.thermostat_id) as tid
                 from string esds inner join meta esm2
                 on esds.access_point_id_name_fk=esm2.id
                 WHERE esm2.sensor_name = 'zoneClimate'
                         AND esds.access_point_id_value = 'Occupied'
                         and esds.timebase >= '60960'
                         AND esds.timebase <= '60986') as esds_tmp
         on esds_tmp.tb=esdn.timebase and esds_tmp.tid=esm1.thermostat_id;

此查询非常快，但总体结果不会在数小时内出现

SELECT (esds.timebase) as tb, (esm2.thermostat_id) as tid
                     from string esds inner join meta esm2
                     on esds.access_point_id_name_fk=esm2.id
                     WHERE esm2.sensor_name = 'zoneClimate'
                             AND esds.access_point_id_value = 'Occupied'
                             and esds.timebase >= '60960'
                             AND esds.timebase <= '60986'

请建议我在每个连接的列上都创建了索引

感谢您的帮助。

谢谢回复

这是解释计划

| id | select_type | table      | type   | possible_keys                       | key                     | key_len | ref                                    | rows | filtered | Extra       |
+----+-------------+------------+--------+-------------------------------------+-------------------------+---------+----------------------------------------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                | NULL                    | NULL    | NULL                                   |  465 |   100.00 |             |
|  1 | PRIMARY     | esm1       | ref    | PRIMARY,thermostat_id,idx_prop_id   | thermostat_id           | 258     | esds_tmp.tid                           |   14 |   100.00 | Using where |
|  1 | PRIMARY     | epc        | eq_ref | PRIMARY                             | PRIMARY                 | 4       | klarifydb.esm1.prop_id                 |    1 |   100.00 | Using where |
|  1 | PRIMARY     | esdn       | ref    | access_point_id_name_fk,timebase_id | access_point_id_name_fk | 5       | klarifydb.esm1.id                      | 9796 |   100.01 | Using where |
|  2 | DERIVED     | esds       | range  | timebase_id                         | timebase_id             | 5       | NULL                                   | 1033 |   100.00 | Using where |
|  2 | DERIVED     | esm2       | eq_ref | PRIMARY                             | PRIMARY                 | 4       | klarifydb.esds.access_point_id_name_fk |    1 |   100.00 | Using where |

myisam 表

  -> SELECT
    ->                                  esm1.prop_id,
    ->                             epc.propertyname,
    ->                                  epc.propertyname,
    ->                                  esm1.thermostat_id,
    ->                                  esm1.thermostat_name,
    ->                                  count(esm1.thermostat_id)*5 as minutes
    ->                              FROM es_sensor_metadata esm1 IGNORE INDEX (primary) inner join es_sensor_data_number  AS esdn
    ->                                      on esm1.id=esdn.access_point_id_name_fk and esm1.sensor_name = 'zoneAveTemp'  AND (esdn.access_point_id_value > 80
    ->                                         or esdn.access_point_id_value < 65)
    ->                              INNER JOIN es_property_c AS epc IGNORE INDEX (primary)  ON epc.proid = esm1.prop_id AND epc.clientid = 3
    ->                              WHERE EXISTS (
    ->                              SELECT 1
    ->                                              from es_sensor_data_string esds inner join es_sensor_metadata esm2
    ->                                              on esds.access_point_id_name_fk=esm2.id
    ->                                              WHERE esm2.sensor_name = 'zoneClimate'
    ->                                                      AND esds.access_point_id_value = 'Occupied'
    ->                                                      and esds.timebase = '60960'
    ->                                                      and esdn.timebase = esds.timebase
    ->                              and esm1.thermostat_id=esm2.thermostat_id
    ->                              )
    ->                    GROUP BY esm1.thermostat_id
    ->                    ORDER BY esm1.prop_id ASC, esm1.thermostat_id ASC;
+----+--------------------+-------+--------+-------------------------+-------------------------+---------+----------------------------------------+------+----------+----------------------------------------------+
| id | select_type        | table | type   | possible_keys           | key                     | key_len | ref                                    | rows | filtered | Extra                                        |
+----+--------------------+-------+--------+-------------------------+-------------------------+---------+----------------------------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY            | epc   | ALL    | client_index            | NULL                    | NULL    | NULL                                   |  805 |    94.78 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | esm1  | ref    | idx_prop_id             | idx_prop_id             | 5       | klarifydb.epc.proid                    |   61 |   100.00 | Using where                                  |
|  1 | PRIMARY            | esdn  | ref    | access_point_id_name_fk | access_point_id_name_fk | 5       | klarifydb.esm1.id                      | 9796 |   100.01 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | esds  | ref    | timebase_id             | timebase_id             | 5       | const                                  |   10 |   100.00 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | esm2  | eq_ref | PRIMARY,thermostat_id   | PRIMARY                 | 4       | klarifydb.esds.access_point_id_name_fk |    1 |   100.00 | Using where                                  |
+----+--------------------+-------+--------+-------------------------+-------------------------+---------+----------------------------------------+------+----------+----------------------------------------------+
5 rows in set, 3 warnings (0.04 sec)

Answer 1

尝试在不使用派生子查询的情况下重写此查询。

SELECT
     esm1.prop_id,
     epc.propertyname,
     esm1.thermostat_id,
     esm1.thermostat_name,
     count(esm1.thermostat_id)*5 as minutes
 FROM meta esm1 inner join data AS esdn
         on esm1.id=esdn.access_point_id_name_fk
 INNER JOIN prop  AS epc ON epc.proid = esm1.prop_id
 INNER JOIN string esds  ON esds.timebase = esdn.timebase
 INNER JOIN meta esm2    ON esds.access_point_id_name_fk=esm2.id
                            AND  esm2.tid=esm1.thermostat_id  
 WHERE esm2.sensor_name = 'zoneClimate'
   AND esds.access_point_id_value = 'Occupied'
   AND esds.timebase >= '60960'
   AND esds.timebase <= '60986'