mysql - 使用多个连接优化 MySQL 查询

Question

我们正在运行的网络应用程序使用的查询之一如下:

SELECT
       p.id, r.id AS report_id, tr.result_id,
       r.report_date, r.department, r.reportStatus, rs.specimen,
       tr.name, tr.value, tr.flag, tr.unit, tr.reference_range
FROM patients AS p
INNER JOIN
    patients_reports AS pr ON pr.patient_id = p.id
INNER JOIN
    reports AS r ON pr.report_id = r.id
INNER JOIN
    results AS rs ON r.id = rs.report_id
INNER JOIN
    test_results AS tr ON rs.id = tr.result_id
WHERE pr.patient_id = 17548
ORDER BY rs.specimen, tr.name, r.report_date;

解释计划如下所示:

+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key       | key_len | ref               | rows   | Extra                                        |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | p     | const  | PRIMARY       | PRIMARY   | 4       | const             |      1 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | rs    | ALL    | PRIMARY       | NULL      | NULL    | NULL              | 152817 |                                              |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY       | PRIMARY   | 4       | demo.rs.report_id |      1 |                                              |
|  1 | SIMPLE      | pr    | eq_ref | PRIMARY       | PRIMARY   | 8       | const,demo.r.id   |      1 | Using where; Using index                     |
|  1 | SIMPLE      | tr    | ref    | result_id     | result_id | 5       | demo.rs.id        |      1 | Using where                                  |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+----------------------------------------------+

查询返回 27371 行。目前 test_results 中有 152730 行。这只是少量的演示数据。

我试图让查询更有效率，但我无法让它更快地执行。我查看了有关文档的各种文章和有关 stackoverflow 的问题，但未能解决此问题。

我尝试删除其中一个连接，如下所示:

SELECT
       pr.patient_id, r.id AS report_id, tr.result_id,
       r.report_date, r.department, r.reportStatus, rs.specimen,
       tr.name, tr.value, tr.flag, tr.unit, tr.reference_range
FROM patients_reports AS pr
INNER JOIN
    reports AS r ON pr.report_id = r.id
INNER JOIN
    results AS rs ON r.id = rs.report_id
INNER JOIN
    test_results AS tr ON rs.id = tr.result_id
WHERE pr.patient_id = 17548
ORDER BY rs.specimen, tr.name, r.report_date;

查询计划如下:

+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+---------------------------------+
| id | select_type | table | type   | possible_keys | key       | key_len | ref               | rows   | Extra                           |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+---------------------------------+
|  1 | SIMPLE      | rs    | ALL    | PRIMARY       | NULL      | NULL    | NULL              | 152817 | Using temporary; Using filesort |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY       | PRIMARY   | 4       | demo.rs.report_id |      1 |                                 |
|  1 | SIMPLE      | pr    | eq_ref | PRIMARY       | PRIMARY   | 8       | const,demo.r.id   |      1 | Using where; Using index        |
|  1 | SIMPLE      | tr    | ref    | result_id     | result_id | 5       | demo.rs.id        |      1 | Using where                     |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------+--------+---------------------------------+

没什么不同。

我已经尝试重新排列查询并使用 STRAIGHT_JOIN 等，但我没有取得任何进展。

如果我能提供有关如何优化查询的建议，我将不胜感激。谢谢。

编辑:啊!我在 results.report_id 上没有索引，但它似乎没有帮助:

+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+--------+---------------------------------+
| id | select_type | table | type   | possible_keys     | key       | key_len | ref               | rows   | Extra                           |
+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+--------+---------------------------------+
|  1 | SIMPLE      | rs    | ALL    | PRIMARY,report_id | NULL      | NULL    | NULL              | 152817 | Using temporary; Using filesort |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY           | PRIMARY   | 4       | demo.rs.report_id |      1 |                                 |
|  1 | SIMPLE      | pr    | eq_ref | PRIMARY           | PRIMARY   | 8       | const,demo.r.id   |      1 | Using where; Using index        |
|  1 | SIMPLE      | tr    | ref    | result_id         | result_id | 5       | demo.rs.id        |      1 | Using where                     |
+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+--------+---------------------------------+

编辑2:

patients_reports 看起来像这样:

+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| patient_id | int(11) | NO   | PRI | 0       |       |
| report_id  | int(11) | NO   | PRI | 0       |       |
+------------+---------+------+-----+---------+-------+

编辑3:

按照@DRapp 的建议添加 results.report_id 索引并再次尝试 STRAIGHT_JOIN 后:

SELECT STRAIGHT_JOIN
       r.id AS report_id, tr.result_id,
       r.report_date, r.department, r.reportStatus, rs.specimen,
       tr.name, tr.value, tr.flag, tr.unit, tr.reference_range
FROM patients_reports AS pr
INNER JOIN
    reports AS r ON pr.report_id = r.id
INNER JOIN
    results AS rs ON r.id = rs.report_id
INNER JOIN
    test_results AS tr ON rs.id = tr.result_id
WHERE pr.patient_id = 17548
ORDER BY rs.specimen, tr.name, r.report_date;

计划是这样的:

+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys     | key       | key_len | ref               | rows | Extra                                        |
+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | pr    | ref    | PRIMARY           | PRIMARY   | 4       | const             | 3646 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY           | PRIMARY   | 4       | demo.pr.report_id |    1 |                                              |
|  1 | SIMPLE      | rs    | ref    | PRIMARY,report_id | report_id | 5       | demo.r.id         |  764 | Using where                                  |
|  1 | SIMPLE      | tr    | ref    | result_id         | result_id | 5       | demo.rs.id        |    1 | Using where                                  |
+----+-------------+-------+--------+-------------------+-----------+---------+-------------------+------+----------------------------------------------+

所以我认为这看起来好多了，但我不确定具体如何判断。此外，查询似乎仍然花费与以前相同的时间。

Answer 1

我会使用 STRAIGHT_JOIN 并使用您的第二个查询，该查询首先包含 patients_reports 表，然后再连接到患者表以获取他们的姓名信息。此外，如果我没有看到它，那么 PATIENT_ID 列在 patients_reports 表上是否有一个索引本身，或者作为复合索引键的第一个元素？

此外，确保 RESULTS 在 Report_ID 上有一个索引，与 TEST_RESULTS(在 Result_ID 上的索引)相同