php - 优化我的 mysql 查询

Question

我清楚地知道关于这个主题已经提出了很多问题，但我无法优化我的问题。下面是我的查询:

    select tc.id, tc.name tc, s.name state, d.name district,     count(distinct case when curdate() between b.starttime and b.endtime then b.id end) as active,    (case when count(distinct case when curdate() between b.starttime and b.endtime then b.id end) > 0 then 'active' when tc.status = 'Archived-I' then 'transitioned' when count(distinct case when curdate() between b.starttime and b.endtime then b.id end) = 0 and tc.status != 'Archived-I' then 'Idle' end ) as _status,    count(distinct(b.id)) as batches, sum(case when sb.status = 'active' then 1 else 0 end) as in_training, count(distinct case when sb.status = 'complete' then sb.student_id end) as trained,    count(distinct(sa.student_id)) as assessed, count(distinct(sp.student_id)) as placed     from training_centers tc left join batches b on b.training_center_id = tc.id     left join student_batches sb on b.id = sb.batch_id     left join student_assessments sa on sa.batch_id = b.id     left join student_placements sp on sp.batch_id = b.id     left join states s on s.id = tc.state_id     left join districts d on d.id = tc.district_id     where tc.implementing_agency_id = 28     group by tc.id     order by tc.name

EXPLAIN 的输出如下:

    id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra    1|SIMPLE|tc|ref|implementing agency|implementing agency|4|const|201|Using temporary; Using filesort    1|SIMPLE|b|ALL|NULL|NULL|NULL|NULL|11018|    1|SIMPLE|sb|ref|batch id|batch id|4|ministry_mis.b.id|10|    1|SIMPLE|sa|ref|batch|batch|4|ministry_mis.b.id|7|    1|SIMPLE|sp|ALL|NULL|NULL|NULL|NULL|78799|    1|SIMPLE|s|eq_ref|PRIMARY|PRIMARY|4|ministry_mis.tc.state_id|1|    1|SIMPLE|d|eq_ref|PRIMARY|PRIMARY|4|ministry_mis.tc.district_id|1|1|

我已经尝试过查询缓存，并且由于我在 php 应用程序中使用此查询，所以我也尝试过 memcache。请帮助我理解，如何优化我的查询或者这里是否有问题。

Answer 1

在 students_placements 表上添加适当的索引(在查询中别名为 sp)。

CREATE INDEX student_placements_IX1
    ON student_placements (batch_id, student_id)

EXPLAIN 输出显示对该表的完整扫描(“ALL”)。有了该索引，我们期望 EXPLAIN 输出显示 ref 操作。

此外，在 batches 表(在查询中别名为 b)上添加一个索引，例如:

CREATE INDEX batches_IX1
    ON batches (training_center_id, id, starttime, endtime)

如果表查询中引用的所有列都来自索引，则该索引称为查询的“覆盖索引”，EXPLAIN 输出将显示“Using index” ” 在“额外”列中。

如果 id 是 training_centers 表中的唯一键或主键(在问题中别名为 tc)，您也可以考虑更改执行此操作的查询:

 GROUP BY tc.name, tc.id
 ORDER BY tc.name

training_centers 上的覆盖索引也可能有好处:

CREATE INDEX training_centers_IX1
    ON training_centers (implementing_agency_id, name, id, state_id, district_id, status)

我们需要在添加索引后查看 EXPLAIN，然后从那里开始。 (有时，如果有适当的索引可用，MySQL 可以利用索引来避免“使用文件排序”操作。)