查询耗时很长(包括Explain)

Question

查询的目标：

按地区显示比赛。

查询：

SELECT school_data_schools_outer.district_id, 
       school_data_race_ethnicity_raw_outer.year,  
       school_data_race_ethnicity_raw_outer.race,
       ROUND( 
           SUM( school_data_race_ethnicity_raw_outer.count) /
                (SELECT SUM(count)
                   FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_inner
             INNER JOIN school_data_schools as school_data_schools_inner 
                  USING (school_id)
                  WHERE school_data_schools_outer.district_id = school_data_schools_inner.district_id 
                    AND school_data_race_ethnicity_raw_outer.year = school_data_race_ethnicity_raw_inner.year) * 100, 2)
      FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_outer
INNER JOIN school_data_schools as school_data_schools_outer USING (school_id)
  GROUP BY school_data_schools_outer.district_id, 
           school_data_race_ethnicity_raw_outer.year, 
           school_data_race_ethnicity_raw_outer.race

mysql> explain SELECT school_data_schools_outer.district_id, school_data_race_ethnicity_raw_outer.year, school_data_race_ethnicity_raw_outer.race,ROUND(SUM(school_data_race_ethnicity_raw_outer.count)/( SELECT SUM(count) FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_inner INNER JOIN school_data_schools as school_data_schools_inner USING (school_id) WHERE school_data_schools_outer.district_id = school_data_schools_inner.district_id and school_data_race_ethnicity_raw_outer.year = school_data_race_ethnicity_raw_inner.year ) * 100,2) FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_outer INNER JOIN school_data_schools as school_data_schools_outer USING (school_id) GROUP BY school_data_schools_outer.district_id, school_data_race_ethnicity_raw_outer.year, school_data_race_ethnicity_raw_outer.race;
+----+--------------------+--------------------------------------+--------+----------------------------+---------+---------+----------------------------------------------------------------------+-------+---------------------------------+
| id | select_type        | table                                | type   | possible_keys              | key     | key_len | ref                                                                  | rows  | Extra                           |
+----+--------------------+--------------------------------------+--------+----------------------------+---------+---------+----------------------------------------------------------------------+-------+---------------------------------+
|  1 | PRIMARY            | school_data_race_ethnicity_raw_outer | ALL    | school_id,school_id_2      | NULL    | NULL    | NULL                                                                 | 84012 | Using temporary; Using filesort |
|  1 | PRIMARY            | school_data_schools_outer            | eq_ref | PRIMARY                    | PRIMARY | 257     | rocdocs_main_drupal_7.school_data_race_ethnicity_raw_outer.school_id |     1 |                                 |
|  2 | DEPENDENT SUBQUERY | school_data_race_ethnicity_raw_inner | ref    | school_id,year,school_id_2 | year    | 4       | func                                                                 |  8402 |                                 |
|  2 | DEPENDENT SUBQUERY | school_data_schools_inner            | eq_ref | PRIMARY                    | PRIMARY | 257     | rocdocs_main_drupal_7.school_data_race_ethnicity_raw_inner.school_id |     1 | Using where                     |
+----+--------------------+--------------------------------------+--------+----------------------------+---------+---------+----------------------------------------------------------------------+-------+---------------------------------+
4 rows in set (0.00 sec)

mysql>

mysql> describe school_data_race_ethnicity_raw;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| school_id | varchar(255) | NO   | MUL | NULL    |                |
| year      | int(11)      | NO   | MUL | NULL    |                |
| race      | varchar(255) | NO   |     | NULL    |                |
| count     | int(11)      | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> describe school_data_schools;
+-------------+----------------+------+-----+---------+-------+
| Field       | Type           | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+-------+
| school_id   | varchar(255)   | NO   | PRI | NULL    |       |
| grade_level | varchar(255)   | NO   |     | NULL    |       |
| district_id | varchar(255)   | NO   |     | NULL    |       |
| school_name | varchar(255)   | NO   |     | NULL    |       |
| address     | varchar(255)   | NO   |     | NULL    |       |
| city        | varchar(255)   | NO   |     | NULL    |       |
| lat         | decimal(20,10) | NO   |     | NULL    |       |
| lon         | decimal(20,10) | NO   |     | NULL    |       |
+-------------+----------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

注意:我也尝试过：

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

Answer 1

这是慢信标：

1. 您在school_data_race_ethnicity_raw_outer上没有正在使用的索引，因此它将扫描您正在使用的相关子查询
2. 的大约84,000行中的每一行，这意味着您的复杂计算必须每行运行一次，即84,000次。

最好的方法是不使用相关子查询，但如果不使用相关子查询，则需要使用covering indexes，这样整个内部查询(以及通过其自己的索引的其他部分)都可以使用索引快速运行。有关索引主题的优秀教程，请查看this。它教会了我很多！现在，您的内部查询只使用school_data_race_ethnicity_raw上的year索引，因此它必须通过读取84000次计算中的每一次的8,000行来查找其所需的其余内容。索引将使这一过程变得更快，例如，在school_data_race_ethnicity_raw上创建一个复合索引，您会发现它有帮助：

CREATE index inner_composite ON school_data_race_ethnicity_raw (year, district_id, schoolid, count)

这将允许从索引中获取WHERE中使用的所有字段，然后是连接字段，然后是要用于选择的字段。您应该会看到它显示在解释结果的“key”列中。此外，如果正确，您将在最右侧的列中看到“using index”，这表明没有发生表访问，速度快了几个数量级。

您可以通过为查询提到的列添加大量索引来试验快速处理样式，并查看在键列中选取了哪些内容。如果出现问题，请阅读您的查询以查看该表中的哪些其他列正在使用中，然后添加一个新的索引，并在右侧添加这些列，看看是否效果更好。一旦你发现了什么是有效的，记得删除未使用的索引。

MySQL不允许您直接索引一列的总和，这将是最快的方式，所以除非您想要移动到另一个DB (如果可以的话，这是个好主意)，否则这总是会有点慢。

Answer 2

这应该是所有你需要聚合的数据，以获得种族按地区计数，不确定为什么你做了这么多的数学在你的原始，因为这是不必要的，以实现你的目标，并强制一些疯狂的子查询。

SELECT SUM(students.count) as studentCount, School.district_id, students.race
FROM school_data_schools schools, 
school_data_race_ethnicity_raw students
WHERE shools.school_id = students.school_id
GROUP BY district_id, race

您可能还需要school_data_race_ethnicity_raw.school_id上的索引(单独使用，而不是作为多列键的一部分)

EDIT没有意识到OP正在寻找百分比细分，而不仅仅是总数

SELECT ((studentCount / districtTotal) * 100) as percentage, district_id, race

FROM(

SELECT SUM(students.count) as studentCount, Schools.district_id, students.race,
  (SELECT SUM(inStudents.count)
   FROM school_data_schools inSchools, 
    school_data_race_ethnicity_raw inStudents
   WHERE inSchools.school_id = inStudents.school_id
   AND inSchools.district_ID = Schools.district_id
   GROUP BY inSchools.district_id) as districtTotal

    FROM school_data_schools schools, 
    school_data_race_ethnicity_raw students

WHERE schools.school_id = students.school_id
GROUP BY district_id, race
) table1

这将运行得非常快，仍然需要确保在school_data_race_ethnicity_raw.school_id上有一个不属于多列索引的索引。您可以在操作here中看到它，尽管我的测试用例相当小，但它似乎确实通过了。