mysql - 提高查询性能/重写查询以在 MySQL 上更快

Question

我有几个查询对当前数据库中的数据运行非常缓慢(几分钟)，我想提高它们的性能。不幸的是，它们有点复杂，所以我通过谷歌获得的信息不足以让我弄清楚要添加哪些索引或者我是否需要重写我的查询或者什么......我希望有人能提供帮助。如果设置得当，我认为它们不应该这么慢。

第一个查询是:

SELECT i.name, i.id, COUNT(c.id) 
FROM cert_certificates c 
JOIN cert_histories h ON h.cert_certificate_id = c.id 
LEFT OUTER JOIN inspectors i ON h.inspector_id = i.id
LEFT OUTER JOIN cert_histories h2 
  ON (h2.cert_certificate_id = c.id AND h.date_changed < h2.date_changed)
WHERE (h.cert_status_ref_id = ? OR h.cert_status_ref_id = ?) 
  AND h2.id IS NULL
GROUP BY i.id, i.name
ORDER BY i.name

第二个查询是:

SELECT l.letter, c.number
FROM cert_certificates c 
JOIN cert_type_letter_refs l ON c.cert_type_letter_ref_id = l.id
JOIN cert_histories h ON h.cert_certificate_id = c.id
LEFT OUTER JOIN cert_histories h2 
  ON (h2.cert_certificate_id = c.id AND h.date_changed < h2.date_changed)
WHERE h.cert_status_ref_id = ? 
  AND h2.id IS NULL 
  AND h.inspector_id = ?
ORDER BY l.letter, c.number

cert_certificates 表和 cert_histories 表一样包含近 19k 条记录(尽管将来该表预计会增长到 cert_certificates 表大小的大约 2-3 倍)。其他 table 都很小；每条少于 10 条记录。

目前唯一的索引是每个表的 id 和 cert_certificates.number。我在几个地方(例如 here )阅读以添加外键索引，但在 cert_histories 表的情况下，几乎所有列(cert_certificate_id、inspector_id、cert_status_ref_id)也是 not advisable (根据该问题的一些答案，例如 Markus Winand 的)，所以我有点迷路了。

如有任何帮助，我们将不胜感激。

预计到达时间:第一个查询的 EXPLAIN 的结果是(对于可怕的格式感到抱歉；我正在使用 SQLyog，它将它呈现在一个漂亮的表格中，但似乎 StackOverflow 不支持表格？):

id select_type table   type    possible_keys   key key_len ref rows    Extra1   SIMPLE  h   ALL NULL    NULL    NULL    NULL    19740   Using where; Using temporary; Using filesort1   SIMPLE  i   ref index_inspectors_on_id  index_inspectors_on_id  768 marketing_development.h.inspector_id    1   1   SIMPLE  c   ref index_cert_certificates_on_id   index_cert_certificates_on_id   768 marketing_development.h.cert_certificate_id 91  Using where; Using index1   SIMPLE  h2  ALL NULL    NULL    NULL    NULL    19740   Using where

第二个查询:

id select_type table   type    possible_keys   key key_len ref rows    Extra1   SIMPLE  h   ALL NULL    NULL    NULL    NULL    19795   Using where; Using temporary; Using filesort1   SIMPLE  c   ref index_cert_certificates_on_id   index_cert_certificates_on_id   768 marketing_development.h.cert_certificate_id 91  Using where1   SIMPLE  l   ALL index_cert_type_letter_refs_on_id   NULL    NULL    NULL    5   Using where; Using join buffer1   SIMPLE  h2  ALL NULL    NULL    NULL    NULL    19795   Using where

Answer 1

您应该在连接字段上创建索引:

cert_certificates.cert_type_letter_ref_id
cert_histories.cert_certificate_id
cert_histories.date_changed
cert_histories.inspector_id