mysql - 插入选择查询需要 10 分钟以上

Question

几周前我运行一个查询时需要不到 1 分钟，现在需要 10 分钟以上，而且看不到结束的迹象。

新查询(需要很长时间)

select sds.school_id, 
  detail.year, 
  detail.race,
  ROUND((detail.count / summary.total) * 100 ,2) as percent 
FROM school_data_race_ethnicity_raw as detail
inner join school_data_schools as sds USING (school_id)
inner join (
  select sds2.district_id, year, sum(count) as total
  from school_data_race_ethnicity_raw
  inner join school_data_schools as sds2 USING (school_id)
  group by sds2.district_id, year
  ) as summary on summary.district_id = sds.district_id 
    and summary.year = detail.year

查询:

INSERT INTO school_data_race_ethnicity_schools (school_id, year, race, percent) (
    SELECT school_id,
           year,
           race,
           ROUND((count/(
        SELECT SUM(count) 
          FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_inner 
         WHERE school_id = school_data_race_ethnicity_raw_outer.school_id 
           and year = school_data_race_ethnicity_raw_outer.year)
                        ) * 100,2) as percent
      FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_outer)    

解释:

mysql> explain SELECT school_id,year,race,ROUND((count/(SELECT SUM(count) 
    -> FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_inner 
    -> WHERE 
    -> school_id = school_data_race_ethnicity_raw_outer.school_id and 
    -> year = school_data_race_ethnicity_raw_outer.year)) * 100,2) as percent
    -> FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_outer;
+----+--------------------+--------------------------------------+------+----------------------------+------+---------+-----------------------------------------------------------------+-------+-------------+
| id | select_type        | table                                | type | possible_keys              | key  | key_len | ref                                                             | rows  | Extra       |
+----+--------------------+--------------------------------------+------+----------------------------+------+---------+-----------------------------------------------------------------+-------+-------------+
|  1 | PRIMARY            | school_data_race_ethnicity_raw_outer | ALL  | NULL                       | NULL | NULL    | NULL                                                            | 84012 |             |
|  2 | DEPENDENT SUBQUERY | school_data_race_ethnicity_raw_inner | ref  | school_id,year,school_id_2 | year | 4       | rocdocs_main_drupal_7.school_data_race_ethnicity_raw_outer.year |  8402 | Using where |
+----+--------------------+--------------------------------------+------+----------------------------+------+---------+-----------------------------------------------------------------+-------+-------------+
2 rows in set (0.00 sec)

创建表:

mysql> show create table school_data_race_ethnicity_raw;
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                  |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| school_data_race_ethnicity_raw | CREATE TABLE `school_data_race_ethnicity_raw` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` varchar(255) NOT NULL,
  `year` int(11) NOT NULL,
  `race` varchar(255) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `school_id` (`school_id`,`year`),
  KEY `year` (`year`,`race`),
  KEY `school_id_2` (`school_id`)
) ENGINE=MyISAM AUTO_INCREMENT=84013 DEFAULT CHARSET=latin1 |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table school_data_race_ethnicity_schools;
+------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                              | Create Table                                                                                                                                                                                                                                                                                                                                                                           |
+------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| school_data_race_ethnicity_schools | CREATE TABLE `school_data_race_ethnicity_schools` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` varchar(255) NOT NULL,
  `year` int(11) NOT NULL,
  `race` varchar(255) NOT NULL,
  `percent` decimal(15,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `year` (`year`,`race`),
  KEY `school_id` (`school_id`,`year`)
) ENGINE=MyISAM AUTO_INCREMENT=24961 DEFAULT CHARSET=latin1 |
+------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show processlist;
+------+---------+--------------------+-----------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id   | User    | Host               | db                    | Command | Time | State        | Info                                                                                                 |
+------+---------+--------------------+-----------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 1739 | [REMOVED] |  [REMOVED] | rocdocs_main_drupal_7 | Query   | 1467 | Sending data | INSERT INTO school_data_race_ethnicity_schools (school_id, year, race, percent) (
SELECT school_id,y |
| 1800 | root    | localhost          | rocdocs_main_drupal_7 | Query   |    0 | NULL         | show processlist                                                                                     |
+------+---------+--------------------+-----------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Answer 1

由于您使用子查询来计算百分比的方式，您的 SELECT 将会非常慢。它正在读取每一行的全年数据。如果您使用子查询来选择总计并连接到该总计，那么它应该运行得更快。

在我的脑海中，这样的东西(虽然不理想)应该比您现有的查询快得多:

select detail.school_id, 
  detail.year, 
  detail.race,
  ROUND((detail.count / summary.total) * 100 ,2) as percent 
FROM school_data_race_ethnicity_raw as detail
inner join (
  select school_id, year, sum(count) as total
  from school_data_race_ethnicity_raw
  group by school_id, year
  ) as summary on summary.school_id = detail.school_id 
    and summary.year = detail.year