php - 需要加快 MySQL 查询速度，只有 4,000 条记录，需要 3 秒以上，有 3 个连接

Question

情况是这样的:

我有几个表格(如下所述)可以跟踪公寓楼中的居民、他们的单元号以及最后一次“看到”他们的时间(我们有很多老年人有健康问题，因此检查他们很重要每 2 天左右一次；有时他们会死在这里，这就是我们知道如何检查他们的方式)。

“检查”的限制条件是它们必须在过去 48 小时内被看过；如果没有，查询应该拉起他们的记录。以下是我正在使用的表定义:

存储居民信息的“people”表:

MariaDB [olin2]> describe people;
+-------------+-------------+------+-----+-------------------+----------------+
| Field       | Type        | Null | Key | Default           | Extra          |
+-------------+-------------+------+-----+-------------------+----------------+
| id          | int(11)     | NO   | PRI | NULL              | auto_increment |
| fname       | varchar(32) | NO   | MUL | NULL              |                |
| lname       | varchar(32) | NO   |     | NULL              |                |
| dob         | date        | YES  |     | NULL              |                |
| license_no  | varchar(24) | NO   |     | NULL              |                | 
| date_added  | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| status      | varchar(8)  | NO   |     | Allow             |                |
| license_exp | date        | YES  |     | NULL              |                |
+-------------+-------------+------+-----+-------------------+----------------+

“单位”表，其中存储单位编号(人们会切换单位，所以我不希望它们出现在“人”表中):

MariaDB [olin2]> describe units;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| number   | varchar(3)  | NO   | MUL | NULL    |                |
| resident | int(11)     | NO   |     | NULL    |                |
| type     | varchar(16) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

和存储“支票”的“健康”表(住户的身份证号码、检查时间和检查对象等):

MariaDB [olin2]> describe wellness;
+--------------+-------------+------+-----+-------------------+----------------+
| Field        | Type        | Null | Key | Default           | Extra          |
+--------------+-------------+------+-----+-------------------+----------------+
| wellness_id  | int(11)     | NO   | PRI | NULL              | auto_increment |
| people_id    | int(11)     | NO   |     | NULL              |                |
| time_checked | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| check_type   | varchar(1)  | NO   |     | NULL              |                |
| username     | varchar(16) | NO   |     | NULL              |                |
| return_date  | timestamp   | YES  |     | NULL              |                |
+--------------+-------------+------+-----+-------------------+----------------+

wellness 表中的return_date 字段是当居民离开超过2 天时，显示时不会包含在结果中(实际上会包含在查询结果中，但我使用 PHP 过滤掉它们)。

这是我一直在使用的查询...几周来它运行良好，但是随着添加的记录越来越多，它的速度明显变慢(现在返回结果需要 3.5 秒):

select p.id, w.time_checked, w.username, w.return_date 
from people p 
    left join units u on p.id = u.resident 
    left join wellness w on p.id = w.people_id 
    left join wellness as w2 on w.people_id = w2.people_id 
        and w.time_checked < w2.time_checked
where w2.people_id is null 
    and w.time_checked  < (now() - interval 48 hour)
order by u.number

我知道我的问题是连接，但我不知道如何在没有它们的情况下获得我需要的结果和/或如何优化此查询以加快速度...这是结果示例(如果需要) :

+----+---------------------+----------+---------------------+
| id | time_checked        | username | return_date         |
+----+---------------------+----------+---------------------+
|  8 | 2013-12-01 11:00:13 | tluce    | 0000-00-00 00:00:00 |
+----+---------------------+----------+---------------------+
1 row in set (3.44 sec)

所以，在这个结果集中，居民 8 已经 3 天没有出现了......结果是正确的，但是 3.44 秒对于我的用户来说是 Not Acceptable ，必须等待。

关于如何改进它有什么想法吗？

编辑(更多信息):

我意识到更新每个人的健康条目会更容易和更快地访问；但是我喜欢手头上有这些数据，因为我从中生成图表以显示 A) 我们最常看到特定居民的时间和 B) 哪些工作人员最常检查人员(也就是 - 谁在做他们的工作，谁没有)

我确实使用了索引，下面是对我的查询进行 EXPLAIN 的结果:

 +------+-------------+-------+--------+---------------+---------+---------+-----------------    -+------+--------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len |  ref              | rows | Extra                                                              |
+------+-------------+-------+--------+---------------+---------+---------+-----------------    -+------+--------------------------------------------------------------------+
|    1 | SIMPLE      | u     | ALL    | NULL          | NULL    | NULL    | NULL             |  107 | Using temporary; Using filesort                                    |
|    1 | SIMPLE      | p     | eq_ref | PRIMARY,idx   | PRIMARY | 4       | olin2.u.resident |    1 | Using where                                                        |
|    1 | SIMPLE      | w     | ALL    | NULL          | NULL    | NULL    | NULL             | 7074 | Using where; Using join buffer (flat, BNL join)                    |
|    1 | SIMPLE      | w2    | ALL    | NULL          | NULL    | NULL    | NULL             | 7074 | Using where; Not exists; Using join buffer (incremental, BNL join)     |
+------+-------------+-------+--------+---------------+---------+---------+-----------------    -+------+--------------------------------------------------------------------+

people 表中的索引:id, fname, lname, license_no健康表:wellness_id单位表:id, number

Answer 1

在可能的键下，每个具有 NULL 的字段都表示未使用索引。因此您可以将索引添加到您用于加入的字段。例如 units.residents。你可以对 wellness.people_id 做同样的事情——