mysql 慢速_加快慢速mysql查询

我正在尝试提高应用程序的性能。我可能需要创建在cron上运行的汇总表，所以应用程序不需要花费太多时间(5-10秒)。这是最好的主意吗？

鉴于下表：

mysql> describe school_data_sets_numeric_data;

+--------------+---------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------------+---------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| data_set_nid | int(11) | NO | MUL | NULL | |

| school_nid | int(11) | NO | MUL | NULL | |

| year | int(11) | NO | MUL | NULL | |

| description | varchar(255) | NO | | NULL | |

| value | decimal(18,5) | NO | | NULL | |

+--------------+---------------+------+-----+---------+----------------+

6 rows in set (0.00 sec)以下查询(为每所学校的每个data_set_nid运行一次)

此查询运行速度很快(0秒)：

SELECT year, description, CONCAT(FORMAT((value/(SELECT SUM(value)

FROM `school_data_sets_numeric_data` as numeric_data_inner

WHERE year = numeric_data_outer.year and data_set_nid = numeric_data_outer.data_set_nid and school_nid = numeric_data_outer.school_nid)) * 100, 2), '%') as value

FROM `school_data_sets_numeric_data` as numeric_data_outer

WHERE data_set_nid = 38251 and school_nid = 32805 ORDER BY id DESC;说明：

+----+--------------------+--------------------+------+---------------------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------------+--------------------+------+---------------------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+-----------------------------+

| 1 | PRIMARY | numeric_data_outer | ref | data_set_nid,data_set_nid_2,school_nid | data_set_nid | 8 | const,const | 17 | Using where; Using filesort |

| 2 | DEPENDENT SUBQUERY | numeric_data_inner | ref | year,data_set_nid,data_set_nid_2,school_nid | data_set_nid | 8 | rocdocs_main_drupal_7.numeric_data_outer.data_set_nid,rocdocs_main_drupal_7.numeric_data_outer.school_nid | 9 | Using where |

+----+--------------------+--------------------+------+---------------------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+-----------------------------+此查询运行缓慢(1.43秒)：

SELECT year, description, CONCAT(FORMAT((SUM(value)/(SELECT SUM(value)

FROM `school_data_sets_numeric_data` as numeric_data_inner

WHERE year = numeric_data_outer.year and data_set_nid = numeric_data_outer.data_set_nid)) * 100, 2), '%') as value

FROM `school_data_sets_numeric_data` as numeric_data_outer

WHERE data_set_nid = 38251 GROUP BY year,description ORDER BY id DESC;说明：

+----+--------------------+--------------------+------+----------------------------------+----------------+---------+-------+-------+----------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------------+--------------------+------+----------------------------------+----------------+---------+-------+-------+----------------------------------------------+

| 1 | PRIMARY | numeric_data_outer | ref | data_set_nid,data_set_nid_2 | data_set_nid_2 | 4 | const | 90640 | Using where; Using temporary; Using filesort |

| 2 | DEPENDENT SUBQUERY | numeric_data_inner | ref | year,data_set_nid,data_set_nid_2 | year | 4 | func | 38871 | Using where |

+----+--------------------+--------------------+------+----------------------------------+----------------+---------+-------+-------+----------------------------------------------+